Postgres source connector with xmin - Data consistency between tables

[beratwallee]

Dear Airbyte Community,
I was reading the code of the Postgres source connector, and I wanted to know how to guarantee data consistency between the tables involved in a connection.

I see this query:

airbyte/airbyte-integrations/connectors/source-postgres/src/main/java/io/airbyte/integrations/source/postgres/xmin/PostgresXminHandler.java

Line 180 in 0f2685b

final String sql = String.format("SELECT %s FROM %s WHERE xmin::text::bigint >= ?",

That is executed for each table at different moments.

When working with related tables, like those with parent-child relationships, dependent data can exist between them. If two separate transactions are in progress while queries are being run, changes made by one transaction may be reflected in one table but not in the other.

What do you think about the following change to ensure data consistency between tables?

...
final String sql = String.format("SELECT %s FROM %s WHERE xmin::text::bigint >= ? && xmin::text::bigint < ?",
          wrappedColumnNames, fullTableName);

final PreparedStatement preparedStatement = connection.prepareStatement(sql.toString());
if (prevRunXminStatus != null) {
  preparedStatement.setLong(1, prevRunXminStatus.getXminXidValue());
  preparedStatement.setLong(2, currentXminStatus.getXminXidValue());
} else {
  // In case ctid sync is not possible we will do the initial load using "WHERE xmin >= 0"
  preparedStatement.setLong(1, 0L);
  preparedStatement.setLong(2, currentXminStatus.getXminXidValue());
}

return preparedStatement;
...

I added AND xmin::text::bigint < ? with the value of the current xmin status. I haven’t yet seen what was done after the query executions. Maybe Airbyte cuts the data to guarantee consistency, but I doubt it.

[beratwallee]

Hi @akashkulk and @subodh1810. I saw that you worked in this section of the code. What do you think about the question I raised in the previous message?