From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alexey Bashtanov <bashtanov@imap.cc>
cc: pgsql-bugs@postgresql.org
Subject: Re: pg_dump losing index column collations for unique and primary
 keys
In-reply-to: <b05ce36a-cefb-ca5e-b386-a400535b1c0b@imap.cc>
References: <b05ce36a-cefb-ca5e-b386-a400535b1c0b@imap.cc>
Comments: In-reply-to Alexey Bashtanov <bashtanov@imap.cc>
	message dated "Tue, 03 Dec 2019 16:35:20 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <13712.1575392277.1@sss.pgh.pa.us>
Date: Tue, 03 Dec 2019 11:57:57 -0500
Message-ID: <13713.1575392277@sss.pgh.pa.us>
Precedence: bulk

Alexey Bashtanov <bashtanov@imap.cc> writes:
> I found that pg_dump fails to note down the collations used primary and 
> unique keys supporting indexes.
> To reproduce:

> create table f(a text);
> create unique index f_pkey on f(a collate "C.UTF-8");
> alter table f add primary key using index f_pkey;

Hm.  I kinda think that we should reject the above.  The point of
ADD PRIMARY KEY USING INDEX ought to be to allow you to build
the index concurrently; it should not be something you can use to
create a DDL state that is impossible to arrive at with plain
ADD PRIMARY KEY.

As an example of the sort of problem that I'm worried about,
consider a situation where the index's collation has a different
notion of equality than the column's default collation has.
(Which is entirely possible now that we have nondeterministic
collations.)  That's going to lead to weird restrictions on
whether the index really satisfies query WHERE conditions, and
I'd bet considerable money that we'd have bugs due to failing
to account for that.

In short, I'd say the bug here is not pg_dump's fault at all,
but failure to insist on collation match in ADD PRIMARY KEY
USING INDEX.

			regards, tom lane