sql: support Hibernate's use of _pg_expandarray

[justinj]

Adding an index to a table in Hibernate issues a query which makes use of this. Note that supporting this also means that we would need to support record types. The query below should work, if this is implemented:

SELECT (i.keys).n FROM (SELECT information_schema._pg_expandarray(indkey) AS keys FROM pg_index) AS i;

This function takes an array a and returns a set of records that looks like the following:

 x   |  n
----------
a[1] |  1
a[2] |  2
a[3] |  3
a[4] |  4
...

The work is to be decomposed as follows:

• support the set-generating function information_schema._pg_expandarray sql: Add the builtin function _pg_expandarray() #24422
• support simple labeled tuple access
  • add tuple labels sql: add labels to Tuples #25283
  • access different columns in SRF sql: add the ability to access a column from the results of an SRF #24832
• support in-flight composite types sql: support lightweight composite types for in-flight values #24866.
  • propagate labels during typing, including subqueries sql,opt: propagate composite types (labeled tuples) #26621
  • labeled tuple serialization bug sql: tuples not properly preserved during serialization #26624.
  • labeled tuples in distsql sql: distsql does not support labeled tuples #26627.
• verify that all the complex queries listed here and in the srfs logic test file work properly
  • add pg_get_indexdef with 3 arguments sql: support pg_get_indexdef with 3 arguments #26629
  • fix the column pg_index.indclass sql: fix the column pg_index.indclass #26504

[petermattis]

@justinj Supporting record types looks to be a sizable project. Is that accurate? Or can this somehow fallout of the existing system easily?

Cc @knz

[justinj]

I haven't investigated too deeply, but if we only support the result type then I suspect it's not too bad. My guess would be that the most difficult part would probably be handling the . operator in a way that was insulated from name resolution.

[knz]

It's definitely a mid-size project, and somewhat larger than implementing arrays as result-value type.

[jordanlewis]

I just realized that this is more difficult than we thought. Not only do we need to support (or at least pretend to support) record types, we also need to support generator functions in render positions, which we currently don't support at all.

See #13156 for more context.

[jordanlewis]

To be more clear, this is in all reality blocked on correlated subquery support as well!

[knz]

Looking at this briefly on @awoods187's request. Resolution here requires support for record types in in-memory values (datums or whatever will replace them), not db encoding for them (i.e. we don't need to store them). Analogous to the same distinction we used when array support was first implemented.

[petermattis]

@jordanlewis Is the query that hibernate issues identical to the use of _pg_expandarray on other ORMs? Or are there minor syntactic differences. I'm wondering if we can do something even simpler (re: more hacky) than in-memory support for the record type.

[jordanlewis]

They're not all identical. Sometimes they occur in the context of larger queries. Here's another example:

        SELECT
            indexrelid,
            (information_schema._pg_expandarray(indclass)).x AS operator_argument_type_oid,
            (information_schema._pg_expandarray(indclass)).n AS operator_argument_position
        FROM
            pg_index

The only thing we really need to support for this "indexing into" the _pg_expandarray function with the .x syntax, for the x and n fields.

[knz]

Bram and I had an offline discussion and Bram discovered this item has two aspects to it:

• implementing _pg_expandarray. Surprisingly (to me) this is merely a SRF, like the others we already support.
• implementing the notation E.x when E is a SRF call; this is surprisingly simply a notation to access the column x in the table produced by migrating E to the FROM position. This is a natural extension of the x.y column notation and has nothing to do with record types. (I had a "mind blown" moment when I realized the consequences of Bram's discovery)

[knz]

@awoods187 this does mean this item is much easier to implement than what we initially thought, it downgrades it from M-L to S.

[awoods187]

I'll update to a S and am excited to see the results.

[BramGruneir]

I just wanted to add a bit here, there are 3 parts that are required to support the statement above and
be able to close this issue.

• sql: Add the builtin function _pg_expandarray() #24422 Implement the information_schema._pg_expandarray as a Set Returning Function (SRF).
• Add the ability to use the . notation to access a table produced from the migration of a SRF to the FROM position. See @knz's comment above for more details.
• Fix the name resolution to handle SELECT unnest(indkey) FROM pg_index;.

[jordanlewis]

pg_index.indkey is already implemented - was there something else you needed from it?

[BramGruneir]

Hmm...

This works:

+---------+
| indkey  |
+---------+
| 1 2     |
|       1 |
|       1 |
|       1 |
|       1 |
| 1 2 4 3 |
| 1 6     |
| 1 7     |
|       1 |
|       1 |
| 2 3     |
|       1 |
|       5 |
|       4 |
| 1 2     |
| 1 2     |
| 1 2     |
|       1 |
|       2 |
+---------+
(19 rows)

But this doesn't (in my branch with _pg_expandarray working):

SELECT information_schema._pg_expandarray(indkey) FROM pg_index;
pq: column name "indkey" not found

And even this doesn't work:

SELECT unnest(indkey) FROM pg_index;
pq: column name "indkey" not found

[jordanlewis]

Yeah, our name resolution can't handle this case at the moment.

[BramGruneir]

Ok, I'll update that checklist for this case.

[knz]

Another test query from metabase/metabase#5050

SELECT NULL AS TABLE_CAT, 
n.nspname AS TABLE_SCHEM, 
ct.relname AS TABLE_NAME, 
a.attname AS COLUMN_NAME, 
(i.keys).n AS KEY_SEQ, 
ci.relname AS PK_NAME 
FROM pg_catalog.pg_class ct 
JOIN pg_catalog.pg_attribute a ON (ct.oid = a.attrelid) 
JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid)
 JOIN (SELECT i.indexrelid, i.indrelid, i.indisprimary, information_schema._pg_expandarray(i.indkey) AS keys FROM pg_catalog.pg_index i) i ON (a.attnum = (i.keys).x AND a.attrelid = i.indrelid) 
JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid) 
WHERE true AND ct.relname = 'sometablename' AND i.indisprimary
 ORDER BY table_name, pk_name, key_seq

[knz]

Oops, no this is not done yet

[knz]

This is complete now.