Обсуждение: ATTACH PARTITION "hangs"
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_weekFOR 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_weekADD CONSTRAINT jobs_23_04_05_week_constraintCHECK ( enqueue_time IS NOT NULL ANDenqueue_time >= '2023-04-05'::timestamp without time zone ANDenqueue_time < '2023-04-12'::timestamp without time zone ) ;
Column | Type | Collation | Nullable |
----------------------+-----------------------------+-----------+----------+-
enqueue_time | timestamp without time zone | | not null |
----------------------+-----------------------------+-----------+----------+-
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
----------------+-------------------------
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
On Thu, May 11, 2023 at 3:06 PM kyle Hailey <kylelf@gmail.com> wrote:
I created a constraint to make the ATTACH command work immediately
You also need to create any indexes which exist on the parent table onto the incoming partition, otherwise they will need to be created during the attach operation.
Cheers,
Jeff
On Thu, May 11, 2023 at 3:06 PM kyle Hailey <kylelf@gmail.com> wrote:
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_weekFOR VALUES FROM ('2023-04-05') TO ('2023-04-12');I created a constraint to make the ATTACH command work immediatelyALTER TABLE jobs_23_04_05_weekADD CONSTRAINT jobs_23_04_05_week_constraintCHECK ( enqueue_time IS NOT NULL ANDenqueue_time >= '2023-04-05'::timestamp without time zone ANDenqueue_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
Do you happen to have a default table with a lot of data in it? That can cause child table attachment to be significantly delayed because it has to compare all the data in the default to see if it matches the new constraint.
Maybe there is a missing index.
I made all the indexes but eye balling it.
That's' why I asked earlier if there is a compare two objects.
It would be awesome if the ATTACHED said what it was doing, what was missing
On Thu, May 11, 2023 at 3:14 PM Jeff Janes <jeff.janes@gmail.com> wrote:
On Thu, May 11, 2023 at 3:06 PM kyle Hailey <kylelf@gmail.com> wrote:I created a constraint to make the ATTACH command work immediatelyYou also need to create any indexes which exist on the parent table onto the incoming partition, otherwise they will need to be created during the attach operation.Cheers,Jeff