Failure on postgres when table name is domains

[tjikkun]

When creating a table in postgres with:

CREATE TABLE domains (
    id bigserial NOT NULL,
    post_id integer NOT NULL,
    message character varying(255) NOT NULL,
    category_id integer NOT NULL    
);

I get the following error

2024-03-28 15:43:40.045 UTC [66] ERROR:  column "domain_catalog" does not exist at character 13
2024-03-28 15:43:40.045 UTC [66] STATEMENT:  SELECT "id","domain_catalog","post_id","domain_schema","domain_name","message","data_type","category_id","character_maximum_length","character_octet_length","character_set_catalog","character_set_schema","character_set_name","collation_catalog","collation_schema","collation_name","numeric_precision","numeric_precision_radix","numeric_scale","datetime_precision","interval_type","interval_precision","domain_default","udt_catalog","udt_schema","udt_name","scope_catalog","scope_schema","scope_name","maximum_cardinality","dtd_identifier" FROM "domains"  WHERE "id" = $1

It seems it pulls in column names that are internal to postgres domains.

[mevdschee]

Thank you for reporting this issue.

It seems it pulls in column names that are internal to postgres domains.

Yes, please report the postgres version you are running. Please also report other relevant versions (OS, PHP, etc).

NB: Did you run the tests?

[tjikkun]

Sorry, not my best bug report ever :)
It was postgres 16.2 on debian (docker container) and PHP 8.3.
I found that the query from getTableColumnsSQL is the issue:

SELECT a.attname AS "COLUMN_NAME", case when a.attnotnull then 'NO' else 'YES' end as "IS_NULLABLE", pg_catalog.format_type(a.atttypid, -1) as "DATA_TYPE", case when a.atttypmod < 0 then NULL else a.atttypmod-4 end as "CHARACTER_MAXIMUM_LENGTH", case when a.atttypid != 1700 then NULL else ((a.atttypmod - 4) >> 16) & 65535 end as "NUMERIC_PRECISION", case when a.atttypid != 1700 then NULL else (a.atttypmod - 4) & 65535 end as "NUMERIC_SCALE", '' AS "COLUMN_TYPE" FROM pg_attribute a JOIN pg_class pgc ON pgc.oid = a.attrelid WHERE pgc.relname = 'domains' AND '' <> 'domains' AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum;

This always returns data, even if you don't have a domains table (but when you do have that table this becomes an issue).

It did just ran the tests, they complete fine, but there is no domains table there :)

[mevdschee]

but there is no domains table there :)

This domains thing is something new in PostgreSQL? Do you know what the pg_attribute and pg_class views look like causing this failure? Probably there is some internal table called domains, right?

[mevdschee]

Can you test the latest master?

[mevdschee]

Somehow the namespaces outside public where also evaluated. I restricted all reflection to the 'public' namespace, while I can imagine that people have other namespaces than public, so maybe it needs to be configurable.

[mevdschee]

This shows the namespaces:

php-crud-api=# select distinct relnamespace::regnamespace::text from pg_class;
    relnamespace    
--------------------
 public
 pg_catalog
 pg_toast
 information_schema
(4 rows)

And the namespace that causes the problem:

php-crud-api=# select relnamespace::regnamespace::text from pg_class where relname = 'domains';
    relnamespace    
--------------------
 information_schema
(1 row)

Maybe the excluding condition should be:

relnamespace::regnamespace::text !~ '^pg_|information_schema'

What do you think?

[mevdschee]

Can you test the latest master?

[tjikkun]

Yes, latest master works, nice!

[mevdschee]

Released in v2.14.26

[mevdschee]

@tjikkun Dankjewel voor je bijdrage / Thank you for your contribution!