Re: Lock issues with partitioned table

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 03/06/2010 16:00, Tom Lane wrote:
> "Jehan-Guillaume (ioguix) de Rorthais" <ioguix(at)free(dot)fr> writes:
>> Shouldn't locks only be on tables/indexes that are actually used by the
>> planner ?
>
> Well, yeah, they are. The planner must take at least AccessShareLock
> on any relation referenced by the query. It might later be able to
> prove that the relation needn't be scanned to deliver the query answer,
> but it first has to lock the relation enough to examine its constraints
> before it can prove that.

Understood, thanks.

> Similarly, indexes get locked for the purpose
> of inspecting them, whether or not they actually get selected for use in
> the plan.

Ok. One question though, as soon as the planer locked the table relation
to check its CHECK contraint then exclude it from its plan (here
test_1), it doesn't need to locks its indexes as well. So I guess the
planer just lock everything first, tables and indexes, THEN, check the
CHECK relations ?

In a partitioned table couldn't it be
1/ lock the table relation
2/ check the CHECK constraint
3.1/ inclusion: lock the indexes
3.2/ exclusion: do nothing

>
> AccessShareLock is a weak enough lock that this generally isn't a
> problem; all that it's doing is ensuring that the table's schema
> doesn't change while we're trying to devise a plan.

Yeah, that's my understanding. However, in the final schema I am messing
with, there's 2 level of partitioning resulting to 409 child tables (!),
each of them with 12 indexes.

A simple request on the top table with correct conditions shows a good
plan, but more than 6500+ locks.

I agree the schema himself is definitely not the best though, and I
already talked about that with its owner...

>
> regards, tom lane

- --
Jehan-Guillaume (ioguix) de Rorthais
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkwHxiMACgkQxWGfaAgowiLdDACfZNumDbI3KVPZoyxXbpGhKCoE
rbIAnRfQmVwm3YF+WGKZ4JWKbGANVtkX
=zfiP
-----END PGP SIGNATURE-----