Creation of Constraints fails

[sandhein]

Is there an existing issue for this?

• [X] I have searched the existing issues

Current Behavior

For some models I create primary keys with the following macro:

{% macro primary_key(this, column) %}
{% set index_name = 'PK_'+ this.name %}

DECLARE
    constraint_name_exists  EXCEPTION;
    PRAGMA EXCEPTION_INIT(constraint_name_exists, -2264);
    name_exists  EXCEPTION;
    PRAGMA EXCEPTION_INIT(name_exists, -955);
BEGIN
    EXECUTE IMMEDIATE 'alter table {{this.name}} add constraint {{index_name}} primary key ({{column}}) nologging';
END;
{% endmacro %}`

When running the model for the first time everything works fine, after the second run, the macro throws an exception ORA-02264.

Looks as if the constraints are not deleted before the table is recreated, so that there still exists an object in the database with the name of the constraint. But due to the renamining of the table from table__dbt_tmp to table, the constraint is not assigned to the table either

Expected Behavior

Delete all existing constraint and indexes during renaming of the table in oracle__rename_relation

{% macro oracle__rename_relation(from_relation, to_relation) -%}
    {% call statement('drop_constraints') -%}
    BEGIN
        FOR r IN (SELECT table_name, constraint_name FROM user_constraints WHERE table_name = upper('{{ from_relation.include(False, False, True).quote(schema=False, identifier=False) }}')) LOOP
            EXECUTE IMMEDIATE 'alter table ' || r.table_name || ' drop constraint ' || r.constraint_name || ' CASCADE';
        END LOOP;
    END;
  {%- endcall %}

  {% call statement('drop_indexes') -%}
    BEGIN
        FOR r IN (SELECT table_name, index_name FROM user_indexes WHERE table_name = upper('{{ from_relation.include(False, False, True).quote(schema=False, identifier=False) }}')) LOOP
            EXECUTE IMMEDIATE 'DROP INDEX ' || r.index_name;
        END LOOP;
    END;
  {%- endcall %}

  {% call statement('rename_relation') -%}
    ALTER {{ from_relation.type }} {{ from_relation }} rename to {{ to_relation.include(schema=False) }}
  {%- endcall %}
{% endmacro %}

Steps To Reproduce

No response

Relevant log output using --debug flag enabled

No response

Environment

- OS:Windows
- Python: 3.8.10
- dbt: 1.2.1

What Oracle database version are you using dbt with?

21c

Additional Context

No response

[aosingh]

Hi @sandhein

Thank you for detailed report.

My concern is that dropping of indexes and constraints is included in renaming of relation which is not expected. Ideally, truncate or dropping of a relation should drop indexes and constraints. Let me check if there is a better way to handle this.

Have you looked at the package dbt_contraints ? Will it make managing of constraints easier ?

[sandhein]

HI @aosingh ,

thanks for looking into this so quickly.

I agree, that the renaming is not the perfect position for dropping the indexes. We used this as a quick workaround. I will have a look at the package you suggested, didn't new it existed :-).

[aosingh]

Hi @sandhein

Just checking to see if you had a chance to explore dbt_constraints and if it solves your usecase?

[aosingh]

@sandhein

We could simply skip constraint creation if it already exists. Let me know if this works ?

{% macro primary_key(this, column) %}
{% set index_name = 'PK_'+ this.name %}

DECLARE
    constraint_name_exists  EXCEPTION;
    PRAGMA EXCEPTION_INIT(constraint_name_exists, -2264);
    name_exists  EXCEPTION;
    PRAGMA EXCEPTION_INIT(name_exists, -955);
BEGIN
    EXECUTE IMMEDIATE 'alter table {{this.name}} add constraint {{index_name}} primary key ({{column}}) nologging';
EXCEPTION 
  WHEN constraint_name_exists or name_exists then 
  dbms_output.put_line( 'constraint creation skipped' ); 
END;
{% endmacro %}`

[sandhein]

Hi @aosingh ,

skipping constraint creation if it already exists was in fact my first approach, when the error occured. Unfortunately this resulted in missing constraints, due to the way dbt creates those tables:

1. Create temporary table my_model__dbt_tmp
2. Rename my_model to my_model__dbt_backup
3. Rename temporary table my_model__dbt_tm to my_model
4. Execute post_hook
5. Drop my_model__dbt_backup with cascade constraints

So the existing constraints are only dropped after the post hook, when my_model__dbt_backup is dropped.

Do you know if there is a way to execute the post-hook after step 5?

[sandhein]

Hi @aosingh , just had a closer look on the package you recommended, dbt_constraint. I think we can really use this to create our primary and foreign keys. Unfortunately it can't create indexes. So we are still experiencing the above mentioned issue when creating an index by using post-hook. But we could fix this by deleting the existing index in a pre-hook

[aosingh]

@sandhein

I actually opened this thread to let you know that I had not forgotten about the issue and was thinking about a solution. And then I read your comment. It seems like your suggested approach is the right approach. Delete index if exists during pre-hook and create the same using post-hook

Thank you letting me know :)

[aosingh]

Closing this issue