Обсуждение: ATTACH PARTITION "hangs"

Поиск
Список
Период
Сортировка

ATTACH PARTITION "hangs"

От
kyle Hailey
Дата:


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     





Re: ATTACH PARTITION "hangs"

От
Jeff Janes
Дата:
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

Re: ATTACH PARTITION "hangs"

От
Keith
Дата:


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_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.

Re: ATTACH PARTITION "hangs"

От
kyle Hailey
Дата:

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 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