Rule on table with DO ALSO increments sequences twice

From: Tim Goddard <tim(at)goddard(dot)nz>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Rule on table with DO ALSO increments sequences twice
Date: 2026-06-21 10:06:01
Message-ID: CAEgDWE4jizpYEazW2PsOga3rq59C7B3DdCLRn_KJCGhYS1A31w@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hello,

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

Attachment Content-Type Size
sequence-double-increment-testcase.sql application/sql 299 bytes
postgresql.conf application/octet-stream 32.0 KB

Browse pgsql-bugs by date

  From Date Subject
Next Message carl clemens 2026-06-21 17:38:25 psql variable substitution in plpgsql loop
Previous Message Laurenz Albe 2026-06-21 05:47:33 Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table