Re: Proposal: Solving the "Return proper effected tuple

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(at)carcass(dot)dhs(dot)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(at)candle(dot)pha(dot)pa(dot)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 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

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(at)carcass(dot)dhs(dot)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 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
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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
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(at)carcass(dot)dhs(dot)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 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
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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.

I don't understand this objection.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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:howe(at)carcass(dot)dhs(dot)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(at)carcass(dot)dhs(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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.

-------------
Best regards,
Steve Howe mailto:howe(at)carcass(dot)dhs(dot)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(at)candle(dot)pha(dot)pa(dot)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:
> 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(at)carcass(dot)dhs(dot)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(at)carcass(dot)dhs(dot)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(at)candle(dot)pha(dot)pa(dot)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).

> 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).

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 peter_e(at)gmx(dot)net

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:howe(at)carcass(dot)dhs(dot)org

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
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Sorry guys - it's killing me! It's 'affected' in the subject line - not
'effected'!!! Sigh :)

Chris

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org]On Behalf Of Bruce Momjian
> Sent: Tuesday, 10 September 2002 10:24 AM
> To: Peter Eisentraut
> Cc: Steve Howe; pgsql-hackers(at)postgresql(dot)org
> Subject: 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
> pgman(at)candle(dot)pha(dot)pa(dot)us | (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 majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

Hello Christopher,

Monday, September 9, 2002, 11:36:44 PM, you wrote:

CKL> Sorry guys - it's killing me! It's 'affected' in the subject line - not
CKL> 'effected'!!! Sigh :)

lol... my bad, English is not my primary language and these things
just seem to happen sometimes... I apologize.

-------------
Best regards,
Steve Howe mailto:howe(at)carcass(dot)dhs(dot)org

On Sun, 8 Sep 2002 19:50:21 -0300, Steve Howe <howe(at)carcass(dot)dhs(dot)org>
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
<pgman(at)candle(dot)pha(dot)pa(dot)us> 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 <mail(at)joeconway(dot)com>
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
<peter_e(at)gmx(dot)net> 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 find an unused bit).

Servus
Manfred