Atomically swap tables

[Axel Möller]

I use H2 in a Spring Boot application with the following h2 config:

jdbc:h2:./my-database;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;MODE=mysql

I have two tables, called

read_from_here

write_to_here

with identical DDL.

The application only reads from read_from_here.

A long processing job writes new rows to the write_to_here table.

I then want to swap these tables so write_to_here is renamed to read_from_here.

I attempt to do this with the following query:

@Transactional
    fun swap(): Either<Throwable, Unit> = Either.catch {
        jdbcTemplate.execute("DROP TABLE IF EXISTS read_from_here; ALTER TABLE write_to_here RENAME TO read_from_here;")
    }

I assumed because this runs in a single transaction that the rename is atomic. But it appears that queries that selects from read_from_here fail during the swap because read_from_here no longer exists during a brief period.

How should I perform an atomic rename of the tables so there is no downtime?

This e-mail and any attachments hereto are intended for the designated recipient(s) only. It may contain confidential and/or proprietary information. If you have received this communication unintentionally, please inform us immediately by return email, and ensure that the original message and copies, attachments, and printouts relating thereto are permanently deleted. Thank you. Any dissemination, distribution or copying of this e-mail is strictly prohibited.

E-mail is susceptible to interception by third parties, data corruption, tampering, unauthorised amendment, and viruses in connection with its transmission. We do not accept liability for any such interception, corruption, tampering, amendment or viruses, or any consequence thereof.

E-mails and attachments thereto sent or received by us may contain personal data. The privacy notice applicable to our processing of personal data is available on our website at www.budbee.com

Please consider the environment before printing this email

[Noel Grandin]

you would need to use SET EXCLUSIVE to briefly block other operations

https://h2database.com/html/commands.html#set_exclusive

[Wayne Fuller]

Instead of the application reading from the current read_from_here table, how about creating a view to point to the current read_from_here table? Then you just have to recreate the view, but the app will just point at the single view. I would assume you are in full control of writing to the write_to_here table where then you can control which one to use for writing.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/32dc641e-99fb-4b56-94b4-814a0c0942efn%40googlegroups.com.

[Axel Möller]

Thank you for the suggestions. Using a view (since this table is readonly) is an excellent idea. I will check if this solves my issues. Basically the logic will be:

1. Create table with some random suffix

2. Import data into that table

3. CREATE OR REPLACE VIEW read_from_here AS SELECT * FROM write_to_here_12345

4. Drop any old table