changefeed-for.md

title	summary	toc
EXPERIMENTAL CHANGEFEED FOR	The EXPERIMENTAL CHANGEFEED FOR statement creates a new core changefeed, which streams row-level changes to the client indefinitely until the underlying connection is closed or the changefeed is canceled.	true

{{site.data.alerts.callout_info}} EXPERIMENTAL CHANGEFEED FOR is the core implementation of changefeeds. For the enterprise-only version, see CREATE CHANGEFEED. {{site.data.alerts.end}}

New in v19.1: The EXPERIMENTAL CHANGEFEED FOR statement creates a new core changefeed, which streams row-level changes to the client indefinitely until the underlying connection is closed or the changefeed is canceled.

For more information, see Change Data Capture.

{% include {{ page.version.version }}/misc/experimental-warning.md %}

Required privileges

Changefeeds can only be created by superusers, i.e., members of the admin role. The admin role exists by default with root as the member.

Considerations

Because core changefeeds return results differently than other SQL statements, they require a dedicated database connection with specific settings around result buffering. In normal operation, CockroachDB improves performance by buffering results server-side before returning them to a client; however, result buffering is automatically turned off for core changefeeds. Core changefeeds also have different cancellation behavior than other queries: they can only be canceled by closing the underlying connection or issuing a CANCEL QUERY statement on a separate connection. Combined, these attributes of changefeeds mean that applications should explicitly create dedicated connections to consume changefeed data, instead of using a connection pool as most client drivers do by default.

This cancellation behavior also extends to client driver usage; in particular, when a client driver calls Rows.Close() after encountering errors for a stream of rows. The pgwire protocol requires that the rows be consumed before the connection is again usable, but in the case of a core changefeed, the rows are never consumed. It is therefore critical that you close the connection, otherwise the application will be blocked forever on Rows.Close().

Synopsis

> EXPERIMENTAL CHANGEFEED FOR table_name [ WITH (option [= value] [, ...]) ];

Parameters

Parameter	Description
table_name	The name of the table (or tables in a comma separated list) to create a changefeed for.
option / value	For a list of available options and their values, see Options below.

Options

Option	Value	Description
updated	N/A	Include updated timestamps with each row.
resolved	INTERVAL	Periodically emit resolved timestamps to the changefeed. Optionally, set a minimum duration between emitting resolved timestamps. If unspecified, all resolved timestamps are emitted. Example: resolved='10s'
envelope	key_only / row	Use key_only to emit only the key and no value, which is faster if you only want to know when the key changes. Default: envelope=row
cursor	Timestamp	Emits any changes after the given timestamp, but does not output the current state of the table first. If cursor is not specified, the changefeed starts by doing a consistent scan of all the watched rows and emits the current value, then moves to emitting any changes that happen after the scan. cursor can be used to start a new changefeed where a previous changefeed ended. Example: CURSOR=1536242855577149065.0000000000
format	json / experimental_avro	Format of the emitted record. Currently, support for Avro is limited and experimental. Default: format=json.
confluent_schema_registry	Schema Registry address	The Schema Registry address is required to use experimental_avro.

Avro limitations

Currently, support for Avro is limited and experimental. Below is a list of unsupported SQL types and values for Avro changefeeds:

• Decimals must have precision specified.

• Decimals with NaN or infinite values cannot be written in Avro.

  {{site.data.alerts.callout_info}} To avoid NaN or infinite values, add a CHECK constraint to prevent these values from being inserted into decimal columns. {{site.data.alerts.end}}

• time, date, interval, uuid, inet, array, and jsonb are not supported in Avro yet.

Examples

Create a changefeed

{% include {{ page.version.version }}/cdc/create-core-changefeed.md %}

Create a changefeed with Avro

{% include {{ page.version.version }}/cdc/create-core-changefeed-avro.md %}

See also

• Change Data Capture
• Other SQL Statements