I was trying to figure out what exactly the "crosscheck snapshot" does in the
RI checks, and hit some assertion failures:
postgres=# create table p(i int primary key);
CREATE TABLE
postgres=# create table f (i int references p on delete cascade on update cascade deferrable initially deferred);
CREATE TABLE
postgres=# insert into p values (1);
INSERT 0 1
postgres=# begin isolation level repeatable read;
BEGIN
postgres=*# table f;
i
---
(0 rows)
In another session:
postgres=# insert into f values (1);
INSERT 0 1
Back in the first session:
postgres=*# delete from p where i=1;
TRAP: FailedAssertion("!(tp.t_data->t_infomask & HEAP_XMAX_INVALID)", File: "heapam.c", Line: 2652)
I'm not familiar enough with this code but I wonder if it's only about
incorrect assertions. When I commented out some, I got error message that
makes sense to me:
postgres=*# delete from p where i=1;
2020-03-17 11:59:19.214 CET [89379] ERROR: could not serialize access due to concurrent update
2020-03-17 11:59:19.214 CET [89379] CONTEXT: SQL statement "DELETE FROM ONLY "public"."f" WHERE $1
OPERATOR(pg_catalog.=)"i""
2020-03-17 11:59:19.214 CET [89379] STATEMENT: delete from p where i=1;
ERROR: could not serialize access due to concurrent update
CONTEXT: SQL statement "DELETE FROM ONLY "public"."f" WHERE $1 OPERATOR(pg_catalog.=) "i""
Similarly, if the test ends with an UPDATE statement, I get this failure:
postgres=*# update p set i=i+1 where i=1;
TRAP: FailedAssertion("!ItemPointerEquals(&oldtup.t_self, &oldtup.t_data->t_ctid)", File: "heapam.c", Line: 3275)
Likewise, with the Assert() statements commented out, the right thing seems to
happen:
2020-03-17 11:57:04.810 CET [88678] ERROR: could not serialize access due to concurrent update
2020-03-17 11:57:04.810 CET [88678] CONTEXT: SQL statement "UPDATE ONLY "public"."f" SET "i" = $1 WHERE $2
OPERATOR(pg_catalog.=)"i""
2020-03-17 11:57:04.810 CET [88678] STATEMENT: update p set i=i+1 where i=1;
ERROR: could not serialize access due to concurrent update
CONTEXT: SQL statement "UPDATE ONLY "public"."f" SET "i" = $1 WHERE $2 OPERATOR(pg_catalog.=) "i""
--
Antonin Houska
Web: https://www.cybertec-postgresql.com
