tcn

Triggered change notifications

tcn : Triggered change notifications

Overview

ID	Extension	Package	Version	Category	License	Language
4920	tcn	tcn	`1.0`	FUNC	PostgreSQL	C

Attribute	Has Binary	Has Library	Need Load	Has DDL	Relocatable	Trusted
--s-dt-	No	Yes	No	Yes	no	yes

Relationships
See Also	pgmq pgmb ulak redis pgq pgmqtt pg_durable redis_fdw fsm_core refint

Packages

PG18	PG17	PG16	PG15	PG14
1.0	1.0	1.0	1.0	1.0

This is a built-in contrib extension ship with the PostgreSQL kernel

Install

Create this extension with:

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');