No title

Started by "Hub.Org News Admin" <>over 26 years ago1 messagesgeneral

_hub_org_news_admin____@unknown.user

over 26 years ago

From bouncefilter Mon Aug 30 20:37:55 1999
Received: from hotmail.com (law-f29.hotmail.com [209.185.131.92])
by hub.org (8.9.3/8.9.3) with SMTP id UAA43271
for <pgsql-general@postgresql.org>;
Mon, 30 Aug 1999 20:37:19 -0400 (EDT)
(envelope-from amycq@hotmail.com)
Received: (qmail 13460 invoked by uid 0); 31 Aug 1999 00:36:47 -0000
Message-ID: <19990831003647.13459.qmail@hotmail.com>
Received: from 216.61.142.192 by www.hotmail.com with HTTP;
Mon, 30 Aug 1999 17:36:47 PDT
X-Originating-IP: [216.61.142.192]
From: "amy cheng" <amycq@hotmail.com>
To: pgsql-general@postgresql.org, olly@lfix.co.uk, ghoffman@ucsd.edu
Subject: Re: [GENERAL] Database Best Practices ???
Date: Mon, 30 Aug 1999 17:36:47 PDT
Mime-Version: 1.0
Content-Type: text/plain; format=flowed

Oliver, or/and, Gary, and all other experts,

I studied this design:
1) inheritance and trigger: since "person" and "person-address" relationship
already has triggers, why "organization" and "individual" still need
triggers with "person-address"?
2) "person-address" is the relationship between "address" and
"person", why "person" still need to DIRECTLY relate with "address"?
and, again (similar to question 1) why "organization" and "individual"
still need?

Perhaps those codes are just jokes? ????????????????????

amy

From: Matthew Hixson <hixson@frozenwave.com>
Reply-To: hixson@frozenwave.com
To: pgsql-general@postgresql.org
Subject: [GENERAL] stored procedures
Date: Mon, 30 Aug 1999 12:58:45 -0700

Could someone point me to a tutorial or HOWTO regarding stored procedures?
I
know very little about them and need to learn everything I possibly can
about
them as soon as possible. I looked throught the Postgres docs and couldn't
find anything that goes into them in depth.
Thank you,
-M@

--
Matthew Hixson - CIO
FroZenWave Communications
http://www.frozenwave.com

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

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com

From bouncefilter Mon Aug 30 16:02:52 1999
Received: from zoso.frozenwave.com (root@[207.141.25.250])
by hub.org (8.9.3/8.9.3) with ESMTP id QAA04904
for <pgsql-general@postgresql.org>;
Mon, 30 Aug 1999 16:02:19 -0400 (EDT)
(envelope-from hixson@frozenwave.com)
Received: from kashmir (root@kashmir.notwork.org [192.168.0.3])
by zoso.frozenwave.com (8.9.3/8.9.3) with SMTP id MAA00472
for <pgsql-general@postgresql.org>; Mon, 30 Aug 1999 12:56:32 -0700
From: Matthew Hixson <hixson@frozenwave.com>
Reply-To: hixson@frozenwave.com
Organization: Frozenwave Communications
To: pgsql-general@postgresql.org
Subject: stored procedures
Date: Mon, 30 Aug 1999 12:58:45 -0700
X-Mailer: KMail [version 1.0.21]
Content-Type: text/plain
MIME-Version: 1.0
Message-Id: <9908301300340I.17509@kashmir>
Content-Transfer-Encoding: 8bit

Could someone point me to a tutorial or HOWTO regarding stored procedures? I
know very little about them and need to learn everything I possibly can about
them as soon as possible. I looked throught the Postgres docs and couldn't
find anything that goes into them in depth.
Thank you,
-M@

--
Matthew Hixson - CIO
FroZenWave Communications
http://www.frozenwave.com

From bouncefilter Mon Aug 30 16:26:58 1999
Received: (from news@localhost) by hub.org (8.9.3/8.9.3) id QAA04981
for pgsql-general@postgresql.org; Mon, 30 Aug 1999 16:02:58 -0400 (EDT)
(envelope-from news)
Date: Mon, 30 Aug 1999 16:02:58 -0400 (EDT)
From: "Hub.Org News Admin" <news>
Message-Id: <199908302002.QAA04981@hub.org>
X-Authentication-Warning: hub.org: news set sender to <news> using -f
To: undisclosed-recipients:;

From bouncefilter Mon Aug 30 20:57:56 1999
Received: (from news@localhost) by hub.org (8.9.3/8.9.3) id UAA43316
for pgsql-general@postgresql.org; Mon, 30 Aug 1999 20:38:03 -0400 (EDT)
(envelope-from news)
Date: Mon, 30 Aug 1999 20:38:03 -0400 (EDT)
From: "Hub.Org News Admin" <news>
Message-Id: <199908310038.UAA43316@hub.org>
X-Authentication-Warning: hub.org: news set sender to <news> using -f
To: undisclosed-recipients:;

From bouncefilter Fri Sep 17 16:13:08 1999
Received: from mail.waccom.net.ec ([209.170.66.7])
by hub.org (8.9.3/8.9.3) with ESMTP id QAA73141
for <pgsql-general@postgreSQL.org>;
Fri, 17 Sep 1999 16:12:24 -0400 (EDT)
(envelope-from adltor@waccom.net.ec)
Received: from waccom.net.ec ([209.170.66.28])
by mail.waccom.net.ec (8.8.8/8.8.8) with ESMTP id PAA22731
for <pgsql-general@postgreSQL.org>; Fri, 17 Sep 1999 15:13:48 -0400
Message-ID: <37CB3A1F.17A78A4E@waccom.net.ec>
Date: Mon, 30 Aug 1999 21:12:48 -0500
From: "Ing. Arturo de la Torre" <adltor@waccom.net.ec>
Organization: S.C. Progress
X-Mailer: Mozilla 4.51 [en] (Win95; I)
X-Accept-Language: en
MIME-Version: 1.0
To: postgresSQL <pgsql-general@postgreSQL.org>
Subject: unsuscribe
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

From bouncefilter Fri Sep 17 16:13:09 1999
Received: from mail.waccom.net.ec ([209.170.66.7])
by hub.org (8.9.3/8.9.3) with ESMTP id QAA73217
for <pgsql-general@postgreSQL.org>;
Fri, 17 Sep 1999 16:12:52 -0400 (EDT)
(envelope-from adltor@waccom.net.ec)
Received: from waccom.net.ec ([209.170.66.28])
by mail.waccom.net.ec (8.8.8/8.8.8) with ESMTP id PAA22741
for <pgsql-general@postgreSQL.org>; Fri, 17 Sep 1999 15:14:17 -0400
Message-ID: <37CB3A3D.82268FAE@waccom.net.ec>
Date: Mon, 30 Aug 1999 21:13:17 -0500
From: "Ing. Arturo de la Torre" <adltor@waccom.net.ec>
Organization: S.C. Progress
X-Mailer: Mozilla 4.51 [en] (Win95; I)
X-Accept-Language: en
MIME-Version: 1.0
To: postgresSQL <pgsql-general@postgreSQL.org>
Subject: (no subject)
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

unsuscribe

From bouncefilter Tue Aug 31 00:54:59 1999
Received: from mail.enterprise.net (mail.enterprise.net [194.72.192.18])
by hub.org (8.9.3/8.9.3) with ESMTP id AAA82160
for <pgsql-general@postgreSQL.org>;
Tue, 31 Aug 1999 00:54:46 -0400 (EDT) (envelope-from olly@lfix.co.uk)
Received: from linda.lfix.co.uk (root@max04-069.enterprise.net
[194.72.196.189])
by mail.enterprise.net (8.8.5/8.8.5) with ESMTP id FAA28652;
Tue, 31 Aug 1999 05:54:42 +0100 (GMT/BST)
Received: from linda.lfix.co.uk (olly@localhost [127.0.0.1])
by linda.lfix.co.uk (8.9.3/8.9.3/Debian/GNU) with ESMTP id FAA10103;
Tue, 31 Aug 1999 05:54:28 +0100
Message-Id: <199908310454.FAA10103@linda.lfix.co.uk>
X-Mailer: exmh version 2.0.2 2/24/98 (debian)
To: "amy cheng" <amycq@hotmail.com>
cc: pgsql-general@postgreSQL.org, ghoffman@ucsd.edu
Subject: Re: [GENERAL] Database Best Practices ???
In-Reply-To: Message from "amy cheng" <amycq@hotmail.com> of "Mon,
30 Aug 1999 17:36:47 PDT." <19990831003647.13459.qmail@hotmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Date: Tue, 31 Aug 1999 05:54:27 +0100
From: "Oliver Elphick" <olly@lfix.co.uk>

"amy cheng" wrote:

Oliver, or/and, Gary, and all other experts,

I studied this design:
1) inheritance and trigger: since "person" and "person-address" relationship

already has triggers, why "organization" and "individual" still need
triggers with "person-address"?

I don't think that triggers can be inherited automatically. That is why I
did it that way. The implementation of inheritance still needs a lot of
work in PostgreSQL.

2) "person-address" is the relationship between "address" and
"person", why "person" still need to DIRECTLY relate with "address"?
and, again (similar to question 1) why "organization" and "individual"
still need?

The refint trigger is to make sure that the address record exists before
the person record is updated; if the relation is new there will not be
anything in person_address yet.

The duplication in organisation and individual is again because
inheritance is not fully and consistently implemented.

Perhaps those codes are just jokes? ????????????????????

The project is not yet complete, so there may be bugs in it!

--
Vote against SPAM: http://www.politik-digital.de/spam/
========================================
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
" ...Take heed, and beware of covetousness; for a man's
life consisteth not in the abundance of the things
which he possesseth." Luke 12:15

From bouncefilter Tue Aug 31 09:30:04 1999
Received: from hotmail.com (law-f117.hotmail.com [209.185.131.180])
by hub.org (8.9.3/8.9.3) with SMTP id JAA54847
for <pgsql-general@postgreSQL.org>;
Tue, 31 Aug 1999 09:29:35 -0400 (EDT)
(envelope-from amycq@hotmail.com)
Received: (qmail 45641 invoked by uid 0); 31 Aug 1999 13:29:03 -0000
Message-ID: <19990831132903.45640.qmail@hotmail.com>
Received: from 216.61.142.192 by www.hotmail.com with HTTP;
Tue, 31 Aug 1999 06:29:02 PDT
X-Originating-IP: [216.61.142.192]
From: "amy cheng" <amycq@hotmail.com>
To: olly@lfix.co.uk
Cc: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] Database Best Practices ???
Date: Tue, 31 Aug 1999 06:29:02 PDT
Mime-Version: 1.0
Content-Type: text/plain; format=flowed

"Oliver Elphick" wrote:

I don't think that triggers can be inherited automatically. That is why I
did it that way. The implementation of inheritance still needs a lot of
work in PostgreSQL.

thanks for the info.

The refint trigger is to make sure that the address record exists before
the person record is updated; if the relation is new there will not be
anything in person_address yet.

I still do not understand. IMHO, "person" should not contain address at all
--because of "person-address".

The project is not yet complete, so there may be bugs in it!

you defend yourself well, except the "person" one ;-)

amy

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com

From bouncefilter Tue Aug 31 09:36:05 1999
Received: from hotmail.com (law-f213.hotmail.com [209.185.130.123])
by hub.org (8.9.3/8.9.3) with SMTP id JAA56076
for <pgsql-general@postgreSQL.org>;
Tue, 31 Aug 1999 09:35:38 -0400 (EDT)
(envelope-from amycq@hotmail.com)
Received: (qmail 37543 invoked by uid 0); 31 Aug 1999 13:35:06 -0000
Message-ID: <19990831133506.37542.qmail@hotmail.com>
Received: from 216.61.142.192 by www.hotmail.com with HTTP;
Tue, 31 Aug 1999 06:35:06 PDT
X-Originating-IP: [216.61.142.192]
From: "amy cheng" <amycq@hotmail.com>
To: grupoitem@ctv.es, pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] Referential integrity
Date: Tue, 31 Aug 1999 06:35:06 PDT
Mime-Version: 1.0
Content-Type: text/plain; format=flowed

no direct support yet. but you can use triggers. you can find the
trigggers in c in the source's contri/refin.c , also, you can find
them in the source's test directory (file name like triggers.sql(?)).

this question is asked n-times (n>=20 (?)). So, in this listing,
also the novice listing, search the "referential integrity" or/and
"foreign key", you will find a lot.

hope this will help.

amy

From: "Pablo Sentis" <grupoitem@ctv.es>
To: "PostgreSQL mailing list" <pgsql-general@postgreSQL.org>
Subject: [GENERAL] Referential integrity
Date: Tue, 31 Aug 1999 12:09:19 +0200

Newbie question:

I don�t know how to work with referential integrity in Postgresql :
Could someone help me?

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com

From bouncefilter Tue Aug 31 12:02:07 1999
Received: from hotmail.com (law-f32.hotmail.com [209.185.131.95])
by hub.org (8.9.3/8.9.3) with SMTP id MAA89139
for <pgsql-general@postgreSQL.org>;
Tue, 31 Aug 1999 12:01:46 -0400 (EDT)
(envelope-from amycq@hotmail.com)
Received: (qmail 25351 invoked by uid 0); 31 Aug 1999 16:01:14 -0000
Message-ID: <19990831160114.25350.qmail@hotmail.com>
Received: from 216.61.142.192 by www.hotmail.com with HTTP;
Tue, 31 Aug 1999 09:01:14 PDT
X-Originating-IP: [216.61.142.192]
From: "amy cheng" <amycq@hotmail.com>
To: pgsql-general@postgreSQL.org
Subject: do delete/insert instead of update if name changes
Date: Tue, 31 Aug 1999 09:01:14 PDT
Mime-Version: 1.0
Content-Type: text/plain; format=flowed

hi, all experts there,

I'm studying the example in postgresql-6.5.1/src/pl/plpgsql/test.
I do not understand why we need "do delete/insert instead of update if name
changes". Is it ONLY for the test, or, there is some
reason for doing that in practical use?

I cut/paste part of it below.

Thanks !!!!!

amy
########################################
-- ************************************************************
-- * BEFORE UPDATE on PHone
-- * - do delete/insert instead of update if name changes
-- ************************************************************
create function tg_phone_bu() returns opaque as '
begin
if new.slotname != old.slotname then
delete from PHone where slotname = old.slotname;
insert into PHone (
slotname,
comment,
slotlink
) values (
new.slotname,
new.comment,
new.slotlink
);
return null;
end if;
return new;
end;
' language 'plpgsql';

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com

