cluster

From ChrUllrich@gmx.de Fri Jun 18 15:01:15 1999
Received: from mail2.gmx.net (qmailr@mail2.gmx.net [194.221.183.62])
	by candle.pha.pa.us (8.9.0/8.9.0) with SMTP id PAA02123
	for <maillist@candle.pha.pa.us>; Fri, 18 Jun 1999 15:01:13 -0400 (EDT)
Received: (qmail 1465 invoked by uid 0); 18 Jun 1999 18:40:59 -0000
Received: from pc19f7c08.dip.t-online.de (HELO christian.ullrich.net) (193.159.124.8)
  by mail2.gmx.net with SMTP; 18 Jun 1999 18:40:59 -0000
Received: (from chris@localhost)
	by christian.ullrich.net (8.9.3/8.9.3) id UAA02806
	for maillist@candle.pha.pa.us; Fri, 18 Jun 1999 20:33:54 +0200
Date: Fri, 18 Jun 1999 20:33:54 +0200
From: Christian Ullrich <ChrUllrich@gmx.de>
To: Bruce Momjian <maillist@candle.pha.pa.us>
Subject: Re: [GENERAL] Two questions about 6.5
Message-ID: <19990618203354.A2716@christian.ullrich.net>
References: <19990617204118.B8536@christian.ullrich.net> <199906181631.MAA29003@candle.pha.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
X-Mailer: Mutt 0.95.6i
In-Reply-To: <199906181631.MAA29003@candle.pha.pa.us>; from Bruce Momjian on Fri, Jun 18, 1999 at 12:31:17PM -0400
Status: RO

On Fri, Jun 18, 1999 at 12:31:17PM -0400, Bruce Momjian wrote:

> Does this work for you?  It works here:
> 
> 	test=> create table te(x int, y int);
> 	CREATE
> 	test=> create unique index i_te on te(x,y);
> 	CREATE
> 	test=>  cluster i_te on te;
> 	CLUSTER

It works. But why doesn't it work on my tables:

create table prautor(
nr int4,
pi int4,
primary key(nr,pi));

create table prauflage(
nr int4,
au int4,
pl text,
be text,
st int4,
vh bool,
ex int4,
primary key(nr,au));

prd=> create unique index i_te on prauflage(nr,au);
CREATE
prd=> cluster i_te on prauflage;
ERROR:  Cannot create unique index. Table contains non-unique values           

-- 
Christian Ullrich
Registrierter Linux-User #125183

"Sie knnen nach R'ed'mond fliegen -- aber Sie werden sterben"

From owner-pgsql-hackers@hub.org Thu Apr 16 15:00:41 1998
Received: from hub.org (hub.org [209.47.148.200])
	by candle.pha.pa.us (8.8.5/8.8.5) with ESMTP id PAA25361
	for <maillist@candle.pha.pa.us>; Thu, 16 Apr 1998 15:00:36 -0400 (EDT)
Received: from localhost (majordom@localhost) by hub.org (8.8.8/8.7.5) with SMTP id OAA28081; Thu, 16 Apr 1998 14:57:37 -0400 (EDT)
Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Thu, 16 Apr 1998 14:57:13 -0400 (EDT)
Received: (from majordom@localhost) by hub.org (8.8.8/8.7.5) id OAA28031 for pgsql-hackers-outgoing; Thu, 16 Apr 1998 14:57:03 -0400 (EDT)
Received: from bjke.com (firewall-user@ganymede.bjke.com [144.210.8.38]) by hub.org (8.8.8/8.7.5) with ESMTP id OAA27987 for <pgsql-hackers@hub.org>; Thu, 16 Apr 1998 14:56:23 -0400 (EDT)
Received: by bjke.com; id OAA29631; Thu, 16 Apr 1998 14:56:06 -0400 (EDT)
Received: from cpsmail.cpsgroup.com(144.210.12.11) by gauntlet.bjke.com via smap (3.2)
	id xma029416; Thu, 16 Apr 98 14:55:54 -0400
Received: by dal_cps.cpsgroup.com with Internet Mail Service (5.0.1458.49)
	id <H40Q5ZTY>; Thu, 16 Apr 1998 13:56:28 -0500
