Trigger function for schema not passed in schema.json
ravin-accelq opened this issue · 0 comments
ravin-accelq commented
PGSync version: 2.5
Postgres version: 10
Elasticsearch version: 8.6
Redis version: 6.5
Python version: 3.6.9
Problem Description:
It seems it tries to create a trigger function for schema not defined in schema.json. template_tenant_schema is no where defined in our schema.json file.
Error Message (if any):
CREATE OR REPLACE FUNCTION template_tenant_schema.table_notify() RETURNS TRIGGER AS $$
DECLARE
channel TEXT;
old_row JSON;
new_row JSON;
notification JSON;
xmin BIGINT;
_indices TEXT [];
_primary_keys TEXT [];
_foreign_keys TEXT [];
BEGIN
-- database is also the channel name.
channel := CURRENT_DATABASE();
IF TG_OP = 'DELETE' THEN
SELECT primary_keys, indices
INTO _primary_keys, _indices
FROM template_tenant_schema._view
WHERE table_name = TG_TABLE_NAME;
old_row = ROW_TO_JSON(OLD);
old_row := (
SELECT JSONB_OBJECT_AGG(key, value)
FROM JSON_EACH(old_row)
WHERE key = ANY(_primary_keys)
);
xmin := OLD.xmin;
ELSE
IF TG_OP <> 'TRUNCATE' THEN
SELECT primary_keys, foreign_keys, indices
INTO _primary_keys, _foreign_keys, _indices
FROM template_tenant_schema._view
WHERE table_name = TG_TABLE_NAME;
new_row = ROW_TO_JSON(NEW);
new_row := (
SELECT JSONB_OBJECT_AGG(key, value)
FROM JSON_EACH(new_row)
WHERE key = ANY(_primary_keys || _foreign_keys)
);
IF TG_OP = 'UPDATE' THEN
old_row = ROW_TO_JSON(OLD);
old_row := (
SELECT JSONB_OBJECT_AGG(key, value)
FROM JSON_EACH(old_row)
WHERE key = ANY(_primary_keys || _foreign_keys)
);
END IF;
xmin := NEW.xmin;
END IF;
END IF;
-- construct the notification as a JSON object.
notification = JSON_BUILD_OBJECT(
'xmin', xmin,
'new', new_row,
'old', old_row,
'indices', _indices,
'tg_op', TG_OP,
'table', TG_TABLE_NAME,
'schema', TG_TABLE_SCHEMA
);
-- Notify/Listen updates occur asynchronously,
-- so this doesn't block the Postgres trigger procedure.
PERFORM PG_NOTIFY(channel, notification::TEXT);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;