From bouncefilter Tue Aug 31 06:16:03 1999
Received: from irene.ctv.es (irene.ctv.es [212.25.129.13])
by hub.org (8.9.3/8.9.3) with ESMTP id GAA25102
for <pgsql-general@postgreSQL.org>;
Tue, 31 Aug 1999 06:16:01 -0400 (EDT)
(envelope-from grupoitem@ctv.es)
Received: from PLSENTIS (ctv21225137018.ctv.es [212.25.137.18])
by irene.ctv.es (8.9.3/8.9.1) with SMTP id MAA21950
for <pgsql-general@postgreSQL.org>;
Tue, 31 Aug 1999 12:15:26 +0200 (MET DST)
X-Envelope-To: <pgsql-general@postgreSQL.org>
Message-ID: <000b01bef398$e63eb380$5a020164@PLSENTIS>
From: "Pablo Sentis" <grupoitem@ctv.es>
To: "PostgreSQL mailing list" <pgsql-general@postgreSQL.org>
Subject: Referential integrity
Date: Tue, 31 Aug 1999 12:09:19 +0200
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="----=_NextPart_000_0008_01BEF3A9.A79D9EA0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3

This is a multi-part message in MIME format.

------=_NextPart_000_0008_01BEF3A9.A79D9EA0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Newbie question:

I don=B4t know how to work with referential integrity in Postgresql :=20
Could someone help me?

------=_NextPart_000_0008_01BEF3A9.A79D9EA0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD W3 HTML//EN">
<HTML>
<HEAD>

<META content=3Dtext/html;charset=3Diso-8859-1 =
http-equiv=3DContent-Type>
<META content=3D'"MSHTML 4.72.3110.7"' name=3DGENERATOR>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV>Newbie question:</DIV>
<DIV> </DIV>
<DIV>I don´t know how to work with referential integrity in =
Postgresql :=20
</DIV>
<DIV>Could someone help me?</DIV>
<DIV> </DIV></BODY></HTML>

------=_NextPart_000_0008_01BEF3A9.A79D9EA0--

From bouncefilter Tue Aug 31 13:18:07 1999
Received: from hotmail.com (law-f31.hotmail.com [209.185.131.94])
by hub.org (8.9.3/8.9.3) with SMTP id NAA02951
for <pgsql-general@postgreSQL.org>;
Tue, 31 Aug 1999 13:17:41 -0400 (EDT)
(envelope-from amycq@hotmail.com)
Received: (qmail 82875 invoked by uid 0); 31 Aug 1999 17:17:09 -0000
Message-ID: <19990831171709.82874.qmail@hotmail.com>
Received: from 216.61.142.192 by www.hotmail.com with HTTP;
Tue, 31 Aug 1999 10:17:08 PDT
X-Originating-IP: [216.61.142.192]
From: "amy cheng" <amycq@hotmail.com>
To: pgsql-general@postgreSQL.org
Subject: change null to ""
Date: Tue, 31 Aug 1999 10:17:08 PDT
Mime-Version: 1.0
Content-Type: text/plain; format=flowed

hi, all experts there,

it's me again :-)

I'm studying the example in postgresql-6.5.1/src/pl/plpgsql/test.
I have another question: why do we need to "Set backlink to empty string if
NULL value given"? is it JUST a test, or, some reasons to
do that in general?

Thanks!!!!!!!!!!!

amy

#################################################

