Postgres Update Trigger Bug
Local postgres version postgres (PostgreSQL) 11.4 (Debian 11.4-1.pgdg90+1). AWS RDS version 10.4
In the node application:
Error executing command "persistence.s2portal.create".
ERROR [23:49:23:253] ├── app : Error (E_UNKNOWN) :: Encountered an unexpected error
error: type of parameter 15 (s2portal) does not match that when preparing the plan (record
)
at Connection.parseE (/usr/src/app/node_modules/sails-postgresql/node_modules/pg/lib/conne
ction.js:539:11)
at Connection.parseMessage (/usr/src/app/node_modules/sails-postgresql/node_modules/pg/lib
/connection.js:366:17)
at TLSSocket.<anonymous> (/usr/src/app/node_modules/sails-postgresql/node_modules/pg/lib/c
onnection.js:105:22)
at TLSSocket.emit (events.js:198:13)
at TLSSocket.EventEmitter.emit (domain.js:448:20)
at addChunk (_stream_readable.js:287:12)
at readableAddChunk (_stream_readable.js:268:11)
at TLSSocket.Readable.push (_stream_readable.js:223:10)
at TLSWrap.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
---------------------------------------------
at Client.connect (/usr/src/app/node_modules/sails-postgresql/node_modules/pg/lib/client.j
s:167:7)
at Object.create (/usr/src/app/node_modules/sails-postgresql/node_modules/pg/lib/pool.js:3
0:18)
at createResource (/usr/src/app/node_modules/sails-postgresql/node_modules/pg/node_modules
/generic-pool/lib/generic-pool.js:261:13)
at dispense (/usr/src/app/node_modules/sails-postgresql/node_modules/pg/node_modules/gener
ic-pool/lib/generic-pool.js:253:9)
at Object.me.acquire (/usr/src/app/node_modules/sails-postgresql/node_modules/pg/node_modu
les/generic-pool/lib/generic-pool.js:319:5)
at Object.pool.connect (/usr/src/app/node_modules/sails-postgresql/node_modules/pg/lib/poo
l.js:78:14)
at PG.connect (/usr/src/app/node_modules/sails-postgresql/node_modules/pg/lib/index.js:49:
8)
at spawnConnection (/usr/src/app/node_modules/sails-postgresql/lib/adapter.js:1394:8)
at Object.find (/usr/src/app/node_modules/sails-postgresql/lib/adapter.js:1074:7)
at module.exports.find (/usr/src/app/node_modules/waterline/lib/waterline/adapter/dql.js:1
19:13)
at module.exports.findOne (/usr/src/app/node_modules/waterline/lib/waterline/adapter/dql.j
s:162:10)
at module.exports._runOperation (/usr/src/app/node_modules/waterline/lib/waterline/query/f
inders/operations.js:411:29)
at module.exports.run (/usr/src/app/node_modules/waterline/lib/waterline/query/finders/ope
rations.js:70:8)
at child.findOne (/usr/src/app/node_modules/waterline/lib/waterline/query/finders/basic.js
:78:16)
at module.exports.Deferred.exec (/usr/src/app/node_modules/waterline/lib/waterline/query/d
eferred.js:485:16)
at module.exports.tryCatcher (/usr/src/app/node_modules/waterline/node_modules/bluebird/js
/release/util.js:11:23)
at module.exports.ret (eval at makeNodePromisifiedEval (/usr/src/app/node_modules/waterlin
e/node_modules/bluebird/js/release/promisify.js:184:12), <anonymous>:13:39)
at module.exports.Deferred.toPromise (/usr/src/app/node_modules/waterline/lib/waterline/qu
ery/deferred.js:494:61)
at module.exports.Deferred.then (/usr/src/app/node_modules/waterline/lib/waterline/query/d
eferred.js:505:15)
at process._tickCallback (internal/process/next_tick.js:68:7)
Details: error: type of parameter 15 (s2portal) does not match that when preparing the pl
an (record)
In the AWS RDS console:
ERROR: type of parameter 15 (s2portal) does not match that when preparing the plan (record)
CONTEXT: PL/pgSQL function notify_trigger() line 24 at assignment
STATEMENT: UPDATE "public"."s2portal" AS "s2portal" SET "device" = $1,
"updatedAt" = $2 WHERE LOWER("s2portal"."id") = $3 RETURNING *
ERROR: type of parameter 15 (record) does not match that when preparing the plan (s2portal)
CONTEXT: PL/pgSQL function notify_trigger() line 24 at assignment
STATEMENT: INSERT INTO "public"."s2portal" ("id", "uuid", "name", "description", "partitionKey", "host", "metadata", "partition", "floor", "identifier", "createdAt", "updatedAt") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) RETURNING *
The original trigger function is:
SET ROLE root;
CREATE OR REPLACE FUNCTION notify_trigger() RETURNS trigger AS $trigger$
DECLARE
rec RECORD;
dat RECORD;
payload TEXT;
BEGIN
-- Set record row depending on operation
CASE TG_OP
WHEN 'UPDATE' THEN
rec := NEW;
dat := OLD;
WHEN 'INSERT' THEN
rec := NEW;
SELECT null INTO dat;
WHEN 'DELETE' THEN
rec := OLD;
SELECT null INTO dat;
ELSE
RAISE EXCEPTION 'Unknown TG_OP: "%". Should not occur!', TG_OP;
END CASE;
-- Build the payload
payload := json_build_object('timestamp',CURRENT_TIMESTAMP,'action',LOWER(TG_OP),'schema',TG_TABLE_SCHEMA,'identity',TG_TABLE_NAME,'record',row_to_json(rec),'old',row_to_json(dat));
-- Notify the channel
PERFORM pg_notify('core_db_event',payload);
RETURN rec;
END;
$trigger$ LANGUAGE plpgsql;
-- Listen for changes in User
DROP TRIGGER IF EXISTS user_notify ON user;
CREATE TRIGGER user_notify AFTER INSERT OR UPDATE OR DELETE ON user
FOR EACH ROW EXECUTE PROCEDURE notify_trigger();
Issue seems to be solved by breaking the create trigger snippet.
DROP TRIGGER IF EXISTS user_notify ON user;
CREATE TRIGGER user_notify AFTER INSERT OR DELETE ON user
FOR EACH ROW EXECUTE PROCEDURE notify_trigger();
DROP TRIGGER IF EXISTS user_update_notify ON user;
CREATE TRIGGER user_update_notify AFTER UPDATE ON user
FOR EACH ROW EXECUTE PROCEDURE notify_trigger();