Re: PostgreSQL Rule does not work with deferred constraint.

Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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