[source-postgres] CdcPostgresSourceLegacyCtidTest isn't testing against postgres 13

[xiaohansong]

https://github.com/airbytehq/airbyte/blob/master/airbyte-integrations/connectors/source-postgres/src/test/java/io/airbyte/integrations/source/postgres/CdcPostgresSourceLegacyCtidTest.java

After we refactored test infra this does not actually test running on legacy postgres versions. We should update that.

I've tried to update that but encountered failures among unit tests: during the initial scan, lsn state in the last state message is different than the previous state message; this only happens on postgres 13 but not 16.

In the test https://github.com/airbytehq/airbyte/blob/master/airbyte-integrations/connectors/source-postgres/src/test/java/io/airbyte/integrations/source/postgres/CdcPostgresSourceTest.java#L199 expects state message stays the same in the same batch. However in postgres 13, the last state message will mutate. See example below:

Previous state message:
"[\"db_dlpenhggyr\",{\"server\":\"db_dlpenhggyr\"}]": "{\"transaction_id\":null,\"lsn\":24834904,\"txId\":678,\"ts_usec\":1707870101598505}"

Final state message generated by PostgresCdcStateHandler:

"[\"db_dlpenhggyr\",{\"server\":\"db_dlpenhggyr\"}]": "{\"transaction_id\":null,\"lsn_proc\":24867752,\"lsn_commit\":24867752,\"lsn\":24867752,\"txId\":678,\"ts_usec\":1707870101598505}"

Some research would be required to understand why the behavior is different, and determine if we have an underlying bug/data loss potential in this case.

[evantahler]

Grooming:

• we need research: is this a real bug or a testing problem. There are legacy and non-legacy tests. The legacy test is passing, but it shouldn't - it's not testing the right thing - when updated, the test fails. The 'latest' test works ok.
• If this is a real bug, regroup and decide on priority.

[xiaohansong]

We have a 'hack' to advance LSN for version < 15:

https://github.com/airbytehq/airbyte/blob/master/airbyte-integrations/connectors/source-postgres/src/main/java/io/airbyte/integrations/source/postgres/PostgresUtils.java#L198

That will change the state and cause the test to fail. Nothing wrong on prod though.