Обсуждение: strange problem

<p><font face="Arial" size="2">Hi,</font><p><font face="Arial" size="2">I don't know what is going on here. Maybe
somebodycan help.</font><p><font face="Arial" size="2">Iam using postgresql 7.3.</font><br /><p><font face="Arial"
size="2">ici=#select id,pseudonym from user_all where pseudonym = 'autologin_funkey';</font><br /><font face="Arial"
size="2"> id| pseudonym</font><br /><font face="Arial" size="2">----+-----------</font><br /><font face="Arial"
size="2">(0rows)</font><p><font face="Arial" size="2">ici=# select id,pseudonym from user_all where pseudonym ~
'^autologin_funkey$';</font><br/><font face="Arial" size="2">  id  |    pseudonym</font><br /><font face="Arial"
size="2">------+------------------</font><br/><font face="Arial" size="2"> 6881 | autologin_funkey</font><br /><font
face="Arial"size="2">(1 row)</font><p><font face="Arial" size="2">ici=# update user_all set pseudonym = (select
pseudonymfrom user_all where pseudonym ~ '^autologin_funkey$') where pseudonym ~ '^autologin_funkey$';</font><p><font
face="Arial"size="2">UPDATE 1</font><p><font face="Arial" size="2">ici=# select id,pseudonym from user_all where
pseudonym= 'autologin_funkey';</font><br /><font face="Arial" size="2">  id  |    pseudonym</font><br /><font
face="Arial"size="2">------+------------------</font><br /><font face="Arial" size="2"> 6881 |
autologin_funkey</font><br/><font face="Arial" size="2">(1 row)</font><br /><p><font face="Arial" size="2">Sebastian
Böhm</font><br/><font face="Arial" size="2">Friendscout24.de</font> 

"Böhm, Sebastian (Vendor)" <sebastian.boehm@friendscout24.de> writes:
> ici=3D# select id,pseudonym from user_all where pseudonym =3D
> 'autologin_funkey';
>  id | pseudonym
> ----+-----------
> (0 rows)

> ici=3D# select id,pseudonym from user_all where pseudonym ~
> '^autologin_funkey$';
>   id  |    pseudonym
> ------+------------------
>  6881 | autologin_funkey
> (1 row)

That's pretty bizarre.  Can you give a procedure for reproducing this
from a standing start?
        regards, tom lane

<p><font size="2">Hi,</font><p><font size="2">I did reindex, vacuum , dump/import, ... </font><br /><font size="2">I
alsotried 7.3.1</font><p><font size="2">-->> It happens only with btree index on that column, without index it
works,with hash index also no problem.</font><p><font size="2">This happend to 0.3% of all rows in this table, after
dump/importexactly the same rows were affected.</font><p><font size="2">Can somebody tell me how to import a 7.3(.1)
dumpinto 7.2.3 ? </font><br /><font size="2">It gives errors on import, so I cannot try it on 7.2.3.</font><br
/><p><fontsize="2">Hope that helps.</font><p><font size="2">Sebastian Böhm</font><br /><font
size="2">Friendscout24</font><p><fontsize="2">-----Ursprüngliche Nachricht-----</font><br /><font size="2">Von: Tom
Lane[<a href="mailto:tgl@sss.pgh.pa.us">mailto:tgl@sss.pgh.pa.us</a>]</font><br /><font size="2">Gesendet: Mittwoch,
18.Dezember 2002 17:42</font><br /><font size="2">An: "Böhm, Sebastian (Vendor)"</font><br /><font size="2">Cc:
'pgsql-bugs@postgresql.org'</font><br/><font size="2">Betreff: Re: [BUGS] strange problem </font><br /><p><font
size="2">"Böhm,Sebastian (Vendor)" <sebastian.boehm@friendscout24.de> writes:</font><br /><font size="2">>
ici=3D#select id,pseudonym from user_all where pseudonym =3D</font><br /><font size="2">>
'autologin_funkey';</font><br/><font size="2">>  id | pseudonym</font><br /><font size="2">>
----+-----------</font><br/><font size="2">> (0 rows)</font><p><font size="2">> ici=3D# select id,pseudonym from
user_allwhere pseudonym ~</font><br /><font size="2">> '^autologin_funkey$';</font><br /><font size="2">>   id 
|   pseudonym</font><br /><font size="2">> ------+------------------</font><br /><font size="2">>  6881 |
autologin_funkey</font><br/><font size="2">> (1 row)</font><p><font size="2">That's pretty bizarre.  Can you give a
procedurefor reproducing this</font><br /><font size="2">from a standing start?</font><p>                        <font
size="2">regards,tom lane</font> 

