Re: Queries using rules show no rows modified? - Mailing list pgsql-hackers

Tom Lane <tgl@sss.pgh.pa.us> writes:
> The basic argument in favor of a patch like this is that if a rule
> replaces (DO INSTEAD) a command with another command of the same
> general type, it is useful to return the tag for the replacement
> command not the original.  I agree with that.

I would argue that the argument in favor of a patch is that there's no
documentation anywhere that behavior changed, or that PQcmdTuples will
not return the expected result in the presence of rules. :-)

Is the change behaviorou propose implementable as a patch to 7.2.1?

> If the client is expecting to get back "INSERT m n" and actually
> gets back "UPDATE n", isn't that client likely to break?

Perhaps.  How many clients are checking that the string returned
matches the query it sent?

I've checked DBD::Pg, it doesn't.  I've checked psycopg, it doesn't,
though it looks like its handling of the value might be a bit bogus.
ecpg doesn't, though it looks like it might choke on an empty string.
PHP doesn't.  QT3 doesn't.  PoPY (another Python interface) doesn't.
The TCL library doesn't even look at the return, it just passes it
back, so I suppose there might be applications doing a direct look.
The python lib included with postgresql doesn't.  In fact, the idiom
is either (in pseudocode):
 if (temp = PQcmdTuples (result)) {   numTuples = atoi (temp); } else {   numTuples = some other arbitrary value; }

or:
 numTuples = atoi (PQcmdTuples (result));

So, no, my *very* unscientific and non-comprehensive survey suggests
that your fears are mostly groundless.  But I haven't seen a single
interface that *is* depending on that being correct, but many of them
return misleading results if PQcmdTuples does.

Which is, if I haven't hammered this enough, not mentioned anywhere in
the documentation. 
> Another issue is that the whole thing falls down if the rewriting
> generates more than one query; both Hiroshi's proposal and mine will
> not return any substitute tag then.  This seems rather restrictive.

If, when you say, "will not return any substitute tag then.", you mean
that, as an end result PQcmdTuple would return an empty string, well,
that seems reasonable---it keeps the DB from returning bogus info, and
an empty string returned from PQcmdTuple _is_ documented as a valid
response, and it looks like most interfaces would handle it just fine
(except maybe for ecpg, which I would argue either has a bug or I'm
not reading right).

I guess there's the argument to be made that any overly-zealous
interface that might choke on getting a different tag back might also
choke on getting no tag back.  But, again, I don't see any doing any
of this.  And they *all* seem to expect PQcmdTuples to either return
legitimate data or nothing at all.

> Maybe we could have behavior like this: if the original command is
> replaced, then use the tag from the last substituted command of the
> same class (eg, if you rewrite an UPDATE into an INSERT and an
> UPDATE, you get the tag from the UPDATE).  If there is *no*
> substitute command of the same class, I still believe that returning
> "UPDATE 0" is correct behavior.  You sent an update, zero tuples
> were updated, end of story.

As long as you document that PQcmdTuples cannot be relied on when
using rules, since the rules might change the query sufficiently to
make it unrecognizable, that's probably OK, though it'll require
significant changes to just about all interface libraries.

> Note that as of CVS tip, the firing order of rules is predictable,
> so the rule author can control which substituted command is "the
> last one".  Without this I don't think that the above would work,
> but with it, it seems like a moderately clean answer.  Moreover it's
> at least somewhat compatible with the pre-7.2.1 behavior --- where
> you got the tag from the last command *executed* regardless of any
> other considerations.  That was definitely broken.

So should I interpret these references to CVS tip as suggesting that
the fix for this change in behavior is not going to be seen until 7.3,
or just that a most-complete fix that tries to deal with multi-rule
invocations would have to wait for 7.3, but that a fix for the simpler
'do instead' case could show up in a 7.2.X release?

Because it seems to me that if we're not going to see a release with a
fix for this change in behavior, we need to make sure that maintainers
of all interfaces know that all bets are off regarding PQcmdTuples in
the (I believe undetectable) presence of rules so they'll make no
effort to use it.

Mike.