Re: Deferred constraint trigger semantics

> laurenz.albe@cybertec.atwrote: > >> b...@yugabyte.com wrote: > >> >> …I tried this: >> >> create table t( >> k serial primary key, >> v int not null, >> constraint t_v_unq unique(v) initially deferred); Here's a better test: -- BLUE session start transaction isolation level read commi

Re: Deferred constraint trigger semantics

> laurenz.a...@cybertec.at wrote: > > Be "seeing" I didn't mean "show to the user". I mean that the code that > implements PostgreSQL constraints takes uncommitted data into account. > > The documentation describes that for the case of uniqueness in some detail: > > https://www.postgresql.org/d

Re: Deferred constraint trigger semantics

On Thu, 2022-05-12 at 22:06 -0700, Bryn Llewellyn wrote: > > > In the case of constraint triggers, yes. But there is no race condition for > > primary key, > > unique and foreign key constraints, because they also "see" uncommitted > > data. > > I can't follow you here, sorry. I tried this: >

Re: Deferred constraint trigger semantics

> laurenz.a...@cybertec.at wrote: > >> b...@yugabyte.com wrote: >> >> …I have always understood that (in Postgres and any respectable RDBMS) >> commits in a multi-session environment are always strictly >> serialized—irrespective of the transaction's isolation level. Am I correct >> to assume

Re: Deferred constraint trigger semantics

> postgres.ro...@gmail.com wrote: > > It adds the "initially deferred" decoration to the "create constraint > trigger" statement. This is (still) the result: > > INFO: trg fired. new.v = 10, n = 5 > INFO: trg fired. new.v = 20, n = 5 > INFO: trg fired. new.v = 30, n = 5 > INFO: trg fired. ne

Re: Deferred constraint trigger semantics

> > > It adds the "initially deferred" decoration to the "create constraint > trigger" statement. This is (still) the result: > > INFO: trg fired. new.v = 10, n = 5 > INFO: trg fired. new.v = 20, n = 5 > INFO: trg fired. new.v = 30, n = 5 > INFO: trg fired. new.v = 40, n = 5 > INFO: trg fired.

Re: Deferred constraint trigger semantics

On Wed, 2022-05-11 at 15:54 -0700, Bryn Llewellyn wrote: > I re-read the penultimate paragraph in Laurenz's post: > > « > By making the trigger INITIALLY DEFERRED, we tell PostgreSQL to check the > condition at COMMIT time. > » > > I have always understood that (in Postgres and any respectable R

Re: Deferred constraint trigger semantics

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> Oops. I did a copy-and-paste error on going from my test env. to email and >> missed out the "deferral" that I'd intended. For completeness, here's the >> test that I meant: >> >> create constraint trigger trg >> after ins

Re: Deferred constraint trigger semantics

On Wed, May 11, 2022 at 3:43 PM Bryn Llewellyn wrote: > > Oops. I did a copy-and-paste error on going from my test env. to email and > missed out the "deferral" that I'd intended. For completeness, here's the > test that I meant: > > create constraint trigger trg > after insert on t1 > for each r

Re: Deferred constraint trigger semantics

> laurenz.a...@cybertec.at wrote: > >> b...@yugabyte.com wrote: >> >> …Internet Search turned up this 2019 post by Laurenz Albe—but nothing else >> at all. >> >> https://www.cybertec-postgresql.com/en/triggers-to-enforce-constraints >>

Re: Deferred constraint trigger semantics

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: Thanks for the point-by-point reply, David. >> ...makes no mention of what you might expect to see in an AFTER EACH ROW >> trigger. > > ...the absence of a comment declaring a guarantee of order means that, like > the comment f

Re: Deferred constraint trigger semantics

On Tue, 2022-05-10 at 17:46 -0700, Bryn Llewellyn wrote: > I looked at the sections "CREATE TRIGGER" and "Chapter 39. Triggers" in the > Current PG doc. > But I failed to find any information about the semantics of the deferred > constraint trigger > or about the use cases that motivated this fea

Re: Deferred constraint trigger semantics

On Tue, May 10, 2022 at 7:52 PM Bryn Llewellyn wrote: > > *david.g.johns...@gmail.com wrote:* > > * However, SQL commands executed in a row-level BEFORE trigger will see > the effects of data changes for rows previously processed in the same outer > command. This requires caution, since the orde

Re: Deferred constraint trigger semantics

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> INFO: trg fired. new.v = 80, n = 8 >> >> It shows the expected "new" value each time it fires. And yet the query >> reflects the table content on statement completion. This seems to be very >> strange. > > From the docum

Re: Deferred constraint trigger semantics

On Tue, May 10, 2022 at 5:47 PM Bryn Llewellyn wrote: > > INFO: trg fired. new.v = 80, n = 8 > > It shows the expected "new" value each time it fires. And yet the query > reflects the table content on statement completion. This seems to be very > strange. > >From the documentation: "Row-level