Re: Reducing lock strength of adding foreign keys

On Sun, Oct 26, 2014 at 9:48 PM, Andreas Karlsson <andreas(at)proxel(dot)se> wrote:
> Agreed.. But I think reducing the lock level of the secondary table is much
> more important than doing the same for the primary table due to the case
> where the secondary table is an existing table which is hit by a workload of
> long running queries and DML while the primary is a new table which is added
> now. In my dream world I could add the new table without any disruption at
> all of queries using the secondary table, no matter the duration of the
> transaction adding the table (barring insertion of actual data into the
> primary table, which would take row locks).

That would indeed be nice, but it doesn't seem very practical, because
the parent needs triggers, too: if you try to delete a row from the
parent, or update the key, it's got to go look at the child and see
whether there are rows against the old value. Then it's got to either
update those rows, or null out the value, or throw an error.
Regardless of which of those things it does (which depends on the ON
DELETE and ON UPDATE settings you choose), it's hard to imagine that
it would be a good idea for any of those things to start happening in
the middle of a transaction or statement.

So, let's take what we can get. :-)

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company