THIS BUG WAS INCORRECTLY POSTED TO THE REDHAT POWERTOOLS PAGE (BUG #775). HERE IT IS AGAIN, THIS TIME IN THE RIGHT PLACE. Hello, There is a serious bug in the PostgreSQL 6.3.2-10 release that is bundled with Redhat 5.2 (we purchased the MacMillan Digital Publishing version CDs if that matters). This bug (and many others) has already been fixed by the PostgreSQL people, and so it should not be much of a problem for you to release a new RPM package of the latest version, which is PostgreSQL 6.4.2. ======================= 1) The bug in particular that is difficult to work around is a fundamental type conversion problem between text and varchar types. The following simplified SQL example illustrates the problem (run this from a psql prompt): create table atable(data varchar(25)); insert into atable values ('dd/mm/yyyy'); select * from atable; select ((substr(data,7,4) || substr(data,4,2)) || substr(data,1,2)) as newform from atable; update atable set data=((substr(data,7,4) || substr(data,4,2)) || substr(data,1,2)); select * from atable; ================== 2) When run from the Redhat bundled 6.3.2-10, the type conversion error manifests itself... create table atable(data varchar(25)); insert into atable values ('dd/mm/yyyy'); select * from atable; CREATE INSERT 322249 1 data ---------- dd/mm/yyyy (1 row) select ((substr(data,7,4) || substr(data,4,2)) || substr(data,1,2)) as newform from atable; newform -------- yyyymmdd (1 row) update atable set data=((substr(data,7,4) || substr(data,4,2)) || substr(data,1,2)); ERROR: parser: attribute 'data' is of type 'varchar' but expression is of type 'text' select * from atable; data ---------- dd/mm/yyyy (1 row) ================== 3) The same script executed under 6.4.2 demonstrates that this bug has been fixed... create table atable(data varchar(25)); insert into atable values ('dd/mm/yyyy'); select * from atable; CREATE INSERT 632073 1 data ---------- dd/mm/yyyy (1 row) select ((substr(data,7,4) || substr(data,4,2)) || substr(data,1,2)) as newform from atable; newform -------- yyyymmdd (1 row) update atable set data=((substr(data,7,4) || substr(data,4,2)) || substr(data,1,2)); select * from atable; UPDATE 1 data -------- yyyymmdd (1 row) =================== 4) The latest sources are available from the web site http://www.postgresql.org/ but are not available from them in RPM format. =================== 5) Note that it is necessary to dump the database before moving from the 6.3.2 version (using the command documented in the INSTALL file of the 6.4.2 src directory: pg_dumpall -o > db.out After 6.4.2 is installed, the data can be restored using the command: psql -e template1 < db.out ================ Please issue an RPM upgrade for PostgreSQL as soon as possible. Thanks, Alvin Austin alvin.ca
*** Bug 776 has been marked as a duplicate of this bug. *** PLEASE REDIRECT THIS BUG REPORT TO THE PostgreSQL BUILDERS. I could not find this component listed. Hello, There is a serious bug in the PostgreSQL 6.3.2-10 release that is bundled with Redhat 5.2 (we purchased the MacMillan Digital Publishing version CDs if that matters). This bug (and many others) has already been fixed by the PostgreSQL people, and so it should not be much of a problem for you to release a new RPM package of the latest version, which is PostgreSQL 6.4.2. 1) The bug in particular that is difficult to work around is a fundamental type conversion problem between text and varchar types. The following SQL illustrates (run this from a psql prompt): create table atable(data varchar(25)); insert into atable values ('dd/mm/yyyy'); select * from atable; select ((substr(data,7,4) || substr(data,4,2)) || substr(data,1,2)) as newform from atable; update atable set data=((substr(data,7,4) || substr(data,4,2)) || substr(data,1,2)); select * from atable; ================== 2) When run from the Redhat bundled 6.3.2.10, the type conversion error manifests itself... create table atable(data varchar(25)); insert into atable values ('dd/mm/yyyy'); select * from atable; CREATE INSERT 322249 1 data ---------- dd/mm/yyyy (1 row) select ((substr(data,7,4) || substr(data,4,2)) || substr(data,1,2)) as newform from atable; newform -------- yyyymmdd (1 row) update atable set data=((substr(data,7,4) || substr(data,4,2)) || substr(data,1,2)); ERROR: parser: attribute 'data' is of type 'varchar' but expression is of type 'text' select * from atable; data ---------- dd/mm/yyyy (1 row) ================== 3) The same script executed under 6.4.2 demonstrates that this bug has been fixed... create table atable(data varchar(25)); insert into atable values ('dd/mm/yyyy'); select * from atable; CREATE INSERT 632073 1 data ---------- dd/mm/yyyy (1 row) select ((substr(data,7,4) || substr(data,4,2)) || substr(data,1,2)) as newform from atable; newform -------- yyyymmdd (1 row) update atable set data=((substr(data,7,4) || substr(data,4,2)) || substr(data,1,2)); select * from atable; UPDATE 1 data -------- yyyymmdd (1 row) =================== 4) The latest sources are available from the web site http://www.postgresql.org/ but are not available from them in RPM format. =================== 5) Note that it is necessary to dump the database before moving from the 6.3.2 version (using the command documented in the INSTALL file of the 6.4.2 src directory: pg_dumpall -o > db.out After 6.4.2 is installed, the data can be restored using the command: psql -e template1 < db.out ================ Please issue an RPM upgrade for PostgreSQL as soon as possible. Thanks, Alvin Austin alvin.ca
Our 5.9 beta release contains postgresql 6.4.2, which should fix the problem you mention. Our forthcoming official Red Hat Linux 6.0 release will of course have the fix too.