* ALTER TABLE ADD COLUMN to inherited table put column in wrong place

(see TODO.detail/inherit)

* Add ALTER TABLE DROP/ALTER COLUMN feature

* Allow CLUSTER on all tables at once, and improve CLUSTER

* Allow CLUSTER on all tables at once, and improve CLUSTER, loses NOT

NULL specification on table(see TODO.detail/cluster)

* Generate error on CREATE OPERATOR of ~~, ~ and and ~*

* Add SIMILAR TO to allow character classes, 'pg_[a-c]%'

* Auto-destroy sequence on DROP of table with SERIAL(Ryan)

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 QAA02173

for <maillist@candle.pha.pa.us>; Fri, 14 May 1999 16:00:44 -0400 (EDT)

Received: from hub.org (hub.org [209.167.229.1]) by renoir.op.net (o1/$Revision: 1.5 $) with ESMTP id QAA02824 for <maillist@candle.pha.pa.us>; Fri, 14 May 1999 16:00:45 -0400 (EDT)

Received: from hub.org (hub.org [209.167.229.1]) by renoir.op.net (o1/$Revision: 1.6 $) with ESMTP id QAA02824 for <maillist@candle.pha.pa.us>; Fri, 14 May 1999 16:00:45 -0400 (EDT)

Received: from hub.org (hub.org [209.167.229.1])

by hub.org (8.9.3/8.9.3) with ESMTP id PAA47798;

Fri, 14 May 1999 15:57:54 -0400 (EDT)

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 TAA16399

for <maillist@candle.pha.pa.us>; Wed, 25 Nov 1998 19:01:01 -0500 (EST)

Received: from hub.org (majordom@hub.org [209.47.148.200]) by renoir.op.net (o1/$Revision: 1.5 $) with ESMTP id SAA05250 for <maillist@candle.pha.pa.us>; Wed, 25 Nov 1998 18:53:12 -0500 (EST)

Received: from hub.org (majordom@hub.org [209.47.148.200]) by renoir.op.net (o1/$Revision: 1.6 $) with ESMTP id SAA05250 for <maillist@candle.pha.pa.us>; Wed, 25 Nov 1998 18:53:12 -0500 (EST)

Received: from localhost (majordom@localhost)

by hub.org (8.9.1/8.9.1) with SMTP id SAA17798;

Wed, 25 Nov 1998 18:49:38 -0500 (EST)

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 k�nnen 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.1 $) 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

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