Enum types in postgres causes codegen to crash

[masterkidan]

Describe the bug
A clear and concise description of what the bug is.

Environment (please complete the following information):

• OS: [macosx]
• Database: postgres]
• Database driver: [pq, pgx]
• Jet version [v2.9.0]

Code snippet
Query statement and model files of interest.

CREATE TYPE enumtype AS ENUM ('T1', 'T2');

Having a type like the above defined in your schema causes generate to fail with the following error:

jet: pq: more than one row returned by a subquery used as an expression 

The issue is with the query here https://github.com/go-jet/jet/blob/c9e627d3336459fd8c676ee354de05601fdf5c2c/generator/postgres/query_set.go
It returns

 enum.name | values 
-----------+--------
 enumtype  | T1
 enumtype  | T2

I believe the query should be

 SELECT t.typname as "enum.name",                                                                                                                                                                                                                        ARRAY_AGG(e.enumlabel) as "values"                                                                                                                                                                                                                     FROM pg_catalog.pg_type t                                                                                                                                                                                                                                        JOIN pg_catalog.pg_enum e on t.oid = e.enumtypid                                                                                                                                                                                                              JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace                                                                                                                                                                                                   WHERE n.nspname = $1                                                                                                                                                                                                                                    GROUP BY t.typename;

so that you can get the correct types that are being expected here

 enum.name | values  
-----------+---------
 enumtype  | {T1,T2}

Expected behavior
A clear and concise description of what you expected to happen.

This should just work automatically

[go-jet]

Hi @masterkidan ,
I couldn't reproduce the bug. It is probably related to other table types where enum is used, because we have enum generation tested(for instance).
Could you share how enum is used?

[masterkidan]

Thanks for the help, yeah I may have speculated a bit above based on the results of the query. Here's the original table I was using

CREATE TABLE IF NOT EXISTS "pgtest_types" (
  "id" SERIAL PRIMARY KEY  NOT NULL,
  "guid" UUID NOT NULL,
  "created_at" TIMESTAMP NOT NULL,
  "updated_at" TIMESTAMP NOT NULL,
  "v_int32" INTEGER NOT NULL,
  "v_uint32" BIGINT NOT NULL,
  "v_int64" BIGINT NOT NULL,
  "v_uint64" BIGINT NOT NULL,
  "v_string" VARCHAR NOT NULL,
  "v_double" DOUBLE PRECISION NOT NULL,
  "v_float" REAL NOT NULL,
  "v_bool" BOOL NOT NULL,
  "v_bytes" BYTEA NOT NULL,
  "nv_double" DOUBLE PRECISION NULL,
  "nv_float" REAL NULL,
  "nv_int64" BIGINT NULL,
  "nv_uint64" BIGINT NULL,
  "nv_int32" INTEGER NULL,
  "nv_uint32" BIGINT NULL,
  "nv_bool" BOOL NULL,
  "nv_string" VARCHAR NULL,
  "nv_bytes" BYTEA NULL,
  "nv_timestamp" TIMESTAMP NOT NULL,
  "sys_timestamp" TIMESTAMP NOT NULL,
  "sys_uuid" UUID NOT NULL,
  "enum1" INTEGER NOT NULL,
  "nenum2" INTEGER NOT NULL,
  "enum3" enumtype1)

[masterkidan]

Some more information .. the crash seems to happen with this query:

WITH primaryKeys AS (
	SELECT column_name
	FROM information_schema.key_column_usage AS c
		LEFT JOIN information_schema.table_constraints AS t
             ON t.constraint_name = c.constraint_name AND 
                c.table_schema = t.table_schema AND 
                c.table_name = t.table_name
	WHERE t.table_schema = $1 AND t.table_name = $2 AND t.constraint_type = 'PRIMARY KEY'
)
SELECT column_name as "column.Name", 
	   is_nullable = 'YES' as "column.isNullable",
       (EXISTS(SELECT 1 from primaryKeys as pk where pk.column_name = columns.column_name)) as "column.IsPrimaryKey",
	   dataType.kind as "dataType.Kind",	
	   (case dataType.Kind when 'base' then data_type else LTRIM(udt_name, '_') end) as "dataType.Name", 
	   FALSE as "dataType.isUnsigned"
FROM information_schema.columns,
	 LATERAL (select (case data_type
				when 'ARRAY' then 'array'
				when 'USER-DEFINED' then 
					case (select typtype from pg_type where typname = columns.udt_name)
						when 'e' then 'enum'
						else 'user-defined'
					end
				else 'base'
			end) as Kind) as dataType
where table_schema = $1 and table_name = $2
order by ordinal_position;

That happens here

Here's the underlying table its trying to query

