On 23/10/2019 17:56, Surafel Temesgen wrote: > > Hi all , > > Temporal table is one of the main new features added in sql standard > 2011. From that I will like to implement system versioned temporal > table which allows to keep past and present data so old data can be > queried. >
Excellent! I've been wanting this feature for a long time now. We're the last major database to not have it. I tried my hand at doing it in core, but ended up having better success at an extension: https://github.com/xocolatl/periods/ > Am propose to implement it like below > > CREATE > > In create table only one table is create and both historical and > current data will be store in it. In order to make history and current > data co-exist row end time column will be added implicitly to primary > key. Regarding performance one can partition the table by row end time > column order to make history data didn't slowed performance. > If we're going to be implicitly adding stuff to the PK, we also need to add that stuff to the other unique constraints, no? And I think it would be better to add both the start and the end column to these keys. Most of the temporal queries will be accessing both. > INSERT > > In insert row start time column and row end time column behave like a > kind of generated stored column except they store current transaction > time and highest value supported by the data type which is +infinity > respectively. > You're forcing these columns to be timestamp without time zone. If you're going to force a datatype here, it should absolutely be timestamp with time zone. However, I would like to see it handle both kinds of timestamps as well as a simple date. > DELETE and UPDATE > > The old data is inserted with row end time column seated to current > transaction time > I don't see any error handling for transaction anomalies. In READ COMMITTED, you can easily end up with a case where the end time comes before the start time. I don't even see anything constraining start time to be strictly inferior to the end time. Such a constraint will be necessary for application-time periods (which your patch doesn't address at all but that's okay). > SELECT > > If the query didn’t contain a filter condition that include system > time column, a filter condition will be added in early optimization > that filter history data. > > Attached is WIP patch that implemented just the above and done on top > of commit b8e19b932a99a7eb5a. Temporal clause didn’t implemented yet > so one can use regular filter condition for the time being > > NOTE: I implement sql standard syntax except it is PERIOD FOR SYSTEM > TIME rather than PERIOD FOR SYSTEM_TIME in CREATE TABLE statement and > system time is not selected unless explicitly asked > Why aren't you following the standard syntax here? > Any enlightenment? > There are quite a lot of typos and other things that aren't written "the Postgres way". But before I comment on any of that, I'd like to see the features be implemented correctly according to the SQL standard.
