Re: PERIOD foreign key feature

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: PERIOD foreign key feature
Дата
Msg-id CAKFQuwZZObJj964eRkw6=kEEA3S_WQiz4Z+r4kk_yEaH9-r_tw@mail.gmail.com
обсуждение исходный текст
Ответ на PERIOD foreign key feature  (Bruce Momjian <bruce@momjian.us>)
Ответы Re: PERIOD foreign key feature
Список pgsql-hackers
On Tue, May 7, 2024 at 7:54 AM Bruce Momjian <bruce@momjian.us> wrote:
In this commit:

        commit 34768ee3616
        Author: Peter Eisentraut <peter@eisentraut.org>
        Date:   Sun Mar 24 07:37:13 2024 +0100

            Add temporal FOREIGN KEY contraints

            Add PERIOD clause to foreign key constraint definitions.  This is
            supported for range and multirange types.  Temporal foreign keys check
            for range containment instead of equality.

            This feature matches the behavior of the SQL standard temporal foreign
            keys, but it works on PostgreSQL's native ranges instead of SQL's
            "periods", which don't exist in PostgreSQL (yet).

            Reference actions ON {UPDATE,DELETE} {CASCADE,SET NULL,SET DEFAULT}
            are not supported yet.

            Author: Paul A. Jungwirth <pj@illuminatedcomputing.com>
            Reviewed-by: Peter Eisentraut <peter@eisentraut.org>
            Reviewed-by: jian he <jian.universality@gmail.com>
            Discussion: https://www.postgresql.org/message-id/flat/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com

this text was added to create_table.sgml:

        In addition, the referenced table must have a primary
        key or unique constraint declared with <literal>WITHOUT
-->     OVERLAPS</literal>.  Finally, if one side of the foreign key
-->     uses <literal>PERIOD</literal>, the other side must too.  If the
        <replaceable class="parameter">refcolumn</replaceable> list is
        omitted, the <literal>WITHOUT OVERLAPS</literal> part of the
        primary key is treated as if marked with <literal>PERIOD</literal>.

In the two marked lines, it says "if one side of the foreign key uses
PERIOD, the other side must too."  However, looking at the example
queries, it seems like if the foreign side has PERIOD, the primary side
must have WITHOUT OVERLAPS, not PERIOD.

Does this doc text need correcting?


The text is factually correct, though a bit hard to parse.

"the other side" refers to the part after "REFERENCES":

FOREIGN KEY ( column_name [, ... ] [, PERIOD column_name ] ) REFERENCES reftable [ ( refcolumn [, ... ] [, PERIOD column_name ] ) ]

***(shouldn't the second occurrence be [, PERIOD refcolum] ?)

The text is pointing out that since the refcolumn specification is optional you may very well not see a second PERIOD keyword in the clause.  Instead it will be inferred from the PK.

Maybe:

Finally, if the foreign key has a PERIOD column_name specification the corresponding refcolumn, if present, must also be marked PERIOD.  If the refcolumn clause is omitted, and thus the reftable's primary key constraint chosen, the primary key must have its final column marked WITHOUT OVERLAPS.

David J.

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Bruce Momjian
Дата:
Сообщение: PERIOD foreign key feature
Следующее
От: Nathan Bossart
Дата:
Сообщение: Re: allow changing autovacuum_max_workers without restarting