Just reporting some unexpected behaviour that I would consider a bug, related to the evaluation of default expressions (in this case incrementing a sequnece) within a rule ON INSERT ... ALSO.
A test case is attached, which may be run in an empty database. This creates two tables, and creates a rule that on insert in to the first table "a", also inserts a record in to the second table "b" which references table "a" via a foreign key.
I expect in this rule that the ID value on the NEW row (NEW.aid) will be that of the row just inserted. However, the sequence is incremented again, resulting in a different ID to that just inserted. It would appear that the DEFAULT expression is being evaluated twice - once for the original role, and again for the rule execution. In this test case, this results in a foreign key error, since the ID being inserted is different from the row in table "a".
While this could plausibly be an intended behaviour, I've looked through the documentation, and can't find a clear indication that this should be the case, so reporting what to me is quite unexpected. It's certainly a very limiting behaviour, in that it makes it difficult to identify the row just inserted in an ALSO rule.
Version tested is recently upgraded from PGDG on Ubuntu 24.04:
PostgreSQL 18.4 (Ubuntu 18.4-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04.1) 13.3.0, 64-bit
Test case has been reproduced in a newly created cluster, with configuration as attached. The test case produces the following output:
$ psql -h /var/run/postgresql -p 5434 -f sequence-double-increment-testcase.sql
BEGIN
CREATE TABLE
CREATE TABLE
CREATE RULE
psql:dev/sequence-double-increment-testcase.sql:9: ERROR: insert or update on table "b" violates foreign key constraint "b_aid_fkey"
DETAIL: Key (aid)=(2) is not present in table "a".
I may of course be mis-reading the documentation or intention behind that, but would be good to clarify either way.
Kind regards,
Tim