-- ************************************************************
-- * BEFORE INSERT or UPDATE on all slots with backlink
-- * - Set backlink to empty string if NULL value given
-- ************************************************************
create function tg_chkbacklink() returns opaque as '
begin
if new.backlink isnull then
new.backlink := '''';
end if;
return new;
end;
' language 'plpgsql';

create trigger tg_chkbacklink before insert or update
on PSlot for each row execute procedure tg_chkbacklink();

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com

From bouncefilter Tue Aug 31 06:48:03 1999
Received: from sema.be (milou.sema.be [212.35.4.18])
by hub.org (8.9.3/8.9.3) with ESMTP id GAA28879
for <pgsql-general@postgreSQL.org>;
Tue, 31 Aug 1999 06:47:20 -0400 (EDT)
(envelope-from Laurent.Marzullo@sema.be)
Received: from sema.be (evinrude.sema.be [193.74.244.95])
by sema.be (8.8.8+Sun/8.8.8) with ESMTP id MAA00947
for <pgsql-general@postgreSQL.org>;
Tue, 31 Aug 1999 12:47:14 +0200 (MET DST)
Message-ID: <37CBB249.6AF39DA1@sema.be>
Date: Tue, 31 Aug 1999 12:45:29 +0200
From: Marzullo Laurent <Laurent.Marzullo@sema.be>
X-Mailer: Mozilla 4.51 [en] (Win95; I)
X-Accept-Language: en
MIME-Version: 1.0
To: PGSQL General list <pgsql-general@postgreSQL.org>
Subject: Prog to generate table structure ...
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Hello,

is there a prog to generate C file describing table structure
of a database.

(Something like dclgen under Ingres...)

If not, I would like to know how to get information about
type of each row of a table to write this kind of prog.
(i.e. Where is this explain ?)

--
+----------------------------------+
| Marzullo Laurent |
| Analyste-programmeur |
| SEMA-GROUP Belgium S.A. |
| T�l: (32) 2 333 52 43 |
| e-mail: Laurent.Marzullo@sema.be |
+----------------------------------+

From bouncefilter Tue Aug 31 07:19:03 1999
Received: from mail-b.bcc.ac.uk (mail-b.bcc.ac.uk [144.82.100.22])
by hub.org (8.9.3/8.9.3) with ESMTP id HAA32833
for <pgsql-general@postgreSQL.org>;
Tue, 31 Aug 1999 07:19:01 -0400 (EDT)
(envelope-from stuart@ludwig.ucl.ac.uk)
Received: from kestrel.ludwig.ucl.ac.uk by mail-b.bcc.ac.uk with SMTP (XT-PP);
Tue, 31 Aug 1999 12:17:25 +0100
Received: from [128.40.242.190]
by kestrel.ludwig.ucl.ac.uk (SMI-8.6/SMI-SVR4) id MAA08181;
Tue, 31 Aug 1999 12:20:23 +0100
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Sender: stuart@kestrel.ludwig.ucl.ac.uk
Message-Id: <v04020a00b3f167fc3a7c@[128.40.242.190]>
In-Reply-To: <37CBB249.6AF39DA1@sema.be>
Date: Tue, 31 Aug 1999 12:15:35 +0100
To: Marzullo Laurent <Laurent.Marzullo@sema.be>,
PGSQL General list <pgsql-general@postgreSQL.org>
From: Stuart Rison <stuart@ludwig.ucl.ac.uk>
Subject: Re: [GENERAL] Prog to generate table structure ...
Content-Transfer-Encoding: 8bit
X-MIME-Autoconverted: from quoted-printable to 8bit by hub.org id HAA32834

don't know about any facilities to generate C files describing table
structure.

As to obtaining that information, I would say it is all in system catalog
tables (i.e. the tables that start with pg_ and which can be listed with
\dS in psql).

These are the tables that are queried when you use such psql commands as
\dt, \di or \d *.

There is information on these tables (along with an ER diagram ) in the PG
manuals.

try http://www.postgresql.org/docs/postgres/extend15617.htm

also, in my verison of PG (v.6.4.0) there is a file in the source code that
gives good examples of queries exploiting system catalogues.

It can be found at <postgresql-src-dir>/src/tutorial/syscat.source.

I don't know where/if that file exists in PG 6.5.* but I would guess in a
similar place.

let me know if you can't find it and I'll send you a copy if you want.

bonne chance,

At 12:45 pm +0200 31/8/99, Marzullo Laurent wrote:

Hello,

is there a prog to generate C file describing table structure
of a database.

(Something like dclgen under Ingres...)

If not, I would like to know how to get information about
type of each row of a table to write this kind of prog.
(i.e. Where is this explain ?)

--
+----------------------------------+
| Marzullo Laurent |
| Analyste-programmeur |
| SEMA-GROUP Belgium S.A. |
| T�l: (32) 2 333 52 43 |
| e-mail: Laurent.Marzullo@sema.be |
+----------------------------------+

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

+--------------------------+--------------------------------------+
| Stuart C. G. Rison       | Ludwig Institute for Cancer Research |
+--------------------------+ 91 Riding House Street               |
| N.B. new phone code!!    | London, W1P 8BT                      |
| Tel. +44 (0)207 878 4041 | UNITED KINGDOM                       |
| Fax. +44 (0)207 878 4040 | stuart@ludwig.ucl.ac.uk              |
+--------------------------+--------------------------------------+

From bouncefilter Tue Aug 31 09:52:06 1999
Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1])
by hub.org (8.9.3/8.9.3) with ESMTP id JAA59424
for <pgsql-general@postgresql.org>;
Tue, 31 Aug 1999 09:52:02 -0400 (EDT)
(envelope-from jose@sferacarta.com)
Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it
[193.207.10.213])
by bologna.nettuno.it (8.9.3/8.9.3/NETTuno 3.3) with ESMTP id PAA06840;
Tue, 31 Aug 1999 15:51:51 +0200 (MDT)
Received: from rosso.sferacarta.com (sferacarta.com) [10.20.30.5]
by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian))
id 11LoWp-00043k-00; Tue, 31 Aug 1999 14:04:51 +0000
Message-ID: <37CBC642.55DD80BC@sferacarta.com>
Date: Tue, 31 Aug 1999 14:10:42 +0200
From: =?iso-8859-1?Q?Jos=E9?= Soares <jose@sferacarta.com>
X-Mailer: Mozilla 4.5 [it] (Win95; I)
X-Accept-Language: it
MIME-Version: 1.0
To: Pablo Sentis <grupoitem@ctv.es>
CC: PostgreSQL mailing list <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Referential integrity
References: <000b01bef398$e63eb380$5a020164@PLSENTIS>
Content-Type: multipart/mixed; boundary="------------E070C692FDB4594C27643993"

Questo � un messaggio multi-parte scritto in formato MIME.
--------------E070C692FDB4594C27643993
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Try using triggers: (see attached example)
Jos�

Pablo Sentis ha scritto:

Part 1.1 Type: Plain Text (text/plain)
Encoding: quoted-printable

--------------E070C692FDB4594C27643993
Content-Type: text/plain; charset=us-ascii;
name="trigger.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="trigger.sql"

--Referential integrity:
--1. Don't allow to add a detail without header
--2. Delete all details in cascade if one decide to delete the header
--3. Update details' key in cascade if header's key is changed

DROP TABLE header;
CREATE TABLE header (
year integer NOT NULL,
number INTEGER NOT NULL,
date DATE NOT NULL,
cod_client CHAR(4) NOT NULL,
CONSTRAINT k_header PRIMARY KEY (year,number)
);

DROP TABLE detail;
CREATE TABLE detail (
year integer NOT NULL,
number INTEGER NOT NULL,
cod_product CHAR(05) NOT NULL,
qty INTEGER NOT NULL,
cost DECIMAL(8,2),
CONSTRAINT k_detail PRIMARY KEY (year,number,cod_product),
CONSTRAINT k_extern FOREIGN KEY(year,number) references HEADER
);

drop function f_not_add_detail();
create function f_not_add_detail() returns opaque as '
declare
/* to avoid insert detail if header doesn''t exist */
tot int;
begin
select number into tot from header
where year = new.year and number = new.number;
if not found then
raise notice ''I cannot add a detail without header!'';
return NULL;
else
return new;
end if;
end;
' language 'plpgsql';

drop function f_del_detail();
create function f_del_detail() returns opaque as '
begin
-- Delete details in cascade...
delete from detail where
detail.year = old.year
and detail.number = old.number;
return new;
end;
' language 'plpgsql';

drop function f_upd_detail();
create function f_upd_detail() returns opaque as '
begin
-- Updates details keys in cascade...
update detail set year = new.year, number = new.number
where detail.year = old.year
and detail.number = old.number;
return new;
end;
' language 'plpgsql';

create trigger t_ins_after before INSERT
on detail for each row execute procedure f_not_add_detail();

create trigger t_del_after after DELETE
on header for each row execute procedure f_del_detail();

create trigger t_upd_after after UPDATE
on header for each row execute procedure f_upd_detail();

insert into header values(1999,321,current_date,'C128');
insert into detail values(1999,321,'B139',2,200.35);
insert into header values(1997,132,current_date,'C500');
insert into detail values(1997,132,'B166',3,120.50);
select * from header;
select * from detail;
update header set year=1998 where year=1999;
select * from header;
select * from detail;

delete from header where year=1998;
select * from header;
select * from detail;

insert into detail values(1999,321,'B139',2,200.35);

--------------E070C692FDB4594C27643993--

From bouncefilter Tue Aug 31 09:57:05 1999
Received: from sema.be (milou.sema.be [212.35.4.18])
by hub.org (8.9.3/8.9.3) with ESMTP id JAA60392
for <pgsql-general@postgreSQL.org>;
Tue, 31 Aug 1999 09:56:42 -0400 (EDT)
(envelope-from Laurent.Marzullo@sema.be)
Received: from sema.be (evinrude.sema.be [193.74.244.95])
by sema.be (8.8.8+Sun/8.8.8) with ESMTP id PAA05506
for <pgsql-general@postgreSQL.org>;
Tue, 31 Aug 1999 15:56:32 +0200 (MET DST)
Message-ID: <37CBDEA7.14902FBC@sema.be>
Date: Tue, 31 Aug 1999 15:54:47 +0200
From: Marzullo Laurent <Laurent.Marzullo@sema.be>
X-Mailer: Mozilla 4.51 [en] (Win95; I)
X-Accept-Language: en
MIME-Version: 1.0
To: PGSQL General list <pgsql-general@postgreSQL.org>
Subject: PostgreSQL table data structure generator...
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Hello again,

I propose myself for developping a prog to generate C Source
code for table structure for a Postgres Table.

If someone have something to say about it (features wanted, new
langage, etc) (s)he's welcome. And if someone have already done
it, please stop me now.

Aim of the project:

Generate C/C++ Data structure for a set of table from a PostgreSQL dB.
The C data structure will be :
Struct or Class containing all the column of the table.

From bouncefilter Tue Aug 31 12:42:07 1999
Received: from castle-smtp.amgen.com (firewall-user@ns2.amgen.com
[138.133.17.7]) by hub.org (8.9.3/8.9.3) with ESMTP id MAA96095
for <pgsql-general@postgresql.org>;
Tue, 31 Aug 1999 12:41:23 -0400 (EDT)
(envelope-from mdalphin@amgen.com)
Received: by castle-smtp.amgen.com; id JAA04997;
Tue, 31 Aug 1999 09:41:21 -0700 (PDT)
Received: from mailbag.amgen.com(138.133.10.78) by castle-smtp.amgen.com via
smap (3.2) id xmaa04966; Tue, 31 Aug 99 09:40:54 -0700
Received: from maat.amgen.com (maat [138.133.145.25])
by mailbag.amgen.com (8.8.5/8.8.5) with SMTP id JAA11736
for <pgsql-general@postgresql.org>;
Tue, 31 Aug 1999 09:37:34 -0700 (PDT)
Received: from amgen.com by maat.amgen.com (SMI-8.6/SMI-SVR4)
id JAA05716; Tue, 31 Aug 1999 09:40:53 -0700
Sender: mdalphin@amgen.com
Message-ID: <37CC05CF.B69ABE76@amgen.com>
Date: Tue, 31 Aug 1999 09:41:51 -0700
From: Mark Dalphin <mdalphin@amgen.com>
Organization: Amgen, Inc.
X-Mailer: Mozilla 4.6C-SGI [en] (X11; U; IRIX64 6.5 IP30)
X-Accept-Language: en
MIME-Version: 1.0
To: pgsql-general@postgresql.org
Subject: How to display user-defined functions?
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Hi,

Is there a way to display user-defined functions? For example, if I define a
function using PL/pgsql, what tables do I query and in what way to learn that
the function exisits and further, what its defintion is? I can find functions
that return known types, eg getTimeStamp, below, shows up with '\df', however
functions defined returning type "opaque" do not show up, eq,
"exon_foreign_keys", below.

As a wish list, a command like, '\dF' to display functions I have defined,
including those returning opaque would be nice.

---------------------------------------------------
-- Listed by '\df' amongst many other functions
CREATE FUNCTION getTimeStamp() RETURNS timestamp AS '
DECLARE
cur_time timestamp;
BEGIN
cur_time = ''now'';
RETURN cur_time;
END;
' LANGUAGE 'plpgsql';

--------------------------------------
-- Not listed by '\df' or by any other means I can locate
CREATE FUNCTION exon_foreign_keys() RETURNS opaque AS '
DECLARE
zhvt_row zhvt%ROWTYPE;
BEGIN
IF NEW.zhvtID ISNULL THEN
RAISE EXCEPTION ''zhvtID can not be NULL'';
END IF;

SELECT * INTO zhvt_row FROM zhvt WHERE zhvtID = NEW.zhvtID;
IF NOT FOUND THEN
RAISE EXCEPTION ''zhvtID = % is not in TABLE zhvt'' , NEW.zhvtID;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

Thanks,
Mark

--
Mark Dalphin email: mdalphin@amgen.com
Mail Stop: 29-2-A phone: +1-805-447-4951 (work)
One Amgen Center Drive +1-805-375-0680 (home)
Thousand Oaks, CA 91320 fax: +1-805-499-9955 (work)

From bouncefilter Tue Aug 31 13:51:08 1999
Received: from mail-b.bcc.ac.uk (mail-b.bcc.ac.uk [144.82.100.22])
by hub.org (8.9.3/8.9.3) with ESMTP id NAA07978
for <pgsql-general@postgreSQL.org>;
Tue, 31 Aug 1999 13:50:23 -0400 (EDT)
(envelope-from stuart@ludwig.ucl.ac.uk)
Received: from kestrel.ludwig.ucl.ac.uk by mail-b.bcc.ac.uk with SMTP (XT-PP);
Tue, 31 Aug 1999 18:50:17 +0100
Received: from [128.40.242.190]
by kestrel.ludwig.ucl.ac.uk (SMI-8.6/SMI-SVR4) id SAA15420;
Tue, 31 Aug 1999 18:51:48 +0100
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
X-Sender: stuart@kestrel.ludwig.ucl.ac.uk
Message-Id: <v04020a03b3f1ba2889de@[128.40.242.190]>
In-Reply-To: <37CC05CF.B69ABE76@amgen.com>
Date: Tue, 31 Aug 1999 18:46:57 +0100
To: Mark Dalphin <mdalphin@amgen.com>, pgsql-general@postgreSQL.org
From: Stuart Rison <stuart@ludwig.ucl.ac.uk>
Subject: Re: [GENERAL] How to display user-defined functions?

At 9:41 am -0700 31/8/99, Mark Dalphin wrote:

Hi,

Is there a way to display user-defined functions? For example, if I define a
function using PL/pgsql, what tables do I query and in what way to learn that
the function exisits and further, what its defintion is? I can find functions
that return known types, eg getTimeStamp, below, shows up with '\df', however
functions defined returning type "opaque" do not show up, eq,
"exon_foreign_keys", below.

I think you need to query the catalog tables for such answer.

There quite a lot of info on catalog tables in the user manual but I find
one of the most useful resources is a list of example SQL queries that
exploit them which can be found in the tutorial section of the PG sources
(at least for PG6.4.0). try looking for
<PG-source>/src/tutorial/syscat.source

from the syscat.source file:

--
-- lists the name, number of arguments and the return type of all user-defined
-- C functions
--
SELECT p.proname, p.pronargs, t.typname
FROM pg_proc p, pg_language l, pg_type t
WHERE p.prolang = l.oid
and p.prorettype = t.oid
and l.lanname = 'c'
ORDER BY proname;

Now to get a \dF like SELECT, based losely on the above, I got:

SELECT p.proname, p.pronargs, t.typname, p.prosrc
FROM pg_proc p, pg_language l, pg_type t
WHERE p.prolang = l.oid
and p.prorettype = t.oid
and l.lanname in ('c','plpgsql')
and p.proowner='1234'
-- replace with appropriate user_id in previous line
-- or perhaps with p.proowner<>'1111' to select all non-superuser
-- defined functions.
UNION
SELECT p.proname, p.pronargs, 'opaque', p.prosrc
FROM pg_proc p, pg_language l
WHERE p.prolang = l.oid
and p.prorettype = 0 -- opaque functions are listed as having return
type oid 0
and l.lanname in ('c','plpgsql')
and p.proowner='1234' -- as above
ORDER BY proname;

Now I'm sure there's a better way of writing/implementing this query (I
cobbled together v. fast) but it should give you the general idea.

As a wish list, a command like, '\dF' to display functions I have defined,
including those returning opaque would be nice.

I must say I'm a big fan of the \d? set of psql 'tools'; \dF get my vote as
an additional one (but should it show source code?).

Also, there was talk as long while back (circa PG 6.3.2) to have add
functionality to the \d? 'functions' (I really don't know what to call
them) such that something like:

\df int4float would pull out \df info but only for function int4float
\da stddev would pull out \da info but only for aggregate stddev

(perhaps even using ~'stddev' instead of ='stddev' if you catch my drift).

Don't know if that ever made it (didn't in PG 6.4.0)

HTH, (& happy exon trapping),

Stuart.

+--------------------------+--------------------------------------+
| Stuart C. G. Rison       | Ludwig Institute for Cancer Research |
+--------------------------+ 91 Riding House Street               |
| N.B. new phone code!!    | London, W1P 8BT                      |
| Tel. +44 (0)207 878 4041 | UNITED KINGDOM                       |
| Fax. +44 (0)207 878 4040 | stuart@ludwig.ucl.ac.uk              |
+--------------------------+--------------------------------------+

From bouncefilter Tue Aug 31 15:22:09 1999
Received: from argh.demon.co.uk (IDENT:grim@argh.demon.co.uk [193.237.6.55])
by hub.org (8.9.3/8.9.3) with ESMTP id PAA27558
for <pgsql-general@postgresql.org>;
Tue, 31 Aug 1999 15:22:06 -0400 (EDT)
(envelope-from grim@argh.demon.co.uk)
Received: (from grim@localhost) by argh.demon.co.uk (8.9.3/8.9.3) id UAA19658
for pgsql-general@postgresql.org; Tue, 31 Aug 1999 20:24:59 +0100
From: Michael Simms <grim@argh.demon.co.uk>
Message-Id: <199908311924.UAA19658@argh.demon.co.uk>
Subject: Bug-tracking
To: pgsql-general@postgresql.org
Date: Tue, 31 Aug 1999 20:24:59 +0100 (BST)
X-Mailer: ELM [version 2.5 PL0pre8]
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

I was wondering what the situation is with the bugtracking system, cos
there are a number of serious bugs that havent even been looked at
according to that, as well as a bug that is killing my whole system
that I reported on here and isnt even *listed* on the bugtrack.

Thanx

M Simms

From bouncefilter Tue Aug 31 15:53:09 1999
Received: from thelab.hub.org (nat203.199.mpoweredpc.net [142.177.203.199])
by hub.org (8.9.3/8.9.3) with ESMTP id PAA32051
for <pgsql-general@postgreSQL.org>;
Tue, 31 Aug 1999 15:52:34 -0400 (EDT) (envelope-from scrappy@hub.org)
Received: from localhost (scrappy@localhost)
by thelab.hub.org (8.9.3/8.9.1) with ESMTP id QAA35600;
Tue, 31 Aug 1999 16:52:42 -0300 (ADT) (envelope-from scrappy@hub.org)
X-Authentication-Warning: thelab.hub.org: scrappy owned process doing -bs
Date: Tue, 31 Aug 1999 16:52:41 -0300 (ADT)
From: The Hermit Hacker <scrappy@hub.org>
To: Michael Simms <grim@argh.demon.co.uk>
cc: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] Bug-tracking
In-Reply-To: <199908311924.UAA19658@argh.demon.co.uk>
Message-ID: <Pine.BSF.4.10.9908311652170.8660-100000@thelab.hub.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

what bugtrackign system? if you are referring to keystone, we trashed it
over a month ago...

On Tue, 31 Aug 1999, Michael Simms wrote:

Hi

I was wondering what the situation is with the bugtracking system, cos
there are a number of serious bugs that havent even been looked at
according to that, as well as a bug that is killing my whole system
that I reported on here and isnt even *listed* on the bugtrack.

Thanx

M Simms

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

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org

From bouncefilter Tue Aug 31 16:03:09 1999
Received: from mail.enterprise.net (mail.enterprise.net [194.72.192.18])
by hub.org (8.9.3/8.9.3) with ESMTP id QAA33772
for <pgsql-general@postgreSQL.org>;
Tue, 31 Aug 1999 16:02:16 -0400 (EDT) (envelope-from olly@lfix.co.uk)
Received: from linda.lfix.co.uk (root@max04-011.enterprise.net
[194.72.196.131])
by mail.enterprise.net (8.8.5/8.8.5) with ESMTP id VAA18249;
Tue, 31 Aug 1999 21:02:11 +0100 (GMT/BST)
Received: from linda.lfix.co.uk (olly@localhost [127.0.0.1])
by linda.lfix.co.uk (8.9.3/8.9.3/Debian/GNU) with ESMTP id VAA26653;
Tue, 31 Aug 1999 21:02:03 +0100
Message-Id: <199908312002.VAA26653@linda.lfix.co.uk>
X-Mailer: exmh version 2.0.2 2/24/98 (debian)
X-URL: http://www.lfix.co.uk/oliver
X-face:
"xUFVDj+ZJtL_IbURmI}!~xAyPC"Mrk=MkAm&tPQnNq(FWxv49R}\>0oI8VM?O2VY+N7@F-
KMLl*!h}B)u@TW|B}6<X<J|}QsVlTi:RA:O7Abc(@D2Y/"J\S,
b1!<&<B/J}b.Ii9@B]H6V!+#sE0Q
_+=`K$5TI|4I0-=Cp%pt~L#QYydO'iBXR~\tT?uftep9n9AF`@SzTwsw6uqJ}pL,
h(cZi}T#PB"#!k
p^e=Z.K~fuw$l?]lUV)?R]U}l; f*~Ol)#fpKR)Yt}XOr6BI\_Jjr0!@GMnpCTnTym4f; c{;
Ms=0{`D Lq9MO6{wj%s-*N"G,g
To: "amy cheng" <amycq@hotmail.com>
cc: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] Database Best Practices ???
In-reply-to: Message from "amy cheng" <amycq@hotmail.com> of Tue,
31 Aug 1999 06:29:02 PDT. <19990831132903.45640.qmail@hotmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Date: Tue, 31 Aug 1999 21:02:02 +0100
From: "Oliver Elphick" <olly@lfix.co.uk>

"amy cheng" wrote:

The refint trigger is to make sure that the address record exists before
the person record is updated; if the relation is new there will not be
anything in person_address yet.

I still do not understand. IMHO, "person" should not contain address at all
--because of "person-address".

The project is not yet complete, so there may be bugs in it!

you defend yourself well, except the "person" one ;-)

I suspect you may be right on that; this was only an extract from a much
larger work, so I haven't got round to reviewing it recently.

From bouncefilter Tue Aug 31 16:23:09 1999
Received: from thelab.hub.org (nat203.199.mpoweredpc.net [142.177.203.199])
by hub.org (8.9.3/8.9.3) with ESMTP id QAA38137
for <pgsql-general@postgresql.org>;
Tue, 31 Aug 1999 16:22:43 -0400 (EDT) (envelope-from scrappy@hub.org)
Received: from localhost (scrappy@localhost)
by thelab.hub.org (8.9.3/8.9.1) with ESMTP id RAA35848
for <pgsql-general@postgresql.org>;
Tue, 31 Aug 1999 17:22:53 -0300 (ADT) (envelope-from scrappy@hub.org)
X-Authentication-Warning: thelab.hub.org: scrappy owned process doing -bs
Date: Tue, 31 Aug 1999 17:22:53 -0300 (ADT)
From: The Hermit Hacker <scrappy@hub.org>
To: pgsql-general@postgresql.org
Subject: lo_* interface ...
Message-ID: <Pine.BSF.4.10.9908311721290.4130-100000@thelab.hub.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

I've been asked about the performance/stability of using BLOBs (lo_*)
under PostgreSQL, and having no experience with them myself, I'm looking
for examples of sites that are, including such stats like size of the
database, max BLOB size, performance and such...

Thanks...

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org

From bouncefilter Tue Aug 31 16:44:10 1999
Received: from finch-post-12.mail.demon.net (finch-post-12.mail.demon.net
[194.217.242.41]) by hub.org (8.9.3/8.9.3) with ESMTP id QAA41337
for <pgsql-general@postgresql.org>;
Tue, 31 Aug 1999 16:44:05 -0400 (EDT)
(envelope-from peter@retep.org.uk)
Received: from maidast.demon.co.uk ([158.152.22.37] helo=maidast.retep.org.uk)
by finch-post-12.mail.demon.net with esmtp (Exim 2.12 #1)
id 11LulA-0000Qk-0C; Tue, 31 Aug 1999 20:44:04 +0000
Received: from tabatha (root@tabatha.retep.org.uk [192.168.4.4])
by maidast.retep.org.uk (8.9.3/8.9.3) with SMTP id VAA04248;
Tue, 31 Aug 1999 21:42:27 +0100
From: Peter Mount <peter@retep.org.uk>
Organization: retep org
To: The Hermit Hacker <scrappy@hub.org>
Subject: Re: [GENERAL] Bug-tracking
Date: Tue, 31 Aug 1999 21:39:52 +0100
X-Mailer: KMail [version 1.0.17]
Content-Type: text/plain
References: <Pine.BSF.4.10.9908311652170.8660-100000@thelab.hub.org>
Cc: pgsql-general@postgresql.org
MIME-Version: 1.0
Message-Id: <99083121405802.03071@tabatha>
Content-Transfer-Encoding: 8bit
X-KMail-Mark:

On Tue, 31 Aug 1999, you wrote:

what bugtrackign system? if you are referring to keystone, we trashed it
over a month ago...

It's still on the web site (at least at midday UK time it was).

On Tue, 31 Aug 1999, Michael Simms wrote:

Hi

I was wondering what the situation is with the bugtracking system, cos
there are a number of serious bugs that havent even been looked at
according to that, as well as a bug that is killing my whole system
that I reported on here and isnt even *listed* on the bugtrack.

Thanx

M Simms

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

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org

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

--
Peter T Mount peter@retep.org.uk Tel +44 (0) 7990 898443
Homepage http://www.retep.org.uk Fax UK +44 (0) 870 0568837
PostgreSQL JDBC http://www.retep.org.uk/postgres/ US +1 734 661 0110
Java PDF Generator http://www.retep.org.uk/pdf/ More details on the website

From bouncefilter Tue Aug 31 19:58:12 1999
Received: from gateway.digiintranet.ro (IDENT:root@gateway.digiro.net
[194.102.93.10]) by hub.org (8.9.3/8.9.3) with ESMTP id TAA66369
for <pgsql-general@postgreSQL.org>;
Tue, 31 Aug 1999 19:57:23 -0400 (EDT) (envelope-from teo@digiro.net)
Received: from digiro.net (ppp17_v90.digiro.net [194.102.93.87])
by gateway.digiintranet.ro (8.8.7/8.8.7) with ESMTP id CAA01765
for <pgsql-general@postgreSQL.org>; Wed, 1 Sep 1999 02:30:47 +0300
Message-ID: <37CC6BAF.7A40B175@digiro.net>
Date: Wed, 01 Sep 1999 02:56:31 +0300
From: Teodor Cimpoesu <teo@digiro.net>
X-Mailer: Mozilla 4.51 [en] (Win98; I)
X-Accept-Language: en
MIME-Version: 1.0
CC: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] change null to ""
References: <19990831171709.82874.qmail@hotmail.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

amy cheng wrote:

hi, all experts there,

it's me again :-)

oh well :)

I'm studying the example in postgresql-6.5.1/src/pl/plpgsql/test.
I have another question: why do we need to "Set backlink to empty string if
NULL value given"? is it JUST a test, or, some reasons to
do that in general?

Nope, it's just an example of "triggers".
You should also post the CREATE statement of PSlot,
does it have a "NOT NULL" constraint for backlink attribute?
if yes, you've got the answer (I would also have a look but
I'm on the other OS right now :> )

-- ************************************************************
-- * BEFORE INSERT or UPDATE on all slots with backlink
-- * - Set backlink to empty string if NULL value given
-- ************************************************************
create function tg_chkbacklink() returns opaque as '
begin
if new.backlink isnull then
new.backlink := '''';
end if;
return new;
end;
' language 'plpgsql';

