Is it possible to somehow rename the Vertica schema without interrupting current transaction, withou

givanx

Hi,

I currently have a task of renaming a Vertica schema.
The problem is that this schema is being intensely used (writes / reads).
I fear that after renaming the schema, all ongoing transactions will just fail.
What happens to ongoing transactions if I run the ALTER SCHEMA RENAME TO command?

Is it possible to somehow rename the Vertica schema without interrupting current transaction, without downtime?

Thank you.

marcothesane

This should work as expected.

Every Vertica transaction picks up a snapshot of the database catalogue at its beginning, and keeps it for the duration of the transaction - until COMMIT (also implicitly or through Autocommit) or ROLLBACK.

So, if a transaction with ID 42, going
INSERT INTO foobar.target SELECT * FROM foo.source;
, is under way at the moment that you go:
ALTER SCHEMA foo RENAME TO bar;
... then transaction with ID 42 will keep working with the snapshot it took before the ALTER SCHEMA command was issued until it commits.

marcothesane

But of course, if the insert-select is followed by a COMMIT, in a script, and a following command wants to select from schema foo that does not exist any more, a new snapshot will be taken, and the script will fail at that point.

givanx

thank you, @marcothesane!