"RETURNING PRIMARY KEY" syntax extension
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.
Currently this feature is implemented in the JDBC driver by appending
"RETURNING *" to the supplied statement. However this means all columns of
affected rows will be returned to the client, which causes significant
performance problems, particularly on wide tables. To mitigate this, it would
be desirable to enable the JDBC driver to request only the primary key value(s).
One possible solution would be to have the driver request the primary key for
a table, but this could cause a race condition where the primary key could change,
and even if it does not, it would entail extra overhead.
A more elegant and universal solution, which would allow the JDBC driver to
request the primary key in a single request, would be to extend the RETURNING
clause syntax with the option PRIMARY KEY. This resolves during parse
analysis into the columns of the primary key, which can be done unambiguously
because the table is already locked by that point and the primary key cannot change.
A patch is attached which implements this, and will be added to the next commitfest.
A separate patch will be submitted to the JDBC project. Example usage shown below.
Regards
Ian Barwick
/* ---------------------------------------------- */
postgres=# CREATE TABLE foo (id SERIAL PRIMARY KEY);
CREATE TABLE
postgres=# INSERT INTO foo VALUES(DEFAULT) RETURNING PRIMARY KEY;
id
----
1
(1 row)
INSERT 0 1
postgres=# CREATE TABLE bar (id1 INT NOT NULL, id2 INT NOT NULL, PRIMARY KEY(id1, id2));
CREATE TABLE
postgres=# INSERT INTO bar VALUES(1,2) RETURNING PRIMARY KEY;
id1 | id2
-----+-----
1 | 2
(1 row)
INSERT 0 1
postgres=# INSERT INTO bar VALUES(2,1),(2,2) RETURNING PRIMARY KEY;
id1 | id2
-----+-----
2 | 1
2 | 2
(2 rows)
INSERT 0 2
postgres=# CREATE TABLE no_pkey (id SERIAL NOT NULL);
CREATE TABLE
postgres=# INSERT INTO no_pkey VALUES(DEFAULT) RETURNING id;
id
----
1
(1 row)
INSERT 0 1
postgres=# INSERT INTO no_pkey VALUES(DEFAULT) RETURNING PRIMARY KEY;
ERROR: Relation does not have any primary key(s)
/* ---------------------------------------------- */
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Attachments:
returning_primary_key.cf1.patchtext/x-patch; name=returning_primary_key.cf1.patchDownload+343-50
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.Currently this feature is implemented in the JDBC driver by appending
"RETURNING *" to the supplied statement. However this means all columns of
affected rows will be returned to the client, which causes significant
performance problems, particularly on wide tables. To mitigate this, it
would
be desirable to enable the JDBC driver to request only the primary key
value(s).
Seems like a good idea.
ERROR: Relation does not have any primary key(s)
"Relation does not have a primary key."
or
"Relation has no primary key." (preferred)
By definition it cannot have more than one so it must have none.
It could have multiple unique constraints but I do not believe they are
considered if not tagged as primary.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/RETURNING-PRIMARY-KEY-syntax-extension-tp5806462p5806463.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
significant
performance drawbacks.Currently this feature is implemented in the JDBC driver by appending
"RETURNING *" to the supplied statement. However this means all columns
of
affected rows will be returned to the client, which causes significant
performance problems, particularly on wide tables. To mitigate this, it
would
be desirable to enable the JDBC driver to request only the primary key
value(s).Seems like a good idea.
ERROR: Relation does not have any primary key(s)
"Relation does not have a primary key."
or
"Relation has no primary key." (preferred)By definition it cannot have more than one so it must have none.
It could have multiple unique constraints but I do not believe they are
considered if not tagged as primary.
Also,
I did see where you account for auto-updatable views but what about complex
views with instead of triggers?
These can still be the target of DML queries but are not guaranteed (though
can possibly) to return a well-defined primary key. At worse an explicit
error about the view itself, not the apparent lack of primary key, should be
emitted.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/RETURNING-PRIMARY-KEY-syntax-extension-tp5806462p5806464.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
significant
performance drawbacks.Currently this feature is implemented in the JDBC driver by appending
"RETURNING *" to the supplied statement. However this means all columns of
affected rows will be returned to the client, which causes significant
performance problems, particularly on wide tables. To mitigate this, it
would
be desirable to enable the JDBC driver to request only the primary key
value(s).Seems like a good idea.
ERROR: Relation does not have any primary key(s)
"Relation does not have a primary key."
or
"Relation has no primary key." (preferred)By definition it cannot have more than one so it must have none.
Ah yes, amazing what a fresh pair of eyes does :). The plural is
the vestige of an earlier iteration which said something about
the relation not having any primary key column(s).
Will fix, thanks.
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
On Monday, June 9, 2014, Ian Barwick <ian@2ndquadrant.com> 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
column(s). This is a widely-used feature, however the implementation has
significant
performance drawbacks.Currently this feature is implemented in the JDBC driver by appending
"RETURNING *" to the supplied statement. However this means all columns
of
affected rows will be returned to the client, which causes significant
performance problems, particularly on wide tables. To mitigate this, it
would
be desirable to enable the JDBC driver to request only the primary key
value(s).
ISTM that having a non-null returning clause variable when no returning is
present in the command makes things more complicated and introduces
unnecessary checks in the not uncommon case of multiple
non-returning commands being issued in series.
returningList was able to be null and so should returningClause. Then if
non-null first check for the easy column listing and then check for the
more expensive PK lookup request.
Then again the extra returning checks may just amount noise.
David J.
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
significant
performance drawbacks.Currently this feature is implemented in the JDBC driver by appending
"RETURNING *" to the supplied statement. However this means all columns of
affected rows will be returned to the client, which causes significant
performance problems, particularly on wide tables. To mitigate this, it
would
be desirable to enable the JDBC driver to request only the primary key
value(s).Seems like a good idea.
ERROR: Relation does not have any primary key(s)
"Relation does not have a primary key."
or
"Relation has no primary key." (preferred)By definition it cannot have more than one so it must have none.
It could have multiple unique constraints but I do not believe they are
considered if not tagged as primary.David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/RETURNING-PRIMARY-KEY-syntax-extension-tp5806462p5806463.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
From memory all unique keys can be considered 'candidate primary keys',
but only one can be designated 'the PRIMARY KEY'.
I also like your preferred error message, and to the full extent of my
decidedly Non-Authority, I hereby authorise it! :-)
Cheers,
Gavin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
David G Johnston <david.g.johnston@gmail.com> 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. Detail and hints
should be in sentence style, but not the message itself.
http://www.postgresql.org/docs/9.3/interactive/error-style-guide.html#AEN100914
--
Kevin Grittner
EDB: 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 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
for example generated alternate keys ?
Cheers
--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic O�
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Ian Barwick <ian@2ndquadrant.com> 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 around. It'd be better probably
if the rewriter or planner did the expansion (and threw the error for
no-primary-key, if necessary).
Alternatively, we could do it like this and consider that the view is
dependent on the primary key constraint, but that seems inflexible.
BTW, it seems like your representation of the clause was rather poorly
chosen: it forces changing a whole lot of code that otherwise would
not need to be changed. I'd have left returningList alone and put the
returningPrimaryKey flag someplace else.
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 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 forget that - me bad.)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 bit longer. They also cannot be partial.
There's generally also the restriction that for some contexts - like
e.g. foreign keys - primary/candidate keys may not be deferrable..
Greetings,
Andres Freund
--
Andres Freund 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
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_ generated primary keys,
and not
for example generated alternate keys ?
I was wondering that myself. I think it's certainly reasonable to expect someone would wan RETURNING SEQUENCE VALUES, which would return the value of every column that owned a sequence (ie: ALTER SEQUENCE ... OWNED BY). ISTM that would certainly handle the performance aspect of this, and it sounds more in line with what I'd expect getGeneratedKeys() to do.
--
Jim C. Nasby, Data Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 <jim@nasby.net> wrote:
I was wondering that myself. I think it's certainly reasonable to expect
someone would wan RETURNING SEQUENCE VALUES, which would return the value
of
every column that owned a sequence (ie: ALTER SEQUENCE ... OWNED BY). ISTM
that would certainly handle the performance aspect of this, and it sounds
more in line with what I'd expect getGeneratedKeys() to do.
Keep in mind that not all generated keys come from sequences. Many people
have custom key generator functions, including UUIDs and other exotic
things like Instagram's setup [1]http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram.
RETURNING GENERATED KEYS perhaps, but then how do we determine that? Any
column that was filled with a default value? But that's potentially
returning far more values than the user will want - I bet 99% of users just
want their generated primary key.
The spec is a bit vague [2]http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys():
Retrieves any auto-generated keys created as a result of executing
this Statement object. If this Statement object did not generate any
keys, an empty ResultSet object is returned.
Note:If the columns which represent the auto-generated keys were
not specified, the JDBC driver implementation will determine the
columns which best represent the auto-generated keys.
The second paragraph refers to [3]http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#execute(java.lang.String,%20int[]) and [4]http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#execute(java.lang.String,%20java.lang.String[]) where the application can
specify which columns it's after. Given that there's a mechanism to specify
which keys the application wants returned in the driver, and the driver in
that case can just issue a RETURNING clause with a column list, my gut feel
would be to just support returning primary keys as that will handle most
cases of e.g. middleware like ORMs fetching that without needing to know
the specific column names.
Cheers
Tom
[1]: http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram
http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram
[2]: http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys()
http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys()
[3]: http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#execute(java.lang.String,%20int[])
http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#execute(java.lang.String,%20int[])
[4]: http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#execute(java.lang.String,%20java.lang.String[])
http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#execute(java.lang.String,%20java.lang.String[])
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.On 10 June 2014 05:53, Jim Nasby <jim@nasby.net
<mailto:jim@nasby.net>> wrote:I was wondering that myself. I think it's certainly reasonable to expect
someone would wan RETURNING SEQUENCE VALUES, which would return thevalue of
every column that owned a sequence (ie: ALTER SEQUENCE ... OWNED
BY). ISTM
that would certainly handle the performance aspect of this, and it
sounds
more in line with what I'd expect getGeneratedKeys() to do.
Keep in mind that not all generated keys come from sequences. Many
people have custom key generator functions, including UUIDs and other
exotic things like Instagram's setup [1].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.
Any column that was filled with a default value? But that's
potentially returning far more values than the user will want - I bet
99% of users just want their generated primary key.
Probably not true - you would want your ORM model to be in sync with
what is database after you save it if you plan to do any further
processing using it.
At least I would :)
The spec is a bit vague [2]:
Retrieves any auto-generated keys created as a result of executing
this Statement object. If this Statement object did not generate any
keys, an empty ResultSet object is returned.Note:If the columns which represent the auto-generated keys were
not specified, the JDBC driver implementation will determine the
columns which best represent the auto-generated keys.The second paragraph refers to [3] and [4] where the application can
specify which columns it's after. Given that there's a mechanism to
specify which keys the application wants returned in the driver, and
the driver in that case can just issue a RETURNING clause with a
column list, my gut feel would be to just support returning primary
keys as that will handle most cases of e.g. middleware like ORMs
fetching that without needing to know the specific column names.
Why not then just leave the whole thing as it is on server side, and let
the ORM specify which "generated keys" it wants ?
Cheers
Tom
[1]
http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram
[2]
http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys()
<http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys%28%29>
[3] http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#execute(java.lang.String,%20int[])
<http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#execute%28java.lang.String,%20int[]%29>
[4] http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#execute(java.lang.String,%20java.lang.String[])
<http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#execute%28java.lang.String,%20java.lang.String[]%29>
--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ
On 10 June 2014 17:49, Hannu Krosing <hannu@2ndquadrant.com> 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 describes the main use
case - changed fields sounds like something that would apply to an update
statement.
Any column that was filled with a default value? But that's potentially
returning far more values than the user will want - I bet 99% of users just
want their generated primary key.Probably not true - you would want your ORM model to be in sync with what
is database after you save it if you plan to do any further processing
using it.
Well, yes, but since RETURNING is non-standard most ORMs are unlikely to
support fetching other generated values that way anyway. The ones that I've
dealt with will do an insert, then a select to get the extra fields. I
don't know if other JDBC drivers allow applications to just specify any old
list of non-key columns to the execute method, but I suspect not, given
that the way they fetch those columns is rather less general-purpose than
our RETURNING syntax.
The second paragraph refers to [3] and [4] where the application can
specify which columns it's after. Given that there's a mechanism to specify
which keys the application wants returned in the driver, and the driver in
that case can just issue a RETURNING clause with a column list, my gut feel
would be to just support returning primary keys as that will handle most
cases of e.g. middleware like ORMs fetching that without needing to know
the specific column names.Why not then just leave the whole thing as it is on server side, and let
the ORM specify which "generated keys" it wants ?
Because java-based ORMs (at least) mostly don't have to - other
server/driver combos manage to implement getGeneratedKeys() without being
explicitly given a column list, they just do the sane thing and return the
appropriate identity column or whatever for the inserted row.
I agree that in hand-crafted JDBC there's no particular problem in making a
user specify a column list, (although I don't think I've EVER seen anyone
actually do that in the wild), but most middleware will expect
getGeneratedKeys() to just work and we should try to do something about
making that case work a bit more efficiently than it does now.
Cheers
Tom
On 06/10/2014 11:02 AM, Tom Dunstan wrote:
On 10 June 2014 17:49, Hannu Krosing <hannu@2ndquadrant.com
<mailto:hannu@2ndquadrant.com>> 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 describes the
main use
case - changed fields sounds like something that would apply to an
update statement.
Not really - it applies to both INSERT and UPDATE if there are any
triggers and/or default values
The use-case is an extended version of getting the key, with the main
aim of making sure
that your ORM model is the same as what is saved in database.
Any column that was filled with a default value? But that's
potentially returning far more values than the user will want - I
bet 99% of users just want their generated primary key.Probably not true - you would want your ORM model to be in sync
with what is database after you save it if you plan to do any
further processing using it.Well, yes, but since RETURNING is non-standard most ORMs are unlikely
to support fetching other generated values that way anyway. The ones
that I've dealt with will do an insert, then a select to get the extra
fields. I don't know if other JDBC drivers allow applications to just
specify any old list of non-key columns to the execute method, but I
suspect not, given that the way they fetch those columns is rather
less general-purpose than our RETURNING syntax.The second paragraph refers to [3] and [4] where the application
can specify which columns it's after. Given that there's a
mechanism to specify which keys the application wants returned in
the driver, and the driver in that case can just issue a
RETURNING clause with a column list, my gut feel would be to just
support returning primary keys as that will handle most cases of
e.g. middleware like ORMs fetching that without needing to know
the specific column names.Why not then just leave the whole thing as it is on server side,
and let the ORM specify which "generated keys" it wants ?Because java-based ORMs (at least) mostly don't have to - other
server/driver combos manage to implement getGeneratedKeys() without
being explicitly given a column list, they just do the sane thing and
return the appropriate identity column or whatever for the inserted row.I agree that in hand-crafted JDBC there's no particular problem in
making a user specify a column list, (although I don't think I've EVER
seen anyone actually do that in the wild), but most middleware will
expect getGeneratedKeys() to just work and we should try to do
something about making that case work a bit more efficiently than it
does now.
But does the ORM already not "know" the names of auto-generated keys and
thus could easily replace them for * in RETURNING ?
Cheers
Tom
--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ
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 keys are also NOT NULL.
The list actually is a bit longer. They also cannot be partial.
What? AFAIK, that only applies to an index. How can the data itself be
partial?
There's generally also the restriction that for some contexts - like
e.g. foreign keys - primary/candidate keys may not be deferrable..
Again, what is deferrable data?
--
Vik
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 be designated 'the PRIMARY KEY'.Almost. Candidate keys are also NOT NULL.
The list actually is a bit longer. They also cannot be partial.
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 those partial
they can't be used for this purpose.
There's generally also the restriction that for some contexts - like
e.g. foreign keys - primary/candidate keys may not be deferrable..Again, what is deferrable data?
You can define primary/unique constraints to be deferrable. c.f. CREATE
TABLE docs.
Greetings,
Andres Freund
--
Andres Freund 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
Andres Freund <andres@2ndquadrant.com> 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 those partial
they can't be used for this purpose.
I have a feeling this conversation is going in the wrong direction.
ISTM that to be useful at all, the set of columns that would be returned
by a clause like this has to be *extremely* predictable; otherwise the
application won't know what to do with the results. If the app has to
examine the table's metadata to identify what it's getting, what's the
point of the feature at all as opposed to just listing the columns you
want explicitly? So I doubt that the use-case for anything more
complicated than returning the primary key, full stop, is really there.
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 by name?
Is it going to save enough to justify depending on a syntax that won't
be universal for a long time to come?
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
