Re: ALTER TABLE SET STATISTICS requires AccessExclusiveLock

On Friday 16 July 2010 21:12:33 Simon Riggs wrote:
> On Fri, 2010-07-16 at 20:41 +0200, Andres Freund wrote:
> > You argue above that you cant change SET [NOT] NULL to be less
> > restrictive because it might change plans - isnt that true for some of
> > the above cases as well?
> >
> > For example UNIQUE/PRIMARY might make join removal possible - which could
> > only be valid after "invalid" tuples where deleted earlier in that
> > transaction. Another case which it influences are grouping plans...
>
> This is only for adding a constraint, not removing it. Join removal
> would be possible after the ALTER finishes, but won't change plans
> already in progress. The idea is to minimise the impact, not maximise
> the benefit of the newly added constraint; I don't think we should block
> all queries just because a few might benefit.
Its not about benefit, its about correctness:

CREATE TABLE testsnap(t int);
INSERT INTO testsnap VALUES(1),(1);

T1:
test=# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
Time: 0.853 ms
test=# explain analyze SELECT t1.* FROM testsnap t1 LEFT JOIN testsnap t2 USING(t); QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Merge Left Join (cost=337.49..781.49 rows=28800 width=4) (actual time=0.090..0.118 rows=4 loops=1)
Merge Cond: (t1.t = t2.t)
-> Sort (cost=168.75..174.75 rows=2400 width=4) (actual time=0.049..0.051 rows=2 loops=1)
Sort Key: t1.t
Sort Method: quicksort Memory: 25kB
-> Seq Scan on testsnap t1 (cost=0.00..34.00 rows=2400 width=4) (actual time=0.018..0.023 rows=2 loops=1)
-> Sort (cost=168.75..174.75 rows=2400 width=4) (actual time=0.026..0.033 rows=3 loops=1)
Sort Key: t2.t
Sort Method: quicksort Memory: 25kB
-> Seq Scan on testsnap t2 (cost=0.00..34.00 rows=2400 width=4) (actual time=0.005..0.009 rows=2 loops=1)
Total runtime: 0.279 ms
(11 rows)

T2:
test=# DELETE FROM testsnap;
DELETE 2
Time: 1.184 ms
test=# ALTER TABLE testsnap ADD CONSTRAINT t unique(t);
NOTICE: 00000: ALTER TABLE / ADD UNIQUE will create implicit index "t" for table "testsnap"
LOCATION: DefineIndex, indexcmds.c:471
ALTER TABLE
Time: 45.639 ms

T1:
Time: 1.948 ms
test=# explain analyze SELECT t1.* FROM testsnap t1 LEFT JOIN testsnap t2 USING(t);
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Seq Scan on testsnap t1 (cost=0.00..1.02 rows=2 width=4) (actual time=0.013..0.016 rows=2 loops=1)
Total runtime: 0.081 ms
(2 rows)

Time: 2.004 ms
test=#

boom.

Andres