RE: Perform streaming logical transactions by background workers and parallel apply

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Hayato Kuroda (Fujitsu) <[email protected]>
To: [email protected] <[email protected]>
Cc: Masahiko Sawada <[email protected]>
Cc: [email protected] <[email protected]>
Cc: Peter Smith <[email protected]>
Cc: Dilip Kumar <[email protected]>
Cc: [email protected] <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Amit Kapila <[email protected]>
Subject: RE: Perform streaming logical transactions by background workers and parallel apply
Date: Wed, 9 Nov 2022 02:54:19 +0000
Message-ID: <TYAPR01MB58663C38803D2E72DE585FE2F53E9@TYAPR01MB5866.jpnprd01.prod.outlook.com> (raw)
In-Reply-To: <OS0PR01MB5716F3493A051C5BB23CAD3C943F9@OS0PR01MB5716.jpnprd01.prod.outlook.com>
References: <OS0PR01MB5716730C06159452335D870D947F9@OS0PR01MB5716.jpnprd01.prod.outlook.com>
	<CAA4eK1Lu-6oXMk7ZaGYLwm3CRLBuzueGbasyHnNpJxu6Mq3mmg@mail.gmail.com>
	<OS3PR01MB6275F145878B4A44586C46CE9E499@OS3PR01MB6275.jpnprd01.prod.outlook.com>
	<CAA4eK1LMVdS6uM7Tw7ANL0BetAd76TKkmAXNNQa0haTe2tax6g@mail.gmail.com>
	<OS3PR01MB6275EFC4B707650DAB9392859E4D9@OS3PR01MB6275.jpnprd01.prod.outlook.com>
	<CAHut+PuVjRgGr4saN7qwq0oB8DANHVR7UfDiciB1Q3cYN54F6A@mail.gmail.com>
	<OS3PR01MB627567CF4B96A23DAFAF77269E4E9@OS3PR01MB6275.jpnprd01.prod.outlook.com>
	<CAA4eK1KEf6w1azJytYnLhd2Lo-=rPkRh1Jv2ePcvs=SKqNQnAQ@mail.gmail.com>
	<CAA4eK1KjGNA8T8O77rRhkv6bRT6OsdQaEy--2hNrJFCc80bN0A@mail.gmail.com>
	<OS0PR01MB571620741B90C769D2727D7F94559@OS0PR01MB5716.jpnprd01.prod.outlook.com>
	<CAD21AoBLPDPCE4jj16ZRE9J8AoxLtvo3yasToaP5kRp6++=yyg@mail.gmail.com>
	<OS0PR01MB57161EFFC1D03B9080A9B812945C9@OS0PR01MB5716.jpnprd01.prod.outlook.com>
	<CAD21AoCRDWMebeDNeMNgf4LpTA23si_1xU0T_F2Rh+BD9HU6=Q@mail.gmail.com>
	<CAA4eK1LJoA868HCMmrzPqfcFL=bcFtAi6WWSJ0NKKQp8gspYVQ@mail.gmail.com>
	<CAD21AoDm3224e=se7=ZYt=R+v0_ZJ4E9dd5y2816_rTTCV+G+Q@mail.gmail.com>
	<CAA4eK1JYFXEoFhJAvg1qU=nZrZLw_87X=2YWQGFBbcBGirAUwA@mail.gmail.com>
	<CAD21AoBDLiFHThzfzvrnViTKnsm-pM5YvfTys_96-jBSXpWYqw@mail.gmail.com>
	<CAD21AoCWovvhGBD2uKcQqbk6px6apswuBrs6dR9+WhP1j2LdsQ@mail.gmail.com>
	<OS0PR01MB5716410E7551394B730D433294389@OS0PR01MB5716.jpnprd01.prod.outlook.com>
	<CAA4eK1+UK0eN9hqU1JqY5WR5-YNbh6_2t8Zvd3bXpViQSE2+Rw@mail.gmail.com>
	<CAA4eK1L5bmFT96XX2bevh9jwJuN3edv7i_-NP+TVt-87yP6LNw@mail.gmail.com>
	<OS0PR01MB5716E366874B253B58FC0A23943C9@OS0PR01MB5716.jpnprd01.prod.outlook.com>
	<OS0PR01MB5716F3493A051C5BB23CAD3C943F9@OS0PR01MB5716.jpnprd01.prod.outlook.com>

