Date: Wed, 7 May 2014 20:54:17 -0400
From: Stephen Frost <sfrost@snowman.net>
To: "M. D." <lists@turnkey.bz>
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: table locking on creating FK
Message-ID: <20140508005417.GG2556@tamriel.snowman.net>
References: <536AA329.9030908@turnkey.bz>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature"; boundary="x1FyvKls8kYUZVXi"
Content-Disposition: inline
In-Reply-To: <536AA329.9030908@turnkey.bz>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org


--x1FyvKls8kYUZVXi
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Mark,

* M. D. (lists@turnkey.bz) wrote:
> Why does a table lock up if I want to create a FK to it?  e.g.  I
> have a separate schema for my own mods to the database but if I want
> to reference anything in the public schema on a customer table, that
> table will be locked up.

That's correct, creating a foreign key to a table requires an
AccessExclusiveLock on the referred-to table.

> Why does a table lockup when disabling a trigger on it?

For both of these, the issue is that we have to make sure every backend
has the same view of the table and all triggers, etc, which exist on the
table.  There is ongoing work to reduce lock levels where possible, now
that PG accesses the catalogs using MVCC semantics (which was not true
previously), but I wouldn't get your hopes up on these changing.

> I just tried this on a live database, and ended up restarting the
> postgres service because the whole table was locked and no users
> were able to do anything.

You would need to simply kill the transaction which held the locks,
using pg_terminate_backend().

> I guess I'm dumb (or stupid) to try it in production, but I wanted
> to create an index on an audit table, so I knew enough that I would
> have to disable the audit trigger before I could create an index on
> a 1.8 million row table.  Then the main gltx table locked up on
> disabling the trigger.  I found the pid of the process by doing
> this:

You might want to investigate the 'CONCURRENTLY' option of CREATE INDEX.

> select * from pg_stat_activity where query ilike '%trigger%';
>=20
> Then tried to cancel the query using this:
>=20
> select pg_cancel_backend(17069);

pg_cancel_backend() will cancel a running *query* but it does not
terminate the transaction.  Locks are held until the end of a
transaction.  You likely wanted 'pg_terminate_backend()', as mentioned
above, which would have both rolled back the transaction and termianted
the database connection.

> But that did not happen within 1 min, and with 90 sales people all
> waiting on this server, I did a kill -9 on that pid to get everyone
> back as soon as possible.  This caused a bunch of "terminating
> connection because of crash of another server process" errors in
> pg_log, but I don't see anything serious after that.

Doing a -9 against a PG server is a very bad idea- don't do it.  Use
pg_terminate_backend().

> Is there any way to recover from a locked situation like this?

In general, I'd suggest you avoid trying to do DDL without a proper
outage window or at least only during non-peak times and only once you
have a good understanding of what locks will be taken out, and for how
long, during your DDL work.

Note also that the way locking is done in PG, once someone wants a
higher lock on a table, everyone else wanting locks on the table have to
wait (even if the table is only currently locked at the lower level).
This avoids the higher-level lock process being stalled forever but does
mean those locks have a high impact on the running system.

	Thanks,

		Stephen

--x1FyvKls8kYUZVXi
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.14 (GNU/Linux)

iQIcBAEBAgAGBQJTatW5AAoJEO1sijiDR2RVlVIQAJWzZ3vYJZfRP/03/FpWnd5x
N02+Nt+Ry5QT8vGnl+DaOxtZbu7pvdGdZ6GTn0+1g1X7CGkLrTE2HJzRhsfqe6Yo
S8S2ZifTOxj49lZX0c0ahIGA2Xb549BrovF9tzS4A5mTACqIPubPMXuNFb0moIUP
9YixAAHUQuBm0fo/m//2ahY6YESbsuTEWuNw+gqvt2uHzNcapi11p5tg1nu0DJ9z
+zH5viyg9mWiZMY+WScWiQ2zSc4K9lT0rgk/VUqpnNrD0uekdeQkU00UleicyxSF
wQaLSlxnPDcM44HMdW4skvWuqEFn64Vrx1z6e28180MIW5buhzj+A2HljCUQrUoO
ccwOcsOWicRFBaF9MwpfCMVEQzt8mFgRJHApJZ22v2zG6c6bxEnHUNudsU+g/Z3Y
qY0Z2sLznr7F00PI5cnZq8W0ZB8RikW3OMhbEpnvY1eOWyd6VMx6tjpP8nkQEIZ8
i0xBsjwiPeHkCVkmytdp58vOrZ0pIYRorm8P1e/j4TIDTZwqENFamrYhLdPXZCAW
6avtFgLC0undHeNASxIXYVQ3Rbw22Tisgc8C85Tpbh9pLg0MKu46Nw1NG2v3/x32
48k2dbvH2X4LcSykco9QolNlzswqRmYQZlaCO2YuJaNeoft7xWMdzCpqVWLBx+i4
drkvTcwNCI5zSQEXpOvI
=3tpf
-----END PGP SIGNATURE-----

--x1FyvKls8kYUZVXi--