Message-ID: <F10BB1FAF801D111829B0060971D839F2330CA@dal_cps.cpsgroup.com>
From: "Jackson, DeJuan" <djackson@cpsgroup.com>
To: PostgreSQL Hackers Mailing List <pgsql-hackers@hub.org>
Cc: PostgreSQL Questions Mailing List <pgsql-questions@postgreSQL.org>
Subject: [HACKERS] Bug or Short between my brain and the keyboard?
Date: Thu, 16 Apr 1998 13:56:27 -0500
X-Priority: 3
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.0.1458.49)
Content-Type: text/plain
Sender: owner-pgsql-hackers@hub.org
Precedence: bulk
Status: RO

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.


From owner-pgsql-ports@hub.org Tue Jun 22 21:03:40 1999
Received: from hub.org (hub.org [209.167.229.1])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id VAA00995
	for <maillist@candle.pha.pa.us>; Tue, 22 Jun 1999 21:03:38 -0400 (EDT)
Received: from hub.org (hub.org [209.167.229.1])
	by hub.org (8.9.3/8.9.3) with ESMTP id VAA24665;
	Tue, 22 Jun 1999 21:00:15 -0400 (EDT)
	(envelope-from owner-pgsql-ports@hub.org)
Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Tue, 22 Jun 1999 20:57:28 +0000 (EDT)
Received: (from majordom@localhost)
	by hub.org (8.9.3/8.9.3) id UAA24107
	for pgsql-ports-outgoing; Tue, 22 Jun 1999 20:57:27 -0400 (EDT)
	(envelope-from owner-pgsql-ports@postgreSQL.org)
X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-ports@postgreSQL.org using -f
Received: (from nobody@localhost)
	by hub.org (8.9.3/8.9.3) id UAA24081;
	Tue, 22 Jun 1999 20:57:21 -0400 (EDT)
	(envelope-from nobody)
Date: Tue, 22 Jun 1999 20:57:21 -0400 (EDT)
From: Unprivileged user <nobody@hub.org>
Message-Id: <199906230057.UAA24081@hub.org>
To: pgsql-ports@postgreSQL.org
Reply-to: Vladimir Dobrokhotov <dvs@rybvod.kamchatka.su>
Subject: [PORTS] Port Bug Report: 1.backend crash when frontend do cluster (sometimes!?), 2.cluster does not work
Sender: owner-pgsql-ports@postgreSQL.org
Precedence: bulk
Status: ROr


============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name               : Vladimir Dobrokhotov
Your email address      : dvs@rybvod.kamchatka.su

Category                : runtime: back-end
Severity                : critical

Summary: 1.backend crash when frontend do cluster (sometimes!?), 2.cluster does not work

System Configuration
--------------------
  Operating System   : FreeBSD 2.2.5-RELEASE

  PostgreSQL version : 6.5

  Compiler used      : gcc 2.7.2.1

Hardware:
---------
Pentium, 32 RAM

Versions of other tools:
------------------------
gmake 3.75, flex 2.5.4

--------------------------------------------------------------------------

Problem Description:
--------------------
This problem come from pg6.2.
cluster (unique index btree(4 filelds varchar(4))) not work - "cannot create unique index. Table contains non-unique values."
In pg6.4.2 command "cluster ind_un.." destroy all indexes on the table but index "ind_un" detroy fields form 2...
In pg6.5 - random one from:
1) ERROR: cannot create unique index. Table contains non-unique values.
2) pgReadData() -- backend closed ... (need restart frontend)
3) pgReadData() -- backend closed ... (need restart backend, frontend) 
4) pgReadData() -- backend closed ... (need restart backend, frontend, remove file "temp_1ddbc".) 


--------------------------------------------------------------------------

Test Case:
----------
=>create table sns (n1 varchar(4), n2 varchar(4), n3 varchar(4), n4 varchar(4), name text);
CREATE
=>insert into sns values ('0','00','','','a');
INSERT 278624 1
=>insert into sns values ('0','01','','','b');
INSERT 278626 1
=>create unique index sn_b on sns using btree(n1,n2,n3,n4);
CREATE
=>cluster sn_b on sns;
ERROR: Cannot create unique index. Table contains non-unique values
=>cluster sn_b on sns;
pgReadData() -- backend closed the channel unexpectedly.
   This probably means the backend terminated abnormally
   before or while processing the request.
