Patch : Global Prepared Statements

Here is the result of my little experiment, for your viewing pleasure,
lol.
Now it works. Well, it hasn't crashed yet... so I guess I can show it to
people ;)

- Purpose :

Allow PHP (or other languages) users to use prepared statements
(pg_exec()) together with persistent connections, while not bothering
about preparing all those statements when the persistent connection is
first created (you have no way of knowing, from PHP, if your persistent
connection is new or second-hand, by the way).

- What it does :

New commands :
GLOBAL PREPARE, does the same as PREPARE, except for all current and
future sessions FROM THE SAME USER instead of just the current session.
GLOBAL DEALLOCATE does almost what you'd expect.

- Results

Here is a comparison between sending the query as string (oldskool) with
parameters in it, and executing a prepared statement via pg_exec() (which
emits a BIND, not a SQL EXECUTE)

CREATE TABLE test( id SERIAL PRIMARY KEY, value INTEGER );
INSERT INTO test (value) SELECT random()*1000000 FROM
generate_series( 1,100000 );
CREATE INDEX test_value ON test( value );
CREATE TABLE test2 (id INTEGER PRIMARY KEY REFERENCES test( id ) ON DELETE
CASCADE, value2 TEXT );
INSERT INTO test2 SELECT id, (random()*1000000)::TEXT FROM test;
CREATE TABLE test3 (id INTEGER PRIMARY KEY REFERENCES test( id ) ON DELETE
CASCADE, value3 INTEGER );
INSERT INTO test3 SELECT id, random()*1000000 FROM test;

- simple SELECT : SELECT * FROM test WHERE id = $1
=> Server CPU load : -46% (almost cut in half)
=> Queries/s from PHP including PHP overhead : +71%

- three table JOIN with order by/limit :
SELECT * FROM test a NATURAL JOIN test2 b NATURAL JOIN test3 c WHERE
a.value < $1 ORDER BY value DESC LIMIT 1

=> Server CPU load : -84% (ie uses 6 times less CPU)
=> Queries/s from PHP including PHP overhead : +418% (ie 5 times faster)

(By the way, with pg_exec on those simple queries, Postgres beats InnoDB
and MyISAM thoroughly in both queries/s (seen by client) and server CPU
used by query).

Note that the patch has no influence on these results whatsoever.
It just allows a PHP coder to easily use the pure goodness of pg_exec()
without hassle.

- Implementation :

* a new system catalog (I snipped the query texts, but they are stored
entirely).

test=# SELECT * FROM pg_global_prepared ;
stmt_owner | stmt_name | stmt_sql
------------+-------------------+-------------------------------------------
10 | test_plan_order_3 | PREPARE test_plan_order_3 (INTEGER) AS
SEL
10 | test_plan_pk_3 | PREPARE test_plan_pk_3 (INTEGER) AS
SELECT
10 | test_plan_pk | PREPARE test_plan_pk (INTEGER) AS SELECT
*
10 | test_plan_order | PREPARE test_plan_order (INTEGER) AS
SELEC

* GLOBAL PREPARE foo AS SELECT ...
Checks syntax, does a PREPARE, if it works, inserts row in
pg_global_prepared with user OID, statement name, and PREPARE command.

* GLOBAL DEALLOCATE foo
Deletes the line from pg_global_prepared
Note that this does not make all other open connections forget the plan ;)

* In the code :

FetchPreparedStatement() gets a new sister,
FetchOrCreatePreparedStatement().
- try to find statement in the session cache (usual behaviour)
found -> ok, we're done
- if transaction is aborted, byebye
- look in pg_global_prepared for the requested statement name and the
current user ID.
not found -> byebye
- SPI_execute the PREPARE statement
- return the prepared statement to caller

SQL EXECUTE, the BIND handler in postgres.c, and some others got calls to
FetchOrCreatePreparedStatement().
So if you pg_exec() or EXECUTE or EXPLAIN EXECUTE, etc, a statement that
does not exist, the backend will PREPARE it for you if it can find it in
pg_global_prepared.

Reason for the user OID is that independent users mst not be able to
modify each other's prepared statements...

Patch is here (against 8.3.1)
http://home.peufeu.com/pg/

This is certainly not ready for production, lol. But since I had fun
writing this, I'd rather share it.

Regards,
Pierre