Re: Extra periods in pg_dump messages
> On 30 Nov 2023, at 02:39, Kyotaro Horiguchi wrote: > In the bleeding-edge version of pg_dump, when a conditionspecifying an > index, for example, is described in an object filter file, the > following message is output. However, there is a period at the end of > the line. Shouldn't this be removed? Yes, ending with a period is for detail and hint messages. Fixed. -- Daniel Gustafsson
Extra periods in pg_dump messages
Sorry for the sequential mails.
In the bleeding-edge version of pg_dump, when a conditionspecifying an
index, for example, is described in an object filter file, the
following message is output. However, there is a period at the end of
the line. Shouldn't this be removed?
$ pg_dump --filter=/tmp/hoge.filter
pg_dump: error: invalid format in filter read from "/tmp/hoge.filter" on line
1: include filter for "index" is not allowed.
The attached patch includes modifications related to the calls to
pg_log_filter_error().
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 57c6836b88..ce50566c3a 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -18812,7 +18812,7 @@ read_dump_filters(const char *filename, DumpOptions *dopt)
case FILTER_OBJECT_TYPE_TABLE_DATA:
case FILTER_OBJECT_TYPE_TABLE_DATA_AND_CHILDREN:
case FILTER_OBJECT_TYPE_TRIGGER:
- pg_log_filter_error(, _("%s filter for \"%s\" is not allowed."),
+ pg_log_filter_error(, _("%s filter for \"%s\" is not allowed"),
"include",
filter_object_type_name(objtype));
exit_nicely(1);
@@ -18851,7 +18851,7 @@ read_dump_filters(const char *filename, DumpOptions *dopt)
case FILTER_OBJECT_TYPE_TRIGGER:
case FILTER_OBJECT_TYPE_EXTENSION:
case FILTER_OBJECT_TYPE_FOREIGN_DATA:
- pg_log_filter_error(, _("%s filter for \"%s\" is not allowed."),
+ pg_log_filter_error(, _("%s filter for \"%s\" is not allowed"),
"exclude",
filter_object_type_name(objtype));
exit_nicely(1);
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index 1b974cf7e8..92389353a4 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -1969,7 +1969,7 @@ read_dumpall_filters(const char *filename, SimpleStringList *pattern)
{
if (comtype == FILTER_COMMAND_TYPE_INCLUDE)
{
- pg_log_filter_error(, _("%s filter for \"%s\" is not allowed."),
+ pg_log_filter_error(, _("%s filter for \"%s\" is not allowed"),
"include",
filter_object_type_name(objtype));
exit_nicely(1);
@@ -1989,7 +1989,7 @@ read_dumpall_filters(const char *filename, SimpleStringList *pattern)
case FILTER_OBJECT_TYPE_SCHEMA:
case FILTER_OBJECT_TYPE_TABLE:
case FILTER_OBJECT_TYPE_TABLE_AND_CHILDREN:
-pg_log_filter_error(, _("unsupported filter object."));
+pg_log_filter_error(, _("unsupported filter object"));
exit_nicely(1);
break;
diff --git a/src/bin/pg_dump/pg_restore.c b/src/bin/pg_dump/pg_restore.c
index 1459e02263..c3beacdec1 100644
--- a/src/bin/pg_dump/pg_restore.c
+++ b/src/bin/pg_dump/pg_restore.c
@@ -535,7 +535,7 @@ read_restore_filters(const char *filename, RestoreOptions *opts)
case FILTER_OBJECT_TYPE_DATABASE:
case FILTER_OBJECT_TYPE_EXTENSION:
case FILTER_OBJECT_TYPE_FOREIGN_DATA:
- pg_log_filter_error(, _("%s filter for \"%s\" is not allowed."),
+ pg_log_filter_error(, _("%s filter for \"%s\" is not allowed"),
"include",
filter_object_type_name(objtype));
exit_nicely(1);
@@ -581,7 +581,7 @@ read_restore_filters(const char *filename, RestoreOptions *opts)
case FILTER_OBJECT_TYPE_TABLE:
case FILTER_OBJECT_TYPE_TABLE_AND_CHILDREN:
case FILTER_OBJECT_TYPE_TRIGGER:
- pg_log_filter_error(, _("%s filter for \"%s\" is not allowed."),
+ pg_log_filter_error(, _("%s filter for \"%s\" is not allowed"),
"exclude",
filter_object_type_name(objtype));
exit_nicely(1);
Foreign Key constraints on xocolatl/periods
Hi, I'm sending this to pgsql-hackers because Vik Fearing (xocolatl), the reviewer of https://commitfest.postgresql.org/30/2316 also has a repository with a pgsql implementation of said functionalities: https://github.com/xocolatl/periods. I have stumbled upon a probable issue (https://github.com/xocolatl/periods/issues/27), can anyone take a look and confirm if the current behavior is the expected? Thanks!
Re: SQL:2011 PERIODS vs Postgres Ranges?
On Thu, Jul 15, 2021 at 6:21 AM Ibrar Ahmed wrote: > Based on last comments of Paul and David S I am changing the status to > "Waiting on Author". I thought the subject was quite out of date, so I sent my last patch here: https://www.postgresql.org/message-id/CA%2BrenyUApHgSZF9-nd-a0%2BOPGharLQLO%3DmDHcY4_qQ0%2BnoCUVg%40mail.gmail.com I also added that thread to the commitfest item. I'm going to change the commitfest entry back to Needs Review, but please let me know if you disagree. Sorry for the confusion! Yours, Paul
Re: SQL:2011 PERIODS vs Postgres Ranges?
On Fri, Apr 9, 2021 at 4:54 PM David Steele wrote: > On 4/8/21 7:40 PM, Paul A Jungwirth wrote: > > On Thu, Apr 8, 2021 at 7:22 AM David Steele wrote: > >> > >> Paul, you can submit to the next CF when you are ready with a new patch. > > > > Thanks David! I've made a lot of progress but still need to finish > > support for CASCADE on temporal foreign keys. I've been swamped with > > other things, but hopefully I can get something during this current > > CF. > > The next CF starts on July 1 so you have some time. > > Regards, > -- > -David > da...@pgmasters.net Based on last comments of Paul and David S I am changing the status to "Waiting on Author". -- Ibrar Ahmed
Re: SQL:2011 PERIODS vs Postgres Ranges?
On 4/8/21 7:40 PM, Paul A Jungwirth wrote: On Thu, Apr 8, 2021 at 7:22 AM David Steele wrote: Paul, you can submit to the next CF when you are ready with a new patch. Thanks David! I've made a lot of progress but still need to finish support for CASCADE on temporal foreign keys. I've been swamped with other things, but hopefully I can get something during this current CF. The next CF starts on July 1 so you have some time. Regards, -- -David da...@pgmasters.net
Re: SQL:2011 PERIODS vs Postgres Ranges?
On Thu, Apr 8, 2021 at 7:22 AM David Steele wrote: > > Paul, you can submit to the next CF when you are ready with a new patch. Thanks David! I've made a lot of progress but still need to finish support for CASCADE on temporal foreign keys. I've been swamped with other things, but hopefully I can get something during this current CF. Paul
Re: SQL:2011 PERIODS vs Postgres Ranges?
On 10/27/20 12:34 PM, Paul Jungwirth wrote: On 10/27/20 7:11 AM, Ibrar Ahmed wrote: I have spent some more time on the patch and did a lot of cleanup along with some fixes, compilation errors, and warnings. Thank you for taking a look at this! I've been swamped with ordinary work and haven't had a chance to focus on it for a while, but I'm hoping to make some improvements over the coming holidays, especially based on feedback from my talk at PgCon. There are a handful of small specific things I'd like to do, and then one big thing: add support for PERIODs. Vik said I could include his old patch for PERIODs, so I'd like to get that working on the latest master, and then rebase my own work on top of it. Then we can accept either ranges or PERIODs in various places (marked by TODOs in the code). Vik also pointed out a way to check foreign keys without using range_agg. He thinks it may even be more efficient. On the other hand it's a much more complicated SQL statement. I'd like to do a performance comparison to get concrete numbers, but if we did use his query, then this patch wouldn't depend on multiranges anymore---which seems like a big aid to moving it forward. Assuming multiranges gets committed, we can always swap in the range_agg query depending on the performance comparison results. I apologize for the slow progress here, and thank you for your help! Looks like Ibrar reopened this patch in the 2020-09 CF rather than moving it to a new one. Given that Paul has not had a chance to look at it since then I'm setting it back to RwF. Paul, you can submit to the next CF when you are ready with a new patch. Regards, -- -David da...@pgmasters.net
Re: SQL:2011 PERIODS vs Postgres Ranges?
On 10/27/20 7:11 AM, Ibrar Ahmed wrote:
I have spent some more time on the patch and did a lot of cleanup along
with some fixes, compilation errors, and warnings.
Thank you for taking a look at this! I've been swamped with ordinary
work and haven't had a chance to focus on it for a while, but I'm hoping
to make some improvements over the coming holidays, especially based on
feedback from my talk at PgCon. There are a handful of small specific
things I'd like to do, and then one big thing: add support for PERIODs.
Vik said I could include his old patch for PERIODs, so I'd like to get
that working on the latest master, and then rebase my own work on top of
it. Then we can accept either ranges or PERIODs in various places
(marked by TODOs in the code).
Vik also pointed out a way to check foreign keys without using
range_agg. He thinks it may even be more efficient. On the other hand
it's a much more complicated SQL statement. I'd like to do a performance
comparison to get concrete numbers, but if we did use his query, then
this patch wouldn't depend on multiranges anymore---which seems like a
big aid to moving it forward. Assuming multiranges gets committed, we
can always swap in the range_agg query depending on the performance
comparison results.
I apologize for the slow progress here, and thank you for your help!
Yours,
--
Paul ~{:-)
p...@illuminatedcomputing.com
Re: SQL:2011 PERIODS vs Postgres Ranges?
On Thu, Sep 17, 2020 at 04:51:01PM +0900, Michael Paquier wrote: > This patch had no reviews, unfortunately it cannot be applied > cleanly. Could you send a rebase please? This had no replies after two weeks, so I have marked the patch as RwF. Please feel free to resubmit if you are planning to work more on that. -- Michael signature.asc Description: PGP signature
Re: SQL:2011 PERIODS vs Postgres Ranges?
On Wed, Mar 11, 2020 at 04:27:53PM -0700, Paul A Jungwirth wrote: > Here is a patch rebasing on master (meant to be applied on top of my > other multirange patch) and newly including UPDATE/DELETE FOR PORTION > OF. FOR PORTION OF works on any table with a temporal primary key. It > restricts the UPDATE/DELETE to the given time frame, and then if the > affected row(s) had any "leftovers" above or below the targeted range, > it INSERTs new rows to preserve the untouched intervals. This patch had no reviews, unfortunately it cannot be applied cleanly. Could you send a rebase please? -- Michael signature.asc Description: PGP signature
Re: SQL:2011 PERIODS vs Postgres Ranges?
On Mon, Nov 11, 2019 at 12:13:20PM -0800, Paul A Jungwirth wrote: > I could use some guidance on where in the query-processing pipeline I > should implement some things here. Basically if you say > [...] Paul, please be careful to update correctly the entry of the patch in the CF app. This was marked as waiting on author, but you are obviously looking for reviews. I have updated the status of the patch accordingly, then moved it again. -- Michael signature.asc Description: PGP signature
Re: SQL:2011 PERIODS vs Postgres Ranges?
On Wed, Nov 6, 2019 at 9:31 PM Paul A Jungwirth wrote: > I've also added some progress on adding FOR PORTION OF to UPDATE and DELETE > (mostly UPDATE). I could use some guidance on where in the query-processing pipeline I should implement some things here. Basically if you say UPDATE t FOR PORTION OF valid_at FROM t1 TO t2 then we need to do several things: - Add a qual like `valid_at && tsrange(t1, t2)`. (I'll assume valid_at is a tsrange column for example's sake, but really it can be any range type. Also valid_at may be a PERIOD instead of a range, which means the start/end are two concrete columns instead, but that doesn't change anything notable here.) - Add a target entry like `SET valid_at = valid_at * tsrange(t1, t2)`. (* = intersection. Basically each bound should be truncated to fit within t1/t2.) - If either bound was "cut" then also do an INSERT to restore the cut-off part, leaving all columns unchanged except for the time part. (DELETE t FOR PORTION OF is very similar.) I think I understand the ModifyTable executor node enough to be able to add the optional INSERTs there when necessary. Adding the qual and the target entry is where I want advice. So far I've been able to add a ForPortionOfClause when parsing and a ForPortionOfExpr when analyzing (much like how we handle ON CONFLICT). I could use those to add a qual and a target list entry during analysis (in fact I've tried that and it seems to work), but I'm pretty sure that's wrong. I recall a long post to pgsql-hackers a month or three back lamenting how new contributors often do work in the analysis phase that should happen later. (I can't find that now, but if anyone has a link I'd appreciate it!) Some considerations (not an exhaustive list): - FOR PORTION OF should work on partitioned tables. - It should work on automatically-updateable views. - It should work on views with CHECK OPTION. - It should work on views with an UPDATE rule. - It should do the right thing for EXPLAIN output (whatever that is). - If a function does a FOR PORTION OF command, then printing the function definition should show that clause (and nothing extra). - Same for printing a rule definition. - Probably if you give a FOR PORTION OF we should forbid you from SETting the time column(s) at the same time, since we want to set them automatically. - Triggers should work normally. (We *should* fire ROW triggers for the INSERTs of the "cut off" bits. Mariadb fires them in this order, which seems correct to me: BEFORE UPDATE, BEFORE INSERT, AFTER INSERT, BEFORE INSERT, AFTER INSERT, AFTER UPDATE. I guess we probably want to fire STATEMENT triggers too, probably once for each INSERT. I'll check what other systems do there.) So I'm thinking the right place to add the quals & target entry is either the end of the rewriting phase or the beginning of the planning phase. (I can still build the expressions in the analysis phase, but I need to keep them "off to the side" in a new forPortionOf attribute until the right time.) We definitely want the extra qual soon enough to help choose indexes. Perhaps we even want to see it in EXPLAIN output (which happens if I add it during analysis); personally I kind of find that helpful. Do we want to add it after processing rewrite rules (and will that change EXPLAIN output)? For adding the target entry, if we are forbidding the user from SETting things, that check needs to happen after processing rewrite rules, right? (Of course it doesn't hurt to check in several places if there is some reason to do that.) Btw I thought about whether we could implement this feature completely on top of either triggers or rules, but I don't think it's quite that simple. Basically: because you could also UPDATE/DELETE the table *without* a FOR PORTION OF, sometimes we need to do the extra things and sometimes not, and we need a way of knowing which is which. And then supporting PERIODs requires a little extra "magic" beyond that. But if someone has a great idea I'm open to hearing about it. :-) Thanks, Paul
Re: SQL:2011 PERIODS vs Postgres Ranges?
On Tue, Aug 6, 2019 at 8:28 PM Paul Jungwirth
wrote:
> Hi Ibrar,
>
> On 8/6/19 3:26 AM, Ibrar Ahmed wrote:
> > - Why we are not allowing any other datatype other than ranges in the
> > primary key. Without that there is no purpose of a primary key.
>
> A temporal primary key always has at least one ordinary column (of any
> type), so it is just a traditional primary key *plus* a PERIOD and/or
> range column to indicate when the record was true.
>
> > - Thinking about some special token to differentiate between normal
> > primary key and temporal primary key
>
> There is already some extra syntax. For the time part of a PK, you say
> `WITHOUT OVERLAPS`, like this:
>
> CONSTRAINT pk_on_t PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
>
> In this example `id` is an ordinary column, and `valid_at` is either a
> Postgres range or a SQL:2011 PERIOD. (The latter is not yet implemented
> in my patch but there are some placeholder comments.)
>
> Similarly a foreign key has one or more traditional columns *plus* a
> range/PERIOD. It needs to have a range/PERIOD on both sides. It too has
> some special syntax, but instead of `WITHOUT OVERLAPS` it is `PERIOD`.
> (Don't blame me, I didn't write the standard :-) So here is an example:
>
> CONSTRAINT fk_t2_to_t FOREIGN KEY (id, PERIOD valid_at)
>REFERENCES t (id, PERIOD valid_at)
>
> You should be able to see my changes to gram.y to support this new syntax.
>
> I hope this clears up how it works! I'm happy to answer more questions
> if you have any. Also if you want to read more:
>
> - This paper by Kulkarni & Michels is a 10-page overview of SQL:2011:
>
>
> https://sigmodrecord.org/publications/sigmodRecord/1209/pdfs/07.industry.kulkarni.pdf
>
> - This is a talk I gave at PGCon 2019 going over the concepts, with a
> lot of pictures. You can find text, slides, and a link to the video here:
>
> https://github.com/pjungwir/postgres-temporal-talk
>
> - This link is ostensibly an annotated bibliography but really tells a
> story about how the research has developed:
>
>
> https://illuminatedcomputing.com/posts/2017/12/temporal-databases-bibliography/
>
> - There is also some discussion about PERIODs vs ranges upthread here,
> as well as here:
>
> https://www.postgresql-archive.org/Periods-td6022563.html
>
>
Thanks, Paul for the explanation. I think its good start, now I am looking
at the
range_agg patch to integrate that with that and test that.
> Yours,
>
> --
> Paul ~{:-)
> p...@illuminatedcomputing.com
>
--
Ibrar Ahmed
Re: SQL:2011 PERIODS vs Postgres Ranges?
Hi Ibrar,
On 8/6/19 3:26 AM, Ibrar Ahmed wrote:
- Why we are not allowing any other datatype other than ranges in the
primary key. Without that there is no purpose of a primary key.
A temporal primary key always has at least one ordinary column (of any
type), so it is just a traditional primary key *plus* a PERIOD and/or
range column to indicate when the record was true.
- Thinking about some special token to differentiate between normal
primary key and temporal primary key
There is already some extra syntax. For the time part of a PK, you say
`WITHOUT OVERLAPS`, like this:
CONSTRAINT pk_on_t PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
In this example `id` is an ordinary column, and `valid_at` is either a
Postgres range or a SQL:2011 PERIOD. (The latter is not yet implemented
in my patch but there are some placeholder comments.)
Similarly a foreign key has one or more traditional columns *plus* a
range/PERIOD. It needs to have a range/PERIOD on both sides. It too has
some special syntax, but instead of `WITHOUT OVERLAPS` it is `PERIOD`.
(Don't blame me, I didn't write the standard :-) So here is an example:
CONSTRAINT fk_t2_to_t FOREIGN KEY (id, PERIOD valid_at)
REFERENCES t (id, PERIOD valid_at)
You should be able to see my changes to gram.y to support this new syntax.
I hope this clears up how it works! I'm happy to answer more questions
if you have any. Also if you want to read more:
- This paper by Kulkarni & Michels is a 10-page overview of SQL:2011:
https://sigmodrecord.org/publications/sigmodRecord/1209/pdfs/07.industry.kulkarni.pdf
- This is a talk I gave at PGCon 2019 going over the concepts, with a
lot of pictures. You can find text, slides, and a link to the video here:
https://github.com/pjungwir/postgres-temporal-talk
- This link is ostensibly an annotated bibliography but really tells a
story about how the research has developed:
https://illuminatedcomputing.com/posts/2017/12/temporal-databases-bibliography/
- There is also some discussion about PERIODs vs ranges upthread here,
as well as here:
https://www.postgresql-archive.org/Periods-td6022563.html
Yours,
--
Paul ~{:-)
p...@illuminatedcomputing.com
Re: SQL:2011 PERIODS vs Postgres Ranges?
Hi Paul, On Mon, Aug 5, 2019 at 3:11 AM Paul A Jungwirth wrote: > On Fri, Aug 2, 2019 at 1:49 PM Ibrar Ahmed wrote: > > I did some clean-up on this patch. I have also refactored a small > portion of the code > > to reduce the footprint of the patch. For simplicity, I have divided the > patch into 6 > > patches, now it is easy to review and debug. > > Please follow the PostgreSQL coding guidelines. I have found places > where you missed that, secondly code even in WIP stage must not have > WARNING because it looks ugly. > > Thank you for the cleanup Ibrar! I'll try to stick to the coding > standards more closely going forward. If you have any review comments > I would certainly appreciate them, especially about the overall > approach. I know that the implementation in its current form is not > very tasteful, but I wanted to get some feedback on the ideas. > > I have reviewed the main design, and in my opinion, it is a good start. - Why we are not allowing any other datatype other than ranges in the primary key. Without that there is no purpose of a primary key. - Thinking about some special token to differentiate between normal primary key and temporal primary key Also just to reiterate: this patch depends on my other CF entry > (range_agg), whose scope has expanded considerably. Right now I'm > focusing on that. And if you're trying to make this code work, it's > important to apply the range_agg patch first, since the temporal > foreign key implementation calls that function. > > Also: since this patch raises the question of how to conform to > SQL:2011 while still supporting Postgres range types, I wrote an > article that surveys SQL:2011 temporal features in MariaDB, DB2, > Oracle, and MS SQL Server: > > https://illuminatedcomputing.com/posts/2019/08/sql2011-survey/ > > A few highlights are: > > - Everyone lets you define PERIODs, but what you can do with them is > still *very* limited. > - No one treats PERIODs as first-class types or expressions; they are > more like table metadata. > > - Oracle PERIODs do permit NULL start/end values, and it interprets > them as "unbounded". That goes against the standard but since it's > what Postgres does with ranges, it suggests to me that maybe we should > follow their lead. Anyway I think a NULL is nicer than a sentinel for > this purpose. > That is an open debate, that we want to strictly follow the standard or map that to PostgreSQL range type which allows NULL. But how you will define a primary key on that? > > Regards, > Paul > -- Ibrar Ahmed
Re: SQL:2011 PERIODS vs Postgres Ranges?
On Fri, Aug 2, 2019 at 1:49 PM Ibrar Ahmed wrote: > I did some clean-up on this patch. I have also refactored a small portion of > the code > to reduce the footprint of the patch. For simplicity, I have divided the > patch into 6 > patches, now it is easy to review and debug. > Please follow the PostgreSQL coding guidelines. I have found places where you > missed that, secondly code even in WIP stage must not have WARNING because it > looks ugly. Thank you for the cleanup Ibrar! I'll try to stick to the coding standards more closely going forward. If you have any review comments I would certainly appreciate them, especially about the overall approach. I know that the implementation in its current form is not very tasteful, but I wanted to get some feedback on the ideas. Also just to reiterate: this patch depends on my other CF entry (range_agg), whose scope has expanded considerably. Right now I'm focusing on that. And if you're trying to make this code work, it's important to apply the range_agg patch first, since the temporal foreign key implementation calls that function. Also: since this patch raises the question of how to conform to SQL:2011 while still supporting Postgres range types, I wrote an article that surveys SQL:2011 temporal features in MariaDB, DB2, Oracle, and MS SQL Server: https://illuminatedcomputing.com/posts/2019/08/sql2011-survey/ A few highlights are: - Everyone lets you define PERIODs, but what you can do with them is still *very* limited. - No one treats PERIODs as first-class types or expressions; they are more like table metadata. - Oracle PERIODs do permit NULL start/end values, and it interprets them as "unbounded". That goes against the standard but since it's what Postgres does with ranges, it suggests to me that maybe we should follow their lead. Anyway I think a NULL is nicer than a sentinel for this purpose. Regards, Paul
Re: SQL:2011 PERIODS vs Postgres Ranges?
The patch does not work. postgres=# CREATE TABLE foo (id int,r int4range, valid_at tsrange, CONSTRAINT bar_pk PRIMARY KEY (r, valid_at WITHOUT OVERLAPS)); CREATE TABLE postgres=# CREATE TABLE bar (id int,r int4range, valid_at tsrange, CONSTRAINT bar_fk FOREIGN KEY (r, PERIOD valid_at) REFERENCES foo); ERROR: cache lookup failed for type 0 The new status of this patch is: Waiting on Author
Re: SQL:2011 PERIODS vs Postgres Ranges?
On Wed, Jul 31, 2019 at 1:01 AM Ibrar Ahmed wrote: > I have rebased the patch to master (1e2fddfa33d3c7cc93ca3ee0f32852699bd3e012) > and fixed some compilation warning. Now I am reviewing the actual code. Thanks for doing that Ibrar. I think the right status for this CF entry is now: Needs review. I have set it that way, in the September CF. By the way, there are some test failures: https://ci.appveyor.com/project/postgresql-cfbot/postgresql/build/1.0.50280 -- Thomas Munro https://enterprisedb.com
Re: SQL:2011 PERIODS vs Postgres Ranges?
Hi Paul, I have rebased the patch to master (1e2fddfa33d3c7cc93ca3ee0f32852699bd3e012) and fixed some compilation warning. Now I am reviewing the actual code. On Fri, Jul 26, 2019 at 6:35 PM Ibrar Ahmed wrote: > The patch requires to rebase on the master branch. > > The new status of this patch is: Waiting on Author > -- Ibrar Ahmed temporal_pks_fks_v005.patch Description: Binary data
Re: SQL:2011 PERIODS vs Postgres Ranges?
The patch requires to rebase on the master branch. The new status of this patch is: Waiting on Author
Re: Periods
On Thu, Jul 4, 2019 at 11:44 AM Alvaro Herrera wrote: > I think that the functionality in your patch is already integrated in > Paul's patch for temporal PK/FK elsewhere ... is that correct, or is > this patch orthogonal to that work? Hi Vik, I wasn't aware that you had moved your work over to an extension. It looks like you've made a lot of progress! I'd be eager to work with you on getting this into core. Alvaro: it's a mix of orthogonal and not orthogonal. :-) My work lets you use range types directly in SQL:2011 constructs, whereas Vik's work lets you use SQL:2011 PERIODs. I would like to support *both*, so I've been intending to add Vik's original patch letting you define PERIODs to my own work. There is no conflict in supporting both ranges and PERIODs because you can't name a PERIOD the same as an existing column. Behind the scenes the easiest way to implement PERIODs is with range types, so there would be very little duplication to permit both. PERIODs suffer from being outside relational theory as a quasi-attribute. You especially see this in composing queries & result sets. For example with ranges you could say this: WITH x AS ( SELECT * FROM y FOR PORTION OF some_range FROM t1 TO t2 ) SELECT * FROM x FOR PORTION OF some_range FROM t3 TO t4 ; But with PERIODs you can't because a PERIOD is not included in `SELECT *`. Also it's unclear how "attached" it is to a table definition. Can you say `SELECT *, some_period FROM x`? If so can you then use `FOR PORTION OF` on that result set? Can you construct an on-the-fly PERIOD expression? Can you pass a period to a function as a parameter? Can a function return a period? Can you ORDER BY a period? GROUP BY one? Can you cast to/from a period? Can you ask a period for its high/low values? Do we treat a PERIOD as a whole new datatype? Can you define a real column of type PERIOD? I haven't found text from the standard that answers most of these questions. (The standard does say you can construct a `PERIOD(t1, t2)` expression but apparently only inside a "period predicate".) Also you can't define PERIODs on types other than date/timestamp/timestamptz, unlike ranges. Also PERIODs require a sentinel value to mean "unbounded" (e.g. 31-JAN-) whereas ranges let you express that with a NULL. (Postgres does have Infinity and -Infinity for timestamp types but I've noticed that client programming languages can't always express ranges with those values.) Personally I intend to use ranges any time I build temporal tables, but supporting PERIODs might have value for people more interested in database portability or someone migrating from elsewhere to Postgres. I had some conversations at PGCon that I felt validated the permit-PERIODS-or-ranges approach, so I'm about ready to expand my patch to handle PERIODs too. For that I would love to draw on Vik's work so far. I think his original patch against core is more likely to be helpful than the extension, but I'll certainly consult both, and Vik if you have any advice let me know! :-) A big difference between a temporal extension vs temporal features in core is implementing DML. An extension pretty much requires you to use INSTEAD OF triggers. Also Vik's README points out that implementing temporal DELETE is hard that way. In core I believe you'd do temporal DML in the executor node. (That's my working theory anyway; I'm still new to that part of the code.) The first thing on my TODO list is to write a blog post comparing how other RDMBSes handle PERIODs and other temporal features. Besides the questions above, how does a trigger work on a table? For example when you DELETE in the middle of a range/period, and it becomes an INSERT plus an UPDATE, I *believe* you still fire the DELETE trigger. And you need to set the NEW/OLD tuples appropriately. You *don't* fire any INSERT & UPDATE triggers. The standard isn't super explicit but that's my take on it, and I want to write down what other vendors are doing. Yours, Paul
Re: Periods
Hello Vik, Paul, On 2019-Jul-04, Vik Fearing wrote: > I've been working on this as an extension instead. It allows me to do some > stuff in plpgsql which is much easier for me. > > https://github.com/xocolatl/periods/ Hmm, okay. > I would love to have all this in core (especially since MariaDB 10.4 > just became the first open source database to get all of this > functionality), but something is better than nothing. Agreed on all accounts. I think that the functionality in your patch is already integrated in Paul's patch for temporal PK/FK elsewhere ... is that correct, or is this patch orthogonal to that work? -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Periods
On Thursday, July 4, 2019 8:04 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: > Hello Vik, > On 2018-Jun-05, Vik Fearing wrote: > >>> I understand that your patch is just to allow defining periods, but I >>> thought I'd share my own hopes earlier rather than later, in case you >>> are doing more work on this. >> >> Yes, I plan on doing much more work on this. My goal is to implement >> (by myself or with help from other) the entire SQL:2016 spec on >> periods and system versioned tables. This current patch is just >> infrastructure. > > Have you had the chance to work on this? Hi Alvaro, I've been working on this as an extension instead. It allows me to do some stuff in plpgsql which is much easier for me. https://github.com/xocolatl/periods/ I would love to have all this in core (especially since MariaDB 10.4 just became the first open source database to get all of this functionality), but something is better than nothing. -- Vik Fearing
Re: Periods
Hello Vik, On 2018-Jun-05, Vik Fearing wrote: > > I understand that your patch is just to allow defining periods, but I > > thought I'd share my own hopes earlier rather than later, in case you > > are doing more work on this. > > Yes, I plan on doing much more work on this. My goal is to implement > (by myself or with help from other) the entire SQL:2016 spec on > periods and system versioned tables. This current patch is just > infrastructure. Have you had the chance to work on this? -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: SQL:2011 PERIODS vs Postgres Ranges?
Hi Pail, On 3/10/19 2:41 AM, Paul A Jungwirth wrote: On Tue, Mar 5, 2019 at 12:35 AM David Steele wrote: I have marked this patch as targeting PG13 since it is clearly not material for PG12. I also added you as the patch author. Thanks David! Targeting PG13 was my intention, so sorry if I messed up the commitfest entry. No worries, that's what I'm here for! Here is a new patch rebased on top of master. My questions are inline as TODO comments for whoever does the review. I'm pretty far along with an add-on patch to create temporal *foreign* keys too, which I think should be part of this same bundle of work. If anyone happens to review the PK patch soon, it might help me avoid the same mistakes in the FK work, but if not that's fine too. :-) Don't worry if you don't attract review in this CF since most people are focused on PG12 items. Even so, getting your patch in early helps because it will have history by the time the final CFs for PG13 come around. Regards, -- -David da...@pgmasters.net
Re: Re: SQL:2011 PERIODS vs Postgres Ranges?
On Tue, Mar 5, 2019 at 12:35 AM David Steele wrote: > I have marked this patch as targeting PG13 since it is clearly not > material for PG12. I also added you as the patch author. Thanks David! Targeting PG13 was my intention, so sorry if I messed up the commitfest entry. Here is a new patch rebased on top of master. My questions are inline as TODO comments for whoever does the review. I'm pretty far along with an add-on patch to create temporal *foreign* keys too, which I think should be part of this same bundle of work. If anyone happens to review the PK patch soon, it might help me avoid the same mistakes in the FK work, but if not that's fine too. :-) Yours, Paul temporal_pks_v0003.patch Description: Binary data
Re: Re: SQL:2011 PERIODS vs Postgres Ranges?
Hi Paul, On 11/24/18 4:55 AM, Paul A Jungwirth wrote: On Fri, Nov 23, 2018 at 3:41 PM Paul A Jungwirth wrote: Here is a patch for my progress on this so far. Well this is embarrassing, but my last patch used the mistaken syntax `PRIMARY KEY (cols, WITHOUT OVERLAPS col)`. Here is a new patch which uses the correct syntax `PRIMARY KEY (cols, col WITHOUT OVERLAPS)`. Sorry about that! Also I went ahead and rebased it off current master. I have marked this patch as targeting PG13 since it is clearly not material for PG12. I also added you as the patch author. Regards, -- -David da...@pgmasters.net
Re: SQL:2011 PERIODS vs Postgres Ranges?
On Fri, Nov 23, 2018 at 3:41 PM Paul A Jungwirth wrote: > Here is a patch for my progress on this so far. Well this is embarrassing, but my last patch used the mistaken syntax `PRIMARY KEY (cols, WITHOUT OVERLAPS col)`. Here is a new patch which uses the correct syntax `PRIMARY KEY (cols, col WITHOUT OVERLAPS)`. Sorry about that! Also I went ahead and rebased it off current master. Yours, Paul temporal_pks_v0002.patch Description: Binary data
Re: SQL:2011 PERIODS vs Postgres Ranges?
Here is a patch for my progress on this so far. I'd love some comments
on the general approach, as I've never contributed anything this
involved before. It's not ready for a commitfest, but it would help me
to have some feedback. There are TODO comments with my major
questions.
This patch lets you say `CONSTRAINT foo PRIMARY KEY (cols, WITHOUT
OVERLAPS some_range_col)`, both in `CREATE TABLE` and `ALTER TABLE`.
It doesn't support foreign keys yet, and it only supports range
columns, not PERIODs. (I'm starting to realize that adding PERIODs
will be a lot of work, although I'm still up for it. :-) The approach
isn't exactly the #2+#3 approach I suggested previously, since
user-exposed functions seem like an odd fit with how things normally
flow out of the grammar, but it follows the goal of permitting either
ranges or PERIODs for temporal keys without breaking the SQL:2011
standard.
It adds regression and pg_dump tests, although no documentation yet. A
few of my new regress tests fail, but only the ones for PERIODs. I
don't know if I need to do anything for pg_dump's custom format. For
the SQL format it exports correct `ALTER TABLE ... ADD CONSTRAINT ...
(... WITHOUT OVERLAPS ...)` statements. Also I left a question in
bin/psql/describe.c about how to make \d show a PK WITHOUT OVERLAPS.
It is based on 3be97b97ed37b966173f027091f21d8a7605e2a5 from Nov 14,
but I can rebase it if you like.
If it's easier to read this in smaller bits, you can find my (somewhat
messy) commit history here:
https://github.com/pjungwir/postgresql/commits/temporal-pks
For a next step (assuming what I've done already isn't too bad): I
could either work on PERIODs (building on Vik Fearing's patch from a
few months ago), or add range-based temporal foreign keys. Any
suggestions?
Thanks!
Paul
On Sun, Oct 28, 2018 at 2:29 PM Paul Jungwirth
wrote:
>
> Hi Jeff,
>
> Thanks for sharing your thoughts and encouragement! :-)
>
> > The model in [7] is
> > based heavily on pack/unpack operators, and it's hard for me to see
> > how those fit into SQL. Also, the pack/unpack operators have some
> > theoretical weirdness that the book does not make clear*.
> >
> > *: My question was about the significance
> > of the order when packing on two intervals. Hugh Darwen was kind
> > enough to reply at length, and offered a lot of insight, but was still
> > somewhat inconclusive.
>
> I'd be interested in seeing that conversation if you ever find it again.
>
> I really like how Date/Darwen/Lorentzos use pack/unpack to explain
> temporal operations as operating on every concurrent "instant"
> separately, and then bringing the adjacent instants back together into
> ranges again. Even if you don't materialize that approach, conceptually
> it makes it easy to understand what's going on.
>
> So what is great about the patch from Anton Dignös
> (https://www.postgresql-archive.org/PROPOSAL-Temporal-query-processing-with-range-types-tt5913058.html)
> is that (like Date/Darwen/Lorentzos) you still have temporal variants
> for every operator in the relational algebra, but they give
> straightforward & efficient implementations of each based on traditional
> operators plus just their two new "normalize" and "align" operations. (I
> think they renamed these in later papers/patches though?) Their main
> paper is at https://files.ifi.uzh.ch/boehlen/Papers/modf174-dignoes.pdf
> if anyone wants to read it. It's short! :-)
>
> The biggest challenge implementing temporal operators in plain SQL is
> merging/splitting ranges from the left & right sides of an operator so
> they line up. A single row can get split into multiple rows, or several
> rows might be merged into one, etc. You can see how tricky Snodgrass's
> "coalesce" operation is in his book. I gave some example SQL to
> implement coalesce with UNNEST plus a range_agg function at
> https://github.com/pjungwir/range_agg but with the Dignös approach I
> don't think you'd need that. Normalize/align targets roughly the same
> problem.
>
> Anyway I'd be curious whether the theoretical weirdness you found in
> pack/unpack also applies to normalize/align.
>
> Yours,
>
> --
> Paul ~{:-)
> p...@illuminatedcomputing.com
temporal_pks_v0001.patch
Description: Binary data
Re: SQL:2011 PERIODS vs Postgres Ranges?
Hi Jeff,
Thanks for sharing your thoughts and encouragement! :-)
> The model in [7] is
> based heavily on pack/unpack operators, and it's hard for me to see
> how those fit into SQL. Also, the pack/unpack operators have some
> theoretical weirdness that the book does not make clear*.
>
> *: My question was about the significance
> of the order when packing on two intervals. Hugh Darwen was kind
> enough to reply at length, and offered a lot of insight, but was still
> somewhat inconclusive.
I'd be interested in seeing that conversation if you ever find it again.
I really like how Date/Darwen/Lorentzos use pack/unpack to explain
temporal operations as operating on every concurrent "instant"
separately, and then bringing the adjacent instants back together into
ranges again. Even if you don't materialize that approach, conceptually
it makes it easy to understand what's going on.
So what is great about the patch from Anton Dignös
(https://www.postgresql-archive.org/PROPOSAL-Temporal-query-processing-with-range-types-tt5913058.html)
is that (like Date/Darwen/Lorentzos) you still have temporal variants
for every operator in the relational algebra, but they give
straightforward & efficient implementations of each based on traditional
operators plus just their two new "normalize" and "align" operations. (I
think they renamed these in later papers/patches though?) Their main
paper is at https://files.ifi.uzh.ch/boehlen/Papers/modf174-dignoes.pdf
if anyone wants to read it. It's short! :-)
The biggest challenge implementing temporal operators in plain SQL is
merging/splitting ranges from the left & right sides of an operator so
they line up. A single row can get split into multiple rows, or several
rows might be merged into one, etc. You can see how tricky Snodgrass's
"coalesce" operation is in his book. I gave some example SQL to
implement coalesce with UNNEST plus a range_agg function at
https://github.com/pjungwir/range_agg but with the Dignös approach I
don't think you'd need that. Normalize/align targets roughly the same
problem.
Anyway I'd be curious whether the theoretical weirdness you found in
pack/unpack also applies to normalize/align.
Yours,
--
Paul ~{:-)
p...@illuminatedcomputing.com
Re: SQL:2011 PERIODS vs Postgres Ranges?
On Sun, 2018-10-21 at 22:10 +0300, Heikki Linnakangas wrote: > On 21/10/2018 21:17, Paul A Jungwirth wrote: > > 3. Build our own abstractions on top of ranges, and then use those > > to > > implement PERIOD-based features. This is the least clear option, > > and I > > imagine it would require a lot more design effort. Our range types > > are > > already a step in this direction. Does anyone think this approach > > has > > promise? If so I can start thinking about how we'd do it. I imagine > > we > > could use a lot of the ideas in [7]. > > ... > > [7] C. J. Date, Hugh Darwen, Nikos Lorentzos. Time and Relational > > Theory, Second Edition: Temporal Databases in the Relational Model > > and > > SQL. 2nd edition, 2014. > > +1 on this approach. I think [7] got the model right. If we can > implement SQL-standard PERIODs on top of it, then that's a bonus, > but > having sane, flexible, coherent set of range operators is more > important > to me. +1 for approach #3 from me as well. It was my original intention for range types, though my first priority was utility and not the standard. I think we are likely to run into a few areas where they aren't a perfect fit to the standard, but I think it's a promising approach and we can probably work around those issues by using special operators. > What are we missing? It's been years since I read that book, but > IIRC > temporal joins is one thing, at least. What features do you have in > mind? We do support temporal joins, just not as efficiently as I'd like, and the language doesn't make it quite as clear as it could be. I look at that book as a source of inspiration, but I don't think it's simple to map features one-to-one. For instance, the model in [7] is based heavily on pack/unpack operators, and it's hard for me to see how those fit into SQL. Also, the pack/unpack operators have some theoretical weirdness that the book does not make clear*. Regards, Jeff Davis *: I asked in a temporal discussion group (that was unfortunately a part of LinkedIn circa 2011 and I can't find any reference to the discussion outside my mailbox). My question was about the significance of the order when packing on two intervals. Hugh Darwen was kind enough to reply at length, and offered a lot of insight, but was still somewhat inconclusive.
Re: SQL:2011 PERIODS vs Postgres Ranges?
Hi ne 21. 10. 2018 v 21:47 odesílatel Paul A Jungwirth < p...@illuminatedcomputing.com> napsal: > On Sun, Oct 21, 2018 at 12:11 PM Heikki Linnakangas > wrote: > > On 21/10/2018 21:17, Paul A Jungwirth wrote: > > > 3. Build our own abstractions on top of ranges, and then use those to > > > implement PERIOD-based features. > > +1 on this approach. I think [7] got the model right. If we can > > implement SQL-standard PERIODs on top of it, then that's a bonus, but > > having sane, flexible, coherent set of range operators is more important > > to me. > > Okay, I'm surprised to hear from you and Isaac that following the > standard isn't as important as I thought, but I'm certainly pleased > not to make it the focus. I just thought that Postgres's reputation > was to be pretty careful about sticking to it. (I think we could still > add a standard-compliant layer, but like you I don't feel a duty to > suffer from it.) It sounds like I should work out some proposed > function signatures and write up how to use them, and see what people > think. Is that a useful approach? > > It can be very unhappy if we cannot to implement standard syntax and behave. The implementation behind or another is not too important. We should not to accept any design that don't allow implement standard. The world is 10 years after standards (maybe more). Now, this feature is implemented in MySQL/MariaDB, and I expecting a press to have standardized syntax after 5 years. Regards Pavel > > What are we missing? > > Here are a few big ones: > > 1. Define temporal primary keys and foreign keys that are known to the > database catalog and controlled as higher-level objects. For instance > I wrote an extension at https://github.com/pjungwir/time_for_keys to > create temporal foreign keys, but the database isn't "aware" of them. > That means they are more cluttered in `\d foo` than necessary (you see > the trigger constraints instead of something about a foreign key), > they don't automatically disappear if you drop the column, it is hard > to make them "polymorphic" (My extension supports only > int+tstzrange.), they don't validate that the referenced table has a > declared temporal PK, they probably have slightly different > locking/transaction semantics than the real RI code, etc. This is what > I'd like to implement right now. > > 2. System time: automatically track DML changes to the table, and let > you query "as of" a given time. > > 3. Temporal joins. I don't want to tackle this myself, because there > is already an amazing proposed patch that does everything we could ask > for at > https://www.postgresql-archive.org/PROPOSAL-Temporal-query-processing-with-range-types-tt5913058.html > (recently updated btw, so I hope someone will look at it!). > > 4. Temporal UPDATE/DELETE: these should be converted to instead change > the end time of old rows and insert new rows with the changed > attributes. I'm interested in implementing this too, but one thing at > a time. . . . > > I really appreciate your sharing your thoughts! > > Paul > >
Re: SQL:2011 PERIODS vs Postgres Ranges?
On Sun, Oct 21, 2018 at 12:11 PM Heikki Linnakangas wrote: > On 21/10/2018 21:17, Paul A Jungwirth wrote: > > 3. Build our own abstractions on top of ranges, and then use those to > > implement PERIOD-based features. > +1 on this approach. I think [7] got the model right. If we can > implement SQL-standard PERIODs on top of it, then that's a bonus, but > having sane, flexible, coherent set of range operators is more important > to me. Okay, I'm surprised to hear from you and Isaac that following the standard isn't as important as I thought, but I'm certainly pleased not to make it the focus. I just thought that Postgres's reputation was to be pretty careful about sticking to it. (I think we could still add a standard-compliant layer, but like you I don't feel a duty to suffer from it.) It sounds like I should work out some proposed function signatures and write up how to use them, and see what people think. Is that a useful approach? > What are we missing? Here are a few big ones: 1. Define temporal primary keys and foreign keys that are known to the database catalog and controlled as higher-level objects. For instance I wrote an extension at https://github.com/pjungwir/time_for_keys to create temporal foreign keys, but the database isn't "aware" of them. That means they are more cluttered in `\d foo` than necessary (you see the trigger constraints instead of something about a foreign key), they don't automatically disappear if you drop the column, it is hard to make them "polymorphic" (My extension supports only int+tstzrange.), they don't validate that the referenced table has a declared temporal PK, they probably have slightly different locking/transaction semantics than the real RI code, etc. This is what I'd like to implement right now. 2. System time: automatically track DML changes to the table, and let you query "as of" a given time. 3. Temporal joins. I don't want to tackle this myself, because there is already an amazing proposed patch that does everything we could ask for at https://www.postgresql-archive.org/PROPOSAL-Temporal-query-processing-with-range-types-tt5913058.html (recently updated btw, so I hope someone will look at it!). 4. Temporal UPDATE/DELETE: these should be converted to instead change the end time of old rows and insert new rows with the changed attributes. I'm interested in implementing this too, but one thing at a time. . . . I really appreciate your sharing your thoughts! Paul
Re: SQL:2011 PERIODS vs Postgres Ranges?
On 21/10/2018 21:17, Paul A Jungwirth wrote: 3. Build our own abstractions on top of ranges, and then use those to implement PERIOD-based features. This is the least clear option, and I imagine it would require a lot more design effort. Our range types are already a step in this direction. Does anyone think this approach has promise? If so I can start thinking about how we'd do it. I imagine we could use a lot of the ideas in [7]. ... [7] C. J. Date, Hugh Darwen, Nikos Lorentzos. Time and Relational Theory, Second Edition: Temporal Databases in the Relational Model and SQL. 2nd edition, 2014. +1 on this approach. I think [7] got the model right. If we can implement SQL-standard PERIODs on top of it, then that's a bonus, but having sane, flexible, coherent set of range operators is more important to me. What are we missing? It's been years since I read that book, but IIRC temporal joins is one thing, at least. What features do you have in mind? - Heikki
Re: SQL:2011 PERIODS vs Postgres Ranges?
On Sun, 21 Oct 2018 at 14:18, Paul A Jungwirth wrote: > Also, just how strictly do we have to follow the standard? Requiring > sentinels like '01 JAN 3000` just seems so silly. Could Postgres > permit nullable start/end PERIOD columns, and give them the same > meaning as ranges (unbounded)? Even if I forgot about ranges > altogether, I'd sure love to avoid these sentinels. > We have "infinity" and "-infinity" values in our date and timestamp types: https://www.postgresql.org/docs/current/static/datatype-datetime.html I think this avoids the silliness with sentinel values. For myself, I don't care about PERIOD etc. one bit. The "every new capability gets its own syntax" model that SQL follows is very old-fashioned, and for good reason. I'm happy with ranges and exclusion constraints. But if we can provide an implementation of PERIOD that makes it easier to port applications written for legacy database systems, it might be worthwhile.
SQL:2011 PERIODS vs Postgres Ranges?
Hello, I'm interested in contributing some temporal database functionality to Postgres, starting with temporal primary and foreign keys. I know some other folks nearby interested in helping out, too. But before we begin I'd like to ask the community about complying with the SQL:2011 standard [1] for these things. In SQL:2011, temporal features all build upon PERIODs, which are a new concept you can attach to tables. Each PERIOD is composed of a start column and an end column (both of some date/time type). You define PERIODs when you CREATE TABLE or ALTER TABLE. Then you refer to the periods when you create primary keys or foreign keys to make them temporal. There are also a handful of new operators for testing two ranges for overlap/succession/etc.[2] Most PERIODs are for tracking the history of a *thing* over time, but if the PERIOD is named SYSTEM_TIME it instead tracks the history of changes to *your database*.[3] (Google for "bitemporal" to read more about this.) Personally I think PERIODs are quite disappointing. They are not part of relational theory. They are not a column, but something else. If you say `SELECT * FROM t` you don't get `PERIODs` (as far as I can tell). But you can mention PERIODs approximately wherever you can mention columns [4], so now we have to support them when projecting, selecting, joining, aggregating, etc. (Or if we are permitted to not support them in some of those places, isn't that even worse?) You can see that PERIODs share a lot with Postgres's own range types. But ranges are a real column, requiring no special-case behavior, either for RDBMS implementers or SQL users. They have a richer set of operators.[5] They don't require any special declarations to put them in a table. They aren't limited to just date/time types. You can even define new range types yourself (e.g. I've found it helpful before to define inetrange and floatrange). Also the start/end columns of a PERIOD must be not nullable,[6] so that unbounded ranges must use sentinels like `01 JAN ` or `01 JAN 3000` instead. Also there is no way (as far as I can tell) to define and use a period within a subquery or CTE or view. Many of these criticisms of PERIODs you can find in [7], pages 403 - 410 (where "interval" means basically our own range types), plus others: for example PERIODs are always closed/open, you can only have a single application PERIOD per table, they are wordy, etc. I expect that any Postgres implementation of the standard would wind up using ranges internally. For example a temporal primary key would use an exclusion constraint based on a range expression, so if you had a PERIOD defined on columns named `valid_start` and `valid_end`, the PK would use something like `EXCLUDE USING gist (id WITH =, tstzrange(valid_start, valid_end) WITH &&)`. Also the new SQL:2011 operators would be easy to implement on top of our range operators. And then a temporal foreign key implementation would use either those or raw range operators. So is there any way for Postgres to offer the same temporal features, but give users the choice of using either PERIODs or ranges? If we built that, would the community be interested in it? I think there are several possible ways to go about it: 1. Permit defining PERIODs on either a start/end column pair, or an existing range column. Then everything else continues to use PERIODs. This seems tidy to implement, although since it acquiesces to the PERIOD-based approach for temporal functionality, it doesn't solve all the problems above. Also as [9] points out, it would lead to incompatibilities in the new `information_schema` views. E.g. `periods` is supposed to have `start_column_name` and `end_column_name` columns.[8] 2. Permit either ranges or PERIODs in the new syntax, e.g. `PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)` where `valid_at` is either a PERIOD or a range column. Similarly with foreign keys. There is probably some `information_schema` messiness here too, but perhaps less than with #1. This seems like a great alternative to application-time PERIODs, but I'm not sure how you'd tell Postgres to use a range column for the system-time dimension.[3] Perhaps just a function, and then the PERIOD of `SYSTEM_TIME` would call that function (with a range expression). 3. Build our own abstractions on top of ranges, and then use those to implement PERIOD-based features. This is the least clear option, and I imagine it would require a lot more design effort. Our range types are already a step in this direction. Does anyone think this approach has promise? If so I can start thinking about how we'd do it. I imagine we could use a lot of the ideas in [7]. 4. Just give up and follow the standard to the letter. I'm not enthusiastic about this, but I also really want temporal features, so I might still do the work if that's what folks preferred. Left to my own devices I would probably go with a mix of #2 & #3, where temporal functionality
Re: Periods
On June 5, 2018 9:47 PM, Paul A Jungwirth p...@illuminatedcomputing.com wrote: > On Sat, May 26, 2018 at 1:56 PM, Vik Fearing vik.fear...@protonmail.com wrote: > >> SQL:2011 introduced the concept of a "period". It takes two existing columns >> and basically does the same thing as our range types except there is no new >> storage. I believe if Jeff Davis had given us range types a few years later >> than he did, it would have been using periods. > > Hi Vik, I'm really glad to see someone working on temporal features! > I've only dabbled in Postgres hacking, but I've been following > temporal database research for several years, so I hope you won't mind > my comments. I already shared some thoughts on this other thread: > http://www.postgresql-archive.org/SQL-2011-Valid-Time-Support-td6020221.html Hi! No, of course I don't mind your comments; I welcome them. I had not seen that thread so I'll go take a look at it. > I would love to see Postgres support the standard but also let > people use range types. I'm not sure I agree that Jeff Davis would > have preferred the SQL:2011 period idea, which is an extra-relational > concept. Since it is attached to a table, it doesn't carry through > cleanly to a result set, so what happens if you want to apply temporal > features to a view, subquery, CTE, or set-returning function? As far as I can see, the standard doesn't say what should happen if you select a period, or even if that's possible. It does however define how to create a period not attached to a table (PERIOD ) so it would be possible to use that for views, subqueries, and the rest of your examples. > A range on the other hand is just a type, so as long as temporal operators > support that type, everything still composes nicely and just works. > The Date/Darwen/Lorenztos book has more to say about that, and I think > it's worth reading. They are unrealistically extreme in their purism, > but here I think they have some good points---points they also raised > against an earlier proposed temporal-SQL standard circa 1998. By the > way here are some thoughts Jeff shared with me about that book, which > he says inspired range types: > https://news.ycombinator.com/item?id=14738655 Thanks, I will read this, too. > I understand that your patch is just to allow defining periods, but I > thought I'd share my own hopes earlier rather than later, in case you > are doing more work on this. Yes, I plan on doing much more work on this. My goal is to implement (by myself or with help from other) the entire SQL:2016 spec on periods and system versioned tables. This current patch is just infrastructure. > Also, it might be nice if Postgres let > you also define periods from a single range column, so that people who > want to use intervals can still stick closer to the standard---I > dunno, just an idea. That's a nice idea, but I'm not sure how I'd fit it into the pg_period catalog which expects two columns. > Also, this may not be very helpful, but I started an extension to > support temporal foreign keys here: > https://github.com/pjungwir/time_for_keys > It uses intervals, not periods, but maybe you can steal some ideas. > :-) I have a half-finished branch porting it from plpgsql to C, so > that I could give them more catalog integration, and also I have hopes > of defining temporal primary keys, although that isn't implemented > yet. Anyway, I mention it because you said, "Application periods can > be used in PRIMARY/UNIQUE keys, foreign keys," but feel free to ignore > it. :-) While I'm waiting for comments on how best to do inheritance and other aspects of my patch, I'm working on getting PRIMARY/UNIQUE keys with periods. That's far from finished though as it is touching parts of the code that I have never looked at before. > In general, I would love Postgres to have some lower-level primitives > like range types and the Dingös operators, and then build the > SQL:2011 support on top of those. I'm happy to contribute work to help > make that happen, although I'd probably need to work with someone with > more Postgres hacking experience to get it done. Any help you can give me (or that I could give you) is greatly appreciated. I'm hoping we can get *something* in v12 with periods.
Re: Periods
On Tue, Jun 5, 2018 at 12:47 PM, Paul A Jungwirth wrote: > Also, this may not be very helpful, but I started an extension to > support temporal foreign keys here: > > https://github.com/pjungwir/time_for_keys > > It uses intervals, not periods, but maybe you can steal some ideas. Sorry for two emails but I wanted to add: the more stealable thing are the tests, which are pretty thorough and took a lot of hours to write. They are yours if you want them. :-) Paul
Re: Periods
2018-05-26 22:56 GMT+02:00 Vik Fearing <vik.fear...@protonmail.com>: > SQL:2011 introduced the concept of a "period". It takes two existing > columns and basically does the same thing as our range types except there > is no new storage. I believe if Jeff Davis had given us range types a few > years later than he did, it would have been using periods. > > Attached is a WIP patch that I have been working on. The only thing left > is completing periods on inherited tables, and finishing up pg_dump. I'm > posting this now just to make sure my basic foundation is sound, and to let > people know that I'm working on this. > > The patch itself doesn't have any functionality, it just allows periods to > be defined. With that, there are several things that we can do: > SYSTEM_TIME periods, which are explicitly not allowed by this patch, will > allow us to do SQL standard versioned tables, and also allows some time > travel functionality. Application periods can be used in PRIMARY/UNIQUE > keys, foreign keys, and give nice new features to UPDATE and DELETE. They > also allow "period predicates" which are the same kind of operations we > already have for range types. All of that is for future patches that build > on the infrastructure presented in this patch. > > The SQL standard restricts period columns to dates or timestamps, but I'm > allowing anything that has a btree operator class, as is the PostgreSQL > way. System periods, once allowed, will only be timestamptz though. > Unfortunately, I had to fully reserve the word PERIOD for this. > > I'm looking for comments on everything except the pg_dump stuff, keeping > in mind that inheritance is not finished either. > > Thanks! > looks interesting Regards Pavel > > > This is patch is based off of 71b349aef4. >
Periods
SQL:2011 introduced the concept of a "period". It takes two existing columns and basically does the same thing as our range types except there is no new storage. I believe if Jeff Davis had given us range types a few years later than he did, it would have been using periods. Attached is a WIP patch that I have been working on. The only thing left is completing periods on inherited tables, and finishing up pg_dump. I'm posting this now just to make sure my basic foundation is sound, and to let people know that I'm working on this. The patch itself doesn't have any functionality, it just allows periods to be defined. With that, there are several things that we can do: SYSTEM_TIME periods, which are explicitly not allowed by this patch, will allow us to do SQL standard versioned tables, and also allows some time travel functionality. Application periods can be used in PRIMARY/UNIQUE keys, foreign keys, and give nice new features to UPDATE and DELETE. They also allow "period predicates" which are the same kind of operations we already have for range types. All of that is for future patches that build on the infrastructure presented in this patch. The SQL standard restricts period columns to dates or timestamps, but I'm allowing anything that has a btree operator class, as is the PostgreSQL way. System periods, once allowed, will only be timestamptz though. Unfortunately, I had to fully reserve the word PERIOD for this. I'm looking for comments on everything except the pg_dump stuff, keeping in mind that inheritance is not finished either. Thanks! This is patch is based off of 71b349aef4.diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 3ed9021c2f..025d6b5355 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -225,6 +225,11 @@ information about partition key of tables + + pg_period + periods + + pg_pltemplate template data for procedural languages @@ -4902,6 +4907,116 @@ SCRAM-SHA-256$iteration count: + + pg_period + + + pg_period + + + + The catalog pg_period stores + information about system and application time periods. + + + + Periods are described in . + + + + pg_period Columns + + + + + Name + Type + References + Description + + + + + + + pername + name + + Period name + + + + perrelid + oid + pg_class.oid + The OID of the pg_class entry for the table containing this period. + + + + perstart + int2 + pg_attribute.attnum + + The attribute number of the start column. + + + + + perend + int2 + pg_attribute.attnum + + The attribute number of the end column. + + + + + peropclass + oid + pg_opclass.oid + + This contains the OID of the operator class to use. + + + + + perconstraint + oid + pg_constraint.oid + + This contains the OID of the CHECK constraint owned by the period to + ensure that (startcolumn + + endcolumn). + + + + + perislocal + bool + + + This period is defined locally for the relation. Note that a period can + be locally defined and inherited simultaneously. + + + + + perinhcount + int4 + + + The number of direct inheritance ancestors this period has. A period + with a nonzero number of ancestors cannot be dropped. + + + + + + + + + pg_pltemplate diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 2cd0b8ab9d..cdbe06196c 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -919,6 +919,64 @@ CREATE TABLE circles ( + + Periods + + + Periods are definitions on a table that associate a period name with a start + column and an end column. Both columns must be of exactly the same type + (including collation), have a btree operator class, and the start column + value must be strictly less than the end column value. + + + + There are two types of periods: application and system. System periods are + distinguished by their name which must be SYSTEM_TIME. Any + other name is an application period. + + + + Currently, periods in PostgreSQL have no functionality; they can only be + defined for future use. + + + + Application Periods + + +period +application + + + + Application periods are defined on a table using the following syntax: + + + +CREATE TABLE billing_addresses ( + customer_id integer, + address_id integer, + valid_from date, + valid_to date, + PERIOD FOR validity (valid_from, valid_to) +); + + + + + System Periods + + +period +system + + + +Periods for SYSTEM_TIME are curre
