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: effects of nullifying bytea column on storage

On Wednesday, May 11, 2022, David Gauthier wrote: > Doesn't vacuum run automatically (or can it be set to run automatically) ? > > https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM David J.

Re: effects of nullifying bytea column on storage

Doesn't vacuum run automatically (or can it be set to run automatically) ? On Wed, May 11, 2022 at 8:05 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wednesday, May 11, 2022, David Gauthier wrote: > >> Hi: >> psql (11.5, server 11.3) on linux >> >> I have a table with a bytea c

Re: effects of nullifying bytea column on storage

On Wednesday, May 11, 2022, David Gauthier wrote: > Hi: > psql (11.5, server 11.3) on linux > > I have a table with a bytea column which, of course, contains binary > data. After 60 days, I no longer need the binary data but want to retain > the rest of the record. Of course it's easy to just u

effects of nullifying bytea column on storage

Hi: psql (11.5, server 11.3) on linux I have a table with a bytea column which, of course, contains binary data. After 60 days, I no longer need the binary data but want to retain the rest of the record. Of course it's easy to just update the bytea column to null for the older records. But I can

Re: consistent postgresql snapshot

On 5/11/22 10:41, Zwettler Markus (OIZ) wrote: PostgreSQL12 running on CentOS7 + ext4. Would it be save to do a "fsfreeze –freeze" + disk snapshot + "fsfreeze –unfreeze" of the PGDATA filesystem to get a consistent snapshot? I am wondering as PostgreSQL default blocksize = 8k while ext4 defa

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

consistent postgresql snapshot

PostgreSQL12 running on CentOS7 + ext4. Would it be save to do a "fsfreeze -freeze" + disk snapshot + "fsfreeze -unfreeze" of the PGDATA filesystem to get a consistent snapshot? I am wondering as PostgreSQL default blocksize = 8k while ext4 default blocksize = 4k, resulting in 2 fs blocks per d

Re: PLPGSQL - extra column existence in trigger

On Sat, May 07, 2022 at 07:41:44AM -0700, David G. Johnston wrote: > No. I’d probably approach this by generically converting the NEW record to > json and working with that. Non-existent object keys return null when > accessed. One note - in my tests working with hstore was significantly faster