Re: Cached Query Plans

> Well if you're caching per-connection then it doesn't really matter
> whether
> you do it on the client side or the server side, it's pretty much
> exactly the
> same problem.

    Actually I thought about doing it on the server since it would then also
work with connection pooling.
    Doing it on the client means the client has to maintain state, which is
not possible in a pool...

> Unsurprisingly most drivers do precisely what you're describing. In Perl
> DBI
> for example you just change $dbh->prepare("") into
> $dbh->prepare_cached("")
> and it does exactly what you want. I would expect the PHP drivers to have
> something equivalent.

    Well, PHP doesn't.
    Perhaps I should patch PHP instead...
    Or perhaps this feature should be implemented in pgpool or pgbouncer.

>>     But, using prepared statements with persistent connections is messy,
>> because you never know if the connection is new or not,

> If you were to fix *that* then both this problem and others (such as
> setting up desired SET-parameter values) would go away.

    True. Languages that keep a long-running context (like application
servers etc) can do this easily.
    Although in the newer versions of PHP, it's not so bad, pconnect seems to
work (ie. it will issue ROLLBACKs when the script dies, reset session
variables like enable_indexscan, etc), so the only remaining problem seems
to be prepared statements.
    And again, adding a method for the application to know if the persistent
connection is new or not, will not work in a connection pool...

    Perhaps a GUC flag saying EXECUTE should raise an error but not kill the
current transaction if the requested prepared statement does not exist ?
Then the application would issue a PREPARE. It could also raise a
non-fatal error when the tables have changed (column added, for instance)
so the application can re-issue a PREPARE.

    But I still think it would be cleaner to do it in the server.

    Also, I rethought about what Gregory Stark said :
> The contention on the shared cache is likely to negate much of the
> planning
> savings but I think it would still be a win.

    If a shared plan cache is implemented, it will mostly be read-only, ie.
when the application is started, new queries will come, so the plans will
have to be written to the cache, but then once the cache contains
everything it needs, it will not be modified that often, so I wouldn't
think contention would be such a problem...

> It's not so easy as all that.  Consider search_path.  Consider temp
> tables.

    Temp tables : I thought plan revalidation took care of this ?
    (After testing, it does work, if a temp table is dropped and recreated,
PG finds it, although of course if a table is altered by adding a column
for instance, it logically fails).

    search_path: I suggested to either put the search_path in the cache key
along with the SQL string, or force queries to specify schema.table for
all tables.
    It is also possible to shoot one's foot with the current PREPARE (ie.
search_path is used to PREPARE but of course not for EXECUTE), and also
with plpgsql functions (ie. the search path used to compile the function
is the one that is active when it is compiled, ie at its first call in the
current connection, and not the search path that was active when the
function was defined)...

SET search_path TO DEFAULT;

CREATE SCHEMA a;
CREATE SCHEMA b;

CREATE TABLE a.test( v TEXT );
CREATE TABLE b.test( v TEXT );

INSERT INTO a.test VALUES ('This is schema a');
INSERT INTO b.test VALUES ('This is schema b');

CREATE OR REPLACE FUNCTION test_search_path()
     RETURNS SETOF TEXT
     LANGUAGE plpgsql
     AS
$$
DECLARE
     x TEXT;
BEGIN
     FOR x IN SELECT v FROM test LOOP
         RETURN NEXT x;
     END LOOP;
END;
$$;

test=> SET search_path TO a,public;
test=> SELECT * FROM test_search_path();
  test_search_path
------------------
  This is schema a
test=> \q
$ psql test

test=> SET search_path TO b,public;
test=> SELECT * FROM test_search_path();
  test_search_path
------------------
  This is schema b

test=> SET search_path TO a,public;
test=> SELECT * FROM test_search_path();
  test_search_path
------------------
  This is schema b