From: Avi Weinberg <AviW@gilat.com>
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Tables get stuck at srsubstate = f
Thread-Topic: Tables get stuck at srsubstate = f
Thread-Index: Adq4Lj6RGx/imOBjS1OdA61z0oxnhQ==
Date: Thu, 6 Jun 2024 16:38:39 +0000
Message-ID:
 <DB9PR07MB71805B7673176FA26D81ABC2CBFA2@DB9PR07MB7180.eurprd07.prod.outlook.com>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0:
 =?us-ascii?Q?E+J/BcI/IQojTbEy8ItE3yHbc+dJF0VZZCCrRst4Mo+5OMQMzxEPLXSZowdb?=
 =?us-ascii?Q?YgN5W0SPWbIXBrGURrNwz6Od2DOyanpSuNHRjNwCTs3nsNaekt6CPPcS8rDj?=
 =?us-ascii?Q?IehXSCcKvbNCdmqkgmdJDpfI+q6EjeCg0dVB9/aDiM8XDuaWKbbr3Fi6GSpb?=
 =?us-ascii?Q?3yAtvdQqqIAqbaWbG2uh0pbUU+59vq7NB6J2xCE8JEyq2ZFkqo62s+WUlUw3?=
 =?us-ascii?Q?nBmKldAzvgfzs6RVUF49F0s1kXma3eTePI/T253XWrWVDrRWSEgG/lM1T0UD?=
 =?us-ascii?Q?kZWGbURqtcTQCXWuqfE6DquN3+Fp5Qu3/7w21wcROqEwkqrQfbZbIZs3HLNA?=
 =?us-ascii?Q?ZyElmOvovNByijR6q4zaQXTS5HWK3MLWS88mIdA+kn57sttUCcIMPaSPWRle?=
 =?us-ascii?Q?EsAUWwqYjWb7nULEdisdRoAmcWgVEno/U9Jfjj8hWb554SDSf345iicHfFUS?=
 =?us-ascii?Q?wN5yrxpyA9J7WuO/QFqzv6WLM+D3cLfmBSDoXqR/MEM0lpS+XE1LGyDx/VYL?=
 =?us-ascii?Q?ZAxkhYeD56YQIfY8ouymkcTHBpw8ubEwUAP9zPopQLIyTRvzNb+gJzUC7tO0?=
 =?us-ascii?Q?XTp19Gh0o4PAbe+tpR1mJo8TNPcxrC4R+fIbh/56/h5IepxUksWe3CDyvdZ/?=
 =?us-ascii?Q?dWmcRYrnIPEkaDeMAcOhn619aAfKOr11TqYndwr82tdzYtuTaV6b5DxaUZKo?=
 =?us-ascii?Q?Du2mQYVqqvnOeLj+L/AL8pVPSwnTLr/zWvV9TGgf2QPPyfODzx9BaS8eCw4x?=
 =?us-ascii?Q?CYGImgpuUYfWmzK7H6plDeU9Ey29PNvlQ03vbgGv5l2zywN9GKP98bI49LTW?=
 =?us-ascii?Q?k7vIMLiovlCHoknYNHb8CIj5o0PMwMWL4xXk2cyPy+MroB5NmGluERTsbaht?=
 =?us-ascii?Q?GH9B8MqCTEX6gYZ+YELTvKKByb4UAyQrdjUjtqhY2lZDBAdUqAgZkiSjhjpw?=
 =?us-ascii?Q?k1RUuwfgLi8o6fbX77vh6g0xTMTXb3uGR9TVBO/3NNhsu13odFIAF9uHaeBT?=
 =?us-ascii?Q?qd7s7mf5J0JEIVkr2ZpSOcdzjncOY1G6sPJOdCzWKfUTa6bCHHrEv1hujBm0?=
 =?us-ascii?Q?R+X3Cpyaep3NCwB6DnageKYIRsawjH7u7bQqP8skWANWlD7BjJ7hWWBZV3sO?=
 =?us-ascii?Q?0/UmNVLQLHUM76N28DNbRai8A2Z2EXeJHGkTKZzFOrZtVZPTB9I62lJHn13y?=
 =?us-ascii?Q?Me12f9XsiVkeYNUE0j4B8oLy6nffY0dwnUtoBLfuWgKQLbMSr2odgc+TNoqs?=
 =?us-ascii?Q?Ow8CdFaT9UBAfHnmq6af4LFINAHsiF3DVH6tcwX0YbCQ1EBGEbLsLgCrOqQp?=
 =?us-ascii?Q?pOwZv1+ahm0poxekLPH9u3vlUUJYnlhTDZJarD7uD0/TSPvMbAI6Xbhd95Bv?=
 =?us-ascii?Q?yAirKuh4wVnX7srp1MrNFOX766ORKcWOwi7RDLNZFoZkYM8PgRyHBEfDEDd7?=
 =?us-ascii?Q?2koiGSbAkBhDzH9YmN+gS9iLQAc+fh+JXl/+CoCdpapc18jABqsABplNDC93?=
 =?us-ascii?Q?HrP8Dktgm+Je88by2MHF/GFm/6DAG3wLUcz9Y2MPcIRym/xjoYGAv4BpFxim?=
 =?us-ascii?Q?k2OgDZMxnzQkcCQfr5M=3D?=
Content-Type: multipart/alternative;
	boundary="_000_DB9PR07MB71805B7673176FA26D81ABC2CBFA2DB9PR07MB7180eurp_"
