ERROR: cannot ALTER TABLE because it has pending trigger events while attempting to drop a column in a table

Question

I wrote a sql tag in my liquibase changeset. this sql code is suppose to make data migration in a table named "Purchase". so i have the following sql queries:

UPDATE purchase
SET location_id = (SELECT location_id FROM shop WHERE shop.id = purchase.shop_id);

then,

ALTER TABLE purchase DROP COLUMN shop_id;

but I'm getting the following error while attempting to execute the second query that's supposed to drop column 'shop_id' in table Purchase

ERROR: cannot ALTER TABLE  because it has pending trigger events

please while is this error thrown and how can i solve it.

in my liquibase changese, my sql tag is written as follow

<sql>
     UPDATE purchase
     SET location_id = (SELECT location_id FROM shop WHERE shop.id = purchase.shop_id);        

     ALTER TABLE purchase DROP COLUMN shop_id;
</sql

Please can somebody help me? thank's in advance for your help

Answer 1

Sounds like you need to commit your changes between the alter and the update.

Try this:

UPDATE purchase
SET location_id = (SELECT location_id FROM shop WHERE shop.id = purchase.shop_id);
COMMIT;
BEGIN;
ALTER TABLE purchase DROP COLUMN shop_id;

The "BEGIN;" is optional, but it sounds like your framework is using a transaction so I'd include it and test to make sure the alter actually "takes".