Re: Strange inconsistency with UPDATE

On 17/08/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Phoenix Kiula" <phoenix(dot)kiula(at)gmail(dot)com> writes:
> > However, I see some inconsisent behavior from Postgresql. When I issue
> > an UPDATE command , it shows me a duplicate violation (which could be
> > correct) --
>
> > -# update TABLE set ACOLUMN = lower(ACOLUMN);
> > ERROR: duplicate key violates unique constraint "TABLE_ACOLUMN_key"
>
> > So I try to find out the offending values of this ACOLUMN that become
> > duplicated when lower(ACOLUMN) is issued:
>
> > -# SELECT lower(ACOLUMN), count(*) FROM TABLE
> > GROUP BY lower(ACOLUMN) HAVING count(*) > 1 ;
> > -------+-------
> > lower | count
> > -------+-------
> > (0 rows)
>
> Yeah, that *is* pretty bizarre.
>
> We have seen some cases where strcoll() yields inconsistent answers
> (leading to arbitrarily silly behavior on Postgres' part) if it is
> expecting a character set encoding different from what Postgres is
> using. What is your lc_collate setting, and are you sure it matches
> the database encoding?

- lc_collate is "en_US.UTF-8"
- database encoding is "utf-8"

> Another possibility is that there's something corrupt about the
> TABLE_ACOLUMN_key index ... does reindexing it change the outcome?

Hmm, I can check. It's just a unique index. Should I drop it and recreate?

Last time I ran a massive "UPDATE mytable SET mycol = lower(mycol)"
query on about 6 millions records, the database seemed to be locked
for eternity. Nothing could insert into it, nor update something else.
Is this what is discussed on this list as "deadlock"? How can I avoid
this if I were to reindex and such?

Thanks!