create trigger tg_chkbacklink before insert or update
on PSlot for each row execute procedure tg_chkbacklink();

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com

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

--
CIMPOESU Teodor, Web Programmer (h)
@ DIGICOM S.A. Bucharest, Romania
@ Internet, site development
@ teo@digiro.net, +(401)-330.47.28

official home page ~ http://www.digiro.net/
Internet web page ~ http://internet.digiro.net/

From bouncefilter Tue Aug 31 19:54:11 1999
Received: from tech.com.au (IDENT:root@techpt.lnk.telstra.net
[139.130.75.122])
by hub.org (8.9.3/8.9.3) with ESMTP id TAA65887
for <pgsql-general@postgreSQL.org>;
Tue, 31 Aug 1999 19:53:44 -0400 (EDT)
(envelope-from chris@tech.com.au)
Received: from tech.com.au ([202.62.130.88])
by tech.com.au (8.9.3/8.9.3) with ESMTP id JAA23560;
Wed, 1 Sep 1999 09:53:19 +1000
Message-ID: <37CC6C31.894DF865@tech.com.au>
Date: Wed, 01 Sep 1999 09:58:41 +1000
From: Chris Bitmead <chris@tech.com.au>
X-Mailer: Mozilla 4.61 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
To: Marzullo Laurent <Laurent.Marzullo@sema.be>
CC: PGSQL General list <pgsql-general@postgreSQL.org>
Subject: Re: [GENERAL] Prog to generate table structure ...
References: <37CBB249.6AF39DA1@sema.be>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

You need to look at the system tables. Like pg_class and the other pg_*
tables.

pg_dump will output CREATE statements to re-create the database. I'm not
sure
that's what you want though.

Marzullo Laurent wrote:

Hello,

is there a prog to generate C file describing table structure
of a database.

(Something like dclgen under Ingres...)

If not, I would like to know how to get information about
type of each row of a table to write this kind of prog.
(i.e. Where is this explain ?)

--
+----------------------------------+
| Marzullo Laurent |
| Analyste-programmeur |
| SEMA-GROUP Belgium S.A. |
| T�l: (32) 2 333 52 43 |
| e-mail: Laurent.Marzullo@sema.be |
+----------------------------------+

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

From bouncefilter Tue Aug 31 20:01:12 1999
Received: from gateway.digiintranet.ro (IDENT:root@gateway.digiro.net
[194.102.93.10]) by hub.org (8.9.3/8.9.3) with ESMTP id UAA67084
for <pgsql-general@postgreSQL.org>;
Tue, 31 Aug 1999 20:00:57 -0400 (EDT) (envelope-from teo@digiro.net)
Received: from digiro.net (ppp17_v90.digiro.net [194.102.93.87])
by gateway.digiintranet.ro (8.8.7/8.8.7) with ESMTP id CAA01770
for <pgsql-general@postgreSQL.org>; Wed, 1 Sep 1999 02:34:21 +0300
Message-ID: <37CC6C85.480B8685@digiro.net>
Date: Wed, 01 Sep 1999 03:00:05 +0300
From: Teodor Cimpoesu <teo@digiro.net>
X-Mailer: Mozilla 4.51 [en] (Win98; I)
X-Accept-Language: en
MIME-Version: 1.0
CC: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] How to display user-defined functions?
References: <37CC05CF.B69ABE76@amgen.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Mark Dalphin wrote:

Hi,

Is there a way to display user-defined functions? For example, if I define a
function using PL/pgsql, what tables do I query and in what way to learn that
the function exisits and further, what its defintion is? I can find functions
that return known types, eg getTimeStamp, below, shows up with '\df', however
functions defined returning type "opaque" do not show up, eq,
"exon_foreign_keys", below.

afaik this *should* work:
SELECT * FROM pg_proc WHERE procname='your_f_name_here';
I'm not sure of "procname",though
also try to display system tables (I think \dS or something)
and peek into other suspicious pg_* tables :)
[snip]
--
CIMPOESU Teodor, Web Programmer (h)
@ DIGICOM S.A. Bucharest, Romania
@ Internet, site development
@ teo@digiro.net, +(401)-330.47.28

official home page ~ http://www.digiro.net/
Internet web page ~ http://internet.digiro.net/

From bouncefilter Tue Aug 31 23:52:40 1999
Received: from rabies.toodarkpark.org (caffeine@rabies.toodarkpark.org
[207.176.94.148]) by hub.org (8.9.3/8.9.3) with ESMTP id XAA95943
for <pgsql-general@postgreSQL.org>;
Tue, 31 Aug 1999 23:52:19 -0400 (EDT)
(envelope-from caffeine@toodarkpark.org)
Received: from localhost (caffeine@localhost)
by rabies.toodarkpark.org (8.8.8/8.8.8/Debian/GNU) with SMTP id
XAA32727; Tue, 31 Aug 1999 23:49:48 -0500
Date: Wed, 1 Sep 1999 04:49:47 +0000 (GMT)
From: Howie <caffeine@toodarkpark.org>
To: Marzullo Laurent <Laurent.Marzullo@sema.be>
cc: PGSQL General list <pgsql-general@postgreSQL.org>
Subject: Re: [GENERAL] PostgreSQL table data structure generator...
In-Reply-To: <37CBDEA7.14902FBC@sema.be>
Message-ID: <Pine.LNX.3.96.990901044827.22274D-100000@rabies.toodarkpark.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Tue, 31 Aug 1999, Marzullo Laurent wrote:

Hello again,

I propose myself for developping a prog to generate C Source
code for table structure for a Postgres Table.

If someone have something to say about it (features wanted, new
langage, etc) (s)he's welcome. And if someone have already done
it, please stop me now.

Aim of the project:

Generate C/C++ Data structure for a set of table from a PostgreSQL dB.
The C data structure will be :
Struct or Class containing all the column of the table.

or you could use GNUstep's ( http://www.gnustep.org ) database library (
aka Enterprise Objects Framework 1.x ), which provides an OO
wrapper for database independent stuff. you dont have to deal with SQL;
you deal with the objects.

quite nice. NeXT ( now Apple ) has been doing that for quite some time
now.

---
Howie <caffeine@toodarkpark.org> URL: http://www.toodarkpark.org
"The distance between insanity and genius is measured only by success."

From bouncefilter Wed Sep 1 00:53:40 1999
Received: from zoso.frozenwave.com (root@[207.141.25.250])
by hub.org (8.9.3/8.9.3) with ESMTP id AAA09995
for <pgsql-general@postgresql.org>; Wed, 1 Sep 1999 00:53:31 -0400 (EDT)
(envelope-from hixson@frozenwave.com)
Received: from kashmir (root@kashmir.notwork.org [192.168.0.3])
by zoso.frozenwave.com (8.9.3/8.9.3) with SMTP id VAA03028
for <pgsql-general@postgresql.org>; Tue, 31 Aug 1999 21:48:15 -0700
From: Matthew Hixson <hixson@frozenwave.com>
Reply-To: hixson@frozenwave.com
Organization: Frozenwave Communications
To: pgsql-general@postgresql.org
Subject: re-post
Date: Tue, 31 Aug 1999 21:50:21 -0700
X-Mailer: KMail [version 1.0.21]
Content-Type: text/plain
MIME-Version: 1.0
Message-Id: <99083121513303.16902@kashmir>
Content-Transfer-Encoding: 8bit

Amy Cheng requested that I post her reply to me back to the list since she did
not have a copy of it.
-M@

hi,

search or browse "functions" and "PL/pgSQL" (and tcl and c/spi), "triggers".
enough to start-up ;-)
also, there is examples in the source's test directory.

but, you are right, not in depth enough. I asked Several times, in
different forms, in this list, NOT a SINGLE answer! Seems that
nice expert are scarce nowadays. Let me put those questions
together anyway, let's see ...

the difference between Oralce's PL/SQL and PL/pgSQL (so
that we can use the books on PL/SQL),

the difference between PL/pgSQL and tcl (note that postgreSQL also support
tcl now),

does PL/pgSQL support array-like feature? how about tcl in postgreSQL?

if I want use OS (i.e. external to dababase), can I use PL/pgSQL or tcl? if
in C (spi), is it easy? anyway to get perl involved?

From: Matthew Hixson <hixson@frozenwave.com>
Reply-To: hixson@frozenwave.com
To: pgsql-general@postgresql.org
Subject: [GENERAL] stored procedures
Date: Mon, 30 Aug 1999 12:58:45 -0700

Could someone point me to a tutorial or HOWTO regarding stored procedures?
I
know very little about them and need to learn everything I possibly can
about
them as soon as possible. I looked throught the Postgres docs and couldn't
find anything that goes into them in depth.
Thank you,
-M@

--
Matthew Hixson - CIO
FroZenWave Communications
http://www.frozenwave.com

From bouncefilter Wed Sep 1 00:14:07 1999
Received: from rabies.toodarkpark.org (caffeine@rabies.toodarkpark.org
[207.176.94.148]) by hub.org (8.9.3/8.9.3) with ESMTP id AAA02413
for <pgsql-general@postgreSQL.org>; Wed, 1 Sep 1999 00:10:34 -0400 (EDT)
(envelope-from caffeine@toodarkpark.org)
Received: from localhost (caffeine@localhost)
by rabies.toodarkpark.org (8.8.8/8.8.8/Debian/GNU) with SMTP id
AAA32740; Wed, 1 Sep 1999 00:08:27 -0500
Date: Wed, 1 Sep 1999 05:08:27 +0000 (GMT)
From: Howie <caffeine@toodarkpark.org>
To: The Hermit Hacker <scrappy@hub.org>
cc: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] lo_* interface ...
In-Reply-To: <Pine.BSF.4.10.9908311721290.4130-100000@thelab.hub.org>
Message-ID: <Pine.LNX.3.96.990901045208.22274E-100000@rabies.toodarkpark.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Tue, 31 Aug 1999, The Hermit Hacker wrote:

I've been asked about the performance/stability of using BLOBs (lo_*)
under PostgreSQL, and having no experience with them myself, I'm looking
for examples of sites that are, including such stats like size of the
database, max BLOB size, performance and such...

