Note: This is a beta release of Red Hat Bugzilla 5.0. The data contained within is a snapshot of the live data so any changes you make will not be reflected in the production Bugzilla. Also email is disabled so feel free to test any aspect of the site that you want. File any problems you find or give feedback here.

Bug 776

Summary: PostgreSQL 6.3.2-10 bug (Redhat 5.2 distribution)
Product: [Retired] Red Hat Linux Reporter: alvin
Component: postgresqlAssignee: Trond Eivind Glomsrxd <teg>
Status: CLOSED CURRENTRELEASE QA Contact:
Severity: high Docs Contact:
Priority: high    
Version: 5.2CC: alvin, paolo.saggese
Target Milestone: ---   
Target Release: ---   
Hardware: All   
OS: Linux   
URL: http://www.postgresql.org/
Whiteboard:
Fixed In Version: Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 1999-03-22 19:28:39 UTC Type: ---
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:

Description alvin 1999-01-11 05:25:54 UTC
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@cs.usask.ca

Comment 1 Jeff Johnson 1999-01-14 16:15:59 UTC
*** 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@cs.usask.ca

Comment 2 Preston Brown 1999-03-22 19:28:59 UTC
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.