db=# \d '113749ab-1a69-44de-b550-79f3945811d9'.'pgtest_types'
                                                           Table "113749ab-1a69-44de-b550-79f3945811d9.pgtest_types"
      Column       |                       Type                       | Collation | Nullable |                                     Default                                     
-------------------+--------------------------------------------------+-----------+----------+---------------------------------------------------------------------------------
 id                | integer                                          |           | not null | nextval('"113749ab-1a69-44de-b550-79f3945811d9".pgtest_types_id_seq'::regclass)
 guid              | uuid                                             |           | not null | 
 created_at        | timestamp without time zone                      |           | not null | 
 updated_at        | timestamp without time zone                      |           | not null | 
 v_int32           | integer                                          |           | not null | 
 v_uint32          | bigint                                           |           | not null | 
 v_int64           | bigint                                           |           | not null | 
 v_uint64          | bigint                                           |           | not null | 
 v_string          | character varying                                |           | not null | 
 v_double          | double precision                                 |           | not null | 
 v_float           | real                                             |           | not null | 
 v_bool            | boolean                                          |           | not null | 
 v_bytes           | bytea                                            |           | not null | 
 nv_double         | double precision                                 |           |          | 
 nv_float          | real                                             |           |          | 
 nv_int64          | bigint                                           |           |          | 
 nv_uint64         | bigint                                           |           |          | 
 nv_int32          | integer                                          |           |          | 
 nv_uint32         | bigint                                           |           |          | 
 nv_bool           | boolean                                          |           |          | 
 nv_string         | character varying                                |           |          | 
 nv_bytes          | bytea                                            |           |          | 
 nv_timestamp      | timestamp without time zone                      |           | not null | 
 sys_timestamp     | timestamp without time zone                      |           | not null | 
 sys_uuid          | uuid                                             |           | not null | 
 enum1             | integer                                          |           | not null | 
 nenum2            | integer                                          |           | not null | 
 enum3             | "113749ab-1a69-44de-b550-79f3945811d9".enumtype1 |           |          | 
 rv_int32          | integer[]                                        |           | not null | 
 rv_uint32         | bigint[]                                         |           | not null | 
 rv_int64          | bigint[]                                         |           | not null | 
 rv_uint64         | bigint[]                                         |           | not null | 
 rv_string         | character varying[]                              |           | not null | 
 rv_double         | double precision[]                               |           | not null | 
 rv_float          | real[]                                           |           | not null | 
 rv_bool           | boolean[]                                        |           | not null | 
 rv_bytes          | bytea[]                                          |           | not null | 
 rnv_timestamp     | timestamp without time zone[]                    |           | not null | 
 rsys_timestamp    | timestamp without time zone[]                    |           | not null | 
 rsys_uuid         | uuid[]                                           |           | not null | 
 renum1            | integer[]                                        |           | not null | 
 rnenum2           | integer[]                                        |           | not null | 
 nv_struct         | jsonb                                            |           |          | 
 nv_date           | date                                             |           |          | 
 publisher_version | jsonb                                            |           | not null | '{"version_part_0": 0, "version_part_1": 0, "version_part_2": 0}'::jsonb
Indexes:
    "pgtest_types_pkey" PRIMARY KEY, btree (id)
    "UQE_pgtest_types_pgtest_types_guid" UNIQUE, btree (guid)

I believe this may be due to the namespace the enum type is created in.

[masterkidan]

The issue is that there are 2 namespaces with the same type

db=# select * from pg_type where typname='enumtype1';
  oid  |  typname  | typnamespace | typowner | typlen | typbyval | typtype | typcategory | typispreferred | typisdefined | typdelim | typrelid | typelem | typarray | typinput | typoutput | typreceive |  typsend  | typmodin | typmodout | typanalyze | typalign | typstorage | typnotnull | typbasetype | typtypmod | typndims | typcollation | typdefaultbin | typdefault | typacl 
-------+-----------+--------------+----------+--------+----------+---------+-------------+----------------+--------------+----------+----------+---------+----------+----------+-----------+------------+-----------+----------+-----------+------------+----------+------------+------------+-------------+-----------+----------+--------------+---------------+------------+--------
 16590 | enumtype1 |        16385 |       10 |      4 | t        | e       | E           | f              | t            | ,        |        0 |       0 |    16589 | enum_in  | enum_out  | enum_recv  | enum_send | -        | -         | -          | i        | p          | f          |           0 |        -1 |        0 |            0 |               |            | 
 16619 | enumtype1 |        16610 |       10 |      4 | t        | e       | E           | f              | t            | ,        |        0 |       0 |    16618 | enum_in  | enum_out  | enum_recv  | enum_send | -        | -         | -          | i        | p          | f          |           0 |        -1 |        0 |            0 |               |            | 
(2 rows)```

[go-jet]

Ok, I've manged to reproduce it. If there is an enum with the same name in two different postgres schemas, generator returns an error.