Re: About #13489, array dimensions and CREATE TABLE ... LIKE - Mailing list pgsql-hackers
From | Bruce Momjian |
---|---|
Subject | Re: About #13489, array dimensions and CREATE TABLE ... LIKE |
Date | |
Msg-id | ZVwI_ozT8z9MCnIZ@momjian.us Whole thread Raw |
Responses |
Re: About #13489, array dimensions and CREATE TABLE ... LIKE
|
List | pgsql-hackers |
On Fri, Sep 8, 2023 at 05:10:51PM -0400, Bruce Momjian wrote: > I knew we only considered the array dimension sizes to be documentation > _in_ the query, but I thought we at least properly displayed the number > of dimensions specified at creation when we described the table in psql, > but it seems we don't do that either. > > A big question is why we even bother to record the dimensions in > pg_attribute if is not accurate for LIKE and not displayed to the user > in a meaningful way by psql. > > I think another big question is whether the structure we are using to > supply the column information to BuildDescForRelation is optimal. The > typmod that has to be found for CREATE TABLE uses: > > typenameTypeIdAndMod(NULL, entry->typeName, &atttypid, &atttypmod); > > which calls typenameTypeIdAndMod() -> typenameType() -> LookupTypeName() > -> LookupTypeNameExtended() -> typenameTypeMod(). This seems very > complicated because the ColumnDef, at least in the LIKE case, already > has the value. Is there a need to revisit how we handle type such > cases? (Bug report moved to hackers, previous bug reporters added CCs.) I looked at this some more and found more fundamental problems. We have pg_attribute.attndims which does record the array dimensionality: CREATE TABLE test (data integer, data_array integer[5][5]); SELECT attndims FROM pg_attribute WHERE attrelid = 'test'::regclass AND attname = 'data_array'; attndims ---------- 2 The first new problem I found is that we don't dump the dimensionality: $ pg_dump test ... CREATE TABLE public.test ( data integer, --> data_array integer[] ); and psql doesn't display the dimensionality: \d test Table "public.test" Column | Type | Collation | Nullable | Default ------------+-----------+-----------+----------+--------- data | integer | | | --> data_array | integer[] | | | A report from 2015 reports that CREATE TABLE ... LIKE and CREATE TABLE ... AS doesn't propagate the dimensionality: https://www.postgresql.org/message-id/flat/20150707072942.1186.98151%40wrigleys.postgresql.org and this thread from 2018 supplied a fix: https://www.postgresql.org/message-id/flat/7862e882-8b9a-0c8e-4a38-40ad374d3634%40brandwatch.com though in my testing it only fixes LIKE, not CREATE TABLE ... AS. This report from April of this year also complains about LIKE: https://www.postgresql.org/message-id/flat/ZD%2B14YZ4IUue8Rhi%40gendo.asyd.net Here is the output from master for LIKE: CREATE TABLE test2 (LIKE test); SELECT attndims FROM pg_attribute WHERE attrelid = 'test2'::regclass AND attname = 'data_array'; attndims ---------- --> 0 and this is the output for CREATE TABLE ... AS: CREATE TABLE test3 AS SELECT * FROM test; SELECT attndims FROM pg_attribute WHERE attrelid = 'test3'::regclass AND attname = 'data_array'; attndims ---------- --> 0 The attached patch fixes pg_dump: $ pg_dump test ... CREATE TABLE public.test2 ( data integer, --> data_array integer[][] ); It uses repeat() at the SQL level rather then modifying format_type() at the SQL or C level. It seems format_type() is mostly used to get the type name, e.g. int4[], rather than the column definition so I added brackets at the call site. I used a similar fix for psql output: \d test Table "public.test" Column | Type | Collation | Nullable | Default ------------+-------------+-----------+----------+--------- data | integer | | | --> data_array | integer[][] | | | The 2018 patch from Alexey Bashtanov fixes the LIKE case: CREATE TABLE test2 (LIKE test); \d test2 Table "public.test2" Column | Type | Collation | Nullable | Default ------------+-------------+-----------+----------+--------- data | integer | | | --> data_array | integer[][] | | | It does not fix CREATE TABLE ... AS because it looks like fixing that would require adding an ndims column to Var for WITH NO DATA and adding ndims to TupleDesc for WITH DATA. I am not sure if that overhead is warrented to fix this item. I have added C comments where they should be added. I would like to apply this patch to master because I think our current deficiencies in this area are unacceptable. An alternate approach would be to remove pg_attribute.attndims so we don't even try to preserve dimensionality. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Attachment
pgsql-hackers by date: