RE: [HACKERS] Bug or Short between my brain and the keyboard? - Mailing list pgsql-hackers

> 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@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@candle.pha.pa.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)