Prevent Error When Dropping Not Existing Sequences, Creating Existing Users

Prevent error when dropping not existing sequences, creating existing users

Liquibase has a failOnError attribute you can set to false on changeSets that include a call that could fail.

<changeSet failOnError="false">
   <createSequence sequenceName="new_sequence"/>
</changeSet>

This allows you to have simple create user, create sequence, drop user, and drop sequence changeSets and if the statement throws an error because they users/sequences exist/don't exist they will still be marked as ran and the update will continue.

The downside of this approach is that it will also mark them as ran and continue if they error for some other reason (bad permissions, connection failure, invalid SQL, etc.) The more accurate approach is to use preconditions, like this:

<changeSet>
   <preconditions onFail="MARK_RAN"><not><sequenceExists/></not></preconditions>
   <createSequence name="new_sequence"/>
</changeSet>

There is no userExists precondition currently, but you can create custom preconditions or fall back to the precondition. See http://www.liquibase.org/documentation/preconditions.html for documentation

cannot drop a system-generated sequence

You should not need to drop an identity column sequence explicitly, as it is associated with the table, and dropping the table with the purge option (or dropping normally and emptying the recycle bin) will also drop the sequence.

create table test_tbl (id  number generated as identity);

Table created.

select * from user_tab_identity_cols;

TABLE_NAME                     COLUMN_NAME GENERATION_TYPE SEQUENCE_NAME IDENTITY_OPTIONS
------------------------------ ----------- --------------- ------------- --------------------------------------------------------------------------------
TEST_TBL                       ID          ALWAYS          ISEQ$$_124811 START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999999999999999999999999, MIN_VAL

Dropping the table leaves the table and its sequence in the recycle bin:

drop table test_tbl;

Table dropped.

select * from user_tab_identity_cols;

TABLE_NAME                     COLUMN_NAME GENERATION_TYPE SEQUENCE_NAME IDENTITY_OPTIONS
------------------------------ ----------- --------------- ------------- --------------------------------------------------------------------------------
BIN$gQ8wv//QTjK+mAGxdffxgQ==$0 ID          ALWAYS          ISEQ$$_124811 START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999999999999999999999999, MIN_VAL

Emptying the recycle bin clears it:

purge recyclebin;

Recyclebin purged.

select * from user_tab_identity_cols;

no rows selected

select * from user_sequences where sequence_name like 'ISEQ$$%';

no rows selected

Dropping all user tables/sequences in Oracle

If you're not intending on keeping the stored procedure, I'd use an anonymous PLSQL block:

BEGIN

  --Bye Sequences!
  FOR i IN (SELECT us.sequence_name
              FROM USER_SEQUENCES us) LOOP
    EXECUTE IMMEDIATE 'drop sequence '|| i.sequence_name ||'';
  END LOOP;

  --Bye Tables!
  FOR i IN (SELECT ut.table_name
              FROM USER_TABLES ut) LOOP
    EXECUTE IMMEDIATE 'drop table '|| i.table_name ||' CASCADE CONSTRAINTS ';
  END LOOP;

END;

Remove identity flag from a column in PostgreSQL

I don't think there is a safe and supported way to do that (without catalog modifications). Fortunately, there is nothing special about sequences that would make dropping them a problem. So take a short down time and:

• remove the default value that uses the identity sequence

• record the current value of the sequence

• drop the table

• create a new sequence with an appropriate START value

• use the new sequence to set new default values

If you want an identity column, you should define it on the partitioned table, not on one of the partitions.

---

Related Topics