Re: Proposal: RETURNING primary_key()

On 3/8/2016 4:42 PM, Craig Ringer wrote:

On 9 March 2016 at 05:40, Igal @ Lucee.org <igal@lucee.org> wrote:

I will try to gather more information about the other DBMSs and drivers and will post my findings here when I have them.

Thanks. I know that's not the most fun thing to do in the world, but it's often needed when implementing something where part of the goal is being compatible with other vendors, etc.

It seems that the implementations vary by the driver, and not the server, as evidenced by the Microsoft SQL Server drivers -- I tested both the official MS driver and the open sourced jTDS driver.

I noticed that you usually don't put html in the emails here, but I think that it's appropriate here to show the information in a clear way (also, according to my computer it's 2016).  I hope that it will be rendered properly:

	MySQL	DB2	SQL Server (MS)	SQL Server (jTDS)	Oracle
Returned Type	SET	SET	ROW	ROW	ROW
Column Name	GENERATED_KEY	[name of identity col]	GENERATED_KEYS	ID	ROWID
Column Type	Unknown (numeric)	integer	numeric	numeric	ROWID
Value	Each inserted value to identity column	Each inserted value to identity column	Last inserted value to identity column	Last inserted value to identity column	internal address location that does not change on UPDATE
Example	(1), (2)	(1), (2)	(2)	(2)	AAAE5nAABAAALCxAAM

Some notes and observations:

It's the Wild West!  Each implementation does something completely different.  Even when something looks similar, e.g. the returned column name from MySQL and SQL Server (MS), it's not:  notice the plural in SQL Server's column name, which is ironic as they only return a single value, as opposed to MySQL which returns a SET.

This has been an "interesting experience" as it was my first exposure to some of those DBMSs. It only reinforced my decision to choose PostgreSQL moving forward, over the alternatives (after using SQL Server for about 20 years).

More notes on the different DBMSs:

The first thing that I tested was against MySQL:

    CREATE TABLE IF NOT EXISTS test_jdbc(name VARCHAR(64), id SERIAL);

An insert to that table via JDBC, with int flag RETURN_GENERATED_KEYS returns a result set with a column named "GENERATED_KEY " and type "UNKNOWN" (as per ResultSetMetaData's getColumnTypeName()), each row in the result set corresponded with an inserted record, so for example:

    INSERT INTO test_jdbc(name) VALUES ('JDBC'), ('PostgreSQL');

returned two rows with the value of the "id" column for the inserted row in each, e.g.

GENERATED_KEY
-------------
7
8

Trying to add multiple SERIAL columns to a table results in an error:

    CREATE TABLE IF NOT EXISTS jdbc(j_name VARCHAR(64), j_id SERIAL, id2 SERIAL)   

Error Code: 1075. Incorrect table definition; there can be only one auto column and it must be defined as a key


SQL Server: via the Microsoft driver

Created table with the command:

    CREATE TABLE dbo.jdbc (
        j_name varchar(64) NOT NULL,
        j_id int IDENTITY(1,1) NOT NULL
    )

Generated Keys return a single row with a column named "GENERATED_KEYS" of type numeric, and the value is the last inserted id (i.e. sequence).  This is different from MySQL which returns a row with the id for each inserted record.


SQL Server: via the jTDS driver

Generated Keys return a single row with a column named "ID" of type numeric, and the value is the last inserted id (i.e. sequence).  The behavior is similar to the Microsoft driver, but the column name is different.


Oracle:

Oracle returns the column ROWID which is of type ROWID as well:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns008.htm

This seems to be similar to PostgreSQL's ctid, but unlike ctid -- when I UPDATE the record the ROWID remains unchanged.

In my test I got the value "AAAE5nAABAAALCxAAM", and when I later ran: 

    SELECT * FROM jdbc WHERE ROWID='AAAE5nAABAAALCxAAM';

I got the information back from that row.  Updating that row does not change its ROWID.

When I tried to insert multiple values with RETURN_GENERATED_KEYS I got an error:  java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

    INSERT INTO jdbc(j_name) SELECT 'PG 9.5.0' FROM DUAL UNION SELECT 'PG 9.5.1' FROM DUAL

The rows are, however, inserted into the table.  Running the same INSERT command without RETURN_GENERATED_KEYS works without error.

(Side note:  This was my first, and hopefully my last, experience with Oracle database, and it's been a real PITA.  If I had tried it out some 20 years ago then the experience would have probably led me to sell the stock short, which would have probably ended with my bankruptcy.  Go figure...)


IBM DB2:

    CREATE TABLE jdbc(j_name VARCHAR(64), j_id INT NOT NULL GENERATED ALWAYS AS IDENTITY)

Generated Keys return a set with the column named "J_ID" of type integer.  One row for each inserted row.

(Side note: after wasting almost a full day setting up and connecting to the DB2 server I realized why Oracle was so successful)