Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
-- Utility functions

create or replace function diff_range( base_range tstzrange, diff_range tstzrange )
returns setof tstzrange as $$
select * from unnest( case
   when NOT base_range && diff_range then array[ base_range ]
   when base_range <@ diff_range then array[]::tstzrange[]
   when base_range @> diff_range
    AND (
      upper(base_range) is distinct from upper(diff_range)
      OR upper_inc(base_range) <> upper_inc(diff_range)
    )
    AND (
      lower(base_range) is distinct from lower(diff_range)
      OR lower_inc(base_range) <> lower_inc(diff_range)
    )
    then array[
      tstzrange( lower(base_range), lower(diff_range), concat(
        CASE WHEN lower_inc(base_range) THEN '[' ELSE '(' END,
        CASE WHEN lower_inc(diff_range) THEN ')' ELSE ']' END
      ) ),
      tstzrange( upper(diff_range), upper(base_range), concat(
        CASE WHEN upper_inc(diff_range) THEN '(' ELSE '[' END,
        CASE WHEN upper_inc(base_range) THEN ']' ELSE ')' END
      ) )
   ]
   when base_range && diff_range then array[ base_range -diff_range ]
   -- This case should never occur:
   else array[ tstzrange( '1970-01-01', '1970-01-01' ) ]
end );
$$ immutable language sql;


create or replace function diff_range_arr( base_range tstzrange, diff_range tstzrange )
returns tstzrange[] as $$
select case
   when NOT base_range && diff_range then array[ base_range ]
   when base_range <@ diff_range then array[]::tstzrange[]
   when base_range @> diff_range
    AND (
      upper(base_range) is distinct from upper(diff_range)
      OR upper_inc(base_range) <> upper_inc(diff_range)
    )
    AND (
      lower(base_range) is distinct from lower(diff_range)
      OR lower_inc(base_range) <> lower_inc(diff_range)
    )
    then array[
      tstzrange( lower(base_range), lower(diff_range), concat(
        CASE WHEN lower_inc(base_range) THEN '[' ELSE '(' END,
        CASE WHEN lower_inc(diff_range) THEN ')' ELSE ']' END
      ) ),
      tstzrange( upper(diff_range), upper(base_range), concat(
        CASE WHEN upper_inc(diff_range) THEN '(' ELSE '[' END,
        CASE WHEN upper_inc(base_range) THEN ']' ELSE ')' END
      ) )
   ]
   when base_range && diff_range then array[ base_range -diff_range ]
   else array[ tstzrange( '1970-01-01', '1970-01-01' ) ]
end;
$$ immutable language sql;


-- variables for accessing bi-temporal data
create or replace function biconf( _name text )
returns text as $$
select nullif( current_setting( 'my.' || _name, true ), '' )
$$ stable language sql;


create or replace function sys_time()
returns timestamptz as $$
declare xtime timestamptz;
begin
select coalesce( biconf( 'sys_time' )::timestamptz, now() )
  into xtime;
return xtime;
end;
$$ stable language plpgsql;


create or replace function app_time()
returns timestamptz as $$
WITH a AS( select biconf( 'app_period' )::tstzrange as p )
select case
  WHEN upper(( select p from a )) = lower(( select p from a ))
    AND lower_inc(( select p from a ))  AND upper_inc(( select p from a ))
    THEN lower(( select p from a ))
  ELSE
    NULL
END;
$$ stable language sql;


create or replace function app_period()
returns tstzrange as $$
SELECT CASE
  WHEN app_time() IS NOT NULL THEN tstzrange( app_time(), null )
  ELSE biconf( 'app_period' )::tstzrange
END;
$$ stable language sql;


create or replace function app_period( _range tstzrange )
returns tstzrange as $$
select set_config( 'my.app_period', _range::text, false );
select coalesce( _range, app_period() );
$$ stable language sql;


create or replace function app_period( _from timestamptz, _to timestamptz )
returns tstzrange as $$
select case when _from = _to
  then app_period( tstzrange( _from, _to, '[]' ) )
  else app_period( tstzrange( _from, _to, '[)' ) )
  end;