We have lost the connection to backend, so further processing is impossible.
 Terminating.
#psql dv14
Connection to database 'dv14' failed.
connectDB() -- ...
#su postgres
$~/rc.local
 postmaster.
^D#psql dv14
Welcome to the POSTGRES interactive sql monitor.
...
=>cluster sn_b on sns;
ERROR: cannot create temp_44050
(rm ~/data/base/dv14/temp_44050)
=>cluster sn_b on sns;
ERROR: Cannot create unique index. Table contains non-unique values
=>cluster sn_b on sns;
pgReadData() -- backend closed the channel unexpectedly.
   This probably means the backend terminated abnormally
   before or while processing the request.
We have lost the connection to backend, so further processing is impossible.
 Terminating.
#psql dv14
Welcome to the POSTGRES interactive sql monitor.
...
=>


--------------------------------------------------------------------------

Solution:
---------


--------------------------------------------------------------------------



From owner-pgsql-hackers@hub.org Sun Aug 29 03:58:30 1999
Received: from renoir.op.net (root@renoir.op.net [209.152.193.4])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id DAA20206
	for <maillist@candle.pha.pa.us>; Sun, 29 Aug 1999 03:58:29 -0400 (EDT)
Received: from hub.org (hub.org [216.126.84.1]) by renoir.op.net (o1/$Revision: 1.3 $) with ESMTP id DAA00347 for <maillist@candle.pha.pa.us>; Sun, 29 Aug 1999 03:44:32 -0400 (EDT)
Received: from hub.org (hub.org [216.126.84.1])
	by hub.org (8.9.3/8.9.3) with ESMTP id DAA91360;
	Sun, 29 Aug 1999 03:35:58 -0400 (EDT)
	(envelope-from owner-pgsql-hackers@hub.org)
Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Sun, 29 Aug 1999 03:31:06 +0000 (EDT)
Received: (from majordom@localhost)
	by hub.org (8.9.3/8.9.3) id DAA90785
	for pgsql-hackers-outgoing; Sun, 29 Aug 1999 03:30:17 -0400 (EDT)
	(envelope-from owner-pgsql-hackers@postgreSQL.org)
Received: from flex.flex.ro (IDENT:root@[193.230.255.4])
	by hub.org (8.9.3/8.9.3) with ESMTP id DAA90462
	for <pgsql-hackers@postgreSQL.org>; Sun, 29 Aug 1999 03:29:38 -0400 (EDT)
	(envelope-from teo@flex.ro)
Received: from flex.ro (IDENT:teo@teo.flex.ro [193.230.255.3])
	by flex.flex.ro (8.9.3/8.9.3) with ESMTP id KAA30242
	for <pgsql-hackers@postgreSQL.org>; Sun, 29 Aug 1999 10:30:47 +0300
Message-ID: <37C8E2FE.5DBA4389@flex.ro>
Date: Sun, 29 Aug 1999 07:36:30 +0000
From: Constantin Teodorescu <teo@flex.ro>
Organization: FLEX Consulting Braila
X-Mailer: Mozilla 4.61 [en] (X11; I; Linux 2.2.11 i586)
X-Accept-Language: en
MIME-Version: 1.0
To: "pgsql-hackers@postgreSQL.org" <pgsql-hackers@postgreSQL.org>
Subject: [HACKERS] Cluster on (index-name) loose NOT NULL properties
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Sender: owner-pgsql-hackers@postgreSQL.org
Precedence: bulk
Status: RO

Hello all,

trying the new version of PgAccess (hope tomorrow will be available) I
discovered that clustering a table on an index loose also the NOT NULL
attributes from the original table. I know that the permissions are also
lost but didn't read anywhere about the NOT NULL.

Best regards,

Constantin Teodorescu
FLEX Consulting Braila, ROMANIA

************