Hi pgsql-admins,
I am working on splitting up tables that are currently in a single schema into multiple, context specific schemas. I would like to do this with minimal downtime from the client perspective. I will have prepopulated the existing application client search_path with the new schema name.
When inquiring about this in postgresql IRC a concern was raised about data safety, due to locks concurrently executing transactions might have on the relation in question when the ALTER TABLE ... SET SCHEMA TO ... command is executed (even if done in a transaction).
Would it be data safe if I took an ACCESS EXCLUSIVE lock on the table?
My testing shows that a session with both the old schema and the new schema in its path will cleanly determine the new location of the relation - does anyone have any caveats or know of any sharp edges I should be aware of here?
Thanks in advance for any advice or clarity anyone can provide,
Joe