RE: [HACKERS] Bug or Short between my brain and the keyboard?

> Can someone comment on this error message? It certainly looks like a
> bug, but I can't figure out why he is getting these problems.
Well, I comment... :) That message is old... I sent that before 6.3.2
came out, but 6.3.2 suffers from the same problem. Just tested it on a
6.3.2 tar build (no patches) on a Red Hat 5.0 system. Statements run:
create table list (k int2);
insert into list values (1);
insert into list select max(k)+1;
insert into list select max(k)+1;
insert into list select max(k)+1;
insert into list select max(k)+1;
insert into list select max(k)+1;
select * from list;
create table list2 (k1 int2 NOT NULL, k2 int2 NOT NULL);
create UNIQUE INDEX l1 ON list2(k1, k2);
create UNIQUE INDEX l2 ON list2(k2, k1);
insert into list2 select l1.k, l2.k from list as l1, list as l2;
select * from list2;
vacuum verbose analyze list2;
cluster l1 on list2;
cluster l2 on list2;

Try it, try it. }8-> (I'm a devil or a cow can't remember which.)

-DEJ

> ----------------------------------------------------------------------
> -----
>
>
> >
> > Just thought I'd try the cluster command. What am I doing wrong.
> > ReadHat 5.0
> > 6.3.1 rpm's
> >
> > [djackson(at)www]$ psql template1
> > Welcome to the POSTGRESQL interactive sql monitor:
> > Please read the file COPYRIGHT for copyright terms of POSTGRESQL
> >
> > type \? for help on slash commands
> > type \q to quit
> > type \g or terminate with semicolon to execute query
> > You are currently connected to the database: template1
> >
> > template1=> \d
> > Couldn't find any tables, sequences or indices!
> > template1=> \l
> > datname |datdba|datpath
> > ---------+------+---------
> > template1| 100|template1
> > postgres | 100|postgres
> > (2 rows)
> >
> > template1=> create database test;
> > CREATEDB
> > template1=> \connect test
> > connecting to new database: test
> > test=> create table list (k int2);
> > CREATE
> > test=> insert into list values (1);
> > INSERT 33769 1
> > test=> insert into list select max(k)+1;
> > .
> > .
> > .
> > test=> select * from list;
> > k
> > -
> > 1
> > 2
> > 3
> > 4
> > 5
> > 6
> > (6 rows)
> >
> > test=> create table list2 (k1 int2 NOT NULL, k2 int2 NOT NULL);
> > CREATE
> > test=> create UNIQUE INDEX l1 ON list2(k1, k2);
> > CREATE
> > test=> create UNIQUE INDEX l2 ON list2(k2, k1);
> > CREATE
> > test=> insert into list2 select l1.k, l2.k from list as l1, list as
> l2;
> > INSERT 0 36
> > test=> select * from list2;
> > k1|k2
> > --+--
> > 1| 1
> > 2| 1
> > 3| 1
> > .
> > .
> > .
> > 4| 6
> > 5| 6
> > 6| 6
> > (36 rows)
> >
> > test=> vacuum verbose analyze list2;
> > NOTICE: Rel list2: Pages 1: Changed 0, Reapped 0, Empty 0, New 0;
> Tup
> > 36: Vac 0, Crash 0, UnUsed 0, MinLen 44, MaxLen 44; Re-using:
> > Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. Elapsed 0/0 sec.
> > NOTICE: Ind l2: Pages 2; Tuples 36. Elapsed 0/0 sec.
> > NOTICE: Ind l1: Pages 2; Tuples 36. Elapsed 0/0 sec.
> > VACUUM
> > test=> cluster l1 on list2;
> > ERROR: Cannot create unique index. Table contains non-unique values
> > test=> cluster l2 on list2;
> > PQexec() -- Request was sent to backend, but backend closed the
> channel
> > before responding.
> > This probably means the backend terminated abnormally before
> or
> > while processing the request.
> >
> >
>
>
> --
> Bruce Momjian | 830 Blythe Avenue
> maillist(at)candle(dot)pha(dot)pa(dot)us | Drexel Hill, Pennsylvania
> 19026
> + If your life is a hard drive, | (610) 353-9879(w)
> + Christ can be your backup. | (610) 853-3000(h)