Re: [BUGS] bug in Prepared statement with DELETE RETURNING and rule on view
On Tuesday, June 11, 2013 12:15 AM Tom Lane wrote: [ got around to looking at this thread finally ] Amit Kapila amit.kap...@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. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] bug in Prepared statement with DELETE RETURNING and rule on view
[ got around to looking at this thread finally ] Amit Kapila amit.kap...@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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] bug in Prepared statement with DELETE RETURNING and rule on view
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). postgres= prepare t1plan (int,int) AS Delete from v1 where c1 between $1 and $2 returning c1,deleted; PREPARE postgres= 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
Re: [BUGS] bug in Prepared statement with DELETE RETURNING and rule on view
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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] bug in Prepared statement with DELETE RETURNING and rule on view
On Tuesday, May 28, 2013 1:28 PM Brice André 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 ? Regards, Brice 2013/5/28 Amit Kapila amit.kap...@huawei.com: 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). postgres= prepare t1plan (int,int) AS Delete from v1 where c1 between $1 and $2 returning c1,deleted; PREPARE postgres= 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
Re: [BUGS] bug in Prepared statement with DELETE RETURNING and rule on view
On Tuesday, May 28, 2013 1:54 PM Brice André wrote: On Tuesday, May 28, 2013 1:28 PM Brice André 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.kap...@huawei.com: 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). postgres= prepare t1plan (int,int) AS Delete from v1 where c1 between $1 and $2 returning c1,deleted; PREPARE postgres= 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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] bug in Prepared statement with DELETE RETURNING and rule on view
On Tuesday, May 28, 2013 6:50 PM Brice André wrote: Hello Amit, Thanks for your answer. 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 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. 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 2013/5/28 Amit Kapila amit.kap...@huawei.com: On Tuesday, May 28, 2013 1:54 PM Brice André wrote: On Tuesday, May 28, 2013 1:28 PM Brice André 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.kap...@huawei.com: 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). postgres= prepare t1plan (int,int) AS Delete from v1 where c1 between $1 and $2 returning c1,deleted; PREPARE postgres= Execute t1plan(10,90); c1 | deleted +- 10 | t 11 | t 12 | t
