"Tom Lane" <[EMAIL PROTECTED]> writes: > One fairly simple answer is to insert a CCI call at the start of > RevalidateCachedPlan. I dislike that solution, at least by itself, > on two grounds: > > * A patch of that form would approximately double the number of CCI > calls involved in executing a plpgsql function; which quite aside from > any performance cost would halve the distance to the > 2^32-commands-per-transaction horizon. We've already heard from people > who ran into that limit, so I don't want to bring it closer.
Wait, shouldn't it be sufficient to do a CCI only in the "if (!plan)" case? Ie, before actually replanning a query? That would only cause an additional CCI the first time through a plpgsql query. Presumably if you're nearing the 4-billion mark it's because you're going through a loop. It's still kind of ugly though. And it wouldn't help any if you're looping around some dynamic SQL. I didn't trace through all your logic so I'm not sure if only doing the CCI if you actually invalidate a previously planned query would help any. > * This would result in executing CCI calls even during stable/immutable > PL functions. I'm not sure that would have any bad semantic side-effects, > but I'm not convinced it wouldn't, either. And it also gives back > whatever command count limit savings we bought when we fixed things > so that stable/immutable functions don't call CCI. Hm, if you have a stable function which looks up some value from a table then would doing a CCI might screw up something like this? postgres=# create table tab(id integer, val text); CREATE TABLE postgres=# insert into tab values (1,'a'); INSERT 0 1 postgres=# insert into tab values (2,'b'); INSERT 0 1 postgres=# insert into tab values (3,'c'); INSERT 0 1 postgres=# create function lookup(integer) returns text as 'select val from tab where id = $1' language sql stable; CREATE FUNCTION postgres=# update tab set val = lookup(id-1); UPDATE 3 postgres=# select * from tab; id | val ----+----- 1 | 2 | a 3 | b (3 rows) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
