Re: ALTER TABLE SET STATISTICS requires AccessExclusiveLock

On Saturday 17 July 2010 09:55:37 Simon Riggs wrote:
> On Fri, 2010-07-16 at 23:03 +0200, Andres Freund wrote:
> > Sure its not that bad, but at least it needs to get documented imho.
> > Likely others should chime in here ;-)
>
> Don't understand you. This is a clear bug in join removal, test case
> attached, a minor rework of your original test case.
As shown below the same issue exists in other codepaths that we cant easily fix
in a stable release :-( - so I think documenting it is the only viable action
for the back-branches.

> > What could the join removal path (and similar places) *possibly* do
> > against such a case? Without stopping to use SnapshotNow I dont see
> > any way :-(
> The bug is caused by allowing join removal to work in serializable
> transactions. The fix for 9.0 is easy and clear: disallow join removal
> when planning a query as the second or subsequent query in a
> serializable transaction.
>
> A wider fix might be worth doing for 9.1, not sure.

Unfortunately the same issue exists with constraint exclusion - and we can
hardly disable that for serializable transactions...

CREATE TABLE testconstr(data int);
INSERT INTO testconstr VALUES(1),(10);

T1:
test=# explain analyze SELECT * FROM testconstr WHERE data > 5;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on testconstr (cost=0.00..40.00 rows=800 width=4) (actual
time=0.029..0.032 rows=1 loops=1)
Filter: (data > 5)
Total runtime: 0.097 ms
(3 rows)

test=# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN

--make sure we do have a snapshot
test=# SELECT * FROM pg_class WHERE 0 = 1

T2:
DELETE FROM testconstr WHERE data >= 5;
ALTER TABLE testconstr ADD CONSTRAINT t CHECK(data < 5);

T1:
test=# explain analyze SELECT * FROM testconstr WHERE data > 5;
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.003..0.003 rows=0
loops=1)
One-Time Filter: false
Total runtime: 0.045 ms
(3 rows)

test=# SET constraint_exclusion = false;
SET
test=# explain analyze SELECT * FROM testconstr WHERE data > 5;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on testconstr (cost=0.00..40.00 rows=800 width=4) (actual
time=0.030..0.033 rows=1 loops=1)
Filter: (data > 5)
Total runtime: 0.099 ms
(3 rows)

Thats seems to be an issue that you realistically can hit in production...

I think the same problem exists with inheritance planning - i.e. a child table
added to a relation in T1 while T2 already holds a snapshot but hasnt used
that specific table was created will see the new child. Thats less severe but
still annoying.

Beside using an actual Snapshot in portions of the planner (i.e. stats should
continue using SnapshotNow) I dont really see a fix here.

Andres

Andres