Re: Vanishing unique constraint

Hello,

On Mon, Jun 1, 2020 at 11:15 AM Bernhard Beroun <bernhard(at)beroun(dot)io> wrote:
>
> Hello,
>
> I am experiencing a strange thing on my production database server, which I can't explain.
>
> On my production database server, there is a table called "label_suggestion" which has a unique constraint on the "name" column. If I do a "\d+ label_suggestion" I can see, that the unique constraint shows up with
>
> "label_suggestion_name_unique" UNIQUE CONSTRAINT, btree (name)
>
> in the output. But when I execute the following query
>
> SELECT name FROM label_suggestion GROUP BY name HAVING COUNT(name) > 1
>
> I can see that there are actually entries with a duplicate name in the database, which makes me believe that the unique constraint isn't there at all. (or at least it's not enforced)

It looks like the underlying index is corrupted. Did you have any
issue on that server? If the datatype is collatable, another
possibility would be that the underlying glibc version (or the
equivalent on your system) was upgraded to a version with different
ordering for your collation. Recently, glibc 2.28 is a quite likely
scenario, see for instance
https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html, but
it could also be some bug, possibly in earlier version if you updated
postgres since the initial install.

> Next, I dumped the complete production database (via pg_dumpall) and applied the dump on my local machine. I did again a "\d+ label_suggestion" and now the unique constraint doesn't show up anymore.

There's probably an error displayed during the restore. The constraint
are restored after the data, so there's no way that the constraint can
be restored if you have duplicated values.

You should definitely find out how you ended up in this situation and
fix the root cause before trying to manually clean up the data.