Re: Queries using rules show no rows modified?

Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)jp> writes:
> What should the backends return for complicated rewrites ?

Well, given that we have only two or three fields to work in,
it obviously has to be a very simplified view of what happened.
But we have to define *something*.

> And how should/could clients handle the results ?
> It doesn't seem easy to me and it seems a flaw of rule
> system.

No, the problem is that the command tag API was designed without any
thought for rule rewriting. But I don't think it's worth revising
that API completely. Even if we did, we'd still have to define what
behavior would be seen by clients that use the existing PQcmdTuples,
etc, calls; so we'd still have to solve these same issues.

Come on, guys, work with me a little here. I've thrown out several
alternative suggestions already, and all I've gotten from either of
you is refusal to think about the problem.

I was thinking last night that it might help to break down the issue a
little bit. We have either two or three result fields to think about:
the tag name, the tuple count, and in the case of INSERT the inserted
row OID. Let's consider each one independently.

1. The tag name: AFAICS, this ought *always* to match the type of the
original command submitted by the client. Doing otherwise could confuse
clients that are submitting multiple commands per query string.
Besides, the only possible downside from making this requirement is that
we couldn't send back an insertion OID when the original command was
an update or delete. How likely is it that a client would expect to
be able to get an insertion OID from such a command?

2. The inserted row OID: per above, will be supplied only if the
original command was an INSERT. If the original insert command is
not removed (no INSTEAD rule), then I think this result should clearly
come from the execution of the original command, regardless of any
additional INSERTs added by rules. If the original command is removed
by INSTEAD, then we can distinguish three sub-cases:
a. No INSERTs in rewriter output: easy, we must return 0.
b. Exactly one INSERT in rewriter output: pretty easy to agree that
we should return this command's result.
c: More than one INSERT in rewriter output: we have a couple of
possibilities here. It'd be reasonable to directly use the
result of the last INSERT, or we could total the results of
all the INSERTs (ie, if taken together they insert a sum total
of one row, return that row OID; else return 0). Maybe there
are other possible behaviors. Any thoughts?

3. The tuple count: this seems the most contentious issue. Again,
if there is no INSTEAD rule I'd be strongly inclined to say we
should just return the count from the original command, ignoring any
commands added by rules. If there is an INSTEAD, we've discussed
several possibilities: use result of last command in the rewritten
series, use result of last command of same type as original command,
sum up the results of all the rewritten commands, maybe some others
that I forgot.

Given Michael's concern about being able to "tell that something
happened", I'm inclined to go with the summing-up behavior in the
INSTEAD cases. This would lead to the following boiled-down behavior:

A. If original command is executed (no INSTEAD), return its tag as-is,
regardless of commands added by rules.

B. If original command is not executed, then return its tag name
plus required fields defined as follows: tuple count is sum of tuple
counts of all replacement commands. For an INSERT, if the replacement
commands taken together inserted a grand total of exactly one tuple,
return that tuple's OID; else return 0.

This is not completely consistent in pathological cases: you could get
a tuple OID returned even when the returned tuple count is greater
than one, which is not a possible case currently. (This would happen
given a rewrite consisting of a single-row INSERT plus additional
update or delete actions that affect some rows.) But that seems
pretty oddball. In all the simple cases I think this proposal gives
reasonable behavior.

A tighter definition for case B would use the sum of the tuple counts
of only the replacement actions that are of the same type as the
original command. This would eliminate the possible inconsistency
between tuple count and insert OID results, and it's arguably saner
than the above proposal: "if it says UPDATE 4, that should mean that
four rows were updated, not that something else happened to four rows".
But it would not meet Michael's concern about using PQcmdTuples to
tell that "something happened". I could live with either definition.

Thoughts, different proposals, alternative ways of breaking down
the problem?

regards, tom lane