Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On Mon, Jul 7, 2014 at 5:50 PM, Robert Haas wrote: > On Fri, Jul 4, 2014 at 12:36 AM, Rushabh Lathia > wrote: > > On Fri, Jul 4, 2014 at 7:37 AM, Tom Lane wrote: > >> Tom Dunstan writes: > >> > On 4 July 2014 00:07, Tom Lane wrote: > >> >> It sounds to me like designing this for JDBC's getGen

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On Fri, Jul 4, 2014 at 12:36 AM, Rushabh Lathia wrote: > On Fri, Jul 4, 2014 at 7:37 AM, Tom Lane wrote: >> Tom Dunstan writes: >> > On 4 July 2014 00:07, Tom Lane wrote: >> >> It sounds to me like designing this for JDBC's getGeneratedKeys method >> >> is a mistake. There isn't going to be an

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On Fri, Jul 4, 2014 at 7:37 AM, Tom Lane wrote: > Tom Dunstan writes: > > On 4 July 2014 00:07, Tom Lane wrote: > >> It sounds to me like designing this for JDBC's getGeneratedKeys method > >> is a mistake. There isn't going to be any way that the driver can > support > >> that without having

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On 4 July 2014 11:37, Tom Lane wrote: > > > Sure it can - it append RETURNING PRIMARY KEY and hand back a ResultSet > > from whatever was returned. It's CURRENTLY doing that, but it's appending > > RETURNING * and leaving it up to the caller of getGeneratedKeys() to work > > out which columns the

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

Tom Dunstan writes: > On 4 July 2014 00:07, Tom Lane wrote: >> It sounds to me like designing this for JDBC's getGeneratedKeys method >> is a mistake. There isn't going to be any way that the driver can support >> that without having looked at the table's metadata for itself, and if >> it's goin

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On 4 July 2014 00:07, Tom Lane wrote: > TBH, I thought that RETURNING PRIMARY KEY was already fairly iffy > in that the application would have little idea what it was getting back. > IF EXISTS would make it so spongy as to be useless, IMO. > IF EXISTS is pretty pointless - while the behaviour of

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

Greg Stark writes: > On Thu, Jul 3, 2014 at 7:37 AM, Tom Lane wrote: >> TBH, I thought that RETURNING PRIMARY KEY was already fairly iffy >> in that the application would have little idea what it was getting back. >> IF EXISTS would make it so spongy as to be useless, IMO. > Seems easy enough to

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On Thu, Jul 3, 2014 at 7:37 AM, Tom Lane wrote: > TBH, I thought that RETURNING PRIMARY KEY was already fairly iffy > in that the application would have little idea what it was getting back. > IF EXISTS would make it so spongy as to be useless, IMO. Seems easy enough to look at the count of colum

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

Rushabh Lathia writes: > On Thu, Jul 3, 2014 at 6:56 AM, Ian Barwick wrote: >> A better solution would be to have an optional "IF EXISTS" clause: >> RETURNING PRIMARY KEY [ IF EXISTS ] > Hmm liked the idea about adding [ IF EXISTS ]. Looking at the grammar so > far we > having [ IF EXISTS ] opti

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On Thu, Jul 3, 2014 at 6:56 AM, Ian Barwick wrote: > On 01/07/14 21:00, Rushabh Lathia wrote: > >> >> I spent some more time on the patch and here are my review comments. >> >> .) Patch gets applied through patch -p1 (git apply fails) >> >> .) trailing whitespace in the patch at various places >>

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On 01/07/14 21:00, Rushabh Lathia wrote: I spent some more time on the patch and here are my review comments. .) Patch gets applied through patch -p1 (git apply fails) .) trailing whitespace in the patch at various places Not sure where you see this, unless it's in the tests, where it's requ

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On 02/07/14 15:16, Ian Barwick wrote: On 14/07/01 23:13, Robert Haas wrote: On Tue, Jul 1, 2014 at 8:00 AM, Rushabh Lathia wrote: .) In map_primary_key_to_list() patch using INDEX_ATTR_BITMAP_IDENTITY_KEY bitmap to get the keycols. In IndexAttrBitmapKind there is also INDEX_ATTR_BITMAP_KEY, so

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On 14/07/01 23:13, Robert Haas wrote: > On Tue, Jul 1, 2014 at 8:00 AM, Rushabh Lathia > wrote: >> .) In map_primary_key_to_list() patch using INDEX_ATTR_BITMAP_IDENTITY_KEY >> bitmap to get the keycols. In IndexAttrBitmapKind there is also >> INDEX_ATTR_BITMAP_KEY, so was confuse between INDEX_A

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On Tue, Jul 1, 2014 at 8:00 AM, Rushabh Lathia wrote: > .) In map_primary_key_to_list() patch using INDEX_ATTR_BITMAP_IDENTITY_KEY > bitmap to get the keycols. In IndexAttrBitmapKind there is also > INDEX_ATTR_BITMAP_KEY, so was confuse between INDEX_ATTR_BITMAP_KEY and > INDEX_ATTR_BITMAP_IDENTIT

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

