java.sql.Struct type support

[RomanHargrave]

It looks like PGJDBC does not support mapping user-defined types by means of java.sql.Struct, which seems to me to be the JVM-standard means of defining user types, and is far more orthagonal than PGObject, which requires that the user be able to parse object literals.

It would be great if Postgres-JDBC were to support using java.sql.Struct unless some manner of critical roadblock stands in the way.

[RomanHargrave]

I would also like to add that this makes it nearly impossible to support custom types when using PGJDBC with most connection poolers, especially HikariCP, which does not offer an interface to access the real connection object (needed to inform PGJDBC of a custom PGobject).

[vlsi]

@RomanHargrave , are you sure HikariCP does not allow to access PGConnection?
I think there is a standard JDBC way of doing that: connection.unwrap(PGConnection.class);

According to brettwooldridge/HikariCP#85 unwrap should work.

It would be great if Postgres-JDBC were to support using java.sql.Struct

The tricky part there is

1. Struct can be nested (i.e. a field of struct A might be some other struct B). I know only makes your life of manual encoding of a complex struct to string harder. However, it makes hard to implement efficient API of processing Structs.
2. "well-known types" are somewhat hard-coded in the pgdjbc. There is no easy API like "oh, I want to serialize Object x as pg type y, give me a converter please". Not to mention "API so user-provided types can be plugged in"
3. There two ways to serialize/deserialize data: text and binary while different backend versions might support different formats in binary
4. Everybody wants stuff like getInt, getTimestamp efficient. You don't really want all those abstractions like "type dictionary, etc" stand in the way.
5. We would want supporting different JDBC versions without rewriting all the code (e.g. support of java.time API in JDK8 in setObject and being able to execute in JDK7 without NoClassDefFoundErrors)

