Discussion: support Composite Types

[chenzl25]

Postgresql Composite Types : https://www.postgresql.org/docs/current/rowtypes.html

Should we support Composite Types? I added this issue because some users ask for this feature, even though they can use struct type to workaround.

[st1page]

More specifically, do you mean the support of the CREATE TYPE statement?

[fuyufjh]

because some users ask for this feature

May I ask which feature they want cannot be fulfilled by our struct type? As far as I know most users actually more familiar with struct type than PG's composite types.

[lmatz]

probably they just want to have an alias to reuse the type instead of typing a long struct everytime?

[chenzl25]

May I ask which feature they want cannot be fulfilled by our struct type? As far as I know most users actually more familiar with struct type than PG's composite types.

No feature can't be fulfilled by our struct type currently. They want both; it is more PG compatible and could be much easier to migrate from a Postgresql-like database to RisingWave. Anyway, that is not urgent.

[fuyufjh]

May I ask which feature they want cannot be fulfilled by our struct type? As far as I know most users actually more familiar with struct type than PG's composite types.

No feature can't be fulfilled by our struct type currently. They want both; it is more PG compatible and could be much easier to migrate from a Postgresql-like database to RisingWave. Anyway, that is not urgent.

I think it's possible to provide both syntax (with same implementation).

Actually, there is a missing piece of struct type - alter schema. We know that table can be altered to add new columns, the requirements also apply to struct/row type. To implement this feature, in my mind, eventually the struct type will become more like a table schema or so-called "row type".

If anyone is interested, I am happy to work this out.

[chenzl25]

Alter type: https://www.postgresql.org/docs/current/sql-altertype.html
Indeed. Type could be altered in PostgreSQL.

[BugenZhao]

To be clear, the ways to alter the composite type of a column:

postgres=# create type my_struct as (a int, b boolean);
CREATE TYPE
postgres=# create table my_table (v my_struct);
CREATE TABLE
postgres=# insert into my_table values ((1, false));
INSERT 0 1
postgres=# select * from my_table;
   v   
-------
 (1,f)
(1 row)

• Alter the type to add a new attribute. All columns with that type will be updated.

postgres=# alter type my_struct add attribute c decimal;
ALTER TYPE
postgres=# select * from my_table;
   v    
--------
 (1,f,)
(1 row)

postgres=# insert into my_table values ((2, true, 8.8));
INSERT 0 1
postgres=# select * from my_table;
     v     
-----------
 (1,f,)
 (2,t,8.8)
(2 rows)

postgres=# alter type my_struct drop attribute c restrict;
ALTER TYPE
postgres=# select * from my_table;
   v   
-------
 (1,f)
 (2,t)
(2 rows)

• Create a new type and a user-defined cast function.

postgres=# create type my_struct_with_c as (a int, b boolean, c decimal);
CREATE TYPE

postgres=# CREATE FUNCTION my_struct_to_my_struct_with_c(source my_struct)
  RETURNS my_struct_with_c
  AS $$
  DECLARE
    result my_struct_with_c;
  BEGIN
    result.a := source.a;
    result.b := source.b;
    result.c := 0; -- Set the default value for c
    RETURN result;
  END;
  $$
  LANGUAGE plpgsql;

CREATE CAST (my_struct AS my_struct_with_c)
  WITH FUNCTION my_struct_to_my_struct_with_c(my_struct)
  AS IMPLICIT;
CREATE FUNCTION
CREATE CAST

postgres=# alter table my_table alter column v type my_struct_with_c;
ALTER TABLE
postgres=# select * from my_table;
    v    
---------
 (1,f,0)
 (2,t,0)
(2 rows)

The latter one is more like how we may support "nested" schema change with anonymous composite types. As the affected table is specified and there's no such thing of mutable type catalog, most of the current design for schema change can be reused.

If we're to support CREATE TYPE and ALTER TYPE and adopt the former approach, things may get much complicated IMO. 😕 Alternatively, we can still leave ALTER TYPE unsupported to keep everything immutable. So this primarily becomes a syntax sugar for avoiding the need to define types multiple times. For altering, users still issue ALTER TABLE .. ALTER COLUMN .. TYPE.

[st1page]

For altering, users still issue ALTER TABLE .. ALTER COLUMN .. TYPE.

Also, we might need add some syntax sugar to support it better
In PG we can only do this

    ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]

And we can support

    ALTER [ COLUMN ] column_name TYPE { add | drop} attribute

[github-actions]

This issue has been open for 60 days with no activity. Could you please update the status? Feel free to continue discussion or close as not planned.