Обсуждение: bug in Prepared statement with DELETE RETURNING and rule on view
Dear all, I found what I really think is a bug in the postgresql 8.4. I have an sql database structure in which a real table has a column that is used to mark the entries as deleted without really deleting them. Then, I have a view that is hiding this to the users, with proper rules that perform real actions on the table. So, a ON DELETE rule on this view is performing an UPDATE which marks the rows as delete without deleting them. The view is hiding the rows tagged as deleted. This code is working from several years and I have a web-service that performs several actions on top of this database. Those actions include a "DELETE ... RETURNING ..." command on the view. This web-service was implemented by a php script that did not use any prepared statement, and everything was working properly. I had performance issue with this solution and I decided to rewrite the service in C++, and to use prepared statements. The SQL commands are exactly the same, but they are now executed from a C++ application using libpq, and they use prepared statements. Now, when I perform the "DELETE...RETURNING ..." statement, the rows are properly marked as deleted in the database, but PQexecPrepared returns PGRES_COMMAND_OK and PQntuples returns 0, as if the query had no result. All other functions that allow retrieving the results behave as if no result was available. I posted this problem on a user mailing list, and with people that helped me in investigating this problem, we wrote a small application that reproduces the problem. I joined the application to this e-mail. The first version of the application worked properly ! After investigation, we realised that it was because, in the first version, the ON DELETE rule of the view really deleted the elements in the table. In this case, the PQexecPrepared statement returns PGRES_TUPLES_OK and we can retrieve the elements with dedicated functions. But once we change the ON DELETE rule to tag the entries as deleted, without really deleting them, the PQexecPrepared statement returns PGRES_COMMAND_OK and everything else behaves like if the statement had no result. In all cases, using non-prepared statements solve the problem. My client is compiled under Windows and uses a 8.4 postgresql server. I tested with one server hosted under Linux (debian) and one server hosted under Windows : both give the same result. One of the guys that helped me investigating the problem told me that he could reproduce the problem on a 9.3 beta 1 version, but I personnaly did not test it. Regards, Brice
Вложения
On Tuesday, May 28, 2013 12:39 AM Brice Andr=E9 wrote: > Dear all, >=20 > I found what I really think is a bug in the postgresql 8.4. >=20 > I have an sql database structure in which a real table has a column > that is used to mark the entries as deleted without really deleting > them. Then, I have a view that is hiding this to the users, with = proper > rules that perform real actions on the table. So, a ON DELETE rule on > this view is performing an UPDATE which marks the rows as delete > without deleting them. The view is hiding the rows tagged as deleted. >=20 > This code is working from several years and I have a web-service that > performs several actions on top of this database. Those actions = include > a "DELETE ... RETURNING ..." command on the view. This web-service was > implemented by a php script that did not use any prepared statement, > and everything was working properly. >=20 > I had performance issue with this solution and I decided to rewrite = the > service in C++, and to use prepared statements. The SQL commands are > exactly the same, but they are now executed from a C++ application > using libpq, and they use prepared statements. I had tried in latest 9.3 code with psql using prepared statements and = it worked fine, please see result below. I shall check your libpq application code as well, but in the mean time = can you please verify whether the below works for you on 8.4 (I don't have = 8.4 setup). postgres=3D> prepare t1plan (int,int) AS Delete from v1 where c1 between = $1 and $2=20 returning c1,deleted;=20 PREPARE=20 postgres=3D> Execute t1plan(10,90);=20 c1 | deleted=20 ----+---------=20 10 | t=20 11 | t=20 12 | t=20 13 | t=20 14 | t=20 15 | t=20 16 | t=20 17 | t=20 18 | t=20 19 | t=20 20 | t=20 21 | t=20 22 | t=20 23 | t=20 24 | t=20 25 | t=20 26 | t=20 27 | t=20 28 | t=20 29 | t=20 30 | t=20 31 | t=20 32 | t=20 33 | t=20 34 | t=20 35 | t=20 36 | t=20 37 | t=20 38 | t=20 39 | t=20 40 | t=20 41 | t=20 42 | t=20 43 | t=20 44 | t=20 45 | t=20 46 | t=20 47 | t=20 48 | t=20 49 | t=20 50 | t=20 51 | t=20 52 | t=20 53 | t=20 54 | t=20 55 | t=20 56 | t=20 57 | t=20 58 | t=20 59 | t=20 60 | t=20 61 | t=20 62 | t=20 63 | t=20 64 | t=20 65 | t=20 66 | t=20 67 | t=20 68 | t=20 69 | t=20 70 | t=20 71 | t=20 72 | t=20 73 | t=20 74 | t=20 75 | t=20 76 | t=20 77 | t=20 78 | t=20 79 | t=20 80 | t=20 81 | t=20 82 | t=20 83 | t=20 84 | t=20 85 | t=20 86 | t=20 87 | t=20 88 | t=20 89 | t=20 90 | t=20 (81 rows)=20 DELETE 0=20 With Regards, Amit Kapila.
On Tue, 2013-05-28 at 12:41 +0530, Amit Kapila wrote: > On Tuesday, May 28, 2013 12:39 AM Brice André wrote: > > Dear all, > > > > I found what I really think is a bug in the postgresql 8.4. > > > > I have an sql database structure in which a real table has a column > > that is used to mark the entries as deleted without really deleting > > them. Then, I have a view that is hiding this to the users, with proper > > rules that perform real actions on the table. So, a ON DELETE rule on > > this view is performing an UPDATE which marks the rows as delete > > without deleting them. The view is hiding the rows tagged as deleted. > > > > This code is working from several years and I have a web-service that > > performs several actions on top of this database. Those actions include > > a "DELETE ... RETURNING ..." command on the view. This web-service was > > implemented by a php script that did not use any prepared statement, > > and everything was working properly. > > > > I had performance issue with this solution and I decided to rewrite the > > service in C++, and to use prepared statements. The SQL commands are > > exactly the same, but they are now executed from a C++ application > > using libpq, and they use prepared statements. > > I had tried in latest 9.3 code with psql using prepared statements and it > worked fine, please see result below. > I shall check your libpq application code as well, but in the mean time can > you please verify whether the below works for you on 8.4 (I don't have 8.4 > setup). > It does work with psql and pgAdmin. The issue is with PQprepare, and PQexecPrepared. So yeah, you need to look at the application code. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
On Tuesday, May 28, 2013 1:28 PM Brice Andr=E9 wrote: I shall look into it today in later half of the day. > Dear Amit, >=20 > Thanks for your answer. >=20 > I performed the same test as you and I get the same result (on my > linux server, debian, postgresql 8.4). >=20 > Maybe the problem is related to libpq ? >=20 > Did you tried the C code provided to see if you can reproduce the > problem ? >=20 > Regards, > Brice >=20 >=20 > 2013/5/28 Amit Kapila <amit.kapila@huawei.com>: > > On Tuesday, May 28, 2013 12:39 AM Brice Andr=E9 wrote: > >> Dear all, > >> > >> I found what I really think is a bug in the postgresql 8.4. > >> > >> I have an sql database structure in which a real table has a column > >> that is used to mark the entries as deleted without really deleting > >> them. Then, I have a view that is hiding this to the users, with > proper > >> rules that perform real actions on the table. So, a ON DELETE rule > on > >> this view is performing an UPDATE which marks the rows as delete > >> without deleting them. The view is hiding the rows tagged as > deleted. > >> > >> This code is working from several years and I have a web-service > that > >> performs several actions on top of this database. Those actions > include > >> a "DELETE ... RETURNING ..." command on the view. This web-service > was > >> implemented by a php script that did not use any prepared = statement, > >> and everything was working properly. > >> > >> I had performance issue with this solution and I decided to rewrite > the > >> service in C++, and to use prepared statements. The SQL commands = are > >> exactly the same, but they are now executed from a C++ application > >> using libpq, and they use prepared statements. > > > > I had tried in latest 9.3 code with psql using prepared statements > and it > > worked fine, please see result below. > > I shall check your libpq application code as well, but in the mean > time can > > you please verify whether the below works for you on 8.4 (I don't > have 8.4 > > setup). > > > > > > postgres=3D> prepare t1plan (int,int) AS Delete from v1 where c1 > between $1 > > and $2 > > returning c1,deleted; > > PREPARE > > postgres=3D> Execute t1plan(10,90); > > c1 | deleted > > ----+--------- > > 10 | t > > 11 | t > > 12 | t > > 13 | t > > 14 | t > > 15 | t > > 16 | t > > 17 | t > > 18 | t > > 19 | t > > 20 | t > > 21 | t > > 22 | t > > 23 | t > > 24 | t > > 25 | t > > 26 | t > > 27 | t > > 28 | t > > 29 | t > > 30 | t > > 31 | t > > 32 | t > > 33 | t > > 34 | t > > 35 | t > > 36 | t > > 37 | t > > 38 | t > > 39 | t > > 40 | t > > 41 | t > > 42 | t > > 43 | t > > 44 | t > > 45 | t > > 46 | t > > 47 | t > > 48 | t > > 49 | t > > 50 | t > > 51 | t > > 52 | t > > 53 | t > > 54 | t > > 55 | t > > 56 | t > > 57 | t > > 58 | t > > 59 | t > > 60 | t > > 61 | t > > 62 | t > > 63 | t > > 64 | t > > 65 | t > > 66 | t > > 67 | t > > 68 | t > > 69 | t > > 70 | t > > 71 | t > > 72 | t > > 73 | t > > 74 | t > > 75 | t > > 76 | t > > 77 | t > > 78 | t > > 79 | t > > 80 | t > > 81 | t > > 82 | t > > 83 | t > > 84 | t > > 85 | t > > 86 | t > > 87 | t > > 88 | t > > 89 | t > > 90 | t > > (81 rows) > > > > > > DELETE 0 > > > > With Regards, > > Amit Kapila. > >
On Tuesday, May 28, 2013 1:54 PM Brice Andr=E9 wrote: > On Tuesday, May 28, 2013 1:28 PM Brice Andr=E9 wrote: >=20 > I shall look into it today in later half of the day. > > Dear Amit, > > > > Thanks for your answer. > > > > I performed the same test as you and I get the same result (on my > > linux server, debian, postgresql 8.4). > > > > Maybe the problem is related to libpq ? > > > > Did you tried the C code provided to see if you can reproduce the > > problem ? I checked your C code and found the reason why you are not able to get = the tuples returned by "Delete .. Returning .." Currently it is not supported to return tuples for non-select statements using PQexecPrepared and the reason is, there is no provision for Describe to send a RowDescription during this = execution. You can refer function PortalRunMulti() in code, if you want to know more details. I could see below way for you to change your application if you want = rows returned by "Delete .. Returning .." Use PQexec for below sql statements: prepare t1plan (int,int) AS Delete from v1 where c1 between $1 and $2 returning c1,deleted; Execute t1plan(10,90); After preparing once, you can call Execute SQL statement multiple times, = it can save your time of prepare each time of delete statement, which was your motto for using PQexecPrepared(). > > > > > 2013/5/28 Amit Kapila <amit.kapila@huawei.com>: > > > On Tuesday, May 28, 2013 12:39 AM Brice Andr=E9 wrote: > > >> Dear all, > > >> > > >> I found what I really think is a bug in the postgresql 8.4. > > >> > > >> I have an sql database structure in which a real table has a > column > > >> that is used to mark the entries as deleted without really > deleting > > >> them. Then, I have a view that is hiding this to the users, with > > proper > > >> rules that perform real actions on the table. So, a ON DELETE = rule > > on > > >> this view is performing an UPDATE which marks the rows as delete > > >> without deleting them. The view is hiding the rows tagged as > > deleted. > > >> > > >> This code is working from several years and I have a web-service > > that > > >> performs several actions on top of this database. Those actions > > include > > >> a "DELETE ... RETURNING ..." command on the view. This = web-service > > was > > >> implemented by a php script that did not use any prepared > statement, > > >> and everything was working properly. > > >> > > >> I had performance issue with this solution and I decided to > rewrite > > the > > >> service in C++, and to use prepared statements. The SQL commands > are > > >> exactly the same, but they are now executed from a C++ = application > > >> using libpq, and they use prepared statements. > > > > > > I had tried in latest 9.3 code with psql using prepared statements > > and it > > > worked fine, please see result below. > > > I shall check your libpq application code as well, but in the mean > > time can > > > you please verify whether the below works for you on 8.4 (I don't > > have 8.4 > > > setup). > > > > > > > > > postgres=3D> prepare t1plan (int,int) AS Delete from v1 where c1 > > between $1 > > > and $2 > > > returning c1,deleted; > > > PREPARE > > > postgres=3D> Execute t1plan(10,90); > > > c1 | deleted > > > ----+--------- > > > 10 | t > > > 11 | t > > > 12 | t > > > 13 | t > > > 14 | t > > > 15 | t > > > 16 | t > > > 17 | t > > > 18 | t > > > 19 | t > > > 20 | t > > > 21 | t > > > 22 | t > > > 23 | t > > > 24 | t > > > 25 | t > > > 26 | t > > > 27 | t > > > 28 | t > > > 29 | t > > > 30 | t > > > 31 | t > > > 32 | t > > > 33 | t > > > 34 | t > > > 35 | t > > > 36 | t > > > 37 | t > > > 38 | t > > > 39 | t > > > 40 | t > > > 41 | t > > > 42 | t > > > 43 | t > > > 44 | t > > > 45 | t > > > 46 | t > > > 47 | t > > > 48 | t > > > 49 | t > > > 50 | t > > > 51 | t > > > 52 | t > > > 53 | t > > > 54 | t > > > 55 | t > > > 56 | t > > > 57 | t > > > 58 | t > > > 59 | t > > > 60 | t > > > 61 | t > > > 62 | t > > > 63 | t > > > 64 | t > > > 65 | t > > > 66 | t > > > 67 | t > > > 68 | t > > > 69 | t > > > 70 | t > > > 71 | t > > > 72 | t > > > 73 | t > > > 74 | t > > > 75 | t > > > 76 | t > > > 77 | t > > > 78 | t > > > 79 | t > > > 80 | t > > > 81 | t > > > 82 | t > > > 83 | t > > > 84 | t > > > 85 | t > > > 86 | t > > > 87 | t > > > 88 | t > > > 89 | t > > > 90 | t > > > (81 rows) > > > > > > > > > DELETE 0 > > > > > > With Regards, > > > Amit Kapila. > > > >=20 >=20 >=20 > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs
On Tuesday, May 28, 2013 6:50 PM Brice Andr=E9 wrote: > Hello Amit, >=20 > Thanks for your answer. >=20 > The reason why it does not work is still not really clear for me .What > I find very strange is that, if you perform exactly same request, with > exactly same C++ code, but that you change the database schema so that > the ON DELETE rule of the view really deletes elements, it works > properly. The inconsistency between both cases looks very strange to > me. What happens when you change ON DELETE rule of the view that really = deletes elements is that command type after applying rule remains same which = means Delete, so it can set the Tag. Refer Function QueryRewrite(). Setting tag means after sql statement execution, it tells you the number = of elements affected. For example 1. when your rule is such that it internally updates, it will mention = after sql execution as=20 DELETE 0 2. when your rule is such that it internally deletes, it will mention = after sql execution as DELETE 81 Now based on whether you can set the tag or not, ChoosePortalStrategy() = will decide portal strategy (PORTAL_ONE_RETURNING or PORTAL_RUN_MULTI). When the rule is to do update, in that case it choose PORTAL_RUN_MULTI = which doesn't send tuples. This is very old code, not sure how we can change to make it work for = your case. I had mentioned my analysis related to code so that others can = also give suggestions. =20 > Thank you for your workaround. I will test it and, if it works, I will > use it as, I agree with you, it should meet my performance > requirements.Note that my application can perform some SQL requests > thousands of time during the same server session. So, for me, using > prepared statement is an important feature ! > Thanks for your help. > Regards, > Brice >=20 > 2013/5/28 Amit Kapila <amit.kapila@huawei.com>: > > On Tuesday, May 28, 2013 1:54 PM Brice Andr=E9 wrote: > >> On Tuesday, May 28, 2013 1:28 PM Brice Andr=E9 wrote: > >> > >> I shall look into it today in later half of the day. > >> > Dear Amit, > >> > > >> > Thanks for your answer. > >> > > >> > I performed the same test as you and I get the same result (on my > >> > linux server, debian, postgresql 8.4). > >> > > >> > Maybe the problem is related to libpq ? > >> > > >> > Did you tried the C code provided to see if you can reproduce the > >> > problem ? > > > > I checked your C code and found the reason why you are not able to > get the > > tuples returned by "Delete .. Returning .." > > > > Currently it is not supported to return tuples for non-select > statements > > using PQexecPrepared and the reason is, there is > > no provision for Describe to send a RowDescription during this > execution. > > You can refer function PortalRunMulti() in code, if you want to know > > more details. > > > > I could see below way for you to change your application if you want > rows > > returned by "Delete .. Returning .." > > Use PQexec for below sql statements: > > > > prepare t1plan (int,int) AS Delete from v1 where c1 between $1 and > $2 > > returning c1,deleted; > > Execute t1plan(10,90); > > > > After preparing once, you can call Execute SQL statement multiple > times, it > > can save your time of prepare each time of delete statement, which > was > > your motto for using PQexecPrepared(). > > > >> > >> > > >> > 2013/5/28 Amit Kapila <amit.kapila@huawei.com>: > >> > > On Tuesday, May 28, 2013 12:39 AM Brice Andr=E9 wrote: > >> > >> Dear all, > >> > >> > >> > >> I found what I really think is a bug in the postgresql 8.4. > >> > >> > >> > >> I have an sql database structure in which a real table has a > >> column > >> > >> that is used to mark the entries as deleted without really > >> deleting > >> > >> them. Then, I have a view that is hiding this to the users, > with > >> > proper > >> > >> rules that perform real actions on the table. So, a ON DELETE > rule > >> > on > >> > >> this view is performing an UPDATE which marks the rows as > delete > >> > >> without deleting them. The view is hiding the rows tagged as > >> > deleted. > >> > >> > >> > >> This code is working from several years and I have a web- > service > >> > that > >> > >> performs several actions on top of this database. Those = actions > >> > include > >> > >> a "DELETE ... RETURNING ..." command on the view. This web- > service > >> > was > >> > >> implemented by a php script that did not use any prepared > >> statement, > >> > >> and everything was working properly. > >> > >> > >> > >> I had performance issue with this solution and I decided to > >> rewrite > >> > the > >> > >> service in C++, and to use prepared statements. The SQL > commands > >> are > >> > >> exactly the same, but they are now executed from a C++ > application > >> > >> using libpq, and they use prepared statements. > >> > > > >> > > I had tried in latest 9.3 code with psql using prepared > statements > >> > and it > >> > > worked fine, please see result below. > >> > > I shall check your libpq application code as well, but in the > mean > >> > time can > >> > > you please verify whether the below works for you on 8.4 (I > don't > >> > have 8.4 > >> > > setup). > >> > > > >> > > > >> > > postgres=3D> prepare t1plan (int,int) AS Delete from v1 where = c1 > >> > between $1 > >> > > and $2 > >> > > returning c1,deleted; > >> > > PREPARE > >> > > postgres=3D> Execute t1plan(10,90); > >> > > c1 | deleted > >> > > ----+--------- > >> > > 10 | t > >> > > 11 | t > >> > > 12 | t > >> > > 13 | t > >> > > 14 | t > >> > > 15 | t > >> > > 16 | t > >> > > 17 | t > >> > > 18 | t > >> > > 19 | t > >> > > 20 | t > >> > > 21 | t > >> > > 22 | t > >> > > 23 | t > >> > > 24 | t > >> > > 25 | t > >> > > 26 | t > >> > > 27 | t > >> > > 28 | t > >> > > 29 | t > >> > > 30 | t > >> > > 31 | t > >> > > 32 | t > >> > > 33 | t > >> > > 34 | t > >> > > 35 | t > >> > > 36 | t > >> > > 37 | t > >> > > 38 | t > >> > > 39 | t > >> > > 40 | t > >> > > 41 | t > >> > > 42 | t > >> > > 43 | t > >> > > 44 | t > >> > > 45 | t > >> > > 46 | t > >> > > 47 | t > >> > > 48 | t > >> > > 49 | t > >> > > 50 | t > >> > > 51 | t > >> > > 52 | t > >> > > 53 | t > >> > > 54 | t > >> > > 55 | t > >> > > 56 | t > >> > > 57 | t > >> > > 58 | t > >> > > 59 | t > >> > > 60 | t > >> > > 61 | t > >> > > 62 | t > >> > > 63 | t > >> > > 64 | t > >> > > 65 | t > >> > > 66 | t > >> > > 67 | t > >> > > 68 | t > >> > > 69 | t > >> > > 70 | t > >> > > 71 | t > >> > > 72 | t > >> > > 73 | t > >> > > 74 | t > >> > > 75 | t > >> > > 76 | t > >> > > 77 | t > >> > > 78 | t > >> > > 79 | t > >> > > 80 | t > >> > > 81 | t > >> > > 82 | t > >> > > 83 | t > >> > > 84 | t > >> > > 85 | t > >> > > 86 | t > >> > > 87 | t > >> > > 88 | t > >> > > 89 | t > >> > > 90 | t > >> > > (81 rows) > >> > > > >> > > > >> > > DELETE 0 > >> > > > >> > > With Regards, > >> > > Amit Kapila. > >> > > > >> > >> > >> > >> -- > >> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > >> To make changes to your subscription: > >> http://www.postgresql.org/mailpref/pgsql-bugs > >
[ got around to looking at this thread finally ]
Amit Kapila <amit.kapila@huawei.com> writes:
> What happens when you change ON DELETE rule of the view that really deletes
> elements is that command type after applying rule remains same which means
> Delete, so it can set the Tag.
The point here is that in extended-query mode, we've defined that only
the same statement that sets the command tag can return RETURNING rows.
In the case at hand, the original DELETE isn't executed at all, being
replaced by an UPDATE according to the rule. But we don't change the
returned command tag to UPDATE, and we don't let the UPDATE's RETURNING
clause return anything to the client. Both of these rules are meant to
ensure unsurprising behavior as seen from the client side. We could
debate changing them, but I'd be pretty worried about breaking user
applications if we did.
At the same time, things don't look terribly consistent because in psql
(which uses simple query protocol) you *do* see the RETURNING results.
That's because simple query protocol doesn't have a restriction that
only one resultset can be returned from a single query. So it's a lot
more wild-west as to what will really happen, and application code is
expected to just deal with that. psql doesn't have a problem with
multiple query results because it doesn't particularly care what they
are; it's just going to print each one. Apps that are supposed to
actually make sense of the data have more of an issue with that. The
extended query protocol was explicitly designed to lock things down
better so that interactions would be more predictable.
The main thing I'm noticing in looking at this is that the documentation
doesn't seem to explain anywhere the restriction to getting RETURNING
results back from only the primary query. We ought to fix that.
regards, tom lane
On Tuesday, June 11, 2013 12:15 AM Tom Lane wrote: > [ got around to looking at this thread finally ] > > Amit Kapila <amit.kapila@huawei.com> writes: > > What happens when you change ON DELETE rule of the view that really > deletes > > elements is that command type after applying rule remains same which > means > > Delete, so it can set the Tag. > > The point here is that in extended-query mode, we've defined that only > the same statement that sets the command tag can return RETURNING rows. > In the case at hand, the original DELETE isn't executed at all, being > replaced by an UPDATE according to the rule. But we don't change the > returned command tag to UPDATE, and we don't let the UPDATE's RETURNING > clause return anything to the client. Both of these rules are meant to > ensure unsurprising behavior as seen from the client side. We could > debate changing them, but I'd be pretty worried about breaking user > applications if we did. There are only 2 points I could think of supporting such behavior: 1. Explain on Delete statement will show Update, so returning command tag as Update is not wrong. 2. Maintaining consistency between psql and client interface. I think user's have facility to obtain information about prepared statement by using PQdescribePrepared() to know what they could expect in result. > At the same time, things don't look terribly consistent because in psql > (which uses simple query protocol) you *do* see the RETURNING results. > That's because simple query protocol doesn't have a restriction that > only one resultset can be returned from a single query. So it's a lot > more wild-west as to what will really happen, and application code is > expected to just deal with that. psql doesn't have a problem with > multiple query results because it doesn't particularly care what they > are; it's just going to print each one. Apps that are supposed to > actually make sense of the data have more of an issue with that. The > extended query protocol was explicitly designed to lock things down > better so that interactions would be more predictable. > > The main thing I'm noticing in looking at this is that the > documentation > doesn't seem to explain anywhere the restriction to getting RETURNING > results back from only the primary query. We ought to fix that. I could think of below text that can be mentioned either in Create Rule (Notes Section) page or in Extended Query section: For extended-query mode, if the RULE changes the original statement, command tag will not be modified and RETURNING clause will not return rows. With Regards, Amit Kapila.