Re: ATTACH PARTITION "hangs"

[Keith <keith@xxxxxxxxxxx>]

On Thu, May 11, 2023 at 3:06 PM kyle Hailey <kylelf@xxxxxxxxx> 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.