Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s5SGN-005TG4-Bk for pgsql-admin@arkaria.postgresql.org; Fri, 10 May 2024 15:34:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1s5SGK-00GcEW-4h for pgsql-admin@arkaria.postgresql.org; Fri, 10 May 2024 15:34:20 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s5SGJ-00GcEN-Om for pgsql-admin@lists.postgresql.org; Fri, 10 May 2024 15:34:20 +0000 Received: from mail-qk1-x734.google.com ([2607:f8b0:4864:20::734]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1s5SGF-000MsA-VM for pgsql-admin@lists.postgresql.org; Fri, 10 May 2024 15:34:19 +0000 Received: by mail-qk1-x734.google.com with SMTP id af79cd13be357-78ef9ce897bso148403985a.0 for ; Fri, 10 May 2024 08:34:16 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aweber.com; s=2018090500; t=1715355255; x=1715960055; darn=lists.postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=RQXLaRUmm1Qp1NWAJucZvcYWMXMAL3GeXQH8DbTWI4U=; b=pmQbtRziV3TYY5gsldvfeLDjjep8drApWa81Pjjb7Gctt/N8W5mV5oL2L4lKslMdId nHxIxwj8wuvBhAS4lLsibsU3RiU7MFnF8z9WOLfwcZz62HtgOyrPyiSIx4hfCWn9rPOr OXpcGoy/Xs8IsYmp22QSsyT817A+6QYYdLhwgU6butvBPVpt3Kem7jNXyL4xfAIb54JO Ea+6YZk2Uc/78C02zBg8GOjMidJlbYI/E0YCp8x468kAv8Q0vgBY3JRG4tumeEVWmQaC ePxc7/QYZpM/g5AL+2HUO7hfO6nF+x0kItE6rPGM+V5oJMMUq8SHDkd0Si3zuZRM1abK Szjg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1715355255; x=1715960055; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=RQXLaRUmm1Qp1NWAJucZvcYWMXMAL3GeXQH8DbTWI4U=; b=mi2yLF9quq+dxDmVL5r2Z8tA2LIxBzY7vEe9pHyQS0vug88rOp7flR3qwuGt7uqEjS c9/G7iBRbqmztO79iqrgzceFlzpKnl1X+KM85GDhUlrfFlPyMorzkRY2JsO/pN49T8SS Nm0894v6CtH5e5T8Et0rx+zn0zcsXexlz5TEkKoTgbpM96l3iVY9fpfoFRXxKzQWOday em9MJ9zTlRX34778STp2LMTgBplCQ10fxjqTwi9NIW/PEPUc1pGSvr03ujBBKL0tdQK7 6JYE0xuIKFZ+RwdWdOakas6wZGts1t5qwjMvSK3Cl55hZhzl8k7TXNgYIIfYWS8UJyjI eX8Q== X-Gm-Message-State: AOJu0Yw9ksUu//KsHZ2VuT23WCW7gdoRAOrORTLSUbnU/f8FGv4AC1y1 2udAKwDXOmJ4CLipM170u8+axgX05M+9scJadBTzWqmST1BycHi+bc1FBLlk+Vcht/W8KBlUQur xwgY= X-Google-Smtp-Source: AGHT+IH6e7m+46UEJCmMEPfKial5S6xvz24ZZ71H/puzu6LWASIyvnU92T0bwP15V3iQzqNRTWzdwg== X-Received: by 2002:a05:620a:c53:b0:792:ba86:67b with SMTP id af79cd13be357-792c7598d38mr345062785a.21.1715355254893; Fri, 10 May 2024 08:34:14 -0700 (PDT) Received: from smtpclient.apple ([172.59.220.236]) by smtp.gmail.com with ESMTPSA id af79cd13be357-792bf27f915sm190639185a.38.2024.05.10.08.34.14 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Fri, 10 May 2024 08:34:14 -0700 (PDT) From: Chris Hoover Message-Id: <54EEFEB1-D416-4F2E-97BC-19CD82B63487@aweber.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_FE67AB7C-9DBB-442C-8462-C5B376698833" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.500.171.1.1\)) Subject: Re: Adding future partition causes deadlock??? Date: Fri, 10 May 2024 11:34:03 -0400 In-Reply-To: Cc: pgsql-admin@lists.postgresql.org To: Keith Fiske References: X-Mailer: Apple Mail (2.3774.500.171.1.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_FE67AB7C-9DBB-442C-8462-C5B376698833 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Keith, The create and then attach appears to work. However, I agree with you, = I really want to know why the create table as partition fails as a = deadlock since that does not make any sense to me. ``` :5432 @=3D> create table if not exists = .=E2=80=9D_2024-05-15" (LIKE = . INCLUDING All); CREATE TABLE :5432 @=3D> alter table . = attach partition .=E2=80=9D
_2024-05-15" for = values from ( '2024-05-15 00:00:00-04' ) to ( '2024-05-16 00:00:00-04' = ); ALTER TABLE ``` Thanks, Chris Hoover Senior DBA AWeber.com Cell: (803) 528-2269 Email: chrish@aweber.com > On May 10, 2024, at 11:05=E2=80=AFAM, Keith Fiske = wrote: >=20 >=20 >=20 > On Fri, May 10, 2024 at 10:12=E2=80=AFAM Chris Hoover = > wrote: >> Hello, >>=20 >> I've been trying to find out why adding a future partition to a = moderately heavy insert/update table would be stopped by deadlocks. = I've searched in Google and seen others have the same issue, but no real = resolution given. >>=20 >> Our table is partitioned by day and we try to stay 6 partitions = ahead. I have gone as far as to lock the parent table and active = partition in "access exclusive mode" and still get the deadlock. The = application is only dealing with current time data. It does not get = future data, so no activity would be happening in the partitions for = future dates. >>=20 >> Any idea why this is happening and how to resolve it? (BTW, = sometimes the partition create goes through fine, but seems like the = majority fail.) >>=20 >> Here is what we are doing: >> ``` >> :5432 @=3D> select = maintain_partitions.maintain_partitions(); >> NOTICE: v_is_active: [t], v_default_schema: [] >> NOTICE: relation "
_2024-05-10" already exists, skipping >> NOTICE: relation "
_2024-05-11" already exists, skipping >> NOTICE: relation "
_2024-05-12" already exists, skipping >> NOTICE: relation "
_2024-05-13" already exists, skipping >> NOTICE: relation "
_2024-05-14" already exists, skipping >> ERROR: deadlock detected >> DETAIL: Process 32724 waits for ShareRowExclusiveLock on relation = 23478 of database 16404; blocked by process 1015. >> Process 1015 waits for ShareLock on transaction 3387140539; blocked = by process 1055. >> Process 1055 waits for RowExclusiveLock on relation 49192 of database = 16404; blocked by process 32724. >> HINT: See server log for query details. >> CONTEXT: SQL statement "create table if not exists = ."
_2024-05-15" partition of = .
for values from ( '2024-05-15 00:00:00-04' ) to = ( '2024-05-16 00:00:00-04' )" >> PL/pgSQL function = maintain_partitions.maintain_partitions_create_tables(text,text,text,text,= integer,text) line 57 at EXECUTE >> SQL statement "SELECT = maintain_partitions.maintain_partitions_create_tables( >> v_parent_table.schemaname, = v_parent_table.tablename, >> v_table_format, v_partition_by, v_loop_num, = v_partition_schema >> )" >> PL/pgSQL function maintain_partitions.maintain_partitions(integer) = line 51 at PERFORM >> :5432 @=3D> >> ``` >>=20 >> Maintain_partitions is an extension I wrote that dynamically builds = the create table sql for us and then executes it. It takes into account = various requirements we have as a business. But you can see the sql it = built and ran is just a normal create table statement. We run it daily = on multiple databases and this specific db is the only one showing this = issue. >>=20 >> -- >> Thanks, >>=20 >> Chris Hoover >> Senior DBA @ AWeber >> Cell: 803-528-2269 >=20 >=20 > Just curious, have you tried creating the table first without = immediately attaching it as a child table then doing an ALTER TABLE to = attach it? I ask this because that is how I did it in pg_partman and I = have yet to have anyone report this bug. Might just be lucky so far tho. >=20 > Even if that does fix it, would be good to know why this causes a = deadlock. Not sure myself at the moment. >=20 > -- > Keith Fiske > Senior Database Engineer > Crunchy Data - http://crunchydata.com --Apple-Mail=_FE67AB7C-9DBB-442C-8462-C5B376698833 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Keith,

The create and then attach = appears to work.  However, I agree with you, I really want to know = why the create table as partition fails as a deadlock since that does = not make any sense to = me.

```
<server>:5432 = <user>@<db>=3D> create table if not exists = <partition_schema>.=E2=80=9D<table>_2024-05-15" (LIKE = <parent_schema>.<parent_table> INCLUDING = All);
CREATE TABLE
<server>:5432 = <user>@<db>=3D> alter table = <parent_schema>.<parent_table> attach partition = <partition_schema>.=E2=80=9D<table>_2024-05-15" for values = from ( '2024-05-15 00:00:00-04' ) to ( '2024-05-16 00:00:00-04' = );
ALTER TABLE
```

Thanks,


Chris = Hoover
Senior DBA
AWeber.com
Cell: (803) = 528-2269
Email: chrish@aweber.com



On May 10, 2024, at 11:05=E2=80=AF= AM, Keith Fiske <keith.fiske@crunchydata.com> wrote:



On Fri, May 10, 2024 at 10:12=E2=80=AFAM Chris = Hoover <chrish@aweber.com> wrote:
Hello,

I've been trying to find out why = adding a future partition to a moderately heavy insert/update table = would be stopped by deadlocks.  I've searched in Google and seen = others have the same issue, but no real resolution = given.

Our table is partitioned by day and we = try to stay 6 partitions ahead.  I have gone as far as to lock = the parent table and active partition in "access exclusive mode" and = still get the deadlock.  The application is only dealing with = current time data.  It does not get future data, so no activity = would be happening in the partitions for future = dates.

Any idea why this is happening and how = to resolve it?  (BTW, sometimes the partition create goes through = fine, but seems like the majority fail.)

Here = is what we are doing:
```
<server>:5432 = <user>@<db>=3D> select = maintain_partitions.maintain_partitions();
NOTICE:  v_is_active: = [t], v_default_schema: [<partition_schema>]
NOTICE: =  relation "<table>_2024-05-10" already exists, = skipping
NOTICE:  relation "<table>_2024-05-11" already = exists, skipping
NOTICE:  relation "<table>_2024-05-12" = already exists, skipping
NOTICE:  relation = "<table>_2024-05-13" already exists, skipping
NOTICE: =  relation "<table>_2024-05-14" already exists, = skipping
ERROR:  deadlock detected
DETAIL:  Process = 32724 waits for ShareRowExclusiveLock on relation 23478 of database = 16404; blocked by process 1015.
Process 1015 waits for ShareLock on = transaction 3387140539; blocked by process 1055.
Process 1055 waits = for RowExclusiveLock on relation 49192 of database 16404; blocked by = process 32724.
HINT:  See server log for query = details.
CONTEXT:  SQL statement "create table if not exists = <partition_schema>."<table>_2024-05-15" partition of = <parent_schema>.<table> for values from ( '2024-05-15 = 00:00:00-04' ) to ( '2024-05-16 00:00:00-04' )"
PL/pgSQL = function = maintain_partitions.maintain_partitions_create_tables(text,text,text,text,= integer,text) line 57 at EXECUTE
SQL statement "SELECT = maintain_partitions.maintain_partitions_create_tables(
    =                   =  v_parent_table.schemaname, v_parent_table.tablename,
  =                     =  v_table_format, v_partition_by, v_loop_num, = v_partition_schema
              =       )"
PL/pgSQL function = maintain_partitions.maintain_partitions(integer) line 51 at = PERFORM
<server>:5432 <user>@<db>=3D>
```

Maintain_partitions is = an extension I wrote that dynamically builds the create table sql for us = and then executes it.  It takes into account various requirements = we have as a business.  But you can see the sql it built and ran is = just a normal create table statement.  We run it daily on multiple = databases and this specific db is the only one showing this = issue.

-- =
Thanks,

Chris Hoover
Senior DBA = @ AWeber
Cell: 803-528-2269


Just curious, = have you tried creating the table first without immediately attaching it = as a child table then doing an ALTER TABLE to attach it? I ask this = because that is how I did it in pg_partman and I have yet to have anyone = report this bug. Might just be lucky so far = tho.

Even if that does fix it, would be good to = know why this causes a deadlock. Not sure myself at the = moment.

-- =
Keith Fiske
Senior Database = Engineer
Crunchy Data - http://crunchydata.com
=

= --Apple-Mail=_FE67AB7C-9DBB-442C-8462-C5B376698833--