Re: SQL:2011 application time
| От | Corey Huinker |
|---|---|
| Тема | Re: SQL:2011 application time |
| Дата | |
| Msg-id | CADkLM=ekdXZceMyggb6Lqj2447VfiS-Bw22Gdgd5tBotfKmi6A@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: SQL:2011 application time (Paul A Jungwirth <pj@illuminatedcomputing.com>) |
| Ответы |
Re: SQL:2011 application time
(Paul A Jungwirth <pj@illuminatedcomputing.com>)
|
| Список | pgsql-hackers |
In IBM DB2 you can only have one because application-time periods must
be named "business_time" (not joking).
I saw that as well, and it made me think that someone at IBM is a fan of Flight Of The Conchords.
Personally I feel like it's a weird limitation and I wouldn't mind
supporting more, but my current implementation only allows for one,
and I'd have to rethink some things to do it differently.
I'm satisfied that it's not something we need to do in the first MVP.
Yes. Even though the name "SYSTEM_TIME" is technically enough, I'd
still include a pertype column to make distinguishing system vs
application periods easier and more obvious.
SYSTEM_TIME seems to allow for DATE values in the start_time and end_time fields, though I cannot imagine how that would ever be practical, unless it were somehow desirable to reject subsequent updates within a 24 hour timeframe. I have seen instances where home-rolled application periods used date values, which had similar problems where certain intermediate updates would simply have to be discarded in favor of the one that was still standing at midnight.
> 2. The system versioning effort has chosen 'infinity' as their end-time value, whereas you have chosen NULL as that makes sense for an unbounded range. Other databases seem to leverage '9999-12-31 23:59:59' (SQLServer, IIRC) whereas some others seem to used '2999-12-31 23:59:59' but those might have been home-rolled temporal implementations. To further add to the confusion, the syntax seems to specify the keyword of MAXVALUE, which further muddies things. The system versioning people went with 'infinity' seemingly because it prescribe and end to the world like SQLServer did, but also because it allowed for a primary key based on (id, endtime) and that's just not possible with NULL endtime values.
I think it's a little weird that our system-time patch mutates your
primary key. None of the other RDMBSes do that. I don't think it's
incompatible (as long as the system time patch knows how to preserve
the extra period/range data in an application-time temporal key), but
it feels messy to me.
Per outline below, I'm proposing an alternate SYSTEM_TIME implementation that would leave the PK as-is.
I would prefer if system-time and application-time used the same value
to mean "unbounded". Using null means we can support any type (not
just types with +-Infinity). And it pairs nicely with range types. If
the only reason for system-time to use Infinity is the primary key, I
think it would be better not to mutate the primary key (and store the
historical records in a separate table as other RDMSes do).
The two "big wins" of infinity seemed (to me) to be:
1. the ability to add "AND end_time = 'infinity'" as a cheap way to get current rows
2. clauses like "WHERE CURRENT_DATE - 3 BETWEEN start_time AND end_time" would work. Granted, there's very specific new syntax to do that properly, but you know somebody's gonna see the columns and try to do it that way.
1. the ability to add "AND end_time = 'infinity'" as a cheap way to get current rows
2. clauses like "WHERE CURRENT_DATE - 3 BETWEEN start_time AND end_time" would work. Granted, there's very specific new syntax to do that properly, but you know somebody's gonna see the columns and try to do it that way.
Btw Oracle also uses NULL to mean "unbounded".
Huh, I missed that one. That is good in that it gives some precedence to how you've approached it.
We presently forbid PKs from including expressions, but my patch lifts
that exception so it can index a rangetype expression built from the
period start & end columns. So even if we must include the system-time
end column in a PK, perhaps it can use a COALESCE expression to store
Infinity even while using NULL to signify "currently true" from a user
perspective.
Either way seems viable, but I understand why you want to leverage ranges in this way.
> 3. I noticed some inconsistency in the results from various "SELECT * FROM portion_of_test" examples. In some, the "valid_at" range is shown but not columns that make it up, and in some others, the "valid_from" and "valid_to" columns are shown, with no mention of the period. From what I've seen, the period column should be invisible unless invoked, like ctid or xmin.
In most cases the tests test the same functionality with both PERIODs
and rangetype columns. For FKs they test all four combinations of
PERIOD-referencing-PERIOD, PERIOD-referencing-range,
range-referencing-PERIOD, and range-referencing-range. If valid_at is
a genuine column, it is included in SELECT *, but not if it is a
PERIOD.
Ok, I'll have to look back over the test coverage to make sure that I understand the behavior now.
> 4. The syntax '2018-03-04' AT TIME ZONE INTERVAL '2' HOUR TO MINUTE simply confounded me.
Me too! I have no idea what that is supposed to mean. But that
behavior predates my patch. I only had to deal with it because it
creates a shift-reduce conflict with `FOR PORTION OF valid_at FROM x
TO y`, where x & y are expressions. I asked about this syntax at my
PgCon 2020 talk, but I haven't ever received an answer. Perhaps
someone else knows what this kind of INTERVAL means (as a modifier of
a time value).
I think I'll open this as a separate thread, because it would simplify matters if we can reject this nonsense syntax.
This was the alternative method of system versioning I proposed recently in the system versioning thread
1. The regular table remains unchanged, but a pg_class attribute named "relissystemversioned" would be set to true
2. I'm unsure if the standard allows dropping a column from a table while it is system versioned, and the purpose behind system versioning makes me believe the answer is a strong "no" and requiring DROP COLUMN to fail on relissystemversioned = 't' seems pretty straightforward.3. The history table would be given a default name of $FOO_history (space permitting), but could be overridden with the history_table option.4. The history table would have relkind = 'h'5. The history table will only have rows that are not current, so it is created empty.6. As such, the table is effectively append-only, in a way that vacuum can actually leverage, and likewise the fill factor of such a table should never be less than 100.7. The history table could only be updated only via system defined triggers (insert,update,delete, alter to add columns), or row migration similar to that found in partitioning. It seems like this would work as the two tables working as partitions of the same table, but presently we can't have multi-parent partitions.8. The history table would be indexed the same as the base table, except that all unique indexes would be made non-unique, and an index of pk + start_time + end_time would be added9. The primary key of the base table would remain the existing pk vals, and would basically function normally, with triggers to carry forth changes to the history table. The net effect of this is that the end_time value of all rows in the main table would always be the chosen "current" value (infinity, null, 9999-12-31, etc) and as such might not actually _need_ to be stored.10. Queries that omit the FOR SYSTEM_TIME clause, as well as ones that use FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP, would simply use the base table directly with no quals to add.11. Queries that use FOR SYSTEM_TIME and not FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP, then the query would do a union of the base table and the history table with quals applied to both.12. It's a fair question whether the history table would be something that could be queried directly. I'm inclined to say no, because that allows for things like SELECT FOR UPDATE, which of course we'd have to reject.13. If a history table is directly referenceable, then SELECT permission can be granted or revoked as normal, but all insert/update/delete/truncate options would raise an error.14. DROP SYSTEM VERSIONING from a table would be quite straightforward - the history table would be dropped along with the triggers that reference it, setting relissystemversioned = 'f' on the base table.
The benefits to your effort here would be:
1. No change to the primary key except for the ones dictated by application period
2. The INFORMATION_SCHEMA view need merely take into account The pg_class.relkind = 'h' entries
3. system versioning is no longer mutating (trigger on X updates X), which eliminates the possibility that application period triggers get into a loop
4. DROP SYSTEM VERSIONING would be entirely transparent to application versioning.
Thoughts?
В списке pgsql-hackers по дате отправления: