first time hacker ;) messing with prepared statements
От | PFC |
---|---|
Тема | first time hacker ;) messing with prepared statements |
Дата | |
Msg-id | op.t8tihwutcigqcu@apollo13.peufeu.com обсуждение исходный текст |
Ответы |
Re: first time hacker ;) messing with prepared statements
(James Mansion <james@mansionfamily.plus.com>)
|
Список | pgsql-hackers |
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 queryThis inserts a row in the above catalog after having run astandard "prepare" on the query to test its validity - Added sql command GLOBAL DEALLOCATEThis 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 ------------------------------------------------------------------------------------------------------------------ IndexScan 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("foundtemplate 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, "failedto prepare statement, SPI_execute code %d", er );// find it againif (prepared_queries) entry = (PreparedStatement*) hash_search(prepared_queries, stmt_name, HASH_FIND, NULL); }
В списке pgsql-hackers по дате отправления:
Следующее
От: James MansionДата:
Сообщение: Re: first time hacker ;) messing with prepared statements