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

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; $$
LANGUAGEplpgsql;
 
 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
conflictSELECT 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;