Обсуждение: Propose: enum with all registered table names?

Hello.

Sometimes I want to save in a table reference to another table's name.
E.g. I am creating an accounting system which (on triggers) logs which record is changed and in which table:

CREATE TABLE log(
  tbl XXX,
  id INTEGER,
  blahblah
);

Nowadays XXX may be:
1) Table name. But it is quite inefficient by disk usage. Another bad thing: if I rename a table, I have to rename all rows in log table.
2) Table OID. It is very bad for pg_dump: after the restoration log table will be unusable.
3) ENUM with all table names. But I have to add an element to ENUM each time I create a new table, and, if I rename a table, I have to rename an ENUM element too.

So it would be very useful if Postgres has a special, system ENUM (e.g. pg_catalog.table_names_enum) which holds names of all tables in the database (format: schema.table), and their elements are automatically renamed when a table is renamed.

Dmitry Koterov wrote:
>
> So it would be very useful if Postgres has a special, system ENUM (e.g.
> pg_catalog.table_names_enum) which holds names of all tables in the database
> (format: schema.table), and their elements are automatically renamed when a
> table is renamed.

Too late :-)

It's regclass you're after I think.

=> CREATE TABLE track_tables (t regclass);
=> INSERT INTO track_tables (t) VALUES ('"A"'::regclass);
INSERT 0 1
=> SELECT * FROM track_tables;
  t
-----
 "A"
(1 row)

=> ALTER TABLE "A" RENAME TO atable;
ALTER TABLE
=> SELECT * FROM track_tables;
   t
--------
 atable
(1 row)


--
  Richard Huxton
  Archonet Ltd

Dmitry Koterov wrote:

> CREATE TABLE log(
>   tbl XXX,
>   id INTEGER,
>   blahblah
> );

Use type regclass.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Richard Huxton <dev@archonet.com> writes:
> It's regclass you're after I think.

A fairly large problem with either regclass or a hypothetical
system-maintained enum is that a table with such a column effectively
has a data dependency on all the tables it mentions.  Which pg_dump
won't know about, so it might try to restore that table before it's
finished creating all the mentioned tables.

I think this will actually work alright in existing pg_dump releases
because it does all the table creation DDL before trying to load any
data.  You might have some problems with future parallel pg_restores
though.

On the whole I'd recommend staying away from the idea.  It doesn't
seem valuable enough to be worth taking risks over.

            regards, tom lane