public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tim Goddard <[email protected]>
To: [email protected]
Subject: Rule on table with DO ALSO increments sequences twice
Date: Sun, 21 Jun 2026 22:06:01 +1200
Message-ID: <CAEgDWE4jizpYEazW2PsOga3rq59C7B3DdCLRn_KJCGhYS1A31w@mail.gmail.com> (raw)

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


Attachments:

  [application/sql] sequence-double-increment-testcase.sql (299B, ../CAEgDWE4jizpYEazW2PsOga3rq59C7B3DdCLRn_KJCGhYS1A31w@mail.gmail.com/3-sequence-double-increment-testcase.sql)
  download

  [application/octet-stream] postgresql.conf (32.0K, ../CAEgDWE4jizpYEazW2PsOga3rq59C7B3DdCLRn_KJCGhYS1A31w@mail.gmail.com/4-postgresql.conf)
  download

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Rule on table with DO ALSO increments sequences twice
  In-Reply-To: <CAEgDWE4jizpYEazW2PsOga3rq59C7B3DdCLRn_KJCGhYS1A31w@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox