review tardis https://github.com/akvo/akvo-lumen/issues/1225

1225.org

inject plpgsql

CREATE OR REPLACE FUNCTION extract_cols_as_char (tablename CHARACTER, my_prefix CHARACTER DEFAULT '')
    RETURNS varchar
AS $func$
DECLARE
    i text;
    str varchar;
BEGIN
    str := '';
    FOR i IN
    SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
        table_name = tablename
        AND table_schema = 'public'
    LOOP
        str := str || ',' || my_prefix || i;
    END LOOP;
    RETURN SUBSTRING(str FROM 2);
END
$func$
LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION history.log_change() RETURNS trigger AS $_$
    DECLARE
      c refcursor;
      tt tstzrange;
      insert_into_fields text;
      select_from_fields text;

    BEGIN
        insert_into_fields := extract_cols_as_char(TG_TABLE_NAME::text);

        select_from_fields := extract_cols_as_char(TG_TABLE_NAME::text, '$1.');

        IF TG_OP = 'INSERT' THEN
            EXECUTE 'INSERT INTO history.' || TG_TABLE_NAME || ' (_validrange, ' || insert_into_fields || ' )'
  	    ' SELECT tstzrange(now(), $$infinity$$, $$[)$$), ' || select_from_fields || '' USING NEW;
            RETURN NEW;
        ELSIF TG_OP = 'UPDATE' THEN
            OPEN c FOR EXECUTE 'SELECT _validrange FROM history.' || TG_TABLE_NAME ||
              ' WHERE id = $1 ORDER BY _validrange DESC LIMIT 1 FOR UPDATE'
              USING NEW.id;
            FETCH FROM c INTO tt;

            IF isempty(tstzrange(lower(tt), now(), $$[)$$)) THEN
               EXECUTE 'DELETE FROM history.' || TG_TABLE_NAME ||
                  ' WHERE CURRENT OF ' || quote_ident(c::text);
            ELSE
                EXECUTE 'UPDATE history.' || TG_TABLE_NAME || ' SET _validrange = tstzrange($1, now(), $$[)$$)' ||
                  ' WHERE CURRENT OF ' || quote_ident(c::text) USING lower(tt);
            END IF;

            EXECUTE 'INSERT INTO history.' || TG_TABLE_NAME || ' (_validrange, ' || insert_into_fields || ' )'
  	    ' SELECT tstzrange(now(), $$infinity$$, $$[)$$), ' || select_from_fields || '' USING NEW;
            RETURN NEW;

            RETURN NEW;
        ELSIF TG_OP = 'DELETE' THEN

          OPEN c FOR EXECUTE 'SELECT _validrange FROM history.' || TG_TABLE_NAME ||
            ' WHERE id = $1 ORDER BY _validrange DESC LIMIT 1 FOR UPDATE' USING OLD.id;

          FETCH FROM c into tt;

          IF isempty(tstzrange(lower(tt), now(), $$[)$$)) THEN
            EXECUTE 'DELETE FROM history.' || TG_TABLE_NAME ||
              ' WHERE CURRENT OF ' || quote_ident(c::text);
            RETURN OLD;
          END IF;

          EXECUTE 'UPDATE history.' || TG_TABLE_NAME ||
            ' SET _validrange = tstzrange($1, now(), $$[)$$) WHERE CURRENT OF ' ||
            quote_ident(c::text) USING lower(tt);

          RETURN OLD;
        END IF;
        RETURN NULL;
    END;
$_$ LANGUAGE plpgsql;
-- ;;

CREATE FUNCTION
CREATE FUNCTION

tenant test

step1 check initial data

select * from data_source;

id	spec	created	modified

step2 - add more data

INSERT INTO public.data_source (id, spec) VALUES (gen_random_uuid(), '{}');
select * from data_source;

INSERT 0 1
id	spec	created	modified
e836638c-2766-4718-8535-9b5c535418d8	{}	2018-05-23 07:07:56.629911+00	2018-05-23 07:07:56.629911+00
33aceb26-90b7-43ca-88b5-0d1f79a29223	{}	2018-05-23 07:07:58.34346+00	2018-05-23 07:07:58.34346+00

step3 alter-tables

ALTER TABLE public.data_source ADD column test bool;
ALTER TABLE history.data_source ADD column test bool;

ALTER TABLE
ALTER TABLE

step4 try add more data

INSERT INTO public.data_source (id, spec, test) VALUES (gen_random_uuid(), '{}', true);
select * from public.data_source;

INSERT 0 1
id	spec	created	modified	test
e836638c-2766-4718-8535-9b5c535418d8	{}	2018-05-23 07:07:56.629911+00	2018-05-23 07:07:56.629911+00
33aceb26-90b7-43ca-88b5-0d1f79a29223	{}	2018-05-23 07:07:58.34346+00	2018-05-23 07:07:58.34346+00
2be95656-36f4-4d95-a3ed-fd189a389414	{}	2018-05-23 07:08:01.460125+00	2018-05-23 07:08:01.460125+00	t

step5 check results

public

select * from  public.data_source ;

id	spec	created	modified	test
33aceb26-90b7-43ca-88b5-0d1f79a29223	{}	2018-05-23 07:07:58.34346+00	2018-05-23 07:07:58.34346+00
2be95656-36f4-4d95-a3ed-fd189a389414	{}	2018-05-23 07:08:01.460125+00	2018-05-23 07:08:01.460125+00	t
e836638c-2766-4718-8535-9b5c535418d8	{}	2018-05-23 07:07:56.629911+00	2018-05-23 07:08:15.860755+00	f

history

select * from  history.data_source ;

id	spec	created	modified	_validrange	test
e836638c-2766-4718-8535-9b5c535418d8	{}	2018-05-23 07:07:56.629911+00	2018-05-23 07:07:56.629911+00	[“2018-05-23 07:07:56.629911+00”,infinity)
33aceb26-90b7-43ca-88b5-0d1f79a29223	{}	2018-05-23 07:07:58.34346+00	2018-05-23 07:07:58.34346+00	[“2018-05-23 07:07:58.34346+00”,infinity)
2be95656-36f4-4d95-a3ed-fd189a389414	{}	2018-05-23 07:08:01.460125+00	2018-05-23 07:08:01.460125+00	[“2018-05-23 07:08:01.460125+00”,infinity)	t

try update existent value

WITH subquery AS (SELECT id from public.data_source LIMIT 1)
UPDATE  public.data_source
SET test=false
FROM subquery
WHERE public.data_source.id=subquery.id;

UPDATE 1

clean data

(in-ns 'dev)
(do (rollback) (migrate))