pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedPERIOD foreign key feature
7+ messages / 4 participants
[nested] [flat]
Commits from this thread:
2024-05-09 c3db1f30cb doc: clarify PERIOD and WITHOUT OVERLAPS in CREATE TABLE
* PERIOD foreign key feature
@ 2024-05-07 14:54 Bruce Momjian <bruce@momjian.us>
2024-05-07 15:23 ` Re: PERIOD foreign key feature David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Bruce Momjian @ 2024-05-07 14:54 UTC (permalink / raw)
To: pgsql-hackers; +Cc: Peter Eisentraut <peter@eisentraut.org>
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....
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?
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PERIOD foreign key feature
2024-05-07 14:54 PERIOD foreign key feature Bruce Momjian <bruce@momjian.us>
@ 2024-05-07 15:23 ` David G. Johnston <david.g.johnston@gmail.com>
2024-05-07 16:43 ` Re: PERIOD foreign key feature Paul Jungwirth <pj@illuminatedcomputing.com>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2024-05-07 15:23 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-hackers; Peter Eisentraut <peter@eisentraut.org>
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....
>
> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PERIOD foreign key feature
2024-05-07 14:54 PERIOD foreign key feature Bruce Momjian <bruce@momjian.us>
2024-05-07 15:23 ` Re: PERIOD foreign key feature David G. Johnston <david.g.johnston@gmail.com>
@ 2024-05-07 16:43 ` Paul Jungwirth <pj@illuminatedcomputing.com>
2024-05-08 12:29 ` Re: PERIOD foreign key feature Peter Eisentraut <peter@eisentraut.org>
0 siblings, 1 reply; 7+ messages in thread
From: Paul Jungwirth @ 2024-05-07 16:43 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org
On 5/7/24 08:23, David G. Johnston wrote:
> On Tue, May 7, 2024 at 7:54 AM Bruce Momjian <bruce@momjian.us <mailto:bruce@momjian.us>> wrote:
> 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.
Yes, David is correct here on all points. I like his suggestion to clarify the language here also.
If you need a patch from me let me know, but I assume it's something a committer can just make happen?
Yours,
--
Paul ~{:-)
pj@illuminatedcomputing.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PERIOD foreign key feature
2024-05-07 14:54 PERIOD foreign key feature Bruce Momjian <bruce@momjian.us>
2024-05-07 15:23 ` Re: PERIOD foreign key feature David G. Johnston <david.g.johnston@gmail.com>
2024-05-07 16:43 ` Re: PERIOD foreign key feature Paul Jungwirth <pj@illuminatedcomputing.com>
@ 2024-05-08 12:29 ` Peter Eisentraut <peter@eisentraut.org>
2024-05-08 14:44 ` Re: PERIOD foreign key feature Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 7+ messages in thread
From: Peter Eisentraut @ 2024-05-08 12:29 UTC (permalink / raw)
To: Paul Jungwirth <pj@illuminatedcomputing.com>; pgsql-hackers@lists.postgresql.org
On 07.05.24 18:43, Paul Jungwirth wrote:
> On 5/7/24 08:23, David G. Johnston wrote:
>> On Tue, May 7, 2024 at 7:54 AM Bruce Momjian <bruce@momjian.us
>> <mailto:bruce@momjian.us>> wrote:
>> 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.
>
> Yes, David is correct here on all points. I like his suggestion to
> clarify the language here also. If you need a patch from me let me know,
> but I assume it's something a committer can just make happen?
In principle yes, but it's also very helpful if someone produces an
actual patch file, with complete commit message, credits, mailing list
link, etc.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PERIOD foreign key feature
2024-05-07 14:54 PERIOD foreign key feature Bruce Momjian <bruce@momjian.us>
2024-05-07 15:23 ` Re: PERIOD foreign key feature David G. Johnston <david.g.johnston@gmail.com>
2024-05-07 16:43 ` Re: PERIOD foreign key feature Paul Jungwirth <pj@illuminatedcomputing.com>
2024-05-08 12:29 ` Re: PERIOD foreign key feature Peter Eisentraut <peter@eisentraut.org>
@ 2024-05-08 14:44 ` Bruce Momjian <bruce@momjian.us>
2024-05-09 03:47 ` Re: PERIOD foreign key feature Paul Jungwirth <pj@illuminatedcomputing.com>
0 siblings, 1 reply; 7+ messages in thread
From: Bruce Momjian @ 2024-05-08 14:44 UTC (permalink / raw)
To: Peter Eisentraut <peter@eisentraut.org>; +Cc: Paul Jungwirth <pj@illuminatedcomputing.com>; pgsql-hackers@lists.postgresql.org
On Wed, May 8, 2024 at 02:29:34PM +0200, Peter Eisentraut wrote:
> > > 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.
> >
> > Yes, David is correct here on all points. I like his suggestion to
> > clarify the language here also. If you need a patch from me let me know,
> > but I assume it's something a committer can just make happen?
>
> In principle yes, but it's also very helpful if someone produces an actual
> patch file, with complete commit message, credits, mailing list link, etc.
I am ready to do the work, but waited a day for Peter to reply, since he
was the author of the text.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PERIOD foreign key feature
2024-05-07 14:54 PERIOD foreign key feature Bruce Momjian <bruce@momjian.us>
2024-05-07 15:23 ` Re: PERIOD foreign key feature David G. Johnston <david.g.johnston@gmail.com>
2024-05-07 16:43 ` Re: PERIOD foreign key feature Paul Jungwirth <pj@illuminatedcomputing.com>
2024-05-08 12:29 ` Re: PERIOD foreign key feature Peter Eisentraut <peter@eisentraut.org>
2024-05-08 14:44 ` Re: PERIOD foreign key feature Bruce Momjian <bruce@momjian.us>
@ 2024-05-09 03:47 ` Paul Jungwirth <pj@illuminatedcomputing.com>
2024-05-09 20:34 ` Re: PERIOD foreign key feature Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 7+ messages in thread
From: Paul Jungwirth @ 2024-05-09 03:47 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; Peter Eisentraut <peter@eisentraut.org>; +Cc: pgsql-hackers@lists.postgresql.org
On 5/8/24 07:44, Bruce Momjian wrote:
> On Wed, May 8, 2024 at 02:29:34PM +0200, Peter Eisentraut wrote:
>>> Yes, David is correct here on all points. I like his suggestion to
>>> clarify the language here also. If you need a patch from me let me know,
>>> but I assume it's something a committer can just make happen?
>>
>> In principle yes, but it's also very helpful if someone produces an actual
>> patch file, with complete commit message, credits, mailing list link, etc.
>
> I am ready to do the work, but waited a day for Peter to reply, since he
> was the author of the text.
Here is a patch for this.
Yours,
--
Paul ~{:-)
pj@illuminatedcomputing.com
Attachments:
0001-Reword-docs-for-temporal-PKs-with-implicit-referent.patch (text/x-patch, 2.1K) download
From d97a60b7e56c57a242668609c8fb82e6a6a32506 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Wed, 8 May 2024 20:41:05 -0700
Subject: [PATCH] Reword docs for temporal PKs with implicit referent
The old docs had confusing language about "one side" of the foreign key,
which could mean either the referenced primary key itself or the
REFERENCES clause of the FK declaration.
Author: David G. Johnston <david.g.johnston@gmail.com>
Discussion: https://www.postgresql.org/message-id/ZjpApuq8I9DE5Elv%40momjian.us
---
doc/src/sgml/ref/create_table.sgml | 12 +++++++-----
1 file changed, 7 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 02f31d2d6fd..75f06bc49cc 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1184,11 +1184,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
referent for its entire duration. This column must be a range or
multirange type. 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>.
+ OVERLAPS</literal>. Finally, if the foreign key has a PERIOD
+ <replaceable class="parameter">column_name</replaceable> specification
+ the corresponding <replaceable class="parameter">refcolumn</replaceable>,
+ if present, must also be marked <literal>PERIOD</literal>. If the
+ <replaceable class="parameter">refcolumn</replaceable> clause is omitted,
+ and thus the reftable's primary key constraint chosen, the primary key
+ must have its final column marked <literal>WITHOUT OVERLAPS</literal>.
</para>
<para>
--
2.42.0
* Re: PERIOD foreign key feature
2024-05-07 14:54 PERIOD foreign key feature Bruce Momjian <bruce@momjian.us>
2024-05-07 15:23 ` Re: PERIOD foreign key feature David G. Johnston <david.g.johnston@gmail.com>
2024-05-07 16:43 ` Re: PERIOD foreign key feature Paul Jungwirth <pj@illuminatedcomputing.com>
2024-05-08 12:29 ` Re: PERIOD foreign key feature Peter Eisentraut <peter@eisentraut.org>
2024-05-08 14:44 ` Re: PERIOD foreign key feature Bruce Momjian <bruce@momjian.us>
2024-05-09 03:47 ` Re: PERIOD foreign key feature Paul Jungwirth <pj@illuminatedcomputing.com>
@ 2024-05-09 20:34 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 7+ messages in thread
From: Bruce Momjian @ 2024-05-09 20:34 UTC (permalink / raw)
To: Paul Jungwirth <pj@illuminatedcomputing.com>; +Cc: Peter Eisentraut <peter@eisentraut.org>; pgsql-hackers@lists.postgresql.org
On Wed, May 8, 2024 at 08:47:45PM -0700, Paul Jungwirth wrote:
> On 5/8/24 07:44, Bruce Momjian wrote:
> > On Wed, May 8, 2024 at 02:29:34PM +0200, Peter Eisentraut wrote:
> > > > Yes, David is correct here on all points. I like his suggestion to
> > > > clarify the language here also. If you need a patch from me let me know,
> > > > but I assume it's something a committer can just make happen?
> > >
> > > In principle yes, but it's also very helpful if someone produces an actual
> > > patch file, with complete commit message, credits, mailing list link, etc.
> >
> > I am ready to do the work, but waited a day for Peter to reply, since he
> > was the author of the text.
>
> Here is a patch for this.
Thanks, patch applied.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-05-09 20:34 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-07 14:54 PERIOD foreign key feature Bruce Momjian <bruce@momjian.us>
2024-05-07 15:23 ` David G. Johnston <david.g.johnston@gmail.com>
2024-05-07 16:43 ` Paul Jungwirth <pj@illuminatedcomputing.com>
2024-05-08 12:29 ` Peter Eisentraut <peter@eisentraut.org>
2024-05-08 14:44 ` Bruce Momjian <bruce@momjian.us>
2024-05-09 03:47 ` Paul Jungwirth <pj@illuminatedcomputing.com>
2024-05-09 20:34 ` Bruce Momjian <bruce@momjian.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox