Hello,
So, I embarked (yesterday) on a weekend project to add a new feature to
Postgres...
I use PHP with persistent connections and always have been bothered that
those very small AJAX queries (usually simple selects returning 1 row)
take more CPU in postgres to parse & plan than to actually execute.
Since those small queries usually come in great numbers, I would like to
PREPARE them beforehand and use php's pg_exec(), (faster than SQL
EXECUTE). Saves about 50% CPU time on the server for those small queries.
However with persistent connections there is a problem : you never know
if the query has already been prepared or not.
Ideally a PHP process would open a persistent connection and find all
queries already prepared, ready to execute...
So :
- Added a system catalog "pg_global_prepared" (one per database actually)
which contains :
- oid of user who created the row
- name of statement
- SQL command for preparing statement
example :
test=# SELECT * FROM pg_global_prepared ;
stmt_owner | stmt_name
| stmt_sql
------------+-----------------+-------------------------------------------------------------------------------------------------------
10 | test | PREPARE test (INTEGER) AS SELECT $1+3;
10 | test_plan_pk | PREPARE test_plan_pk (INTEGER) AS SELECT *
FROM test WHERE id = $1;
10 | test_plan_order | PREPARE test_plan_order (INTEGER) AS
SELECT * FROM test WHERE value < $1 ORDER BY value DESC LIMIT 1;
- Added sql command GLOBAL PREPARE foo (arg types) AS sql query
This inserts a row in the above catalog after having run a standard
"prepare" on the query to test its validity
- Added sql command GLOBAL DEALLOCATE
This removes row(s) from the above catalog, (only those owned by the
current user)
- Messed with EXECUTE (ExecuteQuery) so that :
- if the requested statement is found in session cache, use it (as
usual)
- if not, look into pg_global_prepared to see if there is one of the same
name and created by same user
- if found, use this to PREPARE, then store in session cache, then
execute it
After that I put this logic in FetchPreparedStatement instead so if it is
asked to fetch a non-existing statement for which there is a row in
pg_global_prepared, it will create it.
test=# EXPLAIN ANALYZE EXECUTE test_plan_pk(1);
NOTICE: prepared statement "test_plan_pk" does not exist in local session
cache, now searching pg_global_prepared for a template to create it.
NOTICE: found template for requested statement, executing :
"test_plan_pk" :
NOTICE: PREPARE test_plan_pk (INTEGER) AS SELECT * FROM test WHERE id =
$1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Index Scan using test_pkey on test (cost=0.00..8.28 rows=1 width=8)
(actual time=19.476..19.478 rows=1 loops=1)
Index Cond: (id = $1)
Total runtime: 0.079 ms
(3 lignes)
So, you take whatever persistent connection from a pool and issue an
EXECUTE without worries.
***** Now, the problem :
- EXECUTE, EXPLAIN EXECUTE, EXPLAIN ANALYZE EXECUTE all work
- pg_exec() from php makes it crash
Actually pg_exec() does not use SQL EXECUTE, I think it uses the new
extended query protocol and just sends a message to execute a named
prepared query.
In that case, my code in FetchPreparedStatement crashes :
NOTICE: prepared statement "test_plan_pk" does not exist in local session
cache, now searching pg_global_prepared for a template to create it.
LOG: server process (PID 30692) was terminated by signal 11: Segmentation
fault
LOG: terminating any other active server processes
LOG: all server processes terminated; reinitializing
GDB says it is because CurrentResourceOwner is NULL. Did I forger to
initialize something ? lol.
I'll post more details and complete traceback this afternoon, but here is
the problematic bit of code, this is the code that finds the SQL to
prepare a statement.
Thanks for any suggestion ;)
Relation mycatalog;
HeapTuple tup;
TupleDesc dsc;
NameData stmt_name_data;
ScanKeyData skey[2];
SysScanDesc scan;
Datum datum;
bool found = false;
bool isnull;
const char *sql = "";
namestrcpy(&stmt_name_data, stmt_name);
mycatalog = heap_open(GlobalPreparedRelationId, RowExclusiveLock); <====
crashes here
dsc = RelationGetDescr( mycatalog );
ScanKeyInit(&skey[0],
Anum_pg_global_prepared_stmt_owner,
BTEqualStrategyNumber, F_OIDEQ,
GetUserId());
ScanKeyInit(&skey[1],
Anum_pg_global_prepared_stmt_name,
BTEqualStrategyNumber, F_NAMEEQ,
NameGetDatum(&stmt_name_data));
scan = systable_beginscan(mycatalog, GlobalPreparedIndexId, true,
SnapshotNow, 2, skey);
if( HeapTupleIsValid(tup = systable_getnext(scan)) )
{
datum = heap_getattr( tup, Anum_pg_global_prepared_stmt_sql, dsc, &isnull
);
if( !isnull )
found = true;
}
systable_endscan(scan);
heap_close(mycatalog, RowExclusiveLock);
if( found )
{
int er;
/* found the statement, now prepare it, so this session will have it in
cache for the next EXECUTEs */
sql = DatumGetCString(DirectFunctionCall1(textout, datum));
ereport(NOTICE, (errmsg("found template for requested statement,
executing : \"%s\" :\n%s", stmt_name, sql )));
if (SPI_connect() != SPI_OK_CONNECT)
elog(ERROR, "SPI_connect failed");
er = SPI_execute( sql, false, 0 );
if (SPI_finish() != SPI_OK_FINISH)
elog(ERROR, "SPI_finish failed");
if( er != SPI_OK_UTILITY )
elog(ERROR, "failed to prepare statement, SPI_execute code %d",
er );
// find it again
if (prepared_queries)
entry = (PreparedStatement *) hash_search(prepared_queries, stmt_name,
HASH_FIND, NULL);
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
