[Question] Regarding behavior of pseduo-materialized views and truncate ddl statement
We're exploring using a stored procedure and the event system to rebuild a table hourly, like a materialized view. In some examples I see the procedure perform a truncate on the table before filling.
Truncate is a DDL statement, which means implicit commit. If this procedure takes for example 2 minutes to fill the table, is there a brief window where a user might see an empty or incorrect data set?
Posted Sep 6, 2018, 10:13 PM UTC
13
build one in parallel and replace the existing table instead of clearing and regenerating the single table
Partially I'm curious if my train of thought here is correct, whether it would actually be a potential problem or not.
Also If I do a
DELETE FROMinstead of truncate, that's a DML statement and barring a bad a isolation level (we're default repeatable read) I could wrap the operation in a transaction and maintain atomicity, I think?you would still be in a state with partial data until the process completes, populating a new table then switching the old and new removes that window entirely, but you would have a window serving stale data