Skip site navigation (1) Skip section navigation (2)

Re: CommandCounterIncrement versus plan caching

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-hackers(at)postgreSQL(dot)org>
Subject: Re: CommandCounterIncrement versus plan caching
Date: 2007-11-30 00:11:19
Message-ID: 87r6i8d23c.fsf@oxford.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-hackers
"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!

In response to

Responses

pgsql-hackers by date

Next:From: Brendan JurdDate: 2007-11-30 00:26:35
Subject: Re: Status report on 8.3 release
Previous:From: Andreas 'ads' ScherbaumDate: 2007-11-30 00:10:35
Subject: Re: Status report on 8.3 release

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group