Bookkeeper: upgrade from 24.05 to 24.08.1 fails due to `ERROR: column "spliced" does not exist`

[whitslack]

Issue and Steps to Reproduce

1. Have a working CLN 24.05 node running on a PostgreSQL 15 back end.
2. Upgrade CLN to 24.08.1.
3. Attempt to start the node.
4. CRASH!

   2024-09-24T06:42:43.825Z INFO    plugin-bookkeeper: Killing plugin: exited before replying to init
   2024-09-24T06:42:43.825Z **BROKEN** plugin-bookkeeper: Plugin marked as important, shutting down lightningd!
   

The following error message is emitted on stderr from the lightningd process:

Error executing statement: ERROR:  current transaction is aborted, commands ignored until end of transaction block

The PostgreSQL server error log contains:

2024-09-24 02:42:43.460 EDT [6697] ERROR:  column "spliced" does not exist at character 8
2024-09-24 02:42:43.460 EDT [6697] STATEMENT:  SELECT spliced FROM chain_events
2024-09-24 02:42:43.461 EDT [6697] ERROR:  current transaction is aborted, commands ignored until end of transaction block
2024-09-24 02:42:43.461 EDT [6697] STATEMENT:  ALTER TABLE chain_events ADD spliced INTEGER DEFAULT 0;

It looks like Bookkeeper is trying to access the column spliced in table chain_events before it attempts to alter the table to add the column to it. Because the initial SELECT query fails, the subsequent ALTER TABLE statement in the same transaction is rejected. You need to roll back the failed transaction before you attempt to upgrade the schema.

[whitslack]

I was able to get my node to start by manually executing ALTER TABLE chain_events ADD spliced INTEGER DEFAULT 0; on the Bookkeeper's database. Crisis averted. 😌 Still, the migration ought to be corrected.