tcn

Triggered change notifications

https://www.postgresql.org/docs/current/tcn.html

Overview

Package	Version	Category	License	Language
`tcn`	`1.0`	FUNC	PostgreSQL	C

ID	Extension	Bin	Lib	Load	Create	Trust	Reloc	Schema
4920	`tcn`	No	Yes	No	Yes	Yes	No	-

Related	`pgmq` `pgmb` `ulak` `redis` `pgq` `pgmqtt` `pg_durable` `redis_fdw` `fsm_core` `refint`

Version

PG18	PG17	PG16	PG15	PG14
1.0	1.0	1.0	1.0	1.0

Install

Note: This is a built-in contrib extension of PostgreSQL

CREATE EXTENSION tcn;

Usage

tcn: triggered change notifications via LISTEN/NOTIFY

Provides a trigger function that sends NOTIFY events with information about changed rows, enabling asynchronous change tracking.

CREATE EXTENSION tcn;

Trigger Function

Function	Description
`triggered_change_notification()`	Send NOTIFY on row changes with primary key info

Optional parameter: custom channel name (defaults to tcn).

Notification Payload Format

"table_name",operation,"column"='value',"column"='value'

Operations: I (INSERT), U (UPDATE), D (DELETE).

Examples

CREATE TABLE tcndata (
  a int NOT NULL,
  b date NOT NULL,
  c text,
  PRIMARY KEY (a, b)
);

-- Attach the trigger
CREATE TRIGGER tcndata_tcn
  AFTER INSERT OR UPDATE OR DELETE ON tcndata
  FOR EACH ROW
  EXECUTE FUNCTION triggered_change_notification();

-- Listen for notifications
LISTEN tcn;

-- Changes trigger notifications:
INSERT INTO tcndata VALUES (1, '2024-01-01', 'test');
-- Notification: "tcndata",I,"a"='1',"b"='2024-01-01'

UPDATE tcndata SET c = 'updated' WHERE a = 1;
-- Notification: "tcndata",U,"a"='1',"b"='2024-01-01'

DELETE FROM tcndata WHERE a = 1;
-- Notification: "tcndata",D,"a"='1',"b"='2024-01-01'

-- Use a custom channel name
CREATE TRIGGER my_trigger
  AFTER INSERT OR UPDATE OR DELETE ON my_table
  FOR EACH ROW
  EXECUTE FUNCTION triggered_change_notification('my_channel');

tcn