Filter Settings

Filter Settings#

Table Filter#

If omitted or left empty, this section from the .ini file means that LiveCompare executes against all tables in the first database.

If you want LiveCompare to execute against a specific set of tables, there are different ways to specify this:

publications#

You can filter specific publications, and LiveCompare uses only the tables associated with those publications. You can use the variable publication_name to build the conditional expression, for example:

publications = publication_name = livepub

Requires logical_replication_mode = native .

replication_sets#

When using pglogical or PGD, you can filter specific replication sets, and LiveCompare works only on the tables associated with those replication sets. You can use the variable set_name to build the conditional expression, for example:

replication_sets = set_name in (default, bdrgroup)

Requires logical_replication_mode = pglogical or logical_replication_mode = bdr .

schemas#

You can filter specific schemas, and LiveCompare works only on the tables that belong to those schemas. You can use the variable schema_name to build the conditional expression, for example:

schemas = schema_name != badschema

tables#

The variable table_name can help you build a conditional expression to filter only the tables you want LiveCompare to work on, for example:

tables = table_name not like %%account

In any conditional expression, escape the % character as %% .

The table name must be schema-qualified, unless schema_qualified_table_names is disabled. For example, you can filter only a specific list of tables:

tables = table_name in (myschema1.mytable1, myschema2.mytable2)

If you disable the general setting schema_qualified_table_names , then you must also set an appropriate search_path for Postgres in the connection start_query setting, for example:

[General Setting]
...
schema_qualified_table_names = off

[My Connection]
...
start_query = SET search_path TO myschema1, myschema2

[Table Filter]
tables = table_name in (mytable1, mytable2)

Important

If two or more schemas that were set on search_path contain a table with the same name, just the first one found is considered in the comparison.

The Table Filter section can have a mix of publications , replication_sets , schemas , and tables filters. LiveCompare considers the set of tables that are in the intersection of all filters you specified. For example:

[Table Filter]
publications = publication_name = livepub
replication_sets = set_name in (default, bdrgroup)
schemas = schema_name != badschema
tables = table_name not like %%account

The table filter is applied in the first database to build the table list. If a table exists in the first database and is being considered in the filter, but it doesn’t exist in any other database, then you something like this is added to the logs, and the comparison for that specific table is skipped:

2019-06-17 11:52:41,403 - ERROR - live_table.py - 55 - GetMetaData - P1: livecompare_second_1: Table public.test does not exist
2019-06-17 11:52:41,410 - ERROR - live_round.py - 201 - Initialize - P1: Table public.test does not exist on second connection. Aborting comparison

Similarly, if a table exists in any other database but doesn’t exist in the first database, then it isn’t considered in the comparison, even if you didn’t apply any table filter.

A comparison for a specific table is also skipped if the table column names aren’t exactly the same (unless column_intersection is enabled), and in the same order. An appropriate message is added to the log file as well.

Currently LiveCompare doesn’t check if data types or constraints are the same on both tables.

Important

conflicts mode doesn't make use of the table filter.

Row Filter#

In this section, you can apply a row-level filter to any table, so LiveCompare works only on the rows that satisfy the row filter.

You can write a list of tables under this section, one table per line. All table names must be schema qualified unless schema_qualified_table_names is disabled. For example:

[Row Filter]
public.table1 = id = 10
public.table2 = logdate >= 2000-01-01

In this case, for the table public.table1 , LiveCompare works only in the rows that satisfy the clause id = 10 . For the table public.table2 , only rows that satisfy logdate >= '2000-01-01 are considered in the comparison.

If you disable the general setting schema_qualified_table_names , then you must also set an appropriate search_path for Postgres in the connection start_query setting, for example:

[General Setting]
...
schema_qualified_table_names = off

[My Connection]
...
start_query = SET search_path TO public

[Row Filter]
table1 = id = 10
table2 = logdate >= 2000-01-01

Any kind of SQL condition (same as you put in the WHERE clause) is accepted in the same line as the table row filter. For example, if you have a large table and want to compare only a specific number of IDs, you can create a temporary table with all the IDs. Then you can use an IN clause to emulate a JOIN , like this:

[Row Filter]
public.large_table = id IN (SELECT id2 FROM temp_table)

If a row filter is written incorrectly, then LiveCompare tries to apply the filter but fails. So the comparison for this specific table is skipped, and an exception is written to the log file.

If a table is listed in the Row Filter section but somehow got filtered out by the Table Filter , then the row filter for this table is silently ignored.

