xocolatl / periods Goto Github PK

View Code? Open in Web Editor NEW

238.0 18.0 17.0 315 KB

PERIODs and SYSTEM VERSIONING for PostgreSQL

License: PostgreSQL License

PLpgSQL 95.37% Makefile 0.11% C 4.52%

postgresql periods sql-standards system-versioning extension proof-of-concept

periods's People

Contributors

Stargazers

Watchers

Forkers

klando credativ mlt gridl runejuhl cybernetics ariciputi dineshks1 emrul franksprich eryx67 agnul97 gennadyshcherbakov jean-lopes gkliska tektite-io pmenke-de

periods's Issues

pg_dump -n periods cannot dump the tables ?

mydb=# select version();
                                                     version                                                      
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.2 (Debian 14.2-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)

I am trying to do a pg_restore for a pg_dump that includes the periods schema.
I thought the restore was successful, because I connected my app to the restored db and read ops ran fine until my app tried to insert to a table which had been add_system_time_period-ed, an error happened:
period "system_time" not found on table "carts"

So I digged around, and found out that my restored db has empty periods schema.
And I tried a normal pg_dump on the live server that is still running fine.

This is the pg_dump command and output:

pg_dump -n periods -d mydb
--
-- PostgreSQL database dump
--

-- Dumped from database version 14.1 (Debian 14.1-1.pgdg110+1)
-- Dumped by pg_dump version 14.1 (Debian 14.1-1.pgdg110+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- PostgreSQL database dump complete
--

Notice that the tables were not dumped.

And this is my cmd to list all tables for this periods schema:

psql -d mydb -c '\dt+ periods'
Schema  |        Name         | Type  |  Owner   | Persistence | Access method |    Size    |                                                   Description                   >
---------+---------------------+-------+----------+-------------+---------------+------------+--------------------------------------------------------------------------------->
 periods | for_portion_views   | table | postgres | permanent   | heap          | 0 bytes    | 
 periods | foreign_keys        | table | postgres | permanent   | heap          | 8192 bytes | A registry of foreign keys using periods WITHOUT OVERLAPS
 periods | periods             | table | postgres | permanent   | heap          | 64 kB      | The main catalog for periods.  All "DDL" operations for periods must first take >
 periods | system_time_periods | table | postgres | permanent   | heap          | 80 kB      | 
 periods | system_versioning   | table | postgres | permanent   | heap          | 88 kB      | A registry of tables with SYSTEM VERSIONING
 periods | unique_keys         | table | postgres | permanent   | heap          | 8192 bytes | A registry of UNIQUE/PRIMARY keys using periods WITHOUT OVERLAPS

I wonder what did I miss in order to do a successful pg_dump that also includes the periods schema ?

Why version 1.2 is not visible in my pg14 ?

This is out of curiosity, but after installing postgresql-14-periods in debian buster, the available versions do not include 1.2, only 1.0 and 1.1. Is this intentional ?

Below are the details of my system, package files, and extension infos.

Linux localdebian 4.19.0-18-amd64 #1 SMP Debian 4.19.208-1 (2021-09-29) x86_64 GNU/Linux
Debian 10 Buster

# dpkg -L postgresql-14-periods
/.
/usr
/usr/lib
/usr/lib/postgresql
/usr/lib/postgresql/14
/usr/lib/postgresql/14/lib
/usr/lib/postgresql/14/lib/bitcode
/usr/lib/postgresql/14/lib/bitcode/periods
/usr/lib/postgresql/14/lib/bitcode/periods/periods.bc
/usr/lib/postgresql/14/lib/bitcode/periods.index.bc
/usr/lib/postgresql/14/lib/periods.so
/usr/share
/usr/share/doc
/usr/share/doc/postgresql-14-periods
/usr/share/doc/postgresql-14-periods/README.md.gz
/usr/share/doc/postgresql-14-periods/changelog.Debian.amd64.gz
/usr/share/doc/postgresql-14-periods/changelog.Debian.gz
/usr/share/doc/postgresql-14-periods/changelog.gz
/usr/share/doc/postgresql-14-periods/copyright
/usr/share/doc/postgresql-doc-14
/usr/share/doc/postgresql-doc-14/extension
/usr/share/doc/postgresql-doc-14/extension/README.periods.gz
/usr/share/postgresql
/usr/share/postgresql/14
/usr/share/postgresql/14/extension
/usr/share/postgresql/14/extension/periods--1.0--1.1.sql
/usr/share/postgresql/14/extension/periods--1.0.sql
/usr/share/postgresql/14/extension/periods--1.1--1.2.sql
/usr/share/postgresql/14/extension/periods--1.1.sql
/usr/share/postgresql/14/extension/periods--1.2.sql
/usr/share/postgresql/14/extension/periods.control
/usr/share/doc/postgresql-14-periods/README.periods.gz

# select * from pg_extension where extname = 'periods';
   oid   | extname | extowner | extnamespace | extrelocatable | extversion |                     extconfig                     |    extcondition     
---------+---------+----------+--------------+----------------+------------+---------------------------------------------------+---------------------
 2406036 | periods |       10 |         2200 | f              | 1.1        | {2406063,2406069,2406089,2406101,2406114,2406137} | {"","","","","",""}
(1 row)

# alter extension periods update;
NOTICE:  version "1.1" of extension "periods" is already installed
ALTER EXTENSION

# SELECT * FROM pg_available_extension_versions WHERE name ='periods';
  name   | version | installed | superuser | relocatable | schema |   requires   |                               comment                                
---------+---------+-----------+-----------+-------------+--------+--------------+----------------------------------------------------------------------
 periods | 1.0     | f         | t         | f           |        | {btree_gist} | Provide Standard SQL functionality for PERIODs and SYSTEM VERSIONING
 periods | 1.1     | t         | t         | f           |        | {btree_gist} | Provide Standard SQL functionality for PERIODs and SYSTEM VERSIONING
(2 rows)

System Versioning on table with dropped columns contains corrupted data in history table

Hi, I'm trying to migrate my postgresql 11 database from temporal_tables to periods, and I ran into this issue.

From the looks of it, it kind of makes sense how this could happen since dropped columns in postgres don't just disappear. Is this a known limitation? I could always recreate the source table, but I'd like to make sure there's nothing I'm missing.

Minimal script:

CREATE TABLE test (
    test_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    value VARCHAR NOT NULL
);

ALTER TABLE test
    DROP COLUMN value;

ALTER TABLE test
    ADD COLUMN value2 varchar NOT NULL;

INSERT INTO test (value2)
    VALUES ('hello'), ('world');

SELECT periods.add_system_time_period ('test');
SELECT periods.add_system_versioning ('test');


UPDATE test
SET value2 = 'goodbye'
WHERE test_id = 2;

Expected:

SELECT * FROM test_with_history;

test_id | value2  | system_time_start             | system_time_end               |
--------|---------|-------------------------------|-------------------------------|
      1 | hello   |                     -infinity |                      infinity |
      2 | goodbye | 2019-12-04 18:04:55.825756-08 |                      infinity |
      2 | world   |                     -infinity | 2019-12-04 18:04:55.825756-08 |

Actual:

test_id | value2  | system_time_start             | system_time_end |
--------|---------|-------------------------------|-----------------|
      1 | hello   |                     -infinity |        infinity |
      2 | goodbye | 2019-12-04 18:04:55.825756-08 |        infinity |
      2 |  [NULL] | 2004-06-09 20:21:18.209996-07 |       -infinity |

Trigger system_time_write_history fails

Hello again

I have a database with 30 tables. Executing add_system_time_period and add_system_versioning completes with success. The issue I have is that updates in some of the tables simply crashes the database process and I get this message FATAL: the database system is in recovery mode.

I am executing like this
SELECT periods.add_system_time_period('"MyTable"'); SELECT periods.add_system_versioning('"MyTable"');

Update just a column for this table crashes the process, any column.

Also I tried recreating the same table with a different name and it just works, no more errors.

Can you help me in some way? How can I investigate this to know at least what is the issue or the exact error?

Thank you

Remove add_period from a table

Hello,
For the last few years I used this extension on a specific table

SELECT periods.add_period ('measurement', 'validity', 'tstart', 'tend');

Now I would like to go back to the original behavior. Basically I'd like to be able to write everything on the table without checks on tstart and tend values. I cannot figure out how to do it.

I believe I could leverage on a temporary table, but it seems a bit convoluted. Is there a better approach to remove this function?

How to update system versioning after altering a table?

Hello, I would like to know if there is a suggested way to update system versioning of a table after altering it.
E.g. after adding a column to a table with system versioning, how to update the history table, triggers, etc. so that system versioning works also for the new column.

Foreign key constraints allow inconsistent state?

Hi, I'm fiddling around with the project and this behavior seems odd to me, can anyone confirm it is the expected outcome?

CREATE TABLE uk (id integer, s integer, e integer, CONSTRAINT uk_pkey PRIMARY KEY (id, s, e));
SELECT periods.add_period('uk', 'p', 's', 'e');
SELECT periods.add_unique_key('uk', ARRAY['id'], 'p', key_name => 'uk_id_p', unique_constraint => 'uk_pkey');

CREATE TABLE fk (id integer, uk_id integer, s integer, e integer, PRIMARY KEY (id));
SELECT periods.add_period('fk', 'q', 's', 'e');
SELECT periods.add_foreign_key('fk', ARRAY['uk_id'], 'q', 'uk_id_p', key_name => 'fk_uk_id_q');

INSERT INTO uk(id, s, e)
    VALUES (1, 1, 3)
         , (1, 3, 5);

INSERT INTO fk(uk_id, id, s, e)
    VALUES (1, 1, 1, 2)
         , (1, 2, 2, 5);

--expected: fail,    behavior: deleted
delete from uk where (id, s, e) = (1, 1 ,3);

--expected: fail,    behavior: failed
delete from uk where (id, s, e) = (1, 3 ,5);

INSERT INTO uk(id, s, e)
    VALUES (2, 1, 5);

INSERT INTO fk(uk_id, id, s, e)
    VALUES (2, 4, 2, 4);

--expected: fail,    behavior: updated
update uk set e = 3 where (id,s,e) = (2,1,5);

Is this bi-temporal + scd approach correct ?

Hi, i've setup a simple example to test bi-temporal and scd with system versioning provided by periods. Could you help me validate whether this is the 'correct' approach ? I have anxieties over not using periods for the 'business validity' time.

The example had been tested successfully in psql 12. It includes ddl, dml statements and output in the comments:

CREATE TABLE example (
    id bigint PRIMARY KEY,
    value varchar(255) NOT NULL,
    start_date TIMESTAMPTZ NOT NULL DEFAULT 'infinity'::timestamp,
    end_date TIMESTAMPTZ NOT NULL DEFAULT 'infinity'::timestamp);
SELECT periods.add_system_time_period('example', 'row_start', 'row_end');
SELECT periods.add_system_versioning('example');

INSERT INTO example (id, value) VALUES (1, 'first');
INSERT INTO example (id, value) VALUES (2, 'second');

-- having start date value means it means something to the system
-- having end date value means it's 'deleted'
CREATE VIEW valid_example AS 
SELECT * FROM example WHERE start_date <> 'infinity'::timestamp
AND end_date = 'infinity'::timestamp;

-- the record is still in 'draft mode', not having any special meaning in the system
INSERT INTO example (id, value) VALUES (1, 'draft value');
SELECT * FROM valid_example;
/*
 id | value | start_date | end_date | row_start | row_end 
----+-------+------------+----------+-----------+---------
(0 rows)
*/

-- set start_date, meaning we're now switching 
-- from 'draft mode' to 'effective mode' in the system
UPDATE example SET start_date = NOW() WHERE id = 1;
SELECT * FROM valid_example;
/*
 id | value |          start_date           | end_date |           row_start           | row_end  
----+-------+-------------------------------+----------+-------------------------------+----------
  1 | first | 2020-06-23 02:11:02.362354+00 | infinity | 2020-06-23 02:11:02.362354+00 | infinity
(1 row)
*/

-- adding child to test scd join
CREATE TABLE example_child (
    id bigint PRIMARY KEY,
    example_id bigint NOT NULL REFERENCES example(id),
    value text,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
INSERT INTO example_child (id, example_id, value) VALUES (1, 1, 'first child 1');

CREATE VIEW view_example_child_scd AS 
SELECT e.id parent_id, e.value parent_value, c.id child_id, c.value child_value 
FROM example_child c
JOIN example__as_of(c.created_at) e ON e.id = c.example_id
ORDER BY c.id;

SELECT * FROM view_example_child_scd;
/*
 parent_id | parent_value | child_id |  child_value  
-----------+--------------+----------+---------------
         1 | first        |        1 | first child 1
(1 row)
*/

-- test scd join after updates to the example rec and then a new insert
UPDATE example SET value = 'first modified' WHERE id = 1;
INSERT INTO example_child (id, example_id, value) VALUES (2, 1, 'first child 2');
SELECT * FROM view_example_child_scd;
/*
 parent_id |  parent_value  | child_id |  child_value  
-----------+----------------+----------+---------------
         1 | first          |        1 | first child 1
         1 | first modified |        2 | first child 2
(2 rows)
*/

-- the record is now 'deleted', also not having any special meaning in the system, 
-- but can still be joined with as_of in scd join
UPDATE example SET end_date = NOW() WHERE id = 1;
SELECT * FROM valid_example;
/*
 id | value | start_date | end_date | row_start | row_end 
----+-------+------------+----------+-----------+---------
(0 rows)
*/

SELECT * FROM view_example_child_scd;
/*
scd=# SELECT * FROM view_example_child_scd;
 parent_id |  parent_value  | child_id |  child_value  
-----------+----------------+----------+---------------
         1 | first          |        1 | first child 1
         1 | first modified |        2 | first child 2
(2 rows)
*/

table example;
/*
 id |     value      |          start_date           |           end_date            |           row_start           | row_end  
----+----------------+-------------------------------+-------------------------------+-------------------------------+----------
  2 | second         | infinity                      | infinity                      | 2020-06-23 02:09:51.486705+00 | infinity
  1 | first modified | 2020-06-23 02:11:02.362354+00 | 2020-06-23 02:13:02.290119+00 | 2020-06-23 02:13:02.290119+00 | infinity
*/

table example_history;
/*
 id |     value      |          start_date           | end_date |           row_start           |            row_end            
----+----------------+-------------------------------+----------+-------------------------------+-------------------------------
  1 | first          | infinity                      | infinity | 2020-06-23 02:09:51.483929+00 | 2020-06-23 02:09:51.500365+00
  1 | first          | 2020-06-23 02:09:51.500365+00 | infinity | 2020-06-23 02:09:51.500365+00 | 2020-06-23 02:11:02.362354+00
  1 | first          | 2020-06-23 02:11:02.362354+00 | infinity | 2020-06-23 02:11:02.362354+00 | 2020-06-23 02:12:14.343354+00
  1 | first modified | 2020-06-23 02:11:02.362354+00 | infinity | 2020-06-23 02:12:14.343354+00 | 2020-06-23 02:13:02.290119+00
*/

drop table example_child cascade;
drop table example cascade;
drop table example_history cascade;

Allow history table to be partitioned

Is it possible to allow history table to be partitioned?
Function add_system_versioning doesn't allow me to do so, probably here:

periods/periods--1.2.sql

Line 2601 in d7b2081

CASE object_type

But if I create history table as normal and then substitute its name with name of partitioned
table all works fine:

UPDATE periods.system_versioning SET history_table_name = TO_REGCLASS('partitioned_table_hist')
WHERE history_table_name='normal_table_hist'::regclass;

The only problem is management. I have to change all partitioned table names every time I need to add
a new history table.

How to build extension for linux version?

I want to install this extension for postgres running on linux.
Is there already a package for linux? If not, is it possible to create one?

QUESTION: How to handle Schema changes?

Hello there!

Imagine I have a temporal table for personal information:

UUID (varchar)
main_document (varchar)
name (varchar)
DoB (timestamp)
genre (varchar)
address (varchar)
salary (decimal)

at T1 I run a schema migration and add a new column, from now on the table has:

UUID (varchar)
main_document (varchar)
name (varchar)
DoB (timestamp)
genre (varchar)
address (varchar)
salary (decimal)
EMAIL (varchar)*

Then at T2 I run another schema migration and change the data type of main_document to NUMBER.

UUID (varchar)
main_document (NUMBER)*
name (varchar)
DoB (timestamp)
genre (varchar)
address (varchar)
salary (decimal)
email (varchar)

Then at T3 I run another schema migration and remove the genre column

UUID (varchar)
main_document (number)
name (varchar)
DoB (timestamp)
---------------*
address (varchar)
salary (decimal)
email (varchar)

Then at T4 I run another schema migration and add the genre column, but now it has the data type NUMBER.

UUID (varchar)
main_document (number)
name (varchar)
DoB (timestamp)
genre (NUMBER)*
address (varchar)
salary (decimal)
email (varchar)

How can I query my DB (going back in time) without breaking my existing queries? SQL:2016

Are there any best practices or strategies to avoid all this complexity with these temporal tables + schema migration?

Any help would be much appreciated.

P.S: I didn't find the RIGHT place to post a question to the community. If that's the wrong place, please feel free to remove it. (sorry)

Thanks

syntax error on Windows

PostgreSQL-12 (12.2.1) on Win-10 / Test version can change if needed.

Hi,

thank you for this extension.
I'm learning "Temporal tables" and "System Versioned Tables" from SQL:2011. I've quickly found there's no support as of now in PostgreSQL. Some users mentionned using "temporal_tables" extension and from there found your project that I had missed out.

I've never used PostgreSQL extensions, I might have missed the real how-to.
I got the binaries (thanks for providing them) (in my case: periods-v1.1-pgREL_12_STABLE-windows-x64.zip). I unzipped and installed in folders lib/periods.dll and share/extension/[periods.control, periods--1.0.sql, periods--1.0--1.1.sql, periods--1.1.sql]. Hope it's the right way!

I run the commands

test=# create extension if not exists periods cascade; -- cascade required for 'btree_gist'
CREATE EXTENSION
-- from the README:
test=# CREATE TABLE example ( id bigint PRIMARY KEY, value text, PERIOD FOR system_time (row_start, row_end) ) WITH SYSTEM VERSIONING;
ERREUR:  erreur de syntaxe sur ou près de « FOR » (sorry in French: syntax error on FOR)
LIGNE 1 : ...ample ( id bigint PRIMARY KEY, value text, PERIOD FOR system...
                                                               ^

I tried with another PostgreSQL version, but still no luck. Would you mind explaining the error I might have done? Thanks!

Cannot update a row

create extension if not exists periods cascade;
create table store (
	ID serial primary key,
	name text not null
);
SELECT periods.add_system_time_period('store');
SELECT periods.add_system_versioning('store');
insert into store values (default, 'lol');
update store set name = 'newname' where id = 1;

Everything before the update seems to have run correctly

 add_system_time_period
------------------------
 t
(1 row)
NOTICE:  history table "store_history" created for "store", be sure to index it properly
 add_system_versioning
-----------------------

(1 row)
INSERT 0 1

but after the update

ERROR:  column stp.excluded_column_names does not exist
LINE 1: ...iods.system_time_periods AS stp CROSS JOIN unnest(stp.exclud...
                                                             ^
QUERY:  SELECT u.name FROM periods.system_time_periods AS stp CROSS JOIN unnest(stp.excluded_column_names) AS u (name) WHERE stp.table_name = $1

I'm using 783475d with postgres 12 on debian sid via wsl1

Usage of "_as_of ()"

Hi there,

using postgresql-11-periods/focal-pgdg,now 1.2-1.pgdg20.04+2 amd64

I've defined a table with system versioning as follow:

create table test1 (
  id serial primary key,
  status varchar (30) default 'on'
);
select periods.add_system_time_period ('test1', 'row_start', 'row_end');
select periods.add_system_versioning ('test1');

I've then add some rows to my table via:

insert into test1 (status) values ('on');
insert into test1 (status) values ('off');
insert into test1 (status) values ('on');

Now I'm trying to get my value at given date via test1__as_of ():

ls=# SELECT * FROM test1__as_of (now ());
 id | status |           row_start           | row_end             
----+--------+-------------------------------+----------
  1 | on     | 2020-10-21 19:37:07.567824+02 | infinity
  2 | off    | 2020-10-21 19:52:33.665405+02 | infinity
  3 | on     | 2020-10-21 19:53:44.834846+02 | infinity
(3 rows)

ls=# SELECT * FROM test1__as_of ('2020-10-21 19:52:35.665405+02');
 id | status |           row_start           | row_end             
----+--------+-------------------------------+----------
  1 | on    | 2020-10-21 19:37:07.567824+02 | infinity
  2 | off   | 2020-10-21 19:52:33.665405+02 | infinity
(2 rows)

ls=# SELECT * FROM test1__as_of ('2020-10-21 19:40:07.567824+02');
 id | status |           row_start           | row_end             
----+--------+-------------------------------+----------
  1 | on     | 2020-10-21 19:37:07.567824+02 | infinity
(1 row)

The question is: why I'm gotting all the records instead of a single one containing my status value at given date ?
Am I missing something ?

Best.

Add support for custom system time

Thanks for the extension. Very useful.
I am wondering if it is planned to support the possibility of having a custom system time to be sent for versioning together with the inserts or updates. Something similar to what is described here.

Currently our system stores changes and writes them (few seconds/minutes later) to the postgresql. For making sense of the data it would be useful to associate the timestamp at which the changes happened rather than relying on the system time (the transaction_timestamp() )

I was trying to use only add.periods and using the start_time and end_time as columns for the validity period and playing with add.periods_for_partion_views in order to send updates but it becomes a nightmare (I am not an expert of postgresql). From what I read I might need to implement triggers but I wouldn't like to redo what you already did (surely better than me).

In my understanding I cannot create an history table without first calling the periods.add_system_time_period and periods.add_system_versioning

Is there a workaround to avoid using the transaction_timestamp() but still leverage on all the work you did for versioning? Thanks!

How to migrate from temporal_tables?

I would like to migrate to periods from temporal_tables. Is there a recipe for doing this without data loss?

Support system versioning for views

SQL Server supports querying views with the AS OF clause and SQL Server will transparently apply the clause to all temporal tables participating in the view definition's query. See https://docs.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver15#using-views-with-as-of-sub-clause-in-temporal-queries for details.

I don't know if this feature is defined by the SQL standard or not, but it looks very powerful indeed.

Clarify how to use pg_dump & pg_restore with periods

Relating to #22, I've recently stumbled over the problem of "how to dump & restore system versioned tables".

My personal preference of pg_dump --format=custom --file=dump.psql my_source_database & pg_restore --format=custom --clean --if-exists --single-transcation -d my_target_database doesn't seem to work with periods, as it doesn't seem to like, how pg_restore directly fiddles with its managed tables.

My current workaround for this is

ensure the periods extension is installed in the target database
create the dump with pg_dump as described above
create a use-list, that excludes the periods extension from the restore: pg_restore --format=custom --list dump.psql | grep -Ev 'EXTENSION - (periods|btree_gist) > dump.use-list

disable periods's event-triggers:

ALTER EVENT TRIGGER periods_drop_protection DISABLE;
ALTER EVENT TRIGGER periods_health_checks DISABLE;
ALTER EVENT TRIGGER periods_rename_following DISABLE;

restore the dump, using the use-list pg_restore --format=custom --single-transaction --clean --if-exists --use-list=dump.use-list -d my_target_database dump.psql

re-enable the event-triggers:

ALTER EVENT TRIGGER periods_drop_protection ENABLE;
ALTER EVENT TRIGGER periods_health_checks ENABLE;
ALTER EVENT TRIGGER periods_rename_following ENABLE;

Is there any other recommended way to dumping and restoring a database with periods?

A couple of notes:

The above workaround probably only works correctly, if the entire database is dumped and restored - or at least all periods employing tables all at once -, as the dumped & restored tables in periods's schema (also called periods by default) contain meta-data for all periods employing tables.
btree_gist needs to be excluded from the use-list too, as it's a dependency of periods and thus can't be dropped and recreated during restore
pg_dump currently only supports including named extensions (-e argument) but doesn't support excluding named extensions, which makes the use of use-lists necessary

Existing rows system_time_start is set to infinity

Hello

I encountered this scenario when there are already data in the tables and when executing add_system_time_period the default value for system_time_start is set to infinity.

Is this the expected behavior or am I doing something wrong?

Thank you

PG16: acl tests failing

There a new MAINTAIN privilege, and likely other changes.

https://pgdgbuild.dus.dg-i.net/view/Binaries-Beta/job/postgresql-periods-binaries-beta/12/architecture=amd64,distribution=sid/console#console-section-9

Crash on PG14

Periods is crashing with PG14:

15:49:29  UPDATE sysver SET flap = not flap;
15:49:29 -UPDATE sysver SET flap = not flap;
15:49:29 -UPDATE sysver SET flap = not flap;

https://pgdgbuild.dus.dg-i.net/view/Testsuite-Beta/job/postgresql-periods-autopkgtest-beta/lastFailedBuild/architecture=amd64,distribution=sid/console

15:49:29 #0  0x0000559618975bd1 in ?? ()
15:49:29 No symbol table info available.
15:49:29 #1  0x0000559618975ef7 in datumIsEqual ()
15:49:29 No symbol table info available.
15:49:29 #2  0x00007f0a94d58068 in OnlyExcludedColumnsChanged (rel=<optimized out>, old_row=0x55961b09d7f0, new_row=0x55961b09d618) at ./periods.c:291
15:49:29         old_datum = <optimized out>
15:49:29         new_isnull = false
15:49:29         typlen = <optimized out>
15:49:29         new_datum = <optimized out>
15:49:29         old_isnull = false
15:49:29         typbyval = <optimized out>
15:49:29         ret = <optimized out>
15:49:29         i = 3
15:49:29         values = {17318}
15:49:29         tupdesc = 0x7f0a9b662640
15:49:29         excluded_attnums = 0x55961a8989b0
15:49:29         mcxt = <optimized out>
15:49:29         sql = 0x7f0a94d590f8 "SELECT u.name FROM periods.system_time_periods AS stp CROSS JOIN unnest(stp.excluded_column_names) AS u (name) WHERE stp.table_name = $1"
15:49:29         qplan = 0x55961b367fb0
15:49:29         __func__ = "OnlyExcludedColumnsChanged"
15:49:29 #3  0x00007f0a94d584a6 in generated_always_as_row_start_end (fcinfo=<optimized out>) at ./periods.c:487
15:49:29         old_row = <optimized out>
15:49:29         trigdata = <optimized out>
15:49:29         funcname = 0x7f0a94d592a0 "generated_always_as_row_start_end"
15:49:29         rel = 0x7f0a9b4e7ff8
15:49:29         new_row = 0x55961b09d618
15:49:29         new_tupdesc = 0x7f0a9b662640
15:49:29         values = {94103179959864, 139683532017304}
15:49:29         nulls = {false, false}
15:49:29         columns = {-1689350504, 32522}
15:49:29         start_name = 0x1af35ff1c <error: Cannot access memory at address 0x1af35ff1c>
15:49:29         end_name = 0x55961a7cbb30 "\200\216N\233\n\177"
15:49:29         start_num = <optimized out>
15:49:29         end_num = <optimized out>
15:49:29         typeid = <optimized out>
15:49:29         __func__ = "generated_always_as_row_start_end"
15:49:29 #4  0x0000559618a732c5 in fmgr_security_definer ()
15:49:29 No symbol table info available.

Capitalised role throws error on add_system_versioning

Hi,

Trying to add system versioning to a table owned by the role 'TestUser' throws an exception.

PostgreSQL 13 on Windows
Periods extension compiled locally from source
SQL to reproduce:

CREATE ROLE "TestUser";

CREATE TABLE public."TestTable"
(
    "Id" bigint NOT NULL,
    CONSTRAINT "TestTable_pkey" PRIMARY KEY ("Id")
);

ALTER TABLE public."TestTable" owner to "TestUser";

select periods.add_system_time_period('public."TestTable"', 'FromDate', 'ToDate');
select periods.add_system_versioning('public."TestTable"');

Exception thrown:

ERROR:  role ""TestUser"" does not exist
CONTEXT:  SQL statement "ALTER TABLE public."TestTable_history" OWNER TO """TestUser""""
PL/pgSQL function periods.add_system_versioning(regclass,name,name,name,name,name,name) line 147 at EXECUTE
SQL state: 42704

Looking at the sql in periods--1.2.sql, as far as I can tell the table_owner variable will already double quote the user name when converted to text, so the $I formatting in the execute statement causes another double quote, hence the error.

table_owner is set here on line 2410:

    SELECT n.nspname, c.relname, c.relowner, c.relpersistence, c.relkind
    INTO schema_name, table_name, table_owner, persistence, kind
    FROM pg_catalog.pg_class AS c
    JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
    WHERE c.oid = table_class;

then used here, which is where it gets double quoted again on line 2521 (presumably everywhere table_owner is used with $I):

EXECUTE format('ALTER TABLE %1$I.%2$I OWNER TO %3$I', schema_name, history_table_name, table_owner);

For now I can just user lowercase, but if this project makes it to production casing will be out of my control.

My PostgreSQL knowledge is severely lacking too, sorry. My background is SQL Server.

Thanks,
Mark

relation "example2__for_portion_of_validity" does not exist

CREATE TABLE example2 (
    id bigint,
	value text,
    start_date date,
    end_date date,
    CONSTRAINT example2_pkey PRIMARY KEY (id, start_date, end_date)
);

SELECT periods.add_period('example2', 'validity', 'start_date', 'end_date');
SELECT periods.add_unique_key('example2', ARRAY['id'], 'validity', unique_constraint => 'example2_pkey');

insert into example2 values(1, 'one', '2022-01-01', '2022-12-31');
select * from example2;

update example2__for_portion_of_validity
	set value='one one',
	start_date='2022-03-01',
	end_date='2022-05-01'
where id=1;

This fails with:

ERROR:  relation "example2__for_portion_of_validity" does not exist
LINE 1: update example2__for_portion_of_validity
               ^
SQL state: 42P01
Character: 8

Am I doing something wrong?

Is this sysver?

Your great PGday 2019 presentation mentions https://github.com/xocolatl/sysver which now is a 404. Is this repository the equivalent of sysver?

Can't adjust GRANTs after backup and restore

I tried the following on PostgreSQL 13+14 on Windows 10.

I created a new database and ran:

create extension periods cascade;

create table test (id int not null primary key);

select periods.add_system_time_period('test');
select periods.add_system_versioning('test');

Everything works fine.

Then I dump and restore the database:

pg_dump.exe --file=periods_test.dump --format=custom periods_test
psql -c "DROP DATABASE periods_test;"
pg_restore -d postgres -C periods_test.dump

pg_restore raises these errors:

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3392; 0 0 ACL TABLE test_history postgres
pg_restore: error: could not execute query: ERROR:  cannot revoke SELECT directly from "public.test_history", revoke SELECT from "public.test" instead
CONTEXT:  PL/pgSQL function periods.health_checks() line 255 at RAISE
Command was: REVOKE ALL ON TABLE public.test_history FROM postgres;
GRANT SELECT ON TABLE public.test_history TO postgres;


pg_restore: from TOC entry 3393; 0 0 ACL TABLE test_with_history postgres
pg_restore: error: could not execute query: ERROR:  cannot revoke SELECT directly from "public.test_with_history", revoke SELECT from "public.test" instead
CONTEXT:  PL/pgSQL function periods.health_checks() line 255 at RAISE
Command was: REVOKE ALL ON TABLE public.test_with_history FROM postgres;
GRANT SELECT ON TABLE public.test_with_history TO postgres;


pg_restore: warning: errors ignored on restore: 2

From now on, I can't change privileges.

If I run grant select on test to postgres; I get

ERROR:  cannot grant DELETE to "test_history"; history objects are read-only
CONTEXT:  PL/pgSQL function periods.health_checks() line 138 at RAISE

I think the reason for this is in

periods/periods--1.2.sql

Lines 3397 to 3459 in 9bb3df9

 FOR r IN 

 SELECT *, 

 EXISTS ( 

 SELECT 

 FROM pg_class AS _c 

 CROSS JOIN LATERAL aclexplode(COALESCE(_c.relacl, acldefault('r', _c.relowner))) AS _acl 

 WHERE _c.oid = objects.table_name 

 AND _acl.grantee = objects.grantee 

 AND _acl.privilege_type = 'SELECT' 

 ) AS on_base_table 

 FROM ( 

 SELECT sv.table_name, 

 c.oid::regclass::text AS object_name, 

 c.relkind AS object_type, 

 acl.privilege_type, 

 acl.privilege_type AS base_privilege_type, 

 acl.grantee, 

 'h' AS history_or_portion 

 FROM periods.system_versioning AS sv 

 JOIN pg_class AS c ON c.oid IN (sv.history_table_name, sv.view_name) 

 CROSS JOIN LATERAL aclexplode(COALESCE(c.relacl, acldefault('r', c.relowner))) AS acl 

 UNION ALL 

 SELECT fpv.table_name, 

 c.oid::regclass::text, 

 c.relkind, 

 acl.privilege_type, 

 acl.privilege_type, 

 acl.grantee, 

 'p' AS history_or_portion 

 FROM periods.for_portion_views AS fpv 

 JOIN pg_class AS c ON c.oid = fpv.view_name 

 CROSS JOIN LATERAL aclexplode(COALESCE(c.relacl, acldefault('r', c.relowner))) AS acl 

 UNION ALL 

 SELECT sv.table_name, 

 p.oid::regprocedure::text, 

 'f', 

 acl.privilege_type, 

 'SELECT', 

 acl.grantee, 

 'h' 

 FROM periods.system_versioning AS sv 

 JOIN pg_proc AS p ON p.oid = ANY (ARRAY[sv.func_as_of, sv.func_between, sv.func_between_symmetric, sv.func_from_to]::regprocedure[]) 

 CROSS JOIN LATERAL aclexplode(COALESCE(p.proacl, acldefault('f', p.proowner))) AS acl 

 ) AS objects 

 ORDER BY object_name, object_type, privilege_type 

 LOOP 

 IF 

 r.history_or_portion = 'h' AND 

 (r.object_type, r.privilege_type) NOT IN (('r', 'SELECT'), ('v', 'SELECT'), ('f', 'EXECUTE')) 

 THEN 

 RAISE EXCEPTION 'cannot grant % to "%"; history objects are read-only', 

 r.privilege_type, r.object_name; 

 END IF; 

 IF NOT r.on_base_table THEN 

 RAISE EXCEPTION 'cannot grant % directly to "%"; grant % to "%" instead', 

 r.privilege_type, r.object_name, r.base_privilege_type, r.table_name; 

 END IF; 

 END LOOP;

In the CROSS JOIN is always COALESCE(c.relacl, acldefault('r', c.relowner)).

When pg_restore creates the table test_history, the table only has the default acl belonging to the owner (in the query c.relacl is null, so COALESCE returns acldefault('r', c.relowner) which is {postgres=arwdDxt/postgres}).

The query returns:

table_name	object_name	object_type	privilege_type	base_privilege_type	grantee	history_or_portion	on_base_table
test	test__as_of(timestamp with time zone)	f	EXECUTE	SELECT	10	h	t
test	test__between(timestamp with time zone,timestamp with time zone)	f	EXECUTE	SELECT	10	h	t
test	test__between_symmetric(timestamp with time zone,timestamp with time zone)	f	EXECUTE	SELECT	10	h	t
test	test__from_to(timestamp with time zone,timestamp with time zone)	f	EXECUTE	SELECT	10	h	t
test	test_history	r	DELETE	DELETE	10	h	t
test	test_history	r	INSERT	INSERT	10	h	t
test	test_history	r	REFERENCES	REFERENCES	10	h	t
test	test_history	r	SELECT	SELECT	10	h	t
test	test_history	r	TRIGGER	TRIGGER	10	h	t
test	test_history	r	TRUNCATE	TRUNCATE	10	h	t
test	test_history	r	UPDATE	UPDATE	10	h	t
test	test_with_history	v	DELETE	DELETE	10	h	t
test	test_with_history	v	INSERT	INSERT	10	h	t
test	test_with_history	v	REFERENCES	REFERENCES	10	h	t
test	test_with_history	v	SELECT	SELECT	10	h	t
test	test_with_history	v	TRIGGER	TRIGGER	10	h	t
test	test_with_history	v	TRUNCATE	TRUNCATE	10	h	t
test	test_with_history	v	UPDATE	UPDATE	10	h	t

Because all of the default acl privilege types are returned,

periods/periods--1.2.sql

Lines 3447 to 3453 in 9bb3df9

 IF 

 r.history_or_portion = 'h' AND 

 (r.object_type, r.privilege_type) NOT IN (('r', 'SELECT'), ('v', 'SELECT'), ('f', 'EXECUTE')) 

 THEN 

 RAISE EXCEPTION 'cannot grant % to "%"; history objects are read-only', 

 r.privilege_type, r.object_name; 

 END IF;

raises the exception.

pg_restore can't GRANT the SELECT privilege, so c.relacl will always be null and the health_check() trigger will block every GRANT. Even for tables which don't have periods.

Indexes not updated

Thanks for working on bringing system versioning to PostgreSQL. Looking forward to see this in vanilla PostgreSQL.

I think I found a bug (at least I'm out of ideas) regarding indexes on history tables not being updated.

Minimal example:

SET enable_seqscan=FALSE

CREATE TABLE "test" (
    "id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    "revision" INTEGER NOT NULL
)

SELECT periods.add_system_time_period('test', 'row_start_time', 'row_end_time')
SELECT periods.add_system_versioning('test')

INSERT INTO "test" ("revision") VALUES (1)
INSERT INTO "test" ("revision") VALUES (10)

UPDATE "test" SET "revision" = 2 WHERE ("id" = 1)
UPDATE "test" SET "revision" = 3 WHERE ("id" = 1)

CREATE INDEX "yolo" ON "test_history" ("id", "revision")

UPDATE "test" SET "revision" = 11 WHERE ("id" = 2)

# returns 2 rows
SELECT * FROM "test_history" WHERE "id" = 1

# returns 0 rows if index is used / 1 row if seq scan is used
SELECT * FROM "test_history" WHERE "id" = 2

I'm using a Mac (10.5.4) with PostgreSQL 12.2 with dacb886 build from source.

Installation error

Good afternoon.
Please tell me how to solve the problem of installing the extension.
I compiled the extension from sources and installed it on PostgreSQL 14 version (not the default version). The extension is present in the list of available ones, but during installation an error occurs -

SQL Error [58P01]: ERROR: could not load library "/usr/local/pgsql/lib/periods.so": /usr/local/pgsql/lib/periods.so: undefined symbol: elog_finish
ERROR: could not load library "/usr/local/pgsql/lib/periods.so": /usr/local/pgsql/lib/periods.so: undefined symbol: elog_finish
ERROR: could not load library "/usr/local/pgsql/lib/periods.so": /usr/local/pgsql/lib/periods.so: undefined symbol: elog_finish

error when inserting new record

I get an error when I try to insert a record

CREATE TABLE _example (
id bigint PRIMARY KEY,
value text
);
SELECT periods.add_system_time_period('_example', 'row_start', 'row_end');
SELECT periods.add_system_versioning('_example');

insert into _example select '123434'; <--- error function "generated_always_as_row_start_end" was not called by trigger manager

Support for Range types (e.g. tstzrange?)

Hi, this extension looks excellent. I have been looking to refactor out a home-grown trigger based solution with something closer to standards-based. I'm wondering if this extension will support range types so we can avoid having to have separate from and to columns?

	FOR r IN
	SELECT *,
	EXISTS (
	SELECT
	FROM pg_class AS _c
	CROSS JOIN LATERAL aclexplode(COALESCE(_c.relacl, acldefault('r', _c.relowner))) AS _acl
	WHERE _c.oid = objects.table_name
	AND _acl.grantee = objects.grantee
	AND _acl.privilege_type = 'SELECT'
	) AS on_base_table
	FROM (
	SELECT sv.table_name,
	c.oid::regclass::text AS object_name,
	c.relkind AS object_type,
	acl.privilege_type,
	acl.privilege_type AS base_privilege_type,
	acl.grantee,
	'h' AS history_or_portion
	FROM periods.system_versioning AS sv
	JOIN pg_class AS c ON c.oid IN (sv.history_table_name, sv.view_name)
	CROSS JOIN LATERAL aclexplode(COALESCE(c.relacl, acldefault('r', c.relowner))) AS acl

	UNION ALL

	SELECT fpv.table_name,
	c.oid::regclass::text,
	c.relkind,
	acl.privilege_type,
	acl.privilege_type,
	acl.grantee,
	'p' AS history_or_portion
	FROM periods.for_portion_views AS fpv
	JOIN pg_class AS c ON c.oid = fpv.view_name
	CROSS JOIN LATERAL aclexplode(COALESCE(c.relacl, acldefault('r', c.relowner))) AS acl

	UNION ALL

	SELECT sv.table_name,
	p.oid::regprocedure::text,
	'f',
	acl.privilege_type,
	'SELECT',
	acl.grantee,
	'h'
	FROM periods.system_versioning AS sv
	JOIN pg_proc AS p ON p.oid = ANY (ARRAY[sv.func_as_of, sv.func_between, sv.func_between_symmetric, sv.func_from_to]::regprocedure[])
	CROSS JOIN LATERAL aclexplode(COALESCE(p.proacl, acldefault('f', p.proowner))) AS acl
	) AS objects
	ORDER BY object_name, object_type, privilege_type
	LOOP
	IF
	r.history_or_portion = 'h' AND
	(r.object_type, r.privilege_type) NOT IN (('r', 'SELECT'), ('v', 'SELECT'), ('f', 'EXECUTE'))
	THEN
	RAISE EXCEPTION 'cannot grant % to "%"; history objects are read-only',
	r.privilege_type, r.object_name;
	END IF;

	IF NOT r.on_base_table THEN
	RAISE EXCEPTION 'cannot grant % directly to "%"; grant % to "%" instead',
	r.privilege_type, r.object_name, r.base_privilege_type, r.table_name;
	END IF;
	END LOOP;

xocolatl / periods Goto Github PK

periods's People

Contributors

Stargazers

Watchers

Forkers

periods's Issues

Recommend Projects

Recommend Topics

Recommend Org