PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID16485
PG Version12.2
OSUbuntu 18.04
Opened2020-06-08 08:33:42+00
Reported byJelte Fennema

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      16485
Logged by:          Jelte Fennema
Email address:      (redacted)
PostgreSQL version: 12.2
Operating system:   Ubuntu 18.04

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,
\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
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:
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
index 8fcdf82922..81a61f7bc7 100644
--- a/src/backend/utils/adt/arrayfuncs.c
+++ b/src/backend/utils/adt/arrayfuncs.c
@@ -1267,10 +1267,9 @@ Datum
 	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)
 				 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,
-				 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++)


2020-06-08 08:33:42+00PG Bug reporting formBUG #16485: array_recv causes binary COPY to not work between postgres instances with arrays of custom types