Dropping constraints fails with an index error

[krumft]

Our application is using typeorm version 0.3.16 against a Postgres database, and we maintain our DB schema in TypeScript migrations. These are getting executed successfully by the ORM framework against the 'real' Postgres db.

We are using pg-mem version 2.6.12 to create some unit and integration tests for our application.

Following scenario is failing for us, it is taken from a real-world migration that succeeds against Postgres:

it('should support dropping constraints and indexes', async () => {
    db.public.query('CREATE TABLE "table1" ("id" character varying NOT NULL, CONSTRAINT "PK_constraint_1" PRIMARY KEY ("id"))');
    db.public.query(
            'CREATE TABLE "table2" ("id" character varying NOT NULL, "col" character varying, CONSTRAINT "PK_constraint_2" PRIMARY KEY ("id"))',
    );

    db.public.query('ALTER TABLE "table2" ADD CONSTRAINT "UQ_constraint" UNIQUE ("col")');
    db.public.query('ALTER TABLE "table2" ADD CONSTRAINT "FK_constraint" FOREIGN KEY ("col") REFERENCES "table1"("id") ON DELETE CASCADE ON UPDATE NO ACTION');
    db.public.query('CREATE INDEX "IDX_table2_col" ON "table2" ("col")');

    db.public.query('DROP INDEX "IDX_table2_col"');
    db.public.query('ALTER TABLE "table2" DROP CONSTRAINT "FK_constraint"');
    db.public.query('ALTER TABLE "table2" DROP CONSTRAINT "UQ_constraint"');
});

fails with:

Error: Cannot drop index that does not belong to this table: UQ_constraint

🐜 This seems to be an execution error, which means that your request syntax seems okay,
    but the resulting statement cannot be executed → Probably not a pg-mem error.

*️⃣ Failed SQL statement: ALTER TABLE "table2" DROP CONSTRAINT "UQ_constraint";

👉 You can file an issue at https://github.com/oguimbal/pg-mem along with a way to reproduce this error (if you can), and  the stacktrace:

If we comment the last line that drops "UQ_constraint" the migration succeeds.

[krumft]

I will look into providing a fix for that.

[krumft]

Problem reproduced with this simpler test:

    it('can drop constraint after dropping an index on the same column', () => {
        none(`create table test (col text);
            alter table test add constraint uq unique (col);
            create index idx on test(col);
            drop index idx;
            alter table test drop constraint uq;
        `);
    });

which gives

Error: Cannot drop index that does not belong to this table: uq

🐜 This seems to be an execution error, which means that your request syntax seems okay,
    but the resulting statement cannot be executed → Probably not a pg-mem error.

*️⃣ Reconsituted failed SQL statement: ALTER TABLE test  DROP CONSTRAINT uq

👉 You can file an issue at https://github.com/oguimbal/pg-mem along with a way to reproduce this error (if you can), and  the stacktrace:

      at MemoryTable.dropIndex (src/table.ts:667:19)
      at IndexConstraint.uninstall (src/constraints/index-cst.ts:9:20)
      at Alter.execute (src/execution/schema-amends/alter.ts:67:25)

It seems that this is due to a limitation in the internal model, which treats unique constraints as indexes, and allows at most one index over a given column. When that index is dropped the constraint cannot be dropped, as we already have no indexes for that same column. Hence the error.

[krumft]

Raised https://github.com/oguimbal/pg-mem/pull/333 to fix this.