Rae Stiening (stiening@cannon.astro.umass.edu) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
union all changes char(3) column definition
Long Description
Rae Stiening stiening@cannon.astro.umass.edu 11/10/2001
This script demonstrates the incorrect restoration of a
table created by a union under postgresql version 7.1.3.
CHANGE
DROP
CREATE
INSERT 2468415409 1
INSERT 2468415410 1
VACUUM
CHANGE
DROP
SELECT
rd_flg | cntr
--------+------
ABC | 1
DEF | 2
(2 rows)
rd_flg | cntr
--------+------
ABC | 1
DEF | 2
ABC | 1
DEF | 2
(4 rows)
DROP
You are now connected as new user postgres.
CREATE
rd_flg | cntr
--------+------
A | 1
D | 2
A | 1
D | 2
(4 rows)
The column rd_flg has not been restored properly. Note that
the column definition has changed from char(3) to character
Sample Code
echo Rae Stiening stiening@cannon.astro.umass.edu 11/10/2001
echo This script demonstrates the incorrect restoration of a
echo table created by a union under postgresql version 7.1.3.
psql -c "revoke select on xyzzy from public" wsdb
psql -c "drop table xyzzy" wsdb
psql -c "create table xyzzy (rd_flg char(3),cntr integer)" wsdb
psql -c "insert into xyzzy values('ABC',1)" wsdb
psql -c "insert into xyzzy values('DEF',2)" wsdb
psql -c "vacuum analyze xyzzy" wsdb
psql -c "revoke select on zzxxyy from public" wsdb
psql -c "drop table zzxxyy" wsdb
psql -c "create table zzxxyy as
select * from xyzzy
union all
select * from xyzzy" wsdb
psql -c "select * from xyzzy" wsdb
psql -c "select * from zzxxyy" wsdb
pg_dump -t zzxxyy wsdb > zzxxyy.tbl
psql -c "drop table zzxxyy" wsdb
cat zzxxyy.tbl | psql wsdb
psql -c "select * from zzxxyy" wsdb
echo The column rd_flg has not been restored properly. Note that
echo the column definition has changed from char'('3')' to character
No file was uploaded with this report