Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g2sJj-0005Du-Sw for pgsql-docs@arkaria.postgresql.org; Thu, 20 Sep 2018 06:20:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1g2sJh-0001Zd-ER for pgsql-docs@arkaria.postgresql.org; Thu, 20 Sep 2018 06:19:57 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g2sJh-0001ZW-8k for pgsql-docs@lists.postgresql.org; Thu, 20 Sep 2018 06:19:57 +0000 Received: from mo20.mail-out.ovh.net ([178.32.228.20]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g2sJe-0004yU-Jn for pgsql-docs@lists.postgresql.org; Thu, 20 Sep 2018 06:19:56 +0000 Received: from EX2.emp.local (gw1.pro1.mail.ovh.net [79.137.0.65]) by mo20.mail-out.ovh.net (Postfix) with ESMTPS id C925A55A2F; Thu, 20 Sep 2018 08:19:51 +0200 (CEST) Received: from DAG3EX1.emp.local (172.16.2.5) by EX2.emp.local (172.16.2.2) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256) id 15.1.1531.3; Thu, 20 Sep 2018 08:19:51 +0200 Received: from [192.168.1.14] (90.119.174.156) by DAG3EX1.emp.local (172.16.2.5) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.1.1531.3; Thu, 20 Sep 2018 08:19:50 +0200 Subject: Re: Mention FK creation take ShareRowExclusiveLock on referenced table To: Michael Paquier CC: References: <20180919025302.GF1650@paquier.xyz> From: Adrien Nayrat Openpgp: preference=signencrypt Autocrypt: addr=adrien.nayrat@anayrat.info; prefer-encrypt=mutual; keydata= xsBNBFq2NEMBCAC/W92hl6qJyXV5CkWQOt3WuKA3PwlNbVY8Cuu6BN4mzT56djDn+tTcOD15 L6ExsIbL7p6y/w4lPtMM6Vmy6ByKVAZWNHFpvS6DvTaTDf/mfm1WwixOx5qLg4WyYuiohlCd 8BebhrhS8Lav+Rn29cRUefQHQcnSyc0bX/6ebDgY7hTAvV/f9BJ+XT6uHfrmby0STORIO+Du xekN6rU8ZIORxsdvE9dda3zbQcNLtVe6Y3GJvz/AShI9HYvGkrJchsWeR8aiGmfaX5E/jEme Oe4T6nd/NmoZ8cEjF3rsbE3SYNjAsWmZOZ8HzDZ1gWSY4VtqpIMgF4jJpCu0dyogGLipABEB AAHNKkFkcmllbiBOYXlyYXQgPGFkcmllbi5uYXlyYXRAYW5heXJhdC5pbmZvPsLAfgQTAQIA KAUCWrY0QwIbAwUJA8JnAAYLCQgHAwIGFQgCCQoLBBYCAwECHgECF4AACgkQJLUBR3DFzRdP 7gf/d/f125/CoMQCmSoa3pd96W75w2K2CQ1sQ+n6jzrKojDWUNVeO+dBJtovUQZqxWkFT/zX f9Yrb9xp+OBnaMI/1zWztBP1HASyzy0HDE+JG+oc/7RviQZlR/r4yl09Qcr/rBKCoAbakjRr ClFc++QMt3j7ais0VmzYkKnZXtXn2lRfAlfYzEgEqW5ye3UQG2hmvqZYba4tQX9QlcKtrpJm 3lQ28u+QOXyETamyb2LuR1ZsyrELW0fRnpr9qhuiXKp8MWofccUsXeXa13Wzy+MIvXer/QtB fPQgi5sjtTa5ln3HDdHUc+w/XeKhY56rJodgOft7c9wkz5A3RtrNM1d58M7ATQRatjRDAQgA q5N6VTw5Ol4QSoJFapgF/Lsqnd5X02cF7ZDqTC8c6Yk92ujFmrR5NTffkkk0Zeysy6Dj6Fg1 DDhP5eThw7CyTdCVp8EIWs4RYro7Xj5nVFXgFpL750j2jCgf9VHEY7hrsEq1Z2UHPX2h4tXx Yagh9NDJ7UPv1yVYkF1rfdi9nMOlbAVzNIsVUU3O3770ANfrDAT+cgDjYLUYDmcTx/TLy/g8 PE2nut5BOh8Ispq65BxIuAFIWqihlC499mvBVoJuNm+DyLoxNMOMADa6XLeLf2lQ5HUNPUHd 3Il1e0vhvNGj+vN8oKZURZGVsCCc8HDXh6yT8jA4vdYvKwzVnvTw8QARAQABwsBlBBgBAgAP BQJatjRDAhsMBQkDwmcAAAoJECS1AUdwxc0XvlUIAJg0MWGq7s21o2UChwqRv0ytC6WRy4VU Wue9qR6n4+FUwbE3Pk2xJU//+63Z8ZXcDOjIPhsMtSXc4ptq/EQT9VpHzG3X6/TnM+OkdghB PjzGSquv4xYTzt7HUikTnxRguZPaTYtYJVtUHC77SaSfZx0VonR8lFZ4ppk2q5JZpvVn6gZo umljdjiFJCQXmVqBhkb8U1OA6QLA3QUOo6aUvN7YHUtVZg8k+dE97MWfzG0NwGGFrZSJfOBS iSpxHu8rMEQ6W2icKHQljAfY4O5oG86Uutv7rLvPA707Cl78iOfiWtmVjMRbryRYE5pgKN5H PAnjHQUgRolpFu4IbrwjcVE= Message-ID: <068af352-1ef4-77ee-4f56-3e24a1181fbd@anayrat.info> Date: Thu, 20 Sep 2018 08:23:45 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.0 MIME-Version: 1.0 In-Reply-To: <20180919025302.GF1650@paquier.xyz> Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="9zpUJuk1ibt08nd1Kj4dKwaMcDxZOdYIz" X-Originating-IP: [90.119.174.156] X-ClientProxiedBy: DAG3EX1.emp.local (172.16.2.5) To DAG3EX1.emp.local (172.16.2.5) X-Ovh-Tracer-Id: 4325707443369441988 X-VR-SPAMSTATE: OK X-VR-SPAMSCORE: -100 X-VR-SPAMCAUSE: gggruggvucftvghtrhhoucdtuddrgedtjedrkeelgddutdeiucetufdoteggodetrfdotffvucfrrhhofhhilhgvmecuqfggjfdpvefjgfevmfevgfenuceurghilhhouhhtmecuhedttdenucesvcftvggtihhpihgvnhhtshculddquddttddm List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --9zpUJuk1ibt08nd1Kj4dKwaMcDxZOdYIz Content-Type: multipart/mixed; boundary="5GfIWSM46o9IiS7yMjShfshg3WSCK7SXW"; protected-headers="v1" From: Adrien Nayrat To: Michael Paquier Cc: pgsql-docs@lists.postgresql.org Message-ID: <068af352-1ef4-77ee-4f56-3e24a1181fbd@anayrat.info> Subject: Re: Mention FK creation take ShareRowExclusiveLock on referenced table References: <20180919025302.GF1650@paquier.xyz> In-Reply-To: <20180919025302.GF1650@paquier.xyz> --5GfIWSM46o9IiS7yMjShfshg3WSCK7SXW Content-Type: multipart/mixed; boundary="------------5BDA853CFC89D5436C40C9E4" Content-Language: fr-classic This is a multi-part message in MIME format. --------------5BDA853CFC89D5436C40C9E4 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable On 9/19/18 4:53 AM, Michael Paquier wrote: > On Tue, Sep 18, 2018 at 12:32:54PM +0200, Adrien NAYRAT wrote: >> A few days ago I was surprised a CREATE TABLE containing FK constraint= was >> stuck due to an automatic vacuum freeze (which took ShareUpdateExclusi= veLock >> if I remember) on referenced table. >=20 > Right. See the top of vacuum_rel() where lmode is set. >=20 >> After digging into the code I found theses lines in tablecmds.c : >> >> /* >> * Grab ShareRowExclusiveLock on the pk table, so that someone doesn't= >> * delete rows out from under us. >> */ >> >> Maybe it should be documented in theses pages? >> >> https://www.postgresql.org/docs/current/static/sql-createtable.html >> https://www.postgresql.org/docs/current/static/sql-altertable.html >> >> If you agree I can send a patch. >=20 > That looks like a good idea. Are you thinking about adding a comment > about that in "ADD table_constraint" for the ALTER TABLE page, and in > "FOREIGN KEY" for the CREATE TABLE page? Yes, here is the patch Thanks --=20 Adrien --------------5BDA853CFC89D5436C40C9E4 Content-Type: text/x-patch; name="mention_lock_fk.patch" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="mention_lock_fk.patch" diff --combined doc/src/sgml/ref/alter_table.sgml index 1e4a327327,ec6b4c3311..0000000000 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@@ -377,11 -377,6 +377,11 @@@ WITH ( MODULUS . =20 + + Adding a foreign key requires taking a SHARE ROW EXCLUSIV= E + lock on the referenced table. + + =20 diff --combined doc/src/sgml/ref/create_table.sgml index a974bea6b1,5a19f94ce9..0000000000 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@@ -993,8 -993,6 +993,8 @@@ WITH ( MODULUS REFERENCES permission on the referen= ced table (either the whole table, or the specific referenced columns). + Addding a foreign key constraint requires taking a SHARE= ROW + EXCLUSIVE lock on the referenced table. Note that foreign key constraints cannot be defined between tempo= rary tables and permanent tables. Also note that while it is possible= to define a foreign key on a partitioned table, it is not possible t= o --------------5BDA853CFC89D5436C40C9E4-- --5GfIWSM46o9IiS7yMjShfshg3WSCK7SXW-- --9zpUJuk1ibt08nd1Kj4dKwaMcDxZOdYIz Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- iQEyBAEBCAAdFiEEEF91Q6NvQjZh3AIaJLUBR3DFzRcFAlujPPEACgkQJLUBR3DF zRdi2Qf3cGcFie5WpoLr+BsSYYdh8yGHAm78kA3vz0g5gbb2Mg+aqSmc/418VBOd RsY2qr5lEYtIyxMs6zpo8n926N3HK+cgoNDG4b+A4qEJpa1LmX5yop6YyEPmiD6y vIhAJIaskSsKhEYlSRZjASbypbmM1Hqy2WqolBoqX7+WUcTTcoiPimeVB2EHA8yo 0SXiuTsIZtOvfcqmnXxGpA+hiI3nfEzQ9DFBaV6ZpzqNRC1di1VCy8XImzmXYg7T Cye9MU+eMcR6QSo86CPLgqSes+FO8kj+1aB8iKkEL/ogmabJGOv6AIXl5IsVIeCI HsNetXnwsQ618z6uwsbF3J9Bg4sV =jBks -----END PGP SIGNATURE----- --9zpUJuk1ibt08nd1Kj4dKwaMcDxZOdYIz--