i was planning on moving a mysql database that makes extensive use of
BLOBs to postgres, but the LO support is very space consuming. otherwise,
pgsql is great, dont get me wrong ( this is actually the only db i run
that's been left in mysql-land ). the lo support is stable, or at least
it seemed to be, when i was using it. there was a NULL bug/problem with
the lo package in the contrib dir, though.

for automatic deletes when the lo's corresponding row was deleted, one
would need to use the contrib/lo/ pkg and have a trigger on the table.
unfortunately, this trigger goes nuts when the lo column is null. should
be an easy fix; check for NULL before trying lo_unlink(). you'd want to
use the lo pkg; it just makes life easier.

the size of the database ( ie: tables ) doesnt get significantly larger
since the LO is stored as an OID. there's a physical file, xinv_<oid#>,
under the db dir, however. i never dug into the code, but the file seemed
to be some sort of custom structure/format; the imported object was 1.5k,
but the xinv_<oid> file was larger. if you need specifics i can get those
for you.

working with LO's was somewhat easy; lo_import() reads in the data, makes
a file under the db dir, and returns an oid. lo_export() takes that oid
and exports the data to the filesystem. unfortunately, that brings space
considerations and fs performance into play; in our app, just viewing an
image required querying the db ( granted ), exporting the object from the
db into the filesystem, read()ing & displaying that object, then
unlink()ing it. its a round-about way of doing it, but Oracle's pretty
much the same. to physically remove a LO, one would need to lo_unlink()
it or use the previously mentioned lo pkg in the contrib dir.

overall, the filesize of the LO's ( when compared to the actual data we
sent it ) and having to 'export' the LO into the filesystem were the two
reasons that the db is still mysql-based. mysql does all the BLOB stuff
internally, storing the data in the table. makes for a rather large table
( ours is currently just under 200m, the pgsql-based version came in at
over 500m ) and some odd displays if one did a 'select *' from the
blob-table, but otherwise works nicely.

just fyi, db2 has the ability to store LONG ( aka blob ) data in a
separate tablespace. might be something to look into once postgres
supports tablespaces. else your db dir/partition fills up _very_ quickly.

---
Howie <caffeine@toodarkpark.org> URL: http://www.toodarkpark.org
"The distance between insanity and genius is measured only by success."

From bouncefilter Wed Sep 1 09:43:40 1999
Received: from hotmail.com (law-f248.hotmail.com [209.185.130.213])
by hub.org (8.9.3/8.9.3) with SMTP id JAA78187
for <pgsql-general@postgreSQL.org>; Wed, 1 Sep 1999 09:43:34 -0400 (EDT)
(envelope-from amycq@hotmail.com)
Received: (qmail 19692 invoked by uid 0); 1 Sep 1999 13:43:03 -0000
Message-ID: <19990901134303.19691.qmail@hotmail.com>
Received: from 216.61.142.192 by www.hotmail.com with HTTP;
Wed, 01 Sep 1999 06:43:02 PDT
X-Originating-IP: [216.61.142.192]
From: "amy cheng" <amycq@hotmail.com>
To: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] re-post
Date: Wed, 01 Sep 1999 06:43:02 PDT
Mime-Version: 1.0
Content-Type: text/plain; format=flowed

I can answer least 50% on all of your question only after reading the
"programmer's guide",
one night,( and was also doin' a Postgres+Apache+PHP installation in
parallel).
So did you rtfm?

OK, I understand you are smart! BTW, what is "rtfm" ;-)

the difference between PL/pgSQL and tcl (note that postgreSQL also

support

tcl now),

eww, a big one. tcl is rather a programming language, PL/pgSQL, as it's
name states
is a procedural language, used only in functions.

postgresql now also use tcl as its "intra-server" procedural language.
I have the feeling that tcl is more powerful than PL/pgSQL, and also
tcl is more generally used, seems to me tcl is the future direction,
although PL/pgSQL has more examples now (and closer to Oracle guys).

does PL/pgSQL support array-like feature? how about tcl in postgreSQL?

dunno, did you try?

no. I'm a newbie, but a serious one. That is why I asked so many "non
technical" questions.

if I want use OS (i.e. external to dababase), can I use PL/pgSQL or tcl?

if

in C (spi), is it easy? anyway to get perl involved?

?? I didn't get this Q, if you are refering to postgres API, if you can
use PL/pgSQL
in, say, C code, I think everything that backend sais is valid, will be.
Also have a look @ ecpg. The cleaner way is through libpq. And the C++
API looks also nice.
And yep , you can get perl involved through it's DBI.

I guess what I'm really asking is:

1) can I somehow use perl to
write triggers (and "stored procedures" in general) -- I understand it is
not possible for the whole part, it is only possible for
PL/pgSQL, tcl and C, but can I wirte part of it ? i.e., called by C or tcl
or PL/pgSQL? Why I want to do this? this leads to the second
question:
2) I want to do copy-file-kind of thing in trigger. I know it can
be done through application side. But that is not convenient. And
to do that easily, as everybody knows :-), perl is the best. However, even
without perl, is PL/pgSQL and tcl possible? I assume C can
do it, anybody tried? easy to do?

amy

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com

From bouncefilter Wed Sep 1 10:08:41 1999
Received: from hotmail.com (law-f131.hotmail.com [209.185.131.194])
by hub.org (8.9.3/8.9.3) with SMTP id KAA82100
for <pgsql-general@postgreSQL.org>; Wed, 1 Sep 1999 10:08:00 -0400 (EDT)
(envelope-from amycq@hotmail.com)
Received: (qmail 46148 invoked by uid 0); 1 Sep 1999 14:07:29 -0000
Message-ID: <19990901140729.46147.qmail@hotmail.com>
Received: from 216.61.142.192 by www.hotmail.com with HTTP;
Wed, 01 Sep 1999 07:07:29 PDT
X-Originating-IP: [216.61.142.192]
From: "amy cheng" <amycq@hotmail.com>
To: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] change null to ""
Date: Wed, 01 Sep 1999 07:07:29 PDT
Mime-Version: 1.0
Content-Type: text/plain; format=flowed

You should also post the CREATE statement of PSlot,
does it have a "NOT NULL" constraint for backlink attribute?
if yes, you've got the answer (I would also have a look but
I'm on the other OS right now :> )

No. so, it is JUST an example, intriguing one though.

create table PSlot (
slotname char(20),
pfname text,
slotlink char(20),
backlink char(20)
);

thanks!!!!!!!!!!!!!!!!

amy

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com

From bouncefilter Wed Sep 1 08:49:40 1999
Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1])
by hub.org (8.9.3/8.9.3) with ESMTP id IAA70330
for <pgsql-general@postgresql.org>; Wed, 1 Sep 1999 08:49:25 -0400 (EDT)
(envelope-from jose@sferacarta.com)
Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it
[193.207.10.213])
by bologna.nettuno.it (8.9.3/8.9.3/NETTuno 3.3) with ESMTP id OAA01162;
Wed, 1 Sep 1999 14:49:05 +0200 (MDT)
Received: from rosso.sferacarta.com (sferacarta.com) [10.20.30.5]
by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian))
id 11MBVO-0006CX-00; Wed, 1 Sep 1999 14:36:54 +0000
Message-ID: <37CD1F42.A20F0EFE@sferacarta.com>
Date: Wed, 01 Sep 1999 14:42:42 +0200
From: =?iso-8859-1?Q?Jos=E9?= Soares <jose@sferacarta.com>
X-Mailer: Mozilla 4.5 [it] (Win95; I)
X-Accept-Language: it
MIME-Version: 1.0
To: Teodor Cimpoesu <teo@digiro.net>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to display user-defined functions?
References: <37CC05CF.B69ABE76@amgen.com> <37CC6C85.480B8685@digiro.net>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Your functions are stored in the table pg_proc.
If you want to display the text of a function, try this:

select PROSRC from pg_proc where proname = 'your_function_name';

Jos�

Teodor Cimpoesu ha scritto:

Mark Dalphin wrote:

Hi,

Is there a way to display user-defined functions? For example, if I define a
function using PL/pgsql, what tables do I query and in what way to learn that
the function exisits and further, what its defintion is? I can find functions
that return known types, eg getTimeStamp, below, shows up with '\df', however
functions defined returning type "opaque" do not show up, eq,
"exon_foreign_keys", below.

afaik this *should* work:
SELECT * FROM pg_proc WHERE procname='your_f_name_here';
I'm not sure of "procname",though
also try to display system tables (I think \dS or something)
and peek into other suspicious pg_* tables :)
[snip]
--
CIMPOESU Teodor, Web Programmer (h)
@ DIGICOM S.A. Bucharest, Romania
@ Internet, site development
@ teo@digiro.net, +(401)-330.47.28

official home page ~ http://www.digiro.net/
Internet web page ~ http://internet.digiro.net/

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

From bouncefilter Wed Sep 1 12:11:43 1999
Received: from infoset.cz ([194.213.32.210])
by hub.org (8.9.3/8.9.3) with ESMTP id MAA00520
for <pgsql-general@postgreSQL.org>; Wed, 1 Sep 1999 12:11:28 -0400 (EDT)
(envelope-from dpeder@infoset.cz)
Received: from WinProxy.anywhere (dial-57.gts.cz [194.213.211.57])
by infoset.cz (8.8.7/8.8.7) with SMTP id SAA12353
for <pgsql-general@postgreSQL.org>; Wed, 1 Sep 1999 18:11:24 +0200
Received: from 192.168.1.1 by 192.168.1.3 (WinProxy);
Wed, 1 Sep 1999 18:08:15 +0100
Received: by Dan with Microsoft Mail
id <01BEF4A4.54362100@Dan>; Wed, 1 Sep 1999 18:03:43 +0200
Message-ID: <01BEF4A4.54362100@Dan>
From: =?iso-8859-2?Q?Daniel_P=E9der?= <dpeder@infoset.cz>
To: "pgsql-general@postgreSQL.org" <pgsql-general@postgreSQL.org>
Subject: RE: [GENERAL] Internal limitation of the CREATE VIEW statement
Date: Wed, 1 Sep 1999 18:03:42 +0200
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

probably because the use of reserved word "name" ?
recently I faced similar problem using the word "user" as column name with some of newer PSQL 6.5.xxx
the solution was enclosing it by double quotes like this: select "user", "name", "title" from sometablename;

-----Original Message-----
From: marten@feki.toppoint.de [SMTP:marten@feki.toppoint.de]
Sent: Thursday, September 02, 1999 2:05 AM
To: pgsql-general@postgreSQL.org
Subject: [GENERAL] Internal limitation of the CREATE VIEW statement

I've trouble when creating a view using the following statement:

create view VPHOBJECT as select PHID, BIRTHDAY, COMPANYID, CONTACT,
EMPLOYERID, FEMSALUTATION, HOLDINGID, INTFLGS, MASTERREFERENCEID,
SUPERCOMPANYID, TECHNICALGROUP, CREDITNR, DEBITNR, EMAIL, EMAIL2,
ENTRYSTRING, FAX, FAX2, KUNDNR, NAME, POPLOGIN, POPPASSWORD, TELEFON,
TELEFON2,TELEX, WWW, CUSTOMERNUMBER, TURNOVERTAXNUMBER, REMARK,
LOGINACCOUNT, PASSWD, SURNAME, TITLE, USERFUNCTION from TPHOBJECT

I get an error message:

ERROR: DefineQueryRewrite: rule plain string too long

Even if I do a

create view VPHOBJECT AS SELECT * FROM TPHOBJECT

I get the same error message.

All the other error I receive when creating the database can be
solved by myself - but I've no chance changing this statement.

The other errors I've received:

* errors when creating an index ('asc' and 'desc' are unknown).
* several columns have names which are predefined tokens like
when, interval and so on ...

Marten

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

From bouncefilter Wed Sep 1 16:47:13 1999
Received: from pub.siac.com (pub.siac.com [162.69.5.194])
by hub.org (8.9.3/8.9.3) with ESMTP id QAA35812
for <pgsql-general@postgreSQL.org>; Wed, 1 Sep 1999 16:46:25 -0400 (EDT)
(envelope-from rwagner@siac.com)
Received: from bigmouth.siac.com(162.69.5.8) by pub.siac.com via smap (4.1)
id xma028472; Wed, 1 Sep 99 16:45:35 -0400
Received: from siac.com (rwagner@localhost [127.0.0.1])
by rad.wisdom.siac.com (8.8.6 (PHNE_17135)/8.8.6) with ESMTP id
QAA22276
for <pgsql-general@postgreSQL.org>; Wed, 1 Sep 1999 16:41:50 -0400 (EDT)
Sender: rwagner@siac.com
Message-ID: <37CD8F8E.D01744E0@siac.com>
Date: Wed, 01 Sep 1999 16:41:50 -0400
From: Robert Wagner <rwagner@siac.com>
Reply-To: rwagner@siac.com
Organization: SIAC
X-Mailer: Mozilla 4.5 [en] (X11; I; HP-UX B.10.20 9000/780)
X-Accept-Language: en-GB, en, ru, de, uk
MIME-Version: 1.0
To: pgsql-general@postgreSQL.org
Subject: subscribe
References: <199908041951.PAA15229@rad.wisdom.siac.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

subscribe
end

From bouncefilter Wed Sep 1 17:13:13 1999
Received: from gateway.digiintranet.ro (IDENT:root@gateway.digiro.net
[194.102.93.10]) by hub.org (8.9.3/8.9.3) with ESMTP id RAA39008
for <pgsql-general@postgreSQL.org>; Wed, 1 Sep 1999 17:12:35 -0400 (EDT)
(envelope-from teo@digiro.net)
Received: from digiro.net (ppp88_v90.digiro.net [194.102.93.98])
by gateway.digiintranet.ro (8.8.7/8.8.7) with ESMTP id XAA03648
for <pgsql-general@postgreSQL.org>; Wed, 1 Sep 1999 23:46:04 +0300
Sender: root@gateway.digiintranet.ro
Message-ID: <37CD968E.EC55BFA0@digiro.net>
Date: Thu, 02 Sep 1999 00:11:42 +0300
From: Teodor Cimpoesu <teo@digiro.net>
Organization: DIGICOM S.A.
X-Mailer: Mozilla 4.51 [en] (X11; I; Linux 2.2.5-15 i586)
X-Accept-Language: en
MIME-Version: 1.0
CC: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] re-post
References: <19990901134303.19691.qmail@hotmail.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

amy cheng wrote:

I can answer least 50% on all of your question only after reading the
"programmer's guide",
one night,( and was also doin' a Postgres+Apache+PHP installation in
parallel).
So did you rtfm?

OK, I understand you are smart! BTW, what is "rtfm" ;-)

10x but my point was that you can answer your Qs *only* if you read
first some documentation,
or "read the fine material".

the difference between PL/pgSQL and tcl (note that postgreSQL also

support

tcl now),

eww, a big one. tcl is rather a programming language, PL/pgSQL, as it's
name states
is a procedural language, used only in functions.

postgresql now also use tcl as its "intra-server" procedural language.
I have the feeling that tcl is more powerful than PL/pgSQL, and also
tcl is more generally used, seems to me tcl is the future direction,
although PL/pgSQL has more examples now (and closer to Oracle guys).

