Proposal: RETURNING primary_key()

On 03/07/2016 12:32 PM, Igal @ Lucee.org wrote:

The problem is that we do not always know in advance what the Primary
Key is, and therefore a solution that was implemented in the pgjdbc

I agree that the problem is that you don't always know what the primary
key is.

I would argue the solution is to check before you write the query.

JD

--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

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

On 3/7/2016 12:45 PM, Joshua D. Drake wrote:

I agree that the problem is that you don't always know what the
primary key is.

I would argue the solution is to check before you write the query.

Sure, that would be great, but perhaps I should have give some more context:

We have an application server which allows our developers to query
databases with simplified syntax. Our code is written in a generic way
to allow the developers that use our application server to pass whatever
query they want into the database server, whether it's SQL Server,
MySQL, Oracle, etc.

The code that we use to wrap the SQL statement, as well as the JDBC
code, has no idea about the table or its constraints, so it's not like
I'm writing my own queries, and am just being lazy at checking what the
primary key is. I just can't know what the developer has in his database.

Sure, I can probably query it via metadata tables, etc., but that would
be a much slower process.

Igal

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

"Igal @ Lucee.org" <igal@lucee.org> writes:

On 3/7/2016 12:45 PM, Joshua D. Drake wrote:

I agree that the problem is that you don't always know what the
primary key is.
I would argue the solution is to check before you write the query.

Yeah. I'm rather suspicious of this proposal; I do not think it's
actually very useful to return a primary-key value without any indication
of what the primary key is. There are also corner cases where it seems
pretty ill-defined. For example, suppose you do this on an inheritance
parent table that has a pkey defined, but not all its child tables do
(or maybe they do but their pkeys aren't identical to the parent's).
What should happen then?

Sure, that would be great, but perhaps I should have give some more context:
We have an application server which allows our developers to query
databases with simplified syntax. Our code is written in a generic way
to allow the developers that use our application server to pass whatever
query they want into the database server, whether it's SQL Server,
MySQL, Oracle, etc.

That's an exceptionally weak use-case to argue for this with. Unless
you can get *all* those DBMS suppliers to invent equivalent features,
you're going to have to have pkey-querying logic anyway. The argument
for bespoke syntax for it in just one DBMS seems pretty weak.

I am fairly sure, also, that all of those systems have support for the
SQL-standard information_schema views. So if you write a pkey-identifying
query against those views, you'd have some chance of a solution that
actually did work everywhere.

regards, tom lane

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

On 3/7/2016 1:20 PM, Tom Lane wrote:

Yeah. I'm rather suspicious of this proposal; I do not think it's
actually very useful to return a primary-key value without any indication
of what the primary key is. There are also corner cases where it seems
pretty ill-defined. For example, suppose you do this on an inheritance
parent table that has a pkey defined, but not all its child tables do
(or maybe they do but their pkeys aren't identical to the parent's).
What should happen then?

First, thank you for your reply. I appreciate it. I do not know the
answer to that question.

That's an exceptionally weak use-case to argue for this with. Unless
you can get *all* those DBMS suppliers to invent equivalent features,
you're going to have to have pkey-querying logic anyway. The argument
for bespoke syntax for it in just one DBMS seems pretty weak.

Fair enough, but my idea was that this will be used by the JDBC driver
in this case. The other DBMS suppliers have their JDBC driver return a
value, usually it is SERIAL type. But there is no standard for the
column name. In SQL Server, for example, it is IDENTITYCOL while in
MySQL it is GENERATED_KEY.

The thing is that in SQL Server I can do, for example, "SELECT
@@identity" and get the last value that was inserted. In SQL Server,
however, Microsoft took the easy way and enforced only a single
auto-generated identity column per table. The closest thing I can do in
PostgreSQL is "SELECT lastval()" but what if there are multiple
sequences in that table?

I am fairly sure, also, that all of those systems have support for the
SQL-standard information_schema views. So if you write a pkey-identifying
query against those views, you'd have some chance of a solution that
actually did work everywhere.

The other JDBC drivers return the last SERIAL value from the table that
had the insert, so there's no issue there. Querying the
information_schema views with each INSERT will probably cause a major
performance hit.

Anyway, I trust that you know much more about databases than I do, so if
you don't think that it's a good idea, I accept that.

Best,

Igal

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

Hi

On 08/03/16 05:32, Igal @ Lucee.org wrote:

THE ISSUE:

In JDBC there is a flag called RETURN_GENERATED_KEYS -- https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#RETURN_GENERATED_KEYS

(...)

THE PROPOSAL:

The proposal is to allow something like RETURNING primary_key() (it can be a keyword, not
necessarily a function), e.g.

INSERT INTO test VALUES ('PostgresQL') RETURNING primary_key();

