Обсуждение: Primary key gist index?

On 03/14/2018 06:19 AM, Jeremy Finzel wrote:
> Hello!  From all that I can tell, it is not possible using a btree_gist 
> index as a primary key.  If so, why not?  I have a table with this gist 

https://www.postgresql.org/docs/10/static/btree-gist.html

"In general, these operator classes will not outperform the equivalent 
standard B-tree index methods, and they lack one major feature of the 
standard B-tree code: the ability to enforce uniqueness."

> index which truly ought to be its primary key.  as_of_date is of range 
> date type:
> 
> EXCLUDE USING gist (id WITH =, as_of_date WITH &&)
> 
> Any direction here would be much appreciated.
> 
> Right now, I am forced to create a redundant btree index UNIQUE, btree 
> (id, lower(as_of_date)) in order to have a primary key on the table.
> 
> Thanks!
> Jeremy


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 03/14/2018 06:19 AM, Jeremy Finzel wrote:
> Hello!  From all that I can tell, it is not possible using a btree_gist 
> index as a primary key.  If so, why not?  I have a table with this gist 
> index which truly ought to be its primary key.  as_of_date is of range 
> date type:
> 
> EXCLUDE USING gist (id WITH =, as_of_date WITH &&)

I'm curious why you need a primary key on this table, especially if the 
exclusion constraint is already preventing duplicate/overlapping records?

Technically I think an exclusion constraint (or at least this one) 
fulfills the formal requirements of a primary key (is unique, isn't 
null), but maybe there are other primary-key duties it doesn't meet, 
like defining foreign keys that reference it. I've been on-and-off 
building an extension for temporal foreign keys at [1]. That is pretty 
new, but perhaps it will be useful/interesting to you. And if you have 
any feedback, I'd love to hear it!

But anyway, maybe if you shared why the table needs a real PRIMARY KEY, 
people here can suggest something.

[1] https://github.com/pjungwir/time_for_keys

Yours,

-- 
Paul              ~{:-)
pj@illuminatedcomputing.com

Paul Jungwirth <pj@illuminatedcomputing.com> writes:
> On 03/14/2018 06:19 AM, Jeremy Finzel wrote:
>> Hello!  From all that I can tell, it is not possible using a btree_gist 
>> index as a primary key.  If so, why not?  I have a table with this gist 
>> index which truly ought to be its primary key.  as_of_date is of range 
>> date type:
>> 
>> EXCLUDE USING gist (id WITH =, as_of_date WITH &&)

> Technically I think an exclusion constraint (or at least this one) 
> fulfills the formal requirements of a primary key (is unique, isn't 
> null), but maybe there are other primary-key duties it doesn't meet, 
> like defining foreign keys that reference it.

I think a key point is that an exclusion constraint might or might not
provide behavior that could be construed as uniqueness.  The logic for
PRIMARY KEY hasn't got nearly enough knowledge to tell whether particular
operators used in a particular way in a GIST index will behave in a way
that would support calling that a primary key.  b-tree indexes, on the
other hand, have basically only one behavior, so they're easy.

Also, as you mention, extrapolating behavior that's not really equality
to situations like foreign keys gets pretty interesting pretty fast.
An exclusion constraint using && might ensure that no two values in the
column are identical, but it would not be enough to ensure that a proposed
FK row can't && with more than one PK row.  So what then?

            regards, tom lane

On 03/14/2018 11:10 AM, Jeremy Finzel wrote:
> 
> On Wed, Mar 14, 2018 at 8:33 AM, Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 03/14/2018 06:19 AM, Jeremy Finzel wrote:
> 
>         Hello!  From all that I can tell, it is not possible using a
>         btree_gist index as a primary key.  If so, why not?  I have a
>         table with this gist
> 
> 
>     https://www.postgresql.org/docs/10/static/btree-gist.html
>     <https://www.postgresql.org/docs/10/static/btree-gist.html>
> 
>     "In general, these operator classes will not outperform the
>     equivalent standard B-tree index methods, and they lack one major
>     feature of the standard B-tree code: the ability to enforce uniqueness."
> 
> 
>         index which truly ought to be its primary key.  as_of_date is of
>         range date type:
> 
>         EXCLUDE USING gist (id WITH =, as_of_date WITH &&)
> 
>         Any direction here would be much appreciated.
> 
>         Right now, I am forced to create a redundant btree index UNIQUE,
>         btree (id, lower(as_of_date)) in order to have a primary key on
>         the table.
> 
>         Thanks!
>         Jeremy
> 
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 
> 
> Thank you for the ref.  But I don't understand how an exclusion 
> constraint does not have "the ability to enforce uniqueness" unless they 
> just mean that is the case "under the covers of postgres".  That is 
> exactly what it does, right?  By the definition of the exclusion index I 
> have above, there cannot be more than one row with the same id and 
> as_of_date values.

https://www.postgresql.org/docs/10/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE

"The EXCLUDE clause defines an exclusion constraint, which guarantees 
that if any two rows are compared on the specified column(s) or 
expression(s) using the specified operator(s), not all of these 
comparisons will return TRUE. If all of the specified operators test for 
equality, this is equivalent to a UNIQUE constraint, although an 
ordinary unique constraint will be faster. ..."

I have always taken the above to mean that while is possible to create 
an EXCLUDE that enforces uniqueness that operation is internal to the 
EXCLUDE and is not knowable to Postgres when it is looking for a UNIQUE 
index. Probably because an EXCLUDE can be non-unique.


> 
> Thanks,
> Jeremy


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, 14 Mar 2018 11:28:59 -0700
Paul Jungwirth <pj@illuminatedcomputing.com> wrote:

> > EXCLUDE USING gist (id WITH =, as_of_date WITH &&)  

> null), but maybe there are other primary-key duties it doesn't meet, 
> like defining foreign keys that reference it. I've been on-and-off 

The PK does provide a unique index.
It may be worth adding and index on 

    id where ( upper as_of_date = infinity )

for faster searches of current id's (assuming, of course, that there
is an infinite value for upper of as_of_date).


You will also need a PK if the table is used in foreign key 
constraints -- the other table has to ref this table's PK.
-- 
Steven Lembark                                       1505 National Ave
Workhorse Computing                                 Rockford, IL 61103
lembark@wrkhors.com                                    +1 888 359 3508

On 03/14/2018 11:44 AM, Tom Lane wrote:> Also, as you mention, 
extrapolating behavior that's not really equality
> to situations like foreign keys gets pretty interesting pretty fast.
> An exclusion constraint using && might ensure that no two values in the
> column are identical, but it would not be enough to ensure that a proposed
> FK row can't && with more than one PK row.  So what then?

This is (perhaps) a digression from the OP's question, but in temporal 
databases it is fine if the child record's as_of_time overlaps with 
multiple records from the parent table. In fact that would be pretty 
normal. What's required is that the child's as_of_time is completely 
covered by the "sum" of the as_of_times of the parent records with a 
matching ID. For example:

houses
id  as_of_time                appraisal
1   [2016-01-01, 2017-01-01)  $200k
1   [2017-01-01, 2018-01-01)  $230k

rooms
id  as_of_time                house_id
1   [2016-01-01, 2018-01-01)  1

If you had a "temporal foreign key" from rooms to houses, that example 
should be valid. I understand that's not possible today with normal 
foreign keys, and maybe it's too specific to be desirable as a 
general-purpose feature, but that's what the github extension I linked 
to tries to offer.

-- 
Paul              ~{:-)
pj@illuminatedcomputing.com