PostgreSQL selected row tracking

pgst_install.sql

BEGIN;

CREATE OR REPLACE FUNCTION pgst_suffix_table_name(table_name TEXT, suffix TEXT) RETURNS TEXT AS
$$
    SELECT TEXT (table_name || '_pgst_' || suffix);
$$
LANGUAGE sql IMMUTABLE;


CREATE OR REPLACE FUNCTION pgst_start_for_table(table_name TEXT) RETURNS void AS
$outer$
DECLARE
	data_table_name TEXT;
	track_table_name TEXT;
	track_table_name_func TEXT;
BEGIN
	data_table_name := pgst_suffix_table_name(table_name, 'data');
	track_table_name := pgst_suffix_table_name(table_name, 'track');
	track_table_name_func := track_table_name || '_func';

	EXECUTE 'ALTER TABLE ' || quote_ident(table_name) || ' RENAME TO ' || quote_ident(data_table_name);

	-- EXECUTE 'CREATE TABLE ' || quote_ident(track_table_name) || '(tracktime TIMESTAMP DEFAULT now(), table_row ' || quote_ident(data_table_name) || ')';
	EXECUTE 'CREATE TABLE ' || quote_ident(track_table_name) || '(LIKE ' || quote_ident(data_table_name) || ' INCLUDING ALL)';

	EXECUTE '
		CREATE OR REPLACE FUNCTION ' || quote_ident(track_table_name_func) || '(table_row ' || quote_ident(data_table_name) || ') RETURNS integer AS
		$inner$
		BEGIN
			BEGIN
				INSERT INTO ' || quote_ident(track_table_name) || ' VALUES (table_row.*);
			EXCEPTION WHEN unique_violation THEN
				-- Do nothing
			END;
			RETURN 1;
		END;
		$inner$
		LANGUAGE plpgsql VOLATILE COST 10000;
	';

	EXECUTE format('CREATE VIEW %1$I AS SELECT %2$I.*, %3$I(%2$I.*) FROM %2$I', table_name, data_table_name, track_table_name_func);
END;
$outer$ LANGUAGE plpgsql VOLATILE;


CREATE OR REPLACE FUNCTION pgst_stop_for_table(table_name TEXT, delete_track_tables BOOLEAN) RETURNS void AS
$$
DECLARE
	data_table_name TEXT;
	track_table_name TEXT;
	track_table_name_func TEXT;
BEGIN
	data_table_name := pgst_suffix_table_name(table_name, 'data');
	track_table_name := pgst_suffix_table_name(table_name, 'track');
	track_table_name_func := track_table_name || '_func';

	EXECUTE 'DROP VIEW IF EXISTS ' || quote_ident(table_name);

	EXECUTE 'DROP FUNCTION IF EXISTS ' || quote_ident(track_table_name_func) || '(table_row ' || quote_ident(data_table_name) || ')';

    EXECUTE 'ALTER TABLE IF EXISTS ' || quote_ident(data_table_name) || ' RENAME TO ' || quote_ident(table_name);

    IF delete_track_tables THEN
        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(track_table_name);
    END IF;
END;
$$ LANGUAGE plpgsql VOLATILE;


CREATE OR REPLACE FUNCTION pgst_start_for_all_tables() RETURNS void AS
$$
DECLARE
	table_names CURSOR IS SELECT table_name AS name
		FROM information_schema.tables
		WHERE table_schema = 'public'
		AND table_type = 'BASE TABLE'
        	AND table_name NOT IN ('content_download', 'download_pack',  'content_download_pack', 'entitlement')
		AND table_name NOT LIKE pgst_suffix_table_name('%', 'track');
BEGIN
	FOR tbl IN table_names LOOP
        PERFORM pgst_start_for_table(tbl.name);
	END LOOP;
END;
$$ LANGUAGE plpgsql VOLATILE;


CREATE OR REPLACE FUNCTION pgst_stop_for_all_tables(delete_track_tables BOOLEAN) RETURNS void AS
$$
DECLARE
	view_names CURSOR IS SELECT table_name AS name
		FROM information_schema.tables
		WHERE table_schema = 'public'
		AND table_type = 'VIEW';
BEGIN
	FOR tbl IN view_names LOOP
        PERFORM pgst_stop_for_table(tbl.name, delete_track_tables);
	END LOOP;
END;
$$ LANGUAGE plpgsql VOLATILE;


