sql: support user-defined composite/record types

[knz]

Maintainer note from @jordanlewis

CockroachDB 23.1 will support user-defined composite types.

---

Special case of #25123.

A composite type represents the structure of a row or record; it is essentially just a list of field names and their data types. PostgreSQL allows composite types to be used in many of the same ways that simple types can be used. For example, a column of a table can be declared to be of a composite type.
For example:

CREATE TYPE complex AS (
    r       double precision,
    i       double precision
);

CREATE TYPE inventory_item AS (
    name            text,
    supplier_id     integer,
    price           numeric
);

Special note:

Whenever a user-defined type is created (with CREATE TYPE), PostgreSQL automatically creates an associated array type, whose name consists of the element type's name prepended with an underscore, and truncated if necessary to keep it less than NAMEDATALEN bytes long. (If the name so generated collides with an existing type name, the process is repeated until a non-colliding name is found.) This implicitly-created array type is variable length and uses the built-in input and output functions array_in and array_out. The array type tracks any changes in its element type's owner or schema, and is dropped if the element type is.

https://www.postgresql.org/docs/10/static/rowtypes.html

Jira issue: CRDB-4943

Epic: CRDB-22358

[mikeczabator-doordasher]

I was directed to this issue when doing a ON CONFLICT ... DO UPDATE SET ... command, but I cannot figure out why it would not work. statement_statistics_log.number_of_times_seen is an INT. Any way around this?

select md5(application_name,key),node_id,application_name,flags,key,anonymized,count,first_attempt_count,max_retries,last_error,rows_avg,rows_var,parse_lat_avg,parse_lat_var,plan_lat_avg,plan_lat_var,run_lat_avg,run_lat_var,service_lat_avg,service_lat_var,overhead_lat_avg,overhead_lat_var,bytes_read_avg,bytes_read_var,rows_read_avg,rows_read_var,implicit_txn
from crdb_internal.node_statement_statistics
ON CONFLICT (query_hash)
DO UPDATE SET most_recent_seen = now(),
statement_statistics_log.number_of_times_seen = statement_statistics_log.number_of_times_seen+1;
invalid syntax: statement ignored: at or near "number_of_times_seen": syntax error: unimplemented: this syntax
SQLSTATE: 0A000
DETAIL: source SQL:
insert into statement_statistics_log(query_hash,node_id,application_name,flags,key,anonymized,count,first_attempt_count,max_retries,last_error,rows_avg,rows_var,parse_lat_avg,parse_lat_var,plan_lat_avg,plan_lat_var,run_lat_avg,run_lat_var,service_lat_avg,service_lat_var,overhead_lat_avg,overhead_lat_var,bytes_read_avg,bytes_read_var,rows_read_avg,rows_read_var,implicit_txn)
select md5(application_name,key),node_id,application_name,flags,key,anonymized,count,first_attempt_count,max_retries,last_error,rows_avg,rows_var,parse_lat_avg,parse_lat_var,plan_lat_avg,plan_lat_var,run_lat_avg,run_lat_var,service_lat_avg,service_lat_var,overhead_lat_avg,overhead_lat_var,bytes_read_avg,bytes_read_var,rows_read_avg,rows_read_var,implicit_txn
from crdb_internal.node_statement_statistics
ON CONFLICT (query_hash)
DO UPDATE SET most_recent_seen = now(),
statement_statistics_log.number_of_times_seen = statement_statistics_log.number_of_times_seen+1
                         ^
HINT: You have attempted to use a feature that is not yet implemented.
See: https://go.crdb.dev/issue-v/27792/v20.2

[jordanlewis]

I think the issue is that the first column can't be qualified. So you'd want DO UPDATE SET number_of_times_seen = statement_statistics_log.number_of_times_seen+1. I think the composite type error comes about because the parser doesn't expect to see a qualified name on the left side of the DO UPDATE SET statement.

[mikeczabator-doordasher]

That did it @jordanlewis. Thanks. I thought I tried every variation of that, but I guess not.

[jwaldrip]

Any update on this feature? This is the last blocker for us moving over to CDB.

[rafiss]

We don't support this syntax yet, but a workaround in the meantime is to rely on the implicit types created by a table definition.

root@localhost:26257/defaultdb> create table my_table_typ(a int, b text, c decimal);
CREATE TABLE

root@localhost:26257/defaultdb> select '(1,cat,3.0)'::my_table_typ;;
  my_table_typ
----------------
  (1,cat,3.0)
(1 row)

[jwaldrip]

@rafiss, let me outline the difference for an implementer trying to migrate from PG.

Composite types:

• It just works when the data is migrated.

Creating a table:

• Refactor the entire codebase to join an additional table to achieve the same result.
• Migrate the data in place to a table-based model inside PG.
• Rewrite the libraries that are working with composite types to work with this "workaround".

Workarounds like the one suggested above are simply non-starters when trying to migrate mature production systems.

[rafiss]

@jwaldrip I only meant to provide a possible workaround in case it unblocks you (or anyone else watching this issue) until we are able to fully address the issue. I agree that it may not be a good solution for migrating a mature production system, but it could work for someone who is prototyping or needs to move forward in a new application, and I did not understand your use case from your initial comment.

[postamar]

Considering that composite types are about to be added, I was wondering if there's room for some convenience built-in functions or even casts to map composite types to JSONB and vice versa.

I don't see anything about this in the postgres docs. Reusing their example:

CREATE TYPE complex AS (
    r       double precision,
    i       double precision
);

Wouldn't it be nice to be able to do SELECT (1.0,1.0)::complex::JSON and get {"r":1.0, "i":1.0} as a result and vice-versa?

[rafiss]

@postamar the Postgres way of doing that is:

postgres=# CREATE TYPE complex AS (
    r       double precision,
    i       double precision
);
CREATE TYPE

postgres=# SELECT row_to_json((1.0,1.0)::complex);
  row_to_json
---------------
 {"r":1,"i":1}
(1 row)

[rafiss]

and this is how you can go back:

postgres=# select json_populate_record(null::complex, row_to_json((1.0,1.0)::complex));
 json_populate_record
----------------------
 (1,1)

CRDB v22.2 includes the json_populate_record builtin. The row_to_json builtin has been there for a while.

[jordanlewis]

CockroachDB 23.1 will include user-defined composite types.

[OndraZizka]

Until this is implemented, what are other options to include 1:N within a table (to avoid an extra table and joining)? Just JSONB?