I know next to nothing about tcl, but if it fits you needs, go w/ it.

does PL/pgSQL support array-like feature? how about tcl in postgreSQL?

dunno, did you try?

no. I'm a newbie, but a serious one. That is why I asked so many "non
technical" questions.

if I want use OS (i.e. external to dababase), can I use PL/pgSQL or tcl?

if

in C (spi), is it easy? anyway to get perl involved?

?? I didn't get this Q, if you are refering to postgres API, if you can
use PL/pgSQL
in, say, C code, I think everything that backend sais is valid, will be.
Also have a look @ ecpg. The cleaner way is through libpq. And the C++
API looks also nice.
And yep , you can get perl involved through it's DBI.

I guess what I'm really asking is:

1) can I somehow use perl to
write triggers (and "stored procedures" in general) -- I understand it is
not possible for the whole part, it is only possible for
PL/pgSQL, tcl and C, but can I wirte part of it ? i.e., called by C or tcl
or PL/pgSQL? Why I want to do this? this leads to the second

if you have the perl hammer in your hand, don't look at everything like
@ a nail :)
Of course you may write a C function to do system("myscript.pl"), but I
wouldn't
be proud of that :)

question:
2) I want to do copy-file-kind of thing in trigger. I know it can
be done through application side. But that is not convenient. And
to do that easily, as everybody knows :-), perl is the best. However, even
without perl, is PL/pgSQL and tcl possible? I assume C can
do it, anybody tried? easy to do?

read in the programmer's guide:
14. Server Programming Interface
16. libpq
veery interesting, I meant it!

amy

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com

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

--
CIMPOESU Teodor, Web Programmer (h)
@ DIGICOM S.A. Bucharest, Romania
@ Internet, site development
@ teo@digiro.net, +(401)-330.47.28

official home page ~ http://www.digiro.net/
Internet web page ~ http://internet.digiro.net/

From bouncefilter Wed Sep 1 17:47:13 1999
Received: from wall-hq.rockville.axent.com (wall-hq.rockville.axent.com
[38.178.34.254]) by hub.org (8.9.3/8.9.3) with SMTP id RAA43219
for <pgsql-general@postgreSQL.org>; Wed, 1 Sep 1999 17:46:53 -0400 (EDT)
(envelope-from dheinecke@axent.com)
Received: from raven.axent.com by wall-hq.rockville.axent.com
via smtpd (for postgresql.org [209.167.229.14]) with SMTP;
4 Sep 2019 02:48:29 UT
Received: by Raven.rockville.axent.com with Internet Mail Service (5.0.1460.8)
id <Q28VJSHM>; Wed, 1 Sep 1999 17:48:02 -0400
Message-ID: <8A34CE6287D8D211AB0600A0C9D18223515BBA@Raven.rockville.axent.com>
From: David Heinecke <dheinecke@axent.com>
To: pgsql-general@postgreSQL.org
Subject:
Date: Wed, 1 Sep 1999 17:47:59 -0400
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.0.1460.8)
Content-Type: text/plain

From bouncefilter Wed Sep 1 18:11:14 1999
Received: from smtp.dmti.com (smtp.dmti.com [205.174.42.125])
by hub.org (8.9.3/8.9.3) with SMTP id SAA45875
for <pgsql-general@hub.org>; Wed, 1 Sep 1999 18:11:02 -0400 (EDT)
(envelope-from Phil.Oelkers@experian.com)
Received: from gwpoa1.sch.experian.com (172.31.0.10)
by smtp.dmti.com (Connect2-SMTP 4.32.0001540)
for <pgsql-general@hub.org>; Wed, 1 Sep 1999 17:10:39 -0500
Received: from DMTIDOM-Message_Server by gwpoa1.sch.experian.com
with Novell_GroupWise; Wed, 01 Sep 1999 17:07:19 -0500
Message-Id: <s7cd5d47.026@gwpoa1.sch.experian.com>
X-Mailer: Novell GroupWise 5.5
Date: Wed, 01 Sep 1999 17:07:04 -0500
From: "Phil Oelkers" <Phil.Oelkers@experian.com>
To: <pgsql-general@hub.org>
Subject: newbie Connect question
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
X-MIME-Autoconverted: from quoted-printable to 8bit by hub.org id SAA45886

I'm trying to run the following script through interactive postgres.

CONNECT DATABASE BASEBALL;

CREATE UNIQUE index1 on team(team_name);

If I start postgres as

psql baseball

the script runs no problem. If I start it as

pgsql template1 (or any other database) I get a

cann't connect to database error. So my question is
how do I connect to more than one database at a time?

From bouncefilter Wed Sep 1 19:07:16 1999
Received: from candle.pha.pa.us (maillist@s5-03.ppp.op.net [209.152.195.67])
by hub.org (8.9.3/8.9.3) with ESMTP id TAA52328;
Wed, 1 Sep 1999 19:06:23 -0400 (EDT)
(envelope-from maillist@candle.pha.pa.us)
Received: (from maillist@localhost)
by candle.pha.pa.us (8.9.0/8.9.0) id TAA25607;
Wed, 1 Sep 1999 19:05:12 -0400 (EDT)
From: Bruce Momjian <maillist@candle.pha.pa.us>
Message-Id: <199909012305.TAA25607@candle.pha.pa.us>
Subject: Re: [GENERAL] 6.5.1 pg_dump adds "connect as" line
In-Reply-To: <19990828000342.A17199@wallace.ece.rice.edu> from "Ross J.
Reedstrom" at "Aug 28, 1999 00:03:42 am"
To: "Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu>
Date: Wed, 1 Sep 1999 19:05:11 -0400 (EDT)
CC: Ted Nolan SRI Augusta GA <ted@ags.ga.erg.sri.com>,
pgsql-general@postgreSQL.org, pgsql-patches@postgreSQL.org
X-Mailer: ELM [version 2.4ME+ PL56 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

New, poorly documented default of an old feature. It's always been
recommended that when using pg_dump for backup, the -z switch be used,
to dump ACLs (Access Control Lists) As of 6.5.1, this has been made the
default behavior. I call it poorly documented only because this bit me
as well recently, for the same reason, and the switch added to turn ACL
dumping off ( -x ) is not documented in the usage message. I went and
grovelled in the source to find it. Patch below:

Shame on me.

I forgot to put -x in the pg_dump options display. I did get it into
the manual pages, though.

Fixed now.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

From bouncefilter Wed Sep 1 19:24:15 1999
Received: from candle.pha.pa.us (maillist@s5-03.ppp.op.net [209.152.195.67])
by hub.org (8.9.3/8.9.3) with ESMTP id TAA55078
for <pgsql-general@postgreSQL.org>; Wed, 1 Sep 1999 19:23:24 -0400 (EDT)
(envelope-from maillist@candle.pha.pa.us)
Received: (from maillist@localhost)
by candle.pha.pa.us (8.9.0/8.9.0) id TAA25838;
Wed, 1 Sep 1999 19:21:49 -0400 (EDT)
From: Bruce Momjian <maillist@candle.pha.pa.us>
Message-Id: <199909012321.TAA25838@candle.pha.pa.us>
Subject: Re: [GENERAL] Hardware optimising
In-Reply-To: <37C866FE.F7D1191A@france.sun.com> from Dimitri at "Aug 29, 1999
00:47:26 am"
To: Dimitri <dimitri@france.sun.com>
Date: Wed, 1 Sep 1999 19:21:49 -0400 (EDT)
CC: pgsql-general@postgreSQL.org
X-Mailer: ELM [version 2.4ME+ PL56 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Hi Bruce!

I think you are not right with remark about threads...
At least with Sun Solaris you will be much faster with multithreaded
process vs multi processes.
Why? Because Solaris is real multithreaded kernel, so it sees only
threads in the kernel level.
"Process" is a user view for running task, and 1 process = at least 1
thread.
Why multithreaded process wil be faster than multi processes? - Because
switching between threads will cost much less than between processes, so
you wil have more CPU time to continue your job.
And, of course, all threads can use any CPU, so you win more with more
CPU(s), but even with ONE CPU on your machine you will be faster with 10
threads than with 10 processes...

Agreed. Sentenses removed. New text:

We handle each user connection by creating a Unix process. Backend
processes share data buffers and locking information. With multiple
CPU's, multiple backends can easily run on different CPU's.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

From bouncefilter Wed Sep 1 19:27:17 1999
Received: from argh.demon.co.uk (IDENT:grim@argh.demon.co.uk [193.237.6.55])
by hub.org (8.9.3/8.9.3) with ESMTP id TAA55700
for <pgsql-general@postgresql.org>; Wed, 1 Sep 1999 19:26:57 -0400 (EDT)
(envelope-from grim@argh.demon.co.uk)
Received: (from grim@localhost) by argh.demon.co.uk (8.9.3/8.9.3) id AAA21435;
Thu, 2 Sep 1999 00:29:19 +0100
From: Michael Simms <grim@argh.demon.co.uk>
Message-Id: <199909012329.AAA21435@argh.demon.co.uk>
Subject: Re: [GENERAL] How to make a read-write atomic?
To: stolkd@email.com (Daniel Stolk)
Date: Thu, 2 Sep 1999 00:29:19 +0100 (BST)
Cc: pgsql-general@postgresql.org
In-Reply-To: <37CDCC3E.AE36420@email.com> from "Daniel Stolk" at Sep 02,
1999 01:00:46 AM
X-Mailer: ELM [version 2.5 PL0pre8]
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Hi,

I used to use the BEGIN-COMMIT pair in order to ensure that two
processes didn't read the same data and write at the same time, so
messing things up. But I've recently upgraded to the latest version of
postgresql and now it doesn't work anymore. Does anybody know what's
going on?

To explain my situation in more detail:
I want a process to read the database for the entry that has the highest
customerid number. I then want the process to write the next entry into
the database with a customerid that is one larger. But if two processes
read at the same time, then they will both write an entry that has the
same customerid number. How do I keep this from occurring since the
BEGIN command doesn't seem to work for me anymore?

I am using PostgreSQL 6.5.1 on RedHat 6.0

Thanks, Daniel Stolk

If you are simply looking for a unique value, you can use a sequence.

The best way to do this is to make the customerid be type serial. That way
you dont have to do anything to it, when you insert into thew table, the
new row gets a unique incrimented numeric value

M Simms

From bouncefilter Wed Sep 1 11:13:42 1999
Received: from mail.toppoint.de (LaForge.toppoint.de [195.244.243.6])
by hub.org (8.9.3/8.9.3) with SMTP id LAA91952
for <pgsql-general@postgreSQL.org>; Wed, 1 Sep 1999 11:13:05 -0400 (EDT)
(envelope-from feki.toppoint.de!marten@toppoint.de)
Received: by mail.toppoint.de (Smail3.1.29.1)
id <m11MC4M-0002FIC>; Wed, 1 Sep 99 17:13 MET DST
Received: (from marten@localhost) by feki.toppoint.de (8.9.3/8.9.3) id
RAA11079
for pgsql-general@postgreSQL.org; Wed, 1 Sep 1999 17:05:17 -0700
From: marten@feki.toppoint.de
Message-Id: <199909020005.RAA11079@feki.toppoint.de>
Subject: Internal limitation of the CREATE VIEW statement
To: pgsql-general@postgreSQL.org
Date: Wed, 1 Sep 1999 17:05:17 -0700 (MST)
X-Mailer: ELM [version 2.4ME+ PL47 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

I've trouble when creating a view using the following statement:

I get an error message:

ERROR: DefineQueryRewrite: rule plain string too long

Even if I do a

create view VPHOBJECT AS SELECT * FROM TPHOBJECT

I get the same error message.

All the other error I receive when creating the database can be
solved by myself - but I've no chance changing this statement.

The other errors I've received:

* errors when creating an index ('asc' and 'desc' are unknown).
* several columns have names which are predefined tokens like
when, interval and so on ...

Marten

From bouncefilter Wed Sep 1 20:44:15 1999
Received: from pub.siac.com (pub.siac.com [162.69.5.194])
by hub.org (8.9.3/8.9.3) with ESMTP id UAA67276
for <pgsql-general@postgreSQL.org>; Wed, 1 Sep 1999 20:43:28 -0400 (EDT)
(envelope-from rwagner@siac.com)
Received: from bigmouth.siac.com(162.69.5.8) by pub.siac.com via smap (4.1)
id xma005269; Wed, 1 Sep 99 20:42:39 -0400
Received: by SIAC_NOTES_001.wisdom.siac.com(Lotus SMTP MTA v1.2 (600.1
3-26-1998)) id 852567E0.000364AE ; Wed, 1 Sep 1999 20:37:03 -0400
X-Lotus-FromDomain: SIAC_01
From: "Robert Wagner" <rwagner@siac.com>
To: pgsql-general@postgreSQL.org
Message-ID: <852567E0.00036D3A.00@SIAC_NOTES_001.wisdom.siac.com>
Date: Wed, 1 Sep 1999 20:38:47 -0400
Subject: Re: [GENERAL] How to make a read-write atomic?
Mime-Version: 1.0
Content-type: text/plain; charset=us-ascii
Content-Disposition: inline

Hello All,

Does anybody have experience with building a new TCL wish shell with
pgtclsh, so that the postgress commands such as pg_connect will work within
a script?

I want very much to use the latest 8.2 wish in development and production,
but I'm stuck with an old wish because somebody compiled the old wish with
the postgress library, so for database support I need to work with their
old wish!

There are no instructions that I can find, so I'd appreciate any pointers,
download sites, etc!

Cheers
Rob Wagner
SIAC

From bouncefilter Wed Sep 1 19:02:15 1999
Received: from fordfamilymarketing.com (jforddsl.wvi.com [204.245.255.187])
by hub.org (8.9.3/8.9.3) with ESMTP id TAA51732
for <pgsql-general@postgresql.org>; Wed, 1 Sep 1999 19:01:27 -0400 (EDT)
(envelope-from stolkd@email.com)
Received: from email.com (localhost [127.0.0.1])
by fordfamilymarketing.com (8.9.3/8.9.3) with ESMTP id QAA15650
for <pgsql-general@postgresql.org>; Wed, 1 Sep 1999 16:00:46 -0900
Sender: root@fordfamilymarketing.com
Message-ID: <37CDCC3E.AE36420@email.com>
Date: Thu, 02 Sep 1999 01:00:46 +0000
From: Daniel Stolk <stolkd@email.com>
X-Mailer: Mozilla 4.61 [en] (X11; I; Linux 2.2.5-15 i586)
X-Accept-Language: en
MIME-Version: 1.0
To: pgsql-general@postgresql.org
Subject: How to make a read-write atomic?
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Hi,

I used to use the BEGIN-COMMIT pair in order to ensure that two
processes didn't read the same data and write at the same time, so
messing things up. But I've recently upgraded to the latest version of
postgresql and now it doesn't work anymore. Does anybody know what's
going on?

To explain my situation in more detail:
I want a process to read the database for the entry that has the highest
customerid number. I then want the process to write the next entry into
the database with a customerid that is one larger. But if two processes
read at the same time, then they will both write an entry that has the
same customerid number. How do I keep this from occurring since the
BEGIN command doesn't seem to work for me anymore?

I am using PostgreSQL 6.5.1 on RedHat 6.0

Thanks, Daniel Stolk

From bouncefilter Thu Sep 2 04:16:28 1999
Received: from hotmail.com (law-f128.hotmail.com [209.185.131.191])
by hub.org (8.9.3/8.9.3) with SMTP id EAA41713
for <pgsql-general@postgreSQL.org>; Thu, 2 Sep 1999 04:15:55 -0400 (EDT)
(envelope-from oomoomi@hotmail.com)
Received: (qmail 83526 invoked by uid 0); 2 Sep 1999 08:15:24 -0000
Message-ID: <19990902081524.83525.qmail@hotmail.com>
Received: from 195.146.63.210 by www.hotmail.com with HTTP;
Thu, 02 Sep 1999 01:15:22 PDT
X-Originating-IP: [195.146.63.210]
From: "omid omoomi" <oomoomi@hotmail.com>
To: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] How to make a read-write atomic?
Date: Thu, 02 Sep 1999 01:15:22 PDT
Mime-Version: 1.0
Content-Type: text/plain; format=flowed

hi daniel,

Since ex_locks would make some new limitations, for better design,I suggest
that you minimize the time interval between reading the costomerid# and
writing back its ++ .This would make the undesiered event less probable to
accure. I think you are going to use the ID# in an application form (or some
sort of interactiv UIF). So if you really don't need to reserve the ID# (eg.
showing ID# above the form )at the begining, let it be assigned at the time
of submitting.In other hand, any customer can get his new ID# after filling
and sending his data not before that.
Ofcourse there is more than one way skinning a pig!

good luck ,
omid.

From: Vadim Mikheev <vadim@krs.ru>
To: Daniel Stolk <stolkd@email.com>
CC: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] How to make a read-write atomic?
Date: Thu, 02 Sep 1999 09:39:22 +0800

