[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: PostgreSQL Rule does not work with deferred constraint.
From:       Tom Lane <tgl () sss ! pgh ! pa ! us>
Date:       2022-09-21 15:12:48
Message-ID: 1078305.1663773168 () sss ! pgh ! pa ! us
[Download RAW message or body]

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> I just tried your commands, and it works as you expect on my PostgreSQL =
v15 database.

It does fail for me, but I think it's a well-known trap rather than
a bug (or at least, it's not something that anyone wishes to redesign
the rule system to change).  The problem is that *a rule is a macro*
and therefore it's subject to multiple-evaluation hazards.  Your
volatile default expression does not play nice with that.

Initially you have:

insert into "children" values (default);

Replacement of the "default" produces:

insert into "children" values (gen_random_uuid());

Now the DO ALSO rule produces:

insert into "parent" (id) values (gen_random_uuid());

The two insertions will compute different random UUIDs,
and kaboom.

We tend to recommend using triggers not rules to implement this
sort of behavior; they are less prone to surprises.

			regards, tom lane


[prev in list] [next in list] [prev in thread] [next in thread]