Hi, Takahiro Itagaki írta: > Hi, I'm reviewing your patch and have a couple of comments. >
thanks for the review, comments below. > Boszormenyi Zoltan <z...@cybertec.at> wrote: > > >> we have found that auto-prepare causes a problem if the connection >> is closed and re-opened and the previously prepared query is issued >> again. >> > > You didn't attach actual test cases for the bug, so I verified it > by executing the routines twice in ecpg/test/preproc/autoprep.pgc. > The attached "6-pg85-fix-auto-prepare-multiconn-3-test.patch" > is an additional regression test for it. Is it enough for your case? > Yes, my bad that I didn't attach a test case. The modification to the autoprep.pgc is enough to trigger it because the INSERTs are auto-prepared. >> This fix is that after looking up the query and having it found in the cache >> we also have to check whether this query was prepared in the current >> connection. The attached patch implements this fix and solves the problem >> described above. Also, the missing "return false;" is also added to ECPGdo() >> in execute.c. >> > > In addition to the two issues, I found memory leaks by careless calls > of ecpg_strdup() in ecpg_auto_prepare(). Good catch, thanks. I didn't look in ECPGprepare(), so I just copied the statement and the logic from the ELSE branch. > Prepared stetements also might > leak in a error path. Yes, it is true as well, the statement name ("ecpgN") is not freed in the error path in ECPGdo(). However, thinking a little more about this code: con = ecpg_get_connection(connection_name); prep = ecpg_find_prepared_statement(stmtID, con, NULL); if (!prep && !ECPGprepare(...)) I only wanted to call ECPGprepare() in case it wasn't already prepared. ECPGprepare() also checks for the statement being already prepared with ecpg_find_prepared_statement() but in case it exists it DEALLOCATEs the statement and PREPAREs again so there's would be no saving for auto-prepare calling it unconditionally and we are doing a little extra work by calling ecpg_find_prepared_statement() twice. We need a common function shared by ECPGprepare() and ecpg_auto_prepare() to not do extra work in the auto-prepare case. The attached patch implements this and also your leak fixes plus includes your change for the autoprep.pgc regression test. Thanks and best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: "But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil." (Matthew 5:37) - basics of digital technology. "May your kingdom come" - superficial description of plate tectonics ---------------------------------- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH http://www.postgresql.at/
diff -dcrpN pgsql.orig/src/interfaces/ecpg/ecpglib/execute.c pgsql.1/src/interfaces/ecpg/ecpglib/execute.c *** pgsql.orig/src/interfaces/ecpg/ecpglib/execute.c 2010-01-05 18:02:03.000000000 +0100 --- pgsql.1/src/interfaces/ecpg/ecpglib/execute.c 2010-01-18 11:49:17.000000000 +0100 *************** ECPGdo(const int lineno, const int compa *** 1753,1759 **** --- 1753,1762 ---- stmt->command = ecpg_strdup(command, lineno); } else + { ecpg_raise(lineno, ECPG_INVALID_STMT, ECPG_SQLSTATE_INVALID_SQL_STATEMENT_NAME, stmt->command); + return (false); + } } stmt->connection = con; diff -dcrpN pgsql.orig/src/interfaces/ecpg/ecpglib/prepare.c pgsql.1/src/interfaces/ecpg/ecpglib/prepare.c *** pgsql.orig/src/interfaces/ecpg/ecpglib/prepare.c 2010-01-15 12:55:24.000000000 +0100 --- pgsql.1/src/interfaces/ecpg/ecpglib/prepare.c 2010-01-19 11:53:42.000000000 +0100 *************** replace_variables(char **text, int linen *** 99,125 **** return true; } ! /* handle the EXEC SQL PREPARE statement */ ! /* questionmarks is not needed but remians in there for the time being to not change the API */ ! bool ! ECPGprepare(int lineno, const char *connection_name, const bool questionmarks, const char *name, const char *variable) { - struct connection *con; struct statement *stmt; ! struct prepared_statement *this, ! *prev; PGresult *query; - con = ecpg_get_connection(connection_name); - - if (!ecpg_init(con, connection_name, lineno)) - return false; - - /* check if we already have prepared this statement */ - this = ecpg_find_prepared_statement(name, con, &prev); - if (this && !deallocate_one(lineno, ECPG_COMPAT_PGSQL, con, prev, this)) - return false; - /* allocate new statement */ this = (struct prepared_statement *) ecpg_alloc(sizeof(struct prepared_statement), lineno); if (!this) --- 99,111 ---- return true; } ! static bool ! prepare_common(int lineno, struct connection *con, const bool questionmarks, const char *name, const char *variable) { struct statement *stmt; ! struct prepared_statement *this; PGresult *query; /* allocate new statement */ this = (struct prepared_statement *) ecpg_alloc(sizeof(struct prepared_statement), lineno); if (!this) *************** ECPGprepare(int lineno, const char *conn *** 169,174 **** --- 155,182 ---- return true; } + /* handle the EXEC SQL PREPARE statement */ + /* questionmarks is not needed but remians in there for the time being to not change the API */ + bool + ECPGprepare(int lineno, const char *connection_name, const bool questionmarks, const char *name, const char *variable) + { + struct connection *con; + struct prepared_statement *this, + *prev; + + con = ecpg_get_connection(connection_name); + + if (!ecpg_init(con, connection_name, lineno)) + return false; + + /* check if we already have prepared this statement */ + this = ecpg_find_prepared_statement(name, con, &prev); + if (this && !deallocate_one(lineno, ECPG_COMPAT_PGSQL, con, prev, this)) + return false; + + return prepare_common(lineno, con, questionmarks, name, variable); + } + struct prepared_statement * ecpg_find_prepared_statement(const char *name, struct connection * con, struct prepared_statement ** prev_) *************** ecpg_auto_prepare(int lineno, const char *** 465,485 **** /* if not found - add the statement to the cache */ if (entNo) { ecpg_log("ecpg_auto_prepare on line %d: statement found in cache; entry %d\n", lineno, entNo); ! *name = ecpg_strdup(stmtCacheEntries[entNo].stmtID, lineno); } else { ecpg_log("ecpg_auto_prepare on line %d: statement not in cache; inserting\n", lineno); /* generate a statement ID */ ! *name = (char *) ecpg_alloc(STMTID_SIZE, lineno); ! sprintf(*name, "ecpg%d", nextStmtID++); ! if (!ECPGprepare(lineno, connection_name, 0, ecpg_strdup(*name, lineno), query)) return (false); ! if (AddStmtToCache(lineno, *name, connection_name, compat, query) < 0) return (false); } /* increase usage counter */ --- 473,509 ---- /* if not found - add the statement to the cache */ if (entNo) { + char *stmtID; + struct connection *con; + struct prepared_statement *prep; + ecpg_log("ecpg_auto_prepare on line %d: statement found in cache; entry %d\n", lineno, entNo); ! ! stmtID = stmtCacheEntries[entNo].stmtID; ! ! con = ecpg_get_connection(connection_name); ! prep = ecpg_find_prepared_statement(stmtID, con, NULL); ! /* This prepared name doesn't exist on this connection. */ ! if (!prep && !prepare_common(lineno, con, 0, stmtID, query)) ! return (false); ! ! *name = ecpg_strdup(stmtID, lineno); } else { + char stmtID[STMTID_SIZE]; + ecpg_log("ecpg_auto_prepare on line %d: statement not in cache; inserting\n", lineno); /* generate a statement ID */ ! sprintf(stmtID, "ecpg%d", nextStmtID++); ! if (!ECPGprepare(lineno, connection_name, 0, stmtID, query)) return (false); ! if (AddStmtToCache(lineno, stmtID, connection_name, compat, query) < 0) return (false); + + *name = ecpg_strdup(stmtID, lineno); } /* increase usage counter */ diff -dcrpN pgsql.orig/src/interfaces/ecpg/test/expected/preproc-autoprep.c pgsql.1/src/interfaces/ecpg/test/expected/preproc-autoprep.c *** pgsql.orig/src/interfaces/ecpg/test/expected/preproc-autoprep.c 2009-12-16 11:30:27.000000000 +0100 --- pgsql.1/src/interfaces/ecpg/test/expected/preproc-autoprep.c 2010-01-19 11:54:32.000000000 +0100 *************** *** 23,29 **** #line 6 "autoprep.pgc" ! int main() { /* exec sql begin declare section */ --- 23,29 ---- #line 6 "autoprep.pgc" ! static void test(void) { /* exec sql begin declare section */ *************** if (sqlca.sqlwarn[0] == 'W') sqlprint(); *** 246,251 **** --- 246,256 ---- if (sqlca.sqlcode < 0) sqlprint();} #line 64 "autoprep.pgc" + } + + int main() { + test(); + test(); /* retry */ return 0; } diff -dcrpN pgsql.orig/src/interfaces/ecpg/test/expected/preproc-autoprep.stderr pgsql.1/src/interfaces/ecpg/test/expected/preproc-autoprep.stderr *** pgsql.orig/src/interfaces/ecpg/test/expected/preproc-autoprep.stderr 2009-12-16 11:30:27.000000000 +0100 --- pgsql.1/src/interfaces/ecpg/test/expected/preproc-autoprep.stderr 2010-01-19 11:54:33.000000000 +0100 *************** *** 158,160 **** --- 158,320 ---- [NO_PID]: sqlca: code: 0, state: 00000 [NO_PID]: ecpg_finish: connection regress1 closed [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ECPGdebug: set to 1 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ECPGconnect: opening database regress1 on <DEFAULT> port <DEFAULT> + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 21: query: create table T ( Item1 int , Item2 int ); with 0 parameter(s) on connection regress1 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 21: using PQexec + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 21: OK: CREATE TABLE + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_auto_prepare on line 23: statement found in cache; entry 15328 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ECPGprepare on line 23: name ecpg1; query: "insert into T values ( 1 , null )" + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 23: query: insert into T values ( 1 , null ); with 0 parameter(s) on connection regress1 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 23: using PQexecPrepared for "insert into T values ( 1 , null )" + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 23: OK: INSERT 0 1 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_auto_prepare on line 24: statement found in cache; entry 1640 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ECPGprepare on line 24: name ecpg2; query: "insert into T values ( 1 , $1 )" + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 24: query: insert into T values ( 1 , $1 ); with 1 parameter(s) on connection regress1 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 24: using PQexecPrepared for "insert into T values ( 1 , $1 )" + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: free_params on line 24: parameter 1 = 1 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 24: OK: INSERT 0 1 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_auto_prepare on line 26: statement found in cache; entry 1640 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 26: query: insert into T values ( 1 , $1 ); with 1 parameter(s) on connection regress1 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 26: using PQexecPrepared for "insert into T values ( 1 , $1 )" + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: free_params on line 26: parameter 1 = 2 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 26: OK: INSERT 0 1 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ECPGprepare on line 27: name i; query: " insert into T values ( 1 , 2 ) " + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 28: query: insert into T values ( 1 , 2 ) ; with 0 parameter(s) on connection regress1 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 28: using PQexecPrepared for " insert into T values ( 1 , 2 ) " + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 28: OK: INSERT 0 1 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_auto_prepare on line 30: statement found in cache; entry 13056 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ECPGprepare on line 30: name ecpg3; query: "select Item2 from T order by Item2 nulls last" + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 30: query: select Item2 from T order by Item2 nulls last; with 0 parameter(s) on connection regress1 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 30: using PQexecPrepared for "select Item2 from T order by Item2 nulls last" + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 30: correctly got 4 tuples with 1 fields + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_get_data on line 30: RESULT: 1 offset: -1; array: yes + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_get_data on line 30: RESULT: 2 offset: -1; array: yes + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_get_data on line 30: RESULT: 2 offset: -1; array: yes + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_get_data on line 30: RESULT: offset: -1; array: yes + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 37: query: declare C cursor for select Item1 from T; with 0 parameter(s) on connection regress1 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 37: using PQexec + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 37: OK: DECLARE CURSOR + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 39: query: fetch 1 in C; with 0 parameter(s) on connection regress1 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 39: using PQexec + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 39: correctly got 1 tuples with 1 fields + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_get_data on line 39: RESULT: 1 offset: -1; array: yes + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 42: query: close C; with 0 parameter(s) on connection regress1 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 42: using PQexec + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 42: OK: CLOSE CURSOR + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ECPGprepare on line 44: name stmt1; query: "SELECT item2 FROM T ORDER BY item2 NULLS LAST" + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 48: query: declare cur1 cursor for SELECT item2 FROM T ORDER BY item2 NULLS LAST; with 0 parameter(s) on connection regress1 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 48: using PQexec + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 48: OK: DECLARE CURSOR + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 55: query: fetch cur1; with 0 parameter(s) on connection regress1 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 55: using PQexec + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 55: correctly got 1 tuples with 1 fields + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_get_data on line 55: RESULT: 1 offset: -1; array: yes + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 55: query: fetch cur1; with 0 parameter(s) on connection regress1 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 55: using PQexec + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 55: correctly got 1 tuples with 1 fields + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_get_data on line 55: RESULT: 2 offset: -1; array: yes + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 55: query: fetch cur1; with 0 parameter(s) on connection regress1 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 55: using PQexec + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 55: correctly got 1 tuples with 1 fields + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_get_data on line 55: RESULT: 2 offset: -1; array: yes + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 55: query: fetch cur1; with 0 parameter(s) on connection regress1 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 55: using PQexec + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 55: correctly got 1 tuples with 1 fields + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_get_data on line 55: RESULT: offset: -1; array: yes + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 55: query: fetch cur1; with 0 parameter(s) on connection regress1 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 55: using PQexec + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 55: correctly got 0 tuples with 1 fields + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: raising sqlcode 100 on line 55: no data found on line 55 + [NO_PID]: sqlca: code: 100, state: 02000 + [NO_PID]: ecpg_execute on line 60: query: close cur1; with 0 parameter(s) on connection regress1 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 60: using PQexec + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 60: OK: CLOSE CURSOR + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 62: query: drop table T; with 0 parameter(s) on connection regress1 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 62: using PQexec + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_execute on line 62: OK: DROP TABLE + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ECPGdeallocate on line 0: name stmt1 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ECPGdeallocate on line 0: name ecpg3 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ECPGdeallocate on line 0: name i + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ECPGdeallocate on line 0: name ecpg2 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ECPGdeallocate on line 0: name ecpg1 + [NO_PID]: sqlca: code: 0, state: 00000 + [NO_PID]: ecpg_finish: connection regress1 closed + [NO_PID]: sqlca: code: 0, state: 00000 diff -dcrpN pgsql.orig/src/interfaces/ecpg/test/expected/preproc-autoprep.stdout pgsql.1/src/interfaces/ecpg/test/expected/preproc-autoprep.stdout *** pgsql.orig/src/interfaces/ecpg/test/expected/preproc-autoprep.stdout 2009-12-16 11:30:27.000000000 +0100 --- pgsql.1/src/interfaces/ecpg/test/expected/preproc-autoprep.stdout 2010-01-19 11:54:32.000000000 +0100 *************** item[0] = 1 *** 7,9 **** --- 7,18 ---- item[1] = 2 item[2] = 2 item[3] = -1 + item[0] = 1 + item[1] = 2 + item[2] = 2 + item[3] = -1 + i = 1 + item[0] = 1 + item[1] = 2 + item[2] = 2 + item[3] = -1 diff -dcrpN pgsql.orig/src/interfaces/ecpg/test/preproc/autoprep.pgc pgsql.1/src/interfaces/ecpg/test/preproc/autoprep.pgc *** pgsql.orig/src/interfaces/ecpg/test/preproc/autoprep.pgc 2009-12-16 11:30:27.000000000 +0100 --- pgsql.1/src/interfaces/ecpg/test/preproc/autoprep.pgc 2010-01-19 11:49:21.000000000 +0100 *************** *** 5,11 **** /* test automatic prepare for all statements */ EXEC SQL INCLUDE ../regression; ! int main() { EXEC SQL BEGIN DECLARE SECTION; int item[4], ind[4], i = 1; int item1, ind1; --- 5,11 ---- /* test automatic prepare for all statements */ EXEC SQL INCLUDE ../regression; ! static void test(void) { EXEC SQL BEGIN DECLARE SECTION; int item[4], ind[4], i = 1; int item1, ind1; *************** int main() { *** 62,67 **** --- 62,72 ---- EXEC SQL DROP TABLE T; EXEC SQL DISCONNECT ALL; + } + + int main() { + test(); + test(); /* retry */ return 0; }
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers