Обсуждение: Non-unique values problem after 'add column'

Hello!

I've encountered a very strange problem with Postgresql 7.3.
I have the following set of queries running in transaction, using
libpqxx 2.6.9:

ALTER TABLE my_table ADD new_column TEXT
UPDATE my_table SET new_column = 'disable'
ALTER TABLE my_table ALTER COLUMN new_column SET DEFAULT 'disable'
CREATE UNIQUE INDEX my_index ON my_table (old_column)

The last query fails with:
ERROR:  Cannot create unique index. Table contains non-unique values

Running the same set of queries manually causes no problems. Moreover,
running the last query in a separate transaction works as well.

Why is a new column affecting unique index creation on an already
existing one? Is there any workaround for this issue?

Thank you,
Dima Kagan

On Nov 26, 2007 3:06 AM,  <dima.kagan@gmail.com> wrote:
> Hello!
>
> I've encountered a very strange problem with Postgresql 7.3.
> I have the following set of queries running in transaction, using
> libpqxx 2.6.9:

7.3 is getting pretty old.  I can't say anything though, I've still
got a 7.4 db hanging around somewhere too. :)

> ALTER TABLE my_table ADD new_column TEXT
> UPDATE my_table SET new_column = 'disable'
> ALTER TABLE my_table ALTER COLUMN new_column SET DEFAULT 'disable'
> CREATE UNIQUE INDEX my_index ON my_table (old_column)
>
> The last query fails with:
> ERROR:  Cannot create unique index. Table contains non-unique values
>
> Running the same set of queries manually causes no problems. Moreover,
> running the last query in a separate transaction works as well.

I'm guessing the difference here is NOT libpqxx, but the data in the
db.  Are you sure you're running this against the exact same data set?
 If so, you need to make a self-contained test that shows this
happening only to libpqxx.  I.e. table def, data, sql executed, etc.

Often times, while making such a self-contained proof, you'll figure
out what's really going on, and it usually isn't a bug in libpqxx at
that point.

dima.kagan@gmail.com writes:
> I've encountered a very strange problem with Postgresql 7.3.

7.3.what?

FWIW, I cannot replicate your problem using 7.3.20.  It doesn't sound
like any bug I can remember, but certainly there have been a lot of
bugs fixed over the past five years.

            regards, tom lane