8

We have a couple of migration scripts, which alter the schema from version to version.

Sometimes it happens, that a migration step (e.g. adding a column to a table) was already done manually or by a patch installation, and thus the migration script fails.

How do I prevent the script from stopping on error (ideally at specific expected errors) and instead log a message and continue with the script?

We use PostgresQL 9.1, both a solution for PostgresQL as well as a general SQL solution would be fine.

Alexander Rühl
  • 6,769
  • 9
  • 53
  • 96
  • 1
    WHy not write the scripts so the tasks like adding a column only occur of the column does not exist? – HLGEM Jun 26 '14 at 13:58
  • @HLGEM: And how would you check whether it exists? Also, this was just an example - it can happen in all cases where a command can only run once without an error. – Alexander Rühl Jun 26 '14 at 14:16
  • 1
    As for adding columns to table only when it does not exists, there already is an [answer on SO](http://stackoverflow.com/questions/12597465/how-to-add-column-if-not-exists-on-postgresql) – Tomas Greif Jun 26 '14 at 19:07

2 Answers2

5

Although @LucM's answer seems to be good recommendation - @TomasGreif pointed me to an answer that went more in the direction of my origin request.

For the given example of adding a column, this can be done by using the DO statement for catching the exception:

DO $$ 
    BEGIN
        BEGIN
            ALTER TABLE mytable ADD COLUMN counter integer default 0; 
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'counter column already exists';
        END;
    END;
$$;

The hint led me to the right PostgresQL site, describing the error codes one could trap - so that was the right solution for me.

Alexander Rühl
  • 6,769
  • 9
  • 53
  • 96
1

I don't think that you have another solution than running the entire script outside of a transaction.

What I would do if I was in that situation:

  • Do the modifications to the metadata (drop/create table/column...) outside of a transaction.
  • Do all modifications to the data (update/insert/delete) inside a transaction.
Luc M
  • 16,630
  • 26
  • 74
  • 89
  • Thanks for the hint, this might be a way to do it. Only I was hoping to find something, where I can decide for certain cases, that I accept an error for one command but like to continue and still stay within the complete transaction as done before. – Alexander Rühl Jun 26 '14 at 14:18