$$ stable language sql;


create or replace function app_time( _tstz timestamptz )
returns timestamptz as $$
select
  case when _tstz is not null
  then app_period( _tstz, _tstz )
  else app_period( null )
  end;
select coalesce( _tstz, app_time() );
$$ stable language sql;


-- Utility functions used by triggers to change bi-temporal data


create or replace function deactivate_sql( _tbl regclass )
returns text as $$
begin
  return format('
  -- ****************************************
  -- DEACTIVATION start: update sys_period for affected rows

  if debug then
    raise notice ''DEACTIVATION'';
    raise notice ''sys_time(): %%'', sys_time();
    for r in
      select *
      from %1$s
      WHERE id = OLD.id and app_period = OLD.app_period
        AND  sys_period @> sys_time()
    loop raise notice ''before: %%'', r; end loop;
  end if;

  UPDATE %1$s
  SET
    -- NOTICE: Here new values for sys_time column should be now() instead of sys_time()
    -- Because we can not change past. I do this only for testing purpose
    sys_period = tstzrange( lower(sys_period), sys_time(), ''[)'')
  WHERE id = OLD.id and app_period = OLD.app_period
    AND  sys_period @> sys_time();

  if debug then
  for r in
    select *
    from %1$s
    WHERE id = OLD.id and app_period = OLD.app_period
      AND sys_period -|- tstzrange( sys_time(), NULL )
  loop raise notice ''after : %%'', r; end loop;
  end if;

  -- DEACTIVATION end
  -- ****************************************
', _tbl );

end;
$$ language plpgsql;


create or replace function splitting_sql( _tbl regclass, _view text, _column_list text )
returns text as $$
begin
  return format('
  -- ****************************************
  -- SPLITTING start

  IF app_period() IS NOT NULL
    -- Small optimization. If OLD.app_period contained by app_period() then
    -- whole record is deactivated and there is nothing to split
    AND NOT OLD.app_period <@ app_period()
  THEN
    -- TODO: Create SPLITTING tests with 0 result for DELETE trigger
    if debug then
    -- Here we get the count of splitted intervals: 2, 1, 0
    for r in
      select
        coalesce( array_length( diff_range_arr(app_period, app_period() ), 1 ), 0 ),
        id, %3$s, app_period
      from (select OLD.*) t
    loop raise notice ''SPLITTING: %%'', r; end loop;

    for r in
      select id, %3$s, diff_range( app_period, app_period() )
      from (select OLD.*) t
    loop raise notice ''SPLITTED: %%'', r; end loop;
    end if;

    insert into %1$I ( id, %3$s, app_period )
    select id, %3$s, diff_range( app_period, app_period() )
    from (select OLD.*) t;

    --- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    --- !!! Unfortunately PG does not return OLD
    OLD.app_period =  OLD.app_period * app_period();
  END IF;

  -- SPLITTING end
  -- ****************************************
  ', _tbl, _view, _column_list );
end
$$ language plpgsql;

-- Function to make table bi-temporal from usual table

create or replace function make_bi_temporal( _tbl regclass )
returns void as $$
DECLARE
  _tbl_name text;
BEGIN
_tbl_name =  regexp_replace( _tbl::text, '"', '', 'g' );
execute format( 'alter table %s drop constraint if exists %s_pkey', _tbl, _tbl_name );
execute format( 'alter table %s rename to %s_bt', _tbl, _tbl_name );
execute format( 'alter table %s add column sid serial', _tbl );
execute format( 'alter table %s add column app_period tstzrange NOT NULL DEFAULT app_period()', _tbl );
execute format( 'alter table %s add column sys_period tstzrange NOT NULL DEFAULT tstzrange( sys_time(), NULL, ''[)'' )', _tbl );
execute format( 'alter table %s add constraint %s_id_sys_period_app_period_excl EXCLUDE USING gist ( id WITH =, sys_period WITH &&, app_period WITH && )', _tbl, _tbl_name );
execute format( 'alter table %s add constraint %s_sys_period_not_empty_check CHECK ( NOT isempty( sys_period)  )', _tbl, _tbl_name );
-- One possible issues still exists: Should we rename sequence/index etc. too?

-- NOTICE: here _tbl refer to not bi-temporal name from start: table_name
-- When relation is renamed the _tbl continue refer it
-- but now with different name!
perform refresh_bi_temporal( _tbl );

END $$ language plpgsql;


-- Function to refresh view for bi-temporal data when base table is changed
create or replace function refresh_bi_temporal( _tbl regclass )
returns void as $$
DECLARE
  _column_arr      text[];
  _new_column_arr  text[];
  _column_list     text;
  _new_column_list text;
  _view            text;
  x                text;
BEGIN

-- HUG
raise notice 'WARNING!!! WARNING!!! Do not forget to set search_path'
  USING HINT = 'set search_path TO ''public''';

execute format( '
  SELECT array_agg( column_name::text )
  FROM information_schema.Columns
  WHERE table_name = ''%I''
  -- Commented out for dbfiddle
  -- and table_schema = ( select setting from pg_settings WHERE name=''search_path'')
  and column_name not in ( ''id'', ''sid'', ''app_period'', ''sys_period'' )
  ', _tbl
) INTO _column_arr;

_view =  regexp_replace( _tbl::text, '_bt$', '' );

_column_list =  array_to_string( _column_arr, ',' );

FOREACH x IN ARRAY _column_arr LOOP
  _new_column_arr =  array_append( _new_column_arr, format( 'new.%s', x ) );
END LOOP;
_new_column_list =  array_to_string( _new_column_arr, ',' );


-- Probably here for app_period we should display only current period:
-- app_period * app_period()
-- BUT! When we display whole period the user can understand that
-- range is much wider compared to case when we hide this info
execute format( 'DROP VIEW IF EXISTS %I; create view %I as
  select id, %s, app_period
  FROM %I
  WHERE sys_period @> sys_time() AND(
    biconf( ''app_period'' ) IS NULL
    OR  app_period && biconf( ''app_period'' )::tstzrange
  )',
  _view, _view, _column_list, _tbl
);

--TODO: Use pg_get_expr to get table defaults and apply them to VIEW
-- Currently workaround is used:
-- alter view order_detail alter column sort_order set default 1;

execute format('
  create or replace function %1$s_insert()
  returns trigger as $fn$
  begin

  IF( new.id is null ) THEN
    -- No need to clear because new identificator will be assigned to a row ,
    -- thus no conflict can happen
    if( new.app_period is null ) THEN
      INSERT INTO %1$I ( %3$s )
      values ( %4$s )
      returning id, %3$s, app_period INTO new;
    ELSE
      INSERT INTO %1$I ( %3$s, app_period )
      values ( %4$s, new.app_period )
      returning id, %3$s, app_period INTO new;
    END IF;
  ELSE
    -- No need to clear values from app_period() while INSERT
    -- because insert just insert new values, if client want to escape conflict with existing rows
    -- it MUST delete conflicting values from required period
    if( new.app_period is null ) THEN
      INSERT INTO %1$I ( id, %3$s )
      values ( new.id, %4$s )
      returning id, %3$s, app_period INTO new;
    ELSE
      INSERT INTO %1$I ( id, %3$s, app_period )
      values ( new.id, %4$s, new.app_period )
      returning id, %3$s, app_period INTO new;
    END IF;
  END IF;
  return NEW;
  end;
  $fn$ language plpgsql;

  drop trigger if exists %1$s_insert on %2$I;
  create trigger %1$s_insert
  instead of insert on %2$I
  for each row execute procedure %1$s_insert();
  ', _tbl, _view, _column_list, _new_column_list
);

execute format('
  create or replace function %1$s_update()
  returns trigger as $fn$
  DECLARE
    debug boolean;
  begin

  IF NEW.id is distinct from OLD.id THEN
    RAISE ''Updating for "id" column is prohibited'';
  END IF;

  -- TODO: Probably we should do nothing if OLD.* is same as NEW.*
  -- But at that case we should take care of side effect when
  -- current value interval is splitted by app_period:
  -- before: a, -inf..+inf
  -- after: a, -inf..X; a, X..Y; a, Y..+inf
  -- A: When OLD.* is same as NEW.*  && NEW.app_period <@ app_period()
  -- eg. AND we are not going to split

  select biconf( ''debug'' ) into debug;

  delete from %4$I
  where id = OLD.id and app_period = OLD.app_period;

  IF app_period() IS NOT NULL THEN
    -- We can not distinguish situation when NEN.app_period is provided
    -- and have same value as OLD.app_period
    -- I can try this solution: https://stackoverflow.com/a/8762116/4632019
    IF NEW.app_period <> OLD.app_period THEN
      -- If NEW.app_period is outside of app_period(), then:
      IF NOT NEW.app_period && app_period() THEN
      -- Should we prohibit this update or just warn?
        RAISE NOTICE ''New range "%%" is outside of current app_period(): %%'',
          NEW.app_period, app_period();
      END IF;
    ELSE
      -- User does not provide NEW.app_period thus we shrink OLD.app_period to
      -- the current app_period()
      new.app_period =  OLD.app_period * app_period();
    END IF;
  END IF;



  if debug then
    raise notice ''NEW: %%'', ( new.id, %3$s, new.app_period );
  end if;

  INSERT INTO %1$I (id, %2$s, app_period )
  values ( new.id, %3$s, new.app_period );


  return NEW;
  end;
  $fn$ language plpgsql;

  drop trigger if exists %1$s_update on %4$I;
  create trigger %1$s_update
  instead of update on %4$I
  for each row execute procedure %1$s_update();
', _tbl, _column_list, _new_column_list, _view
);

execute format( '
  create or replace function %1$s_delete_0()
  returns trigger as $fn$
  begin
  raise NOTICE ''DEL0: %%'', OLD;
  RETURN OLD;
  END;
  $fn$ language plpgsql;'
  , _tbl
);

execute format( '
  create or replace function %1$s_delete()
  returns trigger as $fn$
  DECLARE
    r record;
    target_period tstzrange;
    debug boolean;
  begin

  select biconf( ''debug'' ) into debug;

  %4$s

  if debug then
    raise notice ''app_period(): %%'', biconf( ''app_period'' );
  end if;

  %5$s

  return OLD;
  end;
  $fn$ language plpgsql;

  drop trigger if exists %1$s_delete on %2$I;
  create trigger %1$s_delete
  instead of delete on %2$I
  for each row execute procedure %1$s_delete();

  -- create trigger %1$s_delete_0
  -- instead of delete on %2$I
  -- for each row execute procedure %1$s_delete_0();
', _tbl, _view, _column_list,
deactivate_sql( _tbl ), splitting_sql( _tbl, _view, _column_list )
);

END $$ language plpgsql;

-- create table

create table test (
  id SERIAL,
  name text
);

SELECT make_bi_temporal( 'test' );


make_bi_temporal





-- ajust environment


set my.sys_time to '2018-08-14';
select app_period( '2018-08-14', null );
SELECT set_config( 'my.debug', 'true', true );

select sys_time();
select app_time();
select app_period();



app_period


["2018-08-14 00:00:00+01",)




set_config


true




sys_time


2018-08-14 00:00:00+01




app_time


null




app_period


["2018-08-14 00:00:00+01",)


-- fill data

insert into test ( name ) values ( 'a' ), ( 'b' );
select * from test;
2 rows affected



id
name
app_period


1
a
["2018-08-14 00:00:00+01",)

2
b
["2018-08-14 00:00:00+01",)


-- Test that app_period has effect. No data at 2015--2017 period
select app_period( '2015-01-01', '2018-01-01' );
select * from test;

-- Test that app_period has effect. Data exists at 2019--2020 period
select app_period( '2019-01-01', '2020-01-01' );
select * from test;


app_period


["2015-01-01 00:00:00+00","2018-01-01 00:00:00+00")




app_period


["2019-01-01 00:00:00+00","2020-01-01 00:00:00+00")




id
name
app_period


1
a
["2018-08-14 00:00:00+01",)

2
b
["2018-08-14 00:00:00+01",)






-- LOOK AT HERE ;-)






begin;
set my.sys_time to '2018-08-15';

-- Here we set CURRENT period with which we will work:
select app_period( '2018-08-20', '2018-08-25' );
select app_period(); 

-- delete data from period above. 
-- As result we should get data from deleted period and not whole. 
-- Look for !!!!!!!!!!! comment at splitting_sql function
with test_return_data as (
   -- Thus we do not affect anything outside of target period (see SELECT at next section)
   delete from test where name = 'a'
   returning *
)
-- Here I expect that DELETE returns data which is deleted and not whole period 
-- stored at original record
-- EXPECTED app_period: ["2018-08-20", "2018-08-25")
-- ACTUAL   app_period: ["2018-08-14", )
select * from test_return_data;
commit;


app_period


["2018-08-20 00:00:00+01","2018-08-25 00:00:00+01")




app_period


["2018-08-20 00:00:00+01","2018-08-25 00:00:00+01")




id
name
app_period


1
a
["2018-08-14 00:00:00+01",)


-- NOTICE: that 'a'  from tstzrange( '2018-08-20', '2018-08-25' ) period does not exists
-- it was is deleted
select app_period( null ); -- display all data at all periods
select * from test;


app_period


null




id
name
app_period


2
b
["2018-08-14 00:00:00+01",)

1
a
["2018-08-14 00:00:00+01","2018-08-20 00:00:00+01")

1
a
["2018-08-25 00:00:00+01",)


-- Here you can see that original row was deactivated: sid 1. 
-- It was active from 2018-08-14 till 2018-08-15
-- and when we delete data from 20 till 25 we get two rows: sid 3 4

-- From that we can see that 'a' still exists!
-- from 2018-08-14 till 2018-08-20 and from 2018-08-25 till +infinity
-- But DELETE RETURNTING * have said to us that: app_period: ["2018-08-14", ) was deleted
-- This is not true. 
-- So this is **important** to allow triggers to modify output data too.

select * from test_bt;



id
name
sid
app_period
sys_period


2
b
2
["2018-08-14 00:00:00+01",)
["2018-08-14 00:00:00+01",)

1
a
1
["2018-08-14 00:00:00+01",)
["2018-08-14 00:00:00+01","2018-08-15 00:00:00+01")

1
a
3
["2018-08-14 00:00:00+01","2018-08-20 00:00:00+01")
["2018-08-15 00:00:00+01",)

1
a
4
["2018-08-25 00:00:00+01",)
["2018-08-15 00:00:00+01",)


-- If we compare behavior of DELETE with UPDATE. 
-- We can see that UPDATE is allowed to modify outgoing data

select app_period( '2018-08-20', '2018-08-25' );

with test_return_data as (
   -- Thus we do not affect anything outside of target period (see SELECT at next section)
   update test set name = 'x' where name = 'b'
   returning *
)
-- Here I expect that DELETE returns data which is deleted and not whole period 
-- stored at original record
-- EXPECTED app_period: ["2018-08-20", "2018-08-25")
-- ACTUAL   app_period: ["2018-08-20", "2018-08-25")
select * from test_return_data;


app_period


["2018-08-20 00:00:00+01","2018-08-25 00:00:00+01")




id
name
app_period


2
x
["2018-08-20 00:00:00+01","2018-08-25 00:00:00+01")


select app_period( null ); -- display all data at all periods
select * from test;


app_period


null




id
name
app_period


1
a
["2018-08-14 00:00:00+01","2018-08-20 00:00:00+01")

1
a
["2018-08-25 00:00:00+01",)

2
b
["2018-08-14 00:00:00+01","2018-08-20 00:00:00+01")

2
b
["2018-08-25 00:00:00+01",)

2
x
["2018-08-20 00:00:00+01","2018-08-25 00:00:00+01")
id	name	app_period
2	b	["2018-08-14 00:00:00+01",)
1	a	["2018-08-14 00:00:00+01","2018-08-20 00:00:00+01")
1	a	["2018-08-25 00:00:00+01",)