Re: CommandCounterIncrement versus plan caching

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)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's PostgreSQL training!