Re: [HACKERS] Improve the concurency of vacuum full table and select statement on the same relation

2015-10-19 Thread Jinyu
The lock upgrade for vacuum full table tends to cause deadlock with other lock upgrade transaction which is from AccessShareLock to lockmode > AccessShareLock. Tom Lane's concern is that it will cause vacuum full failed after do a lot of work. But If we can always let other transaction failed

Re: [HACKERS] Improve the concurency of vacuum full table and select statement on the same relation

2015-10-17 Thread Jim Nasby
On 10/16/15 10:04 AM, Robert Haas wrote: On Thu, Oct 15, 2015 at 8:28 PM, Jim Nasby wrote: It's just how the authors of pg_repack decided to handle it. It seems pretty reasonable, since you probably don't want an errant DDL statement to cause the rollback of hours or

Re: [HACKERS] Improve the concurency of vacuum full table and select statement on the same relation

2015-10-16 Thread Robert Haas
On Thu, Oct 15, 2015 at 8:28 PM, Jim Nasby wrote: > It's just how the authors of pg_repack decided to handle it. It seems pretty > reasonable, since you probably don't want an errant DDL statement to cause > the rollback of hours or days of pg_repack work. > >

Re: [HACKERS] Improve the concurency of vacuum full table and select statement on the same relation

2015-10-15 Thread Jim Nasby
On 10/13/15 10:18 AM, Jinyu wrote: At 2015-10-12 23:46:12, "Jim Nasby" wrote: On 10/11/15 6:55 AM, Jinyu wrote: Are there other solutions to improve the concurency of vacuum full/cluster and select statement on the same relation? ISTM that if we were going to put

Re: [HACKERS] Improve the concurency of vacuum full table and select statement on the same relation

2015-10-13 Thread Jinyu
>>it's approach to this is to summarily kill anything that attempts DDL on a >>table being repacked. Why? I am confused with it. Could you please explain this? Jinyu Zhang thanks At 2015-10-12 23:46:12, "Jim Nasby" wrote: >On 10/11/15 6:55 AM, Jinyu wrote: >>

Re: [HACKERS] Improve the concurency of vacuum full table and select statement on the same relation

2015-10-12 Thread Jim Nasby
On 10/11/15 6:55 AM, Jinyu wrote: Are there other solutions to improve the concurency of vacuum full/cluster and select statement on the same relation? ISTM that if we were going to put effort into this it makes more sense to pull pg_repack into core. BTW, it's approach to this is to

Re: [HACKERS] Improve the concurency of vacuum full table and select statement on the same relation

2015-10-11 Thread Jinyu
Get it now, thanks. The lock upgrade(from ExclusiveLock to AccessExclusiveLock) may result in deadlocks iff one transaction first takes an AccessShareLock and then takes a lock(lockmode > AccessShareLock) on the same relation. The single SQL statement can't take an AccessShareLock and then

[HACKERS] Improve the concurency of vacuum full table and select statement on the same relation

2015-10-10 Thread Jinyu
Now vacuum full table takes an AccessExclusiveLock on relation at start and select statement takes an AccessShareLock on relation. So 'vacuum full table' blocks select statement on the same table until it is committed and select statement block 'vacuum full table' until it is finished. The

Re: [HACKERS] Improve the concurency of vacuum full table and select statement on the same relation

2015-10-10 Thread Tom Lane
Jinyu writes: > Proposal: vacuum full table takes an ExclusiveLock on relation instead of > AccessExclusiveLock at start. It can' block select statement before call > function "finish_heap_swap". and select statement is safe because vacuum full > table copys tuples from