From: Ian Jackson <ian.jackson@eu.citrix.com>
To: Kevin Grittner <kgrittn@gmail.com>
Cc: xen-devel@lists.xenproject.org
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation
Date: Mon, 12 Dec 2016 14:45:21 +0000
Message-ID: <22606.47105.549023.301281@mariner.uk.xensource.com> (raw)
In-Reply-To: <CACjxUsMsHxZ_SrWw7bdCT1Kn8E9mtA1TvhAEbTt-L9AWKKZLCg@mail.gmail.com>
References: <1481307991-16971-1-git-send-email-ian.jackson@eu.citrix.com>
<CACjxUsMsHxZ_SrWw7bdCT1Kn8E9mtA1TvhAEbTt-L9AWKKZLCg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Kevin Grittner writes ("Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation"):
> If I recall correctly, the constraints for which there can be
> errors appearing due to concurrent transactions are primary key,
> unique, and foreign key constraints. I don't remember seeing it
> happen, but it would not surprise me if an exclusion constraint can
> also cause an error due to a concurrent transaction's interaction
> with the transaction receiving the error.
Is it not in principle also possible to contrive a situation where
some set of (suitably weird) transactions will generate almost any
error, from the outer transaction ?
This is at the very least possible using pgsql's in-sql
exception-trapping facilities. Such a construction might, in
principle, generate any error which can be conditionally generated at
query runtime.
ISTM that depending on the implementation arrangements (which I
frankly don't understand at all) there may be other constructions
which would give "impossible" answers.
Actually, now I come to think of it, the fact that pgsql has an in-sql
exception trapping facility means that the current situation is
clearly an actual bug, in the sense that the behaviour is contrary to
the documentation. (And contrary to any reasonable thing that could
be written in the documentation.)
AIUI the documented behavour is that "every set of successful
transactions is serialisable".
But, consider the following scenario.
Context:
CREATE OR REPLACE FUNCTION demo(nk TEXT, c INTEGER) RETURNS INTEGER AS $$
BEGIN
BEGIN
INSERT INTO t (k,v) VALUES (nk, -1);
EXCEPTION WHEN unique_violation THEN
INSERT INTO c (k,v) VALUES (nk, c);
END;
RETURN 0;
END;
$$ LANGUAGE plpgsql;
DROP TABLE IF EXISTS t;
DROP TABLE IF EXISTS c;
CREATE TABLE t (k TEXT PRIMARY KEY, v INTEGER NOT NULL);
CREATE TABLE c (k TEXT PRIMARY KEY, v INTEGER NOT NULL);
Two identical transactions:
BEGIN;
SELECT count(*) FROM t WHERE k=1; -- save value
-- sleep to ensure conflict
SELECT demo(1, ?); -- using value from SELECT
COMMIT;
I have just tried this and got this result:
osstestdb_test_iwj=> select * from t;
k | v
---+----
1 | -1
(1 row)
osstestdb_test_iwj=> select * from c;
k | v
---+---
1 | 0
(1 row)
osstestdb_test_iwj=>
The row ('1',0) in table c is clearly wrong. No rows with v=0 could
ever be inserted into c by this SQL code, unless the other
transaction is somehow interfering in the middle.
The perl program I used is below. `csreadconfig' does nothing of
particular interest except obtaining the db connnection as $dbh_tests.
Ian.
#!/usr/bin/perl -w
use strict qw(vars);
use Osstest;
use Data::Dumper;
use Osstest::Executive;
csreadconfig();
if (!@ARGV) {
$dbh_tests->do(<<'END');
CREATE OR REPLACE FUNCTION demo(nk TEXT, c INTEGER) RETURNS INTEGER AS $$
BEGIN
BEGIN
INSERT INTO t (k,v) VALUES (nk, -1);
EXCEPTION WHEN unique_violation THEN
INSERT INTO c (k,v) VALUES (nk, c);
END;
RETURN 0;
END;
$$ LANGUAGE plpgsql;
DROP TABLE IF EXISTS t;
DROP TABLE IF EXISTS c;
CREATE TABLE t (k TEXT PRIMARY KEY, v INTEGER NOT NULL);
CREATE TABLE c (k TEXT PRIMARY KEY, v INTEGER NOT NULL);
END
exit 0;
}
our ($k,$v) = @ARGV;
$dbh_tests->begin_work;
$dbh_tests->do("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE");
our ($conflictors)= $dbh_tests->selectrow_array(<<END, {}, $k);
SELECT count(*) FROM t WHERE k=?
END
print STDERR "conflictors=$conflictors\n";
sleep 5;
print STDERR "continuing...\n";
$dbh_tests->do(<<END, {}, $k, $conflictors);
SELECT demo(?,?);
END
$dbh_tests->commit;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: ian.jackson@eu.citrix.com, kgrittn@gmail.com, xen-devel@lists.xenproject.org
Subject: Re: [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation
In-Reply-To: <22606.47105.549023.301281@mariner.uk.xensource.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox