Possible bug related to primary keys autogeneration

Поиск
Список
Период
Сортировка
От Andrea Bergia
Тема Possible bug related to primary keys autogeneration
Дата
Msg-id 56bd3f97-c3e5-4ac8-daf9-ed6e73f48014@list-group.com
обсуждение исходный текст
Ответы Re: Possible bug related to primary keys autogeneration  (Dave Cramer <pg@fastcrypt.com>)
Re: Possible bug related to primary keys autogeneration  (Dave Cramer <pg@fastcrypt.com>)
Список pgsql-jdbc
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.



В списке pgsql-jdbc по дате отправления:

Предыдущее
От: Vladimir Sitnikov
Дата:
Сообщение: Re: Time to release 9.4.1209?
Следующее
От: Dave Cramer
Дата:
Сообщение: Re: Possible bug related to primary keys autogeneration