case2.txt

text/plain

Download

# deadlock caused by non-immutable trigger

## SCHEMA DEFINITIONS

[Publisher]

CREATE TABLE tbl1 (c int);
CREATE PUBLICATION pub FOR ALL TABLES;

[Subscriber]
CREATE TABLE tbl1 (c int);
CREATE TABLE tbl1_log (c int PRIMARY KEY);

CREATE FUNCTION record_insert() RETURNS trigger AS $record_insert$
BEGIN
  SET search_path TO 'public';
  INSERT INTO tbl1_log VALUES (NEW.c);
  RAISE LOG 'record_insert is fired';
  RESET search_path;
  RETURN NEW;
END;
$record_insert$
LANGUAGE plpgsql;

CREATE TRIGGER record_trigger AFTER INSERT ON tbl1 FOR EACH ROW EXECUTE FUNCTION record_insert();
ALTER TABLE tbl1 ENABLE ALWAYS TRIGGER record_trigger

CREATE SUBSCRIPTION sub CONNECTION 'port=$port_N1 user=postgres' PUBLICATION pub WITH(streaming = 'parallel', copy_data = 'false', two_phase = 'on');


### WORKLOADS

Tx1 on publisher
BEGIN;
INSERT INTO tbl1 SELECT i FROM generate_series(1, 5000);

	Tx2 on publisher
	BEGIN;
	INSERT INTO tbl1 VALUES (1);
	COMMIT;
COMMIT;


### RESULTS

Followings were copied from log on subscriber.

```
ERROR:  deadlock detected
DETAIL:  Process (LA) waits for ShareLock on transaction 735; blocked by process (PA).
        Process (PA) waits for AccessShareLock on object 16400 of class 6100 of database 0; blocked by process (LA).
        Process (LA): <command string not enabled>
        Process (PA): <command string not enabled>
```