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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Michael Paquier <michael(at)paquier(dot)xyz>
Cc: Amul Sul <sulamul(at)gmail(dot)com>, pgsql-hackers(at)lists(dot)postgresql(dot)org, a(dot)korotkov(at)postgrespro(dot)ru
Subject: Re: ALTER INDEX ... ALTER COLUMN not present in dump
Date: 2018-12-17 05:24:15
Message-ID: 8776.1545024255@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

Michael Paquier <michael(at)paquier(dot)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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message amul sul 2018-12-17 05:29:08 Re: ALTER INDEX ... ALTER COLUMN not present in dump
Previous Message Michael Paquier 2018-12-17 05:14:39 Re: ALTER INDEX ... ALTER COLUMN not present in dump

Browse pgsql-hackers by date

  From Date Subject
Next Message amul sul 2018-12-17 05:29:08 Re: ALTER INDEX ... ALTER COLUMN not present in dump
Previous Message Michael Paquier 2018-12-17 05:14:39 Re: ALTER INDEX ... ALTER COLUMN not present in dump