FYI something similar has been proposed before:

/messages/by-id/53953EFB.8070701@2ndquadrant.com

The linked thread might provide more insights into the issues surrounding
this proposal.

Regards

Ian Barwick

--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

Ian,

On 3/7/2016 4:17 PM, Ian Barwick wrote:

FYI something similar has been proposed before:

/messages/by-id/53953EFB.8070701@2ndquadrant.com

The linked thread might provide more insights into the issues surrounding
this proposal.

It's funny how I've encountered the same issue and reached the same
conclusion as you did. The main difference is that I suggested
returning NULL values instead of throwing an error.

I read through the whole thread and it seems to me like there was quite
a bit of support for that feature, with Tom still unconvinced that this
feature is useful -- but quite a few others who see the benefit in it,
especially Java users who experience that problem first hand -- and
Rushabh complaining about white space in the patch?

I'm not sure why it was not accepted at the end?

Igal

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

On 8 March 2016 at 08:56, Igal @ Lucee.org <igal@lucee.org> wrote:

I'm not sure why it was not accepted at the end?

The biggest issue, though it might not be clear from that thread, is that
what exactly it means to "return generated keys" is poorly defined by JDBC,
and not necessarily the same thing as "return the PRIMARY KEY".

Should we return the DEFAULT on a UNIQUE column, for example?

IMO other vendors' drivers should be tested for behaviour in a variety of
cases. Ideally the JDBC test suite too. Then specify the exact behaviour of
what we need to satisfy the JDBC driver's requirements and anything else
that might be related.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Craig Ringer <craig@2ndquadrant.com> writes:

On 8 March 2016 at 08:56, Igal @ Lucee.org <igal@lucee.org> wrote:

I'm not sure why it was not accepted at the end?

The biggest issue, though it might not be clear from that thread, is that
what exactly it means to "return generated keys" is poorly defined by JDBC,
and not necessarily the same thing as "return the PRIMARY KEY".

Should we return the DEFAULT on a UNIQUE column, for example?

IMO other vendors' drivers should be tested for behaviour in a variety of
cases.

Yeah. It was asserted in the earlier thread that other vendors implement
this feature as "return the pkey", but that seems to conflict with the
plain language of the JDBC spec: generated columns are an entirely
different thing than primary key columns. So really what I'd like to see
is some work on surveying other implementations to confirm exactly what
behavior they implement. If we're to go against what the spec seems to
say, I want to see a whole lot of evidence that other people do it
consistently in a different way.

regards, tom lane

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

On Mon, Mar 7, 2016 at 11:18 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Craig Ringer <craig@2ndquadrant.com> writes:

On 8 March 2016 at 08:56, Igal @ Lucee.org <igal@lucee.org> wrote:

I'm not sure why it was not accepted at the end?

The biggest issue, though it might not be clear from that thread, is that
what exactly it means to "return generated keys" is poorly defined by JDBC,
and not necessarily the same thing as "return the PRIMARY KEY".

Should we return the DEFAULT on a UNIQUE column, for example?

IMO other vendors' drivers should be tested for behaviour in a variety of
cases.

Yeah. It was asserted in the earlier thread that other vendors implement
this feature as "return the pkey", but that seems to conflict with the
plain language of the JDBC spec: generated columns are an entirely
different thing than primary key columns. So really what I'd like to see
is some work on surveying other implementations to confirm exactly what
behavior they implement. If we're to go against what the spec seems to
say, I want to see a whole lot of evidence that other people do it
consistently in a different way.

I agree that some research should be done on how this works in other
systems, but I think we have a general problem with the server lacking
certain capabilities that make it easy to implement a high-quality
JDBC driver. And I think it would be good to work on figuring out how
to fix that. I feel that some of the replies on this thread were
rather hostile considering that the goal -- good connectors for the
database server -- is extremely important.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

On 3/8/2016 12:12 PM, Robert Haas wrote:

I agree that some research should be done on how this works in other
systems, but I think we have a general problem with the server lacking
certain capabilities that make it easy to implement a high-quality
JDBC driver. And I think it would be good to work on figuring out how
to fix that.

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

Best,

Igal

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

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

On 3/8/2016 12:12 PM, Robert Haas wrote:

I agree that some research should be done on how this works in other
systems, but I think we have a general problem with the server lacking
certain capabilities that make it easy to implement a high-quality
JDBC driver. And I think it would be good to work on figuring out how
to fix that.

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.

Currently I suggest using Connection.prepareStatement(..., String[]
generatedKeyColumns) where possible. I realise that's not practical for all
apps, which is why supporting the int flag form better is desirable, and we
just have to figure out what exactly we should be returning...

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 9 March 2016 at 04:12, Robert Haas <robertmhaas@gmail.com> wrote:

I think we have a general problem with the server lacking
certain capabilities that make it easy to implement a high-quality
JDBC driver. And I think it would be good to work on figuring out how
to fix that.

There are a few frustrations, to be sure, but I'm not sure there's actually
a ton server-side that drastically limits the driver.

One of the worst problems (IMO) is in the driver architecture its self. It
attempts to prevent blocking by guestimating the server's send buffer state
and its recv buffer state, trying to stop them filling and causing the
server to block on writes. It should just avoid blocking on its own send
buffer, which it can control with confidence. Or use some of Java's rather
good concurrency/threading features to simultaneously consume data from the
receive buffer and write to the send buffer when needed, like pgjdbc-ng
does. This makes making use of the pipelining features in Pg's protocol way
harder and less efficient than it should be - but then, PgJDBC still does
this better than libpq, which can't pipeline queries at all.

There certainly are server/protocol frustrations.

QUERY CANCEL RACES
---

Query cancellation sucks badly. Not because it requires a new connection,
though that's unfortunate, but because cancel is backend-level not
statement-level. A statement cancellation key returned as an immediate
response to the Execute message would be quite handy, so we could include
it in cancel requests and eliminate the race by having the cancel request
be a no-op if the statement cancel key doesn't match the currently running
statement.

EARLY CONNECTION CHARSETS
---

There's no way to know the charset of early connection error messages,
which is a flaw in the protocol that isn't specific to PgJDBC its self.
Similarly, you can't specify the text encoding of usernames, passwords, etc
sent to the server.

PER-QUERY GUCs
---

We also have no way to set GUCs per-query, and we need it for
statement_timeout. I really wish Parse and Execute messages allowed
statement-scoped GUCs to be passed at the protocol level. This would be
very, very helpful. The driver can probably work around it by fetching and
then SETing statement_timeout, running the query, then reSETing it
afterwards in a piplelined set of queries, but .... yuck. Also, log spam
galore.

GENERATED KEYS AND RETURNING
---

To get generated keys we have to hack the statement text. There's no
protocol-level equivalent, like we have for row-count limits in the v3
protocol. The ability to specify the set of returned columns at the
protocol level would be very nice. That said, hacking the statement text
isn't *too* bad, mostly because few people are going to do their own
RETURNING statement *and* request generated keys from the driver, the only
time this becomes an issue.

STRING TYPE ISSUES
---

PgJDBC can work around Pg's IMO somewhat overzealous type checks for string
types by passing string parameters as being of unknown-type. The JDBC
interface offers us no easy way to differentiate between "this parameter is
a real textual value" and "this parameter is a string representation of
something that might be another type". We can do it with setObject and
extension class wrappers, but then the user has to import the JDBC driver's
classes directly, use PgJDBC-specific API, etc. The people who have the
most problem with our current behaviour are those least able to do that,
users who're behind a query generation layer or ORM. I'd like to just make
stringtype=unspecified the default in PgJDBC and be done with it; users can
still specify an explicit cast to 'text' in the SQL if they want

PROTOCOL-LEVEL SAVEPOINTS
---