Hi all,

I have tested the patch set in two cases, so I want to share the result. 

====
Case 1. deadlock caused by leader worker, parallel worker, and backend.

Case 2. deadlock caused by non-immutable trigger
===

It has worked well in both cases. PSA reports what I did.
I want to investigate more if anymore wants to check.

Best Regards,
Hayato Kuroda
FUJITSU LIMITED


# Deadlock caused by leader worker, parallel worker, and backend.

## SCHEMA DEFINITIONS

[Publisher]

CREATE TABLE tbl1 (c int);
CREATE TABLE tbl2 (c1 int primary key, c2 int, c3 int);
CREATE PUBLICATION pub FOR ALL TABLES;

[Subscriber]
CREATE TABLE tbl1 (c int);
CREATE UNIQUE INDEX idx_tbl1 on tbl1(c)
CREATE TABLE tbl2 (c1 int primary key, c2 int, c3 int);
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 subscriber
	BEGIN;
	INSERT INTO tbl2 VALUES (1, 2, 3);
	INSERT INTO tbl1 VALUES (1);

		Tx3 on publisher
		BEGIN;
		INSERT INTO tbl2 VALUES(1, 2, 3);
		COMMIT;


### RESULTS

Followings were copied from log on subscriber.

```
ERROR:  deadlock detected
DETAIL:  Process (LA) waits for ShareLock on transaction 743; blocked by process (Backend).
        Process (Backend) waits for ShareLock on transaction 742; blocked by process (PA).
        Process (PA) waits for AccessShareLock on object 16393 of class 6100 of database 0; blocked by process (LA).
        Process (LA): <command string not enabled>
        Process (Backend): INSERT INTO tbl1 VALUES (1);
        Process (PA): <command string not enabled>
```


# 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>
```




Attachments:

  [text/plain] case1.txt (1.4K, ../TYAPR01MB58663C38803D2E72DE585FE2F53E9@TYAPR01MB5866.jpnprd01.prod.outlook.com/2-case1.txt)
  download | inline:
# Deadlock caused by leader worker, parallel worker, and backend.

## SCHEMA DEFINITIONS

[Publisher]

CREATE TABLE tbl1 (c int);
CREATE TABLE tbl2 (c1 int primary key, c2 int, c3 int);
CREATE PUBLICATION pub FOR ALL TABLES;

[Subscriber]
CREATE TABLE tbl1 (c int);
CREATE UNIQUE INDEX idx_tbl1 on tbl1(c)
CREATE TABLE tbl2 (c1 int primary key, c2 int, c3 int);
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 subscriber
	BEGIN;
	INSERT INTO tbl2 VALUES (1, 2, 3);
	INSERT INTO tbl1 VALUES (1);

		Tx3 on publisher
		BEGIN;
		INSERT INTO tbl2 VALUES(1, 2, 3);
		COMMIT;


### RESULTS

Followings were copied from log on subscriber.

```
ERROR:  deadlock detected
DETAIL:  Process (LA) waits for ShareLock on transaction 743; blocked by process (Backend).
        Process (Backend) waits for ShareLock on transaction 742; blocked by process (PA).
        Process (PA) waits for AccessShareLock on object 16393 of class 6100 of database 0; blocked by process (LA).
        Process (LA): <command string not enabled>
        Process (Backend): INSERT INTO tbl1 VALUES (1);
        Process (PA): <command string not enabled>
```


  [text/plain] case2.txt (1.4K, ../TYAPR01MB58663C38803D2E72DE585FE2F53E9@TYAPR01MB5866.jpnprd01.prod.outlook.com/3-case2.txt)
  download | inline:
# 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>
```

view thread (625+ messages)  latest in thread

reply

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: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: RE: Perform streaming logical transactions by background workers and parallel apply
  In-Reply-To: <TYAPR01MB58663C38803D2E72DE585FE2F53E9@TYAPR01MB5866.jpnprd01.prod.outlook.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox