Thread: Possible bug related to primary keys autogeneration

Hello, I have an issue regarding the retrieval of autogenerated keys
using JDBC.

I am running both the client and the server on windows; the client using
the JDBC driver 9.4.1208 and the server has version "PostgreSQL 9.5.3 on
x86_64-pc-mingw64, compiled by gcc.exe (Rev5, Built by MSYS2 project)
4.9.2, 64-bit".

I have the following schema:

CREATE TABLE Documents (
   id SERIAL,
   name VARCHAR(100),
   CONSTRAINT PK_Documents PRIMARY KEY (id)
);

and the following Java code:

try (Connection connection =
DriverManager.getConnection("jdbc:postgresql://localhost/sampledb",
"sampledb", "")) {
     connection.setAutoCommit(false);
     try (PreparedStatement ps = connection.prepareStatement("INSERT
INTO Documents (name) VALUES (?)", new String[]{"ID"})) {
         ps.setString(1, "DocName");
         ps.executeUpdate();

         try (ResultSet generatedKeys = ps.getGeneratedKeys()) {
             if (!generatedKeys.next()) {
                 throw new RuntimeException("Should have been able to
retrieve the generated keys");
             }
             int generatedKey = generatedKeys.getInt(1);
             System.out.println("The generated key is " + generatedKey);
         }
     }
}

I get the following exception:
Exception in thread "main" org.postgresql.util.PSQLException: ERROR:
column "ID" does not exist

Debugging a bit, the issue seems to be in PgConnection.java, line 1641:
since "escape" is set to "true", the driver is adding RETURNING "ID",
quoting the column name. Since the column name's case is different
between the CREATE TABLE statement and the code, PostgreSQL generates an
exception. In fact, if I use new String[]{"id"}, the program works.

I'm wondering whether this can be classified as a bug, or is
intentional. The driver doesn't quote column names generally; it seems
to me that this behavior is a bit annoying. However, I have no idea what
the implications of removing the escaping would be. I do have a
workaround for the moment, but I would like to know whether this is
something that can be changed or not.

I have uploaded a complete, minimal project which includes the code
above at https://github.com/andreabergia/psql-jdbc-possible-bug-report.

Thanks for your time, and for your excellent product.

--
Andrea Bergia
List S.p.A.

Being consistent with cases is generally good advice, but unfortunately my code has to run on multiple databases and it's very hard to control cases in all the code.

Besides, it seems to me that the behavior is inconsistent. I can use whatever case I want in the INSERT INTO clause:

INSERT INTO Documents (name) VALUES ('abc');
INSERT INTO Documents (NAME) VALUES ('abc');
INSERT INTO Documents (NaMe) VALUES ('abc'); but when using the "returning" clause, I have to be careful about my identifiers... Besides, if the driver wasn't quoting the RETURNING clause, everything would work well (as I have tested using the debugger). So, any chance that can be changed in the driver?

This is an artifact of how postgresql treats case.

PostgreSQL folds to lower case. short version "do not use UPPER CASE identifiers"

If you are intent on using upper case you have to use new String[]{"\"ID\""}) to force upper case (to be honest I'm not even sure that would work)

as I said don't use UPPER CASE identifiers

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

On 15 July 2016 at 10:09, Andrea Bergia <a.bergia@list-group.com> wrote:

Hello, I have an issue regarding the retrieval of autogenerated keys using JDBC.

I am running both the client and the server on windows; the client using the JDBC driver 9.4.1208 and the server has version "PostgreSQL 9.5.3 on x86_64-pc-mingw64, compiled by gcc.exe (Rev5, Built by MSYS2 project) 4.9.2, 64-bit".

I have the following schema:

CREATE TABLE Documents (
  id SERIAL,
  name VARCHAR(100),
  CONSTRAINT PK_Documents PRIMARY KEY (id)
);

and the following Java code:

try (Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost/sampledb", "sampledb", "")) {
    connection.setAutoCommit(false);
    try (PreparedStatement ps = connection.prepareStatement("INSERT INTO Documents (name) VALUES (?)", new String[]{"ID"})) {
        ps.setString(1, "DocName");
        ps.executeUpdate();

        try (ResultSet generatedKeys = ps.getGeneratedKeys()) {
            if (!generatedKeys.next()) {
                throw new RuntimeException("Should have been able to retrieve the generated keys");
            }
            int generatedKey = generatedKeys.getInt(1);
            System.out.println("The generated key is " + generatedKey);
        }
    }
}

I get the following exception:
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: column "ID" does not exist

Debugging a bit, the issue seems to be in PgConnection.java, line 1641: since "escape" is set to "true", the driver is adding RETURNING "ID", quoting the column name. Since the column name's case is different between the CREATE TABLE statement and the code, PostgreSQL generates an exception. In fact, if I use new String[]{"id"}, the program works.

I'm wondering whether this can be classified as a bug, or is intentional. The driver doesn't quote column names generally; it seems to me that this behavior is a bit annoying. However, I have no idea what the implications of removing the escaping would be. I do have a workaround for the moment, but I would like to know whether this is something that can be changed or not.

I have uploaded a complete, minimal project which includes the code above at https://github.com/andreabergia/psql-jdbc-possible-bug-report.

Thanks for your time, and for your excellent product.

--
Andrea Bergia
List S.p.A.



--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

-- 
Andrea Bergia
List S.p.A.

Alright, I can understand the rationale around the behavior (and indeed "This isn't likely to be popular" ;-)).
I'll go with a workaround then. Thanks for the very quick feedback!

as the comments around 

https://github.com/pgjdbc/pgjdbc/blob/7311b4ef160231780323573911c1543d0515340b/pgjdbc/src/main/java/org/postgresql/jdbc/PgStatement.java#L1205

suggest if escape is turned on we will quote the returning. The reason for this is to allow people who really want ID returned as opposed to id.

this is unlikely to be changed.

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

On 15 July 2016 at 10:41, Andrea Bergia <a.bergia@list-group.com> wrote:

Being consistent with cases is generally good advice, but unfortunately my code has to run on multiple databases and it's very hard to control cases in all the code.

Besides, it seems to me that the behavior is inconsistent. I can use whatever case I want in the INSERT INTO clause:

INSERT INTO Documents (name) VALUES ('abc');
INSERT INTO Documents (NAME) VALUES ('abc');
INSERT INTO Documents (NaMe) VALUES ('abc'); but when using the "returning" clause, I have to be careful about my identifiers... Besides, if the driver wasn't quoting the RETURNING clause, everything would work well (as I have tested using the debugger). So, any chance that can be changed in the driver?

On 15/07/2016 16:30, Dave Cramer wrote:

This is an artifact of how postgresql treats case.

PostgreSQL folds to lower case. short version "do not use UPPER CASE identifiers"

If you are intent on using upper case you have to use new String[]{"\"ID\""}) to force upper case (to be honest I'm not even sure that would work)

as I said don't use UPPER CASE identifiers

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

On 15 July 2016 at 10:09, Andrea Bergia <a.bergia@list-group.com> wrote:

Hello, I have an issue regarding the retrieval of autogenerated keys using JDBC.

I am running both the client and the server on windows; the client using the JDBC driver 9.4.1208 and the server has version "PostgreSQL 9.5.3 on x86_64-pc-mingw64, compiled by gcc.exe (Rev5, Built by MSYS2 project) 4.9.2, 64-bit".

I have the following schema:

CREATE TABLE Documents (
  id SERIAL,
  name VARCHAR(100),
  CONSTRAINT PK_Documents PRIMARY KEY (id)
);

and the following Java code:

try (Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost/sampledb", "sampledb", "")) {
    connection.setAutoCommit(false);
    try (PreparedStatement ps = connection.prepareStatement("INSERT INTO Documents (name) VALUES (?)", new String[]{"ID"})) {
        ps.setString(1, "DocName");
        ps.executeUpdate();

        try (ResultSet generatedKeys = ps.getGeneratedKeys()) {
            if (!generatedKeys.next()) {
                throw new RuntimeException("Should have been able to retrieve the generated keys");
            }
            int generatedKey = generatedKeys.getInt(1);
            System.out.println("The generated key is " + generatedKey);
        }
    }
}

I get the following exception:
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: column "ID" does not exist

Debugging a bit, the issue seems to be in PgConnection.java, line 1641: since "escape" is set to "true", the driver is adding RETURNING "ID", quoting the column name. Since the column name's case is different between the CREATE TABLE statement and the code, PostgreSQL generates an exception. In fact, if I use new String[]{"id"}, the program works.

I'm wondering whether this can be classified as a bug, or is intentional. The driver doesn't quote column names generally; it seems to me that this behavior is a bit annoying. However, I have no idea what the implications of removing the escaping would be. I do have a workaround for the moment, but I would like to know whether this is something that can be changed or not.

I have uploaded a complete, minimal project which includes the code above at https://github.com/andreabergia/psql-jdbc-possible-bug-report.

Thanks for your time, and for your excellent product.

--
Andrea Bergia
List S.p.A.



--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

-- 
Andrea Bergia
List S.p.A.

-- 
Andrea Bergia
List S.p.A.

I've noticed that everything works as expected if I use:

connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

Unfortunately, this code does not work with Oracle, which is why I started testing the other version (the one with the specified column names). After this discussion, I can't think of any solution other than maintaining two version of my piece of code: one for Oracle, and one PostgreSQL, as I don't see Oracle changing their driver either. :-) It's not a really big deal.

Thanks.

On Fri, Jul 15, 2016 at 10:41 AM, Andrea Bergia <a.bergia@list-group.com> wrote:

Being consistent with cases is generally good advice, but unfortunately my code has to run on multiple databases and it's very hard to control cases in all the code.

Besides, it seems to me that the behavior is inconsistent. I can use whatever case I want in the INSERT INTO clause:

INSERT INTO Documents (name) VALUES ('abc');

​If you were to write that "INSERT INTO Documents (name) VALUES ('abc') RETURNING ID" you wouldn't have a problem because the server is going to lowercase everything for you anyway.  But as soon as you ask Java to append the returning clause for you it doesn't choose to apply PostgreSQL's case normalization rules but instead assumes that the case you provided in the code is the case that you desire - and adds quotes to enforce that decision.  This is nice when you use quote-required identifiers since you don't have to go escaping them in Java.  Maybe a PostgreSQL JDBC implementation could do this but I don't see it happening in this driver.

I could see maybe having a driver config to toggle this behavior...the specifics would be important.

David J.

-- 
Andrea Bergia
List S.p.A.

Being consistent with cases is generally good advice, but unfortunately my code has to run on multiple databases and it's very hard to control cases in all the code.

Besides, it seems to me that the behavior is inconsistent. I can use whatever case I want in the INSERT INTO clause:

INSERT INTO Documents (name) VALUES ('abc');
INSERT INTO Documents (NAME) VALUES ('abc');
INSERT INTO Documents (NaMe) VALUES ('abc'); but when using the "returning" clause, I have to be careful about my identifiers... Besides, if the driver wasn't quoting the RETURNING clause, everything would work well (as I have tested using the debugger). So, any chance that can be changed in the driver?

This is an artifact of how postgresql treats case.

PostgreSQL folds to lower case. short version "do not use UPPER CASE identifiers"

If you are intent on using upper case you have to use new String[]{"\"ID\""}) to force upper case (to be honest I'm not even sure that would work)

as I said don't use UPPER CASE identifiers

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

On 15 July 2016 at 10:09, Andrea Bergia <a.bergia@list-group.com> wrote:

Hello, I have an issue regarding the retrieval of autogenerated keys using JDBC.

I am running both the client and the server on windows; the client using the JDBC driver 9.4.1208 and the server has version "PostgreSQL 9.5.3 on x86_64-pc-mingw64, compiled by gcc.exe (Rev5, Built by MSYS2 project) 4.9.2, 64-bit".

I have the following schema:

CREATE TABLE Documents (
  id SERIAL,
  name VARCHAR(100),
  CONSTRAINT PK_Documents PRIMARY KEY (id)
);

and the following Java code:

try (Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost/sampledb", "sampledb", "")) {
    connection.setAutoCommit(false);
    try (PreparedStatement ps = connection.prepareStatement("INSERT INTO Documents (name) VALUES (?)", new String[]{"ID"})) {
        ps.setString(1, "DocName");
        ps.executeUpdate();

        try (ResultSet generatedKeys = ps.getGeneratedKeys()) {
            if (!generatedKeys.next()) {
                throw new RuntimeException("Should have been able to retrieve the generated keys");
            }
            int generatedKey = generatedKeys.getInt(1);
            System.out.println("The generated key is " + generatedKey);
        }
    }
}

I get the following exception:
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: column "ID" does not exist

Debugging a bit, the issue seems to be in PgConnection.java, line 1641: since "escape" is set to "true", the driver is adding RETURNING "ID", quoting the column name. Since the column name's case is different between the CREATE TABLE statement and the code, PostgreSQL generates an exception. In fact, if I use new String[]{"id"}, the program works.

I'm wondering whether this can be classified as a bug, or is intentional. The driver doesn't quote column names generally; it seems to me that this behavior is a bit annoying. However, I have no idea what the implications of removing the escaping would be. I do have a workaround for the moment, but I would like to know whether this is something that can be changed or not.

I have uploaded a complete, minimal project which includes the code above at https://github.com/andreabergia/psql-jdbc-possible-bug-report.

Thanks for your time, and for your excellent product.

--
Andrea Bergia
List S.p.A.



--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

-- 
Andrea Bergia
List S.p.A.

Alright, I can understand the rationale around the behavior (and indeed "This isn't likely to be popular" ;-)).
I'll go with a workaround then. Thanks for the very quick feedback!

as the comments around 

https://github.com/pgjdbc/pgjdbc/blob/7311b4ef160231780323573911c1543d0515340b/pgjdbc/src/main/java/org/postgresql/jdbc/PgStatement.java#L1205

suggest if escape is turned on we will quote the returning. The reason for this is to allow people who really want ID returned as opposed to id.

this is unlikely to be changed.

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

