Bug: Unique Constraint check fails if there is more than one constraint with the same key

[chicoribas]

I am using BulkInsertOrUpdateAsync with UpdateByProperties in a table that has two unique constraints:

ALTER TABLE reconciliation.reconciliation_discrepancies  ADD CONSTRAINT field_value_discrepancy_unique UNIQUE (record_id, field_value, internal_value, external_value);

ALTER TABLE reconciliation.reconciliation_discrepancies ADD CONSTRAINT missing_field_discrepancy_unique UNIQUE (record_id, missing_field, missing_type);

Note that the record_id column is present on both. This presence in two constrains make the SQL to check constraints existence returns two, making the logic understand that theres no constraint for the columns.

This behavior causes the exception CREATE INDEX CONCURRENTLY cannot run inside a transaction block, as the constraint is not recognized and I am executing this in a transactions.

Proposal

The SQL command to check the existence of constraint should change from this:

q = @"SELECT COUNT(distinct c.conname)
                  FROM pg_catalog.pg_namespace nr,
                      pg_catalog.pg_class r,
                      pg_catalog.pg_attribute a,
                      pg_catalog.pg_namespace nc,
                      pg_catalog.pg_constraint c
                  WHERE nr.oid = r.relnamespace
                  AND r.oid = a.attrelid
                  AND nc.oid = c.connamespace
                  AND r.oid =
                      CASE c.contype
                          WHEN 'f'::""char"" THEN c.confrelid
                      ELSE c.conrelid
                          END
                      AND (a.attnum = ANY (
                          CASE c.contype
                      WHEN 'f'::""char"" THEN c.confkey
                          ELSE c.conkey
                          END))
                      AND NOT a.attisdropped
                      AND (c.contype = ANY (ARRAY ['p'::""char"", 'u'::""char""]))
                      AND (r.relkind = ANY (ARRAY ['r'::""char"", 'p'::""char""]))" +
                $" AND r.relname = '{tableInfo.TableName}'" + 
                $" AND nr.nspname = '{tableInfo.Schema}'" + 
                $" AND a.attname IN('{string.Join("','", primaryKeysColumns)}')";

To this:

 q = @"WITH constraint_columns AS (
        SELECT 
          c.conname,
          a.attname
        FROM pg_catalog.pg_constraint c
        JOIN pg_catalog.pg_class r ON r.oid = c.conrelid
        JOIN pg_catalog.pg_namespace ns ON r.relnamespace = ns.oid
        JOIN pg_catalog.pg_attribute a ON a.attrelid = r.oid
        WHERE c.contype IN ('u', 'p')  -- unique or primary key
          AND (r.relkind = ANY (ARRAY ['r'::"char", 'p'::"char"]))
          AND NOT a.attisdropped
          AND a.attnum = ANY (c.conkey) " +
          $" AND r.relname = '{tableInfo.TableName}' "+
          $" AND ns.nspname = '{tableInfo.Schema}'  " +
      ")
      SELECT 
        COUNT(distinct conname)
      FROM constraint_columns" +
      $" WHERE attname IN ('{string.Join("','", primaryKeysColumns)}') " + 
      "GROUP BY conname "
      $"HAVING COUNT(DISTINCT attname) = {primaryKeysColumns.length};"; 

This way, the count will return only the quantity of constraints with the exact columns received.