Re: [PERFORM] [GENERAL] trigger Before or After
avpro avpro wrote: > in the pgsql documentation > (http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html) > > > i haven't seen anything referring to: how is affected the data inserted in > the new table by a trigger > Before Insert compared with a trigger After Insert? and anything related to > performance In your example (the trigger updates a second table) it should make no difference if the trigger is BEFORE or AFTER INSERT. The difference is that in a BEFORE trigger you can modify the values that will be inserted before the INSERT actually happens. > I read somewhere (I don't find the link anymore) that if the trigger is After > Insert, the data > available in the table LOG might not be available anymore to run the trigger. > is that correct? or I > might understood wrong? I don't quite understand. You will have access to the OLD and NEW values in both BEFORE and AFTER triggers. In an AFTER trigger, the table row has already been modified. > what's the difference related to performance concerning a trigger Before > Insert compared with a > trigger After Insert? I don't think that there is a big difference, but you can easily test it: Insert 10 rows with a BEFORE trigger on the table and compare the time it takes to inserting 10 rows with an AFTER trigger. Yours, Laurenz Albe -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [GENERAL] trigger Before or After
On 11/10/2014 10:38 PM, avpro avpro wrote: hi, in the pgsql documentation (http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html) i haven't seen anything referring to: how is affected the data inserted in the new table by a trigger Before Insert compared with a trigger After Insert? and anything related to performance See bottom of above page and here: http://www.postgresql.org/docs/9.1/static/trigger-definition.html thank you have a sunny day -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Lock pileup causes server to stall
On 11/10/2014 01:40 PM, Alvaro Herrera wrote: > Josh Berkus wrote: >> All, >> >> pg version: 9.3.5 >> RHEL 6.5 >> 128GB/32 cores >> Configured with shared_buffers=16GB >> Java/Tomcat/JDBC application >> >> Server has an issue that whenever we get lock waits (transaction lock >> waits, usually on an FK dependancy) lasting over a minute or more than >> 10 at once, *all* queries on the server slow to a crawl, taking 100X to >> 400X normal execution times. > > Current FK checking makes you wait if the referenced tuple is modified > on any indexed column, not just those that are actually used in > foreign keys. Maybe this case would be sped up if we optimized that. > >> * This applies even to queries which are against other databases, so >> it's not purely a lock blocking issue. > > Oh. Yeah, I think this is more likely a problem with the general lock table and shared_buffers than anything to do with actual lock-blocks. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Lock pileup causes server to stall
On Tue, Nov 11, 2014 at 9:11 AM, Josh Berkus wrote: > On 11/10/2014 01:40 PM, Alvaro Herrera wrote: > > Josh Berkus wrote: > >> All, > >> > >> pg version: 9.3.5 > >> RHEL 6.5 > >> 128GB/32 cores > >> Configured with shared_buffers=16GB > >> Java/Tomcat/JDBC application > >> > >> Server has an issue that whenever we get lock waits (transaction lock > >> waits, usually on an FK dependancy) lasting over a minute or more than > >> 10 at once, *all* queries on the server slow to a crawl, taking 100X to > >> 400X normal execution times. > > > > Current FK checking makes you wait if the referenced tuple is modified > > on any indexed column, not just those that are actually used in > > foreign keys. Maybe this case would be sped up if we optimized that. > > > >> * This applies even to queries which are against other databases, so > >> it's not purely a lock blocking issue. > > > > Oh. > > Yeah, I think this is more likely a problem with the general lock table > and shared_buffers than anything to do with actual lock-blocks. > > Any chance you can run 'perf record -a' on it? Cheers, Jeff
Re: [PERFORM] Lock pileup causes server to stall
> On 10/11/2014, at 22.40, Alvaro Herrera wrote: > > Josh Berkus wrote: >> All, >> >> pg version: 9.3.5 >> RHEL 6.5 >> 128GB/32 cores >> Configured with shared_buffers=16GB >> Java/Tomcat/JDBC application >> >> Server has an issue that whenever we get lock waits (transaction lock >> waits, usually on an FK dependancy) lasting over a minute or more than >> 10 at once, *all* queries on the server slow to a crawl, taking 100X to >> 400X normal execution times. > > Current FK checking makes you wait if the referenced tuple is modified > on any indexed column, not just those that are actually used in > foreign keys. Maybe this case would be sped up if we optimized that. Even if it is an gin index that is being modified? seems like a harsh limitation to me. Jesper -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
