How to temporarily change the database name

Phoenix Forum Questions / Help
,
1

The problem:

I have a site (https://bagelconcertfinder.com) that updates concert listings every night. During covid, a problem arose with the database. Shows were canceled or rescheduled constantly. My database checks for new events and adds them to the list but doesn’t delete canceled or rescheduled events.

Rather than build something that syncs the two datasets, I would rather run a new update like so:

  • Create a temporary “Events” table.
  • Run the updates against that temp table.
  • Drop the current Events table.
  • Rename the TempEvents to “Events”

I have seen all this before. I am not sure how to temporarily change the name of the Events table while I am populating it (via something like MIX_ENV=prod mix run -e UpdateSiteData.start_data_update in a cron job)

Ideas? Thanks!

2

Is this step near-instant or could it take a while?

3

This step takes a few minutes…

4

Well, even with that you can have persistent events and temp_events table and do almost the same workflow as you described, minus dropping the table and then renaming the other one.

Excluding the long step, everything else can be wrapped in an Ecto.Multi and you’d be mostly safe against conflicts. But the long-running step makes this a bit more complicated.

5

What about

Table events
Event Name | Status | Timestamp
Event 1, active, …
Event 2, active, …

Inserting new events
Event Name | Status | Timestamp
Event 1, active, …
Event 2, active, …
Event 1, new, …
Event 2, new, …

Your table now contain a mix of new and old events. Then do a swap on the status. active => inactive, new => active. Later on your can delete the inactive events.

6

I would take a slightly different approach. I would put a view over the top and then when I’ve updated the data into a new table (in any number of cycles or generations you want) then I would recreate the view with something like:

CREATE OR REPLACE VIEW my_view AS SELECT * FROM my_current_data_table;

In postgres at least the DDL is atomic and done in a transaction so safe - and relatively easy.

3 Likes
7

Thanks for the suggestions, folks! I am looking at each, but will probably go with the one that I am least familiar with, just so i learn something new in the process.

thanks again!

8

I think your original plan is the way to go.

  1. Make a new table with the same structure
CREATE TABLE "table_new" (LIKE "table");

  1. Run your import scripts to insert in table_new.

  2. Then replace the tables in a transaction.

BEGIN;
ALTER TABLE "table" RENAME TO "table_old";
ALTER TABLE "table_new" RENAME TO "table";
DROP TABLE "table_old";
COMMIT;
10

The only trick to this, is… how do i update the schema to use “table_new” during the import scripts?

11

Have a second schema probably

12

yeah, that’s the one thing i didn’t want to do, as there would be more than one to maintain…

13

Will this help? elixir - How can you dynamically set a schema table name for a ecto model - Stack Overflow

2 Likes