Обсуждение: Re: Mail an JDBC driver
- postgresql-9.2-1002.jdbc4.jar
- postgresql-9.2-1004.jdbc4.jar
- postgresql-9.3-1103.jdbc4.jar
- postgresql-9.4-1202.jdbc4.jar
- postgresql-9.4-1204.jdbc4.jar
- connect to the database and open schemaA. Set the search_path to schemaA
- issue several SQL statements. They all go to schemaA (correct)
- dump schemaA to a backup file (we call pg_dump as external process)
- rename schemaA to schemaB (ALTER SCHEMA schemaA RENAME TO schemaB)
- restore the backup (we call psql as external process) => now we’ve a duplicate of schemaA (but with another OID)
- execute a “SHOW search_path”. The search_path is still set to schemaA
- issue another e.g.: DELETE SQL statement.
- if it is fully qualified (e.g.: DELETE FROM schemaA.table1 WHERE…), the changes correctly were applied to schemaA
- if we rely on the search_path, the changes are now applied to schemaB (e.g.: DELETE FROM table1 WHERE…). The SQL statement goes to the wrong schema!
If you need more information, please let us know.
If you need more information, please let us know.Is the same DB version (and which exact versions) being used in both tests? I strongly suspect no...Can you turn on full statement logging and see if anything unusual shows up?What's in your WHERE clause on the delete - and did you use Statement or PreparedStatement?Having a self-contained test case makes the previous two items less problematic. Also, that general test case can be translated to psql which would help to isolate the problem to either the driver or the database.David J.
hi,
first, thank you guys for the really quick answers J
Some additional infos
1. it is the same PostgreSQL Server version for all tests: 9.2.4 (we also use 9.4.x but did all tests on the same server)
2. the statement is quite simple: “delete from table where id=?” (id is a column with type “integer”). We use a prepared statement.
3. since we could reproduce the problem with the new JDBC driver and could not reproduced it by just replacing the JAR with the older driver (we renamed the file and replaced it in the project), I would think it is a driver problem.
In addition, I executed a “show search_path” before we do all the DELETE statements and it was always at the same schema name (even in the situation where the deletions went to another schema). The search_path in our application always only has 1 schema name (no list)4. we change the search_path by executing a separate prepared Statement “set search_path to myschemaname” for the connection.
5. Additional info: even by changing our statement from
delete from table where id=?”
to
set search_path to myschemaname;delete from table where id=?”
does not help. But it helps to change the statement to
delete from myschemaname.table where id=?”
but since we’ve lots of such SQL statements in our code (I just outlined one of the situations), it’s not that easy to use a fully-qualified path at each and every position. Even worse for calling stored procedures that shall call another stored procedure in the same schema.
I’ll try to find out if something can be found in the logging by turning on statement logging but this may take same time.
A solution with something like a “refreshSearchPath()” method in the driver would also be a viable solution for us. Currently we’re evaluating if we could change all calls to fully-qualified SQL statements. And since the old driver also works fine for our application we will use this older driver version for the next time.
hi,
first, thank you guys for the really quick answers J
Some additional infos
1. it is the same PostgreSQL Server version for all tests: 9.2.4 (we also use 9.4.x but did all tests on the same server)
2. the statement is quite simple: “delete from table where id=?” (id is a column with type “integer”). We use a prepared statement.
3. since we could reproduce the problem with the new JDBC driver and could not reproduced it by just replacing the JAR with the older driver (we renamed the file and replaced it in the project), I would think it is a driver problem.
In addition, I executed a “show search_path” before we do all the DELETE statements and it was always at the same schema name (even in the situation where the deletions went to another schema). The search_path in our application always only has 1 schema name (no list)
4. we change the search_path by executing a separate prepared Statement “set search_path to myschemaname” for the connection.
5. Additional info: even by changing our statement from
delete from table where id=?”
to
set search_path to myschemaname;delete from table where id=?”
does not help. But it helps to change the statement to
delete from myschemaname.table where id=?”
but since we’ve lots of such SQL statements in our code (I just outlined one of the situations), it’s not that easy to use a fully-qualified path at each and every position. Even worse for calling stored procedures that shall call another stored procedure in the same schema.
I’ll try to find out if something can be found in the logging by turning on statement logging but this may take same time.
A solution with something like a “refreshSearchPath()” method in the driver would also be a viable solution for us. Currently we’re evaluating if we could change all calls to fully-qualified SQL statements. And since the old driver also works fine for our application we will use this older driver version for the next time.
br
Michael
From: David G. Johnston [mailto:david.g.johnston@gmail.com]
Sent: Dienstag, 02. August 2016 14:28
To: KUNES Michael
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Mail an JDBC driver
On Tue, Aug 2, 2016 at 3:31 AM, KUNES Michael <Michael.KUNES@frequentis.com> wrote:
If you need more information, please let us know.
Is the same DB version (and which exact versions) being used in both tests? I strongly suspect no...
Can you turn on full statement logging and see if anything unusual shows up?
What's in your WHERE clause on the delete - and did you use Statement or PreparedStatement?
Having a self-contained test case makes the previous two items less problematic. Also, that general test case can be translated to psql which would help to isolate the problem to either the driver or the database.
David J.
If you need more information, please let us know.Is the same DB version (and which exact versions) being used in both tests? I strongly suspect no...Can you turn on full statement logging and see if anything unusual shows up?What's in your WHERE clause on the delete - and did you use Statement or PreparedStatement?Having a self-contained test case makes the previous two items less problematic. Also, that general test case can be translated to psql which would help to isolate the problem to either the driver or the database.David J.
Hi,Maybe we found an issue in the JDBC drivers due to some change. Originally we used postgresql-9.2-1002.jdbc4.jar, now upgraded to postgresql-9.4.1208.jre7.jar. With the older version, everything worked as we expected, with the newer one we had a problem as described below.The described algorithm was implemented, because we need to duplicate a schema and there is no “duplicate schema” command in PostgreSQL.We did test and did NOT see the problem in
- postgresql-9.2-1002.jdbc4.jar
- postgresql-9.2-1004.jdbc4.jar
- postgresql-9.3-1103.jdbc4.jar
we could reproduce the described problem in
- postgresql-9.4-1202.jdbc4.jar
- postgresql-9.4-1204.jdbc4.jar
To be true, the use-case might seems “special”. Here is a description what we’ve done:
- connect to the database and open schemaA. Set the search_path to schemaA
- issue several SQL statements. They all go to schemaA (correct)
- dump schemaA to a backup file (we call pg_dump as external process)
- rename schemaA to schemaB (ALTER SCHEMA schemaA RENAME TO schemaB)
- restore the backup (we call psql as external process) => now we’ve a duplicate of schemaA (but with another OID)
- execute a “SHOW search_path”. The search_path is still set to schemaA
- issue another e.g.: DELETE SQL statement.
- if it is fully qualified (e.g.: DELETE FROM schemaA.table1 WHERE…), the changes correctly were applied to schemaA
- if we rely on the search_path, the changes are now applied to schemaB (e.g.: DELETE FROM table1 WHERE…). The SQL statement goes to the wrong schema!
We could reproduce the issue with about 10 DELETE statements before the schemaA was backup/rename/restored (exact number is hard to determine because of some DELETE CASCADE foreign constraints). But we can say that with 1-2 DELETE statements, we do not face the described problem.
On 2 August 2016 at 03:31, KUNES Michael <Michael.KUNES@frequentis.com> wrote:Hi,Maybe we found an issue in the JDBC drivers due to some change. Originally we used postgresql-9.2-1002.jdbc4.jar, now upgraded to postgresql-9.4.1208.jre7.jar. With the older version, everything worked as we expected, with the newer one we had a problem as described below.The described algorithm was implemented, because we need to duplicate a schema and there is no “duplicate schema” command in PostgreSQL.We did test and did NOT see the problem in
- postgresql-9.2-1002.jdbc4.jar
- postgresql-9.2-1004.jdbc4.jar
- postgresql-9.3-1103.jdbc4.jar
we could reproduce the described problem in
- postgresql-9.4-1202.jdbc4.jar
- postgresql-9.4-1204.jdbc4.jar
To be true, the use-case might seems “special”. Here is a description what we’ve done:
- connect to the database and open schemaA. Set the search_path to schemaA
- issue several SQL statements. They all go to schemaA (correct)
- dump schemaA to a backup file (we call pg_dump as external process)
- rename schemaA to schemaB (ALTER SCHEMA schemaA RENAME TO schemaB)
- restore the backup (we call psql as external process) => now we’ve a duplicate of schemaA (but with another OID)
- execute a “SHOW search_path”. The search_path is still set to schemaA
- issue another e.g.: DELETE SQL statement.
- if it is fully qualified (e.g.: DELETE FROM schemaA.table1 WHERE…), the changes correctly were applied to schemaA
- if we rely on the search_path, the changes are now applied to schemaB (e.g.: DELETE FROM table1 WHERE…). The SQL statement goes to the wrong schema!
We could reproduce the issue with about 10 DELETE statements before the schemaA was backup/rename/restored (exact number is hard to determine because of some DELETE CASCADE foreign constraints). But we can say that with 1-2 DELETE statements, we do not face the described problem.I will bet you that the exact number is 5..That is when we will change your un-named prepared statement to a named prepared statement.
Dave Cramer <pg@fastcrypt.com> writes:
> So I just tried using prepared statements and wasn't able to duplicate
> this. It would be really nice if you could test this against at recent
> version of PostgreSQL, and provide us with a self contained test case?
I think that this is probably affected by this 9.3-era backend change:
Author: Tom Lane <tgl@sss.pgh.pa.us>
Branch: master Release: REL9_3_BR [0d5fbdc15] 2013-01-25 14:14:41 -0500
Change plan caching to honor, not resist, changes in search_path.
In the initial implementation of plan caching, we saved the active
search_path when a plan was first cached, then reinstalled that path
anytime we needed to reparse or replan. The idea of that was to try to
reselect the same referenced objects, in somewhat the same way that views
continue to refer to the same objects in the face of schema or name
changes. Of course, that analogy doesn't bear close inspection, since
holding the search_path fixed doesn't cope with object drops or renames.
Moreover sticking with the old path seems to create more surprises than
it avoids. So instead of doing that, consider that the cached plan depends
on search_path, and force reparse/replan if the active search_path is
different than it was when we last saved the plan.
This gets us fairly close to having "transparency" of plan caching, in the
sense that the cached statement acts the same as if you'd just resubmitted
the original query text for another execution. There are still some corner
cases where this fails though: a new object added in the search path
schema(s) might capture a reference in the query text, but we'd not realize
that and force a reparse. We might try to fix that in the future, but for
the moment it looks too expensive and complicated.
With the 9.2 database, if you cache a DELETE query as a prepared
statement, then it will retain the original search path and continue to
use that if the statement needs to be replanned. Moreover I'm pretty
sure that its notion of "original search path" was defined in terms of
schema OIDs not names, so that the table in the renamed schema would
continue to be targeted.
We got rid of that behavior precisely because it turned out to be more
surprising than useful ... but 9.2 is operating as designed. The apparent
dependency on JDBC version probably has to do with different driver
choices about whether/when to prepare the DELETE statement.
regards, tom lane
If you need more information, please let us know.
If you need more information, please let us know.Is the same DB version (and which exact versions) being used in both tests? I strongly suspect no...Can you turn on full statement logging and see if anything unusual shows up?What's in your WHERE clause on the delete - and did you use Statement or PreparedStatement?Having a self-contained test case makes the previous two items less problematic. Also, that general test case can be translated to psql which would help to isolate the problem to either the driver or the database.David J.
hi,
first, thank you guys for the really quick answers J
Some additional infos
1. it is the same PostgreSQL Server version for all tests: 9.2.4 (we also use 9.4.x but did all tests on the same server)
2. the statement is quite simple: “delete from table where id=?” (id is a column with type “integer”). We use a prepared statement.
3. since we could reproduce the problem with the new JDBC driver and could not reproduced it by just replacing the JAR with the older driver (we renamed the file and replaced it in the project), I would think it is a driver problem.
In addition, I executed a “show search_path” before we do all the DELETE statements and it was always at the same schema name (even in the situation where the deletions went to another schema). The search_path in our application always only has 1 schema name (no list)
4. we change the search_path by executing a separate prepared Statement “set search_path to myschemaname” for the connection.
5. Additional info: even by changing our statement from
delete from table where id=?”
to
set search_path to myschemaname;delete from table where id=?”
does not help. But it helps to change the statement to
delete from myschemaname.table where id=?”
but since we’ve lots of such SQL statements in our code (I just outlined one of the situations), it’s not that easy to use a fully-qualified path at each and every position. Even worse for calling stored procedures that shall call another stored procedure in the same schema.
I’ll try to find out if something can be found in the logging by turning on statement logging but this may take same time.
A solution with something like a “refreshSearchPath()” method in the driver would also be a viable solution for us. Currently we’re evaluating if we could change all calls to fully-qualified SQL statements. And since the old driver also works fine for our application we will use this older driver version for the next time.
br
Michael
From: David G. Johnston [mailto:david.g.johnston@gmail.com]
Sent: Dienstag, 02. August 2016 14:28
To: KUNES Michael
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Mail an JDBC driver
On Tue, Aug 2, 2016 at 3:31 AM, KUNES Michael <Michael.KUNES@frequentis.com> wrote:
If you need more information, please let us know.
Is the same DB version (and which exact versions) being used in both tests? I strongly suspect no...
Can you turn on full statement logging and see if anything unusual shows up?
What's in your WHERE clause on the delete - and did you use Statement or PreparedStatement?
Having a self-contained test case makes the previous two items less problematic. Also, that general test case can be translated to psql which would help to isolate the problem to either the driver or the database.
David J.
If you need more information, please let us know.Is the same DB version (and which exact versions) being used in both tests? I strongly suspect no...Can you turn on full statement logging and see if anything unusual shows up?What's in your WHERE clause on the delete - and did you use Statement or PreparedStatement?Having a self-contained test case makes the previous two items less problematic. Also, that general test case can be translated to psql which would help to isolate the problem to either the driver or the database.David J.
hi,
first, thank you guys for the really quick answers J
Some additional infos
1. it is the same PostgreSQL Server version for all tests: 9.2.4 (we also use 9.4.x but did all tests on the same server)
2. the statement is quite simple: “delete from table where id=?” (id is a column with type “integer”). We use a prepared statement.
3. since we could reproduce the problem with the new JDBC driver and could not reproduced it by just replacing the JAR with the older driver (we renamed the file and replaced it in the project), I would think it is a driver problem.
In addition, I executed a “show search_path” before we do all the DELETE statements and it was always at the same schema name (even in the situation where the deletions went to another schema). The search_path in our application always only has 1 schema name (no list)4. we change the search_path by executing a separate prepared Statement “set search_path to myschemaname” for the connection.
5. Additional info: even by changing our statement from
delete from table where id=?”
to
set search_path to myschemaname;delete from table where id=?”
does not help. But it helps to change the statement to
delete from myschemaname.table where id=?”
but since we’ve lots of such SQL statements in our code (I just outlined one of the situations), it’s not that easy to use a fully-qualified path at each and every position. Even worse for calling stored procedures that shall call another stored procedure in the same schema.
I’ll try to find out if something can be found in the logging by turning on statement logging but this may take same time.
A solution with something like a “refreshSearchPath()” method in the driver would also be a viable solution for us. Currently we’re evaluating if we could change all calls to fully-qualified SQL statements. And since the old driver also works fine for our application we will use this older driver version for the next time.
Hi,Maybe we found an issue in the JDBC drivers due to some change. Originally we used postgresql-9.2-1002.jdbc4.jar, now upgraded to postgresql-9.4.1208.jre7.jar. With the older version, everything worked as we expected, with the newer one we had a problem as described below.The described algorithm was implemented, because we need to duplicate a schema and there is no “duplicate schema” command in PostgreSQL.We did test and did NOT see the problem in
- postgresql-9.2-1002.jdbc4.jar
- postgresql-9.2-1004.jdbc4.jar
- postgresql-9.3-1103.jdbc4.jar
we could reproduce the described problem in
- postgresql-9.4-1202.jdbc4.jar
- postgresql-9.4-1204.jdbc4.jar
To be true, the use-case might seems “special”. Here is a description what we’ve done:
- connect to the database and open schemaA. Set the search_path to schemaA
- issue several SQL statements. They all go to schemaA (correct)
- dump schemaA to a backup file (we call pg_dump as external process)
- rename schemaA to schemaB (ALTER SCHEMA schemaA RENAME TO schemaB)
- restore the backup (we call psql as external process) => now we’ve a duplicate of schemaA (but with another OID)
- execute a “SHOW search_path”. The search_path is still set to schemaA
- issue another e.g.: DELETE SQL statement.
- if it is fully qualified (e.g.: DELETE FROM schemaA.table1 WHERE…), the changes correctly were applied to schemaA
- if we rely on the search_path, the changes are now applied to schemaB (e.g.: DELETE FROM table1 WHERE…). The SQL statement goes to the wrong schema!
We could reproduce the issue with about 10 DELETE statements before the schemaA was backup/rename/restored (exact number is hard to determine because of some DELETE CASCADE foreign constraints). But we can say that with 1-2 DELETE statements, we do not face the described problem.
On 2 August 2016 at 03:31, KUNES Michael <Michael.KUNES@frequentis.com> wrote:Hi,Maybe we found an issue in the JDBC drivers due to some change. Originally we used postgresql-9.2-1002.jdbc4.jar, now upgraded to postgresql-9.4.1208.jre7.jar. With the older version, everything worked as we expected, with the newer one we had a problem as described below.The described algorithm was implemented, because we need to duplicate a schema and there is no “duplicate schema” command in PostgreSQL.We did test and did NOT see the problem in
- postgresql-9.2-1002.jdbc4.jar
- postgresql-9.2-1004.jdbc4.jar
- postgresql-9.3-1103.jdbc4.jar
we could reproduce the described problem in
- postgresql-9.4-1202.jdbc4.jar
- postgresql-9.4-1204.jdbc4.jar
To be true, the use-case might seems “special”. Here is a description what we’ve done:
- connect to the database and open schemaA. Set the search_path to schemaA
- issue several SQL statements. They all go to schemaA (correct)
- dump schemaA to a backup file (we call pg_dump as external process)
- rename schemaA to schemaB (ALTER SCHEMA schemaA RENAME TO schemaB)
- restore the backup (we call psql as external process) => now we’ve a duplicate of schemaA (but with another OID)
- execute a “SHOW search_path”. The search_path is still set to schemaA
- issue another e.g.: DELETE SQL statement.
- if it is fully qualified (e.g.: DELETE FROM schemaA.table1 WHERE…), the changes correctly were applied to schemaA
- if we rely on the search_path, the changes are now applied to schemaB (e.g.: DELETE FROM table1 WHERE…). The SQL statement goes to the wrong schema!
We could reproduce the issue with about 10 DELETE statements before the schemaA was backup/rename/restored (exact number is hard to determine because of some DELETE CASCADE foreign constraints). But we can say that with 1-2 DELETE statements, we do not face the described problem.I will bet you that the exact number is 5..That is when we will change your un-named prepared statement to a named prepared statement.
Dave Cramer <pg@fastcrypt.com> writes:
> So I just tried using prepared statements and wasn't able to duplicate
> this. It would be really nice if you could test this against at recent
> version of PostgreSQL, and provide us with a self contained test case?
I think that this is probably affected by this 9.3-era backend change:
Author: Tom Lane <tgl@sss.pgh.pa.us>
Branch: master Release: REL9_3_BR [0d5fbdc15] 2013-01-25 14:14:41 -0500
Change plan caching to honor, not resist, changes in search_path.
In the initial implementation of plan caching, we saved the active
search_path when a plan was first cached, then reinstalled that path
anytime we needed to reparse or replan. The idea of that was to try to
reselect the same referenced objects, in somewhat the same way that views
continue to refer to the same objects in the face of schema or name
changes. Of course, that analogy doesn't bear close inspection, since
holding the search_path fixed doesn't cope with object drops or renames.
Moreover sticking with the old path seems to create more surprises than
it avoids. So instead of doing that, consider that the cached plan depends
on search_path, and force reparse/replan if the active search_path is
different than it was when we last saved the plan.
This gets us fairly close to having "transparency" of plan caching, in the
sense that the cached statement acts the same as if you'd just resubmitted
the original query text for another execution. There are still some corner
cases where this fails though: a new object added in the search path
schema(s) might capture a reference in the query text, but we'd not realize
that and force a reparse. We might try to fix that in the future, but for
the moment it looks too expensive and complicated.
With the 9.2 database, if you cache a DELETE query as a prepared
statement, then it will retain the original search path and continue to
use that if the statement needs to be replanned. Moreover I'm pretty
sure that its notion of "original search path" was defined in terms of
schema OIDs not names, so that the table in the renamed schema would
continue to be targeted.
We got rid of that behavior precisely because it turned out to be more
surprising than useful ... but 9.2 is operating as designed. The apparent
dependency on JDBC version probably has to do with different driver
choices about whether/when to prepare the DELETE statement.
regards, tom lane
hi Dave,
we can try to make a self-contained test, but this may take some time. We’ve to extract this from our software into a separate project.
Vice versa I can offer to try a test version of a JDBC driver in our environment if you want.
In the meantime we tried this
Connection con = …
conn.unwrap(PGConnection.class).setPrepareThreshold(10);
With this adaptation, the problem is after deletion number 10. So your bet seems to be 100% correct J
But I fear, if we set the Threshold to e.g. Integer.MAX_VALUE this will slow down the performance?
br
Michael
P.S.: hope it is ok that I included the other mail-contributors to my reply. If not, please tell me and I’ll just reply to the mailing list and the sender of the mail.
From: davecramer@gmail.com [mailto:davecramer@gmail.com] On Behalf Of Dave Cramer
Sent: Dienstag, 02. August 2016 15:28
To: KUNES Michael
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Mail an JDBC driver
On 2 August 2016 at 09:19, Dave Cramer <pg@fastcrypt.com> wrote:
On 2 August 2016 at 03:31, KUNES Michael <Michael.KUNES@frequentis.com> wrote:
Hi,
Maybe we found an issue in the JDBC drivers due to some change. Originally we used postgresql-9.2-1002.jdbc4.jar, now upgraded to postgresql-9.4.1208.jre7.jar. With the older version, everything worked as we expected, with the newer one we had a problem as described below.
The described algorithm was implemented, because we need to duplicate a schema and there is no “duplicate schema” command in PostgreSQL.
We did test and did NOT see the problem in
· postgresql-9.2-1002.jdbc4.jar
· postgresql-9.2-1004.jdbc4.jar
· postgresql-9.3-1103.jdbc4.jar
we could reproduce the described problem in
· postgresql-9.4-1202.jdbc4.jar
· postgresql-9.4-1204.jdbc4.jar
To be true, the use-case might seems “special”. Here is a description what we’ve done:
1. connect to the database and open schemaA. Set the search_path to schemaA
2. issue several SQL statements. They all go to schemaA (correct)
3. dump schemaA to a backup file (we call pg_dump as external process)
4. rename schemaA to schemaB (ALTER SCHEMA schemaA RENAME TO schemaB)
5. restore the backup (we call psql as external process) => now we’ve a duplicate of schemaA (but with another OID)
6. execute a “SHOW search_path”. The search_path is still set to schemaA
7. issue another e.g.: DELETE SQL statement.
a. if it is fully qualified (e.g.: DELETE FROM schemaA.table1 WHERE…), the changes correctly were applied to schemaA
b. if we rely on the search_path, the changes are now applied to schemaB (e.g.: DELETE FROM table1 WHERE…). The SQL statement goes to the wrong schema!
We could reproduce the issue with about 10 DELETE statements before the schemaA was backup/rename/restored (exact number is hard to determine because of some DELETE CASCADE foreign constraints). But we can say that with 1-2 DELETE statements, we do not face the described problem.
I will bet you that the exact number is 5..
That is when we will change your un-named prepared statement to a named prepared statement.
Dave Cramer
davec@postgresintl.com
So I just tried using prepared statements and wasn't able to duplicate this. It would be really nice if you could test this against at recent version of PostgreSQL, and provide us with a self contained test case?
Dave Cramer
davec@postgresintl.com
statement, then it will retain the original search path and continue to
use that if the statement needs to be replanned. Moreover I'm pretty
sure that its notion of "original search path" was defined in terms of
schema OIDs not names, so that the table in the renamed schema would
continue to be targeted.
We got rid of that behavior precisely because it turned out to be more
surprising than useful ... but 9.2 is operating as designed. The apparent
dependency on JDBC version probably has to do with different driver
choices about whether/when to prepare the DELETE statement.
hi Dave,
we can try to make a self-contained test, but this may take some time. We’ve to extract this from our software into a separate project.
Vice versa I can offer to try a test version of a JDBC driver in our environment if you want.
In the meantime we tried this
Connection con = …
conn.unwrap(PGConnection.class).setPrepareThreshold(10);
With this adaptation, the problem is after deletion number 10. So your bet seems to be 100% correct J
But I fear, if we set the Threshold to e.g. Integer.MAX_VALUE this will slow down the performance?
br
Michael
P.S.: hope it is ok that I included the other mail-contributors to my reply. If not, please tell me and I’ll just reply to the mailing list and the sender of the mail.
From: davecramer@gmail.com [mailto:davecramer@gmail.com] On Behalf Of Dave Cramer
Sent: Dienstag, 02. August 2016 15:28
To: KUNES Michael
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Mail an JDBC driver
On 2 August 2016 at 09:19, Dave Cramer <pg@fastcrypt.com> wrote:
On 2 August 2016 at 03:31, KUNES Michael <Michael.KUNES@frequentis.com> wrote:
Hi,
Maybe we found an issue in the JDBC drivers due to some change. Originally we used postgresql-9.2-1002.jdbc4.jar, now upgraded to postgresql-9.4.1208.jre7.jar. With the older version, everything worked as we expected, with the newer one we had a problem as described below.
The described algorithm was implemented, because we need to duplicate a schema and there is no “duplicate schema” command in PostgreSQL.
We did test and did NOT see the problem in
· postgresql-9.2-1002.jdbc4.jar
· postgresql-9.2-1004.jdbc4.jar
· postgresql-9.3-1103.jdbc4.jar
we could reproduce the described problem in
· postgresql-9.4-1202.jdbc4.jar
· postgresql-9.4-1204.jdbc4.jar
To be true, the use-case might seems “special”. Here is a description what we’ve done:
1. connect to the database and open schemaA. Set the search_path to schemaA
2. issue several SQL statements. They all go to schemaA (correct)
3. dump schemaA to a backup file (we call pg_dump as external process)
4. rename schemaA to schemaB (ALTER SCHEMA schemaA RENAME TO schemaB)
5. restore the backup (we call psql as external process) => now we’ve a duplicate of schemaA (but with another OID)
6. execute a “SHOW search_path”. The search_path is still set to schemaA
7. issue another e.g.: DELETE SQL statement.
a. if it is fully qualified (e.g.: DELETE FROM schemaA.table1 WHERE…), the changes correctly were applied to schemaA
b. if we rely on the search_path, the changes are now applied to schemaB (e.g.: DELETE FROM table1 WHERE…). The SQL statement goes to the wrong schema!
We could reproduce the issue with about 10 DELETE statements before the schemaA was backup/rename/restored (exact number is hard to determine because of some DELETE CASCADE foreign constraints). But we can say that with 1-2 DELETE statements, we do not face the described problem.
I will bet you that the exact number is 5..
That is when we will change your un-named prepared statement to a named prepared statement.
Dave Cramer
davec@postgresintl.com
So I just tried using prepared statements and wasn't able to duplicate this. It would be really nice if you could test this against at recent version of PostgreSQL, and provide us with a self contained test case?
Dave Cramer
davec@postgresintl.com
Hi,
we change the schema name by extending the file that we create via pg_dump.
- we execute pg_dump to dump the schema into a temp-file
- then we create a new temp file with
o BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
o DROP SCHEMA IF EXISTS " + destinationSchemaName + " CASCADE;"
o ALTER SCHEMA " + sourceSchemaName + " RENAME TO " + destinationSchemaName + ';'
o append the contents from pg_dump
o COMMIT;
- this new temp-file is then restored by spawning psql
At the moment we switched back to the older driver what is 100% fine for our purposes.
And we’re thinking about adapting our SW to
a) always using fully-qualified requests (including some code-cleanup)
b) changing the whole dump/restore algorithm to avoid the renaming of the schema (i.e.: dump schema -> adapt dumped file -> restore schema under its new name)
Even if the solution from our side worked fine for some time, I think we can also improve at our side to get rid of this special use case.
We wanted to inform you mostly to give as much info to improve the driver also in the future J
br
Michael
From: Vladimir Sitnikov [mailto:sitnikov.vladimir@gmail.com]
Sent: Dienstag, 02. August 2016 16:23
To: Dave Cramer; KUNES Michael
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Mail an JDBC driver
Apologies for the long read.
TL;DR: S1, S2, S3 are my proposed solutions.
I would like to hear feedback on S3 if that is the only thing you would read below.
KUNES Michael> rename schemaA to schemaB (ALTER SCHEMA schemaA RENAME TO schemaB)
Michael,
Are you issuing "alter schema rename" via pgjdbc or via psql?
I think current issue is related to https://github.com/pgjdbc/pgjdbc/issues/496 (and hackers thread inside: http://www.postgresql.org/message-id/22921.1358876659@sss.pgh.pa.us )
To my best knowledge client side has no idea if there was a schema change (e.g. alter schema rename, alter table rename, set search_path, etc), so from client perspective, "cache invalidation" is a non-trivial task.
On top of that, PostgreSQL itself has no easy way to tell when the statements need be re-parsed in a generic case. Basically, any DDL can cause statement invalidation (see Tom's example in the hackers link above)
S1) The simplest solution would be to reset the connection pool right after schema duplication.
S2) Second option is to issue a "deallocate all" request via executeUpdate kind of call, however you would have to issue that in each and every connection, and you don't want to "deallocate all" often as it will hurt the performance. Note: "deallocate all" is not yet supported by pgjdbc, however it is not a rocket science.
S3) I wonder if LISTEN/NOTIFY could be reused to track/invalidate statement cache.
For instance, each pgjdbc connection subscribes to "pgjdbc_statement_invalidate" channel. When someone wants to reset the cache, he issues "notify" on the specific channel, and that is propagated to the relevant clients. For instance, as sysadmin did "alter ...", he could issue "notify..." statement and that would transparently renew the statements for all connected pgjdbc clients.
Dave>FYI, setSchema is the correct way to change the search path
Technically speaking, I'm not sure if we should invalidate the cache on each and every `setSchema` call.
1) Why invalidate the cache if application is issuing setSchema with exactly the same schema again and again?
2) setSchema does not support multiple schemas on the path, so for complex paths applications would have to resort to execute...("set ...")
Personally speaking, I wish search_path to be a GUC_REPORT. That is server should send notifications when the value changes over time. Of course "full cache invalidation on search_path change" is not optimal, however the changes should not be often, and that would provide at least some solution to the "wrong statement executed" or "statement executed in the wrong schema" problem.
Vladimir
we change the schema name by extending the file that we create via pg_dump.
hi Dave,
we can try to make a self-contained test, but this may take some time. We’ve to extract this from our software into a separate project.
Vice versa I can offer to try a test version of a JDBC driver in our environment if you want.
In the meantime we tried this
Connection con = …
conn.unwrap(PGConnection.class).setPrepareThreshold(10);
With this adaptation, the problem is after deletion number 10. So your bet seems to be 100% correct J
But I fear, if we set the Threshold to e.g. Integer.MAX_VALUE this will slow down the performance?
br
Michael
P.S.: hope it is ok that I included the other mail-contributors to my reply. If not, please tell me and I’ll just reply to the mailing list and the sender of the mail.
From: davecramer@gmail.com [mailto:davecramer@gmail.com] On Behalf Of Dave Cramer
Sent: Dienstag, 02. August 2016 15:28
To: KUNES Michael
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Mail an JDBC driver
On 2 August 2016 at 09:19, Dave Cramer <pg@fastcrypt.com> wrote:
On 2 August 2016 at 03:31, KUNES Michael <Michael.KUNES@frequentis.com> wrote:
Hi,
Maybe we found an issue in the JDBC drivers due to some change. Originally we used postgresql-9.2-1002.jdbc4.jar, now upgraded to postgresql-9.4.1208.jre7.jar. With the older version, everything worked as we expected, with the newer one we had a problem as described below.
The described algorithm was implemented, because we need to duplicate a schema and there is no “duplicate schema” command in PostgreSQL.
We did test and did NOT see the problem in
· postgresql-9.2-1002.jdbc4.jar
· postgresql-9.2-1004.jdbc4.jar
· postgresql-9.3-1103.jdbc4.jar
we could reproduce the described problem in
· postgresql-9.4-1202.jdbc4.jar
· postgresql-9.4-1204.jdbc4.jar
To be true, the use-case might seems “special”. Here is a description what we’ve done:
1. connect to the database and open schemaA. Set the search_path to schemaA
2. issue several SQL statements. They all go to schemaA (correct)
3. dump schemaA to a backup file (we call pg_dump as external process)
4. rename schemaA to schemaB (ALTER SCHEMA schemaA RENAME TO schemaB)
5. restore the backup (we call psql as external process) => now we’ve a duplicate of schemaA (but with another OID)
6. execute a “SHOW search_path”. The search_path is still set to schemaA
7. issue another e.g.: DELETE SQL statement.
a. if it is fully qualified (e.g.: DELETE FROM schemaA.table1 WHERE…), the changes correctly were applied to schemaA
b. if we rely on the search_path, the changes are now applied to schemaB (e.g.: DELETE FROM table1 WHERE…). The SQL statement goes to the wrong schema!
We could reproduce the issue with about 10 DELETE statements before the schemaA was backup/rename/restored (exact number is hard to determine because of some DELETE CASCADE foreign constraints). But we can say that with 1-2 DELETE statements, we do not face the described problem.
I will bet you that the exact number is 5..
That is when we will change your un-named prepared statement to a named prepared statement.
Dave Cramer
davec@postgresintl.com
So I just tried using prepared statements and wasn't able to duplicate this. It would be really nice if you could test this against at recent version of PostgreSQL, and provide us with a self contained test case?
Dave Cramer
davec@postgresintl.com
statement, then it will retain the original search path and continue to
use that if the statement needs to be replanned. Moreover I'm pretty
sure that its notion of "original search path" was defined in terms of
schema OIDs not names, so that the table in the renamed schema would
continue to be targeted.
We got rid of that behavior precisely because it turned out to be more
surprising than useful ... but 9.2 is operating as designed. The apparent
dependency on JDBC version probably has to do with different driver
choices about whether/when to prepare the DELETE statement.
hi Dave,
we can try to make a self-contained test, but this may take some time. We’ve to extract this from our software into a separate project.
Vice versa I can offer to try a test version of a JDBC driver in our environment if you want.
In the meantime we tried this
Connection con = …
conn.unwrap(PGConnection.class).setPrepareThreshold(10);
With this adaptation, the problem is after deletion number 10. So your bet seems to be 100% correct J
But I fear, if we set the Threshold to e.g. Integer.MAX_VALUE this will slow down the performance?
br
Michael
P.S.: hope it is ok that I included the other mail-contributors to my reply. If not, please tell me and I’ll just reply to the mailing list and the sender of the mail.
From: davecramer@gmail.com [mailto:davecramer@gmail.com] On Behalf Of Dave Cramer
Sent: Dienstag, 02. August 2016 15:28
To: KUNES Michael
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Mail an JDBC driver
On 2 August 2016 at 09:19, Dave Cramer <pg@fastcrypt.com> wrote:
On 2 August 2016 at 03:31, KUNES Michael <Michael.KUNES@frequentis.com> wrote:
Hi,
Maybe we found an issue in the JDBC drivers due to some change. Originally we used postgresql-9.2-1002.jdbc4.jar, now upgraded to postgresql-9.4.1208.jre7.jar. With the older version, everything worked as we expected, with the newer one we had a problem as described below.
The described algorithm was implemented, because we need to duplicate a schema and there is no “duplicate schema” command in PostgreSQL.
We did test and did NOT see the problem in
· postgresql-9.2-1002.jdbc4.jar
· postgresql-9.2-1004.jdbc4.jar
· postgresql-9.3-1103.jdbc4.jar
we could reproduce the described problem in
· postgresql-9.4-1202.jdbc4.jar
· postgresql-9.4-1204.jdbc4.jar
To be true, the use-case might seems “special”. Here is a description what we’ve done:
1. connect to the database and open schemaA. Set the search_path to schemaA
2. issue several SQL statements. They all go to schemaA (correct)
3. dump schemaA to a backup file (we call pg_dump as external process)
4. rename schemaA to schemaB (ALTER SCHEMA schemaA RENAME TO schemaB)
5. restore the backup (we call psql as external process) => now we’ve a duplicate of schemaA (but with another OID)
6. execute a “SHOW search_path”. The search_path is still set to schemaA
7. issue another e.g.: DELETE SQL statement.
a. if it is fully qualified (e.g.: DELETE FROM schemaA.table1 WHERE…), the changes correctly were applied to schemaA
b. if we rely on the search_path, the changes are now applied to schemaB (e.g.: DELETE FROM table1 WHERE…). The SQL statement goes to the wrong schema!
We could reproduce the issue with about 10 DELETE statements before the schemaA was backup/rename/restored (exact number is hard to determine because of some DELETE CASCADE foreign constraints). But we can say that with 1-2 DELETE statements, we do not face the described problem.
I will bet you that the exact number is 5..
That is when we will change your un-named prepared statement to a named prepared statement.
Dave Cramer
davec@postgresintl.com
So I just tried using prepared statements and wasn't able to duplicate this. It would be really nice if you could test this against at recent version of PostgreSQL, and provide us with a self contained test case?
Dave Cramer
davec@postgresintl.com
Hi,
we change the schema name by extending the file that we create via pg_dump.
- we execute pg_dump to dump the schema into a temp-file
- then we create a new temp file with
o BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
o DROP SCHEMA IF EXISTS " + destinationSchemaName + " CASCADE;"
o ALTER SCHEMA " + sourceSchemaName + " RENAME TO " + destinationSchemaName + ';'
o append the contents from pg_dump
o COMMIT;
- this new temp-file is then restored by spawning psql
At the moment we switched back to the older driver what is 100% fine for our purposes.
And we’re thinking about adapting our SW to
a) always using fully-qualified requests (including some code-cleanup)
b) changing the whole dump/restore algorithm to avoid the renaming of the schema (i.e.: dump schema -> adapt dumped file -> restore schema under its new name)
Even if the solution from our side worked fine for some time, I think we can also improve at our side to get rid of this special use case.
We wanted to inform you mostly to give as much info to improve the driver also in the future J
br
Michael
From: Vladimir Sitnikov [mailto:sitnikov.vladimir@gmail.com]
Sent: Dienstag, 02. August 2016 16:23
To: Dave Cramer; KUNES Michael
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Mail an JDBC driver
Apologies for the long read.
TL;DR: S1, S2, S3 are my proposed solutions.
I would like to hear feedback on S3 if that is the only thing you would read below.
KUNES Michael> rename schemaA to schemaB (ALTER SCHEMA schemaA RENAME TO schemaB)
Michael,
Are you issuing "alter schema rename" via pgjdbc or via psql?
I think current issue is related to https://github.com/pgjdbc/pgjdbc/issues/496 (and hackers thread inside: http://www.postgresql.org/message-id/22921.1358876659@sss.pgh.pa.us )
To my best knowledge client side has no idea if there was a schema change (e.g. alter schema rename, alter table rename, set search_path, etc), so from client perspective, "cache invalidation" is a non-trivial task.
On top of that, PostgreSQL itself has no easy way to tell when the statements need be re-parsed in a generic case. Basically, any DDL can cause statement invalidation (see Tom's example in the hackers link above)
S1) The simplest solution would be to reset the connection pool right after schema duplication.
S2) Second option is to issue a "deallocate all" request via executeUpdate kind of call, however you would have to issue that in each and every connection, and you don't want to "deallocate all" often as it will hurt the performance. Note: "deallocate all" is not yet supported by pgjdbc, however it is not a rocket science.
S3) I wonder if LISTEN/NOTIFY could be reused to track/invalidate statement cache.
For instance, each pgjdbc connection subscribes to "pgjdbc_statement_invalidate" channel. When someone wants to reset the cache, he issues "notify" on the specific channel, and that is propagated to the relevant clients. For instance, as sysadmin did "alter ...", he could issue "notify..." statement and that would transparently renew the statements for all connected pgjdbc clients.
Dave>FYI, setSchema is the correct way to change the search path
Technically speaking, I'm not sure if we should invalidate the cache on each and every `setSchema` call.
1) Why invalidate the cache if application is issuing setSchema with exactly the same schema again and again?
2) setSchema does not support multiple schemas on the path, so for complex paths applications would have to resort to execute...("set ...")
Personally speaking, I wish search_path to be a GUC_REPORT. That is server should send notifications when the value changes over time. Of course "full cache invalidation on search_path change" is not optimal, however the changes should not be often, and that would provide at least some solution to the "wrong statement executed" or "statement executed in the wrong schema" problem.
Vladimir
we change the schema name by extending the file that we create via pg_dump.