Frequently Asked Questions

Using a column filter

• What happens if I set up a column filter on a table with OIDS? Can I filter on xmin?

  Column filters work fine on tables with OIDs, but spock cannot filter on system columns like oid or xmin.

• What happens if a column being filtered on is dropped?

  Currently in spock replication, you can drop even a primary key on the provider. If a column being filtered on is dropped on the provider, it is removed from the column filter too. Use spock.repset_show_table() to confirm this behavior.

  Columns on each subscriber remain as defined, which is correct and expected. In this state, the subscriber replicates INSERTs, but does not replicate UPDATEs and DELETEs.

• If I add a column, does it automatically get included?

  If you add a column to a table on the provider, it is not automatically added to the column filter.

Using a row filter

• Can I create a row_filter on a table with OIDS? Can I filter on xmin?

  Yes, row_filter behavior works fine for table with OIDs, but spock cannot filter on system columns (like xmin).

• What types of functions can we execute in a row_filter? Can we use a volatile sampling function, for example?

  We can execute immutable, stable and volatile functions in a row_filter. Exercise caution with regard to writes as any expression which will do writes can potentially throw an error and stop replication.

  Volatile sampling function in row_filter: This would not work in practice as it would not get correct snapshot of the data in live system. Theoretically with static data, it works.

• Can we test a JSONB datatype that includes some form of attribute filtering?

  Yes, a row_filter on attributes of JSONB datatype works fine.

Using apply_delay with time changes

• Does apply_delay accommodate time zone changes, for example Daylight Savings Time?

  There is a similar mechanism in physical replication - recovery_min_apply_delay. However, if you set an interval during daylight savings times, you might get that interval + the time change (in other words, you'll get a 1h delay instead of a 2h delay because of the time differential). This may lead to stopping and starting the database service twice per year.

  Yes, apply_delay accommodates time changes like Daylight Savings Time. The value of apply_delay stays the same in practice, if a time shift happens after the subscription was created. However, we do not recommend running heavy workloads during a time change as spock replication needs some time ( ~ 5 minutes) to recover.