> Well if you're caching per-connection then it doesn't really matter=20=20
> whether
> you do it on the client side or the server side, it's pretty much=20=20
> exactly the
> same problem.
Actually I thought about doing it on the server since it would then also=
=20=20
work with connection pooling.
Doing it on the client means the client has to maintain state, which
is=20=
=20
not possible in a pool...
> Unsurprisingly most drivers do precisely what you're describing. In
Perl=
=20=20
> DBI
> for example you just change $dbh->prepare("") into=20=20
> $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,=20=
=20
>> 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.=09
True. Languages that keep a long-running context (like application=20=20
servers etc) can do this easily.
Although in the newer versions of PHP, it's not so bad, pconnect seems
to=
=20=20
work (ie. it will issue ROLLBACKs when the script dies, reset
session=20=20
variables like enable_indexscan, etc), so the only remaining problem
seems=
=20=20
to be prepared statements.
And again, adding a method for the application to know if the persistent=
=20=20
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=
=20=20
current transaction if the requested prepared statement does not exist
?=20=
=20
Then the application would issue a PREPARE. It could also raise a=20=20
non-fatal error when the tables have changed (column added, for instance)=
=20=20
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=20=20
> 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.=20=
=20
when the application is started, new queries will come, so the plans will=
=20=20
have to be written to the cache, but then once the cache contains=20=20
everything it needs, it will not be modified that often, so I wouldn't=20=
=20
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,=20=
=20
PG finds it, although of course if a table is altered by adding a
column=20=
=20
for instance, it logically fails).
search_path: I suggested to either put the search_path in the cache
key=20=
=20
along with the SQL string, or force queries to specify schema.table
for=20=
=20
all tables.
It is also possible to shoot one's foot with the current PREPARE (ie.=20=
=20
search_path is used to PREPARE but of course not for EXECUTE), and
also=20=
=20
with plpgsql functions (ie. the search path used to compile the
function=20=
=20
is the one that is active when it is compiled, ie at its first call in
the=
=20=20
current connection, and not the search path that was active when the=20=20
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=3D> SET search_path TO a,public;
test=3D> SELECT * FROM test_search_path();
test_search_path
------------------
This is schema a
test=3D> \q
$ psql test
test=3D> SET search_path TO b,public;
test=3D> SELECT * FROM test_search_path();
test_search_path
------------------
This is schema b
test=3D> SET search_path TO a,public;
test=3D> SELECT * FROM test_search_path();
test_search_path
------------------
This is schema b
--=20
Sent via pgsql-performance mailing list (pgsql-performance@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
|
