Re: [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation

On Mon, Dec 12, 2016 at 8:45 AM, Ian Jackson <ian(dot)jackson(at)eu(dot)citrix(dot)com> wrote:

> AIUI the documented behavour is that "every set of successful
> transactions is serialisable".

Well, in context that is referring to serializable transactions.
No such guarantee is provided for other isolation levels.

By the way, existing behavior should be sufficient to prevent
serialization anomalies from becoming manifest in the database;
where it is less than ideal is that it is hard to tell from the
SQLSTATE on a failure whether a retry is sensible. It would be
nice to provide the additional functionality, but database is
performing as intended and (as far as I know) as documented. If
the documentation on this is not clear, I'd be happy to help get it
fixed, but barring any deficiency there, this is a feature request,
not a bug report.

> But, consider the following scenario.
>
> [example]

> I have just tried this and got this result:
>
> [nonsensical results]

I didn't. First, I got this when I tried to start the concurrent
transactions using the example as provided:

test=# SELECT count(*) FROM t WHERE k=1; -- save value
ERROR: operator does not exist: text = integer
LINE 1: SELECT count(*) FROM t WHERE k=1;
^
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.

That is as it should be. There is no equality comparison operator
supported for text on one side and integer on the other. There
would be no principled way to determine the correct result of
comparing '2' and '15' or of comparing '01' and '1'. It kinda
raises a question of what you are running that did *not* generate
this error. What version with what modifications are you running?

So, I modified it so that it *should* run, set
default_transaction_isolation = 'serializable' on both connections,
and got this:

*** CONNECTION 1 ***
test=# CREATE OR REPLACE FUNCTION demo(nk TEXT, c INTEGER) RETURNS INTEGER AS $$
test$# BEGIN
test$# BEGIN
test$# INSERT INTO t (k,v) VALUES (nk, -1);
test$# EXCEPTION WHEN unique_violation THEN
test$# INSERT INTO c (k,v) VALUES (nk, c);
test$# END;
test$# RETURN 0;
test$# END;
test$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
test=#
test=# DROP TABLE IF EXISTS t;
DROP TABLE
test=# DROP TABLE IF EXISTS c;
DROP TABLE
test=#
test=# CREATE TABLE t (k TEXT PRIMARY KEY, v INTEGER NOT NULL);
CREATE TABLE
test=# CREATE TABLE c (k TEXT PRIMARY KEY, v INTEGER NOT NULL);
CREATE TABLE
test=#
test=# BEGIN;
BEGIN
test=# SELECT count(*) FROM t WHERE k = '1'; -- save value
count
-------
0
(1 row)

test=# -- sleep to ensure conflict

*** CONNECTION 2 ***
test=# BEGIN;
BEGIN
test=# SELECT count(*) FROM t WHERE k = '1'; -- save value
count
-------
0
(1 row)

test=# -- sleep to ensure conflict

*** CONNECTION 1 ***
test=# SELECT demo('1', 0); -- using value from SELECT
demo
------
0
(1 row)

test=#

*** CONNECTION 2 ***
test=# SELECT demo('1', 0); -- using value from SELECT
*** CONNECTION 2 blocks ***

*** CONNECTION 1 ***
test=# COMMIT;
COMMIT
test=#

*** CONNECTION 2 unblocks and outputs ***
ERROR: could not serialize access due to read/write dependencies
among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during write.
HINT: The transaction might succeed if retried.
CONTEXT: SQL statement "INSERT INTO t (k,v) VALUES (nk, -1)"
PL/pgSQL function demo(text,integer) line 4 at SQL statement
test=#

As you can see, this generated a serialization failure. I decided
to do what an application should, and retry the transaction.

*** CONNECTION 2 ***
test=# ROLLBACK;
ROLLBACK
test=# BEGIN;
BEGIN
test=# SELECT count(*) FROM t WHERE k = '1'; -- save value
count
-------
1
(1 row)

test=# SELECT demo('1', 1); -- using value from SELECT
demo
------
0
(1 row)

test=# COMMIT;
COMMIT
test=# SELECT * FROM t;
k | v
---+----
1 | -1
(1 row)

test=# SELECT * FROM c;
k | v
---+---
1 | 1
(1 row)

test=#

If you have some way to cause a set of concurrent serializable
transactions to generate results from those transactions which
commit which is not consistent with some one-at-a-time order of
execution, I would be very interested in seeing the test case.
The above, however, is not it.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company