psqlODBC would benefit from protocol-level SAVEPOINT and ROLLBACK TO
SAVEPOINT, mostly to reduce logspam and parser overhead. PgJDBC would be
able to use this to emulate other DBMSes error handling behaviour too, when
requested by a client. (Yes, I know about the correctness and performance
issues, but you tell that to someone who just wants to Port Their Stuff
From Oracle But Can't Change The Code).

SERVER_VERSION_NUM
---

server_version_num should be GUC_REPORT and it's really annoying that it
isn't. I never agreed with the arguments about why that wasn't changed, and
I still want it changed.

LOST TYPMOD, NULLABILITY INFO
---

The server throws away typmod and nullability knowledge as soon as you do
anything with a column. This is frustrating for the driver's metadata API
support. Having result columns marked non-null in Describe would be handy.

LAZY BYTEA
---

The protocol offers no way to lazily fetch large values like BYTEA. Many
vendors can fetch small results and return a handle that gets larger
results from the server on-demand. This means that many clients expect that

SELECT * FROM my_table_with_100MB_bytea_column;

will not fetch all those bytea values to the client until/unless they're
actually accessed. They don't have to generate new and different queries
each time. ORMs in particular benefit from this. Ideally we'd have the
protocol-level ability to return a handle to the relevant TOAST entry that
clients can then fetch using further protocol messages on-demand so long as
they're on the same session, haven't committed or rolled back, and haven't
run another statement. This would make working with big binary objects in
the DB considerably more practical.

I'm sure there are others I haven't remembered or run into in there too.

See https://wiki.postgresql.org/wiki/Todo#Wire_Protocol_Changes for some.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Tue, Mar 8, 2016 at 8:12 PM, Craig Ringer <craig@2ndquadrant.com> wrote:

There certainly are server/protocol frustrations.

I'm sympathetic to all of these and think we should work on fixing
them, particularly...

STRING TYPE ISSUES
---

PgJDBC can work around Pg's IMO somewhat overzealous type checks ...

This.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

On 9 March 2016 at 21:30, Robert Haas <robertmhaas@gmail.com> wrote:

On Tue, Mar 8, 2016 at 8:12 PM, Craig Ringer <craig@2ndquadrant.com>
wrote:

There certainly are server/protocol frustrations.

I'm sympathetic to all of these and think we should work on fixing
them, particularly...

STRING TYPE ISSUES
---

PgJDBC can work around Pg's IMO somewhat overzealous type checks ...

This.

I've raised that multiple times and got nowhere. More importantly, I'm
reasonably convinced that passing string types as UNKNOWNOID is what users
generally want and expect anyway, and is consistent with what happens when
you write string literals directly in SQL. I think we should just change
PgJDBC to default to this already-optional behaviour, which is currently
controlled by the stringtype=unspecified JDBC parameter.

Other drivers have similar issues, and can fix it the same way. Maybe we
should document it somewhere, but I think this is in many ways the least
deserving of attention. Partly because clients can work around it easily,
partly because the energy input required for any change will be prohibitive
and is better spent elsewhere.

I'd *much* rather have things like query cancel cookies, per-query GUCs at
the protocol level, etc.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 3/8/2016 5:12 PM, Craig Ringer wrote:

One of the worst problems (IMO) is in the driver architecture its
self. It attempts to prevent blocking by guestimating the server's
send buffer state and its recv buffer state, trying to stop them
filling and causing the server to block on writes. It should just
avoid blocking on its own send buffer, which it can control with
confidence. Or use some of Java's rather good concurrency/threading
features to simultaneously consume data from the receive buffer and
write to the send buffer when needed, like pgjdbc-ng does.

Are there good reasons to use pgjdbc over pgjdbc-ng then?

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

On 10 March 2016 at 00:41, Igal @ Lucee.org <igal@lucee.org> wrote:

On 3/8/2016 5:12 PM, Craig Ringer wrote:

One of the worst problems (IMO) is in the driver architecture its self.
It attempts to prevent blocking by guestimating the server's send buffer
state and its recv buffer state, trying to stop them filling and causing
the server to block on writes. It should just avoid blocking on its own
send buffer, which it can control with confidence. Or use some of Java's
rather good concurrency/threading features to simultaneously consume data
from the receive buffer and write to the send buffer when needed, like
pgjdbc-ng does.

Are there good reasons to use pgjdbc over pgjdbc-ng then?

Maturity, support for older versions (-ng just punts on support for
anything except new releases) and older JDBC specs, completeness of support
for some extensions. TBH I haven't done a ton with -ng yet.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

On 9 March 2016 at 05:40, Igal @ Lucee.org <igal@lucee.org
<mailto: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)

On Mar 10, 2016, at 2:07 PM, Igal @ Lucee.org <igal@lucee.org> wrote:

(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...)

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

This email made me laugh.

...Robert

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

On 11 March 2016 at 03:07, Igal @ Lucee.org <igal@lucee.org> wrote:

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).

Pretty sure we have at least one person here using mailreader software
that's old enough to vote in most countries, but I tend to share the
sentiment. At least when there's actually a functional reason like this :)

Thanks so much for doing this testing.

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.

I honestly didn't expect that. I knew Oracle returned ROWID, but I have to
admit I thought the others would probably just return the key column(s).

When you supply the column type, does that (with the exception of Oracle)
match the column type of the generated key col?

Did you try GENERATED ALWAYS cols (where supported), UNIQUE columns with
DEFAULTs, composite columns, etc? Part of the question for Pg is what
exactly we should and should not be returning.

(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...)

I rather less than fondly recall my own attempts to get Oracle Express
installed and running for some test or another a while ago. Amazing that it
can be that fiddly. MS-SQL on the other hand "just worked" and dropped me
into the most gorgeously wonderful admin tool and SQL editor ever.

I wonder if any of these drivers have extension options and compat flags
that you have to turn on to get better behaviour like returning a set? Or
if they're just that limited?

Anyway, from the sounds of this we have a fair bit of freedom to define
what we want at both the Pg and driver level so long as we satisfy the
basic constraint that we should return a set of generated keys in the case
where a statement does an insert that adds rows to a table with a SERIAL
(or an owned SEQUENCE). Seems like we could do pretty much whatever we want
for multiple-generated-columns cases etc.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services