Mirror changes (new columns) made in the primary table to the _history table

[nelsonic]

We have created the basic SQL script that handles creating the _history table for each primary and a trigger for each table which mirrors the data on INSERT/UPDATE see: create.sql and #1 Now what we want is a trigger that

We could achieve this either by creating a Trigger on the INFORMATION_SCHEMA.COLUMNS table that listens for INSERT/UPDATE and runs an SQL function e.g:

Pseudocode:

CREATE TRIGGER db_name_table_change
AFTER INSERT OR UPDATE ON INFORMATION_SCHEMA.COLUMNS 
FOR EACH ROW EXECUTE PROCEDURE column_changed_trigger();

OR we can write an Elixir function that does the checking before the app starts.

Pseudocode

query = """
SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_catalog = 'database_bame'
"""

# loop through list of primary tables and corresponding `_history` table columns
# confirm that they are mirrored. If not, create the necessary columns in `_history`

@RobStallion what is your preferred approach? 💭 Should we continue on our SQL functions quest or write some Elixir today? 🌻

• [x] Loop through all columns in all tables for a given database
• [x] Check if column in primary table exists in _history table
• [x] If column does not exist, create it.

[nelsonic]

Working SQL Code: 🎉

ALTER TABLE ps_counters ADD COLUMN robs_column INTEGER;

DO $$
DECLARE
  row RECORD;
  db_name TEXT := 'hits_dev';
  history_table_name TEXT;
  history_column_name TEXT;
BEGIN
FOR row IN
  SELECT column_name, data_type, table_name, character_maximum_length
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_catalog = format('%I', db_name)
  AND table_schema = 'public'
  AND column_name NOT LIKE '_id'
  AND table_name not like 'schema_migrations'
  ORDER BY column_name ASC, table_name DESC -- history tables first
LOOP

  RAISE NOTICE 'column_name: %, table_name: %', row.column_name, row.table_name;

  IF row.table_name like '%_history' THEN
    history_table_name := row.table_name;
    history_column_name := row.column_name;

  ELSE
    IF history_table_name LIKE (row.table_name || '_history') THEN
      RAISE NOTICE 'table names match';
      -- if column names and table names of the history table and current
      -- table match then we do not need to do anything
      IF history_column_name NOT LIKE row.column_name THEN
        RAISE NOTICE 'we need to insert column into history table: %', row.column_name;
          -- execute create column in history table

      END IF;

    ELSE
      RAISE NOTICE 'column exists in primary but not history. column_name: %, table: %, history_table_name: %',
        row.column_name, row.table_name, history_table_name;
        -- ADD the COLUMN to the _history table:
        EXECUTE format('ALTER TABLE %I ADD COLUMN %I %s;',
            (row.table_name || '_history'), row.column_name, row.data_type);

    END IF;
  END IF;

END LOOP;
END; $$

[nelsonic]

Re-worked by @RobStallion:

DO $$
DECLARE
  row RECORD;
  db_name TEXT := 'hits_dev';
  history_table_name TEXT;
  history_column_name TEXT;
  hist_tbl TEXT;
  table_names_match BOOLEAN;
  column_names_match BOOLEAN;

BEGIN
  FOR row IN
    SELECT column_name, data_type, table_name, character_maximum_length
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_catalog = format('%I', db_name)
    AND table_schema = 'public'
    AND column_name NOT LIKE '_id'
    AND table_name not like 'schema_migrations'
    ORDER BY column_name ASC, table_name DESC -- history tables first
  LOOP

    RAISE NOTICE 'column_name: %, table_name: %', row.column_name, row.table_name;

    -- if we have a history table, update the variables
    IF row.table_name LIKE '%_history' THEN
      history_table_name := row.table_name;
      history_column_name := row.column_name;

    -- Route for original tables
    ELSE
      hist_tbl := (row.table_name || '_history');
      table_names_match := history_table_name = hist_tbl;
      column_names_match := history_column_name = row.column_name;

      IF NOT table_names_match OR (table_names_match AND NOT column_names_match) THEN

        RAISE NOTICE '---> Original exists. Creating column in history table';
        EXECUTE format('ALTER TABLE %I ADD COLUMN %I %s;', hist_tbl, row.column_name, row.data_type);
      END IF;
    END IF;
  END LOOP;