MIME-Version: 1.0
X-OriginatorOrg: gilat.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: DB9PR07MB7180.eurprd07.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: e81cbcc3-99f3-4fb5-884b-08dc86471ef4
X-MS-Exchange-CrossTenant-originalarrivaltime: 06 Jun 2024 16:38:39.9824
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 7300b1a3-573a-4010-92a6-1c65cd85e927
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: l6hdPcQfcFGEubT+p3vHuFPhFkm5WdT5VBQWo4spFa0X3yHOXKRjI4xZ+MP5tZBE
X-MS-Exchange-Transport-CrossTenantHeadersStamped: AS8PR07MB7877
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/DB9PR07MB71805B7673176FA26D81ABC2CBFA2%40DB9PR07MB7180.eurprd07.prod.outlook.com>
Precedence: bulk

--_000_DB9PR07MB71805B7673176FA26D81ABC2CBFA2DB9PR07MB7180eurp_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi all,

I'm using logical replication with Postgres 15.2.

When creating logical replication on multiple tables, sometimes the initial=
 sync get stuck for few tables at state srsubstate =3D f.  If I recreate th=
e logical replication again, it may get stuck at srsubstate =3D f  for othe=
r tables, so it does not seems to be table specific.  If course, in the maj=
ority of cases, building the logical replication succeed and all tables are=
 showing srsubstate =3D r.

What might be the reason that logical replication get stuck during initial =
sync at srsubstate =3Df?
We use this view pg_stat_progress_copy to monitor initial sync.  Is it poss=
ible that calling this frequently during initial sync causes some lock not =
to be released?
Is it a Postgres bug?

Thanks!

IMPORTANT - This email and any attachments is intended for the above named =
addressee(s), and may contain information which is confidential or privileg=
ed. If you are not the intended recipient, please inform the sender immedia=
tely and delete this email: you should not copy or use this e-mail for any =
purpose nor disclose its contents to any person.

--_000_DB9PR07MB71805B7673176FA26D81ABC2CBFA2DB9PR07MB7180eurp_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Aptos;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0cm;
	text-align:right;
	direction:rtl;
	unicode-bidi:embed;
	font-size:11.0pt;
	font-family:"Aptos",sans-serif;
	mso-ligatures:standardcontextual;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Aptos",sans-serif;
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-size:11.0pt;
	font-family:"Aptos",sans-serif;}
@page WordSection1
	{size:612.0pt 792.0pt;
	margin:72.0pt 90.0pt 72.0pt 90.0pt;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3D"EN-US" link=3D"#467886" vlink=3D"#96607D" style=3D"word-wrap:=
break-word">
<div class=3D"WordSection1">
<p class=3D"MsoNormal" style=3D"text-align:left;direction:ltr;unicode-bidi:=
embed">Hi all,<o:p></o:p></p>
<p class=3D"MsoNormal" style=3D"text-align:left;direction:ltr;unicode-bidi:=
embed"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal" style=3D"text-align:left;direction:ltr;unicode-bidi:=
embed">I'm using logical replication with Postgres 15.2.&nbsp;
<o:p></o:p></p>
<p class=3D"MsoNormal" style=3D"text-align:left;direction:ltr;unicode-bidi:=
embed"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal" style=3D"text-align:left;direction:ltr;unicode-bidi:=
embed">When creating logical replication on multiple tables, sometimes the =
initial sync get stuck for few tables at state srsubstate =3D f.&nbsp; If I=
 recreate the logical replication again, it
 may get stuck at srsubstate =3D f &nbsp;for other tables, so it does not s=
eems to be table specific. &nbsp;If course, in the majority of cases, build=
ing the logical replication succeed and all tables are showing srsubstate =
=3D r.&nbsp; &nbsp;<o:p></o:p></p>
<p class=3D"MsoNormal" style=3D"text-align:left;direction:ltr;unicode-bidi:=
embed"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal" style=3D"text-align:left;direction:ltr;unicode-bidi:=
embed">What might be the reason that logical replication get stuck during i=
nitial sync at srsubstate =3Df?&nbsp;
<o:p></o:p></p>
<p class=3D"MsoNormal" style=3D"text-align:left;direction:ltr;unicode-bidi:=
embed">We use this view pg_stat_progress_copy to monitor initial sync.&nbsp=
; Is it possible that calling this frequently during initial sync causes so=
me lock not to be released?<o:p></o:p></p>
<p class=3D"MsoNormal" style=3D"text-align:left;direction:ltr;unicode-bidi:=
embed">Is it a Postgres bug?<o:p></o:p></p>
<p class=3D"MsoNormal" style=3D"text-align:left;direction:ltr;unicode-bidi:=
embed"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal" style=3D"text-align:left;direction:ltr;unicode-bidi:=
embed">Thanks!<o:p></o:p></p>
<p class=3D"MsoNormal" style=3D"text-align:left;direction:ltr;unicode-bidi:=
embed">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;
<o:p></o:p></p>
</div>
IMPORTANT - This email and any attachments is intended for the above named =
addressee(s), and may contain information which is confidential or privileg=
ed. If you are not the intended recipient, please inform the sender immedia=
tely and delete this email: you
 should not copy or use this e-mail for any purpose nor disclose its conten=
ts to any person.
</body>
</html>

--_000_DB9PR07MB71805B7673176FA26D81ABC2CBFA2DB9PR07MB7180eurp_--