"Böhm, Sebastian (Vendor)" <sebastian.boehm@friendscout24.de> writes:
> This happend to 0.3% of all rows in this table, after dump/import exactly
> the same rows were affected.

So you can reproduce the problem after dumping/reloading?  If you could
send me the dump file (off-list!) I'd be willing to investigate here ...
        regards, tom lane

<p><font size="2">Hi,</font><br /><p><font size="2">I installed an newer rpm of the glibc (2.2.2-8.1 / 2.2.2-4 before)
undreinitialized the database with locale=C.</font><p><font size="2">Just after installing the rpm, the problem was
stillthere (reindex I did), but after dump/initdb --locale=C/import the problem was gone.</font><p><font size="2">Dont
knowwheater it is the reinit with new glibc or the reinit with locale=C, dont have the time to figure it out.</font><br
/><fontsize="2">(have to sleep now)</font><p><font size="2">Thank you for your help !</font><p><font size="2">Sebastian
Böhm</font><br/><p><font size="2">-----Ursprüngliche Nachricht-----</font><br /><font size="2">Von: Tom Lane [<a
href="mailto:tgl@sss.pgh.pa.us">mailto:tgl@sss.pgh.pa.us</a>]</font><br/><font size="2">Gesendet: Donnerstag, 19.
Dezember2002 00:18</font><br /><font size="2">An: "Böhm, Sebastian (Vendor)"</font><br /><font size="2">Betreff: Re:
AW:[BUGS] strange problem </font><br /><p><font size="2">Thanks for sending the test data.  Unsurprisingly, I don't see
any</font><br/><font size="2">failure here:</font><p><font size="2">regression=# select id,pseudonym from test4 where
pseudonym= 'autologin_funkey';</font><br /><font size="2">  id  |    pseudonym</font><br /><font
size="2">------+------------------</font><br/><font size="2"> 6881 | autologin_funkey</font><br /><font size="2">(1
row)</font><p><fontsize="2">but I was already suspecting a platform-dependent or locale-dependent</font><br /><font
size="2">problem.</font><p><fontsize="2">Please update to glibc 2.2.3 or later, then rebuild (or at least</font><br
/><fontsize="2">reindex) your tables, and let me know if you can still see a problem.</font><p>                       
<fontsize="2">regards, tom lane</font> 

"Böhm, Sebastian (Vendor)" <sebastian.boehm@friendscout24.de> writes:
> I installed an newer rpm of the glibc (2.2.2-8.1 / 2.2.2-4 before) und
> reinitialized the database with locale=3DC.

> Just after installing the rpm, the problem was still there (reindex I =
> did),
> but after dump/initdb --locale=3DC/import the problem was gone.
> Dont know wheater it is the reinit with new glibc or the reinit with
> locale=3DC, dont have the time to figure it out.

If I am correct in guessing that strcoll is to blame, then setting the
locale to C would have made the problem go away.  I am dubious that
glibc 2.2.2-8 fixes the strcoll bug --- the notes I have say that that
fix appeared in glibc 2.2.3.  However, if you're happy running in C
locale then it doesn't really matter whether strcoll works or not ...
        regards, tom lane