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:howe@carcass.dhs.org
-------------------------------------------------------------------------
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.
(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).
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().
These "extended" functions should return the same as the original
functions, for single commands issued, but they should give more
detailed information if a complex command had been issued.
A simple examples of complex calls to those functions would return
(case situation: two inserts then a delete command which affects three
rows):
PQcmdStatusEx() ==> 'INSERT INSERT DELETE'
PQcmdTuplesEx() ==> '1 1 3'
PQoidValueEx() ==> '939494 939495 0'
The advantage of this solution is that it does not suffer from the
problems of the other solutions (namely, what return when multiple
commands are issued in a single rule).
This would imply that other "XXXXEx()" functions should have to be
made (namely PQcmdTuples() and PQoidStatus()), but it might worth the
effort because it would cover all the three tags, for all executed
commands, giving the possibility of reconstituting the whole
execution, and most importantly, without brokering existing
applications. And those functions would be very easy to code after all
(just append to the return string of those functions the value return
for a call to the original function, for each applied command). The
client application could parse those strings easily and get any info
needed for all the steps of the execution.
Still, the best situation would have original PQcmdStatus(),
PQcmdTuples(), PQoidValue() functions fixed accordingly to some of the
other Proposals, making the fix available also for existing
applications, and this proposal applied.
Another possibility still on the idea in this solution would be just
one function returning a SETOF with three columns (one for each of
those three functions), each row representing a command issued (same
stack but in another format). But I like the first solution (returning
strings for each function) better, as it would follow better the style
of the results for the existing libpq functions.
Finally, an additional, good side effect of these functions is that
they could also return the same information for another odd
situations: when multiple commands are executed on a regular command
line. Currently, only the results for the last execution string are
returned.
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.
Final Comments
--------------
I particularly would like to see Proposals #1 or #2 implemented, and
if possible Proposal #3 too.
This would provide a good solution for existing clients and a great
solution for the future for new clients.
Maybe someone wishes to combine ideas from the first and second
proposals to make a better Proposal. This would be interesting to
hear.
Of course, given the simplicity of the solutions and urgency of the
fix, I think this could well fit on a pre 7.3 release, if someone can
code it.
Finally, would like to thank Bruce Momjian for the help and support in
writing this Proposal, and hope the PostgreSQL team can reach an
agreement on what's the best solution for this issue.
-------------------------------------------------------------------------
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
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
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 commandif 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
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:howe@carcass.dhs.org
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 commandif 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 zeroHow about:
if no INSTEAD,
return value of original commandif 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 zeroThis 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
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Hello 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 commandif 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
JC> only 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:howe@carcass.dhs.org
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 zeroBM> 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
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Steve Howe wrote:
JC> return OID if sum of all replacement INSERTs in the rule inserted
JC> only 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.
I don't understand this objection.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Hello 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:howe@carcass.dhs.org
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
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:howe@carcass.dhs.org
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
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Joe 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
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Hello 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
JC> only 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:howe@carcass.dhs.org
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
JC> only 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
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Bruce 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
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:howe@carcass.dhs.org
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:howe@carcass.dhs.org
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
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
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).
Show quoted text
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).
