Investigate deduplication in compiled_contracts table

[kuzdogan]

As explained in #1416 the deplucation of the rows seem to be not working in the compiled_contracts table. Copying the explanation here:

Deduplication not working in compiled_contracts

When debugging the tests I've noticed the identical test contracts are getting added to the compiled_contracts table multiple times. I'd have expected them to be deduplicated because the table has a unique key property in the schema definition:

sourcify/services/database/migrations/20231109160022-create-alliance-schema.js

Line 204 in 653952d

CONSTRAINT compiled_contracts_pseudo_pkey UNIQUE (compiler, language, creation_code_hash, runtime_code_hash)

However during testing the contracts get added multiple times:

SELECT compiler, "language", creation_code_hash, runtime_code_hash FROM public.compiled_contracts x

Am I missing something or is there something wrong?

To reproduce

1. add a breakpoint at
   

   sourcify/services/server/test/integration/repository-handlers/files.spec.ts

   Line 56 in 653952d

   const address = await deployAndVerifyContract(

2. Add an .only modifier to describe:
   

   sourcify/services/server/test/integration/repository-handlers/files.spec.ts

   Line 43 in 653952d

   describe.only(`Pagination in /files/contracts/{full|any|partial}/${chainFixture.chainId}`, async function () {

3. run the Mocha - Server Integration, deploy and verify multiple contracts, and connect to the test database at localhost:5431.

[manuelwedler]

I was interested and had a quick look why this happens. It seems like the NULL is treated as a different value each time. See this: https://stackoverflow.com/questions/8289100/create-unique-constraint-with-null-columns

We should be able to easily fix it when recreating the database. We just need:

CONSTRAINT compiled_contracts_pseudo_pkey UNIQUE NULLS NOT DISTINCT (compiler, language, creation_code_hash, runtime_code_hash) 

[marcocastignoli]

Nice! So we need to add this in either the sourcify migration or a new migration (Right now we are not using migrations properly)

-- Drop the existing unique constraint
ALTER TABLE compiled_contracts
DROP CONSTRAINT compiled_contracts_pseudo_pkey;

-- Add the new unique constraint with NULLS NOT DISTINCT
CONSTRAINT compiled_contracts_pseudo_pkey UNIQUE NULLS NOT DISTINCT (compiler, language, creation_code_hash, runtime_code_hash) 

[kuzdogan]

First #1416 then this. NULLs in Postgres are a bit headache 😄