dropping partitions and concurrent reads

S1, S2 are concurrent sessions:

S1: create table test_par (v int);
S1: create table test_ch1 (check (v > 0 and v <= 2)) inherits (test_par);
S1: create table test_ch2 (check (v > 2 and v <= 4)) inherits (test_par);
S1: begin;
S1: drop table test_ch1 cascade;

S2: select * from test_par where v = 3;

S1: commit;

in S2 I get:

ERROR: could not open relation with OID 66962

This can happen without partitioning by dropping the table you're
reading. That won't ever be better than an ERROR, because obviously you
can't answer the query without the table.

However it seems to be worse in the case of inheritance/partitioning
because you are dropping a table that you don't need to answer the
select query. Also, it seems like a common use-case for partitioning.

Acquiring an ACCESS EXCLUSIVE lock on the parent is a workaround. Should
we do that when dropping a child table? Or can plan invalidation correct
this?

Regards,
Jeff Davis

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> S1, S2 are concurrent sessions:

> S1: create table test_par (v int);
> S1: create table test_ch1 (check (v > 0 and v <= 2)) inherits (test_par);
> S1: create table test_ch2 (check (v > 2 and v <= 4)) inherits (test_par);
> S1: begin;
> S1: drop table test_ch1 cascade;

> S2: select * from test_par where v = 3;

> S1: commit;

> in S2 I get:

> ERROR: could not open relation with OID 66962

Oh? Are you using 8.4+?

regards, tom lane

On Tue, 2009-09-15 at 16:58 -0400, Tom Lane wrote:
> Oh? Are you using 8.4+?

Oops, connecting to the wrong port. 8.5-dev works fine.

Regards,
Jeff Davis