Returning from a rule with extended query protocol
Hi,
I was testing creating a rule that uses RETURNING and noticed a difference
between the extended query protocol and the simple query protocol. In the
former, RETURNING is ignored (at least in my case) and the latter it is
respected:
CREATE table test (id bigint, deleted boolean);
CREATE RULE soft_delete AS ON DELETE TO test DO INSTEAD (UPDATE test set
deleted = true WHERE id = old.id RETURNING old.*);
INSERT INTO test values (1, false);
# extended protocol result
postgres=# DELETE FROM test WHERE id = $1 RETURNING * \bind 1 \g
DELETE 0
# simple protocol result
postgres=# DELETE FROM test WHERE id = 1 RETURNING *;
id | deleted
----+---------
1 | t
(1 row)
DELETE 0
I was wondering if this is something that is just fundamentally not
expected to work or if it might be able to work without jeopardizing
critical parts of Postgres. If the latter I was interested in digging
through the code and seeing if I could figure it out.
Note that I work on a driver/client for Postgres and the example above came
from a user. I'm not sure if it's the best way to do what they want but
their question sparked my interest in the general behaviour of returning
from rules with the extended query protocol.
Thanks
On Sun, 11 Aug 2024 at 13:29, Greg Rychlewski <greg.rychlewski@gmail.com> wrote:
I was testing creating a rule that uses RETURNING and noticed a difference between the extended query protocol and the simple query protocol. In the former, RETURNING is ignored (at least in my case) and the latter it is respected:
That seems like a bug to me. The simple and extended protocol should
return the same data for the same query. I'm guessing CREATE RULE
isn't often enough for this difference to be noticed earlier. So yeah
please dig through the code and submit a patch to fix this.
Greg Rychlewski <greg.rychlewski@gmail.com> writes:
I was testing creating a rule that uses RETURNING and noticed a difference
between the extended query protocol and the simple query protocol. In the
former, RETURNING is ignored (at least in my case) and the latter it is
respected:
I think this might be the same issue recently discussed here:
/messages/by-id/1df84daa-7d0d-e8cc-4762-85523e45e5e7@mailbox.org
That discussion was leaning towards the idea that the cost-benefit
of fixing this isn't attractive and we should just document the
discrepancy. However, with two reports now, maybe we should rethink.
regards, tom lane
On Sun, 11 Aug 2024 at 23:54, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I think this might be the same issue recently discussed here:
/messages/by-id/1df84daa-7d0d-e8cc-4762-85523e45e5e7@mailbox.org
Yeah that's definitely the same issue.
That discussion was leaning towards the idea that the cost-benefit
of fixing this isn't attractive and we should just document the
discrepancy. However, with two reports now, maybe we should rethink.
I think it's interesting that both reports use rules in the same way,
i.e. to implement soft-deletes. That indeed seems like a pretty good
usecase for them. And since pretty much every serious client library
uses the extended query protocol, this breaks that usecase. But if
it's hard to fix, I'm indeed not sure if it's worth the effort. If we
don't we should definitely document it though, at CREATE RULE and in
the protocol spec.