Dropping an unique constraint on a column and then dropping the column fails with index error

[krumft]

Our application is using typeorm version 0.3.11 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 have recently started adopting pg-mem to write unit tests (well ... ok, integration tests) for our entities and repositories. We are using version 2.6.8 of the package. We do execute all application migrations before each test.

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

it('test dropping unique constraint', async () => {
    db.public.query(
        'CREATE TABLE "table" ("id" character varying NOT NULL, "col" character varying, CONSTRAINT "REL_constraint" UNIQUE ("col"), CONSTRAINT "PK_constraint" PRIMARY KEY ("id"))',
    );
    db.public.query('ALTER TABLE "table" DROP CONSTRAINT "REL_constraint"');
    db.public.query('ALTER TABLE "table" DROP COLUMN "col"');
});

fails with:

Error: Cannot drop index that does not belong to this table: REL_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 "table" DROP COLUMN "col";

👉 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 (.../node_modules/pg-mem/src/table.ts:663:19)
    at ColRef.drop (.../node_modules/pg-mem/src/column.ts:195:24)
    at Alter.execute (.../node_modules/pg-mem/src/execution/schema-amends/alter.ts:54:29)
    at .../node_modules/pg-mem/src/execution/statement-exec.ts:203:42
    at pushExecutionCtx (.../node_modules/pg-mem/src/utils.ts:391:16)
    at .../node_modules/pg-mem/src/execution/statement-exec.ts:192:54
    at StatementExec.niceErrors (.../node_modules/pg-mem/src/execution/statement-exec.ts:221:20)
    at StatementExec.executeStatement (.../node_modules/pg-mem/src/execution/statement-exec.ts:192:21)
    at DbSchema.queries (.../node_modules/pg-mem/src/schema/schema.ts:130:45)
    at queries.next (<anonymous>)

We have worked the problem around with this hack:

.interceptQueries((queryText) => {
    if (queryText.includes('DROP CONSTRAINT')) {
        return [];
    }
    return null;
});

Still, it would be great if pg-mem aligns better with Postgres on the behaviour in this scenario.

[krumft]

After some debugging it seems that the root cause is that in pg-mem when a constraint is being dropped the field usedInIndexes in the column representation is not updated. When adding the constraint the system creates an index and that field is updated. This is why when the column is dropped in the next statement of our test, the system sees the REL_constraint index inside usedInIndexes and tries to drop it.

[krumft]

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

[oguimbal]

Hi !

Thanks for the PR, I released it as pg-mem@2.6.9

[krumft]

Great, thank you!