I have an ELT process that deals with a pretty small amount of data. I would like to have 1 schema called prod with the results of the previous ELT run and 1 schema called dev where I will perform the transformations of the current ELT run. Once the current ELT run is complete I would like to make the switch by running something along the lines of:

start transaction;
drop schema prod; 
alter schema dev rename to prod;
end transaction;

All queries issued outside of the ELT process are read only. How would MVCC handle this? What would happen to read only queries that were running when the drop schema command is issued? What would happen to read only queries issued after the drop schema command? What about after the end of the transaction?


DROP SCHEMA would attempt obtaining exclusive use of the schema first, so it would only actually manage to drop the schema when PostgreSQL is done retrieving data from ongoing queries. Further queries would likely be locked until the end of the transaction in your question.

Queries accessing tables in the prod schema may fail after this transaction is finished if they (queries or functions procedures) were compiled prior to the dropping and renaming of the schemas, but should be safe for queries from newly established connections.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.