Postgres Triggers

Overview

Orvanta enables database-driven automation by connecting to PostgreSQL databases and triggering scripts or flows in response to transactions (INSERT, UPDATE, DELETE) on specified tables. The feature uses PostgreSQL’s logical replication streaming protocol for efficient, low-latency triggering. Postgres triggers are not available on Orvanta Cloud.

Logical replication fundamentals

The system operates through two key mechanisms:

Publications define which changes should be replicated by selecting specific tables or schemas to track.

Replication slots retain changes from a publication until successfully delivered to subscribers, guaranteeing reliability and preventing data loss.

Prerequisites and configuration

WAL level setting

PostgreSQL requires the Write-Ahead Log level set to 'logical'. This can be configured via SQL:

ALTER SYSTEM SET wal_level = 'logical';

Or by editing postgresql.conf:

wal_level = logical

Both approaches require a database restart. Verification is possible through:

SHOW wal_level;

Enabling logical replication typically increases WAL data by 10-30%, depending on write activity volume.

Additional parameters

max_wal_senders determines concurrent walsender processes (default: 10). Each active trigger consumes one walsender process, so triggering limits depend on this setting.

max_replication_slots controls available replication slots. The maximum number of triggers creatable equals the slots available.

Setup and configuration

Creating triggers

Users navigate to the Postgres triggers page and establish a new trigger, requiring either creation of a new Postgres resource (with hostname, port, database name, credentials, and optional SSL settings) or reuse of an existing resource.

Tracking options

All tables monitors transactions across the entire database.

Specific schemas tracks all tables within selected schemas, including future additions.

Specific tables monitors only designated tables, with optional column selection.

Configuration constraints

Valid combinations include schema-level tracking or specific table tracking without column selection. Combining schema-level tracking with specific table tracking that includes column selection is invalid.

Advanced features

Row filtering

WHERE conditions filter rows during replication with restrictions: conditions cannot contain user-defined functions, operators, types, or system column references. For UPDATE/DELETE operations, conditions may reference only replica identity columns.

Column selection

When tracking specific tables, selecting columns reduces transmitted data. For UPDATE/DELETE transactions, selected columns must be part of the table’s replica identity. Omitting replica identity columns causes operation failures.

Publications and replication slots management

The Advanced section allows creating custom publications, selecting existing publications, creating custom replication slots, or selecting existing slots. Users can also delete slots and publications as needed.

Script generation

Orvanta auto-generates TypeScript script templates for tracked tables. The generated function receives a transaction object containing:

• transaction_type: “insert” | “update” | “delete”
• schema_name: string
• table_name: string
• old_row: optional, present for update transactions
• row: the transaction data

External database hosters

Neon configuration

Neon’s default pooled connections via pgbouncer reject the replica parameter required for logical replication.

Option 1 (Recommended): Remove the -pooler suffix from the host. For example, change <cluster-name>-pooler.neon.tech to <cluster-name>.neon.tech.

Option 2: Modify pgbouncer configuration to allow the replica parameter by updating ignore_startup_parameters = replica.

Troubleshooting

Starting failures

WAL sender limit reached occurs when max_wal_senders capacity is exhausted. Solutions include disabling existing triggers on the resource or increasing the limit on the server.

Creation failures

Replication slot limit reached happens when max_replication_slots is exceeded. Solutions include deleting unused replication slots, increasing the server limit, or managing slots manually through the Advanced section.

Error handling

Postgres triggers support local error handlers that override workspace-level handlers for specific triggers, as documented in the error handling section.