On 15 July 2016 at 10:41, Andrea Bergia <a.bergia@list-group.com> wrote:

Being consistent with cases is generally good advice, but unfortunately my code has to run on multiple databases and it's very hard to control cases in all the code.

Besides, it seems to me that the behavior is inconsistent. I can use whatever case I want in the INSERT INTO clause:

INSERT INTO Documents (name) VALUES ('abc');
INSERT INTO Documents (NAME) VALUES ('abc');
INSERT INTO Documents (NaMe) VALUES ('abc'); but when using the "returning" clause, I have to be careful about my identifiers... Besides, if the driver wasn't quoting the RETURNING clause, everything would work well (as I have tested using the debugger). So, any chance that can be changed in the driver?

On 15/07/2016 16:30, Dave Cramer wrote:

This is an artifact of how postgresql treats case.

PostgreSQL folds to lower case. short version "do not use UPPER CASE identifiers"

If you are intent on using upper case you have to use new String[]{"\"ID\""}) to force upper case (to be honest I'm not even sure that would work)

as I said don't use UPPER CASE identifiers

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

On 15 July 2016 at 10:09, Andrea Bergia <a.bergia@list-group.com> wrote:

Hello, I have an issue regarding the retrieval of autogenerated keys using JDBC.

I am running both the client and the server on windows; the client using the JDBC driver 9.4.1208 and the server has version "PostgreSQL 9.5.3 on x86_64-pc-mingw64, compiled by gcc.exe (Rev5, Built by MSYS2 project) 4.9.2, 64-bit".

I have the following schema:

CREATE TABLE Documents (
  id SERIAL,
  name VARCHAR(100),
  CONSTRAINT PK_Documents PRIMARY KEY (id)
);

and the following Java code:

try (Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost/sampledb", "sampledb", "")) {
    connection.setAutoCommit(false);
    try (PreparedStatement ps = connection.prepareStatement("INSERT INTO Documents (name) VALUES (?)", new String[]{"ID"})) {
        ps.setString(1, "DocName");
        ps.executeUpdate();

        try (ResultSet generatedKeys = ps.getGeneratedKeys()) {
            if (!generatedKeys.next()) {
                throw new RuntimeException("Should have been able to retrieve the generated keys");
            }
            int generatedKey = generatedKeys.getInt(1);
            System.out.println("The generated key is " + generatedKey);
        }
    }
}

I get the following exception:
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: column "ID" does not exist

Debugging a bit, the issue seems to be in PgConnection.java, line 1641: since "escape" is set to "true", the driver is adding RETURNING "ID", quoting the column name. Since the column name's case is different between the CREATE TABLE statement and the code, PostgreSQL generates an exception. In fact, if I use new String[]{"id"}, the program works.

I'm wondering whether this can be classified as a bug, or is intentional. The driver doesn't quote column names generally; it seems to me that this behavior is a bit annoying. However, I have no idea what the implications of removing the escaping would be. I do have a workaround for the moment, but I would like to know whether this is something that can be changed or not.

I have uploaded a complete, minimal project which includes the code above at https://github.com/andreabergia/psql-jdbc-possible-bug-report.

Thanks for your time, and for your excellent product.

--
Andrea Bergia
List S.p.A.



--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

-- 
Andrea Bergia
List S.p.A.

-- 
Andrea Bergia
List S.p.A.

I've noticed that everything works as expected if I use:

connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

Unfortunately, this code does not work with Oracle, which is why I started testing the other version (the one with the specified column names). After this discussion, I can't think of any solution other than maintaining two version of my piece of code: one for Oracle, and one PostgreSQL, as I don't see Oracle changing their driver either. :-) It's not a really big deal.

Thanks.

On Fri, Jul 15, 2016 at 10:41 AM, Andrea Bergia <a.bergia@list-group.com> wrote:

Being consistent with cases is generally good advice, but unfortunately my code has to run on multiple databases and it's very hard to control cases in all the code.

Besides, it seems to me that the behavior is inconsistent. I can use whatever case I want in the INSERT INTO clause:

INSERT INTO Documents (name) VALUES ('abc');

​If you were to write that "INSERT INTO Documents (name) VALUES ('abc') RETURNING ID" you wouldn't have a problem because the server is going to lowercase everything for you anyway.  But as soon as you ask Java to append the returning clause for you it doesn't choose to apply PostgreSQL's case normalization rules but instead assumes that the case you provided in the code is the case that you desire - and adds quotes to enforce that decision.  This is nice when you use quote-required identifiers since you don't have to go escaping them in Java.  Maybe a PostgreSQL JDBC implementation could do this but I don't see it happening in this driver.

I could see maybe having a driver config to toggle this behavior...the specifics would be important.

David J.

-- 
Andrea Bergia
List S.p.A.