PG snapshots are failing with 'tx is closed'

[roysc]

When using the Postgres publisher, snapshots are failing due to the transaction object being prematurely closed. We need to track down how this happens and make test cases for it if possible.

FATA[2022-08-22T09:04:35-05:00]/src/cmd/stateSnapshot.go:79 github.com/vulcanize/ipld-eth-state-snapshot/cmd.stateSnapshot() tx is closed SubCommand=stateSnapshot

[prathamesh0]

@roysc

• I believe what's happening is the tx passed to CommitOrRollback(tx, err) gets outdated once PrepareTxForBatch is called on it and it is closed. So when the createSnapshot function returns, it tries to commit an already closed transaction.
• Also see the remaining task in https://github.com/vulcanize/ipld-eth-state-snapshot/issues/55

[roysc]

Thanks @prathamesh0! That's what I suspected, but I haven't found why pgx apparently returns a closed tx from Begin.

However, it works fine if I remove the PrepareTxForBatch calls entirely. That was needed in the past when transactions would become too large and need to be flushed, but maybe upgrades or hardware differences have obviated it. I think we can sidestep investigating this further and just remove that method.

[roysc]

Now that the service runs, it's revealed a concurrency issue. Not sure if this is related, but I will put this here until a new issue is opened.

There's a deadlock/contention among the PG threads, while accessing the blocks table.

2022-08-22 17:35:35.252 CDT [98157] DETAIL:  Process 98157 waits for ShareLock on transaction 5320; blocked by process 98155.
    Process 98155 waits for ShareLock on transaction 5321; blocked by process 98157.
    Process 98157: INSERT INTO public.blocks (block_number, key, data) VALUES ($1, $2, $3) ON CONFLICT (key, block_number) DO NOTHING
    Process 98155: INSERT INTO public.blocks (block_number, key, data) VALUES ($1, $2, $3) ON CONFLICT (key, block_number) DO NOTHING
2022-08-22 17:35:35.252 CDT [98157] HINT:  See server log for query details.
2022-08-22 17:35:35.252 CDT [98157] CONTEXT:  while inserting index tuple (2030,33) in relation "blocks"
2022-08-22 17:35:35.252 CDT [98157] STATEMENT:  INSERT INTO public.blocks (block_number, key, data) VALUES ($1, $2, $3) ON CONFLICT (key, block_number) DO NOTHING
2022-08-22 17:35:35.259 CDT [98156] ERROR:  could not serialize access due to concurrent update
2022-08-22 17:35:35.259 CDT [98156] STATEMENT:  INSERT INTO public.blocks (block_number, key, data) VALUES ($1, $2, $3) ON CONFLICT (key, block_number) DO NOTHING

[i-norden]

@roysc is this still an issue (in v5)?

[roysc]

I don't recall ever seeing it in v5. I can try to verify on different machines.

[roysc]

I haven't been able to reproduce this again on a couple machines, but I have been using old chain data as I haven't been able to sync a significant amount of mainnet data post-Merge. But for now I think this can be closed and re-opened or revisited if it shows up again.