Re: ALTER INDEX ... ALTER COLUMN not present in dump

Michael Paquier <michael@paquier.xyz> writes:
> As Alexander and others state on this thread, it looks a bit weird to
> use internally-produced attribute names in those SQL queries, which is
> why the new grammar has been added.  At the same time, it looks more
> solid to me to represent the dumps with those column names instead of
> column numbers.  Tom, Alexander, as you have commented on the original
> thread, perhaps you have an opinion here to share?

The problem is that there's no guarantee that the new server would
generate the same column name for an index column --- and I don't
want to try to lock things down so much that there would be such
a guarantee.  So I'd go with the column-number form.

As an example:

regression=# create table foo (expr int, f1 int, f2 int);
CREATE TABLE
regression=# create index on foo ((f1+f2));
CREATE INDEX
regression=# create index on foo (expr, (f1+f2));
CREATE INDEX
regression=# \d foo
                Table "public.foo"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 expr   | integer |           |          | 
 f1     | integer |           |          | 
 f2     | integer |           |          | 
Indexes:
    "foo_expr_expr1_idx" btree (expr, (f1 + f2))
    "foo_expr_idx" btree ((f1 + f2))

regression=# \d foo_expr_idx
     Index "public.foo_expr_idx"
 Column |  Type   | Key? | Definition 
--------+---------+------+------------
 expr   | integer | yes  | (f1 + f2)
btree, for table "public.foo"

regression=# \d foo_expr_expr1_idx
  Index "public.foo_expr_expr1_idx"
 Column |  Type   | Key? | Definition 
--------+---------+------+------------
 expr   | integer | yes  | expr
 expr1  | integer | yes  | (f1 + f2)
btree, for table "public.foo"

If we were to rename the "foo.expr" column at this point,
and then dump and reload, the expression column in the
second index would presumably acquire the name "expr"
not "expr1", because "expr" would no longer be taken.
So if pg_dump were to try to use that index column name
in ALTER ... SET STATISTICS, it'd fail.

            regards, tom lane