Re: Queries using rules show no rows modified?

On Fri, 10 May 2002 10:51:05 -0400, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
wrote:
>Thoughts, different proposals, alternative ways of breaking down
>the problem?
Well, you asked for it, so here is my wishlist :-)

From a user POV I expect a command to return the number of "rows" it
has processed successfully. By "rows" I mean rows of the table (or
view or whatever) my command (seemingly) handles, I'd not be
interested in any side effects my command has because of triggers
and/or rules.

Suppose there is a user called Al B. If, for example, his DB designer
gives him a table foo (id int, name text) to store his data, he may
consider this table as a black box. Al does not want to (and probably
even should not) know about rules and triggers. So when he enters
INSERT INTO foo VALUES (10, 'ten');
he expects to get
INSERT nnn 1
or an error message. He doesn't care for any INSERTs into changelogs
or UPDATEs to accounting data, he just wants to know whether *his*
INSERT was successful.

Next, if Al enters
INSERT INTO foo SELECT ... FROM bar WHERE ...
and the SELECT statement returns 47 rows, he expects
INSERT 0 47
if there is no problem.

UPDATE foo ... WHERE ...
Here the WHERE clause identifies a certain number of rows which are to
be updated. Again this number should be returned as the tuple count.
Same for DELETE.

>A. If original command is executed (no INSTEAD), return its tag as-is,
>regardless of commands added by rules.
Yes, please. This is fully compatible with my wishes.

>B. If original command is not executed, then return its tag name
Agreed.

>plus required fields defined as follows: tuple count is sum of tuple
>counts of all replacement commands.
No, please don't care about replacement commands. If a rule can be
viewed as something that is executed "for each row", then simply let
"each row" that is processed successfully contribute 1 to the tuple
count. (Well, I know, this is not always easy. I guess it's easier
for INSERT and harder for UPDATE and DELETE. But isn't it a nice
goal?)

While I'm fairly sure about my preferences up to here, there are some
points I don't have a strong opinion on:

OIDs: With an ordinary table the OID returned by INSERT can be used
to retrieve the new row with SELECT ... WHERE oid=nnn. Ideally this
would hold for tables and views with rules, but there is no easy way
for the backend to know the correct OID, when there are more than 1
INSERT statements in the rule. So here's one more idea for your
sub-case 2c: Let the programmer specify which OID to return, maybe by
an extension to the INSERT syntax, allowed only in rules:
INSERT INTO ... VALUES (...) RETURNING OID ???

DO INSTEAD NOTHING: Should this be considered successful execution or
should it contribute 0 to the tuple count? I don't know which one is
less surprising. I tend to the latter.

Just my 0.02.
Servus
Manfred