Thread: Bug or Short between my brain and the keyboard?
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.
Yep, its a bug. Not sure about the cause, but will look into it in the next few weeks. > > 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)
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. --------------------------------------------------------------------------- > > 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)