Hi,
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. The application gets back a error code -201 which seems bogus
and it turned out to be a missing
return (false);
after ecpg_raise(ECPG_SQLSTATE_INVALID_SQL_STATEMENT_NAME)
on line 1756 in execute.c, so the error handling later in the same function
masqueraded this error. But fixing this doesn't solve the main problem
that the query is in the auto-prepared query cache but not is not prepared
on the server if the connection is closed and re-opened.
The same problem may also arise if the same query is used over multiple
simultaneous connections.
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.
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 -dcrp 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 -dcrp 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-18 11:32:26.000000000 +0100
*************** ecpg_auto_prepare(int lineno, const char
*** 465,472 ****
--- 465,481 ----
/* if not found - add the statement to the cache */
if (entNo)
{
+ struct connection *con;
+ struct prepared_statement *prep;
+
ecpg_log("ecpg_auto_prepare on line %d: statement found in cache; entry %d\n", lineno, entNo);
*name = ecpg_strdup(stmtCacheEntries[entNo].stmtID, lineno);
+
+ con = ecpg_get_connection(connection_name);
+ prep = ecpg_find_prepared_statement(*name, con, NULL);
+ /* This prepared name doesn't exist on this connection. */
+ if (!prep && !ECPGprepare(lineno, connection_name, 0, ecpg_strdup(*name, lineno), query))
+ return (false);
}
else
{
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