END; $$

[RobStallion]

Converted the above do block into a function and called it in the create_history function...

CREATE OR REPLACE FUNCTION apply_alterations(db_name TEXT)
RETURNS BOOLEAN AS $$
DECLARE
  row RECORD;
  history_table_name TEXT;
  history_column_name TEXT;
  hist_tbl TEXT;
  table_names_match BOOLEAN;
  column_names_match BOOLEAN;

BEGIN
  FOR row IN
    SELECT column_name, data_type, table_name, character_maximum_length
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_catalog = format('%I', db_name)
    AND table_schema = 'public'
    AND column_name NOT LIKE '_id'
    AND table_name not like 'schema_migrations'
    ORDER BY column_name ASC, table_name DESC -- history tables first
  LOOP

    RAISE NOTICE 'column_name: %, table_name: %', row.column_name, row.table_name;

    -- if we have a history table, update the variables
    IF row.table_name LIKE '%_history' THEN
      history_table_name := row.table_name;
      history_column_name := row.column_name;

    ELSE
      hist_tbl := (row.table_name || '_history');
      table_names_match := history_table_name = hist_tbl;
      column_names_match := history_column_name = row.column_name;
    -- Route for original tables

      IF NOT table_names_match OR (table_names_match AND NOT column_names_match) THEN

        RAISE NOTICE '---> Original exists. Creating column in history table';
        EXECUTE format('ALTER TABLE %I ADD COLUMN %I %s;', hist_tbl, row.column_name, row.data_type);
      END IF;
    END IF;
  END LOOP;

  RETURN true;  -- boolean!
END; $$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION create_history(db_name TEXT)
RETURNS boolean

AS $func$

DECLARE
  tables RECORD;
  table_name TEXT;
  new_table TEXT;
  trigger_name TEXT;

BEGIN

FOR tables IN
  (SELECT t.table_name
  FROM information_schema.tables t
  WHERE t.table_catalog = format('%I', db_name)
  AND t.table_schema = 'public'
  AND t.table_name not like '%_history'
  AND t.table_name not like 'schema_migrations'
  )
LOOP
  table_name := tables.table_name;
  new_table := table_name || '_history';
  trigger_name := new_table || '_trigger';

  EXECUTE format('CREATE TABLE IF NOT EXISTS %I
      (_id serial PRIMARY KEY, like %I)', new_table, table_name);

  --  execute the mirror tables function here:
  PERFORM apply_alterations(db_name);

  EXECUTE format('CREATE TRIGGER %I
    AFTER INSERT OR UPDATE ON %I FOR EACH ROW
    EXECUTE PROCEDURE history_trigger(%I, %I)',
      trigger_name, table_name, db_name, table_name);

  END LOOP;

  RETURN true;  -- boolean!
END; $func$
LANGUAGE plpgsql;

[RobStallion]

The function works as expected but when we call it more than once, we get the following error...

ERROR:  trigger "addresses_history_trigger" for relation "addresses" already exists
CONTEXT:  SQL statement "CREATE TRIGGER addresses_history_trigger
    AFTER INSERT OR UPDATE ON addresses FOR EACH ROW
    EXECUTE PROCEDURE history_trigger(app_dev, addresses)"
PL/pgSQL function create_history(text) line 30 at EXECUTE

This appears to be caused because the trigger is already created.

There is not a create or replace trigger syntax so we will need to implement this behaviour ourselves. This SO question looks like it has the exact thing we need. Going to try this next

[nelsonic]

@RobStallion solved the Error in https://github.com/dwyl/postgres-history-spike/blob/55d8c22347adce14ca22dfe208f1670e59b67995/sql/create.sql#L31

The final code for this issue is: https://github.com/dwyl/postgres-history-spike/blob/55d8c22347adce14ca22dfe208f1670e59b67995/sql/apply_alterations.sql#L1-L45

Closing as this is done. 🎉