Errored when inserting array value of custom datatype in binary mode .

[thiru-baffle]

Description

We created the custom type. And created the custom_type[] column. While inserting the array values in those columns in binary mode, the server errored with 'no binary input function available for type custom_type'.

If the array values are in the text mode, it works as expected.

Steps to reproduce

1. Create customType.
2. Create customtype[] column in a table.
3. insert into table values ('\0xcustomdatatypeValues,...); -> make sure this data is transferred via binary mode.

Expected outcome

The values to be inserted.

Actual outcome

Error from the server with ErrMessage: 'no binary input function available for type custom_type'.

Analysis

None.

If applicable, please provide logs that demonstrate the issue.
Please remove any sensitive information from the logs.

[leopan3]

Hi, I was not able to reproduce this issue. Using the example from https://github.com/aws/pg_tle/blob/main/docs/09_datatypes.md#create-base-type, an array value can be successfully inserted and retrieved:

-- Create shell type
SELECT pgtle.create_shell_type('public', 'test_citext');
 create_shell_type 
-------------------
 
(1 row)

-- Create I/O functions
CREATE FUNCTION public.test_citext_in(input text) RETURNS bytea AS
$$
  SELECT pg_catalog.convert_to(input, 'UTF8');
$$ IMMUTABLE STRICT LANGUAGE sql;
CREATE FUNCTION public.test_citext_out(input bytea) RETURNS text AS
$$
  SELECT pg_catalog.convert_from(input, 'UTF8');
$$ IMMUTABLE STRICT LANGUAGE sql;
-- Create base type
SELECT pgtle.create_base_type('public', 'test_citext', 'test_citext_in(text)'::regprocedure, 'test_citext_out(bytea)'::regprocedure, -1);
 create_base_type 
------------------
 
(1 row)

-- Create a table using array type
CREATE TABLE test_dt2(c1 test_citext[]);
-- Insert a value
INSERT INTO test_dt2 VALUES ('{test1, test2}');
SELECT * FROM test_dt2;
      c1       
---------------
 {test1,test2}
(1 row)

insert into table values ('\0xcustomdatatypeValues,...); -> make sure this data is transferred via binary mode.

Not sure if this is related, an array value input should be enclosed within curly braces and separated by commas; https://www.postgresql.org/docs/current/arrays.html#ARRAYS-INPUT

Also, the input value has to be a textual (string) representation; a binary input representation is not supported in pg_tle yet. Could you try using a textual (string) representation instead of binary representation as the input and see if that works?

[thiru-baffle]

String representation works. But it doesn't work for binary representation of an array.

The following script will convert the text array to binary representation ( the following code converts array values to binary formats but not always).

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Array;

public class Main {

    public static void main(String[] args) {
        String url = "jdbc:postgresql://localhost:5432/postgres";
        String user = "postgres";
        String password = "your_password";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            String sql = "INSERT INTO test (array_column) VALUES (?)";
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                String[] arrayData = {"Sample data", "Sample data"};
                Array array = conn.createArrayOf("VARCHAR", arrayData);
                pstmt.setArray(1, array);
                pstmt.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

Wireshark capture confirms that data is serialized using binary formats.

[leopan3]

Binary input representation (either an array or individual value) is not supported for a custom data type created by pg_tle API; Only string representative as the input format is supported.

If a binary input representative is important for your application, could you create a feature request and share your use cases so that we may add this feature in a future version?