Re: [HACKERS] Proposal: Solving the Return proper effected tuple count from complex commands [return] issue
On Sun, 8 Sep 2002 19:50:21 -0300, Steve Howe [EMAIL PROTECTED] wrote: Proposal #1 (author: Steve Howe): - PQcmdStatus() == Should return the last executed command #1a or the same as the original command #1b = #2 PQcmdTuples() == should return the sum of modified rows of all commands executed by the rule (DELETE / INSERT / UPDATE). = #2c PQoidValue() == should return the value for the last INSERT executed command in the rule (if any). Proposal #2 (author: Tom lane): --- PQcmdStatus() == Should always return the same command type original submitted by the client. PQcmdTuples() == If no INSTEAD rule, return same output as for original command, ignoring other commands in the rule.If there is INSTEAD rules, use result of last command in the rewritten series, #2a use result of last command of same type as original command #2b or sum up the results of all the rewritten commands. #2c PQoidValue() == If the original command was not INSERT, return 0. otherwise, if one INSERT, return it's original PQoidValue(). If more then one INSERT command applied, use last #2A or other possibilities #2B; one of these possibilities is: return 0 (#2C). On Sun, 8 Sep 2002 21:52:45 -0400 (EDT), Bruce Momjian [EMAIL PROTECTED] wrote: :So, to summarize #2, we have: : : if no INSTEAD, : return value of original command : : if INSTEAD, : return tag of original command : return sum of all affected rows with the same tag this is a new interpretation: #2d : return OID if all INSERTs in the rule insert only one row, else zero this is #2C Proposal #3 (author: Steve Howe): - Another possibility (which does not go against the other proposals but extends them) would be returning a stack of all commands executed and returning it on new functions, whose extend the primary's functionality; let's say these new functions are called PQcmdStatusEx(), PQcmdTuplesEx() and PQoidValueEx(). Proposal #4 (author: Hiroshi Inoue): Hiroshi's proposal consist in a makeshift solution as stated on http://archives.postgresql.org/pgsql-general/2002-05/msg00170.php. Please refer to that thread for details. Proposal #5: On Sun, 08 Sep 2002 19:54:45 -0700, Joe Conway [EMAIL PROTECTED] wrote: : if no INSTEAD, : return value of original command : : if INSTEAD, : return tag MUTATED : return sum of sum of tuple counts of all replacement commands this equals #2c : return OID if sum of all replacement INSERTs in the rule inserted : only one row, else zero this is #2C On Mon, 9 Sep 2002 20:41:41 +0200 (CEST), Peter Eisentraut [EMAIL PROTECTED] wrote: :The major premise in the standard's point of view is that :views are supposed to be transparent. That is, if : :SELECT * FROM my_view WHERE condition; : :return N rows, then a subsequently executed : :UPDATE my_view SET ... WHERE condition; : :returns an update count of N, no matter what happens behind the scenes. ISTM this is one of those problems where there is no generic solution. Whatever you implement, it is easy to come up with an example that shows that the implementation is broken (for a suitable definition of broken), because there are so many different ways to use this feature. Here is just another bad idea: As it is impossible to *guess* the correct behaviour, let the dba *define* what he wants. There is no CREATE RULE statement in SQL92, so we can't break any standard by changing its syntax. CREATE [ OR REPLACE ] RULE name AS ON event TO table [ WHERE condition ] DO [ INSTEAD ] action where action can be: NOTHING | rulequery | ( rulequery; rulequery ... ) where rulequery is: [ COUNT ] query (or any other keyword instead of COUNT) Proposal #6: If no INSTEAD, return value of original command (this is compatible to #2), else ... PQcmdStatus() == Always return tag of original command (this equals #2). PQcmdTuples() == Sum up the results of all the rewritten commands marked as COUNTed. PQoidValue() == If the original command was not INSERT, return 0. otherwise, if all COUNTed rewritten INSERTs insert exactly one row, then return its OID, else 0. Proposal #7 (a variation of #6): If no INSTEAD, treat the original command the same as a COUNTed rewritten command. +/- for both #6 and #7 Pro: Regarding PQcmdTuples this can emulate #1 and all variants of #2. Con: need to store COUNTed flag for rule queries == catalog change == initdb == not for 7.3 (except we can
Re: [HACKERS] Proposal: Solving the Return proper effected tuple
Here are the proposals for solutioning the Return proper effected tuple count from complex commands [return] issue as seen on TODO. Any comments ?... This is obviously open to voting and discussion. We don't have a whole lot of freedom in this; this area is covered by the SQL standard. The major premise in the standard's point of view is that views are supposed to be transparent. That is, if SELECT * FROM my_view WHERE condition; return N rows, then a subsequently executed UPDATE my_view SET ... WHERE condition; returns an update count of N, no matter what happens behind the scenes. I don't think this matches Tom Lane's view exactly, but it's a lot closer than your proposal. Yes, exactly. I think it does match Tom's proposal as best we can. But we need a knowing dba that creates correct rules. Since you can create a lot more powerful views in pg than usual, I guess that is not such a farfetched demand. I do not know whether above extends to inserts ? In Informix you can create views WITH CHECK OPTION, then inserted and updated rows are guaranteed to still be visible by the view. If you don't add that clause, inserts and updates may produce rows that are not visible through the view. The affected row count still includes those though. Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Proposal: Solving the Return proper effected tuple
Oh, this is bad news. The problem we have is that rules don't distinguish the UPDATE on the underlying tables of the rule from other updates that may appear in the query. If we go with Tom's idea and total just UPDATE's, we will get the right answer when there is only one UPDATE in the ruleset. As long as the rules don't overlap (1 row is handled by 1 instead statement, another row by a different one), it is ok. Again, you can create non instead rules or triggers for the other work needed. I am still in favor of not distinguishing the different tags. The dba needs to take responsibility anyway (as long as we don't autogenerate the rules for simple cases). Andreas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal: Solving the Return proper effected tuple count
I don't think we should add tuple counts from different commands, i.e. adding UPDATE and DELETE counts just yields a totally meaningless number. Agreed. I don't think there is any need/desire to add additional API routines to handle multiple return values. Yup. Can I get some votes on this? I vote for Tom's proposal, especially regarding non instead rules (a note to Steve: non instead rules are not for views). I also think summing up is good, it would nicely fit the partitioned table requirements. And even if you imagine an insert statement with one row, even though I would be quite surprised if I got 3 rows inserted as an answer, I think it is the dba's responsibility to do the 2nd and 3rd row with a non instead rule or a trigger. For the same reason I would not restrict the count to one tag (do what you don't want in the count with a non instead rule or a trigger). I would vote for OID from first or last command. And I would disregarding the tag, since that gives me the power to transparently move an updated table to a history keeping table, that only does inserts. Whether first or last result is probably not so important, since the rule creator can influence what is done first/last, no ? You'd only need to know which. Andreas ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Proposal: Solving the Return proper effected tuple
Steve Howe writes: Here are the proposals for solutioning the Return proper effected tuple count from complex commands [return] issue as seen on TODO. Any comments ?... This is obviously open to voting and discussion. We don't have a whole lot of freedom in this; this area is covered by the SQL standard. The major premise in the standard's point of view is that views are supposed to be transparent. That is, if SELECT * FROM my_view WHERE condition; return N rows, then a subsequently executed UPDATE my_view SET ... WHERE condition; returns an update count of N, no matter what happens behind the scenes. I don't think this matches Tom Lane's view exactly, but it's a lot closer than your proposal. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Proposal: Solving the Return proper effected tuple count from complex commands [return] issue
Hello Peter, Monday, September 9, 2002, 3:41:41 PM, you wrote: PE Steve Howe writes: Here are the proposals for solutioning the Return proper effected tuple count from complex commands [return] issue as seen on TODO. Any comments ?... This is obviously open to voting and discussion. PE We don't have a whole lot of freedom in this; this area is covered by the PE SQL standard. The major premise in the standard's point of view is that PE views are supposed to be transparent. That is, if PE SELECT * FROM my_view WHERE condition; PE return N rows, then a subsequently executed PE UPDATE my_view SET ... WHERE condition; PE returns an update count of N, no matter what happens behind the scenes. I PE don't think this matches Tom Lane's view exactly, but it's a lot closer PE than your proposal. If there was a single statement per rules executed, this would be end of discussion... but as you know there can be possible multiple statements per rules, and the difficulty is what do to in those cases. As far as of now, Tom Lane's proposal seems to be the most accepted, without using a new API. - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Proposal: Solving the Return proper effected tuple
Peter Eisentraut wrote: Steve Howe writes: Here are the proposals for solutioning the Return proper effected tuple count from complex commands [return] issue as seen on TODO. Any comments ?... This is obviously open to voting and discussion. We don't have a whole lot of freedom in this; this area is covered by the SQL standard. The major premise in the standard's point of view is that views are supposed to be transparent. That is, if SELECT * FROM my_view WHERE condition; return N rows, then a subsequently executed UPDATE my_view SET ... WHERE condition; returns an update count of N, no matter what happens behind the scenes. I don't think this matches Tom Lane's view exactly, but it's a lot closer than your proposal. Oh, this is bad news. The problem we have is that rules don't distinguish the UPDATE on the underlying tables of the rule from other updates that may appear in the query. If we go with Tom's idea and total just UPDATE's, we will get the right answer when there is only one UPDATE in the ruleset. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Proposal: Solving the Return proper effected tuple count
Hello Bruce, Monday, September 9, 2002, 12:16:32 AM, you wrote: BM Joe Conway wrote: BM Any suggestion on how to show the tag mutated? Do we want to add more BM tag possibilities? Again, I don't agree with PQcmdStatus() returning a pseudo-keyword, since I would expect a SQL command executed. I prefer Tom's suggestion of returning the same kind of command executed, or the last command as of Proposal #1. I don't think we should add tuple counts from different commands, i.e. adding UPDATE and DELETE counts just yeilds a totally meaningless number. I don't know about that. The number of rows affected is indeed this number. It's just that they were not all affected in the same way. BM Yes, that is true. The problem is that a DELETE returning a value of 10 BM may have deleted only one row and updated another 9 rows. In such BM cases, returning 1 is better. Of course, if there are multiple deletes BM then perhaps the total is better, but then again, there is no way to BM flag this so we have to do one or the other consistently. BM BM The real problem which you outline is that suppose the delete does _no_ BM deletes but only inserts. In my plan, we would return zero while in BM yours you would return the rows updated. You have a good point here, Bruce. And for avoiding it, maybe Tom's suggestion is the best. Unless the new API as of Proposal #3 is introduced. BM In my view, if you return a delete tag, you better only count deletes. Yes, this is Tom's Proposal and it makes more sense when you imagine a case situation. Proposal #1 tried to be more compatible with the behavior of multiple commands execution but that would lead us to bad situations like Bruce exposes here. BM Also, your total affected isn't going to work well with INSERT because BM we could return a non-1 for rows affected and still return an OID, which BM would be quite confusing. I did the total only matching tags because it BM does mesh with the INSERT behavior. Even if this is 100% true, I'm afraid the only way to cover all specific situations is the new API. Let's remember it's easy to implement, and could server to both multiple commands execution *and* this rules situation. I don't think there is any need/desire to add additional API routines to handle multiple return values. Agreed. BM Yep. OK, this counts two points against the new API :) - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Proposal: Solving the Return proper effected tuple count from complex commands [return] issue
Hello Bruce, Monday, September 9, 2002, 12:21:11 AM, you wrote: BM Steve Howe wrote: Hello Bruce, But this *is* the total number of rows affected. There is no current (defined) behavior of rows affected by the same kind of command issued, although I agree it makes some sense. BM Yes, that is a good point, i.e. rows effected. However, see my previous BM email on how this doesn't play with with INSERT. I agree with your point. In fact, since everybody until now seems to agree that the last command behavior isn't consistent, I think Tom's suggestion is the best. BM We don't like to add complexity if we can help it. I understand. If we can reach an agreement on another way, that's ok for me... We still have to hear the other developers about this, but for a while, my votes go to Proposal's #2 (by Tom) and Proposal #3 if enough people consider it important. - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Proposal: Solving the Return proper effected tuple
Bruce Momjian wrote: Joe Conway wrote: This is basically Tom's proposal, but substituting MUTATED for the original command tag name acknowledges that the original command was not executed unchanged. It also serves as a warning that the affected tuple count is from one or more substitute operations, not the original command. Any suggestion on how to show the tag mutated? Do we want to add more tag possibilities? The suggestion was made based on what I think is the desired behavior, but I must admit I have no idea how it would be implemented at this point. It may turn out to be more pain than it's worth. I don't know about that. The number of rows affected is indeed this number. It's just that they were not all affected in the same way. Yes, that is true. The problem is that a DELETE returning a value of 10 may have deleted only one row and updated another 9 rows. In such cases, returning 1 is better. Of course, if there are multiple deletes then perhaps the total is better, but then again, there is no way to flag this so we have to do one or the other consistently. The real problem which you outline is that suppose the delete does _no_ deletes but only inserts. In my plan, we would return zero while in yours you would return the rows updated. In my view, if you return a delete tag, you better only count deletes. Also, your total affected isn't going to work well with INSERT because we could return a non-1 for rows affected and still return an OID, which would be quite confusing. I did the total only matching tags because it does mesh with the INSERT behavior. Sure, but that's why I am in favor of changing the tag. If you did: DELETE FROM fooview WHERE name LIKE 'Joe%'; and got: MUTATED 507324 3 it would mean that 3 tuples in total were affected by all of the substitute operations, only of of them being an INSERT, and the Oid of the lone INSERT was 507324. If instead I got: DELETE 0 I'd be back to having no useful information. Did any rows in fooview match the criteria LIKE 'Joe%'? Did any data in my database get altered? Can't tell from this. Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposal: Solving the Return proper effected tuple
Steve Howe wrote: We still have to hear the other developers about this, but for a while, my votes go to Proposal's #2 (by Tom) and Proposal #3 if enough people consider it important. I think Tom and Hirosh were the people most involved in the previous discussion. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Proposal: Solving the Return proper effected tuple count
Joe Conway wrote: Sure, but that's why I am in favor of changing the tag. If you did: DELETE FROM fooview WHERE name LIKE 'Joe%'; and got: MUTATED 507324 3 it would mean that 3 tuples in total were affected by all of the substitute operations, only of of them being an INSERT, and the Oid of the lone INSERT was 507324. If instead I got: DELETE 0 I'd be back to having no useful information. Did any rows in fooview match the criteria LIKE 'Joe%'? Did any data in my database get altered? Can't tell from this. OK. Do any people have INSTEAD rules where there are not commands matching the original query tag? Can anyone think of such a case being created? The only one I can think of is UPDATE implemented as separate INSERT and DELETE commands. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Proposal: Solving the Return proper effected tuple count
Hello Bruce, Monday, September 9, 2002, 12:22:26 AM, you wrote: BM Steve Howe wrote: JC return OID if sum of all replacement INSERTs in the rule inserted JConly one row, else zero I don't agree with this one since it would lead us to a meaningless information... what would be the number retrieved ? Not an OID, nor nothing. BM I don't understand this objection. I misunderstood Joe's statement into thinking we wanted to sum the OIDs for all INSERT commands applied :) Please ignore this. But now that I read it again, I would prefer having at least one OID for the last inserted row. With this info, I would be able to refresh my client dataset to reflect the new inserted rows. I see returning 0 if multiple INSERT commands issued is as weird as returning some OID if multiple INSERT commands issued. But the second options is usable, while the first one is useless... So I would prefer retrieving the last inserted OID. - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal: Solving the Return proper effected tuple
Steve Howe wrote: Hello Bruce, Monday, September 9, 2002, 12:22:26 AM, you wrote: BM Steve Howe wrote: JC return OID if sum of all replacement INSERTs in the rule inserted JConly one row, else zero I don't agree with this one since it would lead us to a meaningless information... what would be the number retrieved ? Not an OID, nor nothing. BM I don't understand this objection. I misunderstood Joe's statement into thinking we wanted to sum the OIDs for all INSERT commands applied :) Please ignore this. But now that I read it again, I would prefer having at least one OID for the last inserted row. With this info, I would be able to refresh my client dataset to reflect the new inserted rows. I see returning 0 if multiple INSERT commands issued is as weird as returning some OID if multiple INSERT commands issued. But the second options is usable, while the first one is useless... So I would prefer retrieving the last inserted OID. We would return 0 for oid and an insert count, just like INSERT INTO ... SELECT. How is that weird? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal: Solving the Return proper effected tuple count
Hello Bruce, Monday, September 9, 2002, 12:36:38 AM, you wrote: BM Joe Conway wrote: Sure, but that's why I am in favor of changing the tag. If you did: DELETE FROM fooview WHERE name LIKE 'Joe%'; and got: MUTATED 507324 3 it would mean that 3 tuples in total were affected by all of the substitute operations, only of of them being an INSERT, and the Oid of the lone INSERT was 507324. If instead I got: DELETE 0 I'd be back to having no useful information. Did any rows in fooview match the criteria LIKE 'Joe%'? Did any data in my database get altered? Can't tell from this. BM OK. Do any people have INSTEAD rules where there are not commands BM matching the original query tag? Can anyone think of such a case being BM created? I can think a thousand cases. For instance, one could create an update rule that would delete rows referenced on a second table (to avoid orphan rows). OR a user could make an insert rule that empties a table with DELETE so that only one row can always be assumed in that table... the possibilities are infinite. BM The only one I can think of is UPDATE implemented as separate INSERT and BM DELETE commands. I'm afraid the great imagination of PostgreSQL users has come to all kind of uses and misuses for such a powerful feature :) - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Proposal: Solving the Return proper effected tuple
Bruce Momjian wrote: OK. Do any people have INSTEAD rules where there are not commands matching the original query tag? Can anyone think of such a case being created? The only one I can think of is UPDATE implemented as separate INSERT and DELETE commands. I could see an UPDATE implemented as an UPDATE and an INSERT. You would UPDATE the original row to mark it as dead (e.g. change END_DATE from NULL to CURRENT_DATE), and INSERT a new row to represent the new state. This is pretty common in business systems where you need complete transaction history, and never update in place over critical data. Similarly, a DELETE might be implemented as an UPDATE for the same reason (mark it dead, but keep the data). In fact, the view itself might screen out the dead rows using the field which was UPDATED. Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal: Solving the Return proper effected tuple count
Hello Bruce, Monday, September 9, 2002, 12:39:20 AM, you wrote: BM I don't understand this objection. I misunderstood Joe's statement into thinking we wanted to sum the OIDs for all INSERT commands applied :) Please ignore this. But now that I read it again, I would prefer having at least one OID for the last inserted row. With this info, I would be able to refresh my client dataset to reflect the new inserted rows. I see returning 0 if multiple INSERT commands issued is as weird as returning some OID if multiple INSERT commands issued. But the second options is usable, while the first one is useless... So I would prefer retrieving the last inserted OID. BM We would return 0 for oid and an insert count, just like INSERT INTO ... BM SELECT. How is that weird? It's not weird, or as weird as the other proposal which is retrieving the last inserted OID number. If we can return some information for the client, why not doing it ? :-) - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal: Solving the Return proper effected tuple count
Steve Howe wrote: BM We would return 0 for oid and an insert count, just like INSERT INTO ... BM SELECT. How is that weird? It's not weird, or as weird as the other proposal which is retrieving the last inserted OID number. If we can return some information for the client, why not doing it ? :-) Well, we don't return an OID from a random row when we do INSERT INTO ... SELECT (and no one has complained about it) so I can't see why we would return an OID there. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposal: Solving the Return proper effected tuple
On Sun, 8 Sep 2002, Steve Howe wrote: Here are the proposals for solutioning the Return proper effected tuple count from complex commands [return] issue as seen on TODO. Any comments ?... This is obviously open to voting and discussion. As it seems we're voting, I think Tom's scheme is about as good as we'll do for the current API. I actually think that a better API is a good idea, but it's an API change and we're in beta, so not for 7.3. I'm not 100% sure which of the PQcmdTuples behaviors makes sense (actually I'm pretty sure neither do, but since the general complaint is knowing whether something happened or not, sum gets around the last statement doing 0 rows and running into the same type of problem). Proposal #2 (author: Tom lane): - Tom Lane's proposal, as posted on http://candle.pha.pa.us/mhonarc/todo.detail/return/msg00012.html, consists basically on the following: PQcmdStatus() == Should always return the same command type original submitted by the client. PQcmdTuples() == If no INSTEAD rule, return same output as for original command, ignoring other commands in the rule.If there is INSTEAD rules, use result of last command in the rewritten series, use result of last command of same type as original command or sum up the results of all the rewritten commands. (I particularly prefer the sum). PQoidValue() == If the original command was not INSERT, return 0. otherwise, if one INSERT, return it's original PQoidValue(). If more then one INSERT command applied, use last or other possibilities (please refer to the thread for details). Please refer to the original post to refer to the original message. I would like to point out that it was the most consistent proposal pointed out until now on the previous discussions (Bruce M. agrees with this one). ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Proposal: Solving the Return proper effected tuple count from complex commands [return] issue
Hello all, Here are the proposals for solutioning the Return proper effected tuple count from complex commands [return] issue as seen on TODO. Any comments ?... This is obviously open to voting and discussion. -- Best regards, Steve Howe mailto:[EMAIL PROTECTED] - Introduction These are three proposals to give a solution on the issue: * Return proper effected tuple count from complex commands [return] ... as seen on TODO http://developer.postgresql.org/todo.php as of 09 Sep 2002. Affect Versions: PostgreSQL v7.2X PostgreSQL pre 7.2 versions has inconsistent behavior as stated below. References -- The main thread discussion is listed in (1): http://momjian.postgresql.org/cgi-bin/pgtodo?return Some previous discussion started on (2): http://archives.postgresql.org/pgsql-general/2002-05/msg00096.php The topic was revisited by Steve Howe in the thread (3): http://archives.postgresql.org/pgsql-hackers/2002-09/msg00429.php Problem Description: PQcmdStatus(), PQcmdTuples() and PQoidValue() do not work properly on rules, most notably updating views. An additional layer of problems can arise if user issues multiple commands per rule, as of what should be the output of those functions in that situation. Specially problematic is PQcmdTuples(), which will return 0, confusing client applications into thinking nothing was updated and even breaking some applications. The pre-version 7.2 behavior is not acceptable as stated by Tom Lane on the threads above. An urgent fix is demanded to allow applications using rules to work properly and allow clients to retrieve proper execution information. Proposal #1 (author: Steve Howe): - As stated in the threads above (from the [References] topic), we have 3 tags to worry about, returned by the following functions: PQcmdStatus() - command status string PQcmdTuples() - number of rows updated PQoidValue() - last inserted OID My proposal consists basically on having the same behavior of when multiple commands per execution string are executed currently (except for PQcmdTuples()) : PQcmdStatus() == Should return the last executed command or the same as the original command (I prefer the second way, but the first is more intuitive on a multiple execution case, as I'll explain below). PQcmdTuples() == should return the sum of modified rows of all commands executed by the rule (DELETE / INSERT / UPDATE). PQoidValue() == should return the value for the last INSERT executed command in the rule (if any). Using this scheme, any SELECT commands executed would not count on PQcmdTuples(), what makes plain sense. The other commands would give a similar response to what we already have when we issue multiple commands per execution string. I would like to quote an issued pointed by Tom Lane, from one of the messages on the thread above: I'm also concerned about having an understandable definition for the OID returned for an INSERT query --- if there are additional INSERTs triggered by rules, does that mean you don't get to see the OID assigned to the single row you tried to insert? In this case, the user has to be aware that if he issued multiple commands, he will get the result for only the last one. This is is the same behavior of multiple commands when you execute: db# insert into MyTable values(1 ,1); insert into MyTable values(2 ,2); INSERT 93345 1 INSERT 93346 1 Of course this could lead to have a PQcmdStatus() return value greater then the number of rows viewable by the rule, but I think that's perfectly understandable if there are multiple commands involved and the client application programmer should be aware of that. PQoidStatus() will return the OID only for the last command, so (again) the proposed behavior is compatible on what already happens when you issue multiple commands. So if the user issues some insert commands but The proposed behavior would be the same for DO and DO INSTEAD rules unless someone points out some flaw. Proposal #2 (author: Tom lane): - Tom Lane's proposal, as posted on http://candle.pha.pa.us/mhonarc/todo.detail/return/msg00012.html, consists basically on the following: PQcmdStatus() == Should always return the same command type original submitted by the client. PQcmdTuples() == If no INSTEAD rule, return same output as for original command, ignoring other commands in the rule.If there is INSTEAD rules, use result of last command in the rewritten series, use result of last command of same type as original command or sum up the results of all the rewritten commands.
Re: [HACKERS] Proposal: Solving the Return proper effected tuple count
I liked option #2. I don't think the _last_ query in a rule should have any special handling. So, to summarize #2, we have: if no INSTEAD, return value of original command if INSTEAD, return tag of original command return sum of all affected rows with the same tag return OID if all INSERTs in the rule insert only one row, else zero This INSERT behavior seems consistent with INSERTs inserting multiple rows via INSERT INTO ... SELECT: test= create table x (y int); inseCREATE TABLE test= insert into x select 1; INSERT 507324 1 ^^ test= insert into x select 1 union select 2; INSERT 0 2 ^ I don't think we should add tuple counts from different commands, i.e. adding UPDATE and DELETE counts just yeilds a totally meaningless number. I don't think there is any need/desire to add additional API routines to handle multiple return values. Can I get some votes on this? We have one user very determined to get a fix, and the TODO.detail file has another user who really wants a fix. --- Proposal #2 (author: Tom lane): - Tom Lane's proposal, as posted on http://candle.pha.pa.us/mhonarc/todo.detail/return/msg00012.html, consists basically on the following: PQcmdStatus() == Should always return the same command type original submitted by the client. PQcmdTuples() == If no INSTEAD rule, return same output as for original command, ignoring other commands in the rule.If there is INSTEAD rules, use result of last command in the rewritten series, use result of last command of same type as original command or sum up the results of all the rewritten commands. (I particularly prefer the sum). PQoidValue() == If the original command was not INSERT, return 0. otherwise, if one INSERT, return it's original PQoidValue(). If more then one INSERT command applied, use last or other possibilities (please refer to the thread for details). -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal: Solving the Return proper effected tuple
Bruce Momjian wrote: I liked option #2. I don't think the _last_ query in a rule should have any special handling. So, to summarize #2, we have: if no INSTEAD, return value of original command if INSTEAD, return tag of original command return sum of all affected rows with the same tag return OID if all INSERTs in the rule insert only one row, else zero How about: if no INSTEAD, return value of original command if INSTEAD, return tag MUTATED return sum of sum of tuple counts of all replacement commands return OID if sum of all replacement INSERTs in the rule inserted only one row, else zero This is basically Tom's proposal, but substituting MUTATED for the original command tag name acknowledges that the original command was not executed unchanged. It also serves as a warning that the affected tuple count is from one or more substitute operations, not the original command. I don't think we should add tuple counts from different commands, i.e. adding UPDATE and DELETE counts just yeilds a totally meaningless number. I don't know about that. The number of rows affected is indeed this number. It's just that they were not all affected in the same way. I don't think there is any need/desire to add additional API routines to handle multiple return values. Agreed. Can I get some votes on this? We have one user very determined to get a fix, and the TODO.detail file has another user who really wants a fix. +1 for the version above ;-) Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal: Solving the Return proper effected tuple count from complex commands [return] issue
Hello Bruce, Sunday, September 8, 2002, 10:52:45 PM, you wrote: BM I liked option #2. I don't think the _last_ query in a rule should have BM any special handling. BM So, to summarize #2, we have: BM if no INSTEAD, BM return value of original command The problem is, this would lead us to the same behavior of Proposal #1 (returning the value for the last command executed), which you didn't like... BM if INSTEAD, BM return tag of original command BM return sum of all affected rows with the same tag BM return OID if all INSERTs in the rule insert only one row, else zero BM This INSERT behavior seems consistent with INSERTs inserting multiple BM rows via INSERT INTO ... SELECT: BM test= create table x (y int); BM inseCREATE TABLE BM test= insert into x select 1; BM INSERT 507324 1 BM^^ BM test= insert into x select 1 union select 2; BM INSERT 0 2 BM^ BM I don't think we should add tuple counts from different commands, i.e. BM adding UPDATE and DELETE counts just yeilds a totally meaningless BM number. But this *is* the total number of rows affected. There is no current (defined) behavior of rows affected by the same kind of command issued, although I agree it makes some sense. BM I don't think there is any need/desire to add additional API routines to BM handle multiple return values. I'm ok with that if we can reach an agreement on how the existing API should work. But as I stated, a new API would be a no-discussion way to solve this, and preferably extending some of the other proposals. BM Can I get some votes on this? We have one user very determined to get a BM fix, and the TODO.detail file has another user who really wants a fix. *Please* let's do it :) Thanks. - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Proposal: Solving the Return proper effected tuple count
Joe Conway wrote: Bruce Momjian wrote: I liked option #2. I don't think the _last_ query in a rule should have any special handling. So, to summarize #2, we have: if no INSTEAD, return value of original command if INSTEAD, return tag of original command return sum of all affected rows with the same tag return OID if all INSERTs in the rule insert only one row, else zero How about: if no INSTEAD, return value of original command if INSTEAD, return tag MUTATED return sum of sum of tuple counts of all replacement commands return OID if sum of all replacement INSERTs in the rule inserted only one row, else zero This is basically Tom's proposal, but substituting MUTATED for the original command tag name acknowledges that the original command was not executed unchanged. It also serves as a warning that the affected tuple count is from one or more substitute operations, not the original command. Any suggestion on how to show the tag mutated? Do we want to add more tag possibilities? I don't think we should add tuple counts from different commands, i.e. adding UPDATE and DELETE counts just yeilds a totally meaningless number. I don't know about that. The number of rows affected is indeed this number. It's just that they were not all affected in the same way. Yes, that is true. The problem is that a DELETE returning a value of 10 may have deleted only one row and updated another 9 rows. In such cases, returning 1 is better. Of course, if there are multiple deletes then perhaps the total is better, but then again, there is no way to flag this so we have to do one or the other consistently. The real problem which you outline is that suppose the delete does _no_ deletes but only inserts. In my plan, we would return zero while in yours you would return the rows updated. In my view, if you return a delete tag, you better only count deletes. Also, your total affected isn't going to work well with INSERT because we could return a non-1 for rows affected and still return an OID, which would be quite confusing. I did the total only matching tags because it does mesh with the INSERT behavior. I don't think there is any need/desire to add additional API routines to handle multiple return values. Agreed. Yep. Can I get some votes on this? We have one user very determined to get a fix, and the TODO.detail file has another user who really wants a fix. +1 for the version above ;-) OK, we are getting closer. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Proposal: Solving the Return proper effected tuple count
Hello Joe, Sunday, September 8, 2002, 11:54:45 PM, you wrote: JC Bruce Momjian wrote: I liked option #2. I don't think the _last_ query in a rule should have any special handling. So, to summarize #2, we have: if no INSTEAD, return value of original command if INSTEAD, return tag of original command return sum of all affected rows with the same tag return OID if all INSERTs in the rule insert only one row, else zero JC How about: JC if no INSTEAD, JC return value of original command JC if INSTEAD, JC return tag MUTATED I see PQcmdStatus() returning a SQL command and not a pseudo-keyword, so I don't agree with this tag. JC return sum of sum of tuple counts of all replacement commands Agreed. JC return OID if sum of all replacement INSERTs in the rule inserted JConly one row, else zero I don't agree with this one since it would lead us to a meaningless information... what would be the number retrieved ? Not an OID, nor nothing. JC I don't know about that. The number of rows affected is indeed this JC number. It's just that they were not all affected in the same way. Agreed too... JC +1 for the version above ;-) Which ? Yours or Tom's ? :) - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposal: Solving the Return proper effected tuple
Steve Howe wrote: Hello Bruce, Sunday, September 8, 2002, 10:52:45 PM, you wrote: BM I liked option #2. I don't think the _last_ query in a rule should have BM any special handling. BM So, to summarize #2, we have: BM if no INSTEAD, BM return value of original command The problem is, this would lead us to the same behavior of Proposal #1 (returning the value for the last command executed), which you didn't like... I don't like treating the last command as special when there is more than one command. Of course, if there is only no INSTEAD, the main statement is the only one we care about returning information for. BM if INSTEAD, BM return tag of original command BM return sum of all affected rows with the same tag BM return OID if all INSERTs in the rule insert only one row, else zero BM This INSERT behavior seems consistent with INSERTs inserting multiple BM rows via INSERT INTO ... SELECT: BM test= create table x (y int); BM inseCREATE TABLE BM test= insert into x select 1; BM INSERT 507324 1 BM^^ BM test= insert into x select 1 union select 2; BM INSERT 0 2 BM^ BM I don't think we should add tuple counts from different commands, i.e. BM adding UPDATE and DELETE counts just yeilds a totally meaningless BM number. But this *is* the total number of rows affected. There is no current (defined) behavior of rows affected by the same kind of command issued, although I agree it makes some sense. Yes, that is a good point, i.e. rows effected. However, see my previous email on how this doesn't play with with INSERT. BM I don't think there is any need/desire to add additional API routines to BM handle multiple return values. I'm ok with that if we can reach an agreement on how the existing API should work. But as I stated, a new API would be a no-discussion way to solve this, and preferably extending some of the other proposals. We don't like to add complexity if we can help it. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster