Lock issues with partitioned table

From: "Jehan-Guillaume (ioguix) de Rorthais" <ioguix(at)free(dot)fr>
To: pgsql-general(at)postgresql(dot)org
Subject: Lock issues with partitioned table
Date: 2010-06-03 10:12:48
Message-ID: 4C078020.4080606@free.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Leonardo F 2010-06-03 10:36:24 Auto-partitioning in COPY
Previous Message Rodger Donaldson 2010-06-03 09:43:50 Re: What Linux edition we should chose?