Daniel Stolk wrote:

Hi,

I used to use the BEGIN-COMMIT pair in order to ensure that two
processes didn't read the same data and write at the same time, so
messing things up. But I've recently upgraded to the latest version of
postgresql and now it doesn't work anymore. Does anybody know what's
going on?

To explain my situation in more detail:
I want a process to read the database for the entry that has the highest
customerid number. I then want the process to write the next entry into
the database with a customerid that is one larger. But if two processes
read at the same time, then they will both write an entry that has the
same customerid number. How do I keep this from occurring since the
BEGIN command doesn't seem to work for me anymore?

Use LOCK TABLE IN SHARE ROW EXCLUSIVE MODE before reading max
customerid. Please read doc and "Migration to v6.5" in HISTORY.

Vadim

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

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com

From bouncefilter Wed Sep 1 21:40:23 1999
Received: from sunpine.krs.ru (SunPine.krs.ru [195.161.16.37])
by hub.org (8.9.3/8.9.3) with ESMTP id VAA74329
for <pgsql-general@postgreSQL.org>; Wed, 1 Sep 1999 21:39:32 -0400 (EDT)
(envelope-from vadim@krs.ru)
Received: from krs.ru (dune.krs.ru [195.161.16.38])
by sunpine.krs.ru (8.8.8/8.8.8) with ESMTP id JAA09355;
Thu, 2 Sep 1999 09:39:22 +0800 (KRSS)
Sender: root@sunpine.krs.ru
Message-ID: <37CDD54A.59CAA28A@krs.ru>
Date: Thu, 02 Sep 1999 09:39:22 +0800
From: Vadim Mikheev <vadim@krs.ru>
Organization: OJSC Rostelecom (Krasnoyarsk)
X-Mailer: Mozilla 4.5 [en] (X11; I; FreeBSD 3.0-RELEASE i386)
X-Accept-Language: ru, en
MIME-Version: 1.0
To: Daniel Stolk <stolkd@email.com>
CC: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] How to make a read-write atomic?
References: <37CDCC3E.AE36420@email.com>
Content-Type: text/plain; charset=koi8-r
Content-Transfer-Encoding: 7bit

Daniel Stolk wrote:

Hi,

I used to use the BEGIN-COMMIT pair in order to ensure that two
processes didn't read the same data and write at the same time, so
messing things up. But I've recently upgraded to the latest version of
postgresql and now it doesn't work anymore. Does anybody know what's
going on?

To explain my situation in more detail:
I want a process to read the database for the entry that has the highest
customerid number. I then want the process to write the next entry into
the database with a customerid that is one larger. But if two processes
read at the same time, then they will both write an entry that has the
same customerid number. How do I keep this from occurring since the
BEGIN command doesn't seem to work for me anymore?

Use LOCK TABLE IN SHARE ROW EXCLUSIVE MODE before reading max
customerid. Please read doc and "Migration to v6.5" in HISTORY.

Vadim

From bouncefilter Thu Sep 2 04:21:28 1999
Received: from bbaer.muenster.de (root@bbaer.muenster.de [195.202.32.20])
by hub.org (8.9.3/8.9.3) with ESMTP id EAA42287
for <pgsql-general@postgresql.org>; Thu, 2 Sep 1999 04:21:12 -0400 (EDT)
(envelope-from holger@klawitter.de)
Received: from klawitter.de (holger@mueamc-wan175.citykom.de [195.202.35.175])
by bbaer.muenster.de (8.8.8/8.8.8) with ESMTP id KAA06786
for <pgsql-general@postgresql.org>; Thu, 2 Sep 1999 10:21:08 +0200
Sender: holger@muenster.de
Message-ID: <37CE3373.84A08805@klawitter.de>
Date: Thu, 02 Sep 1999 10:21:07 +0200
From: Holger Klawitter <holger@klawitter.de>
X-Mailer: Mozilla 4.51 [en] (X11; U; Linux 2.2.11 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: pgsql-general@postgresql.org
Subject: backend crashes
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Hi there,

I am using Linus 2.2.11, PostgreSQL 6.5.1, 128M RAM + 256M Swap.
I am operating on a table "links" with 12 text columns and 5200 rows
(many cells are empty). This folloing select crashes the backend:

select
f.id as orig, t.id as dest
into
"better-az"
from
links f, links t
where
f.id <> t.id and
( f.g <> '' and lower(f.g) = lower(t.g) ) and
f.a = '' and
t.a <> '' and
f.d = t.d
;

The message is:
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
We have lost the connection to the backend, so further processing
Terminating.

Is this join just too complex? Or did I trap into a bug?

Regards,
Holger Klawitter

--
Holger Klawitter +49 (0)251 484 0637
holger@klawitter.de http://www.klawitter.de/

From bouncefilter Thu Sep 2 04:33:30 1999
Received: from capitular.tissat.es (capitular.tissat.es [195.77.30.34])
by hub.org (8.9.3/8.9.3) with ESMTP id EAA44153;
Thu, 2 Sep 1999 04:32:30 -0400 (EDT)
(envelope-from echiner@tissat.es)
Received: from tissat.es ([195.77.30.15]) by capitular.tissat.es
(Netscape Messaging Server 3.6) with ESMTP id AAA6614;
Thu, 2 Sep 1999 10:31:55 +0200
Message-ID: <37CE3592.2558E205@tissat.es>
Date: Thu, 02 Sep 1999 10:30:10 +0200
From: "Esteban Chiner Sanz" <echiner@tissat.es>
X-Mailer: Mozilla 4.5 [es] (WinNT; U)
X-Accept-Language: es
MIME-Version: 1.0
To: pgsql-general@postgresql.org, pgsql-sql@postgresql.org
Subject: Max function on Timestamp
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

I have a table in my database with a timestamp field and I can't use a
max function on it. You can do it with any other date types (date,
timespan, etc.) but not with timestamp.
Is there any problem on doing it? Why isn't it implemented in PostgreSQL
6.5.1? What would be the easiest way of doing it?
Thank you in advance,

Esteban Chiner

P.D: There aren't also any "less than" or "greater than" operators on
timestamp...

------------------------------------------------------------
Esteban Chiner Sanz mailto (work): echiner@tissat.es
TISSAT
Av. Aragon, 30, 5� planta Phone: 96 393 9950
Valencia (SPAIN)

From bouncefilter Thu Sep 2 04:46:29 1999
Received: from rtsoft.msk.ru (rtsoft.rmt.ru [194.67.160.206])
by hub.org (8.9.3/8.9.3) with ESMTP id EAA46498
for <pgsql-general@postgreSQL.org>; Thu, 2 Sep 1999 04:46:02 -0400 (EDT)
(envelope-from mak@rtsoft.msk.ru)
Received: from makushina.rtsoft.msk.ru (makushina.rtsoft.msk.ru
[192.168.200.49])
by rtsoft.msk.ru (8.9.3/8.9.3) with SMTP id MAA16465
for <pgsql-general@postgreSQL.org>; Thu, 2 Sep 1999 12:59:48 +0400
Received: by makushina.rtsoft.msk.ru with Microsoft Mail
id <01BEF541.02739080@makushina.rtsoft.msk.ru>;
Thu, 2 Sep 1999 12:45:17 +0400
Message-ID: <01BEF541.02739080@makushina.rtsoft.msk.ru>
From: "Natalya S. Makushina" <mak@rtsoft.msk.ru>
To: "pgsql-general@postgreSQL.org" <pgsql-general@postgreSQL.org>
Subject: GEQO and KSQO problem.
Date: Thu, 2 Sep 1999 12:45:16 +0400
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 8bit
X-MIME-Autoconverted: from quoted-printable to 8bit by hub.org id EAA46522

Hello all!

When i had posted the SQL query like this

"select distinct CLIENTS.CLIENTID,PRINADLEG.PRIM,CLIENTS.NAME_1,CLIENTS.NAME_2,CLIENTS.STRANA,CLIENTS.REGION, CLIENTS.INDEKC,
CLIENTS.GOROD,CLIENTS.OBLAST,CLIENTS.ULICA_DOM,CLIENTS.A_YA,CLIENTS.FLG_ADR,CLIENTS.TYP_CLS, CLIENTS.SITE,CLIENTS.OTRASL,
CLIENTS.VID_D,CLIENTS.KATEGOR,CLIENTS.METKI,CLIENTS.MANAGER,CLIENTS.MANAGER_ID, CLIENTS.PRIM,CLIENTS.ARH,CLIENTS.NEW_F,
CLIENTS.WRITER,CLIENTS.FLG_MY
from CLIENTS ,PRINADLEG ,SOTRUD
where CLIENTS.CLIENTID=SOTRUD.CLIENTID and
( CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (NOT CLIENTS.ARH) and lower(SOTRUD.EMAIL) LIKE lower('%ruslanmr@hotmail.com%')
OR CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (not CLIENTS.ARH) and lower(SOTRUD.EMAIL) LIKE lower('%matukin@hotmail.com%')
OR CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (not CLIENTS.ARH) and lower(SOTRUD.EMAIL) LIKE lower('%knirti@kaluga.ru%')
OR CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (not CLIENTS.ARH) and lower(SOTRUD.EMAIL) LIKE lower('%avk@vniicom.vsu.ru%')
OR CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (not CLIENTS.ARH) and lower(SOTRUD.EMAIL) LIKE lower('%avk@vniicom.vsu.ru%')
)
order by CLIENTS.NEW_F, CLIENTS.NAME_1"

my server worked very, very slow. When i had tried shutdown it's borrowed 1 hour aproximately.

In postgres log file i saw message:
FATAL 1: palloc memory memory exhausted

I have found in the postgres mailing lists that it was a GEQO problem.
I tried to turn on the KSQO, but there was no any effect.
In documentaion there is a phrase like this
"Memory exhaustion may occur with more than 10 relation involved in a query."
But i have only 3 relation involved in query.

What is a solution of the this problem?

Thanks for help

Natalya Makushina
mak@rtsoft.msk.ru

From bouncefilter Thu Sep 2 08:38:31 1999
Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1] (may be
forged)) by hub.org (8.9.3/8.9.3) with ESMTP id IAA78570;
Thu, 2 Sep 1999 08:38:13 -0400 (EDT)
(envelope-from jose@sferacarta.com)
Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it
[193.207.10.213])
by bologna.nettuno.it (8.9.3/8.9.3/NETTuno 3.3) with ESMTP id OAA20843;
Thu, 2 Sep 1999 14:30:34 +0200 (MDT)
Received: from rosso.sferacarta.com (sferacarta.com) [10.20.30.5]
by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian))
id 11MXmA-0008MX-00; Thu, 2 Sep 1999 14:23:42 +0000
Message-ID: <37CE6DA6.B40F424E@sferacarta.com>
Date: Thu, 02 Sep 1999 14:29:26 +0200
From: =?iso-8859-1?Q?Jos=E9?= Soares <jose@sferacarta.com>
X-Mailer: Mozilla 4.5 [it] (Win95; I)
X-Accept-Language: it
MIME-Version: 1.0
To: Esteban Chiner Sanz <echiner@tissat.es>
CC: pgsql-general@postgresql.org, pgsql-sql@postgresql.org
Subject: Re: [GENERAL] Max function on Timestamp
References: <37CE3592.2558E205@tissat.es>
Content-Type: multipart/mixed; boundary="------------DAC176195F4C064AA7CEA545"

Questo � un messaggio multi-parte scritto in formato MIME.
--------------DAC176195F4C064AA7CEA545
Content-Type: multipart/alternative;
boundary="------------368C53E2E25CE33F9079E5BE"

--------------368C53E2E25CE33F9079E5BE
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

It works for me (v6.5.1)
I have the following MAX aggregates:

prova=> \da max
aggname|type |description
-------+--------+-----------
max |abstime |
max |date |
max |datetime|
max |float4 |
max |float8 |
max |int2 |
max |int4 |
max |int8 |
max |money |
max |numeric |
max |text |
max |timespan|
(12 rows)

...
prova=> insert into test values (current_timestamp);
INSERT 720683 1
prova=> select data from test;
data
----------------------
1999-09-02 16:14:24+02
1999-09-02 16:14:27+02
1999-09-02 16:14:53+02
(3 rows)