CREATE OR REPLACE FUNCTION pgst_swap_tracked_tables() RETURNS void AS
$$
DECLARE
	track_table_names CURSOR IS SELECT table_name AS name
		FROM information_schema.tables
		WHERE table_schema = 'public'
		AND table_name LIKE pgst_suffix_table_name('%', 'track');
    regular_table_name TEXT;
    tmp_table_name TEXT;
BEGIN
	FOR tbl IN track_table_names LOOP
        regular_table_name := REPLACE(tbl.name, pgst_suffix_table_name('', 'track'), '');
        tmp_table_name := pgst_suffix_table_name(regular_table_name, 'tmp');
        EXECUTE 'ALTER TABLE IF EXISTS ' || quote_ident(regular_table_name) || ' RENAME TO ' || quote_ident(tmp_table_name);
        EXECUTE 'ALTER TABLE IF EXISTS ' || quote_ident(tbl.name) || ' RENAME TO ' || quote_ident(regular_table_name);
        EXECUTE 'ALTER TABLE IF EXISTS ' || quote_ident(tmp_table_name) || ' RENAME TO ' || quote_ident(tbl.name);
	END LOOP;
END;
$$ LANGUAGE plpgsql VOLATILE;

COMMIT;

pgst_uninstall.sql

BEGIN;

DROP FUNCTION IF EXISTS pgst_suffix_table_name(TEXT, TEXT);
DROP FUNCTION IF EXISTS pgst_start_for_table(TEXT);
DROP FUNCTION IF EXISTS pgst_stop_for_table(TEXT, BOOLEAN);
DROP FUNCTION IF EXISTS pgst_start_for_all_tables();
DROP FUNCTION IF EXISTS pgst_stop_for_all_tables(BOOLEAN);
DROP FUNCTION IF EXISTS pgst_swap_tracked_tables();

COMMIT;

temp.sql

-- pg_dump --data-only --username=adobestock --host=as-db --dbname=adobestock -t "*_pgst_track" > foo.sql

-- pg_dump --column-inserts -T backoffice_log -T '*_old' -T '*_history' -T '*_track' --data-only --username=adobestock --host=as-db --dbname=adobestock  | grep -v 'pg_catalog.setval' >tests/behat/foo.sql 

DELETE FROM content_download WHERE member_id NOT IN (SELECT member_id FROM member);
DELETE FROM entitlement_member WHERE member_id NOT IN (SELECT member_id FROM member);
DELETE FROM entitlement WHERE (member_id IS NULL OR member_id NOT IN (SELECT member_id FROM member)) AND entitlement_id NOT IN (SELECT entitlement_id FROM entitlement_member);
DELETE FROM download_pack WHERE (member_id IS NULL OR member_id NOT IN (SELECT member_id FROM member)) AND entitlement_id NOT IN (SELECT entitlement_id FROM entitlement);
DELETE FROM content_download_pack WHERE content_download_id NOT IN (SELECT content_download_id FROM content_download);

SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables where relname not like '%_track' and relname not like '%_history' ORDER BY n_live_tup DESC;

--- download_pack
select distinct on (sao, entitlement_id, properties, product_key, quota, state, download_pack_type_id, license_type_id, member_id) *  FROM download_pack order by sao, entitlement_id, properties, product_key, quota, state, download_pack_type_id, license_type_id, expiration desc

create table download_pack_tmp (like download_pack including all);
insert into download_pack_tmp select distinct on (sao, entitlement_id, properties, product_key, quota, state, download_pack_type_id, license_type_id, member_id) *  FROM download_pack order by sao, entitlement_id, properties,
product_key, quota, state, download_pack_type_id, license_type_id, member_id, expiration desc;
alter table download_pack rename to download_pack_old;
alter table download_pack_tmp rename to download_pack;
---

--- content_download
select distinct on (member_id, entitlement_id, content_id, content_member_id, license_id, downloaded is not null, fotolia_id32, reference_id is not null, properties) *  FROM content_download order by member_id, entitlement_id, content_id, content_member_id, license_id, downloaded is not null, fotolia_id32, reference_id is not null, properties, expire desc;

create table content_download_tmp (like content_download including all);
insert into content_download_tmp select distinct on (member_id, entitlement_id, content_id, content_member_id, license_id, downloaded is not null, fotolia_id32, reference_id is not null, properties) *  FROM content_download order by member_id, entitlement_id, content_id, content_member_id, license_id, downloaded is not null, fotolia_id32, reference_id is not null, properties, expire desc;
alter table content_download rename to content_download_old;
alter table content_download_tmp rename to content_download;
---