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

-------------------------------------------------------------------------