Firebird 3: drop column with check constraint fail with no current record for fetch operation error

[maxirobaina]

Hi,

When I want remove a column with a check constraint it fails. I tested it with Firebird 3 LI-V3.0.5.33220 on Ubuntu 20.04.6 LTS

Example:

CREATE TABLE DJANGO_CELERY_BEAT_CLOCKEDSH
(
  ID integer NOT NULL PRIMARY KEY,
  CLOCKED_TIME timestamp NOT NULL,
  ENABLED boolean NOT NULL
);

alter table DJANGO_CELERY_BEAT_CLOCKEDSH
add constraint CHK_DJANGO_CELERY_BEAT_CLOC0
check ( "ENABLED" IN (false,true));

Then run

ALTER TABLE DJANGO_CELERY_BEAT_CLOCKEDSH DROP ENABLED

Whit this error message:

Context: Statement::Execute( ALTER TABLE DJANGO_CELERY_BEAT_CLOCKEDSH DROP ENABLED )
Message: isc_dsql_execute2 failed

SQL Message : -607
This operation is not defined for system tables.

Engine Code    : 335544351
Engine Message :
unsuccessful metadata update
ALTER TABLE DJANGO_CELERY_BEAT_CLOCKEDSH failed
no current record for fetch operation
At trigger 'RDB$TRIGGER_23'

[hvlad]

Try with current 3.0.11, please

[hvlad]

Also, it is weird to use isc_dsql_execute2 for DDL statement.

[maxirobaina]

Also, it is weird to use isc_dsql_execute2 for DDL statement.

In this case I was using Flamerobin to run that scripts

[maxirobaina]

Try with current 3.0.11, please

I'll try. There aren't firebird 3.0.11 packege for ubuntu in ppa

[hvlad]

Just run it using FlameRobin and Firebird 3.0.12 (Win64) - no problem

[maxirobaina]

Hi @hvlad Can confirm same error with firebird 3.0.7 in ubuntu server 20.04.6 and same error en Firebird 3.0.11 in Windows 10.

[maxirobaina]

UPDATE: its seems to be a problem with a DB in special. Restoring a backup it works fine, without error. I don't know where is the problem. There were several changes in the metadata but I can figured out where it fails

[hvlad]

Could you validate database (gfix -v -fu) ?

[maxirobaina]

Could you validate database (gfix -v -fu) ?

No validations erros was found.

[aafemt]

Did you commit transaction after constraint creation?

[maxirobaina]

Did you commit transaction after constraint creation?

If I drop the field without drop constraint, this error ocurr. If I first drop the constraint and then drop the field works fine.

[aafemt]

This answer is not for my question.

[hvlad]

Please, run two queries below right before dropping the field and in the same transaction, and show results here:

select *
  from RDB$INDICES t3, RDB$RELATION_CONSTRAINTS t4, RDB$INDEX_SEGMENTS t5
 where t3.RDB$RELATION_NAME = 'DJANGO_CELERY_BEAT_CLOCKEDSH'
   and t3.RDB$INDEX_NAME = t5.RDB$INDEX_NAME
   and t4.RDB$INDEX_NAME = t5.RDB$INDEX_NAME
   and t5.RDB$FIELD_NAME = 'ENABLED'
   and exists(select * from RDB$INDEX_SEGMENTS t6
               where t6.RDB$INDEX_NAME = t5.RDB$INDEX_NAME
                 and t6.RDB$FIELD_NAME <> 'ENABLED'
         )

