BUG #16485: array_recv causes binary COPY to not work between postgres instances with arrays of custom types - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      16485
Logged by:          Jelte Fennema
Email address:      jelte.fennema@microsoft.com
PostgreSQL version: 12.2
Operating system:   Ubuntu 18.04
Description:

COPY in binary format should, based on the documentation, work between
different instances of the same Postgres server. This is not the case when
copying arrays of custom types. 

Steps to reproduce:

CREATE TYPE composite_type AS (
    i integer,
    i2 integer
);

CREATE TABLE composite_type_table
(
    col composite_type[]
);
INSERT INTO composite_type_table VALUES  (ARRAY['(1,
2)'::composite_type]);
\copy composite_type_table to dump.raw BINARY;

drop type composite_type cascade;

drop table composite_type_table;
-- same type, but with different OID (imitating different postgres
database)
CREATE TYPE composite_type AS (
    i integer,
    i2 integer
);

CREATE TABLE composite_type_table
(
    col composite_type[]
);
\copy composite_type_table from dump.raw BINARY;
-- ERROR:  42804: wrong element type
-- CONTEXT:  COPY composite_type_table, line 1, column col
-- LOCATION:  array_recv, arrayfuncs.c:1316



This piece of code seems to be the issue:

https://github.com/postgres/postgres/blob/ec5d6fc4ae8c75391d99993cd030a8733733747d/src/backend/utils/adt/arrayfuncs.c#L1312-L1318
Like the comment (from 17 years ago) mentions, I don't think this check is
actually needed. And in this case it's actually hurts. Storing the OID at
all actually seems unnecassary, but changing that doesn't seem worth the
backwards incompatibility to me.
As some extra information this binary copy is not the issue I'm facing
myself. In my case I'm running into this, while trying to make Citus use the
binary protocol between nodes to save data.

The following change on top of REL_12_STABLE fixes the issue for me. If this
is considered the right fix, I'd appreciate some help get this in through
the right procedures. It would be great if this could be fixed in all
currently supported versions of Postgres.


diff --git a/src/backend/utils/adt/arrayfuncs.c
b/src/backend/utils/adt/arrayfuncs.c
index 8fcdf82922..81a61f7bc7 100644
--- a/src/backend/utils/adt/arrayfuncs.c
+++ b/src/backend/utils/adt/arrayfuncs.c
@@ -1267,10 +1267,9 @@ Datum
 array_recv(PG_FUNCTION_ARGS)
 {
     StringInfo    buf = (StringInfo) PG_GETARG_POINTER(0);
-    Oid            spec_element_type = PG_GETARG_OID(1);    /* type of an array
+    Oid            element_type = PG_GETARG_OID(1);    /* type of an array
                                                          * element */
     int32        typmod = PG_GETARG_INT32(2);    /* typmod for array elements */
-    Oid            element_type;
     int            typlen;
     bool        typbyval;
     char        typalign;
@@ -1307,14 +1306,12 @@ array_recv(PG_FUNCTION_ARGS)
                 (errcode(ERRCODE_INVALID_BINARY_REPRESENTATION),
                  errmsg("invalid array flags")));
 
-    element_type = pq_getmsgint(buf, sizeof(Oid));
-    if (element_type != spec_element_type)
-    {
-        /* XXX Can we allow taking the input element type in any cases? */
-        ereport(ERROR,
-                (errcode(ERRCODE_DATATYPE_MISMATCH),
-                 errmsg("wrong element type")));
-    }
+    /*
+     * ignore element_type stored in binary data. We already know the type.
+     * The stored type can actually be different from the type here, because
+     * OIDs do not necessarily match between different installations.
+     */
+    pq_getmsgint(buf, sizeof(Oid));
 
     for (i = 0; i < ndim; i++)
     {