Subscriptions might lose data

[josephg]

I'm currently implementing CDC in a web application on top of postgres.

I want to:

• Fetch the users table
• Be notified whenever the users table changes and update my local copy

But obviously, I also need to be careful of the race condition: What happens if the data is modified between my fetch and subscribe calls?

The "right answer", as far as I can tell, is to get the WAL log location (lsn) along with my fetch query, and then subscribe to all changes from that location onwards. For example, to fetch:

let startLsn
{
  await sql.begin('ISOLATION LEVEL REPEATABLE READ', async sql => {
    const lsnResult = await sql`SELECT pg_current_wal_lsn() as lsn`;
    startLsn = lsnResult[0].lsn

    const result = await sql<User[]>`SELECT * FROM users`;
    result.forEach((user) => {
      users.set(user.id, user);
    });
  })
}

But then to subscribe I need to pass the LSN in. I want to make this query:

sql`START_REPLICATION SLOT users_cache_slot LOGICAL ${startLsn} (proto_version '1', publication_names 'users_pub')`

Unfortunately, the subscription code in postgres can't do this as far as I can tell?