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.
On 6/12/23 6:43 AM, Axel Möller wrote:
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
<https://www.budbee.com/en/external-privacy-notice> applicable to our
processing of personal data is available on our website at
www.budbee.com <https://www.budbee.com>
*Please consider the environment before printing this email*
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/32dc641e-99fb-4b56-94b4-814a0c0942efn%40googlegroups.com
<https://groups.google.com/d/msgid/h2-database/32dc641e-99fb-4b56-94b4-814a0c0942efn%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/ba7a9f3b-e1a3-908c-0f04-27c631a92f37%40fullers4.com.
