ATTACH PARTITION "hangs"

From: kyle Hailey <kylelf(at)gmail(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: ATTACH PARTITION "hangs"
Date: 2023-05-11 19:05:36
Message-ID: CADsdiQj_AgVzHvWvecghH22tt+ghP70ZOVg2rQsqKtXsSAEm9w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

WHy when I attach a partition , it takes hours to complete (hasn't
completed yet) when it should be immediate?

ALTER TABLE jobs ATTACH PARTITION jobs_23_04_05_week
FOR VALUES FROM ('2023-04-05') TO ('2023-04-12');

I created a constraint to make the ATTACH command work immediately

ALTER TABLE jobs_23_04_05_week
ADD CONSTRAINT jobs_23_04_05_week_constraint
CHECK ( enqueue_time IS NOT NULL AND
enqueue_time >= '2023-04-05'::timestamp without time zone AND
enqueue_time < '2023-04-12'::timestamp without time zone ) ;

Column | Type | Collation | Nullable
|
----------------------+-----------------------------+-----------+----------+-
enqueue_time | timestamp without time zone | | not null |

pg_stat_activity:

Name |Value
----------------+-------------------------
query_time |643.08
trxn_time |643.08
pid |598303
pg_blocking_pids|{}
wait_event |
datid |16489
datname |the_young
pid |598303
leader_pid |
usesysid |16467
usename |steve_jobs
application_name|psql
client_addr |73.158.189.114
client_hostname |
client_port |51778
backend_start |2023-05-11 11:49:42.695 -
xact_start |2023-05-11 11:50:32.185 -
query_start |2023-05-11 11:50:32.185 -
state_change |2023-05-11 11:50:32.185 -
wait_event_type |
wait_event |
state |active
backend_xid |1884897466
backend_xmin |1884897460
query |ALTER TABLE jobs ATTACH P
backend_type |client backend

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message chrisp 2023-05-11 20:45:35 OS rpms needed to support install of postgres 15
Previous Message Matthew Planchard 2023-05-11 17:03:40 Re: Index recreation details with REINDEX TABLE CONCURRENTLY