Table Monitors

Overview

A table monitor is an entity that can be created to watch a target table for inserts, updates, or deletes, and relay notifications to a queue for consumption by interested clients. A table monitor is bound to the a single type of table operation to monitor when created and cannot be changed to monitor another type afterwards. For instance, a table monitor created to watch for inserts cannot monitor update or deletes or be modified to watch for one of those two instead, after creation.

Table monitors provide the following notifications, based on table activity monitored.

Table ActivityTable Monitor Notification
insertsThe entirety of each record inserted into the target table will be published to the queue.
updatesThe number of records updated for each update operation executed against the target table will be published to the queue.
deletesThe number of records deleted for each delete operation executed against the target table will be published to the queue.

Configuration

Table monitor notifications are published to ZMQ, which listens for subscriber connections on the system table monitor port (default 9002) configured as set_monitor_port under Network Configuration in the gpudb.conf file.

When a table is the target of both a table monitor and a multi-head ingest, notifications are pushed from the worker nodes to the head node through the head node's table monitor proxy port (default 9003) configured as set_monitor_proxy_port under Network Configuration in the gpudb.conf file.

Notifications of inserts pulled from the queue are multi-part messages, with the first message being the topic ID and the remainder being binary-encoded Avro objects, one per record inserted. These must be decoded using the type schema returned by the table monitor creation call.

Managing Table Monitors

A table monitor can be created with the /create/tablemonitor endpoint. This call will return two values:

  • topic ID - used to subscribe to the table monitor's notifications and to remove the table monitor
  • type schema - used to decode records within insert notifications

During the table monitor's lifetime, it cannot be modified.

The existence of a table monitor on a table can be verified by calling the /show/table endpoint with the corresponding table name, and parsing the response for the list of attached table monitors found under the additional_info > table_monitor keys. Each table monitor's monitored event type (insert, update, or delete) and topic ID are available.

A table monitor will run continuously until it is no longer needed. It can be removed by topic ID, using the /clear/tablemonitor endpoint.

Table monitors will be temporarily unavailable during a database restart, but will become active once the database begins servicing requests again.

Limitations & Cautions

Example

For a tutorial on using table monitors with an associated complete example, see Table Monitor Guide.