Re: Adding SHOW CREATE TABLE

On Wed, Jun 21, 2023 at 8:52 PM Kirk Wolak <wolakk(at)gmail(dot)com> wrote:

> On Mon, Jun 5, 2023 at 7:43 AM Jelte Fennema <postgres(at)jeltef(dot)nl> wrote:
>
>> On Thu, 1 Jun 2023 at 18:57, Kirk Wolak <wolakk(at)gmail(dot)com> wrote:
>> > Can this get turned into a Patch? Were you offering this code up for
>> others (me?) to pull, and work into a patch?
>> > [If I do the patch, I am not sure it gives you the value of reducing
>> what CITUS has to maintain. But it dawns on
>> > me that you might be pushing a much bigger patch... But I would take
>> that, as I think there is other value in there]
>>
>

> Yeah, the Citus code only handles things that Citus supports in
>> distributed tables. Which is quite a lot, but indeed not everything
>> yet. Temporary and inherited tables are not supported in this code
>> afaik. Possibly more. See the commented out
>> EnsureRelationKindSupported for what should be supported (normal
>> tables and partitioned tables afaik).
>>
>
>
Okay, apologies for the long delay on this. I have the code Jelte
submitted working. And I have (almost) figured out how to add the function
so it shows up in the pg_catalog... (I edited files I should not have, I
need to know the proper process... Anyone...)

Not sure if it is customary to attach the code when asking about stuff.
For the most part, it was what Jelte Gave us with a pg_get_tabledef()
wrapper to call...

Here is the output it produces for *select
pg_get_tabledef('pg_class'::regclass); * (Feedback Welcome)

CREATE TABLE pg_class (oid oid NOT NULL, relname name NOT NULL COLLATE "C",
relnamespace oid NOT NULL, reltype oid NOT NULL, reloftype oid NOT NULL,
relowner oid NOT NULL, relam oid NOT NULL, relfilenode oid NOT NULL,
reltablespace oid NOT NULL, relpages integer NOT NULL, reltuples real NOT
NULL, relallvisible integer NOT NULL, reltoastrelid oid NOT NULL,
relhasindex boolean NOT NULL, relisshared boolean NOT NULL, relpersistence
"char" NOT NULL, relkind "char" NOT NULL, relnatts smallint NOT NULL,
relchecks smallint NOT NULL, relhasrules boolean NOT NULL, relhastriggers
boolean NOT NULL, relhassubclass boolean NOT NULL, relrowsecurity boolean
NOT NULL, relforcerowsecurity boolean NOT NULL, relispopulated boolean NOT
NULL, relreplident "char" NOT NULL, relispartition boolean NOT NULL,
relrewrite oid NOT NULL, relfrozenxid xid NOT NULL, relminmxid xid NOT
NULL, relacl aclitem[], reloptions text[] COLLATE "C", relpartbound
pg_node_tree COLLATE "C") USING heap

==
My Comments/Questions:
1) I would prefer Legible output, like below
2) I would prefer to leave off COLLATE "C" IFF that is the DB Default
3) The USING heap... I want to pull UNLESS the value is NOT the default
(That's a theme in my comments)
4) I *THINK* including the schema would be nice?
5) This version will work with a TEMP table, but NOT EMIT "TEMPORARY"...
Thoughts? Is emitting [pg_temp.] good enough?
6) This version enumerates sequence values (Drop always, or Drop if they
are the default values?)
7) Should I enable the pg_get_seqdef() code
8) It does NOT handle Inheritance (Yet... Is this important? Is it okay to
just give the table structure for this table?)
9) I have not tested against Partitions, etc... I SIMPLY want initial
feedback on Formatting

-- Legible:
CREATE TABLE pg_class (oid oid NOT NULL,
relname name NOT NULL COLLATE "C",
relnamespace oid NOT NULL,
reltype oid NOT NULL,
...
reloptions text[] COLLATE "C",
relpartbound pg_node_tree COLLATE "C"
)

-- Too verbose with "*DEFAULT*" Sequence Values:
CREATE TABLE t1 (id bigint GENERATED BY DEFAULT AS IDENTITY *(INCREMENT BY
1 MINVALUE 1 MAXVALUE 9223372036854775807 START WITH 1 CACHE 1 NO CYCLE)*
NOT NULL,
f1 text
) WITH (autovacuum_vacuum_cost_delay='0', fillfactor='80',
autovacuum_vacuum_insert_threshold='-1',
autovacuum_analyze_threshold='500000000',
autovacuum_vacuum_threshold='500000000',
autovacuum_vacuum_scale_factor='1.5')

Thanks,

Kirk...
PS: After I get feedback on Formatting the output, etc. I will gladly
generate a new .patch file and send it along. Otherwise Jelte gets 100% of
the credit, and I don't want to look like I am changing that.