Altering Table with Sqitch Rework command

Question

I've tried several times to follow Sqitch's 'postgres tutorial', just except instead of altering a function (which uses CREATE OR REPLACE FUNCTION), I'm changing a field name in a table to see how it'll workout after a deployment. But it ends up with below error. Could someone please point me to the right direction?

$ sqitch verify
Verifying sqtest_db
  * appschema .... ok
  * contact ...... ok
Undeployed change:
  * contact
Verify successful


$ sqitch deploy
Deploying changes to sqtest_db
  + contact .. psql:deploy/contact.sql:10: ERROR:  relation "contact" already exists
not ok
"/usr/local/bin/psql" unexpectedly returned exit value 3

Deploy failed

This is my before tagged and after tagged deploy query

BEFORE tagging the DB

BEGIN;
CREATE TABLE sq_schema.contact
  (
    log_date DATE NOT NULL,
    emp_name CHARACTER VARYING(100) DEFAULT ''
  );
COMMIT;

Tagging DB with

sqitch rework contact --requires appschema -n 'Added CONTACT table'

AFTER tagging

BEGIN;

CREATE TABLE sq_schema.contact
  (
    log_date DATE NOT NULL,

    -- Change field name,
    employee_name CHARACTER VARYING(100) DEFAULT ''
  );

COMMIT;

The author of Sqitch answered my above question in [Github](https://github.com/theory/sqitch/issues/282#issuecomment-176862111). Solution is to add `ALTER TABLE` file instead. — Da CodeKid, Jan 29 '16 at 18:30

score 16 · Accepted Answer · answered Jan 29 '16 at 18:52

16

Rework is intended for making idempotent changes, such as CREATE OR REPLACE FUNCTION. The CREATE TABLE statement is not idempotent. If you want to add a column to a table, I suggest either:

If you have not released your database, just modify the CREATE TABLE statement in the original change and sqitch rebase to revert all changes and redeploy with the updated table. This is ideal when doing development.
Otherwise, add a new change, named $table_$column or some such, and use an ALTER TABLE statement to add the new column. This is the approach to take if you have released the database already, though you can also do it before release if you like.

answered Jan 29 '16 at 18:52

theory

9,178
10
59
129

Hello! How does idempotence work with reverting changes? Does the requirement of idempotent changes mean that they can be reverted without breaking anything? For example, if in the original question the column is added to the table with `ALTER_TABLE`, then how should the revert action should be implemented: should it revert only this bit of change altering table to remove the new column, or should it work as regular revert and just `DROP` entire table? – Innokentiy Alaytsev Mar 05 '18 at 16:14
1

`ALTER TABLE` is not idempotent: if you run it twice, the second time it will fail. In that case, you want to have the revert script also `ALTER TABLE` to remove the column, so that you leave it in the same state as if the deploy had never occurred. IOW, Idempotence isn't relevant to normal add/revert changes, only to rework changes. – theory Mar 06 '18 at 14:44
1

Hello! Thank you for the clarification! I have a project where some default data (roles and permissions) is deployed by sqitch. I had some problems when I have tried to make `sqitch rework` of the data deployment changes to add new data. Now I see that I have done it wrong. – Innokentiy Alaytsev Mar 06 '18 at 22:58

Altering Table with Sqitch Rework command

1 Answers1

Linked