--C7zPtVaVf+AK4Oqc
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
Hello,
Using PostgreSQL 8.2, I have "atable" one of whose columns reference a
column in "othertable". I see serialization failures as a result of
*inserts* to atable in the context of:
'"SELECT 1 FROM ONLY othertable x WHERE "otherid" =3D $1 FOR SHARE OF
x" ' in 'INSERT INTO atable (otherid, col2, col3) VALUES (.., ..,
..)'
My interpretation is that the acquisition of a lock on the row in
question is due to the enforcement of the foreign key constraint, and
that, combined with the fact that this locking is performed on a
per-row level, this creates a conflict with any concurrent transaction
updating that row in othertable, regardless of whether 'otherid' is
touched.
First off, is this correct?
If yes:
To me, it would be advantegous if "bogus" conflicts were not generated
like this. Although I realize that serializable transactions are
subject to retries, one still tends to design transactions
specifically to avoid generating conflicts. It is useful to know that
a particular transaction is guaranteed to not generate serialization
failures. And if that is not possible, than at least minimizing the
risk is useful. Normally, an INSERT is conflict-free and it would be
nice to keep it that way.
Unfortunately, the introduction of enforced referential integrity has
this negative side effect.
If my interpretation above is correct; is the use of row-level locking
due to:
(1) it being mandated by a standard?
(2) "cell"-level beinginefficient?
(3) no one having implemented "cell"-level locking?
(4) there being a problem with having a DELETE correctly
conflict with a "cell"-level lock?
(*) something else?
In short, I am wondering whether this behavior is intended or a
side-efffect of implementation details.
--=20
/ Peter Schuller
PGP userID: 0xE9758B7D or 'Peter Schuller <peter.schuller@[EMAIL PROTECTED]
>'
Key retrieval: Send an E-Mail to getpgpkey@[EMAIL PROTECTED]
peter.schuller@[EMAIL PROTECTED]
Web: http://www.scode.org
--C7zPtVaVf+AK4Oqc
Content-Type: application/pgp-signature
Content-Disposition: inline
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.8 (FreeBSD)
iEYEARECAAYFAkfydYIACgkQDNor2+l1i31wvwCffFDzcs8jU4MJsomL3Eksa4p9
zlAAnR+R5p2cFbxjzPmkLRzCr9lHV4SF
=FzYh
-----END PGP SIGNATURE-----
--C7zPtVaVf+AK4Oqc--
|