I've already tried to tackle this problem and I do not see an elegant way of solving that.
What I have in mind up to the point is it would be nice to have some DSL (e.g. https://www.jetbrains.com/mps/) so we can declare "valid" type conversions in a, well, declarative way and let some kind of code generator to generate boilerplate code.

If you have some time, it would be nice if you could share your thoughts.

[RomanHargrave]

connection.unwrap

I am aware of this, unfortunately my current SQL stack only supports initializing the connection pooler and providing it the the query abstraction layer (I could easily change this, I suppose). That being said, it would be better if some manner of pluggable factory were supported by the pooler spec, but alas, this is not in any way related to PGJDBC.

I solved my issue simply by taking the simple implementation of PGobject returned by PG for types it does not have a PGobject registered for and then converting it. I do not plan to perform a large amount of transactions involving this type in the application using PGJDBC, so the extra step is no issue.

That being said, I can see how the problems you mentioned would be roadblocks to implementing Struct support.

My need for Struct came primarily out of some frustration with implementing a Java/Scala representation of a composite type. Due to the predictable nature of composite type literals, PGobject could be subclassed to create a composite type object that can programmatically compute a Postgres composite literal from a sequence of member values, and conversely, extract, and assign the strictest applicable type to, member values extracted from a composite literal.

As long as this implementation knows the types of the composite's fields in the order that they occur, it should be relatively simple to parse it given that Postgres stringifies composites in the form (value1,value2,...). This could be achieved in a variety of ways. The only potential issue that comes to mind is members whose string representation contain a comma.

That being said, while I have some idea of how the driver behaves, I cannot say I know every detail for sure, at least with regards to what values it might hand a PGobect to translate.

[yawaramin]

@RomanHargrave

The only potential issue that comes to mind is members whose string representation contain a comma.

AFAICT Postgres automatically wraps those strings in double quotes. It doesn't wrap strings which don't contain commas though. So you would have to deal with both scenarios.

[taojoe]

anythings update?

[mdoering]

Having to parse and generate Postgres compliant strings is not nice. Please support Struct!

[davecramer]

One of the challenges is that we also have to create the corresponding struct compatible with your code. Is there anything we can do to make it easier for you such as a helper function for compliant strings ?

[mdoering]

I do have working code, so it's not a show stopper for me. But I did struggle to find documentation on how to convert to and from PgObject. Helper functions to convert (foo,3,bar) into String[] and back again with (un)escaping of quotes would be a simple, but appreciated support.

[davecramer]

care to share some examples ? even privately

[mdoering]

I am using MyBatis and have written an abstract TypeHandler for custom postgres types as well as another one for arrays of custom types.

[davecramer]

for https://github.com/CatalogueOfLife/backend/blob/c9ae8cb668dd347cf49c93bbece257432ff997bb/dao/src/main/java/life/catalogue/db/type2/CustomAbstractTypeHandler.java#L52
Are you aware of the $ quoting option for postgres. https://www.postgresql.org/docs/13/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING

[mdoering]

Thanks, I wasn't. But it does not seem to be supported when passing PgObject values:

Caused by: org.postgresql.util.PSQLException: ERROR: malformed record literal: "($$Œre-Fölíñgé$$,$$O'Hara$$,$$oere@foo.bar$$,$$1234,"5678".90/x$$)"

When I use quotes for each value like this the dollars get included in the value and are not removed

("$$Œre-Fölíñgé$$","$$O'Hara$$","$$oere@foo.bar$$","$$1234,"5678".90/x$$")

[davecramer]

Send me a small test case. Pretty sure this should work.

[mdoering]

import org.postgresql.jdbc.PgConnection;
import org.postgresql.util.PGobject;

import java.sql.*;
import java.util.Arrays;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * Create a database with this DDL:
 * CREATE TYPE person AS (given text, family text, email text, orcid text);
 * CREATE TABLE company (key int primary key, contact person);
 */
public class PgObjectTest {

  static AtomicInteger id = new AtomicInteger(0);

  static PGobject buildPgObject(String typeName, Function<String, String> escapeFunc, String... cols) throws SQLException {
    PGobject pgObject = new PGobject();
    pgObject.setType(typeName);
    String value = Arrays.stream(cols)
      .map(escapeFunc)
      .collect(Collectors.joining(","));
    pgObject.setValue("(" + value + ")");
    return pgObject;
  }

  /**
   * Quotes all values to not worry about commas.
   * Escapes quotes by doubling them.
   * NULLs become empty strings.
   */
  static String pgEscape(String x) {
    return x == null ? "" : '"' + x.replaceAll("\"", "\"\"") + '"';
  }

  static String pgEscapeDollar(String x) {
    return x == null ? "" : "\"$$" + x + "\"$$";
  }

  static void testEscapeFunc(PreparedStatement insert, PreparedStatement read, Function<String, String> escapeFunc) throws SQLException {
    PGobject pgo = buildPgObject("person", escapeFunc,"O'Hara", "Œre-Fölíñgé", "Maxi\t<oere@foo.bar>\nhidden", "1234,\"5678\".90/x");
    insert.setInt(1, id.incrementAndGet());
    insert.setObject(2, pgo);
    insert.execute();
    System.out.println("\nInserted "+id);

    read.setInt(1, id.get());
    ResultSet rs = read.executeQuery();
    rs.next();
    printCol(rs, 1);
    printCol(rs, 2);
    printCol(rs, 3);
    printCol(rs, 4);
  }

  static void printCol(ResultSet rs, int i) throws SQLException {
    System.out.println("-----");
    System.out.println(rs.getString(i));
  }

  public static void main(String[] args) throws Exception{
    try (PgConnection c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/test", "postgres", "").unwrap(PgConnection.class)) {
      try (
        PreparedStatement insert = c.prepareStatement("INSERT INTO company (key, contact) VALUES (?,?)");
        PreparedStatement read = c.prepareStatement("SELECT (contact).given, (contact).family, (contact).email, (contact).orcid FROM company WHERE key=?");
        Statement st = c.createStatement()
      ) {
        st.execute("TRUNCATE company");
        testEscapeFunc(insert, read, PgObjectTest::pgEscape);
        testEscapeFunc(insert, read, PgObjectTest::pgEscapeDollar);
      }
    }
  }
}

[mdoering]

This runs the same insert and reads with 2 different escape functions.
With postgresql-42.2.16.jar this yields:

Inserted 1
-----
O'Hara
-----
Œre-Fölíñgé
-----
Maxi	<oere@foo.bar>
hidden
-----
1234,"5678".90/x

Inserted 2
-----
$$O'Hara$$
-----
$$Œre-Fölíñgé$$
-----
$$Maxi	<oere@foo.bar>
hidden$$
-----
$$1234,5678.90/x$$

[davecramer]

So ya, the $ quoting doesn't work like I thought it would.

[azolotko]

@davecramer Could you please explain why the issue was closed? Has it been somehow resolved? Or is it just a "won't fix"?

[kirillsalykin]

I am also curious why the issue closed. Seems like a needed feature.

[davecramer]

basically, it's very difficult with the current codebase. Feel free to create a PR