Re: ATTACH PARTITION "hangs"

On Thu, May 11, 2023 at 3:06 PM kyle Hailey <kylelf(at)gmail(dot)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_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
>
>
>
>

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.