Thread: 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