Jesper Krogh wrote: > > > On 10/11/2014, at 22.40, Alvaro Herrera <alvherre@xxxxxxxxxxxxxxx> 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. Well, as I recall it's only unique indexes, so it's not *that* harsh. Anyway, the fklocks patch was stupidly complex (and still got much stuff wrong). I didn't want to add more ground to objections by additionally breaking the abstraction between heapam and the concept of "columns referenced by a foreign key constraint". So it was discussed and decided we'd leave that for future improvement. Patches are welcome, particularly if they come from the future. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
