Postgres sink update error where table name exists in multiple schemas

Directions

Error write error, All primary keys were not accounted for when importing into Postgres sink where the table name exists in more than one schema.

Bug report

Relevant pipeline.js:

System info:

Transporter version: 0.5.2
OS: Ubuntu 18.04.4 LTS
DB version(s): postgres (PostgreSQL) 12.4 (Debian 12.4-1.pgdg100+1)

Reproducible Steps:

Create two tables with the same name, in different schemas:

CREATE TABLE public.example_table
(
    public_example_table_id bigint NOT NULL,
    CONSTRAINT example_table_pkey PRIMARY KEY (public_example_table_id)
)

CREATE SCHEMA other
    AUTHORIZATION postgres;

CREATE TABLE other.example_table
(
    other_example_table_id bigint NOT NULL,
    CONSTRAINT example_table_pkey PRIMARY KEY (other_example_table_id)
)

Run the script used by the Postgres writer, as found at https://github.com/compose/transporter/blob/9e154e76b7d2977d9ac7756660779b512cace87f/adaptor/postgres/writer.go#L178-L187

        SELECT
            column_name
        FROM information_schema.table_constraints constraints
            INNER JOIN information_schema.constraint_column_usage column_map
                ON column_map.constraint_name = constraints.constraint_name
        WHERE constraints.constraint_type = 'PRIMARY KEY'
            AND constraints.table_schema = 'other'
            AND constraints.table_name = 'example_table'
/*
column_name
----------------
public_example_table_id
other_example_table_id
*/

Note that the primary keys from both tables are returned, not just the primary key from the requested schema.
Run transporter, writing to the Postgres sink. The error write error, All primary keys were not accounted for is returned and the etl fails.

What did you expect to happened?

Only the primary keys for the destination table and schema are used.

What actually happened?

The import failed as transporter erroneously identified a primary key as missing from the message data.

compose / transporter

Postgres sink update error where table name exists in multiple schemas #497