select *
  from RDB$RELATION_CONSTRAINTS t7, RDB$CHECK_CONSTRAINTS t8, RDB$DEPENDENCIES t9
 where t7.RDB$RELATION_NAME = 'DJANGO_CELERY_BEAT_CLOCKEDSH'
   and t7.RDB$CONSTRAINT_TYPE = 'CHECK'
   and t8.RDB$TRIGGER_NAME = t9.RDB$DEPENDENT_NAME
   and t9.RDB$DEPENDENT_TYPE = 2
   and t9.RDB$DEPENDED_ON_TYPE = 0
   and t9.RDB$DEPENDED_ON_NAME = 'DJANGO_CELERY_BEAT_CLOCKEDSH'
   and t9.RDB$FIELD_NAME = 'ENABLED'
   and t8.RDB$CONSTRAINT_NAME = t7.RDB$CONSTRAINT_NAME
   and exists(select * from RDB$DEPENDENCIES t10
               where t10.RDB$DEPENDENT_NAME = t8.RDB$TRIGGER_NAME
                 and t10.RDB$DEPENDENT_TYPE = 2
                 and t10.RDB$DEPENDED_ON_TYPE = 0
                 and t10.RDB$DEPENDED_ON_NAME = 'DJANGO_CELERY_BEAT_CLOCKEDSH'
                 and t10.RDB$FIELD_NAME <> 'ENABLED'
         )

[maxirobaina]

select * from RDB$RELATION_CONSTRAINTS t7, RDB$CHECK_CONSTRAINTS t8, RDB$DEPENDENCIES t9 where t7.RDB$RELATION_NAME = 'DJANGO_CELERY_BEAT_CLOCKEDSH' and t7.RDB$CONSTRAINT_TYPE = 'CHECK' and t8.RDB$TRIGGER_NAME = t9.RDB$DEPENDENT_NAME and t9.RDB$DEPENDENT_TYPE = 2 and t9.RDB$DEPENDED_ON_TYPE = 0 and t9.RDB$DEPENDED_ON_NAME = 'DJANGO_CELERY_BEAT_CLOCKEDSH' and t9.RDB$FIELD_NAME = 'ENABLED' and t8.RDB$CONSTRAINT_NAME = t7.RDB$CONSTRAINT_NAME and exists(select * from RDB$DEPENDENCIES t10 where t10.RDB$DEPENDENT_NAME = t8.RDB$TRIGGER_NAME and t10.RDB$DEPENDENT_TYPE = 2 and t10.RDB$DEPENDED_ON_TYPE = 0 and t10.RDB$DEPENDED_ON_NAME = 'DJANGO_CELERY_BEAT_CLOCKEDSH' and t10.RDB$FIELD_NAME <> 'ENABLED' )

Both queries return a empty result.

[maxirobaina]

@hvlad I make another test.

Running on a new restore backup, starting from scratch: create table, create the check constraint, select existent constraiant from system tables, it returns two check constraint, Is that correct?

select *
from RDB$RELATION_CONSTRAINTS rc
inner join RDB$CHECK_CONSTRAINTS cc using (RDB$CONSTRAINT_NAME) 
inner join RDB$TRIGGERS t using (RDB$TRIGGER_NAME, RDB$RELATION_NAME)
where rc.RDB$CONSTRAINT_TYPE = 'CHECK'
and t.RDB$RELATION_NAME = 'DJANGO_CELERY_BEAT_CLOCKEDSH'

[hvlad]

There is one constraint and two system triggers behind it, it is correct.

[hvlad]

Both queries return a empty result.

Change and exists() by and not exists() in both queries and repeat, please.

[maxirobaina]

Both queries return a empty result.

Change and exists() by and not exists() in both queries and repeat, please.

First query result is empty.

Second query returns

CHK_DJANGO_CELERY_BEAT_CLOC0 CHECK DJANGO_CELERY_BEAT_CLOCKEDSH NO NO [null] CHK_DJANGO_CELERY_BEAT_CLOC0 CHECK_943 CHECK_943 DJANGO_CELERY_BEAT_CLOCKEDSH ENABLED 2 0 [null] CHK_DJANGO_CELERY_BEAT_CLOC0 CHECK DJANGO_CELERY_BEAT_CLOCKEDSH NO NO [null] CHK_DJANGO_CELERY_BEAT_CLOC0 CHECK_944 CHECK_944 DJANGO_CELERY_BEAT_CLOCKEDSH ENABLED 2 0 [null]

[hvlad]

It is as expected. And after these queries the DROP ENABLED will fail ? If yes, I'm out of ideas for now.