From: Greg Nancarrow <gregn4...@gmail.com> > Firstly, in order to perform parallel-safety checks in the case of > partitions, the > patch currently recursively locks/unlocks > (AccessShareLock) each partition during such checks (as each partition may > itself be a partitioned table). Is there a better way of performing the > parallel-safety checks and reducing the locking requirements?
First of all, as you demonstrated the planning time and execution time of parallel insert, I think the increased planning time is negligible when the parallel insert is intentionally used for loading large amount of data. However, it's a problem if the overhead is imposed on OLTP transactions. Does the overhead occur with the default values of max_parallel_workers_per_gather = 2 and max_parall_workers = 8? To avoid this heavy checking during planning, I'm wondering if we can have an attribute in pg_class, something like relhasindexes and relhas triggers. The concerning point is that we have to maintain the accuracy of the value when dropping ancillary objects around the table/partition. > Secondly, I found that when running "make check-world", the > "partition-concurrent-attach" test fails, because it is expecting a partition > constraint to be violated on insert, while an "alter table attach partition > ..." is > concurrently being executed in another transaction. Because of the partition > locking done by the patch's parallel-safety checking code, the insert blocks > on > the exclusive lock held by the "alter table" in the other transaction until > the > transaction ends, so the insert ends up successfully completing (and thus > fails > the test) when the other transaction ends. To overcome this test failure, the > patch code was updated to instead perform a conditional lock on the partition, > and on failure (i.e. because of an exclusive lock held somewhere else), just > assume it's parallel-unsafe because the parallel-safety can't be determined > without blocking on the lock. This is not ideal, but I'm not sure of what > other > approach could be used and I am somewhat reluctant to change that test. If > anybody is familiar with the "partition-concurrent-attach" test, any ideas or > insights would be appreciated. That test looks sane. I think what we should do is to disable parallel operation during that test. It looks like some of other existing test cases disable parallel query by setting max_parallel_workers_per_gather to 0. It's not strange that some tests fail with some configuration. autovacuum is disabled in many places of the regression test. Rather, I don't think we should introduce the trick to use ConditionalLockAcquire(). Otherwise, the insert would be executed in a serial fashion without the user knowing it -- "What? The insert suddenly slowed down multiple times today, and it didn't finish within the planned maintenance window. What's wrong?" Regards Takayuki Tsunakawa