On Wed, Jun 15, 2016 at 2:40 PM, Alvaro Herrera <alvhe...@2ndquadrant.com> wrote: > Kevin Grittner wrote: >> On Wed, Jun 15, 2016 at 1:59 PM, Alvaro Herrera <alvhe...@2ndquadrant.com> >> wrote: > >> > We actually go quite some lengths to support this case, even when it's >> > the opinion of many that we shouldn't. For example VACUUM doesn't try >> > to find index entries using the values in each deleted tuple; instead we >> > remember the TIDs and then scan the indexes (possibly many times) to >> > find entries that match those TIDs -- which is much slower. Yet we do >> > it this way to protect the case that somebody is doing the >> > not-really-IMMUTABLE function. >> > >> > In other words, I don't think we consider the position you argued as >> > acceptable. >> >> What are you saying is unacceptable, and what behavior would be >> acceptable instead? > > The answer "we don't support the situation where you have an index using > an IMMUTABLE function that isn't actually immutable" is not acceptable. > The acceptable solution would be a design that doesn't have that > property as a requisite. > > I think having various executor(/heapam) checks that raise errors when > queries are executed from within ANALYZE is acceptable.
Here is an example of a test case showing that: -- connection 1 drop table if exists t1; create table t1 (c1 int not null); drop table if exists t2; create table t2 (c1 int not null); insert into t1 select generate_series(1, 10000); drop function mysq(i int); create function mysq(i int) returns int language plpgsql immutable as $mysq$ begin return (i * (select c1 from t2)); end $mysq$; insert into t2 values (1); create index t1_c1sq on t1 ((mysq(c1))); begin transaction isolation level repeatable read; select 1; -- connection 2 vacuum analyze verbose t1; delete from t1 where c1 between 1000 and 1999; delete from t1 where c1 = 8000; update t2 set c1 = 1; -- connection 1 analyze verbose t1; -- when run after threshold, STO error occurs The tail end of that, running the analyze once immediately and once after the threshold is: test=# -- connection 1 test=# analyze verbose t1; -- when run after threshold, STO error occurs INFO: analyzing "public.t1" INFO: "t1": scanned 45 of 45 pages, containing 8999 live rows and 1001 dead rows; 8999 rows in sample, 8999 estimated total rows ANALYZE test=# -- connection 1 analyze verbose t1; -- when run after threshold, STO error occurs INFO: analyzing "public.t1" INFO: "t1": scanned 45 of 45 pages, containing 8999 live rows and 1001 dead rows; 8999 rows in sample, 8999 estimated total rows ERROR: snapshot too old CONTEXT: SQL statement "SELECT (i * (select c1 from t2))" PL/pgSQL function mysq(integer) line 3 at RETURN Is there some other behavior which would be preferred? -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
