Re: CommandCounterIncrement versus plan caching

"Tom Lane" <tgl@sss.pgh.pa.us> 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'sPostgreSQL training!