hmm i dont know about this one

theres gotta be a better way than this right?

maybe a last updated timestamp, but count seems dubious to me

yeah some form on in-db upsert plz: https://kb.objectrocket.com/postgresql/how-to-perform-the-postgresql-upsert-in-existing-table-1260 or similar

We enable deleting of event log records when we delete a run.

This is an attempt to make the bookkeeping accurate in case we delete the last record of a known asset key.

Short of maintaining a counter, we'd have to run a table-scanning distinct query upon run deletion.

The alternative is not try to do that book-keeping and allow via the UI to delete asset-keys from the summary table

Wasn't sure if we were going to try to support older versions of postgres. honestly, the fact that some syntax is supported by some versions of postgres (> 9.5) and not others seems a pain.

this took 30s on the RDS test db, we worried about it bigger instances?

we should print out whats happening here - potentially use tqdm or whatever for the progress on asset key writes

did you check this query at all?

cannot be migrated -- if you intended to migrate a persistent instance, etc.

helpful to have docs on the arguments so that ppl can implement this

there are other ways to do this that would make sure we avoid locking the db up. e.g.,

• we could select count(1) on the rows (not attempting a uniqueness/group by query) for which we want to insert asset_key entries, to get a count of rows to display to the user.
• then we can page over the rows to index (ordering by id) and do batch upserts

that will be computationally more expensive but it moves the computation to the migration process and out of the db

i think we should have this on all our db objects tbh

hm what happens if the migration fails halfway -- we need to drop the index right?

would it be insane to have a test that made assertions about the output of explain on this query

I think having a long-running read should not lock up the db (selects should only block alter/drop/truncate queries).

No, we don't mark the migration as successful unless the script runs to completion.

consider the select distinct that locked up reads from dwall's db

but what state is the index left in? is there the possibility of duplicate entries?

I think the select distinct had more to do with blocking on the graphql webserver, rather than at the DB level. The query itself should not block other reads.

I can add a note about the importance of idempotency for the migrations. But the pattern of checking if the secondary index is enabled before reads should mean that it being in an incomplete state should not affect anything because there will be no reads from it.

ya some notes on the webserver problem here https://elementl.quip.com/zZtAA2HE2mFN/Dagit-Webserver-Throughput

yep that makes sense

so if i run it and it fails halfway, then i run it again, will it drop the existing values? i don't see that here

It will not drop the existing values... But there is a unique constraint on asset_keys so you will not get duplicate values.

We also do the asset key table bookkeeping when we call delete_events on the event log storage, so I don't think you would end up with stale values that appear in the asset key table that are not in the event log table without manually dropping rows from the event_log.