Thread: Lock issues with partitioned table
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I have some trouble understanding the locking policy with partitioned
tables. Here is a simple schema based on a real one:
CREATE DATABASE test;
\c test
CREATE TABLE test(
id integer PRIMARY KEY,
id_dummy integer,
id_part1 integer
);
CREATE INDEX i_fk_test_dummy ON test (id_dummy);
CREATE INDEX i_fk_test_part1 ON test (id_part1);
CREATE TABLE test_1 (
CONSTRAINT test_1_pkey PRIMARY KEY (id),
CONSTRAINT test_1_check_part1 CHECK (id_part1 = 1)
) INHERITS (test);
INSERT INTO test_1
SELECT x, RANDOM(), 1 FROM generate_series(1,1000,1) AS t(x);
CREATE INDEX i_fk_test_1_dummy ON test_1 (id_dummy);
CREATE INDEX i_fk_test_1_part1 ON test_1 (id_part1);
CREATE TABLE test_2 (
CONSTRAINT test_2_pkey PRIMARY KEY (id),
CONSTRAINT test_2_check_part1 CHECK (id_part1 = 2)
) INHERITS (test);
INSERT INTO test_2
SELECT x, RANDOM(), 2 FROM generate_series(1,1000,1) AS t(x);
CREATE INDEX i_fk_test_2_dummy ON test_2 (id_dummy);
CREATE INDEX i_fk_test_2_part1 ON test_2 (id_part1);
ANALYZE;
Explain on "SELECT id FROM test WHERE id_part1=2;"
gives me:
Result (cost=4.33..32.38 rows=1010 width=4)
-> Append (cost=4.33..32.38 rows=1010 width=4)
-> Bitmap Heap Scan on test (cost=4.33..14.88 rows=10 width=4)
Recheck Cond: (id_part1 = 2)
-> Bitmap Index Scan on i_fk_test_part1 (cost=0.00..4.33
rows=10 width=0)
Index Cond: (id_part1 = 2)
-> Seq Scan on test_2 test (cost=0.00..17.50 rows=1000 width=4)
Filter: (id_part1 = 2)
Which looks perfect (but those indexes on table test which is supposed
to be empty so that are useless and add the Bitmap nodes instead of one
costless seqscan).
But pg_locks shows me something I don't understand:
test=# SELECT c.relname, l.locktype, l.mode FROM pg_locks l JOIN
pg_class c ON (c.oid=l.relation) WHERE l.pid=pg_backend_pid();
relname | locktype | mode
----------------------------+----------+-----------------
pg_locks | relation | AccessShareLock
pg_class | relation | AccessShareLock
pg_class_oid_index | relation | AccessShareLock
pg_class_relname_nsp_index | relation | AccessShareLock
test_pkey | relation | AccessShareLock
i_fk_test_dummy | relation | AccessShareLock
i_fk_test_part1 | relation | AccessShareLock
test | relation | AccessShareLock
test_1 | relation | AccessShareLock
test_1_pkey | relation | AccessShareLock
i_fk_test_1_dummy | relation | AccessShareLock
i_fk_test_1_part1 | relation | AccessShareLock
test_2 | relation | AccessShareLock
test_2_pkey | relation | AccessShareLock
i_fk_test_2_dummy | relation | AccessShareLock
i_fk_test_2_part1 | relation | AccessShareLock
(16 rows)
Why do we lock on i_fk_test_dummy, all test_1 related relations and
i_fk_test_2_dummy ?
Actually I don't even understand why we lock on pkeys as well here...
Shouldn't locks only be on tables/indexes that are actually used by the
planner ?
Thanks for lights and feedbacks !
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkwHgBoACgkQxWGfaAgowiIgDACdE8qz/AxHHkUfyuuhKMdxz14j
1QQAn355bPAxfFsuWP1qmjmtH1TGboUF
=LicT
-----END PGP SIGNATURE-----
"Jehan-Guillaume (ioguix) de Rorthais" <ioguix@free.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. Similarly, indexes get locked for the purpose
of inspecting them, whether or not they actually get selected for use in
the plan.
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.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 03/06/2010 16:00, Tom Lane wrote: > "Jehan-Guillaume (ioguix) de Rorthais" <ioguix@free.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-----