↑↓ select ↵ open ⌫ change scope Open full search

PG.CENTER connects PostgreSQL documentation, reference, and ecosystem knowledge. Maintained by Pigsty.

Wiki / Logical Decoding Plugins / Testing and examples

test_decoding

Testing and examples

A test and example output plugin that renders logical changes in a textual format.

Reading PostgreSQL 18.6.

Description

A test and example output plugin that renders logical changes in a textual format.

Interface family
Logical-decoding output plugin
Handler or routine
_PG_output_plugin_init
Recorded callbacks
21

Interface and capability boundaries

The matrix records callbacks actually registered in this source build. Registration identifies an implemented interface hook; options, query shape, privileges and provider rules determine whether an operation is allowed.

The complete same-version manual below retains configuration, constraints and examples. No runtime capability test is claimed.

Output and protocol boundaries

Callback presence and protocol versions are separate from client negotiation and enabled plugin options. Streaming and two-phase processing require the matching protocol and configuration.

test_decoding is an example/test output format; pgoutput implements the logical-replication protocol. Their output contracts are not interchangeable.

Registered implementation in core source

{
	cb->startup_cb = pg_decode_startup;
	cb->begin_cb = pg_decode_begin_txn;
	cb->change_cb = pg_decode_change;
	cb->truncate_cb = pg_decode_truncate;
	cb->commit_cb = pg_decode_commit_txn;
	cb->filter_by_origin_cb = pg_decode_filter;
	cb->shutdown_cb = pg_decode_shutdown;
	cb->message_cb = pg_decode_message;
	cb->filter_prepare_cb = pg_decode_filter_prepare;
	cb->begin_prepare_cb = pg_decode_begin_prepare_txn;
	cb->prepare_cb = pg_decode_prepare_txn;
	cb->commit_prepared_cb = pg_decode_commit_prepared_txn;
	cb->rollback_prepared_cb = pg_decode_rollback_prepared_txn;
	cb->stream_start_cb = pg_decode_stream_start;
	cb->stream_stop_cb = pg_decode_stream_stop;
	cb->stream_abort_cb = pg_decode_stream_abort;
	cb->stream_prepare_cb = pg_decode_stream_prepare;
	cb->stream_commit_cb = pg_decode_stream_commit;
	cb->stream_change_cb = pg_decode_stream_change;
	cb->stream_message_cb = pg_decode_stream_message;
	cb->stream_truncate_cb = pg_decode_stream_truncate;
}

Registered interface handlers

Interface operationSource observationCallbackImplementation
Begin transactionHandler registered; conditions applybegin_cbpg_decode_begin_txn
Row changesHandler registered; conditions applychange_cbpg_decode_change
CommitHandler registered; conditions applycommit_cbpg_decode_commit_txn
TRUNCATEHandler registered; conditions applytruncate_cbpg_decode_truncate
Logical messagesHandler registered; conditions applymessage_cbpg_decode_message
Start streamed transactionHandler registered; conditions applystream_start_cbpg_decode_stream_start
Stream row changesHandler registered; conditions applystream_change_cbpg_decode_stream_change
Prepare transactionHandler registered; conditions applyprepare_cbpg_decode_prepare_txn
Commit preparedHandler registered; conditions applycommit_prepared_cbpg_decode_commit_prepared_txn
Rollback preparedHandler registered; conditions applyrollback_prepared_cbpg_decode_rollback_prepared_txn

Option names recognized by this plugin build

Extracted from the option parser; names alone do not describe defaults, accepted values or protocol requirements.

Source option name
force-binary
include-rewrites
include-timestamp
include-xids
only-local
skip-empty-xacts
stream-changes

Manual definition

F.45. test_decoding — SQL-based test/example module for WAL logical decoding

test_decoding is an example of a logical decoding output plugin. It doesn't do anything especially useful, but can serve as a starting point for developing your own output plugin.

test_decoding receives WAL through the logical decoding mechanism and decodes it into text representations of the operations performed.

Typical output from this plugin, used over the SQL logical decoding interface, might be:

postgres=# SELECT * FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'include-xids', '0');
   lsn     | xid |                       data
-----------+-----+--------------------------------------------------
 0/16D30F8 | 691 | BEGIN
 0/16D32A0 | 691 | table public.data: INSERT: id[int4]:2 data[text]:'arg'
 0/16D32A0 | 691 | table public.data: INSERT: id[int4]:3 data[text]:'demo'
 0/16D32A0 | 691 | COMMIT
 0/16D32D8 | 692 | BEGIN
 0/16D3398 | 692 | table public.data: DELETE: id[int4]:2
 0/16D3398 | 692 | table public.data: DELETE: id[int4]:3
 0/16D3398 | 692 | COMMIT
(8 rows)

We can also get the changes of the in-progress transaction, and the typical output might be:

postgres[33712]=#* SELECT * FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'stream-changes', '1');
    lsn    | xid |                       data
-----------+-----+--------------------------------------------------
 0/16B21F8 | 503 | opening a streamed block for transaction TXN 503
 0/16B21F8 | 503 | streaming change for TXN 503
 0/16B2300 | 503 | streaming change for TXN 503
 0/16B2408 | 503 | streaming change for TXN 503
 0/16BEBA0 | 503 | closing a streamed block for transaction TXN 503
 0/16B21F8 | 503 | opening a streamed block for transaction TXN 503
 0/16BECA8 | 503 | streaming change for TXN 503
 0/16BEDB0 | 503 | streaming change for TXN 503
 0/16BEEB8 | 503 | streaming change for TXN 503
 0/16BEBA0 | 503 | closing a streamed block for transaction TXN 503
(10 rows)

Related entries

Documentation and source

Source build
Version
18.6
Build
PostgreSQL 18.6 source archive
Source fingerprint
555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f

Compare versions

PostgreSQL 17 → 18: unchanged.

Compares recorded interfaces and attributes. Source fingerprints and build metadata are excluded; an absent sample is not proof of the introduction or removal release.

Export JSON · Back to Logical Decoding Plugins · Recorded in PostgreSQL 10 through 20; the first sample is not necessarily its introduction.