Re: [h2] Atomically swap tables
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
On Monday, 12 June 2023 at 20:13:50 UTC+2 Wayne Fuller wrote:
> 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 = 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*
>
> --
> 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
>
>
> .
>
>
--
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*
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/b4c72f87-5cd6-4ed3-8518-236899f5d92an%40googlegroups.com.
Re: [h2] Atomically swap tables
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 = 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*
--
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+unsubscr...@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
.
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/ba7a9f3b-e1a3-908c-0f04-27c631a92f37%40fullers4.com.
Re: [h2] Atomically swap tables
you would need to use SET EXCLUSIVE to briefly block other operations https://h2database.com/html/commands.html#set_exclusive -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/CAFYHVnVXHu0j5T4sfGLDQ%2BZVvYZwqjPM%2BccMGiQ5mFXEJ83QyA%40mail.gmail.com.
[h2] Atomically swap tables
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 = 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*
--
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+unsubscr...@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.