prova=> select max(data) from test;
max
---------------------------
02/09/1999 16:14:53.00 CEST
(1 row)

If it doesn't work for you try to run the attached script.

Jos�

Esteban Chiner Sanz ha scritto:

I have a table in my database with a timestamp field and I can't use a
max function on it. You can do it with any other date types (date,
timespan, etc.) but not with timestamp.
Is there any problem on doing it? Why isn't it implemented in PostgreSQL
6.5.1? What would be the easiest way of doing it?
Thank you in advance,

Esteban Chiner

P.D: There aren't also any "less than" or "greater than" operators on
timestamp...

------------------------------------------------------------
Esteban Chiner Sanz mailto (work): echiner@tissat.es
TISSAT
Av. Aragon, 30, 5� planta Phone: 96 393 9950
Valencia (SPAIN)

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

--------------368C53E2E25CE33F9079E5BE
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
<tt>It works for me (v6.5.1)</tt>
 <tt>I have the following MAX aggregates:</tt><tt></tt>
<tt>prova=> \da max</tt>
 <tt>aggname|type    |description</tt>
 <tt>-------+--------+-----------</tt>
 <tt>max    |abstime |</tt>
 <tt>max    |date    |</tt>
 <tt>max    |datetime|</tt>
 <tt>max    |float4  |</tt>
 <tt>max    |float8  |</tt>
 <tt>max    |int2    |</tt>
 <tt>max    |int4    |</tt>
 <tt>max    |int8    |</tt>
 <tt>max    |money   |</tt>
 <tt>max    |numeric |</tt>
 <tt>max    |text    |</tt>
 <tt>max    |timespan|</tt>
 <tt>(12 rows)</tt><tt></tt>
<tt>...</tt>
 <tt>prova=> insert into test values (current_timestamp);</tt>
 <tt>INSERT 720683 1</tt>
 <tt>prova=> select data from test;</tt>
 <tt>data</tt>
 <tt>----------------------</tt>
 <tt>1999-09-02 16:14:24+02</tt>
 <tt>1999-09-02 16:14:27+02</tt>
 <tt>1999-09-02 16:14:53+02</tt>
 <tt>(3 rows)</tt><tt></tt>
<tt>prova=> select max(data) from test;</tt>
 <tt>max</tt>
 <tt>---------------------------</tt>
 <tt>02/09/1999 16:14:53.00 CEST</tt>
 <tt>(1 row)</tt><tt></tt>
<tt>If it doesn't work for you try to run the attached script.</tt>
José
  
Esteban Chiner Sanz ha scritto:
<blockquote TYPE=CITE>I have a table in my database with a timestamp field
and I can't use a
 max function on it. You can do it with any other date types (date,
 timespan, etc.) but not with timestamp.
 Is there any problem on doing it? Why isn't it implemented in PostgreSQL
 6.5.1? What would be the easiest way of doing it?
 Thank you in advance,
               
Esteban Chiner
P.D: There aren't also any "less than" or "greater than" operators on
 timestamp...
------------------------------------------------------------
  Esteban Chiner Sanz       
mailto (work): echiner@tissat.es
  TISSAT
  Av. Aragon, 30, 5ª planta  Phone: 96 393 9950
  Valencia (SPAIN)
************</blockquote>
</html>

--------------368C53E2E25CE33F9079E5BE--

--------------DAC176195F4C064AA7CEA545
Content-Type: text/plain; charset=us-ascii;
name="min_max.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="min_max.sql"

-- crea le funzioni MIN e MAX per oid, text, char, varchar, timestamp e time.

drop function timestampsmaller (timestamp,timestamp);
create function timestampsmaller (timestamp,timestamp) returns timestamp as
'
begin
if $1 > $2 then
return $2;
else
return $1;
end if;
end;
' language 'plpgsql';

drop aggregate min timestamp;
create aggregate min (basetype = timestamp, sfunc1 = timestampsmaller,
stype1 = timestamp, stype2 = timestamp);

drop function timestamplarger (timestamp,timestamp);
create function timestamplarger (timestamp,timestamp) returns timestamp as
'
begin
if $1 < $2 then
return $2;
else
return $1;
end if;
end;
' language 'plpgsql';

drop aggregate max timestamp;
create aggregate max (basetype = timestamp, sfunc1 = timestamplarger,
stype1 = timestamp, stype2 = timestamp);

drop function timesmaller (time,time);
create function timesmaller (time,time) returns time as
'
begin
if $1 > $2 then
return $2;
else
return $1;
end if;
end;
' language 'plpgsql';

drop aggregate min time;
create aggregate min (basetype = time, sfunc1 = timesmaller,
stype1 = time, stype2 = time);

drop function timelarger (time,time);
create function timelarger (time,time) returns time as
'
begin
if $1 < $2 then
return $2;
else
return $1;
end if;
end;
' language 'plpgsql';

drop aggregate max time;
create aggregate max (basetype = time, sfunc1 = timelarger,
stype1 = time, stype2 = time);

drop function oidsmaller (oid,oid);
create function oidsmaller (oid, oid) returns oid as
'
begin
if $1 > $2 then
return $2;
else
return $1;
end if;
end;
' language 'plpgsql';

drop function oidlarger (oid,oid);
create function oidlarger (oid, oid) returns oid as
'
begin
if $1 < $2 then
return $2;
else
return $1;
end if;
end;
' language 'plpgsql';

drop aggregate max oid;
create aggregate max (basetype = oid, sfunc1 = oidlarger,
stype1 = oid, stype2 = oid);
drop aggregate min oid;
create aggregate min (basetype = oid, sfunc1 = oidsmaller,
stype1 = oid, stype2 = oid);

drop function txtsmaller(text,text);
create function txtsmaller(text, text) returns text as
'
begin
if $1 > $2 then
return $2;
else
return $1;
end if;
end;
' language 'plpgsql';

drop aggregate min text;
create aggregate min (basetype = text, sfunc1 = txtsmaller,
stype1 = text, stype2 = text);

drop function txtlarger (text,text);
create function txtlarger (text, text) returns text as
'
begin
if $1 < $2 then
return $2;
else
return $1;
end if;
end;
' language 'plpgsql';

drop aggregate max text;
create aggregate max (basetype = text, sfunc1 = txtlarger,
stype1 = text, stype2 = text);
drop table b;
create table b (a varchar(6),d date, t time,dt timestamp, i interval);
insert into b values ('ZETA','12-05-1998','10:20','1997-11-21 10:23',interval '3:31');
insert into b values ('BETA','12-11-1998','22:50','1996-13-30 12:31',interval '2:10');
insert into b values ('ALFA','22-02-1996','12:36','1994-02-11 11:24',interval '1:30');
insert into b values ('GAMA','11-10-1968','16:30','1994-10-31 02:11',interval '2:33');
insert into b values ('NETA',current_date,current_time,current_timestamp,interval '2:33');
select min(a),min(oid),min(d),min(t),min(dt),min(i) from b;
select max(a),max(oid),max(d),max(t),max(dt),max(i) from b;
select oid,* from b;

--------------DAC176195F4C064AA7CEA545--

From bouncefilter Thu Sep 2 08:46:31 1999
Received: from thelab.hub.org (nat203.199.mpoweredpc.net [142.177.203.199])
by hub.org (8.9.3/8.9.3) with ESMTP id IAA79598
for <pgsql-general@postgreSQL.org>; Thu, 2 Sep 1999 08:45:32 -0400 (EDT)
(envelope-from scrappy@hub.org)
Received: from localhost (scrappy@localhost)
by thelab.hub.org (8.9.3/8.9.1) with ESMTP id JAA51997;
Thu, 2 Sep 1999 09:45:31 -0300 (ADT) (envelope-from scrappy@hub.org)
X-Authentication-Warning: thelab.hub.org: scrappy owned process doing -bs
Date: Thu, 2 Sep 1999 09:45:31 -0300 (ADT)
From: The Hermit Hacker <scrappy@hub.org>
To: "Natalya S. Makushina" <mak@rtsoft.msk.ru>
cc: "pgsql-general@postgreSQL.org" <pgsql-general@postgreSQL.org>
Subject: Re: [GENERAL] GEQO and KSQO problem.
In-Reply-To: <01BEF541.02739080@makushina.rtsoft.msk.ru>
Message-ID: <Pine.BSF.4.10.9909020944530.8660-100000@thelab.hub.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

First off, what version of PostgreSQL?

Second...what does 'explain' show for this query...

On Thu, 2 Sep 1999, Natalya S. Makushina wrote:

Hello all!

When i had posted the SQL query like this

"select distinct CLIENTS.CLIENTID,PRINADLEG.PRIM,CLIENTS.NAME_1,CLIENTS.NAME_2,CLIENTS.STRANA,CLIENTS.REGION, CLIENTS.INDEKC,
CLIENTS.GOROD,CLIENTS.OBLAST,CLIENTS.ULICA_DOM,CLIENTS.A_YA,CLIENTS.FLG_ADR,CLIENTS.TYP_CLS, CLIENTS.SITE,CLIENTS.OTRASL,
CLIENTS.VID_D,CLIENTS.KATEGOR,CLIENTS.METKI,CLIENTS.MANAGER,CLIENTS.MANAGER_ID, CLIENTS.PRIM,CLIENTS.ARH,CLIENTS.NEW_F,
CLIENTS.WRITER,CLIENTS.FLG_MY
from CLIENTS ,PRINADLEG ,SOTRUD
where CLIENTS.CLIENTID=SOTRUD.CLIENTID and
( CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (NOT CLIENTS.ARH) and lower(SOTRUD.EMAIL) LIKE lower('%ruslanmr@hotmail.com%')
OR CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (not CLIENTS.ARH) and lower(SOTRUD.EMAIL) LIKE lower('%matukin@hotmail.com%')
OR CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (not CLIENTS.ARH) and lower(SOTRUD.EMAIL) LIKE lower('%knirti@kaluga.ru%')
OR CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (not CLIENTS.ARH) and lower(SOTRUD.EMAIL) LIKE lower('%avk@vniicom.vsu.ru%')
OR CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (not CLIENTS.ARH) and lower(SOTRUD.EMAIL) LIKE lower('%avk@vniicom.vsu.ru%')
)
order by CLIENTS.NEW_F, CLIENTS.NAME_1"

my server worked very, very slow. When i had tried shutdown it's borrowed 1 hour aproximately.

In postgres log file i saw message:
FATAL 1: palloc memory memory exhausted

I have found in the postgres mailing lists that it was a GEQO problem.
I tried to turn on the KSQO, but there was no any effect.
In documentaion there is a phrase like this
"Memory exhaustion may occur with more than 10 relation involved in a query."
But i have only 3 relation involved in query.

What is a solution of the this problem?

Thanks for help

Natalya Makushina
mak@rtsoft.msk.ru

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

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org

From bouncefilter Thu Sep 2 08:01:31 1999
Received: from armadint.teleord.co.uk (armadint.teleord.co.uk
[193.132.206.41])
by hub.org (8.9.3/8.9.3) with ESMTP id IAA74095
for <pgsql-general@postgresql.org>; Thu, 2 Sep 1999 08:00:57 -0400 (EDT)
(envelope-from davida@teleord.co.uk)
Received: from wombat.teleordering.co.uk (wombat.teleordering.co.uk
[172.17.4.7])
by armadint.teleord.co.uk (8.9.1a/8.9.1/DJH/TeleordA 3.0) with ESMTP id
LAA09294
for <pgsql-general@postgresql.org>; Thu, 2 Sep 1999 11:54:02 GMT
Received: from davef (davef.teleordering.co.uk [172.17.4.88])
by wombat.teleordering.co.uk (8.8.6/8.8.6/DJH/TeleordW 2.2) with SMTP
id LAA00462
for <pgsql-general@postgresql.org>; Thu, 2 Sep 1999 11:58:39 GMT
Message-Id: <199909021158.LAA00462@wombat.teleordering.co.uk>
Comments: Authenticated sender is <davida@wombat>
From: "David Fury" <davida@teleord.co.uk>
To: pgsql-general@postgresql.org
Date: Thu, 2 Sep 1999 13:04:56 +0000
MIME-Version: 1.0
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Subject: Join performance
Reply-to: d.fury@teleord.co.uk
Priority: normal
X-mailer: Pegasus Mail for Windows (v2.42a)

I need to find a way of forcing a more efficient execution plan for
the following scenario:

CREATE TABLE live_biblio (
isbn text NOT NULL UNIQUE PRIMARY KEY,
author text,
binding text,
classif text,
editor text,
rrp money,
imprint text,
pub_date date,
title text,
bic1 text);

loaded using COPY, then vacuum analyzed, c. 550,000 rows

CREATE TABLE charts
(
isbn TEXT NOT NULL PRIMARY KEY,
quantity INT4 NOT NULL,
value MONEY NOT NULL,
prev_chart_pos INT4,
weeks_in_chart INT4,
current_pos INT4 NOT NULL
)

loaded using COPY then vacuum analyzed, 5000 rows

PostgreSQL 6.5.1 under RedHat on a twin 450MHz Intel box with 512Mb
memory (nothing else running apart from apache)

The idea of this setup is that we keep a central Bibliographic
database keyed on ISBN and use it as a central reference for various
types of book-sales related data. In this case we have a bestsellers
list (the 'charts' table). To retrieve the top 100 chart with
bibliographic attributes attached, I would do the following.

select cht.current_pos, bib.title, bib.... etc.
from live_biblio bib, charts cht
where bib.isbn = cht.isbn
and current_pos < 101
order by current_pos

However the resulting execution plan (involving a scan on the
live_biblio table) makes use of this technique unworkable (response
timeof c. 15-20 seconds with only one user)

NOTICE: QUERY PLAN:

Sort (cost=1165023.88 rows=184422 width=40)
-> Nested Loop (cost=1165023.88 rows=184422 width=40)
-> Seq Scan on live_biblio bib (cost=31056.12 rows=553155
width=24) -> Index Scan using charts_pkey on charts cht
(cost=2.05 rows=1667 width=16)

What I would really like to see is the restriction on the charts table
being actioned first, with the resulting list of key values driving an
indexed access to the live_biblio table.

Is there any way that I could rearrange things so that Postgres
carries out the join using the primary key indexes of the two tables?
I don't want to have to de-normalise the data to replicate
bibliographic attributes around the various tables of the database.

Any ideas gratefully received.

David.

David Fury
Software Developer

Whitaker BookTrack

d.fury@teleord.co.uk Tel: 01252 742578