Important

conflicts mode doesn't make use of the row filter.

Using current timestamp in Row Filter#

The Row Filter is applied differently depending on the data_fetch_mode :

  • On Postgres, setting data_fetch_mode to server_side_cursors_with_hold or server_side_cursors_without_hold causes the Row Filter to be applied only at the beginning of the table comparison, when the query is executed. This means that using a server-side cursor to fetch data ensures the data is seen as a snapshot of how it was beginning of the comparison.

  • On Postgres, setting data_fetch_mode to prepared_statements (the default) includes the Row Filter in the prepared query, which is then executed at every data buffer that’s fetched. This means that, if the query uses now() , CURRENT_TIMESTAMP , or SYSDATE (on EDB Postgres Advanced Server) on the Row Filter , then when the prepared statement executes, Postgres reevaluates the current timestamp.

So, suppose you’re using now() , CURRENT_TIMESTAMP , or SYSDATE on the Row Filter , for example:

[Row Filter]
public.table3 = logdate < CURRENT_TIMESTAMP

In this case, you must also use a server-side cursor to ensure the current timestamp is evaluated only at the beginning of the queries. In other words, data_fetch_mode must be set to a value different from prepared_statements .

On Oracle, the data_fetch_mode setting is ignored, and the query is executed at the beginning. Then data is fetched by way of the client-side cursor. This approach ensures data is seen as a snapshot of how it was at the beginning of the comparison. This is a client-side cursor, but the behavior is similar to using a server-side cursor in Postgres.

Column Filter#

In this section, you can apply a column-level filter to any table, so LiveCompare works only on the columns that aren’t part of the column filter.

You can write a list of tables under this section, one table per line. All table names must be schema qualified unless schema_qualified_table_names is disabled. For example, suppose that both public.table1 and public.table2 have the columns column1 , column2 , column3 , column4 , and column5 :

[Column Filter]
public.table1 = column1, column3
public.table2 = column1, column5

In this case, for the table public.table1 , LiveCompare works only in the columns column2 , column4 , and column5 , filtering out column1 and column3 . For the table public.table2 , only the columns column2 , column3 , and column4 are considered in the comparison, filtering out column1 and column5 .

If you disable the general setting schema_qualified_table_names , then you must also set an appropriate search_path for Postgres in the connection start_query setting, for example:

[General Setting]
...
schema_qualified_table_names = off

[My Connection]
...
start_query = SET search_path TO public

[Column Filter]
table1 = column1, column3
table2 = column1, column5

If absent column names are given in the column filter, that is, the column doesn’t exist in the given table, then LiveCompare logs a message about the missing columns and ignores them. It uses just the valid ones, if any.

If a table is listed in the Column Filter section but somehow got filtered out by the Table Filter , then the column filter for this table is silently ignored.

Important

If a column specified in a Column Filter is part of the table PK, then it isn't ignored in the comparison. LiveCompare logs that and ignores the filter of such a column.

Important

conflicts mode doesn't make use of the column filter.

Comparison Key#

Similar to the Column Filter , in this section you can also specify a list of columns per table. These columns are considered as a comparison key for the specific table, even if the table has a primary key or UNIQUE constraint.

For example:

[Comparison Key]
public.table1 = col_a, col_b
public.table2 = c1, c2

In this example, for table public.table1 , the comparison key is columns col_a and col_b . For table public.table2 , columns c1 and c2 are considered as a comparison key.

The same behavior about missing columns or filtered out or missing tables that are explained in Column Filter , also apply to the comparison key. Similarly, the Comparison Key section is ignored in conflicts mode.

Conflicts Filter#

In this section, you can specify a filter to use in --conflicts mode while fetching conflicts from PGD nodes. You can build any SQL conditional expression and use these fields in the expression:

  • origin_node : The upstream node of the subscription.

  • target_node : The downstream node of the subscription.

  • local_time : The timestamp when the conflict occurred in the node.

  • conflict_type : The type of conflict.

  • conflict_resolution : The resolution that was applied.

  • nspname : Schema name of the involved relation.

  • relname : Relation name of the involved relation.

You must use the conflicts attribute under the section. For example:

[Conflicts Filter]
conflicts = conflict_type = update_missing AND nspname = my_schema

If you add this piece of configuration to your .ini file, LiveCompare fetches only conflicts that are of type update_missing and related to tables under the schema my_schema while querying for conflicts in each of the PGD nodes.

Important

This section is exclusively for --conflicts mode.

Contents