[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]


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 |


Name            |Value                    
query_time      |643.08                  
trxn_time       |643.08                  
pid             |598303                  
wait_event      |                        
datid           |16489                    
datname         |the_young                    
pid             |598303                  
leader_pid      |                        
usesysid        |16467                    
usename         |steve_jobs                    
client_addr     |          
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.

[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux