Enabling and disabling column-level conflict resolution v5.6
Documentation improvements are made only to the latest version.
As per semantic versioning, PGD minor releases remain backward compatible and may include important bug fixes and enhancements.
We recommend upgrading the latest minor release as soon as possible.
If you want up-to-date information, read the latest PGD documentation.
Permissions required
Column-level conflict detection uses the column_timestamps type. This type requires any user needing to detect column-level conflicts to have at least the bdr_application role assigned.
The bdr.alter_table_conflict_detection() function manages column-level conflict resolution.
Using bdr.alter_table_conflict_detection to enable column-level conflict resolution
The bdr.alter_table_conflict_detection function takes a table name and column name as its arguments. The column is added to the table as a column_modify_timestamp column. The function also adds two triggers (BEFORE INSERT and BEFORE UPDATE) that are responsible for maintaining timestamps in the new column before each change.
db=# CREATE TABLE my_app.test_table (id SERIAL PRIMARY KEY, val INT); CREATE TABLE db=# ALTER TABLE my_app.test_table REPLICA IDENTITY FULL; ALTER TABLE db=# SELECT bdr.alter_table_conflict_detection( db(# 'my_app.test_table'::regclass, db(# 'column_modify_timestamp', 'cts'); alter_table_conflict_detection -------------------------------- t db=# \d my_app.test_table Table "my_app.test_table" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+-------------------------------------------------- id | integer | | not null | nextval('my_app.test_table_id_seq'::regclass) val | integer | | | cts | bdr.column_timestamps | | not null | 's 1 775297963454602 0 0'::bdr.column_timestamps Indexes: "test_table_pkey" PRIMARY KEY, btree (id) Triggers: bdr_clcd_before_insert BEFORE INSERT ON my_app.test_table FOR EACH ROW EXECUTE FUNCTION bdr.column_timestamps_current_insert() bdr_clcd_before_update BEFORE UPDATE ON my_app.test_table FOR EACH ROW EXECUTE FUNCTION bdr.column_timestamps_current_update()
The new column specifies NOT NULL with a default value, which means that ALTER TABLE ... ADD COLUMN doesn't perform a table rewrite.
Note
Avoid using columns with the bdr.column_timestamps data type for other purposes, as doing so can have negative effects. For example, it switches the table to column-level conflict resolution, which doesn't work correctly without the triggers.
Listing tables with column-level conflict resolution
You can list tables having column-level conflict resolution enabled with the following query.
SELECT nc.nspname, c.relname FROM pg_attribute a JOIN (pg_class c JOIN pg_namespace nc ON c.relnamespace = nc.oid) ON a.attrelid = c.oid JOIN (pg_type t JOIN pg_namespace nt ON t.typnamespace = nt.oid) ON a.atttypid = t.oid WHERE NOT pg_is_other_temp_schema(nc.oid) AND nt.nspname = 'bdr' AND t.typname = 'column_timestamps' AND NOT a.attisdropped AND c.relkind IN ('r', 'v', 'f', 'p');
This query detects the presence of a column of type bdr.column_timestamp.