SQL:2011 PERIODS vs Postgres Ranges?

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL:2011 PERIODS vs Postgres Ranges?

Paul A Jungwirth
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 0000` 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 is exposed by a layer of public functions
that use ranges (maybe accepting PERIODs too), and then implement the
PERIOD-based syntax by calling those functions. Using functions for
the range-based layer isn't as "strong" an abstraction as designing
SQL syntax, so it should be less effort, and also reduce risk of
future conflicts. I'd still personally really appreciate *also* doing
some #2 though, so that I could access those features via SQL syntax
(not functions), but with ranges instead of PERIODs. What do the rest
of you think?

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.

Finally: I know Vik Fearing already made a start at defining
PERIODs.[9] I don't know if he's gone any further, but perhaps he can
chime in if so. I'd be happy to build on what he's done already.

I'm eager to start work on this, but I also want to get some community
buy-in before I go too far. Temporal is such a massive set of
concepts, I believe it's important to have some discussion before just
jumping in. (Btw I've written an annotated bibliography about temporal
databases at [10] if anyone wants to read more.) If I can do anything
to facilitate a fuller plan, let me know. I can write up a more
detailed proposal, etc. Thanks for your feedback!

Yours,
Paul

[1] I'm using the draft docs at
https://www.wiscorp.com/SQLStandards.html at the link titled "SQL:20nn
Working Draft Documents". Several of the PDFs in that zip file mention
the new temporal features, but by far the most important is Part 2
(7IWD2-02-Foundation-2011-12.pdf). If you search for "period" you
should find lots of results.

[2] 4.14.2: The operators are overlaps, equals, contains, precedes,
succeeds, immediately precedes, and immediate succeeds.

[3] See 4.14.1 for more about the special PERIOD named SYSTEM_TIME.
Whereas application-time PERIODs store a history of a *thing*, the
SYSTEM_TIME PERIOD stores a history of changes to the *database*
itself.

[4] In Part 12 the `<identifier chain>` of SQL syntax is amended to
include `PERIODs` as well as columns.

[5] https://www.postgresql.org/docs/current/static/functions-range.html

[6] 4.6.5.3: "The columns shall both be of a datetime data type and
known not nullable."

[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.

[8] 5.38 (7IWD2-11-Schemata-2011-12.pdf).

[9] https://www.postgresql-archive.org/Periods-td6022563.html

[10] https://illuminatedcomputing.com/posts/2017/12/temporal-databases-bibliography/

Reply | Threaded
Open this post in threaded view
|

Re: SQL:2011 PERIODS vs Postgres Ranges?

Isaac Morland
On Sun, 21 Oct 2018 at 14:18, Paul A Jungwirth <[hidden email]> 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:


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.
Reply | Threaded
Open this post in threaded view
|

Re: SQL:2011 PERIODS vs Postgres Ranges?

Heikki Linnakangas
In reply to this post by Paul A Jungwirth
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

Reply | Threaded
Open this post in threaded view
|

Re: SQL:2011 PERIODS vs Postgres Ranges?

Paul A Jungwirth
On Sun, Oct 21, 2018 at 12:11 PM Heikki Linnakangas <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: SQL:2011 PERIODS vs Postgres Ranges?

Pavel Stehule
Hi

ne 21. 10. 2018 v 21:47 odesílatel Paul A Jungwirth <[hidden email]> napsal:
On Sun, Oct 21, 2018 at 12:11 PM Heikki Linnakangas <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: SQL:2011 PERIODS vs Postgres Ranges?

Jeff Davis-8
In reply to this post by Heikki Linnakangas
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.


Reply | Threaded
Open this post in threaded view
|

Re: SQL:2011 PERIODS vs Postgres Ranges?

Paul A Jungwirth
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              ~{:-)
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: SQL:2011 PERIODS vs Postgres Ranges?

Paul A Jungwirth
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
<[hidden email]> 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              ~{:-)
> [hidden email]

temporal_pks_v0001.patch (39K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: SQL:2011 PERIODS vs Postgres Ranges?

Paul A Jungwirth
On Fri, Nov 23, 2018 at 3:41 PM Paul A Jungwirth
<[hidden email]> 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 (37K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Re: SQL:2011 PERIODS vs Postgres Ranges?

David Steele
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
> <[hidden email]> 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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Re: SQL:2011 PERIODS vs Postgres Ranges?

Paul A Jungwirth
On Tue, Mar 5, 2019 at 12:35 AM David Steele <[hidden email]> 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 (39K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: SQL:2011 PERIODS vs Postgres Ranges?

David Steele
Hi Pail,

On 3/10/19 2:41 AM, Paul A Jungwirth wrote:
> On Tue, Mar 5, 2019 at 12:35 AM David Steele <[hidden email]> 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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: SQL:2011 PERIODS vs Postgres Ranges?

Paul A Jungwirth
On Sat, Mar 9, 2019 at 10:42 PM David Steele <[hidden email]> wrote:
> On 3/10/19 2:41 AM, Paul A Jungwirth wrote:
> > 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.

Here is that patch. I've changed the title from "temporal_pks" to
"temporal_fks" but it includes both.

I've rebased on top of latest master, but also the patch assumes my
other range_agg patch is already included. (I use range_agg to help
implement the foreign key checks.)

This patch is hopefully getting close, but I'd still call it a WIP. It
has docs and tests, but it hasn't had any review yet (and neither has
range_agg). Here are a few limitations:

- It supports only range types, not SQL:2011 PERIODs. I'd like to add
PERIODs too, but that seems like a major undertaking. I would probably
need to partner with someone more experienced to get it done. Maybe it
should be a separate CF entry.
- The foreign keys support only NO ACTION and RESTRICT, not
CASCADE/SET NULL/SET DEFAULT. The latter options are hard to implement
now but would be trivial after adding UPDATE/DELETE FROM t FOR PORTION
OF r FROM t1 TO t2, so I'm thinking that will be next on my list. :-)
- I'm sure there are plenty of errors & infelicities people more
knowledgeable than me can point out.

Any feedback is gratefully welcomed. :-)

Yours,
Paul

temporal_fks_v0001.patch (159K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: SQL:2011 PERIODS vs Postgres Ranges?

Paul A Jungwirth
> Here is that patch. I've changed the title from "temporal_pks" to
> "temporal_fks" but it includes both.

Here are rebased patches to add temporal PKs and FKs. Note they depend
on my other commitfest entry adding a range_agg function. The PKs
patch should be applied first, then the FKs patch. These are mostly
the same as before, but I've added a small optimization to the FK
patch. The traditional FK code skips the UPDATE triggers if the PK/FK
columns didn't change. For temporal RI, we can broaden the condition:
on a PK update, if the new PK range is a superset of the old (and the
other parts of the key are equal), we can skip the check. On an FK
update, if the new FK range is a subset of the old (and the other
parts of the key are equal), we can skip the check.

These are still very WIP patches. They include a bunch of TODO
comments where I'm hoping to get feedback. Also their "taste" is
surely questionable. In many cases I took the least-obtrusive path
even where that might not be the best one. If anyone wants to offer
some corrections, I would gratefully pay attention. :-)

My plan is to work on UPDATE/DELETE FOR PORTION OF next, then use that
to add CASCADE functionality to temporal FKs. I've started reading how
executor nodes work, but I'll probably reach out with some questions.
. . . :-) Also I need to decide how triggers should behave in a
temporal update/delete. I have my own opinion about what is sensible,
but I haven't found guidance in the standard, so I'm going to see what
other RDBMSes are doing. (If someone has a reference to a part of the
standard I overlooked, let me know! :-)

Yours,
Paul

temporal_fks_v0002.patch (135K) Download Attachment
temporal_pks_v0004.patch (39K) Download Attachment