GoldenGate Parallel Replicat Migration fails on INT2VECTOR conversion

[jonstjohn]

Describe the problem

When Oracle GoldenGate is used to migrate an Oracle database to CRDB using the PostgreSQL driver in parallel replicat mode, it fails with a type conversion error. This appears to occur when a compound primary key is used in the CRDB target database and was traced to a problem with INT2VECTOR conversion.

This ticket is not specifically to address GoldenGate working in parallel replicat mode, but to fix the INT2VECTOR conversion discrepancy between PostgreSQL and CRDB.

To Reproduce

The simplest possible reproduction of the issue is to run the following query in PostgreSQL and CRDB:

select array_to_string(array[(select array[1,2]::int2vector)],' ');

In PostgreSQL, the following output is observed:

d=# select array_to_string(array[(select array[1,2]::int2vector)],' ');
 array_to_string
-----------------
 1 2

In CRDB, the following is observed:

> select array_to_string(array[(select array[1,2]::int2vector)],' ');
      array_to_string
----------------------------
  ARRAY[1:::INT8,2:::INT8]

The type conversion error in CRDB can be produced by running the following query:

> with t1 as (select ARRAY[1,2]::INT2VECTOR as c1) SELECT string_to_array(array_to_string(ARRAY[c1], ' '), ' ')[1]::INT2 from t1;
ERROR: could not parse "ARRAY[1:::INT8,2:::INT8]" as type int: strconv.ParseInt: parsing "ARRAY[1:::INT8,2:::INT8]": invalid syntax
SQLSTATE: 22P02

Whereas in PostgreSQL, this same query produces this result:

with t1 as (select ARRAY[1,2]::INT2VECTOR as c1) SELECT string_to_array(array_to_string(ARRAY[c1], ' '), ' ') from t1;
 string_to_array
-----------------
 {1,2}

Expected behavior

INT2VECTOR conversion has the same result in PostgreSQL and CRDB.

Additional data / screenshots

The original query produced by GoldenGate that exhibited this issue was:

SELECT 
    g AS cat, 
    w.nspname AS schem, 
    w.relname AS tabname, 
    i.unique AS non_unique, 
    w.nspname AS iqualifier, 
    i.conname AS iname, 
    i.type, 
    s.n AS ordinalpos, 
    a.attname AS colname, 
    'A'::CHAR AS ascdesc, 
    NULL AS cardinalitycol, 
    COALESCE(i.pages, 
    w.relpages) AS pagescol, 
    i.conbin AS filtercondition 
FROM (
    SELECT 
        c.oid, 
        c.relpages, 
        g, n.nspname, 
        c.relname 
    FROM ROWS FROM (current_database()) AS g,
    pg_catalog.pg_namespace AS n, 
    pg_catalog.pg_class AS c 
    WHERE (
        (c.relnamespace = n.oid) 
        AND (n.nspname = 'public')) 
        AND (c.relname = 'tbl')
    ) AS w 
    LEFT JOIN (
        SELECT 
            3 AS type, 
            NULL AS pages, 
            1 AS unique, 
            conrelid, 
            conname, 
            string_to_array(array_to_string(conkey, ' '), ' ') AS conkey, 
            conbin 
        FROM 
            pg_catalog.pg_constraint 
        WHERE 
            contype = 'c' 
        UNION 
        SELECT 
            CASE WHEN i.indisclustered THEN 1 
                WHEN m.amname = 'hash' THEN 2 
                ELSE 3 END, c.relpages,
            CASE WHEN i.indisunique THEN 0 
                ELSE 1 END, 
            i.indrelid, 
            c.relname, 
            string_to_array(array_to_string(ARRAY[i.indkey], ' '), ' '), 
            NULL 
        FROM 
            pg_catalog.pg_index AS i 
            LEFT JOIN pg_catalog.pg_class AS c 
                ON c.oid = i.indexrelid,
            pg_catalog.pg_am AS m 
        WHERE 
            m.oid = c.relam
    ) AS i 
        ON (i.conrelid = w.oid),
    (SELECT * FROM ROWS FROM (generate_series(1, 32, 1))) AS s (n),
    pg_catalog.pg_attribute AS a 
WHERE 
    (a.attrelid = w.oid) 
    AND (a.attnum = i.conkey[s.n]::INT2);

To reproduce the error using this query, it is necessary to create a table in the current database tbl with a compound primary key, e.g.:

create table tbl (c1 int, c2 int, constraint tbl_pk PRIMARY KEY (c1, c2));

Environment:

• CockroachDB version: Reproduced on 22.1.9 and 22.2.2
• Server OS: Reproduced locally on Mac OSX
• Client app: cockroach sql

Additional context

The result is that GoldenGate cannot be run in parallel replicat mode.

Jira issue: CRDB-23601

[msirek]

SELECT array_to_string(ARRAY[ARRAY[ARRAY[5,6], ARRAY[2,3]]], ' ');
                      array_to_string
------------------------------------------------------------
  ARRAY[ARRAY[5:::INT8,6:::INT8],ARRAY[2:::INT8,3:::INT8]]