I spent some more time on the patch and here are my review comments. .) Patch gets applied through patch -p1 (git apply fails) .) trailing whitespace in the patch at various places .) Unnecessary new line + and - in the patch. (src/backend/rewrite/rewriteManip.c::getInsertSelectQuery()) (src/inc

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On 27/06/14 09:09, Tom Dunstan wrote: On 27 June 2014 06:14, Gavin Flower mailto:gavinflo...@archidevsys.co.nz>> wrote: On 27/06/14 00:12, Rushabh Lathia wrote: INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK') returning primary key, dname; I think allowing other colu

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On 27 June 2014 06:14, Gavin Flower wrote: > On 27/06/14 00:12, Rushabh Lathia wrote: > >> INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK') returning primary >> key, dname; >> >> I think allowing other columns with PRIMARY KEY would be more useful >> syntax. >> Even in later versions if we wa

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On 27/06/14 00:12, Rushabh Lathia wrote: Thanks for sharing latest patch. For me this syntax is limiting the current returning clause implementation. Because its not allowing the returning PRIMARY KEYS with other columns, and if user or application require that they need to do RETURNING *. Fo

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

Thanks for sharing latest patch. For me this syntax is limiting the current returning clause implementation. Because its not allowing the returning PRIMARY KEYS with other columns, and if user or application require that they need to do RETURNING *. Following syntax not working, which i think is v

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On 25/06/14 16:04, Ian Barwick wrote: Hi On 14/06/25 15:13, Rushabh Lathia wrote: Hello All, I assigned my self as reviewer of the patch. I gone through the mail chain discussion and in that question has been raised about the feature and its implementation, so would like to know what is the cu

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

Hi On 14/06/25 15:13, Rushabh Lathia wrote: > Hello All, > > I assigned my self as reviewer of the patch. I gone through the > mail chain discussion and in that question has been raised about > the feature and its implementation, so would like to know what is > the current status of this project/

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

Hello All, I assigned my self as reviewer of the patch. I gone through the mail chain discussion and in that question has been raised about the feature and its implementation, so would like to know what is the current status of this project/patch. Regards, On Thu, Jun 12, 2014 at 5:53 PM, Ian B

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On 14/06/12 20:58, Jochem van Dieten wrote: > On Thu, Jun 12, 2014 at 12:25 PM, Ian Barwick wrote: > > On 14/06/12 18:46, Jochem van Dieten wrote: > > I haven't checked the code, but I am hoping it will help with the > problem > > where a RETURNING * is added to a statement that is no

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On 2014-06-12 13:58:31 +0200, Jochem van Dieten wrote: > But the obvious way to fix the JDBC issue is not to fix it by adding a > 'mini parser' on the JDBC side, but to make SELECT ... RETURNING PRIMARY > KEY a regular select that silently ignores the returning clause and doesn't > throw an error o

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On Thu, Jun 12, 2014 at 12:25 PM, Ian Barwick wrote: > On 14/06/12 18:46, Jochem van Dieten wrote: > > I haven't checked the code, but I am hoping it will help with the problem > > where a RETURNING * is added to a statement that is not an insert or > update > > by the JDBC driver. That has been r

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On 14/06/12 18:46, Jochem van Dieten wrote: > On Wed, Jun 11, 2014 at 2:39 AM, Tom Lane wrote: > > I'm not even 100% sold that automatically returning the primary key > is going to save any application logic. Could somebody point out > *exactly* where an app is going to save effort wi

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On Wed, Jun 11, 2014 at 2:39 AM, Tom Lane wrote: > > I'm not even 100% sold that automatically returning the primary key > is going to save any application logic. Could somebody point out > *exactly* where an app is going to save effort with this type of > syntax, compared to requesting the column

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

> > Is it going to save enough to justify depending on a syntax that won't > be universal for a long time to come? > Oh, and on the won't-be-universal-for-a-while point - the status quo works fine, it's just less efficient than it should be. Once someone upgrades to 9.5 or whatever, and upgrades t

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On 11 June 2014 10:09, Tom Lane wrote: > I'm not even 100% sold that automatically returning the primary key > is going to save any application logic. Could somebody point out > *exactly* where an app is going to save effort with this type of > syntax, compared to requesting the columns it wants

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

Andres Freund writes: > On 2014-06-11 00:21:58 +0200, Vik Fearing wrote: >> What? AFAIK, that only applies to an index. How can the data itself be >> partial? > I don't follow? Gavin above talked about unique keys - which in postgres > you can create using CREATE UNIQUE INDEX. And if you make t

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On 2014-06-11 00:21:58 +0200, Vik Fearing wrote: > On 06/09/2014 07:13 PM, Andres Freund wrote: > > On 2014-06-09 13:42:22 +0200, Vik Fearing wrote: > >> On 06/09/2014 09:06 AM, Gavin Flower wrote: > >>> From memory all unique keys can be considered 'candidate primary keys', > >>> but only one can

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On 06/09/2014 07:13 PM, Andres Freund wrote: > On 2014-06-09 13:42:22 +0200, Vik Fearing wrote: >> On 06/09/2014 09:06 AM, Gavin Flower wrote: >>> From memory all unique keys can be considered 'candidate primary keys', >>> but only one can be designated 'the PRIMARY KEY'. >> >> Almost. Candidate k

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On 06/10/2014 11:02 AM, Tom Dunstan wrote: > On 10 June 2014 17:49, Hannu Krosing > wrote: > > RETURNING GENERATED KEYS perhaps, but then how do we determine that? > What about RETURNING CHANGED FIELDS ? > > Might be quite complicated technically, but this

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On 10 June 2014 17:49, Hannu Krosing wrote: > RETURNING GENERATED KEYS perhaps, but then how do we determine that? > > What about RETURNING CHANGED FIELDS ? > > Might be quite complicated technically, but this is what is probably > wanted. > Seems to be getting further away from something that

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On 06/10/2014 03:19 AM, Tom Dunstan wrote: > A definite +1 on this feature. A while ago I got partway through > hacking the hibernate postgres dialect to make it issue a RETURNING > clause to spit out the primary key before I realised that the driver > was already doing a RETURNING * internally. >

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

A definite +1 on this feature. A while ago I got partway through hacking the hibernate postgres dialect to make it issue a RETURNING clause to spit out the primary key before I realised that the driver was already doing a RETURNING * internally. On 10 June 2014 05:53, Jim Nasby wrote: > I was wo

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On 6/9/14, 8:35 AM, Hannu Krosing wrote: On 06/09/2014 06:58 AM, Ian Barwick wrote: Hi, The JDBC API provides the getGeneratedKeys() method as a way of retrieving primary key values without the need to explicitly specify the primary key column(s). Is it defined by the standard, to return _only

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On 2014-06-09 13:42:22 +0200, Vik Fearing wrote: > On 06/09/2014 09:06 AM, Gavin Flower wrote: > > From memory all unique keys can be considered 'candidate primary keys', > > but only one can be designated 'the PRIMARY KEY'. > > Almost. Candidate keys are also NOT NULL. The list actually is a bi

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On 09/06/14 23:42, Vik Fearing wrote: On 06/09/2014 09:06 AM, Gavin Flower wrote: From memory all unique keys can be considered 'candidate primary keys', but only one can be designated 'the PRIMARY KEY'. Almost. Candidate keys are also NOT NULL. Yeah, obviously! (Except, I did actually forge

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

Ian Barwick writes: > [ RETURNING PRIMARY KEY ] It looks to me like this is coded to have the expansion of the "primary key" done at parse time, which seems like fundamentally the wrong thing. Consider a view or rule containing this clause; the pkey might be different by the time execution rolls

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On 06/09/2014 06:58 AM, Ian Barwick wrote: > Hi, > > The JDBC API provides the getGeneratedKeys() method as a way of > retrieving > primary key values without the need to explicitly specify the primary key > column(s). Is it defined by the standard, to return _only_ generated primary keys, and not

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

David G Johnston wrote: >>   ERROR:  Relation does not have any primary key(s) > > "Relation does not have a primary key." > or > "Relation has no primary key." (preferred) Project style says that the primary message should not capitalize the first word, nor should it end in a period.  Detai

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On 06/09/2014 09:06 AM, Gavin Flower wrote: > From memory all unique keys can be considered 'candidate primary keys', > but only one can be designated 'the PRIMARY KEY'. Almost. Candidate keys are also NOT NULL. -- Vik -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To m

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On 09/06/14 17:47, David G Johnston wrote: Ian Barwick wrote Hi, The JDBC API provides the getGeneratedKeys() method as a way of retrieving primary key values without the need to explicitly specify the primary key column(s). This is a widely-used feature, however the implementation has signific

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On Monday, June 9, 2014, Ian Barwick wrote: > > > On 09/06/14 14:47, David G Johnston wrote: > >> Ian Barwick wrote >> >>> Hi, >>> >>> The JDBC API provides the getGeneratedKeys() method as a way of >>> retrieving >>> primary key values without the need to explicitly specify the primary key >>> c

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

On 09/06/14 14:47, David G Johnston wrote: Ian Barwick wrote Hi, The JDBC API provides the getGeneratedKeys() method as a way of retrieving primary key values without the need to explicitly specify the primary key column(s). This is a widely-used feature, however the implementation has signif

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

David G Johnston wrote > > Ian Barwick wrote >> Hi, >> >> The JDBC API provides the getGeneratedKeys() method as a way of >> retrieving >> primary key values without the need to explicitly specify the primary key >> column(s). This is a widely-used feature, however the implementation has >> signi

Re: [HACKERS] "RETURNING PRIMARY KEY" syntax extension

Ian Barwick wrote > Hi, > > The JDBC API provides the getGeneratedKeys() method as a way of retrieving > primary key values without the need to explicitly specify the primary key > column(s). This is a widely-used feature, however the implementation has > significant > performance drawbacks. > >