Re: Slightly OT: outer joins
On Tue, 20 Nov 2001, Fran Fabrizio wrote:
This is a little off topic but this is the best source of SQL knowledge
I know about so hopefully this will be interesting enough for someone to
answer. :-)
I've got the following tables:
Table people
id fname lname
1 bob smith
2 tom jones
3 jane doe
4 mike porter
Table food
id favorite_food
2 eggrolls
3 ice cream
Table color
id color
1 red
3 blue
I want a query to produce the result set:
fname lname favorite_color favorite_food
bob smith red null
tom jones null eggrolls
jane doe blue ice cream
mike porter null null
I'm having lots of trouble getting the right result or knowing whether
this is even a valid usage of outer joins. Can somebody show me a
working query? Thanks!
Hi Fran!
I'm a beginner, and maybe I will misinform you, but I think in the above
case you want your tables being joined by the ID column. In that case you
_should_ have a row in your auxiliary tables (color, food) for every
occuring IDs in the main table. It will solve your problem:
---
drop table people;
drop table food;
drop table color;
create table people(id int4,fname char(10),lname char(10));
create table food(id int4,favorite_food char(10));
create table color(id int4,color char(10));
copy food from stdin;
1
2 eggrolls
3 ice cream
4
\.
copy color from stdin;
1 red
2
3 blue
4
\.
copy people from stdin;
1 bob smith
2 tom jones
3 jane doe
4 mike porter
\.
select fname,lname,color,favorite_food from people,food,color where
people.id=food.id and people.id=color.id;
Udv: rpetike
From bouncefilter Fri Jan 14 02:41:06 2000
Received: from cap-ferrat.albourne.com (cap-ferrat.albourne.com
[195.89.178.227]) by hub.org (8.9.3/8.9.3) with ESMTP id CAA84363
for <pgsql-general@postgreSQL.org>;
Fri, 14 Jan 2000 02:40:49 -0500 (EST)
(envelope-from a.joubert@albourne.com)
Received: from albourne.com (bishop-rock.albourne.com [195.89.178.230])
by cap-ferrat.albourne.com (8.9.3/8.9.3/Albourne/UKS/2.9/MAPS) with
ESMTP id HAA18017; Fri, 14 Jan 2000 07:39:32 GMT
Sender: a.joubert@albourne.com
Message-ID: <387ED2BA.2916CEB5@albourne.com>
Date: Fri, 14 Jan 2000 07:39:38 +0000
From: Adriaan Joubert <a.joubert@albourne.com>
X-Mailer: Mozilla 4.7 [en] (X11; U; Linux 2.2.14 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: admin <admin@wtbwts.com>
CC: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] searching oid's
References: <Pine.BSF.4.10.10001131420450.71001-100000@server.b0x.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
admin wrote:
Actually, I think pg_dump with the -o flag keeps oid's, therefore allowing
me to keep referential integrity after backup.Using oid's is not a good idea, as they don't automatically get dumped with
pg_dump. And once your referential integrity gets screwed up and you are
using oid's you are really in a mess, as you cannot change oids. Use a serial
field to generate a key for every row, which generates you a sequence of
integers. It is much better than oids at a cost of 4 bytes.
Yes oids get dumped with the -o flag. That is why I said automatically. Fact
remains that you cannot manipulate oids. Should you ever want to copy a table into
an exisiting system you would have to do a new initdb to make sure that the oids
in your table are not in use. And if anything ever gets corrupted it is much
harder to recover and fix it, as you have no control over the oid values that the
system assigns. I would definitely recommend a separate serial value, and I
believe this is also what is recommended in the postgres docs.
Adriaan
From bouncefilter Fri Jan 14 02:47:06 2000
Received: from nella.ujep.cz (root@nella.ujep.cz [195.113.136.38])
by hub.org (8.9.3/8.9.3) with ESMTP id CAA84907
for <pgsql-general@postgresql.org>;
Fri, 14 Jan 2000 02:46:05 -0500 (EST)
(envelope-from borek@rethymno.ujep.cz)
Received: from rethymno.ujep.cz (root@rethymno.ujep.cz [195.113.136.42])
by nella.ujep.cz (8.9.3/8.9.3) with ESMTP id IAA16306
for <pgsql-general@postgresql.org>; Fri, 14 Jan 2000 08:46:02 +0100
Received: from localhost (borek@localhost)
by rethymno.ujep.cz (8.9.3/8.9.3) with ESMTP id IAA07356
for <pgsql-general@postgresql.org>; Fri, 14 Jan 2000 08:46:02 +0100
Date: Fri, 14 Jan 2000 08:45:55 +0100 (CET)
From: Borek Lupomesky <Borek.Lupomesky@ujep.cz>
Reply-To: Borek.Lupomesky@ujep.cz
To: pgsql-general@postgresql.org
Subject: Does patch for GROUP BY/aggr. bug exist?
Message-ID: <Pine.LNX.4.20.0001140844490.7171-100000@rethymno.ujep.cz>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hello,
Is a patch for GROUP BY/aggregates returing one-line response instead
of empty one available somewhere?
Bye Borek
- --
=====================================================================
BOREK LUPOMESKY, network administrator University of J. E. Purkyne
Ceske mladeze 8
WWW: http://www.ujep.cz/~lupomesk/ Usti nad Labem, 40096
IRCnet: Borek @ #usti The Czech Republic
PGP keyid: B6A06AEB ICQ: 10139578 tel: +420-602-376368
==========[ MIME/ISO-8859-2 & PGP encrypted mail welcome ]===========
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.0 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iD8DBQE4ftQ6A6dWI7agausRApcSAKCqAe4Uk1LSD0BKATDo2t4sgdRFpQCfS2ir
5t72hrsegREdXwx81Znahic=
=WlXC
-----END PGP SIGNATURE-----
From bouncefilter Thu Jan 13 18:38:00 2000
Received: from clio.trends.ca (root@clio.trends.ca [209.47.148.2])
by hub.org (8.9.3/8.9.3) with ESMTP id SAA64512
for <pgsql-general@postgreSQL.org>;
Thu, 13 Jan 2000 18:37:59 -0500 (EST)
(envelope-from pgman@candle.pha.pa.us)
Received: from candle.pha.pa.us (s5-03.ppp.op.net [209.152.195.67])
by clio.trends.ca (8.9.3+Sun/8.9.3) with ESMTP id SAA24056
for <pgsql-general@postgreSQL.org>;
Thu, 13 Jan 2000 18:37:55 -0500 (EST)
Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id SAA14168
for pgsql-general@postgreSQL.org; Thu, 13 Jan 2000 18:33:52 -0500 (EST)
Received: from renoir.op.net (root@renoir.op.net [207.29.195.4])
by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id SAA12559
for <pgman@candle.pha.pa.us>; Thu, 13 Jan 2000 18:01:03 -0500 (EST)
Received: from fluweelblad.leidenuniv.nl (fluweelblad.LeidenUniv.nl
[132.229.149.131]) by renoir.op.net (o1/$Revision: 1.18 $) with
ESMTP id RAA27667 for <pgman@candle.pha.pa.us>;
Thu, 13 Jan 2000 17:55:20 -0500 (EST)
Received: from hades ([192.168.2.100])
by fluweelblad.leidenuniv.nl (8.9.3/8.9.3) with SMTP id XAA08003
for <pgman@candle.pha.pa.us>; Thu, 13 Jan 2000 23:54:43 +0100
Message-ID: <00b601bf5e66$681a6a60$6402a8c0@leidenuniv.nl>
From: "Martijn Stalenhoef" <staaltje@dds.nl>
To: <pgman@candle.pha.pa.us>
Subject: PostgreSQL Question
Date: Fri, 14 Jan 2000 00:07:30 -0800
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 5.00.2615.200
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200
Sender: pgman@candle.pha.pa.us
Hello!
There's one thing that might be useful to add to the FAQ, as I can't seem to
find the info in the docs:
Is there a way to set the database location at compile time to something
other than PREFIX/data?
My startegy is to separate the data from the applications and I'm sure I'm
not the only one that does this.
Thanks for your time
Martijn
From bouncefilter Fri Jan 14 07:44:10 2000
Received: from backup.poznan.mtl.pl (IDENT:root@backup.poznan.mtl.pl
[212.160.241.147]) by hub.org (8.9.3/8.9.3) with ESMTP id HAA65745
for <pgsql-general@postgresql.org>;
Fri, 14 Jan 2000 07:43:34 -0500 (EST)
(envelope-from m.mazurek@multinet.pl)
Received: from localhost (mazek@localhost)
by arka.poznan.mtl.pl (8.9.3/8.9.2) with ESMTP id JAA37059;
Fri, 14 Jan 2000 09:21:16 +0100 (CET)
X-Authentication-Warning: arka.poznan.mtl.pl: mazek owned process doing -bs
Date: Fri, 14 Jan 2000 09:21:15 +0100 (CET)
From: Marcin Mazurek - Multinet SA - Poznan <m.mazurek@multinet.pl>
X-Sender: mazek@arka.poznan.mtl.pl
To: Barry Baldwin <bbaldwin@indyme.com>
cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Image data type
In-Reply-To: <319A9588FB5ED2119FAC00A0C912E3292786CC@INDYMEEXCH>
Message-ID: <Pine.BSF.4.21.0001140920100.3812-100000@arka.poznan.mtl.pl>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
On Thu, 13 Jan 2000, Barry Baldwin wrote:
I've read the users guide and administrators guide and I don't see any
mention of a data type that would allow you to store image files.
Is there such a data type? The users guide mentions being able to define
your own types but says that it is discussed elsewhere. If there is
no data type to store images, will there be in the future?
You can use large objects, with examples from jdbc driver I manage to do
some banner system with images stored in postgres.
mazek
From bouncefilter Fri Jan 14 03:46:07 2000
Received: from qube.dsdeurne.nl (41.17.dialup.globalxs.nl [213.196.17.41] (may
be forged)) by hub.org (8.9.3/8.9.3) with ESMTP id DAA13895
for <pgsql-general@postgreSQL.org>;
Fri, 14 Jan 2000 03:46:04 -0500 (EST)
(envelope-from w.aarts@dsdeurne.nl)
Received: from dsdeurne.nl (qube.dsdeurne.nl [10.254.211.253])
by qube.dsdeurne.nl (8.9.2/8.9.2) with SMTP id JAA17741;
Fri, 14 Jan 2000 09:41:51 +0100 (CET)
Date: Fri, 14 Jan 2000 09:41:51 +0100 (CET)
Message-Id: <200001140841.JAA17741@qube.dsdeurne.nl>
To: The Hermit Hacker <scrappy@hub.org>,
Paul Branston <apbran@rannoch.demon.co.uk>
Subject: Re: [GENERAL] Database synchronisation over the internet...
From: Wim Aarts <w.aarts@dsdeurne.nl>
X-Mailer: TWIG 2.1.0
Cc: pgsql-general@postgreSQL.org
Reply-To: w.aarts@dsdeurne.nl
I am also working on the self same thing. I am using ssh to authenticate
and encrpyt the connection. The master database uses pg_dump, since the
data is not too large, to create a file which is securely copied via scp
to the slave host. An ssh connection then logs into the slave,drops the
table and loads the data from the dump file which has been copied over.seems to work so far, but there must be more refinements I can add.
use ssh to create a tunnel between server A and server B, then, from
Server A, issue somethig like:psql -h ServerB -p 5432 -c "drop table <tablename>";
pg_dump tablename | psql -h ServerB -p 5432 ...
So encryption will be with ssh. Thanks.
Then, considering both options, my question would be what happens if the
internet connection failed for some period of time? Will the second command
keep visitors waiting?
Are there any different kind of solutions? Where only changed data is synced?
Is such a solution likely to be slower than using dumps?
Cheers Wim.
From bouncefilter Fri Jan 14 08:14:10 2000
Received: from hanka.synergie.cz (synergiecb.vol.cz [212.27.193.24])
by hub.org (8.9.3/8.9.3) with SMTP id IAA71871
for <pgsql-general@postgresql.org>;
Fri, 14 Jan 2000 08:14:02 -0500 (EST)
(envelope-from karel.hojdar@synergie.cz)
Received: (qmail 27633 invoked by uid 1006); 14 Jan 2000 14:14:27 -0000
Received: from gt.synergie.cz (HELO nt?main?sg.synergie.cz) (212.27.193.34)
by hanka.synergie.cz with SMTP; 14 Jan 2000 14:14:27 -0000
Received: by NT_MAIN_SG with Internet Mail Service (5.0.1460.8)
id <C79RWP9D>; Fri, 14 Jan 2000 14:13:16 +0100
Message-ID: <F0F2C79F20CFD2119D07080009DC528431E153@NT_MAIN_SG>
From: "Hojdar Karel Ing." <karel.hojdar@synergie.cz>
To: "'pgsql-general@postgresql.org'" <pgsql-general@postgresql.org>
Subject: How to get number of the week from datetime?
Date: Fri, 14 Jan 2000 14:13:15 +0100
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.0.1460.8)
Content-Type: text/plain
Hi,
how I can get some agregates (avg, min, max) for whole week (in specified
year)?
For example from table with two columns : time datetime and value float8.
And I want to get average of value based on whole weeks.
Thanks Karel
From bouncefilter Fri Jan 14 08:19:10 2000
Received: from thelab.hub.org (nat200.60.mpoweredpc.net [142.177.200.60])
by hub.org (8.9.3/8.9.3) with ESMTP id IAA72365
for <pgsql-general@postgreSQL.org>;
Fri, 14 Jan 2000 08:18:40 -0500 (EST) (envelope-from scrappy@hub.org)
Received: from localhost (scrappy@localhost)
by thelab.hub.org (8.9.3/8.9.1) with ESMTP id JAA85611;
Fri, 14 Jan 2000 09:18:52 -0400 (AST) (envelope-from scrappy@hub.org)
X-Authentication-Warning: thelab.hub.org: scrappy owned process doing -bs
Date: Fri, 14 Jan 2000 09:18:52 -0400 (AST)
From: The Hermit Hacker <scrappy@hub.org>
To: Wim Aarts <w.aarts@dsdeurne.nl>
cc: Paul Branston <apbran@rannoch.demon.co.uk>, pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] Database synchronisation over the internet...
In-Reply-To: <200001140841.JAA17741@qube.dsdeurne.nl>
Message-ID: <Pine.BSF.4.21.0001140915040.46499-100000@thelab.hub.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
On Fri, 14 Jan 2000, Wim Aarts wrote:
I am also working on the self same thing. I am using ssh to authenticate
and encrpyt the connection. The master database uses pg_dump, since the
data is not too large, to create a file which is securely copied via scp
to the slave host. An ssh connection then logs into the slave,drops the
table and loads the data from the dump file which has been copied over.seems to work so far, but there must be more refinements I can add.
use ssh to create a tunnel between server A and server B, then, from
Server A, issue somethig like:psql -h ServerB -p 5432 -c "drop table <tablename>";
pg_dump tablename | psql -h ServerB -p 5432 ...So encryption will be with ssh. Thanks.
Then, considering both options, my question would be what happens if the
internet connection failed for some period of time? Will the second command
keep visitors waiting?
That's the big risk on this one...you need to be very confident in your
connections, unfortunately ...
Are there any different kind of solutions? Where only changed data is
synced? Is such a solution likely to be slower than using dumps?
Currently, none that I'm aware of...if you are into programming, you could
probably write a quick-n-dirty program that connected to both databases at
the same time and assuming that you have some sort of unique key on the
tables in question, compared the tuple on the master server with what the
slave server has at this time, and if different, updates the slave with
the info from the master ...
There have been several ppl talking about replication issues...best place
to look is the archives and search for replication ...
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
From bouncefilter Fri Jan 14 08:24:10 2000
Received: from hanka.synergie.cz (synergiecb.vol.cz [212.27.193.24])
by hub.org (8.9.3/8.9.3) with SMTP id IAA72811
for <pgsql-general@postgresql.org>;
Fri, 14 Jan 2000 08:23:41 -0500 (EST)
(envelope-from karel.hojdar@synergie.cz)
Received: (qmail 27685 invoked by uid 1006); 14 Jan 2000 14:24:03 -0000
Received: from gt.synergie.cz (HELO nt?main?sg.synergie.cz) (212.27.193.34)
by hanka.synergie.cz with SMTP; 14 Jan 2000 14:24:03 -0000
Received: by NT_MAIN_SG with Internet Mail Service (5.0.1460.8)
id <C79RWP9N>; Fri, 14 Jan 2000 14:22:49 +0100
Message-ID: <F0F2C79F20CFD2119D07080009DC528431E155@NT_MAIN_SG>
From: "Hojdar Karel Ing." <karel.hojdar@synergie.cz>
To: "'pgsql-general@postgresql.org'" <pgsql-general@postgresql.org>
Subject: How to get number of the week from datetime?
Date: Fri, 14 Jan 2000 14:22:48 +0100
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.0.1460.8)
Content-Type: text/plain
Hi,
how I can get some agregates (avg, min, max) for whole week (in specified
year)?
For example from table with two columns : time datetime and value float8.
And I want to get average of value based on whole weeks. If I try to use
date_part('week',time) - Postgresql doesn't recognize word 'week'.
Thanks Karel
From bouncefilter Fri Jan 14 08:52:14 2000
Received: from sultan.intranet.org (root@ppp-84-74.infonie.fr [195.242.84.74])
by hub.org (8.9.3/8.9.3) with ESMTP id IAA79370;
Fri, 14 Jan 2000 08:51:45 -0500 (EST) (envelope-from advl@advl.org)
Received: from localhost (localhost [[UNIX: localhost]])
by sultan.intranet.org (8.9.3/8.9.3) id PAA01102;
Fri, 14 Jan 2000 15:02:00 +0100
From: Compte utilisateur Sultan-advl <webmaster@advl.org>
Reply-To: webmaster@advl.org
Organization: ADVL
To: pgsql-interfaces@postgresql.org, pgman@candle.pha.pa.us,
pgsql-novice@postgresql.org, pgsql-general@postgresql.org
Subject: psql ERROR : Character Types Tuple is too big: size xxxxx
Date: Fri, 14 Jan 2000 14:29:34 +0100
X-Mailer: KMail [version 1.0.21]
Content-Type: text/plain
Cc: jks@p1.selectacast.net, chalaoux@cybercable.fr
MIME-Version: 1.0
Message-Id: <00011415015800.01007@sultan>
Content-Transfer-Encoding: 8bit
X-MIME-Autoconverted: from quoted-printable to 8bit by hub.org id IAA79384
Hi,
From Bruce online book :
"There are synonyms for the terms table, row, and column. Table is more
formally referred to as a relation or class, row as record or tuple, and column
as field or attribute.
"
====
8Kb limit apply to :
'tuple' when insert ?
'tuple' in design stage (what's append with fixe types) ?
column (Case of fixe types) ???
What's wrong ?
- Design database
- Postgresql limit
Can't find informations on documentation and mail archive's obscure on 8Kb
limit.
Thanks fo help.
Emmanuel DISCORS
http://www.advl.org/kiwi/
===================
Error under psql interface :
A) The tuple in the 'insert' is < 8K but the tuple in table
description is > 8Kb
newkiwi=> insert into localisation values ('999', 'ee', 'ff', '999', 'gg',
'hh', 'ii', 'jj', '999'); ERROR: Tuple is too big: size 12356
newkiwi=> \d localisation
Table = localisation
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| id_loc | int4 not null | 4 |
| voie_loc | char() | 2048 |
| codebat_loc | char() | 2048 |
| idplan_loc | int4 | 4 |
| xplan_loc | char() | 2048 |
| yplan_loc | char() | 2048 |
| acces_loc | char() | 2048 |
| accesen_loc | char() | 2048 |
| id_ville_loc | int4 | 4 |
+----------------------------------+----------------------------------+-------+
Indices: localisation_pkey
localisationi1
localisationi2
localisationi3B) The tuple in the 'insert' is < 8K and the tuple in table
description is < 8Kb
newkiwi=> insert into localisation values ('999', 'ee', 'ff', '999', 'gg', 'hh', 'ii', 'jj', '999');
INSERT 62016 1
newkiwi=> insert into localisation values ('999', 'ee', 'ff', '999', 'gg', 'hh', 'ii', 'jj', '999');
ERROR: Cannot insert a duplicate key into a unique index
newkiwi=> insert into localisation values ('9998', 'ee', 'ff', '999', 'gg', 'hh', 'ii', 'jj', '999');
INSERT 62018 1
newkiwi=> \d localisation
Table = localisation
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| id_loc | int4 not null | 4 |
| voie_loc | char() | 1024 |
| codebat_loc | char() | 1024 |
| idplan_loc | int4 | 4 |
| xplan_loc | char() | 1024 |
| yplan_loc | char() | 1024 |
| acces_loc | char() | 1024 |
| accesen_loc | char() | 1024 |
| id_ville_loc | int4 | 4 |
+----------------------------------+----------------------------------+-------+
Indices: localisation_pkey
localisationi1
localisationi2
localisationi3
===============================================From bouncefilter Fri Jan 14 10:35:12 2000
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
by hub.org (8.9.3/8.9.3) with ESMTP id KAA06124;
Fri, 14 Jan 2000 10:34:19 -0500 (EST)
(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id KAA29534;
Fri, 14 Jan 2000 10:34:14 -0500 (EST)
To: webmaster@advl.org
cc: pgsql-interfaces@postgreSQL.org, pgsql-novice@postgreSQL.org,
pgsql-general@postgreSQL.org
Subject: Re: [INTERFACES] psql ERROR : Character Types Tuple is too big: size
xxxxx
In-reply-to: <00011415015800.01007@sultan>
References: <00011415015800.01007@sultan>
Comments: In-reply-to Compte utilisateur Sultan-advl <webmaster@advl.org>
message dated "Fri, 14 Jan 2000 14:29:34 +0100"
Date: Fri, 14 Jan 2000 10:34:14 -0500
Message-ID: <29531.947864054@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
Compte utilisateur Sultan-advl <webmaster@advl.org> writes:
newkiwi=> insert into localisation values ('999', 'ee', 'ff', '999', 'gg', 'hh', 'ii', 'jj', '999'); ERROR: Tuple is too big: size 12356 newkiwi=> \d localisation Table = localisation +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | id_loc | int4 not null | 4 | | voie_loc | char() | 2048 | | codebat_loc | char() | 2048 | | idplan_loc | int4 | 4 | | xplan_loc | char() | 2048 | | yplan_loc | char() | 2048 | | acces_loc | char() | 2048 | | accesen_loc | char() | 2048 | | id_ville_loc | int4 | 4 | +----------------------------------+----------------------------------+-------+
Well, you've got six fixed-size 2K fields, which are going to take up
12K all by themselves, plus there's a few dozen bytes of overhead.
I'd suggest using varchar or text instead of char --- do you really
need 12K of blank-padding in every record?
regards, tom lane
From bouncefilter Fri Jan 14 10:55:13 2000
Received: from ra.bpsi.net (ra.bpsi.net [199.199.134.1])
by hub.org (8.9.3/8.9.3) with ESMTP id KAA09159;
Fri, 14 Jan 2000 10:54:19 -0500 (EST)
(envelope-from duccini@bpsi.net)
Received: (from duccini@localhost) by ra.bpsi.net (8.9.0/8.9.0) id JAA24332;
Fri, 14 Jan 2000 09:54:09 -0600 (CST)
Date: Fri, 14 Jan 2000 09:54:07 -0600 (CST)
From: "D. Duccini" <duccini@backpack.com>
X-Sender: duccini@ra.bpsi.net
To: Tom Lane <tgl@sss.pgh.pa.us>
cc: webmaster@advl.org, pgsql-interfaces@postgreSQL.org,
pgsql-novice@postgreSQL.org, pgsql-general@postgreSQL.org
Subject: Re: [NOVICE] Re: [INTERFACES] psql ERROR : Character Types Tuple is
too big: size xxxxx
In-Reply-To: <29531.947864054@sss.pgh.pa.us>
Message-ID: <Pine.GSO.3.96.1000114095253.23313B-100000@ra.bpsi.net>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
and on top of that, why insert character strings into columns expecting
numeric values?
defintely use VARCHAR unless you are going to be filling those columns up
-duck
Compte utilisateur Sultan-advl <webmaster@advl.org> writes:
newkiwi=> insert into localisation values ('999', 'ee', 'ff', '999', 'gg', 'hh', 'ii', 'jj', '999'); ERROR: Tuple is too big: size 12356 newkiwi=> \d localisation Table = localisation +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | id_loc | int4 not null | 4 | | voie_loc | char() | 2048 | | codebat_loc | char() | 2048 | | idplan_loc | int4 | 4 | | xplan_loc | char() | 2048 | | yplan_loc | char() | 2048 | | acces_loc | char() | 2048 | | accesen_loc | char() | 2048 | | id_ville_loc | int4 | 4 | +----------------------------------+----------------------------------+-------+Well, you've got six fixed-size 2K fields, which are going to take up
12K all by themselves, plus there's a few dozen bytes of overhead.I'd suggest using varchar or text instead of char --- do you really
need 12K of blank-padding in every record?regards, tom lane
************
-----------------------------------------------------------------------------
david@backpack.com BackPack Software, Inc. www.backpack.com
+1 651.645.7550 voice "Life is an Adventure.
+1 651.645.9798 fax Don't forget your BackPack!"
-----------------------------------------------------------------------------From bouncefilter Fri Jan 14 11:10:12 2000
Received: from consumercontact.com ([209.167.202.12])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA15299
for <pgsql-general@postgresql.org>;
Fri, 14 Jan 2000 11:09:45 -0500 (EST)
(envelope-from rick@consumercontact.com)
Received: from consumercontact.com ([192.168.1.54])
by consumercontact.com (8.8.7/8.8.7) with ESMTP id LAA04101;
Fri, 14 Jan 2000 11:08:41 -0500
Message-ID: <387F4A5D.6A50CECE@consumercontact.com>
Date: Fri, 14 Jan 2000 11:10:05 -0500
From: Rick Delaney <rick@consumercontact.com>
X-Mailer: Mozilla 4.61 [en] (Win95; U)
X-Accept-Language: en
MIME-Version: 1.0
To: Adriaan Joubert <a.joubert@albourne.com>
CC: admin <admin@wtbwts.com>, pgsql-general@postgresql.org
Subject: Oids vs Serial fields (was Re: [GENERAL] searching oid's)
References: <Pine.BSF.4.10.10001131420450.71001-100000@server.b0x.com>
<387ED2BA.2916CEB5@albourne.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Adriaan Joubert wrote:
Yes oids get dumped with the -o flag. That is why I said automatically. Fact
remains that you cannot manipulate oids. Should you ever want to copy a table into
an exisiting system you would have to do a new initdb to make sure that the oids
in your table are not in use. And if anything ever gets corrupted it is much
harder to recover and fix it, as you have no control over the oid values that the
system assigns. I would definitely recommend a separate serial value, and I
believe this is also what is recommended in the postgres docs.
You make some good points, but where is this recommended in the docs? I'd like
to see more of "serial vs oid" if there is something.
Bruce's book doesn't recommend one over the other, though it does mention some
oid limitations. It doesn't mention that the sequence for a serial value isn't
dropped when its table is, btw.
I'd be interested in what others have to say on this topic.
--Rick
From bouncefilter Fri Jan 14 11:20:12 2000
Received: from henry.newn.cam.ac.uk (henry.newn.cam.ac.uk [131.111.204.130])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA16393
for <pgsql-general@postgresql.org>;
Fri, 14 Jan 2000 11:19:16 -0500 (EST)
(envelope-from prlw1@newn.cam.ac.uk)
Received: from [131.111.204.180] (helo=quartz.newn.cam.ac.uk)
by henry.newn.cam.ac.uk with esmtp (Exim 2.12 #1) id 1299Rf-0006V8-00
for pgsql-general@postgresql.org; Fri, 14 Jan 2000 16:19:27 +0000
Received: from prlw1 by quartz.newn.cam.ac.uk with local (Exim 2.12 #1)
id 1299RV-00056y-00
for pgsql-general@postgresql.org; Fri, 14 Jan 2000 16:19:17 +0000
Date: Fri, 14 Jan 2000 16:19:17 +0000
From: Patrick Welche <prlw1@newn.cam.ac.uk>
To: pgsql-general@postgresql.org
Subject: Date format
Message-ID: <20000114161917.I19251@quartz.newn.cam.ac.uk>
Reply-To: prlw1@cam.ac.uk
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Mutt/1.1.1i
Someone posted a message here asking how you set the date style. We went
through
- environment variable PGDATESTYLE
- SET DateStyle TO
- the -e flag to the backend
but of course if you are using ISO it isn't necessary!! There is no
ambiguity in
test=> create table tab (t datetime);
CREATE
test=> insert into tab values('2000-1-2');
INSERT 416553 1
test=> select * from tab;
t
------------------------------
Sun 02 Jan 00:00:00 2000 GMT
(1 row)
The -e exists because there would be ambiguity between European/US, but with
year first, it must be yyyy-mm-dd (at least for sufficiently large years).
Sorry I can't remember your email address.
Cheers,
Patrick
From bouncefilter Fri Jan 14 11:53:12 2000
Received: from candle.pha.pa.us (root@s5-03.ppp.op.net [209.152.195.67])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA24612
for <pgsql-general@postgreSQL.org>;
Fri, 14 Jan 2000 11:52:32 -0500 (EST)
(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id
LAA01175;
Fri, 14 Jan 2000 11:43:37 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200001141643.LAA01175@candle.pha.pa.us>
Subject: Re: Oids vs Serial fields (was Re: [GENERAL] searching oid's)
In-Reply-To: <387F4A5D.6A50CECE@consumercontact.com> from Rick Delaney at "Jan
14, 2000 11:10:05 am"
To: Rick Delaney <rick@consumercontact.com>
Date: Fri, 14 Jan 2000 11:43:37 -0500 (EST)
CC: Adriaan Joubert <a.joubert@albourne.com>, admin <admin@wtbwts.com>,
pgsql-general@postgreSQL.org
X-Mailer: ELM [version 2.4ME+ PL66 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Adriaan Joubert wrote:
Yes oids get dumped with the -o flag. That is why I said automatically. Fact
remains that you cannot manipulate oids. Should you ever want to copy a table into
an exisiting system you would have to do a new initdb to make sure that the oids
in your table are not in use. And if anything ever gets corrupted it is much
harder to recover and fix it, as you have no control over the oid values that the
system assigns. I would definitely recommend a separate serial value, and I
believe this is also what is recommended in the postgres docs.You make some good points, but where is this recommended in the docs? I'd like
to see more of "serial vs oid" if there is something.Bruce's book doesn't recommend one over the other, though it does mention some
oid limitations. It doesn't mention that the sequence for a serial value isn't
dropped when its table is, btw.
Is there some issue in the comparison I missed?
That is a good point about the sequence not being dropped.
--
Bruce Momjian | http://www.op.net/~candle
pgman@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 19026From bouncefilter Fri Jan 14 12:14:13 2000
Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1])
by hub.org (8.9.3/8.9.3) with ESMTP id MAA32738
for <pgsql-general@postgresql.org>;
Fri, 14 Jan 2000 12:13:16 -0500 (EST)
(envelope-from jose@sferacarta.com)
Received: from proxy.sferacarta.com (sfcabop1.nettuno.it [193.207.10.213])
by bologna.nettuno.it (8.9.3/8.9.3/NETTuno 4.1) with ESMTP id SAA23236;
Fri, 14 Jan 2000 18:12:10 +0100 (MET)
Received: from rosso.sferacarta.com (sferacarta.com) [10.20.30.5]
by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian))
id 129BBw-0006Zk-00; Fri, 14 Jan 2000 18:11:20 +0000
Message-ID: <387F5973.12A8D4C5@sferacarta.com>
Date: Fri, 14 Jan 2000 18:14:27 +0100
From: Jose Soares <jose@sferacarta.com>
X-Mailer: Mozilla 4.6 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
To: "Hojdar Karel Ing." <karel.hojdar@synergie.cz>
CC: "'pgsql-general@postgresql.org'" <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] How to get number of the week from datetime?
References: <F0F2C79F20CFD2119D07080009DC528431E155@NT_MAIN_SG>
Content-Type: multipart/mixed; boundary="------------D76527B467D83F77A8A400CA"
This is a multi-part message in MIME format.
--------------D76527B467D83F77A8A400CA
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
"Hojdar Karel Ing." wrote:
Hi,
how I can get some agregates (avg, min, max) for whole week (in specified
year)?
For example from table with two columns : time datetime and value float8.
And I want to get average of value based on whole weeks. If I try to use
date_part('week',time) - Postgresql doesn't recognize word 'week'.Thanks Karel
************
select date_part('dow',current_date);
If you are looking for a function to calculate the no. week of the year...
try the attached function.
Jos���
--------------D76527B467D83F77A8A400CA
Content-Type: text/plain; charset=us-ascii;
name="week.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="week.sql"
-- ISO-8601 assigns a number to each week of the year.
-- A week that lies partly in one year and partly in another is
-- assigned a number in the year in which most of its days lie.
-- This means that:
-- Week 1 of any year is the week that contains 4 January.
-- or equivalently
-- Week 1 of any year is the week that contains the first Thrusday
-- in January .
-- If the week is 0 that means that first week is less than 4 days.
-- returns the week number of the year (1 - 53)...
drop function week(date);
create function week(date) returns int2 as
'declare
p int2;
i int2;
week int4;
year text;
start date;
diff timespan;
mais int2;
txt text;
begin
year:= date_part(''year'',$1);
if textlen(year) = 1 then
year:= ''000'' || year;
end if;
if textlen(year) = 2 then
year:= ''00'' || year;
end if;
start:= year || ''-01-01'';
week:= date_part(''dow'',start);
if week > 3 or week = 0 then
mais:= 0;
else
mais:= 1;
end if;
week:= date_part(''dow'',start);
diff:= date_part(''epoch'',$1) - date_part(''epoch'',start);
txt:= diff;
p:= textpos(txt,'' '');
if p = 0 then
i:= 0;
else
i:= substr(txt,1,p - 1);
end if;
return (i + week) / 7 + mais;
end;
' language 'plpgsql';
select week('1997-01-01'), week('1997-12-31');
select week('1998-01-01'), week('1998-12-31');
select week('1999-01-01'), week('1999-12-31');
select week(current_date);
--------------D76527B467D83F77A8A400CA--
From bouncefilter Fri Jan 14 13:30:14 2000
Received: from sultan.intranet.org (root@ppp-100-50.infonie.fr
[195.242.100.50]) by hub.org (8.9.3/8.9.3) with ESMTP id NAA48767
for <pgsql-interfaces@postgreSQL.org>;
Fri, 14 Jan 2000 13:29:21 -0500 (EST) (envelope-from advl@advl.org)
Received: from localhost (localhost [[UNIX: localhost]])
by sultan.intranet.org (8.9.3/8.9.3) id TAA01963;
Fri, 14 Jan 2000 19:04:41 +0100
From: Compte utilisateur Sultan-advl <webmaster@advl.org>
Reply-To: webmaster@advl.org
Organization: ADVL
To: <duccini@backpack.com>
Subject: Re: [INTERFACES] psql ERROR : Character Types Tuple is too big: size
xxxxx
Date: Fri, 14 Jan 2000 19:00:08 +0100
X-Mailer: KMail [version 1.0.21]
Content-Type: text/plain
Cc: pgsql-interfaces@postgreSQL.org
References: <Pine.GSO.3.96.1000114095253.23313B-100000@ra.bpsi.net>
MIME-Version: 1.0
Message-Id: <00011419044100.01934@sultan>
Content-Transfer-Encoding: 8bit
X-MIME-Autoconverted: from quoted-printable to 8bit by hub.org id NAA49326
Hi,
Just to see if psql raise an error message ;-)
Emmanuel.
Le ven, 14 jan 2000, D. Duccini a ���crit :
and on top of that, why insert character strings into columns expecting
numeric values?defintely use VARCHAR unless you are going to be filling those columns up
-duck
Compte utilisateur Sultan-advl <webmaster@advl.org> writes:
newkiwi=> insert into localisation values ('999', 'ee', 'ff', '999', 'gg', 'hh', 'ii', 'jj', '999'); ERROR: Tuple is too big: size 12356 newkiwi=> \d localisation Table = localisation +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | id_loc | int4 not null | 4 | | voie_loc | char() | 2048 | | codebat_loc | char() | 2048 | | idplan_loc | int4 | 4 | | xplan_loc | char() | 2048 | | yplan_loc | char() | 2048 | | acces_loc | char() | 2048 | | accesen_loc | char() | 2048 | | id_ville_loc | int4 | 4 | +----------------------------------+----------------------------------+-------+Well, you've got six fixed-size 2K fields, which are going to take up
12K all by themselves, plus there's a few dozen bytes of overhead.I'd suggest using varchar or text instead of char --- do you really
need 12K of blank-padding in every record?regards, tom lane
************
----------------------------------------------------------------------------- david@backpack.com BackPack Software, Inc. www.backpack.com +1 651.645.7550 voice "Life is an Adventure. +1 651.645.9798 fax Don't forget your BackPack!" -----------------------------------------------------------------------------
From bouncefilter Fri Jan 14 15:55:15 2000
Received: from rage.hub.org (root@nat193.192.mpoweredpc.net [142.177.193.192])
by hub.org (8.9.3/8.9.3) with ESMTP id PAA85965
for <pgsql-general@hub.org>; Fri, 14 Jan 2000 15:54:39 -0500 (EST)
(envelope-from jeff@hub.org)
Received: from localhost (jeff@localhost)
by rage.hub.org (8.9.3/8.9.3) with ESMTP id QAA09706
for <pgsql-general@hub.org>; Fri, 14 Jan 2000 16:55:03 -0400 (AST)
(envelope-from jeff@hub.org)
X-Authentication-Warning: rage.hub.org: jeff owned process doing -bs
Date: Fri, 14 Jan 2000 16:55:02 -0400 (AST)
From: Jeff MacDonald <jeff@hub.org>
Reply-To: Jeff MacDonald <jeff@hub.org>
To: pgsql-general@hub.org
Subject: cgi with postgres
Message-ID: <Pine.BSF.4.10.10001141651160.428-100000@rage.hub.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
hey folks,
this is a security issue i'd like to get some info
on, i'm sure it's more with cgi than postgres, but
heck.
issue: how to secure cgi's that access postgres
problem: passwords for postgres database are stored
in plain text in scripts. (lets assume, perl,
not a compiled language)
points:
make cgi dir 711
big deal, they can get the name of the file
from the web, and copy it.
set an obscure cgi script alias in apache
big deal, they can read the cgi conf file.
this is assuming they already have an account
on the machine, something that cannot be ruled
out.
question in short: how to make perl accessing databases
more secure, so any jack can't modify a database.
thanks in advance.
Jeff MacDonald
jeff@hub.org
===================================================================
So long as the Universe had a beginning, we can suppose it had a
creator, but if the Universe is completly self contained , having
no boundry or edge, it would neither be created nor destroyed
It would simply be.
===================================================================
From bouncefilter Fri Jan 14 16:20:21 2000
Received: from berghold.net (IDENT:peter@cc1008600-a.etntwn1.nj.home.com
[24.3.204.54]) by hub.org (8.9.3/8.9.3) with ESMTP id QAA94203;
Fri, 14 Jan 2000 16:19:56 -0500 (EST)
(envelope-from peter@berghold.net)
Received: (from peter@localhost) by berghold.net (8.9.3/8.9.3) id QAA09741;
Fri, 14 Jan 2000 16:19:54 -0500
Message-ID: <20000114161954.A9728@uboat.berghold.net>
Date: Fri, 14 Jan 2000 16:19:54 -0500
From: "Peter L. Berghold" <peter@berghold.net>
To: Jeff MacDonald <jeff@hub.org>, pgsql-general@hub.org
Subject: Re: [GENERAL] cgi with postgres
References: <Pine.BSF.4.10.10001141651160.428-100000@rage.hub.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Mailer: Mutt 0.91.1
In-Reply-To: <Pine.BSF.4.10.10001141651160.428-100000@rage.hub.org>;
from Jeff MacDonald on Fri, Jan 14, 2000 at 04:55:02PM -0400
On Fri, Jan 14, 2000 at 04:55:02PM -0400, Jeff MacDonald wrote:
this is a security issue i'd like to get some info
on, i'm sure it's more with cgi than postgres, but
heck.
First off, if the server is set up correctly a casual user should not be
able to browse the cgi-bin directory and see your code.
I'm not sure what server you are creating your scripts on, but if it is
Apache and mod_perl is available to you then this is even better. You
can create a handler in mod_perl for a "pseudo-directory" and hide your
code that way.
However, as I said in my first paragraph this should not be necessary as
normally web browsers can't browse the cgi-bin directory anyway and your
cgi-script should just send back to the browser html code and not the
perl code itself. Unless something is very very wrong....
--
-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
Peter L. Berghold Peter@Berghold.Net
"Linux renders ships http://www.berghold.net
NT renders ships useless...."
From bouncefilter Fri Jan 14 16:46:18 2000
Received: from corvette.mascari.com (dhcp26142035.columbus.rr.com
[24.26.142.35]) by hub.org (8.9.3/8.9.3) with ESMTP id QAA02292;
Fri, 14 Jan 2000 16:45:16 -0500 (EST)
(envelope-from mascarm@mascari.com)
Received: from mascari.com (ferrari.mascari.com [192.168.2.1])
by corvette.mascari.com (8.9.3/8.9.3) with ESMTP id QAA04944;
Fri, 14 Jan 2000 16:43:58 -0500
Message-ID: <387F9862.699DB03B@mascari.com>
Date: Fri, 14 Jan 2000 16:42:58 -0500
From: Mike Mascari <mascarm@mascari.com>
Organization: Mascari Development Inc
X-Mailer: Mozilla 4.7 [en] (Win95; I)
X-Accept-Language: en
MIME-Version: 1.0
To: Jeff MacDonald <jeff@hub.org>
CC: pgsql-general@hub.org
Subject: Re: [GENERAL] cgi with postgres
References: <Pine.BSF.4.10.10001141651160.428-100000@rage.hub.org>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Jeff MacDonald wrote:
hey folks,
this is a security issue i'd like to get some info
on, i'm sure it's more with cgi than postgres, but
heck.issue: how to secure cgi's that access postgres
problem: passwords for postgres database are stored
in plain text in scripts. (lets assume, perl,
not a compiled language)points:
make cgi dir 711
big deal, they can get the name of the file
from the web, and copy it.set an obscure cgi script alias in apache
big deal, they can read the cgi conf file.this is assuming they already have an account
on the machine, something that cannot be ruled
out.question in short: how to make perl accessing databases
more secure, so any jack can't modify a database.thanks in advance.
Jeff MacDonald
jeff@hub.org
I'm not sure if this is definitive - hackers are very clever, but
this SHOULD do it:
1. httpd runs as user 'nobody'
2. 'nobody' doesn't have a shell account (shell = /bin/false)
3. no one can 'su' to 'nobody', except root (obviously)
4. pg_hba.conf allows only local connections (127.0.0.1)
5. the postmaster isn't running with -i
6. 'nobody' owns the database and has granted privileges to only
those other users as appropriate
7. The password (as added protection) is in some file readable by
user 'nobody' only.
Hope that helps,
Mike Mascari
From bouncefilter Fri Jan 14 16:45:16 2000
Received: from rage.hub.org (root@nat193.192.mpoweredpc.net [142.177.193.192])
by hub.org (8.9.3/8.9.3) with ESMTP id QAA02148
for <pgsql-general@hub.org>; Fri, 14 Jan 2000 16:44:53 -0500 (EST)
(envelope-from jeff@hub.org)
Received: from localhost (jeff@localhost)
by rage.hub.org (8.9.3/8.9.3) with ESMTP id RAA09818;
Fri, 14 Jan 2000 17:45:10 -0400 (AST) (envelope-from jeff@hub.org)
X-Authentication-Warning: rage.hub.org: jeff owned process doing -bs
Date: Fri, 14 Jan 2000 17:45:10 -0400 (AST)
From: Jeff MacDonald <jeff@hub.org>
Reply-To: Jeff MacDonald <jeff@hub.org>
To: Alfred Perlstein <bright@wintelcom.net>
cc: pgsql-general@hub.org
Subject: Re: [GENERAL] cgi with postgres
In-Reply-To: <20000114135329.D508@fw.wintelcom.net>
Message-ID: <Pine.BSF.4.10.10001141743510.428-100000@rage.hub.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
alfred, that seems like a very reasonable solution,
in regard to the other chaps responce, i'm not worried
about web users anyway, cause they can't see the perl
source. it's users on the system i'd like to protect
against.
On Fri, 14 Jan 2000, Alfred Perlstein wrote:
* Jeff MacDonald <jeff@hub.org> [000114 13:38] wrote:
hey folks,
this is a security issue i'd like to get some info
on, i'm sure it's more with cgi than postgres, but
heck.issue: how to secure cgi's that access postgres
problem: passwords for postgres database are stored
in plain text in scripts. (lets assume, perl,
not a compiled language)points:
make cgi dir 711
big deal, they can get the name of the file
from the web, and copy it.how about sourcing a conf file that's in a 700 dir?
set an obscure cgi script alias in apache
big deal, they can read the cgi conf file.this is assuming they already have an account
on the machine, something that cannot be ruled
out.question in short: how to make perl accessing databases
more secure, so any jack can't modify a database.thanks in advance.
Jeff MacDonald
jeff@hub.org--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
Jeff MacDonald
jeff@hub.org
===================================================================
So long as the Universe had a beginning, we can suppose it had a
creator, but if the Universe is completly self contained , having
no boundry or edge, it would neither be created nor destroyed
It would simply be.
===================================================================
From bouncefilter Fri Jan 14 16:31:16 2000
Received: from fw.wintelcom.net (bright@ns1.wintelcom.net [209.1.153.20])
by hub.org (8.9.3/8.9.3) with ESMTP id QAA97732;
Fri, 14 Jan 2000 16:31:00 -0500 (EST)
(envelope-from bright@fw.wintelcom.net)
Received: (from bright@localhost)
by fw.wintelcom.net (8.9.3/8.9.3) id NAA02061;
Fri, 14 Jan 2000 13:53:30 -0800 (PST)
Date: Fri, 14 Jan 2000 13:53:30 -0800
From: Alfred Perlstein <bright@wintelcom.net>
To: Jeff MacDonald <jeff@hub.org>
Cc: pgsql-general@hub.org
Subject: Re: [GENERAL] cgi with postgres
Message-ID: <20000114135329.D508@fw.wintelcom.net>
References: <Pine.BSF.4.10.10001141651160.428-100000@rage.hub.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Mailer: Mutt 1.0i
In-Reply-To: <Pine.BSF.4.10.10001141651160.428-100000@rage.hub.org>;
from jeff@hub.org on Fri, Jan 14, 2000 at 04:55:02PM -0400
* Jeff MacDonald <jeff@hub.org> [000114 13:38] wrote:
hey folks,
this is a security issue i'd like to get some info
on, i'm sure it's more with cgi than postgres, but
heck.issue: how to secure cgi's that access postgres
problem: passwords for postgres database are stored
in plain text in scripts. (lets assume, perl,
not a compiled language)points:
make cgi dir 711
big deal, they can get the name of the file
from the web, and copy it.
how about sourcing a conf file that's in a 700 dir?
set an obscure cgi script alias in apache
big deal, they can read the cgi conf file.this is assuming they already have an account
on the machine, something that cannot be ruled
out.question in short: how to make perl accessing databases
more secure, so any jack can't modify a database.thanks in advance.
Jeff MacDonald
jeff@hub.org
--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
From bouncefilter Fri Jan 14 17:23:17 2000
Received: from news.tht.net (news.hub.org [216.126.91.242])
by hub.org (8.9.3/8.9.3) with ESMTP id RAA12954
for <pgsql-general@postgresql.org>;
Fri, 14 Jan 2000 17:23:05 -0500 (EST)
(envelope-from news@news.tht.net)
Received: (from news@localhost) by news.tht.net (8.9.3/8.9.3) id RAA51148
for pgsql-general@postgresql.org; Fri, 14 Jan 2000 17:20:25 -0500 (EST)
(envelope-from news)
X-Authentication-Warning: news.tht.net: news set sender to <news> using -f
Message-ID: <387FA13A.1C926363@earthlink.net>
From: rut <rutl@earthlink.net>
X-Mailer: Mozilla 4.7 [en] (Win98; I)
X-Accept-Language: en
MIME-Version: 1.0
X-Newsgroups: comp.databases.postgresql.questions
Subject: sql question
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 29
X-Complaints-To: abuse@earthlink.net
X-Trace: newsread1.prod.itd.earthlink.net 947888210 206.133.64.168 (Fri,
14 Jan 2000 14:16:50 PST)
Organization: EarthLink Network, Inc.
X-ELN-Date: Fri Jan 14 14:16:50 2000
Date: Fri, 14 Jan 2000 22:16:50 GMT
To: pgsql-questions@postgresql.org
Using vb6 and ado 2.1 if I open a connection string like so:
Public Sub setup()
Dim cnn1 As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
Dim varDate As Variant
' Open connection.
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Database=db; "
Set cnn1 = New ADODB.Connection
cnn1.Open strCnn
End Sub
Will this code still try to send the username (machine name) to the sql
server for authentication even though it is not explicitly sent here?
Also, If the Provider is not specified, does the system revert to an
odbc driver vs the ole driver?
Please email if possible:
rutledgj@earthlink.net
From bouncefilter Fri Jan 14 17:41:16 2000
Received: from pawprint.colloquium.co.uk (www.pawprint.co.uk [192.153.153.58])
by hub.org (8.9.3/8.9.3) with ESMTP id RAA20556
for <pgsql-general@postgresql.org>;
Fri, 14 Jan 2000 17:40:23 -0500 (EST)
(envelope-from neil@pawprint.co.uk)
Received: (from neil@localhost)
by pawprint.colloquium.co.uk (8.9.3/8.9.3) id WAA02175
for pgsql-general@postgresql.org; Fri, 14 Jan 2000 22:41:25 GMT
Date: Fri, 14 Jan 2000 22:41:25 +0000
From: Neil Burrows <neil@remo.demon.co.uk>
To: pgsql-general@postgresql.org
Subject: More Rule creation problems (and nowhere near 8K)
Message-ID: <20000114224125.A2165@pawprint.colloquium.co.uk>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Mailer: Mutt 1.0i
Hi,
I am having a problem trying to create a rule on a view and keep getting
ERROR: DefineQueryRewrite: rule plan string too big.
I've looked though the mailing list and docs and have seen mentions that
the limit of a rule is 8192 characters. The rule (see below) is no more
than 600 characters as it is and the error still appears even when all the
whitespace is removed.
Is this correct and if so what is the actual limit for the length of a
rule?
I am currently using PostgreSQL 6.5.3.
Thanks.
--
-- Rule to INSERT & add correct date
--
CREATE RULE customer_insert AS ON INSERT TO customers
DO INSTEAD INSERT INTO customer_table
VALUES (NEXTVAL('customer_seq'), new.company, new.forename,
new.surname,LOWER(new.username), new.password, new.address1,
new.address2, new.address3, new.town, new.postcode, new.day_phone,
new.evening_phone, new.fax, new.mobile, new.package,
current_datetime(), current_datetime(), new.suspended,
new.suspended_date, new.deleted, new.deleted_date, new.comments);
Regards,
---[ Neil Burrows ]-----------------------------------------------------
E-mail: neil@pawprint.co.uk | You're only young once, but
Web : http://www.remo.demon.co.uk/ | you can stay immature forever
-------< PGP Key available from http://www.remo.demon.co.uk/pgp/ >--------
From bouncefilter Fri Jan 14 17:51:18 2000
Received: from Mail.austin.rr.com (sm2.texas.rr.com [24.93.35.55])
by hub.org (8.9.3/8.9.3) with ESMTP id RAA21903
for <pgsql-general@postgresql.org>;
Fri, 14 Jan 2000 17:50:38 -0500 (EST)
(envelope-from eloehr@austin.rr.com)
Received: from austin.rr.com ([24.27.30.243]) by Mail.austin.rr.com with
Microsoft SMTPSVC(5.5.1877.197.19); Fri, 14 Jan 2000 16:41:27 -0600
Sender: ed
Message-ID: <387FA88F.41F1F860@austin.rr.com>
Date: Fri, 14 Jan 2000 16:51:59 -0600
From: Ed Loehr <eloehr@austin.rr.com>
X-Mailer: Mozilla 4.7 [en] (X11; U; Linux 2.2.12-20smp i686)
X-Accept-Language: en
MIME-Version: 1.0
To: pg-gen <pgsql-general@postgresql.org>
Subject: [GENERAL] GRANT ALL ON * TO username?
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
I'd like to grant read-access to every single DB object in a database
for one user. I'd hoped for
GRANT SELECT ON * TO <username>
but the '*' syntax is wrong and I don't see an alternative in the
docs...
How is this done?
Cheers,
Ed Loehr
From bouncefilter Fri Jan 14 17:30:20 2000
Received: from fw.wintelcom.net (bright@ns1.wintelcom.net [209.1.153.20])
by hub.org (8.9.3/8.9.3) with ESMTP id RAA14766;
Fri, 14 Jan 2000 17:29:59 -0500 (EST)
(envelope-from bright@fw.wintelcom.net)
Received: (from bright@localhost)
by fw.wintelcom.net (8.9.3/8.9.3) id OAA04406;
Fri, 14 Jan 2000 14:52:29 -0800 (PST)
Date: Fri, 14 Jan 2000 14:52:28 -0800
From: Alfred Perlstein <bright@wintelcom.net>
To: Jeff MacDonald <jeff@hub.org>
Cc: pgsql-general@hub.org
Subject: Re: [GENERAL] cgi with postgres
Message-ID: <20000114145228.F508@fw.wintelcom.net>
References: <20000114135329.D508@fw.wintelcom.net>
<Pine.BSF.4.10.10001141743510.428-100000@rage.hub.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Mailer: Mutt 1.0i
In-Reply-To: <Pine.BSF.4.10.10001141743510.428-100000@rage.hub.org>;
from jeff@hub.org on Fri, Jan 14, 2000 at 05:45:10PM -0400
* Jeff MacDonald <jeff@hub.org> [000114 14:07] wrote:
alfred, that seems like a very reasonable solution,
in regard to the other chaps responce, i'm not worried
about web users anyway, cause they can't see the perl
source. it's users on the system i'd like to protect
against.
I'm not sure what you mean, but there is a problem, unless you
execute the scripts as a user other than the default cgi user then
you may run into problems because then people can craft a cgi and
run it through the server to gain access to the 700 dir, you'll
either need some sort of setuid (to a special user, not root) or
use some sort of cgiwrapper.
-Alfred
On Fri, 14 Jan 2000, Alfred Perlstein wrote:
* Jeff MacDonald <jeff@hub.org> [000114 13:38] wrote:
hey folks,
this is a security issue i'd like to get some info
on, i'm sure it's more with cgi than postgres, but
heck.issue: how to secure cgi's that access postgres
problem: passwords for postgres database are stored
in plain text in scripts. (lets assume, perl,
not a compiled language)points:
make cgi dir 711
big deal, they can get the name of the file
from the web, and copy it.how about sourcing a conf file that's in a 700 dir?
set an obscure cgi script alias in apache
big deal, they can read the cgi conf file.this is assuming they already have an account
on the machine, something that cannot be ruled
out.question in short: how to make perl accessing databases
more secure, so any jack can't modify a database.thanks in advance.
Jeff MacDonald
jeff@hub.org--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]Jeff MacDonald
jeff@hub.org===================================================================
So long as the Universe had a beginning, we can suppose it had a
creator, but if the Universe is completly self contained , having
no boundry or edge, it would neither be created nor destroyed
It would simply be.
===================================================================
--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
From bouncefilter Fri Jan 14 17:05:20 2000
Received: from pony.ip-solutions.net (c829653-a.sttln1.wa.home.com
[24.10.39.79]) by hub.org (8.9.3/8.9.3) with ESMTP id RAA09360
for <pgsql-general@hub.org>; Fri, 14 Jan 2000 17:05:08 -0500 (EST)
(envelope-from moebius@ip-solutions.net)
From: moebius@ip-solutions.net
Received: from spider.ip-solutions.net (spider.ip-solutions.net
[192.168.100.4])
by pony.ip-solutions.net (8.9.3/8.8.7) with ESMTP id OAA25655
for <pgsql-general@hub.org>; Fri, 14 Jan 2000 14:10:18 -0800
Received: (from nobody@localhost)
by spider.ip-solutions.net (8.9.3/8.8.7) id PAA01362;
Fri, 14 Jan 2000 15:04:56 -0800
Date: Fri, 14 Jan 2000 15:04:56 -0800
Message-Id: <200001142304.PAA01362@spider.ip-solutions.net>
X-Authentication-Warning: spider.ip-solutions.net: nobody set sender to
moebius@ip-solutions.net using -f
To: PostgresGeneral <pgsql-general@hub.org>
Reply-To: moebius@ip-solutions.net
MIME-Version: 1.0
Content-Type: text/plain
Content-Transfer-Encoding: 8bit
User-Agent: IMP/PHP3 Imap webMail Program 2.0.10
Sender: moebius@ip-solutions.net
Subject: New To List
Hello All,
I'm not really sure how joining the list works but I
wanted to say hello. I am new to databases and also to
Postgres. Started reading the book up on the site. Very
helpful.
I do have a question though. I am trying to take a DB
written for MySql and convert the format of statements
over to Postgres for creation. I can't seem to find a
value 'blob' in PG. Is there something similar?? Also if
anyone can help me with this (see below). I can't figure
out how to make a primary, unique key. Here's the sql
file:
CREATE TABLE session (
sid varchar(255) NOT NULL,
val blob NOT NULL,
changed varchar(14) NOT NULL,
PRIMARY KEY (sid),
UNIQUE sid_2 (sid)
);
Again any help or pointers in the right direction is
greatly appreciated.
Kind regards,
Harry Hoffman
Product Systems Specialist
Restaurants Unlimited Inc.
(206) 634-3082 x. 270
From bouncefilter Fri Jan 14 18:09:17 2000
Received: from mercury.shreve.net (IDENT:root@mercury.shreve.net
[208.206.76.23]) by hub.org (8.9.3/8.9.3) with ESMTP id SAA27727
for <pgsql-general@postgreSQL.org>;
Fri, 14 Jan 2000 18:08:27 -0500 (EST)
(envelope-from kheflin@shreve.net)
Received: from mercury.shreve.net (IDENT:kheflin@mercury.shreve.net
[208.206.76.23])
by mercury.shreve.net (8.9.3/8.9.3) with ESMTP id RAA29151
for <pgsql-general@postgreSQL.org>; Fri, 14 Jan 2000 17:08:27 -0600
Date: Fri, 14 Jan 2000 17:08:27 -0600 (CST)
From: Kevin Heflin <kheflin@shreve.net>
To: "PGSQL-General (E-mail)" <pgsql-general@postgreSQL.org>
Subject: problem with date range
Message-ID: <Pine.LNX.4.10.10001141702540.26735-100000@mercury.shreve.net>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
have a query like so:
select crimeid, areaid,
sum( CASE when dateof='1-8-2000' then total else 0 end) as crimes1
from stats GROUP BY crimeid, areaid;
This works and gives me results like I want like so:
crimeid|areaid|crimes1
-------+------+-------
4| 2| 0
5| 2| 0
5| 3| 20
6| 2| 0
7| 2| 0
8| 2| 0
9| 2| 0
10| 2| 0
11| 2| 0
12| 2| 0
But what I really need would be the same query as above but give me
results which show total crimes for the Current year to date, and also
total crimes for 'last week'
Im wanting 'last week' to be the last complete week. Say today is
01/14/2000 the last complete week would be 01/02/2000 --> 01/08/2000
Any suggestions would be appreciated.
Kevin
--------------------------------------------------------------------
Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103
VP/Production | 333 Texas St #175 | FAX:318.221.6612
kheflin@shreve.net | Shreveport, LA 71101 | http://www.shreve.net
--------------------------------------------------------------------
From bouncefilter Fri Jan 14 19:15:21 2000
Received: from thelab.hub.org (nat200.60.mpoweredpc.net [142.177.200.60])
by hub.org (8.9.3/8.9.3) with ESMTP id TAA40883;
Fri, 14 Jan 2000 19:14:53 -0500 (EST) (envelope-from scrappy@hub.org)
Received: from localhost (scrappy@localhost)
by thelab.hub.org (8.9.3/8.9.1) with ESMTP id UAA91128;
Fri, 14 Jan 2000 20:15:07 -0400 (AST) (envelope-from scrappy@hub.org)
X-Authentication-Warning: thelab.hub.org: scrappy owned process doing -bs
Date: Fri, 14 Jan 2000 20:15:07 -0400 (AST)
From: The Hermit Hacker <scrappy@hub.org>
To: Jeff MacDonald <jeff@hub.org>
cc: pgsql-general@hub.org
Subject: Re: [GENERAL] cgi with postgres
In-Reply-To: <Pine.BSF.4.10.10001141651160.428-100000@rage.hub.org>
Message-ID: <Pine.BSF.4.21.0001142014370.46499-100000@thelab.hub.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
On Fri, 14 Jan 2000, Jeff MacDonald wrote:
hey folks,
this is a security issue i'd like to get some info
on, i'm sure it's more with cgi than postgres, but
heck.issue: how to secure cgi's that access postgres
problem: passwords for postgres database are stored
in plain text in scripts. (lets assume, perl,
not a compiled language)points:
make cgi dir 711
big deal, they can get the name of the file
from the web, and copy it.set an obscure cgi script alias in apache
big deal, they can read the cgi conf file.
Side point ... why isn't the apache conf file secure? Only user root
needs to be able to read it, no?
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
From bouncefilter Fri Jan 14 19:37:18 2000
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 TAA46180
for <pgsql-general@postgresql.org>;
Fri, 14 Jan 2000 19:36:37 -0500 (EST)
(envelope-from maillist@remo.demon.co.uk)
Received: from remo.demon.co.uk ([158.152.14.149] helo=TAZ)
by finch-post-12.mail.demon.net with smtp (Exim 2.12 #1)
id 129HCl-0009dS-0C
for pgsql-general@postgresql.org; Sat, 15 Jan 2000 00:36:35 +0000
From: "Neil Burrows" <maillist@remo.demon.co.uk>
To: <pgsql-general@postgresql.org>
Subject: More Rule creation problems (and nowhere near 8K)
Date: Sat, 15 Jan 2000 00:35:43 -0000
Message-ID: <HKEJLLJFGPEAOLIDJHHDEEGOCFAA.maillist@remo.demon.co.uk>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0)
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.5600
Importance: Normal
Hi,
I am having a problem trying to create a rule on a view and keep getting
ERROR: DefineQueryRewrite: rule plan string too big.
I've looked though the mailing list and docs and have seen mentions that
the limit of a rule is 8192 characters. The rule (see below) is no more
than 600 characters as it is and the error still appears even when all the
whitespace is removed.
Is this correct and if so what is the actual limit for the length of a
rule?
I am currently using PostgreSQL 6.5.3.
Thanks.
--
-- Rule to INSERT & add correct date
--
CREATE RULE customer_insert AS ON INSERT TO customers
DO INSTEAD INSERT INTO customer_table
VALUES (NEXTVAL('customer_seq'), new.company, new.forename,
new.surname,LOWER(new.username), new.password, new.address1,
new.address2, new.address3, new.town, new.postcode, new.day_phone,
new.evening_phone, new.fax, new.mobile, new.package,
current_datetime(), current_datetime(), new.suspended,
new.suspended_date, new.deleted, new.deleted_date, new.comments);
Regards,
---[ Neil Burrows ]-----------------------------------------------------
E-mail: neil@pawprint.co.uk | You're only young once, but
Web : http://www.remo.demon.co.uk/ | you can stay immature forever
-------< PGP Key available from http://www.remo.demon.co.uk/pgp/ >--------
From bouncefilter Sat Jan 15 00:58:21 2000
Received: from pcr.ca (www.pcr.ca [207.139.158.13] (may be forged))
by hub.org (8.9.3/8.9.3) with ESMTP id AAA70974
for <pgsql-general@postgresql.org>;
Sat, 15 Jan 2000 00:58:01 -0500 (EST)
(envelope-from admin@wtbwts.com)
Received: by pcr.ca (Postfix, from userid 1000)
id 57DA91FE8; Sat, 15 Jan 2000 00:56:53 +0000 (GMT)
Received: from localhost (localhost [127.0.0.1])
by pcr.ca (Postfix) with ESMTP id 4F7BB1F8C
for <pgsql-general@postgresql.org>;
Sat, 15 Jan 2000 00:56:53 +0000 (GMT)
Date: Sat, 15 Jan 2000 00:56:53 +0000 (GMT)
From: admin <admin@wtbwts.com>
X-Sender: admin@server.b0x.com
To: pgsql-general@postgresql.org
Subject: keeping track of date
Message-ID: <Pine.BSF.4.10.10001150041400.86352-100000@server.b0x.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
What would be the recommended way to keep track of the date/time a tuple
was inserted?
Should I be using triggers on insert, or is there an internal variable I
can use to avoid creating another field?
Thanks,
Marc
From bouncefilter Sat Jan 15 01:48:24 2000
Received: from pcr.ca (www.pcr.ca [207.139.158.13] (may be forged))
by hub.org (8.9.3/8.9.3) with ESMTP id BAA89462
for <pgsql-general@postgresql.org>;
Sat, 15 Jan 2000 01:47:36 -0500 (EST)
(envelope-from admin@wtbwts.com)
Received: by pcr.ca (Postfix, from userid 1000)
id ACD331FE8; Sat, 15 Jan 2000 01:46:29 +0000 (GMT)
Received: from localhost (localhost [127.0.0.1])
by pcr.ca (Postfix) with ESMTP id 879561F8C
for <pgsql-general@postgresql.org>;
Sat, 15 Jan 2000 01:46:29 +0000 (GMT)
Date: Sat, 15 Jan 2000 01:46:29 +0000 (GMT)
From: admin <admin@wtbwts.com>
X-Sender: admin@server.b0x.com
To: pgsql-general@postgresql.org
Subject: rules vs triggers
Message-ID: <Pine.BSF.4.10.10001150132330.86487-100000@server.b0x.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
After reading chapters 8 and 13 of the Programmer's Guide, I thought I
understood the pros and cons of using rules vs triggers. After writing my
own, I'm still not sure I understand.
For example, lets take the check_primary_key() function in the referential
integrity trigger [contrib/spi/refint.c]. The same code could've been
written using rules since there is no need for checking validity. I
thought the rule of thumb was: "use rules as much as possible, unless you
need to check for valid values." -- my understanding
I seem to be missing a few valuable points about rules and triggers, I'd
appreciate if someone could nudge me in the right direction.
Marc
From bouncefilter Fri Jan 14 21:44:25 2000
Received: from webintention.com (IDENT:qmailr@[209.43.173.117])
by hub.org (8.9.3/8.9.3) with SMTP id VAA91072
for <pgsql-general@postgresql.org>;
Fri, 14 Jan 2000 21:43:35 -0500 (EST)
(envelope-from blake@webintention.com)
Received: (qmail 4368 invoked from network); 15 Jan 2000 03:15:00 -0000
Received: from unknown (HELO jody) (209.43.173.118)
by 209.43.173.117 with SMTP; 15 Jan 2000 03:15:00 -0000
Message-ID: <000101bf5f02$3d7f7600$7f64a8c0@jody.webintention.com>
From: "Blake Starkenburg" <blake@webintention.com>
To: <pgsql-general@postgresql.org>
Subject: Postgresql back-end - INTEGRATED - Excell Spreedsheets
Date: Fri, 14 Jan 2000 18:42:31 -0800
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
Hello All,
I am looking for opinions and suggestions on setting up integration between
a back-end PostgreSQL database engine hosted on a web server and a client
using Excel spreadsheets to store and print internal product information and
pricing.
Here's the scenario: I am using a Linux web server, Apache, PostgreSQL,
PHP3.
The client's secretaries input all product information currently into Excel
spreadsheets, at there office location on there workstations, dial-up
Internet connection, they will not at the current time switch to complete
web integration. Each month they update product pricing and new products
additions into the excel spreadsheet. On the client's web site we have a
back-end database with the same product and pricing information. The
problem, I need to develop a way for the client to upload an exported excel
spreadsheet in tab-deliminated format to the server and rebuild or update
the PostgreSQl database on the web to reflect the changes.
My first thought on the situation would be to write the proper scripts to
allow the client to upload the file to the web server, then click on a
button via a web browser that would run a compiled PHP command to grad the
uploaded file, put it into a variable and run the command psql pg_dump and
rebuild the database with the updated information. This seems inefficient
however, along with the problem of the server running as 'nobody' and I am
wondering if a more efficient ways exists and if you all would have any
suggestions?
Thank you everyone,
Blake S.
From bouncefilter Fri Jan 14 22:44:20 2000
Received: from Mail.austin.rr.com (sm2.texas.rr.com [24.93.35.55])
by hub.org (8.9.3/8.9.3) with ESMTP id WAA19349
for <pgsql-general@postgresql.org>;
Fri, 14 Jan 2000 22:44:13 -0500 (EST)
(envelope-from eloehr@austin.rr.com)
Received: from austin.rr.com ([24.27.30.243]) by Mail.austin.rr.com with
Microsoft SMTPSVC(5.5.1877.197.19); Fri, 14 Jan 2000 21:35:02 -0600
Sender: ed
Message-ID: <387FED5D.50ACA580@austin.rr.com>
Date: Fri, 14 Jan 2000 21:45:33 -0600
From: Ed Loehr <eloehr@austin.rr.com>
X-Mailer: Mozilla 4.7 [en] (X11; U; Linux 2.2.12-20smp i686)
X-Accept-Language: en
MIME-Version: 1.0
To: Neil Burrows <maillist@remo.demon.co.uk>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] More Rule creation problems (and nowhere near 8K)
References: <HKEJLLJFGPEAOLIDJHHDEEGOCFAA.maillist@remo.demon.co.uk>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Neil Burrows wrote:
Hi,
I am having a problem trying to create a rule on a view and keep getting
ERROR: DefineQueryRewrite: rule plan string too big.
I've looked though the mailing list and docs and have seen mentions that
the limit of a rule is 8192 characters. The rule (see below) is no more
than 600 characters as it is and the error still appears even when all the
whitespace is removed.
If I read the previous comments on this topic correctly, it's not the length
of your query that matters, but rather the length of what your query gets
translated into... a very annoying bug, indeed. This is the number one
barrier to my ODBC use as it makes views pretty much unusable.
Cheers,
Ed Loehr
From bouncefilter Sat Jan 15 01:23:21 2000
Received: from news.tht.net (news.hub.org [216.126.91.242])
by hub.org (8.9.3/8.9.3) with ESMTP id BAA81200
for <pgsql-general@postgresql.org>;
Sat, 15 Jan 2000 01:23:06 -0500 (EST)
(envelope-from news@news.tht.net)
Received: (from news@localhost) by news.tht.net (8.9.3/8.9.3) id BAA83238
for pgsql-general@postgresql.org; Sat, 15 Jan 2000 01:00:40 -0500 (EST)
(envelope-from news)
X-Authentication-Warning: news.tht.net: news set sender to <news> using -f
Message-ID: <38800CCE.437B27FC@bellsouth.net>
From: Gerald Sievers <gsievers@bellsouth.net>
X-Mailer: Mozilla 4.04 [en]C-bls40 (Win95; U)
MIME-Version: 1.0
X-Newsgroups: comp.databases.postgresql.questions
Subject: Re: Postgresql Perl Problem
References: <pQPd4.14376$G55.169911@news1.rdc1.ab.home.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 23
Date: Sat, 15 Jan 2000 00:59:42 -0500
X-Trace: news3.mia 947916028 209.214.1.208 (Sat, 15 Jan 2000 01:00:28 EST)
To: pgsql-questions@postgresql.org
use Pg; #upper case P. or did you forget to compile postgres w/perl support?
HydroMan wrote:
I am trying my first query a postgresql database using perl (using a Redhat
6.0 distribution). The script fails on the line:$conn=Pg::connectdb("dbname=mydatabase");
with the error 'Can't locate pg.pm in @INC.
I thought that I may have been missing the perl5 interface for postgres, and
tried to find one. The linux documentation suggests the site
ftp://ftp.kciLink.com/pub/PostgresPerl-1.3.tar.gz, but its not there. Can
anyone tell me if this missing intergace is the problem, and if it is, where
I can get PostgresPerl-1.3.tar.gz?Thanx,
Jason
From bouncefilter Sat Jan 15 05:10:24 2000
Received: from mail.enterprise.net (mail.enterprise.net [194.72.192.18])
by hub.org (8.9.3/8.9.3) with ESMTP id FAA56449
for <pgsql-general@hub.org>; Sat, 15 Jan 2000 05:09:54 -0500 (EST)
(envelope-from olly@lfix.co.uk)
Received: from linda.lfix.co.uk (root@max04-060.enterprise.net
[194.72.196.180])
by mail.enterprise.net (8.8.5/8.8.5) with ESMTP id KAA16784;
Sat, 15 Jan 2000 10:09:51 GMT
Received: from lfix.co.uk (olly@localhost [127.0.0.1])
by linda.lfix.co.uk (8.9.3/8.9.3/Debian 8.9.3-6) with ESMTP id KAA21853;
Sat, 15 Jan 2000 10:09:42 GMT
Message-Id: <200001151009.KAA21853@linda.lfix.co.uk>
X-Authentication-Warning: linda.lfix.co.uk: Host olly@localhost [127.0.0.1]
claimed to be lfix.co.uk
X-Mailer: exmh version 2.1.1 10/15/1999 (debian)
To: moebius@ip-solutions.net
cc: PostgresGeneral <pgsql-general@hub.org>, olly@linda.lfix.co.uk
Subject: Re: [GENERAL] New To List
In-Reply-To: Message from moebius@ip-solutions.net of "Fri,
14 Jan 2000 15:04:56 PST."
<200001142304.PAA01362@spider.ip-solutions.net>
References: <200001142304.PAA01362@spider.ip-solutions.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Date: Sat, 15 Jan 2000 10:09:40 +0000
From: "Oliver Elphick" <olly@lfix.co.uk>
moebius@ip-solutions.net wrote:
I do have a question though. I am trying to take a DB
written for MySql and convert the format of statements
over to Postgres for creation. I can't seem to find a
value 'blob' in PG. Is there something similar?? Also if
large objects may be what you want?
anyone can help me with this (see below). I can't figure
out how to make a primary, unique key. Here's the sql
file:
CREATE TABLE session (
sid varchar(255) NOT NULL,
val blob NOT NULL,
changed varchar(14) NOT NULL,
PRIMARY KEY (sid),
UNIQUE sid_2 (sid)
);
CREATE TABLE session (
sid varchar(255) PRIMARY KEY,
you don't need the UNIQUE constraint, since it is implicit in
PRIMARY KEY. A unique constraint on a single field can be
expressed by
field type UNIQUE
If a combination of fields is the primary key, you can use
CREATE TABLE foo (
f1 TEXT NOT NULL,
f2 TEXT NOT NULL,
...,
PRIMARY KEY (f1,f2)
);
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
"For I know that my redeemer liveth, and that he shall
stand at the latter day upon the earth"
Job 19:25
From bouncefilter Wed Jan 12 06:02:35 2000
Received: from vpcit.ru (qmailr@dialup.vpcit.ru [195.38.52.246])
by hub.org (8.9.3/8.9.3) with SMTP id GAA85603
for <pgsql-general@postgreSQL.org>;
Wed, 12 Jan 2000 06:02:15 -0500 (EST) (envelope-from yura@vpcit.ru)
Received: (qmail 13425 invoked from network); 12 Jan 2000 11:02:07 -0000
Received: from gercon.vpcit.ru (195.38.52.226)
by dialup.vpcit.ru with SMTP; 12 Jan 2000 11:02:07 -0000
Date: Sat, 15 Jan 2000 15:49:35 +0500
From: yura <yura@vpcit.ru>
X-Mailer: The Bat! (v1.36) S/N F29DEE5D / Educational
Reply-To: yura <yura@vpcit.ru>
Organization: Gercon
X-Priority: 3 (Normal)
Message-ID: <15659.000115@vpcit.ru>
To: pgsql-general <pgsql-general@postgreSQL.org>
Subject: case-insensitive like operator
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Hello All,
I have a following problem:
We have ported MS Acess database to PostgreSQL. Everything ok, but our
user are used to search data in tables using filters, and Access does
case insensitive search, but when working with Postgres database it
converts filters into queries with 'like' operator. So is there any
way to make 'like' operator case insensitive? Or maybe somebody has
the same problems and knows the solution?
Best regards,
Yury mailto:yura@vpcit.ru
From bouncefilter Wed Jan 12 06:41:36 2000
Received: from vpcit.ru (qmailr@dialup.vpcit.ru [195.38.52.246])
by hub.org (8.9.3/8.9.3) with SMTP id GAA92974
for <pgsql-general@postgreSQL.org>;
Wed, 12 Jan 2000 06:40:46 -0500 (EST) (envelope-from yura@vpcit.ru)
Received: (qmail 13972 invoked from network); 12 Jan 2000 11:40:43 -0000
Received: from gercon.vpcit.ru (195.38.52.226)
by dialup.vpcit.ru with SMTP; 12 Jan 2000 11:40:43 -0000
Date: Sat, 15 Jan 2000 16:28:11 +0500
From: yura <yura@vpcit.ru>
X-Mailer: The Bat! (v1.36) S/N F29DEE5D / Educational
Reply-To: yura <yura@vpcit.ru>
Organization: Gercon
X-Priority: 3 (Normal)
Message-ID: <15686.000115@vpcit.ru>
To: pgsql-general <pgsql-general@postgreSQL.org>
Subject: Re[2]Have services been enabled for port 5432? Check /etc/services. I have a pair of lines: postgres 5432/tcp # POSTGRES postgres 5432/udp # POSTGRES: [GENERAL] case-insensitive like operator
In-reply-To: <387C6485.19D8D772@albourne.com>
References: <387C6485.19D8D772@albourne.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Hello Adriaan,
Wednesday, January 12, 2000, 4:24:53 PM, you wrote:
AJ> Postgres has a load of text search operators. The most powerful is ~*
AJ> whcih gives you a case insensitive regular expression search.
AJ> Adriaan
Thank you for reply, but i can't use ~* operator, because i don't
create queries, MS Access generates queries and it creates queries
with 'like' operator.
Best regards,
yura mailto:yura@vpcit.ru
From bouncefilter Wed Jan 12 07:02:36 2000
Received: from vpcit.ru (qmailr@dialup.vpcit.ru [195.38.52.246])
by hub.org (8.9.3/8.9.3) with SMTP id HAA98974
for <pgsql-general@postgreSQL.org>;
Wed, 12 Jan 2000 07:01:49 -0500 (EST) (envelope-from yura@vpcit.ru)
Received: (qmail 14196 invoked from network); 12 Jan 2000 12:01:42 -0000
Received: from gercon.vpcit.ru (195.38.52.226)
by dialup.vpcit.ru with SMTP; 12 Jan 2000 12:01:41 -0000
Date: Sat, 15 Jan 2000 16:49:09 +0500
From: yura <yura@vpcit.ru>
X-Mailer: The Bat! (v1.36) S/N F29DEE5D / Educational
Reply-To: yura <yura@vpcit.ru>
Organization: Gercon
X-Priority: 3 (Normal)
Message-ID: <15700.000115@vpcit.ru>
To: pgsql-general <pgsql-general@postgreSQL.org>
Subject: Re[2]Have services been enabled for port 5432? Check /etc/services. I have a pair of lines: postgres 5432/tcp # POSTGRES postgres 5432/udp # POSTGRES: [GENERAL] case-insensitive like operator
In-reply-To:
<Pine.LNX.3.96.1000112170646.120D-100000@trishul.wipinfo.soft.net>
References: <Pine.LNX.3.96.1000112170646.120D-100000@trishul.wipinfo.soft.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Hello Shadkam,
Wednesday, January 12, 2000, 4:41:20 PM, you wrote:
SI> Hi Yury,
SI> I do not know the most correct solution but I used a work around while
SI> encountering a similar problem
SI> select * from table_name
SI> where upper (field_name) like 'SOME_THING_IN_UPPERCASE'
SI> Hope it helps.
SI> - Cheers
SI> - Shad.
SI> + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
SI> | Each human owes infinitely more to the human race than to the |
SI> | particular country in which he was born. -- Francois Fenelon |
SI> + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
SI> On Sat, 15 Jan 2000, yura wrote:
Hello All,
I have a following problem:
We have ported MS Acess database to PostgreSQL. Everything ok, but our
user are used to search data in tables using filters, and Access does
case insensitive search, but when working with Postgres database it
converts filters into queries with 'like' operator. So is there any
way to make 'like' operator case insensitive? Or maybe somebody has
the same problems and knows the solution?Best regards,
Yury mailto:yura@vpcit.ru************
Unfortunately, MS Access converts filters into queries
select ... from <tab> where <fieldname> like <text>
Best regards,
yura mailto:yura@vpcit.ru
From bouncefilter Sat Jan 15 11:47:33 2000
Received: from mail.austin.rr.com (sm1.texas.rr.com [24.93.35.54])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA26422
for <pgsql-general@postgresql.org>;
Sat, 15 Jan 2000 11:47:22 -0500 (EST)
(envelope-from eloehr@austin.rr.com)
Received: from austin.rr.com ([24.27.30.243]) by mail.austin.rr.com with
Microsoft SMTPSVC(5.5.1877.197.19); Sat, 15 Jan 2000 10:47:51 -0600
Sender: ed
Message-ID: <3880A4E9.B1604F20@austin.rr.com>
Date: Sat, 15 Jan 2000 10:48:41 -0600
From: Ed Loehr <eloehr@austin.rr.com>
X-Mailer: Mozilla 4.7 [en] (X11; U; Linux 2.2.12-20smp i686)
X-Accept-Language: en
MIME-Version: 1.0
To: admin <admin@wtbwts.com>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] keeping track of date
References: <Pine.BSF.4.10.10001150041400.86352-100000@server.b0x.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
admin wrote:
What would be the recommended way to keep track of the date/time a tuple
was inserted?Should I be using triggers on insert, or is there an internal variable I
can use to avoid creating another field?
Here's how I'm doing it:
CREATE TABLE mytable (
...
creation_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
Cheers,
Ed Loehr
From bouncefilter Sat Jan 15 11:59:29 2000
Received: from bastuba.partitur.se (bastuba.partitur.se [193.219.246.194])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA27631
for <pgsql-general@postgresql.org>;
Sat, 15 Jan 2000 11:59:07 -0500 (EST)
(envelope-from girgen@partitur.se)
Received: from elbas.partitur.se (elbas.partitur.se [193.219.246.222])
by bastuba.partitur.se (8.8.8/8.8.8) with ESMTP id RAA14011;
Sat, 15 Jan 2000 17:59:05 +0100 (CET)
(envelope-from girgen@partitur.se)
Received: from partitur.se (localhost [127.0.0.1])
by elbas.partitur.se (8.9.3/8.9.3) with ESMTP id RAA46432;
Sat, 15 Jan 2000 17:59:04 +0100 (CET)
(envelope-from girgen@partitur.se)
Sender: girgen@partitur.se
Message-ID: <3880A758.F4B53ACB@partitur.se>
Date: Sat, 15 Jan 2000 17:59:04 +0100
From: Palle Girgensohn <girgen@partitur.se>
Organization: Partitur
X-Mailer: Mozilla 4.7 [en] (X11; I; FreeBSD 3.3-RELEASE i386)
X-Accept-Language: sv, en
MIME-Version: 1.0
To: pgsql-general@postgresql.org
Subject: What's this: OID IS INVALID. TUPGONE 0.
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Hi!
what does this mean?
pp=> vacuum analyze;
NOTICE: Rel pg_statistic: TID 1/93: OID IS INVALID. TUPGONE 0.
VACUUM
pp=>
/Palle
From bouncefilter Sat Jan 15 14:08:35 2000
Received: from test133.ee.ic.ac.uk (IDENT:root@test133.ee.ic.ac.uk
[155.198.133.240]) by hub.org (8.9.3/8.9.3) with ESMTP id OAA55986
for <pgsql-general@hub.org>; Sat, 15 Jan 2000 14:07:45 -0500 (EST)
(envelope-from smacedo@ic.ac.uk)
Received: from localhost (smacedo@localhost)
by test133.ee.ic.ac.uk (8.9.3/8.9.3) with ESMTP id TAA04518;
Sat, 15 Jan 2000 19:06:22 GMT
X-Authentication-Warning: test133.ee.ic.ac.uk: smacedo owned process doing -bs
Date: Sat, 15 Jan 2000 19:06:22 +0000 (GMT)
From: Silvio Macedo <smacedo@ic.ac.uk>
X-Sender: smacedo@test133.ee.ic.ac.uk
To: pgsql-general@hub.org
cc: blake@webintention.com
Subject: Re: Postgresql back-end - INTEGRATED - Excell Spreedsheets
In-Reply-To: <200001150403.XAA27234@hub.org>
Message-ID: <Pine.LNX.4.21.0001151857330.4488-100000@test133.ee.ic.ac.uk>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Hi!
I would suggest that you use PG ODBC drivers to make a system data source
(Setting-Control Panel-ODBC-Add... , after installing PG ODBC driver of
course).
Then, you would use VB for App or whatever excel Macros you do to fill in
those external tables. I have not tried this, but, as far as I know, it
would work, and would be reasonably efficient. I did it with Access
applications, that, in all regards would be very similar.
With some programming, you could even make the secretaries edit the real
tables (or a copy, but still on the PG DB) directly from their m$ excel
spreadsheets.
Any comments ?
Cheers, Silvio
Date: Fri, 14 Jan 2000 18:42:31 -0800
From: "Blake Starkenburg" <blake@webintention.com>Hello All,
I am looking for opinions and suggestions on setting up integration between
a back-end PostgreSQL database engine hosted on a web server and a client
using Excel spreadsheets to store and print internal product information and
pricing.Here's the scenario: I am using a Linux web server, Apache, PostgreSQL,
PHP3.The client's secretaries input all product information currently into Excel
spreadsheets, at there office location on there workstations, dial-up
Internet connection, they will not at the current time switch to complete
web integration. Each month they update product pricing and new products
additions into the excel spreadsheet. On the client's web site we have a
back-end database with the same product and pricing information. The
problem, I need to develop a way for the client to upload an exported excel
spreadsheet in tab-deliminated format to the server and rebuild or update
the PostgreSQl database on the web to reflect the changes.My first thought on the situation would be to write the proper scripts to
allow the client to upload the file to the web server, then click on a
button via a web browser that would run a compiled PHP command to grad the
uploaded file, put it into a variable and run the command psql pg_dump and
rebuild the database with the updated information. This seems inefficient
however, along with the problem of the server running as 'nobody' and I am
wondering if a more efficient ways exists and if you all would have any
suggestions?Thank you everyone,
Blake S.
--
``````````````` Silvio Emanuel Nunes Barbosa de Macedo '''''''''''''''''''
smacedo@ic.ac.uk smacedo@inescn.pt
Imperial College, University of London INESC Porto
Intelligent and Interactive Systems Telecom. and Multimedia
Exhibition Road, Pc da Republica, 93
London SW7 2AZ, England 4050-497 PORTO PORTUGAL
Tel:+44 171 5946323 Tel:+351 22 2094220
From bouncefilter Sat Jan 15 19:29:39 2000
Received: from jason05.u.washington.edu (root@jason05.u.washington.edu
[140.142.78.6]) by hub.org (8.9.3/8.9.3) with ESMTP id TAA47182
for <pgsql-general@postgreSQL.org>;
Sat, 15 Jan 2000 19:29:03 -0500 (EST)
(envelope-from fpm@u.washington.edu)
Received: from saul7.u.washington.edu (fpm@saul7.u.washington.edu
[140.142.82.2])
by jason05.u.washington.edu (8.9.3+UW99.09/8.9.3+UW99.09) with ESMTP id
QAA40288; Sat, 15 Jan 2000 16:29:00 -0800
Received: from localhost (fpm@localhost)
by saul7.u.washington.edu (8.9.3+UW99.09/8.9.3+UW99.09) with ESMTP id
QAA29985; Sat, 15 Jan 2000 16:28:59 -0800 (PST)
Date: Sat, 15 Jan 2000 16:28:58 -0800 (PST)
From: Frank Miles <fpm@u.washington.edu>
To: "C.D. Gan" <cdgan@hotmail.com>
cc: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] Error connecting database
In-Reply-To: <20000115192120.11728.qmail@hotmail.com>
Message-ID: <Pine.OSF.4.10.10001151623430.14858-100000@saul7.u.washington.edu>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
You don't give many details. I'll assume you're using some variant of
Unix.
[1]: Have you checked that the postmaster is running? Try running 'ps aux' to check that it is.
aux' to check that it is.
[2]: Have services been enabled for port 5432? Check /etc/services. I have a pair of lines: postgres 5432/tcp # POSTGRES postgres 5432/udp # POSTGRES
I have a pair of lines:
postgres 5432/tcp # POSTGRES
postgres 5432/udp # POSTGRES
[3]: Do you have permissions to the given database?
[4]: When all else fails, you can try running strace on the process. (Please read the strace man page).
(Please read the strace man page).
Hope this helps!
-frank
On Sun, 16 Jan 2000, C.D. Gan wrote:
I'm just trying to run PGSQL and running the test for it. However I get this
message,Connection to database 'template1' failed.
connectDB() -- connect() failed: No such file or directory
Is the postmaster running at 'localhost' and accepting connections on Unix
socket '5432'?createdb: database creation failed on i-city.
What does that mean? Is it mean that the connect() file is not found under
my /usr/local/pgsql/bin?Gan
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com************
From bouncefilter Sat Jan 15 19:29:43 2000
Received: from sultan.intranet.org (root@ppp-153.net10.magic.fr
[195.154.128.153]) by hub.org (8.9.3/8.9.3) with ESMTP id TAA47209;
Sat, 15 Jan 2000 19:29:25 -0500 (EST) (envelope-from advl@advl.org)
Received: from localhost (localhost [[UNIX: localhost]])
by sultan.intranet.org (8.9.3/8.9.3) id BAA03227;
Sun, 16 Jan 2000 01:36:26 +0100
From: Compte utilisateur Sultan-advl <webmaster@advl.org>
Reply-To: webmaster@advl.org
Organization: ADVL
To: Jeff MacDonald <jeff@hub.org>
Subject: Re: [GENERAL] cgi with postgres
Date: Sun, 16 Jan 2000 01:31:04 +0100
X-Mailer: KMail [version 1.0.21]
Content-Type: text/plain
References: <Pine.BSF.4.10.10001141651160.428-100000@rage.hub.org>
Cc: pgsql-general@hub.org
MIME-Version: 1.0
Message-Id: <00011601362602.03115@sultan>
Content-Transfer-Encoding: 8bit
X-MIME-Autoconverted: from quoted-printable to 8bit by hub.org id TAC47212
Hi,
Take a look at :
Python : http://www.python.org/
There is an adapteur to Postgresql ==> Pygresql
This is GREAT for Unix (Linux) cgi with Postgresql...
Just for information.
Emmanuel.
Le ven, 14 jan 2000, vous avez ���crit :
hey folks,
this is a security issue i'd like to get some info
on, i'm sure it's more with cgi than postgres, but
heck.issue: how to secure cgi's that access postgres
problem: passwords for postgres database are stored
in plain text in scripts. (lets assume, perl,
not a compiled language)
***
From bouncefilter Sun Jan 16 00:53:37 2000
Received: from pcr.ca (www.pcr.ca [207.139.158.13] (may be forged))
by hub.org (8.9.3/8.9.3) with ESMTP id AAA17841
for <pgsql-general@postgresql.org>;
Sun, 16 Jan 2000 00:53:22 -0500 (EST)
(envelope-from admin@wtbwts.com)
Received: by pcr.ca (Postfix, from userid 1000)
id 39AE51FE8; Sun, 16 Jan 2000 00:52:28 +0000 (GMT)
Received: from localhost (localhost [127.0.0.1])
by pcr.ca (Postfix) with ESMTP id 1DB701F8C
for <pgsql-general@postgresql.org>;
Sun, 16 Jan 2000 00:52:28 +0000 (GMT)
Date: Sun, 16 Jan 2000 00:52:28 +0000 (GMT)
From: Marc Tardif <admin@wtbwts.com>
X-Sender: admin@server.b0x.com
To: pgsql-general@postgresql.org
Subject: attribute level rules not supported?
Message-ID: <Pine.BSF.4.10.10001160042460.90301-100000@server.b0x.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Here's the problem:
test=> CREATE TABLE a ( refb OID, at INT2 );
test=> CREATE TABLE b ( refa OID, bt INT2 );
test=> CREATE RULE ar AS ON UPDATE TO a.at
test-> DO UPDATE b SET b.bt = NEW.at
test-> WHERE refa = OLD.oid;
ERROR: attribute level rules currently not supported
What am I doing wrong? I've read and re-read chapter 8 - rules from the
postgresql programmer's guide, but I can't see why this rule doesn't work.
Any suggestions would be appreciated.
Marc
From bouncefilter Sat Jan 15 14:22:36 2000
Received: from hotmail.com (law2-f241.hotmail.com [216.32.181.241])
by hub.org (8.9.3/8.9.3) with SMTP id OAA57951
for <pgsql-general@postgresql.org>;
Sat, 15 Jan 2000 14:21:51 -0500 (EST)
(envelope-from cdgan@hotmail.com)
Received: (qmail 11729 invoked by uid 0); 15 Jan 2000 19:21:20 -0000
Message-ID: <20000115192120.11728.qmail@hotmail.com>
Received: from 202.188.180.214 by www.hotmail.com with HTTP;
Sat, 15 Jan 2000 11:21:20 PST
X-Originating-IP: [202.188.180.214]
From: "C.D. Gan" <cdgan@hotmail.com>
To: pgsql-general@postgresql.org
Subject: Error connecting database
Date: Sun, 16 Jan 2000 03:21:20 MYT
Mime-Version: 1.0
Content-Type: text/plain; format=flowed
I'm just trying to run PGSQL and running the test for it. However I get this
message,
Connection to database 'template1' failed.
connectDB() -- connect() failed: No such file or directory
Is the postmaster running at 'localhost' and accepting connections on Unix
socket '5432'?
createdb: database creation failed on i-city.
What does that mean? Is it mean that the connect() file is not found under
my /usr/local/pgsql/bin?
Gan
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com
From bouncefilter Sun Jan 16 13:43:45 2000
Received: from clio.trends.ca (root@clio.trends.ca [209.47.148.2])
by hub.org (8.9.3/8.9.3) with ESMTP id NAA11381
for <pgsql-general@hub.org>; Sun, 16 Jan 2000 13:42:56 -0500 (EST)
(envelope-from hifiber7@compuserve.com)
From: hifiber7@compuserve.com
Received: from mailmachinepc107.aol.com (98AE3960.ipt.aol.com [152.174.57.96])
by clio.trends.ca (8.9.3+Sun/8.9.3) with SMTP id NAA15554
for <pgsql-general@hub.org>; Sun, 16 Jan 2000 13:42:50 -0500 (EST)
Message-Id: <200001161842.NAA15554@clio.trends.ca>
To: <pgsql-general@hub.org>
Subject: AD:Family Reunion T Shirts & More
Date: Sun, 16 Jan 2000 10:07:00
Message sent by: Kuppler Graphics, 32 West Main Street, Maple
Shade, New Jersey, 08052,
1-800-810-4330. This list will NOT be sold. All addresses
are automatically added to our remove list.
Hello. My name is Bill from Kuppler Graphics. We do
screenprinting on T Shirts, Sweatshirts,
Jackets, Hats, Tote Bags and more!
Do you or someone you know have a Family Reunion coming up?
Kuppler Graphics would like to
provide you with some great looking T Shirts for your Reunion.
Kuppler Graphics can also provide you with custom T's and
promotional items such as imprinted
magnets, keychains, pens, mugs, hats, etc. for your business or
any fundraising activity
(church, school, business etc.) We also can provide you with
quality embroidery.
We are a family owned company with over 15 years of experience.
All work is done at this location. No middle man. Our prices
are great!
Click reply to email us or call 1-800-810-4330 for more info
Bill
Kuppler Graphics
From bouncefilter Sun Jan 16 14:26:49 2000
Received: from mailmachinepc107.aol.com (98AE3960.ipt.aol.com [152.174.57.96])
by hub.org (8.9.3/8.9.3) with SMTP id OAA18735;
Sun, 16 Jan 2000 14:26:24 -0500 (EST)
(envelope-from hifiber7@compuserve.com)
From: hifiber7@compuserve.com
Message-Id: <200001161926.OAA18735@hub.org>
Subject: AD:Family Reunion T Shirts & More
Date: Sun, 16 Jan 2000 10:50:32
To: undisclosed-recipients:;
Message sent by: Kuppler Graphics, 32 West Main Street, Maple
Shade, New Jersey, 08052,
1-800-810-4330. This list will NOT be sold. All addresses
are automatically added to our remove list.
Hello. My name is Bill from Kuppler Graphics. We do
screenprinting on T Shirts, Sweatshirts,
Jackets, Hats, Tote Bags and more!
Do you or someone you know have a Family Reunion coming up?
Kuppler Graphics would like to
provide you with some great looking T Shirts for your Reunion.
Kuppler Graphics can also provide you with custom T's and
promotional items such as imprinted
magnets, keychains, pens, mugs, hats, etc. for your business or
any fundraising activity
(church, school, business etc.) We also can provide you with
quality embroidery.
We are a family owned company with over 15 years of experience.
All work is done at this location. No middle man. Our prices
are great!
Click reply to email us or call 1-800-810-4330 for more info
Bill
Kuppler Graphics
From bouncefilter Sun Jan 16 12:07:51 2000
Received: from merganser.its.uu.se (merganser.its.uu.se [130.238.6.236])
by hub.org (8.9.3/8.9.3) with ESMTP id MAA88304
for <pgsql-general@postgresql.org>;
Sun, 16 Jan 2000 12:07:24 -0500 (EST)
(envelope-from peter@localhost.its.uu.se)
Received: from regulus.its.uu.se ([130.238.7.19]:62546 "EHLO
regulus.its.uu.se")
by merganser.its.uu.se with ESMTP id <S305167AbQAPRGj>;
Sun, 16 Jan 2000 18:06:39 +0100
Received: from peter (helo=localhost)
by regulus.its.uu.se with local-esmtp (Exim 3.02 #2)
id 129tEx-0003Zi-00; Sun, 16 Jan 2000 18:13:23 +0100
Date: Sun, 16 Jan 2000 18:13:23 +0100 (CET)
From: Peter Eisentraut <peter_e@gmx.net>
To: yura <yura@vpcit.ru>
cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Re[2]Have services been enabled for port 5432? Check /etc/services. I have a pair of lines: postgres 5432/tcp # POSTGRES postgres 5432/udp # POSTGRES: [GENERAL] case-insensitive like operator
In-Reply-To: <15686.000115@vpcit.ru>
Message-ID: <Pine.LNX.4.21.0001151946210.386-100000@localhost.localdomain>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-1
Content-Transfer-Encoding: 8BIT
Sender: Peter Eisentraut <peter@hub.org>
On 2000-01-15, yura mentioned:
Hello Adriaan,
Wednesday, January 12, 2000, 4:24:53 PM, you wrote:
AJ> Postgres has a load of text search operators. The most powerful is ~*
AJ> whcih gives you a case insensitive regular expression search.AJ> Adriaan
Thank you for reply, but i can't use ~* operator, because i don't
create queries, MS Access generates queries and it creates queries
with 'like' operator.
The way I read the SQL standard, LIKE matches are always case-sensitive,
so Access would be in violation.
--
Peter Eisentraut Sernanders v���g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
From bouncefilter Sun Jan 16 12:08:00 2000
Received: from merganser.its.uu.se (merganser.its.uu.se [130.238.6.236])
by hub.org (8.9.3/8.9.3) with ESMTP id MAA88307
for <pgsql-general@postgresql.org>;
Sun, 16 Jan 2000 12:07:25 -0500 (EST)
(envelope-from peter@localhost.its.uu.se)
Received: from regulus.its.uu.se ([130.238.7.19]:62694 "EHLO
regulus.its.uu.se")
by merganser.its.uu.se with ESMTP id <S307201AbQAPRGn>;
Sun, 16 Jan 2000 18:06:43 +0100
Received: from peter (helo=localhost)
by regulus.its.uu.se with local-esmtp (Exim 3.02 #2)
id 129tFE-0003Zv-00; Sun, 16 Jan 2000 18:13:40 +0100
Date: Sun, 16 Jan 2000 18:13:40 +0100 (CET)
From: Peter Eisentraut <peter_e@gmx.net>
To: omid omoomi <oomoomi@hotmail.com>
cc: danderso@crystalsugar.com, pgsql-general@postgresql.org
Subject: Re: [GENERAL] PSQL Function() help....
In-Reply-To: <20000113085149.45693.qmail@hotmail.com>
Message-ID: <Pine.LNX.4.21.0001151954070.386-100000@localhost.localdomain>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-1
Content-Transfer-Encoding: 8BIT
Sender: Peter Eisentraut <peter@hub.org>
An array is not quite the same as a result set. What the questioner wanted
(as I understand it) was a function that returned more than one row. That
doesn't work. If you manage to do it via arrays, more power to you.
On 2000-01-13, omid omoomi mentioned:
Hello,
Sure you can use arrays as returned results in your function.regards.
Omid OmoomiFrom: Peter Eisentraut <peter_e@gmx.net>
To: Dale Anderson <danderso@crystalsugar.com>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] PSQL Function() help....
Date: Wed, 12 Jan 2000 04:58:53 +0100On 2000-01-12, Dale Anderson mentioned:
Here is what I am trying to do. I an trying to create a function
that is passed two numbers, get_people(2000,1). The first value is a
year, and the second is a week. What I want the function to do, is to
select all the names from a name table, and return a list of names
that don't have an entry in the data table for the year, and week
specified. Can this be done??No. Functions cannot return result sets. Certainly a deficiency, but
nobody is perfect.--
Peter Eisentraut Sernanders v���g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden************
--
Peter Eisentraut Sernanders v���g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
From bouncefilter Sun Jan 16 12:07:54 2000
Received: from merganser.its.uu.se (merganser.its.uu.se [130.238.6.236])
by hub.org (8.9.3/8.9.3) with ESMTP id MAA88332
for <pgsql-general@postgresql.org>;
Sun, 16 Jan 2000 12:07:44 -0500 (EST)
(envelope-from peter@localhost.its.uu.se)
Received: from regulus.its.uu.se ([130.238.7.19]:62991 "EHLO
regulus.its.uu.se")
by merganser.its.uu.se with ESMTP id <S339991AbQAPRHA>;
Sun, 16 Jan 2000 18:07:00 +0100
Received: from peter (helo=localhost)
by regulus.its.uu.se with local-esmtp (Exim 3.02 #2)
id 129tFK-0003Zx-00; Sun, 16 Jan 2000 18:13:46 +0100
Date: Sun, 16 Jan 2000 18:13:46 +0100 (CET)
From: Peter Eisentraut <peter_e@gmx.net>
To: Gabriel Fernandez <gabi@unica.edu>
cc: PostgreSQL-general <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Confussion with table-lock levels and isolation levels
In-Reply-To: <387D94F8.9CB6F223@unica.edu>
Message-ID: <Pine.LNX.4.21.0001151955470.386-100000@localhost.localdomain>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-1
Content-Transfer-Encoding: 8BIT
Sender: Peter Eisentraut <peter@hub.org>
On 2000-01-13, Gabriel Fernandez mentioned:
- What does exactly mean that a mode 'CONFLICTS' with another
?
- Does it mean that another concurrent transactions having
these modes will have to wait until the first transaction
have finished (commit or roll back) ?
Yes. You can try that pretty easily. Start up two psqls, in the first one
enter:
BEGIN TRANSACTION;
LOCK pg_class IN ACCESS EXCLUSIVE MODE;
In the second one enter:
SELECT * FROM pg_class;
The latter will hang.
--
Peter Eisentraut Sernanders v���g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
From bouncefilter Sun Jan 16 12:07:47 2000
Received: from merganser.its.uu.se (merganser.its.uu.se [130.238.6.236])
by hub.org (8.9.3/8.9.3) with ESMTP id MAA88318
for <pgsql-general@postgresql.org>;
Sun, 16 Jan 2000 12:07:29 -0500 (EST)
(envelope-from peter@localhost.its.uu.se)
Received: from regulus.its.uu.se ([130.238.7.19]:62843 "EHLO
regulus.its.uu.se")
by merganser.its.uu.se with ESMTP id <S309253AbQAPRGv>;
Sun, 16 Jan 2000 18:06:51 +0100
Received: from peter (helo=localhost)
by regulus.its.uu.se with local-esmtp (Exim 3.02 #2)
id 129tFP-0003a6-00; Sun, 16 Jan 2000 18:13:51 +0100
Date: Sun, 16 Jan 2000 18:13:51 +0100 (CET)
From: Peter Eisentraut <peter_e@gmx.net>
To: Rick Delaney <rick@consumercontact.com>
cc: Adriaan Joubert <a.joubert@albourne.com>, admin <admin@wtbwts.com>,
pgsql-general@postgresql.org
Subject: Re: Oids vs Serial fields (was Re: [GENERAL] searching oid's)
In-Reply-To: <387F4A5D.6A50CECE@consumercontact.com>
Message-ID: <Pine.LNX.4.21.0001152008310.386-100000@localhost.localdomain>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-1
Content-Transfer-Encoding: 8BIT
Sender: Peter Eisentraut <peter@hub.org>
On 2000-01-14, Rick Delaney mentioned:
You make some good points, but where is this recommended in the docs? I'd like
to see more of "serial vs oid" if there is something.
Although I don't represent any official opinion, the tendency among the
developers would seem to be not using oids for unique values. The serial
type is guaranteed to be supported for this but oids just happen to work.
--
Peter Eisentraut Sernanders v���g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
From bouncefilter Sun Jan 16 12:08:03 2000
Received: from merganser.its.uu.se (merganser.its.uu.se [130.238.6.236])
by hub.org (8.9.3/8.9.3) with ESMTP id MAA88305;
Sun, 16 Jan 2000 12:07:24 -0500 (EST)
(envelope-from peter@localhost.its.uu.se)
Received: from regulus.its.uu.se ([130.238.7.19]:62770 "EHLO
regulus.its.uu.se")
by merganser.its.uu.se with ESMTP id <S309248AbQAPRGr>;
Sun, 16 Jan 2000 18:06:47 +0100
Received: from peter (helo=localhost)
by regulus.its.uu.se with local-esmtp (Exim 3.02 #2)
id 129tFU-0003aM-00; Sun, 16 Jan 2000 18:13:56 +0100
Date: Sun, 16 Jan 2000 18:13:56 +0100 (CET)
From: Peter Eisentraut <peter_e@gmx.net>
To: Alfred Perlstein <bright@wintelcom.net>
cc: Jeff MacDonald <jeff@hub.org>, pgsql-general@hub.org
Subject: Re: [GENERAL] cgi with postgres
In-Reply-To: <20000114135329.D508@fw.wintelcom.net>
Message-ID: <Pine.LNX.4.21.0001152014020.386-100000@localhost.localdomain>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-1
Content-Transfer-Encoding: 8BIT
Sender: Peter Eisentraut <peter@hub.org>
On 2000-01-14, Alfred Perlstein mentioned:
issue: how to secure cgi's that access postgres
problem: passwords for postgres database are stored
in plain text in scripts. (lets assume, perl,
not a compiled language)points:
make cgi dir 711
big deal, they can get the name of the file
from the web, and copy it.how about sourcing a conf file that's in a 700 dir?
Security through obscurity is little security indeed.
--
Peter Eisentraut Sernanders v���g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
From bouncefilter Sun Jan 16 12:07:47 2000
Received: from merganser.its.uu.se (merganser.its.uu.se [130.238.6.236])
by hub.org (8.9.3/8.9.3) with ESMTP id MAA88314
for <pgsql-general@postgresql.org>;
Sun, 16 Jan 2000 12:07:27 -0500 (EST)
(envelope-from peter@localhost.its.uu.se)
Received: from regulus.its.uu.se ([130.238.7.19]:62829 "EHLO
regulus.its.uu.se")
by merganser.its.uu.se with ESMTP id <S303125AbQAPRGv>;
Sun, 16 Jan 2000 18:06:51 +0100
Received: from peter (helo=localhost)
by regulus.its.uu.se with local-esmtp (Exim 3.02 #2)
id 129tFY-0003ac-00; Sun, 16 Jan 2000 18:14:00 +0100
Date: Sun, 16 Jan 2000 18:14:00 +0100 (CET)
From: Peter Eisentraut <peter_e@gmx.net>
To: Ed Loehr <eloehr@austin.rr.com>
cc: pg-gen <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] GRANT ALL ON * TO username?
In-Reply-To: <387FA88F.41F1F860@austin.rr.com>
Message-ID: <Pine.LNX.4.21.0001152020430.386-100000@localhost.localdomain>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-1
Content-Transfer-Encoding: 8BIT
Sender: Peter Eisentraut <peter@hub.org>
On 2000-01-14, Ed Loehr mentioned:
I'd like to grant read-access to every single DB object in a database
for one user. I'd hoped forGRANT SELECT ON * TO <username>
but the '*' syntax is wrong and I don't see an alternative in the
docs...How is this done?
The standard doesn't allow this either. You'd have to do some scripting
magic.
--
Peter Eisentraut Sernanders v���g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
From bouncefilter Sun Jan 16 12:08:04 2000
Received: from merganser.its.uu.se (merganser.its.uu.se [130.238.6.236])
by hub.org (8.9.3/8.9.3) with ESMTP id MAA88328;
Sun, 16 Jan 2000 12:07:42 -0500 (EST)
(envelope-from peter@localhost.its.uu.se)
Received: from regulus.its.uu.se ([130.238.7.19]:62936 "EHLO
regulus.its.uu.se")
by merganser.its.uu.se with ESMTP id <S339986AbQAPRG4>;
Sun, 16 Jan 2000 18:06:56 +0100
Received: from peter (helo=localhost)
by regulus.its.uu.se with local-esmtp (Exim 3.02 #2)
id 129tFh-0003b1-00; Sun, 16 Jan 2000 18:14:09 +0100
Date: Sun, 16 Jan 2000 18:14:08 +0100 (CET)
From: Peter Eisentraut <peter_e@gmx.net>
To: The Hermit Hacker <scrappy@hub.org>
cc: Jeff MacDonald <jeff@hub.org>, pgsql-general@hub.org
Subject: Re: [GENERAL] cgi with postgres
In-Reply-To: <Pine.BSF.4.21.0001142014370.46499-100000@thelab.hub.org>
Message-ID: <Pine.LNX.4.21.0001152028400.386-100000@localhost.localdomain>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-1
Content-Transfer-Encoding: 8BIT
Sender: Peter Eisentraut <peter@hub.org>
On 2000-01-14, The Hermit Hacker mentioned:
Side point ... why isn't the apache conf file secure? Only user root
needs to be able to read it, no?
Huh? You run apache as root?
--
Peter Eisentraut Sernanders v���g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
From bouncefilter Sun Jan 16 18:59:49 2000
Received: from pcr.ca (www.pcr.ca [207.139.158.13] (may be forged))
by hub.org (8.9.3/8.9.3) with ESMTP id SAA78538
for <pgsql-general@postgresql.org>;
Sun, 16 Jan 2000 18:58:58 -0500 (EST)
(envelope-from admin@wtbwts.com)
Received: by pcr.ca (Postfix, from userid 1000)
id 80F071FEC; Sun, 16 Jan 2000 18:57:45 +0000 (GMT)
Received: from localhost (localhost [127.0.0.1])
by pcr.ca (Postfix) with ESMTP id 5CD541F8C
for <pgsql-general@postgresql.org>;
Sun, 16 Jan 2000 18:57:45 +0000 (GMT)
Date: Sun, 16 Jan 2000 18:57:45 +0000 (GMT)
From: Marc Tardif <admin@wtbwts.com>
X-Sender: admin@server.b0x.com
To: pgsql-general@postgresql.org
Subject: pg_dump: couldn't find the template1 database.
Message-ID: <Pine.BSF.4.10.10001161853270.2392-100000@server.b0x.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
I have updated my port of postgresql on freebsd-current using the
following commands:
# make
# pkg_delete postgresql-6.5.1
# make install
Everything is working well, except for pg_dump which now returns:
pg_dump: couldn't find the template1 database. You are really hosed.
Giving up.
Yet, the template1 database does exist in it's rightful place in
/usr/local/pgsql/data/base. The template1 directory is full of files I
don't quite understand and everything seems to be working well. I'm hoping
someone would know what's the problem with pg_dump or my template1
database.
Thanks in advance,
Marc
From bouncefilter Sun Jan 16 14:30:49 2000
Received: from relay.flashnet.it (root@dns2.flashnet.it [194.247.160.8])
by hub.org (8.9.3/8.9.3) with ESMTP id OAA19072
for <pgsql-general@postgresql.org>;
Sun, 16 Jan 2000 14:30:03 -0500 (EST)
(envelope-from bigpaul@flashnet.it)
Received: from flashnet.it (ip035.pool-02.flashnet.it [195.191.2.36])
by relay.flashnet.it (8.9.3/8.9.3) with ESMTP id UAA16011
for <pgsql-general@postgresql.org>; Sun, 16 Jan 2000 20:29:54 +0100
Sender: root@relay.flashnet.it
Message-ID: <38821A30.8A9EAF27@flashnet.it>
Date: Sun, 16 Jan 2000 20:21:20 +0100
From: Il Paolone <bigpaul@flashnet.it>
Reply-To: bigpaul@flashnet.it
X-Mailer: Mozilla 4.6 [en] (X11; I; Linux 2.2.12 i686)
X-Accept-Language: it, en
MIME-Version: 1.0
To: pgsql-general@postgresql.org
Subject: Debian php3+postgresql unable to connect
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
I have Debian 2.1
PostgreSQL 6.5.3
with PHP 3.0.5
e php-pgsql module
I have set up everything. Apache works, php works, pgsql module is
loaded but this is the message whe i try to connect to the db:
Unable to connect to PostgresSQL server: connectDB() failed: Is the
postmaster running and accepting TCP/IP(with -i) connections at
'bigpaul' on port '5432'? in /var/www/ciao.php3 on line 34
The sintax to connect to the db is:
<?PHP
$dbserver = "bigpaul"; //bigpaul is my server name
$dbport = "5432";
$database = "gestione";
$conn = pg_Connect ("host=$dbserver port=$dbport dbname=$database");
if (!$conn) { // Errore accesso al database
echo "Si è verificato un errore.\n";
} else {
pg_Exec................
In the documentation it is written that if you have this kind of problem
it means that:
1) the postmaster it is not running
2) you have not specified the server name.
But:
I have the capabilities to use psql so I think that the postmaster is
running (I can create db, tables etc.)
I have specified, omitted, declared as "127.0.0.1" the server name
So what can I do?
Thanks
G.
--
LDR - Linux Domande e Risposte: http://web.tiscalinet.it/linuxfaq
Pluto mirror: http://www.pluto.linux.it/ildp/LDR
Gaetano Paolone <bigpaul@flashnet.it>
E non dimenticate Linuxlinks: http://jump.to/linuxlinks
From bouncefilter Sun Jan 16 16:03:48 2000
Received: from www.wgcr.org (IDENT:root@www.wgcr.org [206.74.232.194])
by hub.org (8.9.3/8.9.3) with ESMTP id QAA42358
for <pgsql-general@postgreSQL.org>;
Sun, 16 Jan 2000 16:03:45 -0500 (EST)
(envelope-from lamar.owen@wgcr.org)
Received: from lorc.wgcr.org (dial-29.r11.ncbrvr.infoave.net [207.144.78.93])
by www.wgcr.org (8.9.3/8.9.3/WGCR) with SMTP id QAA12809;
Sun, 16 Jan 2000 16:03:49 -0500
From: Lamar Owen <lamar.owen@wgcr.org>
Organization: Little to None
To: bigpaul@flashnet.it, Il Paolone <bigpaul@flashnet.it>,
pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] Debian php3+postgresql unable to connect
Date: Sun, 16 Jan 2000 16:08:12 -0500
X-Mailer: KMail [version 1.0.28]
Content-Type: text/plain
References: <38821A30.8A9EAF27@flashnet.it>
In-Reply-To: <38821A30.8A9EAF27@flashnet.it>
MIME-Version: 1.0
Message-Id: <00011616090200.00555@lorc.wgcr.org>
Content-Transfer-Encoding: 8bit
On Sun, 16 Jan 2000, Il Paolone wrote:
Unable to connect to PostgresSQL server: connectDB() failed: Is the
postmaster running and accepting TCP/IP(with -i) connections at
'bigpaul' on port '5432'? in /var/www/ciao.php3 on line 34
Is postmaster being started with the '-i' switch??
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11
From bouncefilter Sun Jan 16 15:52:47 2000
Received: from fw.wintelcom.net (bright@ns1.wintelcom.net [209.1.153.20])
by hub.org (8.9.3/8.9.3) with ESMTP id PAA37552;
Sun, 16 Jan 2000 15:51:47 -0500 (EST)
(envelope-from bright@fw.wintelcom.net)
Received: (from bright@localhost)
by fw.wintelcom.net (8.9.3/8.9.3) id NAA12201;
Sun, 16 Jan 2000 13:14:35 -0800 (PST)
Date: Sun, 16 Jan 2000 13:14:35 -0800
From: Alfred Perlstein <bright@wintelcom.net>
To: Peter Eisentraut <peter_e@gmx.net>
Cc: Jeff MacDonald <jeff@hub.org>, pgsql-general@hub.org
Subject: Re: [GENERAL] cgi with postgres
Message-ID: <20000116131435.G508@fw.wintelcom.net>
References: <20000114135329.D508@fw.wintelcom.net>
<Pine.LNX.4.21.0001152014020.386-100000@localhost.localdomain>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Mailer: Mutt 1.0i
In-Reply-To: <Pine.LNX.4.21.0001152014020.386-100000@localhost.localdomain>;
from peter_e@gmx.net on Sun, Jan 16, 2000 at 06:13:56PM +0100
* Peter Eisentraut <peter_e@gmx.net> [000116 09:30] wrote:
On 2000-01-14, Alfred Perlstein mentioned:
issue: how to secure cgi's that access postgres
problem: passwords for postgres database are stored
in plain text in scripts. (lets assume, perl,
not a compiled language)points:
make cgi dir 711
big deal, they can get the name of the file
from the web, and copy it.how about sourcing a conf file that's in a 700 dir?
Security through obscurity is little security indeed.
I don't see how using the unix permissions as a
form of ACL is security through obscurity... or do you
chmod 644 /etc/shadow on your boxes?
--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
From bouncefilter Sun Jan 16 17:33:48 2000
Received: from relay.flashnet.it (root@dns2.flashnet.it [194.247.160.8])
by hub.org (8.9.3/8.9.3) with ESMTP id RAA61060
for <pgsql-general@postgresql.org>;
Sun, 16 Jan 2000 17:33:26 -0500 (EST)
(envelope-from bigpaul@flashnet.it)
Received: from flashnet.it (ip073.pool-04.flashnet.it [195.191.4.74])
by relay.flashnet.it (8.9.3/8.9.3) with ESMTP id XAA11233;
Sun, 16 Jan 2000 23:33:14 +0100
Sender: root@relay.flashnet.it
Message-ID: <388244A2.7AE67CF7@flashnet.it>
Date: Sun, 16 Jan 2000 23:22:26 +0100
From: Il Paolone <bigpaul@flashnet.it>
Reply-To: bigpaul@flashnet.it
X-Mailer: Mozilla 4.6 [en] (X11; I; Linux 2.2.12 i686)
X-Accept-Language: it, en
MIME-Version: 1.0
To: Lamar Owen <lamar.owen@wgcr.org>, pgsql-general@postgresql.org
Subject: Re: [GENERAL] Debian php3+postgresql unable to connect
References: <38821A30.8A9EAF27@flashnet.it>
<00011616090200.00555@lorc.wgcr.org> <38823926.BF23F62@flashnet.it>
<00011617190602.00555@lorc.wgcr.org>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lamar Owen wrote:
On Sun, 16 Jan 2000, Il Paolone wrote:
Lamar Owen wrote:
Is postmaster being started with the '-i' switch??
I have found with deja, this french problem just now, I am trying, I'll
let you know.
----------------------------------------------------------------------
Fait un petit tour dans le repertoire /etc/postgresql.
Tu y trouveras des fichiers de config, dont postmaster.init qui
contient une ligne pour autoriser les connexion TCP/IP
: PGALLOWTCPIP=yes Ainsi que PGPORT=5432 pour definir le port TCP.
Il y a aussi le fichier pg_hba.conf qui te permets d'autoriser les
connexions a distances.
-----------------------------------------------------------------------
YYYYYYYYEEEEEEEEEES! it works, once again deja.com saved me. And you
also pushed me to look for the right solution.
Thanks a lot.
I also found this....
------------------------------------------------------------------------------------
Check line 64 of /usr/lib/postgresql/bin/postgresql-startup.
echo link it to /bin/true and run '/etc/init.d/postgresql
start'
There is a mis-matched quote mark. Change the first ` to a ' and the
postmaster
should start.
--------------------------------------------------------------------------------------
--
LDR - Linux Domande e Risposte: http://web.tiscalinet.it/linuxfaq
Pluto mirror: http://www.pluto.linux.it/ildp/LDR
Gaetano Paolone <bigpaul@flashnet.it>
E non dimenticate Linuxlinks: http://jump.to/linuxlinks
From bouncefilter Sun Jan 16 17:40:49 2000
Received: from kiln.isn.net (root@kiln.isn.net [198.167.161.1])
by hub.org (8.9.3/8.9.3) with ESMTP id RAA61579
for <pgsql-general@postgreSQL.org>;
Sun, 16 Jan 2000 17:39:54 -0500 (EST)
(envelope-from ctassell@isn.net)
Received: from niki (dunken04.isn.net [216.129.42.71])
by kiln.isn.net (8.9.3/8.9.3) with ESMTP id SAA25924;
Sun, 16 Jan 2000 18:39:17 -0400
Message-Id: <4.2.0.58.20000116183519.00a80dd0@mailer.isn.net>
X-Sender: ctassell@mailer.isn.net
X-Mailer: QUALCOMM Windows Eudora Pro Version 4.2.0.58
Date: Sun, 16 Jan 2000 18:36:50 -0400
To: "C.D. Gan" <cdgan@hotmail.com>, pgsql-general@postgreSQL.org
From: Charles Tassell <ctassell@isn.net>
Subject: Re: [GENERAL] Error connecting database
In-Reply-To: <20000115192120.11728.qmail@hotmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed
You might want to check that you have run initdb. Log in as user postgres
and type:
/usr/local/pgsql/bin/initdb
Then check to make sure that postmaster is actually running, as someone
else suggested.
At 11:21 PM 1/15/00, C.D. Gan wrote:
I'm just trying to run PGSQL and running the test for it. However I get
this message,Connection to database 'template1' failed.
connectDB() -- connect() failed: No such file or directory
Is the postmaster running at 'localhost' and accepting connections on Unix
socket '5432'?createdb: database creation failed on i-city.
What does that mean? Is it mean that the connect() file is not found under
my /usr/local/pgsql/bin?Gan
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com************
From bouncefilter Sun Jan 16 18:23:48 2000
Received: from news.tht.net (news.hub.org [216.126.91.242])
by hub.org (8.9.3/8.9.3) with ESMTP id SAA71403
for <pgsql-general@postgresql.org>;
Sun, 16 Jan 2000 18:23:13 -0500 (EST)
(envelope-from news@news.tht.net)
Received: (from news@localhost) by news.tht.net (8.9.3/8.9.3) id RAA51516
for pgsql-general@postgresql.org; Sun, 16 Jan 2000 17:54:47 -0500 (EST)
(envelope-from news)
X-Authentication-Warning: news.tht.net: news set sender to <news> using -f
From: clyde jones <pylqrp@trbpvgvrf.pbz.tht.net>
X-Newsgroups: comp.databases.postgresql.questions
Subject: web frontend for postgres database?
Organization: Luna Consulting
User-Agent: MT-NewsWatcher/3.0 (PPC)
Message-ID: <pylqrp-E48617.17543616012000@news>
Lines: 15
Date: Sun, 16 Jan 2000 22:54:32 GMT
X-Trace: typhoon1.gnilink.net 948063272 151.196.11.187 (Sun,
16 Jan 2000 17:54:32 EST)
To: pgsql-questions@postgresql.org
Can anyone recommend a good web-based frontend for a postgres database?
I want to port some databases to postgres, and I would love to make all
my forms html. I am primarily looking for a html UI for the database,
and a creation/management tool second.
thanks
--
Pray to God, But Hammer Away
- Spanish Proverb
Clyde Jones
jjj.trbpvgvrf.pbz/pylqr-wbarf
pylqrp@trbpvgvrf.pbz
From bouncefilter Sun Jan 16 18:36:50 2000
Received: from Viola.Opus1.COM (IDENT:unIDENTified@Viola.Opus1.COM
[192.245.12.8]) by hub.org (8.9.3/8.9.3) with ESMTP id SAA75571;
Sun, 16 Jan 2000 18:36:13 -0500 (EST) (envelope-from ron@Opus1.COM)
Received: from opus1.com ([204.27.149.77]) by Opus1.COM (PMDF V5.2-32 #9830)
with ESMTPS id <01JKS227977698CZWJ@Opus1.COM>;
Sun, 16 Jan 2000 16:36:11 MST
Date: Sun, 16 Jan 2000 16:36:09 -0700
From: Ron Chmara <ron@Opus1.COM>
Subject: Re: [GENERAL] cgi with postgres
To: Peter Eisentraut <peter_e@gmx.net>
Cc: Alfred Perlstein <bright@wintelcom.net>, Jeff MacDonald <jeff@hub.org>,
pgsql-general@hub.org
Reply-to: ron@Opus1.COM
Message-id: <388255E6.F5E5324D@opus1.com>
Organization: Ronin
MIME-version: 1.0
X-Mailer: Mozilla 4.7 (Macintosh; U; PPC)
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7bit
X-Accept-Language: en
References: <Pine.LNX.4.21.0001152014020.386-100000@localhost.localdomain>
Peter Eisentraut wrote:
On 2000-01-14, Alfred Perlstein mentioned:
issue: how to secure cgi's that access postgres
problem: passwords for postgres database are stored
in plain text in scripts. (lets assume, perl,
not a compiled language)points:
make cgi dir 711
big deal, they can get the name of the file
from the web, and copy it.how about sourcing a conf file that's in a 700 dir?
Security through obscurity is little security indeed.
1. If anybody can read your web page, well, then, you
would need to make a "reader" account in your DBMS.
That way, anybody can get the username and password,
and it may not matter. As long as this user only has
perms to the database (as in, no shell, no other perms
on the box), it's about as secure as any other service
that passes user/pass over the 'net in readable form....
2. Deeper accounts can be controled via .htaccess,
via apache. Again, this has holes, but now the potential
hacker has two hoops to jump through.
3. Access to specific virtual hosts can be controlled
via packet filtering, for more secure connections to
an admin page. Yet another hoop.
4. Security through obscurity _can_ be of great value,
if your threat scenario is primarily individuals who
wouldn't know where to look, or what to look for. If
your threat scenario includes individuals who do have
that skill level, yup, it's not that much protection.
It's like hiding the lock on a door. It's provide security
against lock pickers, but no security against the guy
with a sledge hammer. A hoop for those with little
imagination or knowledge....
Security is not something you can program, buy, or build,
it's a mindset. For each hoop you require users to jump
through, it's another hoop for a hacker to jump through.
Since you _will_ be hacked, by somebody, eventually, you
have to balance the level of security with the level of
*value* it offers. For example, disabling cgi's helps quite
a bit, from a security standpoint, but it makes users
cranky. Is that crankyness worth the security of removing
all cgi's? If your transactions are worth several thousand
a day, of course it is. Are your databases so valuable that
you don't want to allow users write access? Don't put
them on writable media. Do you need to guarantee that
only specific users have access to specific transactions
in a specific database? Then cut the cord on internet access,
entirely.
Back to the original question:
issue: how to secure cgi's that access postgres
For *absolutely secure* environments, remove the cgi's, erase
the database, take the box in back, and burn it, then crush it
into little bits, then sprinkle the ashes over no less than a
300 km radius, in non-equal distribution. ;-)
For slightly less security, you should follow two basic, old-school
rules: Something you know, and something you have. For each level
of security, you should require at least one measure of each. For
vanilla access, they would *know* where the server is, and *have* one
pasword. More secure: They would *know* a non-htttp-linked, non-listed,
CGI file to access, and *have* a specfic IP they are coming from. Next,
maybe knowing another access code, and having a stored cookie
which is checked against... and on, and on, so by the time they have
a high level of access, they've managed to use passwords, RSA keys,
their IP, a fingerprint, an eyeball scan..... but it's still not quite as
secure as destroying all of the data. :-) Up to that point, it's just
a matter of making enough hoops to jump through that it keeps
most of your threat assesments at bay, and using them in an
_additive_ fashion.
Additive? Well, a retinal scan and password combo is only
two hoops, but a retinal scan, and password, and RSA key, and
initiating IP, and facial scan, and tunneled session, is 6 things
a cracker has to break, in the right order, in order to get in. So
you have to balance how much security you need versus how many
hoops you want to make, as setting up 20+ boxes, in series, to
use a SELECT statement, may be a bit absurd for your application.
:-) I've done it for giggles, performance was not the best, but
it worked well enough that it was pretty much impenetrable
from over the internet. Only took two or three minutes to pass
a select staement through, and half an hour or so to return
a few rows... <g>
HTH,
-Ronabop
From bouncefilter Sun Jan 16 19:08:49 2000
Received: from fw.wintelcom.net (bright@ns1.wintelcom.net [209.1.153.20])
by hub.org (8.9.3/8.9.3) with ESMTP id TAA83009;
Sun, 16 Jan 2000 19:08:42 -0500 (EST)
(envelope-from bright@fw.wintelcom.net)
Received: (from bright@localhost)
by fw.wintelcom.net (8.9.3/8.9.3) id QAA17066;
Sun, 16 Jan 2000 16:31:31 -0800 (PST)
Date: Sun, 16 Jan 2000 16:31:31 -0800
From: Alfred Perlstein <bright@wintelcom.net>
To: Ron Chmara <ron@Opus1.COM>
Cc: Peter Eisentraut <peter_e@gmx.net>, Jeff MacDonald <jeff@hub.org>,
pgsql-general@hub.org
Subject: Re: [GENERAL] cgi with postgres
Message-ID: <20000116163131.K508@fw.wintelcom.net>
References: <Pine.LNX.4.21.0001152014020.386-100000@localhost.localdomain>
<388255E6.F5E5324D@opus1.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Mailer: Mutt 1.0i
In-Reply-To: <388255E6.F5E5324D@opus1.com>;
from ron@Opus1.COM on Sun, Jan 16, 2000 at 04:36:09PM -0700
* Ron Chmara <ron@Opus1.COM> [000116 16:18] wrote:
Peter Eisentraut wrote:
On 2000-01-14, Alfred Perlstein mentioned:
issue: how to secure cgi's that access postgres
problem: passwords for postgres database are stored
in plain text in scripts. (lets assume, perl,
not a compiled language)points:
make cgi dir 711
big deal, they can get the name of the file
from the web, and copy it.how about sourcing a conf file that's in a 700 dir?
Security through obscurity is little security indeed.
1. If anybody can read your web page, well, then, you
would need to make a "reader" account in your DBMS.
That way, anybody can get the username and password,
and it may not matter. As long as this user only has
perms to the database (as in, no shell, no other perms
on the box), it's about as secure as any other service
that passes user/pass over the 'net in readable form....2. Deeper accounts can be controled via .htaccess,
via apache. Again, this has holes, but now the potential
hacker has two hoops to jump through.3. Access to specific virtual hosts can be controlled
via packet filtering, for more secure connections to
an admin page. Yet another hoop.4. Security through obscurity _can_ be of great value,
if your threat scenario is primarily individuals who
wouldn't know where to look, or what to look for. If
your threat scenario includes individuals who do have
that skill level, yup, it's not that much protection.
It's like hiding the lock on a door. It's provide security
against lock pickers, but no security against the guy
with a sledge hammer. A hoop for those with little
imagination or knowledge....
All these options don't take into account that perhaps you don't
want people _on the same box_ to be able to even read arbitrary
things from your database. Sure you can protect the tables from
modification via postgresql's internal ACL system, however what
if you wish to deny read access as well?
For this you need a file with an ACL prevents other _local users_
from reading it.
This is _not_ security through obscurity, this is basic ACL control.
Again, figure a way to 'protect' your shadow password database
without relying on an external auth server.
Security is not something you can program, buy, or build,
it's a mindset. For each hoop you require users to jump
through, it's another hoop for a hacker to jump through.
Since you _will_ be hacked, by somebody, eventually, you
have to balance the level of security with the level of
*value* it offers. For example, disabling cgi's helps quite
a bit, from a security standpoint, but it makes users
cranky. Is that crankyness worth the security of removing
all cgi's? If your transactions are worth several thousand
a day, of course it is. Are your databases so valuable that
you don't want to allow users write access? Don't put
them on writable media. Do you need to guarantee that
only specific users have access to specific transactions
in a specific database? Then cut the cord on internet access,
entirely.Back to the original question:
issue: how to secure cgi's that access postgres
For *absolutely secure* environments, remove the cgi's, erase
the database, take the box in back, and burn it, then crush it
into little bits, then sprinkle the ashes over no less than a
300 km radius, in non-equal distribution. ;-)For slightly less security, you should follow two basic, old-school
rules: Something you know, and something you have. For each level
of security, you should require at least one measure of each. For
vanilla access, they would *know* where the server is, and *have* one
pasword. More secure: They would *know* a non-htttp-linked, non-listed,
CGI file to access, and *have* a specfic IP they are coming from. Next,
maybe knowing another access code, and having a stored cookie
which is checked against... and on, and on, so by the time they have
a high level of access, they've managed to use passwords, RSA keys,
their IP, a fingerprint, an eyeball scan..... but it's still not quite as
secure as destroying all of the data. :-) Up to that point, it's just
a matter of making enough hoops to jump through that it keeps
most of your threat assesments at bay, and using them in an
_additive_ fashion.Additive? Well, a retinal scan and password combo is only
two hoops, but a retinal scan, and password, and RSA key, and
initiating IP, and facial scan, and tunneled session, is 6 things
a cracker has to break, in the right order, in order to get in. So
you have to balance how much security you need versus how many
hoops you want to make, as setting up 20+ boxes, in series, to
use a SELECT statement, may be a bit absurd for your application.
:-) I've done it for giggles, performance was not the best, but
it worked well enough that it was pretty much impenetrable
from over the internet. Only took two or three minutes to pass
a select staement through, and half an hour or so to return
a few rows... <g>
I'd say these are good examples of security through absurdity,
interesting to research, but as implied from the responce times
of this system: not usable.
sorry, :)
--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
From bouncefilter Sun Jan 16 20:03:50 2000
Received: from Viola.Opus1.COM (IDENT:unIDENTified@Viola.Opus1.COM
[192.245.12.8]) by hub.org (8.9.3/8.9.3) with ESMTP id UAA98171;
Sun, 16 Jan 2000 20:03:13 -0500 (EST) (envelope-from ron@Opus1.COM)
Received: from opus1.com ([204.27.149.77]) by Opus1.COM (PMDF V5.2-32 #9830)
with ESMTPS id <01JKS533XSYO9C28HS@Opus1.COM>;
Sun, 16 Jan 2000 18:03:10 MST
Date: Sun, 16 Jan 2000 18:03:14 -0700
From: Ron Chmara <ron@Opus1.COM>
Subject: Re: [GENERAL] cgi with postgres
To: Alfred Perlstein <bright@wintelcom.net>
Cc: Peter Eisentraut <peter_e@gmx.net>, Jeff MacDonald <jeff@hub.org>,
pgsql-general@hub.org
Reply-to: ron@Opus1.COM
Message-id: <38826A4B.8AA18BCE@opus1.com>
Organization: Ronin
MIME-version: 1.0
X-Mailer: Mozilla 4.7 (Macintosh; U; PPC)
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7bit
X-Accept-Language: en
References: <Pine.LNX.4.21.0001152014020.386-100000@localhost.localdomain>
<388255E6.F5E5324D@opus1.com> <20000116163131.K508@fw.wintelcom.net>
Alfred Perlstein wrote:
* Ron Chmara <ron@Opus1.COM> [000116 16:18] wrote:
Snip_> Of security items.....
All these options don't take into account that perhaps you don't
want people _on the same box_
Well, I assumed that web clients, using cgi, was the "Subject:",
so I didn't adrdress it. :-)
to be able to even read arbitrary
things from your database. Sure you can protect the tables from
modification via postgresql's internal ACL system, however what
if you wish to deny read access as well?
For this you need a file with an ACL prevents other _local users_
from reading it.
Hm. If they cannot read the file, how do they read it for access? :-)
If they _can_ read a component for access, you already have an
item to be eploited, again, it's about finding _balance_ in one's
access needs. The only secure server is a non-existant one.
This is _not_ security through obscurity, this is basic ACL control.
Which is insecure to attacks from anything that uses it. :-)
Again, figure a way to 'protect' your shadow password database
without relying on an external auth server.
Completely protected? On a single server? Can't be done. Relatively
protected? Sure. But it's not "secure", it's "relatively secure".
Snip_>
. For
vanilla access, they would *know* where the server is, and *have* one
pasword....
So
you have to balance how much security you need versus how many
hoops you want to make, as setting up 20+ boxes, in series, to
use a SELECT statement, may be a bit absurd for your application.
I'd say these are good examples of security through absurdity,
interesting to research, but as implied from the responce times
of this system: not usable.
sorry, :)
Perhaps you missed the point behind starting with something
extremely insecure (such as a single ACL, as you propose, and I started
with something as inherently insucre as that) , and scaling up to
absurdly slow, hard to break, but still insecure: There's no such
thing as a secure server. So you have to find balance, knowing that
whatever you do is insecure in *some* way, and relatively secure
in other ways.
-Ronabop
From bouncefilter Sun Jan 16 20:29:51 2000
Received: from pony.ip-solutions.net (c829653-a.sttln1.wa.home.com
[24.10.39.79]) by hub.org (8.9.3/8.9.3) with ESMTP id UAA01568
for <pgsql-general@postgreSQL.org>;
Sun, 16 Jan 2000 20:28:55 -0500 (EST)
(envelope-from moebius@ip-solutions.net)
From: moebius@ip-solutions.net
Received: from localhost (moebius@localhost)
by pony.ip-solutions.net (8.9.3/8.8.7) with ESMTP id RAA01631;
Sun, 16 Jan 2000 17:34:17 -0800
Date: Sun, 16 Jan 2000 17:34:17 -0800 (PST)
To: Lamar Owen <lamar.owen@wgcr.org>
cc: Il Paolone <bigpaul@flashnet.it>, Il Paolone <bigpaul@flashnet.it>,
pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] Debian php3+postgresql unable to connect
In-Reply-To: <00011616090200.00555@lorc.wgcr.org>
Message-ID: <Pine.LNX.4.10.10001161729340.1621-100000@pony.ip-solutions.net>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Hey All,
It's the pg_Connect string that's giving you problems. Make sure that
the syntax is pg_connect("host","port","database"). Also b/c it is most
likely that user nobody is the owner of the webserver you will need to
grant INSERT UPDATE and DELETE permission to nobody.
HTH,
Harry Hoffman
Product Systems Specialist
Restaurants Unlimited Inc.
Seattle WA
206 634-3082 ext. 270
On Sun, 16 Jan 2000, Lamar Owen wrote:
On Sun, 16 Jan 2000, Il Paolone wrote:
Unable to connect to PostgresSQL server: connectDB() failed: Is the
postmaster running and accepting TCP/IP(with -i) connections at
'bigpaul' on port '5432'? in /var/www/ciao.php3 on line 34Is postmaster being started with the '-i' switch??
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11************
From bouncefilter Sun Jan 16 22:47:53 2000
Received: from rage.hub.org (root@nat193.192.mpoweredpc.net [142.177.193.192])
by hub.org (8.9.3/8.9.3) with ESMTP id WAA31929;
Sun, 16 Jan 2000 22:46:52 -0500 (EST) (envelope-from jeffm@pgsql.com)
Received: from localhost (jeffm@localhost)
by rage.hub.org (8.9.3/8.9.3) with ESMTP id XAA16475;
Sun, 16 Jan 2000 23:47:08 -0400 (AST) (envelope-from jeffm@pgsql.com)
X-Authentication-Warning: rage.hub.org: jeffm owned process doing -bs
Date: Sun, 16 Jan 2000 23:47:08 -0400 (AST)
From: "Jeff MacDonald <jeff@pgsql.com>" <jeffm@pgsql.com>
X-Sender: jeffm@rage.hub.org
Reply-To: Jeff MacDonald <jeff@pgsql.com>
cc: Alfred Perlstein <bright@wintelcom.net>,
Peter Eisentraut <peter_e@gmx.net>, Jeff MacDonald <jeff@hub.org>,
pgsql-general@hub.org
Subject: Re: [GENERAL] cgi with postgres
In-Reply-To: <38826A4B.8AA18BCE@opus1.com>
Message-ID: <Pine.BSF.4.10.10001162346500.435-100000@rage.hub.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
ok.
lets get back to the point, i'm not worried about webusers,
so .htaccess is not an issue.
this all arouse when one of hub's clients who runs a course
evaluation system asked
'what's to stop someone from logging in to hub, writing a script
with my name in user=user in the dbi::connect function. then
making it enter a bunch of bogus data.'
at first i thought gee well just put a passwd on the database,
then i thought, gee that's stupid since it's stored in plain text.
======================================================
Jeff MacDonald
jeff@pgsql.com irc: bignose on EFnet
======================================================
From bouncefilter Sun Jan 16 23:55:52 2000
Received: from co832821-a (cogeco-91-40.cgocable.net [24.141.91.40])
by hub.org (8.9.3/8.9.3) with ESMTP id XAA51080
for <pgsql-general@hub.org>; Sun, 16 Jan 2000 23:55:29 -0500 (EST)
(envelope-from reinke@e-softinc.com)
Received: from e-softinc.com ([10.1.1.4])
by co832821-a (8.8.7/8.8.7) with ESMTP id XAA21159;
Sun, 16 Jan 2000 23:54:47 -0500
Message-ID: <3882A127.F532A000@e-softinc.com>
Date: Sun, 16 Jan 2000 23:57:11 -0500
From: Thomas Reinke <reinke@e-softinc.com>
Organization: E-Soft Inc.
X-Mailer: Mozilla 4.5 [en] (WinNT; U)
X-Accept-Language: en
MIME-Version: 1.0
To: John Henderson <jrh@is.com.fj>
CC: pgsql-general@hub.org
Subject: Re: [GENERAL] cursors
References: <00ad01bf60a7$72e9e7c0$ea7c3eca@john.is.com.fj>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Not that I believe this might cause the problem, but isn't
the syntax for fetch
"fetch 1000 in cursor_name" instead of "from cursor_name"?
We've been using this syntax for quite some time on
a system that has way more data than would be successfully
held in memory.
Also, we've found in the past that making sure the database
is vacuumed on a somewhat regular basis helps with memory
problems.
Cheers, Thomas
John Henderson wrote:
Hi,
I am using postgres 6.4 on bsd/os3.0
The select that I want to use:
$select="select user_name,start,stop,nas_ip,port,
date_part('epoch',sess_time)
from $sessions_table s, userdir u
where user_name=username
and services in $in_phrase";
runs out of memory.
So I cleverly broke it down using cursors....
$result = $conn->exec("BEGIN");
$result = $conn->exec("declare cursor1 cursor for $select");
$result = $conn->exec("fetch 1000 from cursor1");NOT SO CLEVER says the log
query: fetch 1000 from cursor1
ProcessUtility: fetch 1000 from cursor1
FATAL 1: palloc failure: memory exhausted
proc_exit(0) [#0]
shmem_exit(0) [#0]
exit(0)Questions:
So, correct me if I'm wrong but it looks like cursors do not actually save
any memory and I have to use SELECT ... LIMIT ... OFFSET for this and to do
that I have to upgrade from postgresql6.4Or is there another way?
And, it seems to me that there is a problem with pg_dump from 6.4 and reload
the data into 6.5.3 because of some sort of structural changes. Where is the
documentation that will safely take me from 6.4 to 6.5.3?Thanks,
John************
--
------------------------------------------------------------
Thomas Reinke Tel: (905) 331-2260
Director of Technology Fax: (905) 331-2504
E-Soft Inc. http://www.e-softinc.com
From bouncefilter Sun Jan 16 23:02:52 2000
Received: from homer.is.com.fj (homer.is.com.fj [202.62.124.238])
by hub.org (8.9.3/8.9.3) with ESMTP id XAA37342
for <pgsql-general@hub.org>; Sun, 16 Jan 2000 23:02:46 -0500 (EST)
(envelope-from jrh@is.com.fj)
Received: from john (test2.is.com.fj [202.62.124.234])
by homer.is.com.fj (8.9.3/8.9.3) with SMTP id RAA10314
for <pgsql-general@hub.org>; Mon, 17 Jan 2000 17:02:36 +1300 (FJDST)
Message-ID: <00ad01bf60a7$72e9e7c0$ea7c3eca@john.is.com.fj>
Reply-To: "John Henderson" <jrh@is.com.fj>
From: "John Henderson" <jrh@is.com.fj>
To: <pgsql-general@hub.org>
Subject: cursors
Date: Mon, 17 Jan 2000 16:58:08 +1200
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 4.72.3110.1
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Hi,
I am using postgres 6.4 on bsd/os3.0
The select that I want to use:
$select="select user_name,start,stop,nas_ip,port,
date_part('epoch',sess_time)
from $sessions_table s, userdir u
where user_name=username
and services in $in_phrase";
runs out of memory.
So I cleverly broke it down using cursors....
$result = $conn->exec("BEGIN");
$result = $conn->exec("declare cursor1 cursor for $select");
$result = $conn->exec("fetch 1000 from cursor1");
NOT SO CLEVER says the log
query: fetch 1000 from cursor1
ProcessUtility: fetch 1000 from cursor1
FATAL 1: palloc failure: memory exhausted
proc_exit(0) [#0]
shmem_exit(0) [#0]
exit(0)
Questions:
So, correct me if I'm wrong but it looks like cursors do not actually save
any memory and I have to use SELECT ... LIMIT ... OFFSET for this and to do
that I have to upgrade from postgresql6.4
Or is there another way?
And, it seems to me that there is a problem with pg_dump from 6.4 and reload
the data into 6.5.3 because of some sort of structural changes. Where is the
documentation that will safely take me from 6.4 to 6.5.3?
Thanks,
John
From bouncefilter Mon Jan 17 06:01:56 2000
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 GAA64704
for <pgsql-general@postgreSQL.org>;
Mon, 17 Jan 2000 06:01:33 -0500 (EST)
(envelope-from holger@klawitter.de)
Received: from klawitter.de (holger@mueata-e1-wan046.citykom.de
[195.202.34.46])
by bbaer.muenster.de (8.9.3/8.9.3) with ESMTP id LAA22645
for <pgsql-general@postgreSQL.org>; Mon, 17 Jan 2000 11:56:37 +0100
Sender: holger@muenster.de
Message-ID: <3882D6EF.8991DE11@klawitter.de>
Date: Mon, 17 Jan 2000 09:46:39 +0100
From: Holger Klawitter <holger@klawitter.de>
X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.13 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: "pgsql-general@postgreSQL.org" <pgsql-general@postgreSQL.org>
Subject: Re: [GENERAL] Error connecting database
References: <4.2.0.58.20000116183519.00a80dd0@mailer.isn.net>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Also, you might want to check whether you have started the server with
the "-i" option (that's what the Unix Socket message suggests).
Regards,
Holger Klawitter
--
Holger Klawitter +49 (0)251 484 0637
holger@klawitter.de http://www.klawitter.de/
From bouncefilter Mon Jan 17 03:47:56 2000
Received: from Viola.Opus1.COM (IDENT:unIDENTified@Viola.Opus1.COM
[192.245.12.8]) by hub.org (8.9.3/8.9.3) with ESMTP id DAA30807;
Mon, 17 Jan 2000 03:47:17 -0500 (EST) (envelope-from ron@Opus1.COM)
Received: from opus1.com ([204.27.149.74]) by Opus1.COM (PMDF V5.2-32 #9830)
with ESMTPS id <01JKSLAI1OB69C2D4N@Opus1.COM>;
Mon, 17 Jan 2000 01:47:16 MST
Date: Mon, 17 Jan 2000 01:47:00 -0700
From: Ron Chmara <ron@Opus1.COM>
Subject: Re: [GENERAL] cgi with postgres
To: Jeff MacDonald <jeff@pgsql.com>
Cc: Alfred Perlstein <bright@wintelcom.net>,
Peter Eisentraut <peter_e@gmx.net>, Jeff MacDonald <jeff@hub.org>,
pgsql-general@hub.org
Reply-to: ron@Opus1.COM
Message-id: <3882D70C.FA61E59@opus1.com>
Organization: Ronin
MIME-version: 1.0
X-Mailer: Mozilla 4.7 (Macintosh; U; PPC)
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7bit
X-Accept-Language: en
References: <Pine.BSF.4.10.10001162346500.435-100000@rage.hub.org>
"Jeff MacDonald " wrote:
'what's to stop someone from logging in to hub, writing a script
with my name in user=user in the dbi::connect function. then
making it enter a bunch of bogus data.'
at first i thought gee well just put a passwd on the database,
then i thought, gee that's stupid since it's stored in plain text.
Depends on how you're set up. You can use host based
authentification, so only valid machines can make changes.
You can shut out shell accounts on the machine, and then
use the host's username/pass function for connection. You
can use postgres crypt'ed passwords, which doesn't store passwords
at all, it stores a block of zeros encrypted using the password....
at least on the machine itself, not in the CGI's...
(Yes, I'm rambling about other issues, but I'll get there...)
If you're only requiring one username, one password, for a file
everybody has access to, well, yes, that's a problem. Which is why
password files usually have permissions on them, so :
-rw------- 1 postgres demigods 8192 Jan 7 11:55 pg_shadow
can only be read/written by user postgres, group demigods.
See:
http://www.postgresql.org/doxlist.html for a rough overview
of how this is supposed to work..
(Still rambling, not about scripted, stored, user/pass access yet)
Yes, yes, you can have connections that are username only. You
can have username and password, in a cgi, which is why you
*don't use real, powerful, usernames and passwords in
scripts*. You use things like www or nobody, which write to
temporary tables. Which are then inspected for accuracy,
or validated, or verified. If your question is framed as
"when I give others access into the bank, how do I know
they won't steal money?", the response is "by not
letting them near the real money". You set up a user for
the db that has the _bare_ minimum, and you never, ever,
use a cgi. or a script, that allows more rights to be
granted.
Put another way: It's a bad move to have suid root programs
available to the regular users for the same reason...
A better metaphor might be: You don't leave your housekeys
at the front door for the mailman to deliver the mail. You
may have a locked mailbox, and when you want to move
the mail, you unlock the box, and _then_ move it. The
user doesn't have privleges to dump bad data into the
secure tables, only insecure tables.
HTH,
-Bop
From bouncefilter Mon Jan 17 05:25:58 2000
Received: from ns.prov-liege.be (ns.prov-liege.be [193.190.122.12])
by hub.org (8.9.3/8.9.3) with ESMTP id FAA54572
for <pgsql-general@hub.org>; Mon, 17 Jan 2000 05:25:08 -0500 (EST)
(envelope-from Fabian.Frederick@prov-liege.be)
From: Fabian.Frederick@prov-liege.be
Received: by ns.prov-liege.be; (8.8.8/1.3/10May95) id LAA14168;
Mon, 17 Jan 2000 11:18:33 +0100 (GMT+0100)
Received: by mesepl.epl.prov-liege.be with Internet Mail Service (5.5.2650.21)
id <Z0BK5X8L>; Mon, 17 Jan 2000 11:22:39 +0100
Message-ID: <17AB709C82E5D111ACF20000F805F4532B44B1@mesadm.epl.prov-liege.be>
To: pgsql-general@hub.org
Subject: System tables
Date: Mon, 17 Jan 2000 11:22:38 +0100
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2650.21)
Content-Type: text/plain;
charset="iso-8859-1"
Hi !
I've got trivial question : what are system table names ?
Do system tables store access statistics ?
Regards, Fabian
From bouncefilter Mon Jan 17 06:20:57 2000
Received: from meryl.it.uu.se (root@meryl.it.uu.se [130.238.12.42])
by hub.org (8.9.3/8.9.3) with ESMTP id GAA67217
for <pgsql-general@hub.org>; Mon, 17 Jan 2000 06:20:12 -0500 (EST)
(envelope-from e99re41@DoCS.UU.SE)
Received: from Puma.DoCS.UU.SE (e99re41@Puma.DoCS.UU.SE [130.238.9.112])
by meryl.it.uu.se (8.8.5/8.8.5) with ESMTP id MAA01431;
Mon, 17 Jan 2000 12:20:08 +0100 (MET)
Received: from localhost (e99re41@localhost) by Puma.DoCS.UU.SE
(8.6.12/8.6.12) with SMTP id MAA08635;
Mon, 17 Jan 2000 12:20:03 +0100
X-Authentication-Warning: Puma.DoCS.UU.SE: e99re41 owned process doing -bs
Date: Mon, 17 Jan 2000 12:20:03 +0100 (MET)
From: Peter Eisentraut <e99re41@DoCS.UU.SE>
Reply-To: Peter Eisentraut <peter_e@gmx.net>
To: Fabian.Frederick@prov-liege.be
cc: pgsql-general@hub.org
Subject: Re: [GENERAL] System tables
In-Reply-To: <17AB709C82E5D111ACF20000F805F4532B44B1@mesadm.epl.prov-liege.be>
Message-ID: <Pine.GSO.4.02A.10001171217560.8593-100000@Puma.DoCS.UU.SE>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
System tables are all tables starting with pg_. They store everything
about your database installation, such as name of the actual databases,
tables, attribute types, rules, indexes, what-have-you. Access statistics
as in "How many times did table x get accessed since then" are not stored
anywhere, at least not where you could make use of it.
On Mon, 17 Jan 2000 Fabian.Frederick@prov-liege.be wrote:
Hi !
I've got trivial question : what are system table names ?
Do system tables store access statistics ?Regards, Fabian
************
--
Peter Eisentraut Sernanders vaeg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
From bouncefilter Mon Jan 17 06:36:57 2000
Received: from oxmail.ox.ac.uk (oxmail3.ox.ac.uk [163.1.2.9])
by hub.org (8.9.3/8.9.3) with ESMTP id GAA72362
for <pgsql-general@postgresql.org>;
Mon, 17 Jan 2000 06:35:59 -0500 (EST)
(envelope-from moray.mcconnachie@computing-services.oxford.ac.uk)
Received: from ermine.ox.ac.uk ([163.1.2.13])
by oxmail.ox.ac.uk with esmtp (Exim 2.10 #1)
id 12AARP-0004Vr-00; Mon, 17 Jan 2000 11:35:23 +0000
Received: from ermine.ox.ac.uk ([163.1.2.13] helo=moraypc ident=root)
by ermine.ox.ac.uk with smtp (Exim 3.12 #1)
id 12AARO-0004KD-00; Mon, 17 Jan 2000 11:35:22 +0000
Message-ID: <01ee01bf60de$f05546f0$760e01a3@oucs.ox.ac.uk>
From: "Moray McConnachie" <moray.mcconnachie@computing-services.oxford.ac.uk>
To: "Peter Eisentraut" <peter_e@gmx.net>, "yura" <yura@vpcit.ru>
Cc: "pgsql-general" <pgsql-general@postgresql.org>
References: <Pine.LNX.4.21.0001151946210.386-100000@localhost.localdomain>
Subject: Re: Re[2]Have services been enabled for port 5432? Check /etc/services. I have a pair of lines: postgres 5432/tcp # POSTGRES postgres 5432/udp # POSTGRES: [GENERAL] case-insensitive like operator
Date: Mon, 17 Jan 2000 11:35:21 -0000
Organization: Oxford University
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 8bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Best way to handle this IMHO is to write a routine that uses Access
direct ODBC stuff to handle queries that must be case insensitive -
either that or use LCase(fieldname) & LCase(searchvalue) in your
Access queries, but this will be slower.
Here's my routine in VB for the former:
Public Function SQLPass(SQL As String, Optional DontLog As Variant)
Dim wrkODBC As Workspace
Dim conSearch As Connection
Dim qdfTemp As QueryDef
Dim connectstring As String, success As Byte, errornumber As Long
If IsMissing(DontLog) Then DontLog = False
DontLog = True
connectstring = "PUT YOUR CONNECT STRING HERE"
Set wrkODBC = CreateWorkspace("", "moray", "", dbUseODBC)
Set conSearch = wrkODBC.OpenConnection("Search", , ,
connectstring)
Set qdfTemp = conSearch.CreateQueryDef("")
With qdfTemp
.Prepare = dbQUnprepare
.SQL = SQL
success = 1
On Error GoTo SQLerror
.Execute
End With
If Not (DontLog) And (Nz(InStr(SQL, "UPDATE"), 0) > 0 Or
Nz(InStr(SQL, "CREATE"), 0) > 0 Or Nz(InStr(SQL, "INSERT"), 0) > 0 Or
Nz(InStr(SQL, "DELETE"), 0) > 0) Then
LogSQL SQL, success ' log if it is an update or delete or
create or insert query...
End If
conSearch.Close
wrkODBC.Close
Exit Function
SQLerror:
errornumber = Err.number
' Debug.Print errornumber
On Error GoTo 0
If errornumber = 3146 Then
success = 0
MsgBox ("ERROR trying to do " & SQL & " in server database.")
Resume Next
Else
success = 0
If Not (DontLog) = True Then LogSQL SQL, success
Err.Clear
Err.Raise (errornumber)
End If
End Function
----------------------------------------------------------------------
----------------
Moray.McConnachie@computing-services.oxford.ac.uk
----- Original Message -----
From: Peter Eisentraut <peter_e@gmx.net>
To: yura <yura@vpcit.ru>
Cc: pgsql-general <pgsql-general@postgreSQL.org>
Sent: Sunday, January 16, 2000 5:13 PM
Subject: Re: Re[2]Have services been enabled for port 5432? Check /etc/services. I have a pair of lines: postgres 5432/tcp # POSTGRES postgres 5432/udp # POSTGRES: [GENERAL] case-insensitive like operator
On 2000-01-15, yura mentioned:
Hello Adriaan,
Wednesday, January 12, 2000, 4:24:53 PM, you wrote:
AJ> Postgres has a load of text search operators. The most powerful
is ~*
AJ> whcih gives you a case insensitive regular expression search.
AJ> Adriaan
Thank you for reply, but i can't use ~* operator, because i don't
create queries, MS Access generates queries and it creates queries
with 'like' operator.
The way I read the SQL standard, LIKE matches are always
case-sensitive,
so Access would be in violation.
--
Peter Eisentraut Sernanders v���g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
************
From bouncefilter Mon Jan 17 08:55:58 2000
Received: from nefertiti.pasteur.fr (nefertiti.pasteur.fr [157.99.64.20])
by hub.org (8.9.3/8.9.3) with ESMTP id IAA99777;
Mon, 17 Jan 2000 08:55:23 -0500 (EST)
(envelope-from bortz@pasteur.fr)
Received: from ezili.sis.pasteur.fr (ezili.sis.pasteur.fr [157.99.60.56])
by nefertiti.pasteur.fr (8.10.0.Beta10/8.10.0.Beta10) with ESMTP id
e0HDtJT09646; Mon, 17 Jan 2000 14:55:20 +0100 (MET)
Received: from pasteur.fr (localhost [127.0.0.1])
by ezili.sis.pasteur.fr (8.9.3/8.9.3/Debian 8.9.3-6) with ESMTP id
OAA27261; Mon, 17 Jan 2000 14:55:19 +0100
Message-Id: <200001171355.OAA27261@ezili.sis.pasteur.fr>
X-Authentication-Warning: ezili.sis.pasteur.fr: Host localhost [127.0.0.1]
claimed to be pasteur.fr
X-Mailer: exmh version 2.1.1 10/15/1999 (debian)
From: Stephane Bortzmeyer <bortzmeyer@pasteur.fr>
To: Jeff MacDonald <jeff@hub.org>
cc: pgsql-general@hub.org
Subject: Re: [GENERAL] cgi with postgres
In-reply-to: <Pine.BSF.4.10.10001141651160.428-100000@rage.hub.org>
(Jeff MacDonald <jeff@hub.org>'s message of
Fri, 14 Jan 2000 16:55:02 -0400)
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Date: Mon, 17 Jan 2000 14:55:18 +0100
Sender: bortz@pasteur.fr
On Friday 14 January 2000, at 16 h 55, the keyboard of Jeff MacDonald
<jeff@hub.org> wrote:
make cgi dir 711
big deal, they can get the name of the file
from the web, and copy it.
My CGIs sources a config file, in mode 700, only readable by 'www' (the user
which executes Apache).
I adopted this after a CGI was inadvenrdently made available on the Web, in
source form :-)
If you have CGI authors you do not trust, have two Apache, with different IDs.
this is assuming they already have an account
on the machine, something that cannot be ruled
out.
Unix file protections are enough against local users.
From bouncefilter Mon Jan 17 10:07:59 2000
Received: from [12.6.225.3] ([12.6.225.3])
by hub.org (8.9.3/8.9.3) with SMTP id KAA22508
for <pgsql-general@postgreSQL.org>;
Mon, 17 Jan 2000 10:07:35 -0500 (EST)
(envelope-from rdavis@lillysoftware.com)
Received: from lillysoftware.com by [12.6.225.3]
via smtpd (for postgresql.org [216.126.84.28]) with SMTP;
17 Jan 2000 15:06:13 UT
Received: from lillysoftware.com (BOBIBM333 [173.100.2.237]) by
ponyexpress.LillySoftware.com with SMTP (Microsoft Exchange
Internet Mail Service Version 5.5.2448.0)
id CQG7MWYZ; Mon, 17 Jan 2000 10:05:36 -0500
Message-ID: <38833030.F6B9BA1A@lillysoftware.com>
Date: Mon, 17 Jan 2000 10:07:29 -0500
From: Robert Davis <rdavis@lillysoftware.com>
X-Mailer: Mozilla 4.61 [en] (Win98; U)
X-Accept-Language: en
MIME-Version: 1.0
To: Bruce Bantos <anon@mgfairfax.rr.com>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How do you live without OUTER joins?
References: <028f01bf5c95$0eb15b50$0200a8c0@rsdevelop>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
select * from company comp, company_category cat
where comp.com_cat_abbr = cat.com_cat_abbr
and comp.com_cat_abbr not in (select com_cat_addr from company_category);
If sub selects work as advertised in postgresql
this is the same as the oracle syntax:
select * from company comp, company_category cat
where comp.com_cat_abbr = cat.com_cat_abbr(+);
bob
Bruce Bantos wrote:
I know that this may seem like a stale topic. I am not complaining about
outer joins not being available yet in PostgreSQL. I just want to know how
you live without them. I am migrating a production system to PostgreSQL and
I do not know how to duplicate the functionality.For example, how can I live without outer joins in the example below:
In my current Oracle DB, I have a number of "lookup" tables that contain
something like this:TABLE company_category:
com_cat_abbr | com_cat_long
--------------------------------------------------
SB | Small Business
LB | Large Business
NP | Not for Profitetc.
Then in my main table, lets say the "company" table I have:
company_name | com_cat_abbr
------------------------------------------------------------
Microsoft | LB
United Way | NP
Coca Cola | NULLIf I allow nulls in my com_cat_abbr column above, then how could I do a
simple query to show the company table with the full com_cat_long
description? These alternatives do not appear attractive:- Don't allow nulls and force a default value in the com_cat_abbr column
- Don't do the query - if you want to display it that way handle it in the
client
- get rid of the lookup table and store the full text in the company tableI like to have the lookup tables because I use them in the front end client
to populate pulldowns, they save storage space, they allow some limited
flexibility in changing the definition for the abbreviation, and they allow
administrators to be able to see the abbreviation and understand what they
are looking at. When referential integrity becomes available, I will use
these lookup tables to enforce integrity.What are my alternatives? What is everyone else doing in their Postgres
system? Thanks.- B
************
--
rdavis@lillysoftware.com
rsdavis@mediaone.net
http://people.ne.mediaone.net/rsdavis
From bouncefilter Mon Jan 17 10:18:59 2000
Received: from rage.hub.org (root@nat193.192.mpoweredpc.net [142.177.193.192])
by hub.org (8.9.3/8.9.3) with ESMTP id KAA24113
for <pgsql-general@hub.org>; Mon, 17 Jan 2000 10:18:26 -0500 (EST)
(envelope-from jeff@hub.org)
Received: from localhost (jeff@localhost)
by rage.hub.org (8.9.3/8.9.3) with ESMTP id LAA17794;
Mon, 17 Jan 2000 11:18:08 -0400 (AST) (envelope-from jeff@hub.org)
X-Authentication-Warning: rage.hub.org: jeff owned process doing -bs
Date: Mon, 17 Jan 2000 11:18:07 -0400 (AST)
From: Jeff MacDonald <jeff@hub.org>
Reply-To: Jeff MacDonald <jeff@hub.org>
To: Stephane Bortzmeyer <bortzmeyer@pasteur.fr>
cc: pgsql-general@hub.org
Subject: Re: [GENERAL] cgi with postgres
In-Reply-To: <200001171355.OAA27261@ezili.sis.pasteur.fr>
Message-ID: <Pine.BSF.4.10.10001171117200.428-100000@rage.hub.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
My CGIs sources a config file, in mode 700, only readable by 'www' (the user
which executes Apache).
this option works, but not well if the user isn't root.
Jeff MacDonald
jeff@hub.org
===================================================================
So long as the Universe had a beginning, we can suppose it had a
creator, but if the Universe is completly self contained , having
no boundry or edge, it would neither be created nor destroyed
It would simply be.
===================================================================
From bouncefilter Mon Jan 17 10:39:59 2000
Received: from [12.6.225.3] ([12.6.225.3])
by hub.org (8.9.3/8.9.3) with SMTP id KAA29968
for <pgsql-general@postgresql.org>;
Mon, 17 Jan 2000 10:39:26 -0500 (EST)
(envelope-from rdavis@lillysoftware.com)
Received: from lillysoftware.com by [12.6.225.3]
via smtpd (for postgresql.org [216.126.84.28]) with SMTP;
17 Jan 2000 15:38:04 UT
Received: from lillysoftware.com (BOBIBM333 [173.100.2.237]) by
ponyexpress.LillySoftware.com with SMTP (Microsoft Exchange
Internet Mail Service Version 5.5.2448.0)
id CQG7MW78; Mon, 17 Jan 2000 10:37:31 -0500
Message-ID: <388337AB.9E3B84D1@lillysoftware.com>
Date: Mon, 17 Jan 2000 10:39:24 -0500
From: Robert Davis <rdavis@lillysoftware.com>
X-Mailer: Mozilla 4.61 [en] (Win98; U)
X-Accept-Language: en
MIME-Version: 1.0
To: Bruce Bantos <anon@mgfairfax.rr.com>, pgsql-general@postgresql.org
Subject: Re: [GENERAL] How do you live without OUTER joins?
References: <028f01bf5c95$0eb15b50$0200a8c0@rsdevelop>
<38833030.F6B9BA1A@lillysoftware.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Whoops. The "and" should be an "or".
select * from company comp, company_category cat
where comp.com_cat_abbr = cat.com_cat_abbr
or comp.com_cat_abbr not in (select com_cat_addr from company_category);
I read the other posts on a different named thread and they seem to indicate a "union" is necessary.
I am not sure why though?
Robert Davis wrote:
select * from company comp, company_category cat
where comp.com_cat_abbr = cat.com_cat_abbr
and comp.com_cat_abbr not in (select com_cat_addr from company_category);If sub selects work as advertised in postgresql
this is the same as the oracle syntax:select * from company comp, company_category cat
where comp.com_cat_abbr = cat.com_cat_abbr(+);bob
Bruce Bantos wrote:
I know that this may seem like a stale topic. I am not complaining about
outer joins not being available yet in PostgreSQL. I just want to know how
you live without them. I am migrating a production system to PostgreSQL and
I do not know how to duplicate the functionality.For example, how can I live without outer joins in the example below:
In my current Oracle DB, I have a number of "lookup" tables that contain
something like this:TABLE company_category:
com_cat_abbr | com_cat_long
--------------------------------------------------
SB | Small Business
LB | Large Business
NP | Not for Profitetc.
Then in my main table, lets say the "company" table I have:
company_name | com_cat_abbr
------------------------------------------------------------
Microsoft | LB
United Way | NP
Coca Cola | NULLIf I allow nulls in my com_cat_abbr column above, then how could I do a
simple query to show the company table with the full com_cat_long
description? These alternatives do not appear attractive:- Don't allow nulls and force a default value in the com_cat_abbr column
- Don't do the query - if you want to display it that way handle it in the
client
- get rid of the lookup table and store the full text in the company tableI like to have the lookup tables because I use them in the front end client
to populate pulldowns, they save storage space, they allow some limited
flexibility in changing the definition for the abbreviation, and they allow
administrators to be able to see the abbreviation and understand what they
are looking at. When referential integrity becomes available, I will use
these lookup tables to enforce integrity.What are my alternatives? What is everyone else doing in their Postgres
system? Thanks.- B
************
--
rdavis@lillysoftware.com
rsdavis@mediaone.net
http://people.ne.mediaone.net/rsdavis************
--
rdavis@lillysoftware.com
rsdavis@mediaone.net
http://people.ne.mediaone.net/rsdavis
From bouncefilter Mon Jan 17 10:56:19 2000
Received: from dc1.digitalcyclone.com ([204.73.221.140])
by hub.org (8.9.3/8.9.3) with ESMTP id KAA32365
for <pgsql-general@postgresql.org>;
Mon, 17 Jan 2000 10:55:59 -0500 (EST)
(envelope-from davidl@digitalcyclone.com)
Received: from digitalcyclone.com (dcpc3.digitalcyclone.com [204.73.221.103])
by dc1.digitalcyclone.com (980427.SGI.8.8.8/970903.SGI.AUTOCF)
via ESMTP id JAA28307 for <pgsql-general@postgresql.org>;
Mon, 17 Jan 2000 09:51:41 -0600 (CST)
Sender: davidl@dc1.digitalcyclone.com
Message-ID: <38833BF9.72E92B06@digitalcyclone.com>
Date: Mon, 17 Jan 2000 09:57:45 -0600
From: David Larson <davidl@digitalcyclone.com>
Organization: Digital Cyclone
X-Mailer: Mozilla 4.61 [en] (X11; I; Linux 2.2.12-20smpDL i686)
X-Accept-Language: en
MIME-Version: 1.0
To: pgsql-general@postgresql.org
Subject: After enabling the "no-flush" option, can I force a flush adhoc?
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
For performance reasons, everyone is pushing the "no-flush"
option, and I can see why -- it is incredibly fast in comparison
to running without it.
However, I would feel more comfortable if I had an option to
force a flush. There are times during the day when there is
larger amounts of activity, but during the quieter times, I would
like to be able to revert back to the "flush" mode -- because
of the data loss threat.
Is there an API that I can invoke when I deem appropriate?
I would like to be able to either force a flush to occur right
now, or, preferably, to be able to switch operating modes
("flush" or "no-flush") on demand.
From bouncefilter Mon Jan 17 11:03:01 2000
Received: from nefertiti.pasteur.fr (nefertiti.pasteur.fr [157.99.64.20])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA36643;
Mon, 17 Jan 2000 11:02:36 -0500 (EST)
(envelope-from bortz@pasteur.fr)
Received: from ezili.sis.pasteur.fr (ezili.sis.pasteur.fr [157.99.60.56])
by nefertiti.pasteur.fr (8.10.0.Beta10/8.10.0.Beta10) with ESMTP id
e0HG2YT05357; Mon, 17 Jan 2000 17:02:34 +0100 (MET)
Received: from pasteur.fr (localhost [127.0.0.1])
by ezili.sis.pasteur.fr (8.9.3/8.9.3/Debian 8.9.3-6) with ESMTP id
RAA17771; Mon, 17 Jan 2000 17:02:34 +0100
Message-Id: <200001171602.RAA17771@ezili.sis.pasteur.fr>
X-Authentication-Warning: ezili.sis.pasteur.fr: Host localhost [127.0.0.1]
claimed to be pasteur.fr
X-Mailer: exmh version 2.1.1 10/15/1999 (debian)
From: Stephane Bortzmeyer <bortzmeyer@pasteur.fr>
To: Jeff MacDonald <jeff@hub.org>
cc: pgsql-general@hub.org
Subject: Re: [GENERAL] cgi with postgres
In-reply-to: <Pine.BSF.4.10.10001171117200.428-100000@rage.hub.org>
(Jeff MacDonald <jeff@hub.org>'s message of
Mon, 17 Jan 2000 11:18:07 -0400)
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Date: Mon, 17 Jan 2000 17:02:33 +0100
Sender: bortz@pasteur.fr
On Monday 17 January 2000, at 11 h 18, the keyboard of Jeff MacDonald
<jeff@hub.org> wrote:
My CGIs sources a config file, in mode 700, only readable by 'www' (the user
which executes Apache).this option works, but not well if the user isn't root.
Can you elaborate? Of course, it works well and of course, the actual user is not root.
From bouncefilter Mon Jan 17 11:06:00 2000
Received: from dc1.digitalcyclone.com ([204.73.221.140])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA37026
for <pgsql-general@postgresql.org>;
Mon, 17 Jan 2000 11:05:36 -0500 (EST)
(envelope-from davidl@digitalcyclone.com)
Received: from digitalcyclone.com (dcpc3.digitalcyclone.com [204.73.221.103])
by dc1.digitalcyclone.com (980427.SGI.8.8.8/970903.SGI.AUTOCF)
via ESMTP id KAA28840 for <pgsql-general@postgresql.org>;
Mon, 17 Jan 2000 10:01:17 -0600 (CST)
Sender: davidl@dc1.digitalcyclone.com
Message-ID: <38833E39.E0C9C119@digitalcyclone.com>
Date: Mon, 17 Jan 2000 10:07:21 -0600
From: David Larson <davidl@digitalcyclone.com>
Organization: Digital Cyclone
X-Mailer: Mozilla 4.61 [en] (X11; I; Linux 2.2.12-20smpDL i686)
X-Accept-Language: en
MIME-Version: 1.0
To: pgsql-general@postgresql.org
Subject: After enabling the "no-flush" option, can I force a flush adhoc?
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
For performance reasons, everyone is pushing the "no-flush"
option, and I can see why -- it is incredibly fast in comparison
to running without it.
However, I would feel more comfortable if I had an option to
force a flush. There are times during the day when there is
larger amounts of activity, but during the quieter times, I would
like to be able to revert back to the "flush" mode -- because
of the data loss threat.
Is there an API that I can invoke when I deem appropriate?
I would like to be able to either force a flush to occur right
now, or, preferably, to be able to switch operating modes
("flush" or "no-flush") on demand. Of course, I want to do
this without restarting 'postmaster'.
From bouncefilter Mon Jan 17 11:10:01 2000
Received: from [12.6.225.3] ([12.6.225.3])
by hub.org (8.9.3/8.9.3) with SMTP id LAA37626
for <pgsql-general@postgreSQL.org>;
Mon, 17 Jan 2000 11:09:50 -0500 (EST)
(envelope-from rdavis@lillysoftware.com)
Received: from lillysoftware.com by [12.6.225.3]
via smtpd (for postgresql.org [216.126.84.28]) with SMTP;
17 Jan 2000 16:08:27 UT
Received: from lillysoftware.com (BOBIBM333 [173.100.2.237]) by
ponyexpress.LillySoftware.com with SMTP (Microsoft Exchange
Internet Mail Service Version 5.5.2448.0)
id CQG7MXA0; Mon, 17 Jan 2000 11:07:55 -0500
Message-ID: <38833ECA.90FC0350@lillysoftware.com>
Date: Mon, 17 Jan 2000 11:09:47 -0500
From: Robert Davis <rdavis@lillysoftware.com>
X-Mailer: Mozilla 4.61 [en] (Win98; U)
X-Accept-Language: en
MIME-Version: 1.0
To: Bruce Bantos <anon@mgfairfax.rr.com>, pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] How do you live without OUTER joins?
References: <028f01bf5c95$0eb15b50$0200a8c0@rsdevelop>
<38833030.F6B9BA1A@lillysoftware.com>
<388337AB.9E3B84D1@lillysoftware.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Okay 3rd try:
select comp.*, cat.com_cat_abbr from company comp, company_category cat
where comp.com_cat_abbr = cat.com_cat_abbr
union all
select comp.*, null from company comp
where comp.com_cat_abbr not in (select com_cat_addr from company_category);
This is the same as bruce m.
The real problem then is there is no generic way because you are forced to add enough nulls to pad out the other table.
I read the other posts on a different named thread and they seem to indicate a "union" is necessary.
I am not sure why though?
The union is necessary because the "or" causes a cartesian product on the 2 clauses.
Robert Davis wrote:
Whoops. The "and" should be an "or".
select * from company comp, company_category cat
where comp.com_cat_abbr = cat.com_cat_abbr
or comp.com_cat_abbr not in (select com_cat_addr from company_category);I read the other posts on a different named thread and they seem to indicate a "union" is necessary.
I am not sure why though?Robert Davis wrote:
select * from company comp, company_category cat
where comp.com_cat_abbr = cat.com_cat_abbr
and comp.com_cat_abbr not in (select com_cat_addr from company_category);If sub selects work as advertised in postgresql
this is the same as the oracle syntax:select * from company comp, company_category cat
where comp.com_cat_abbr = cat.com_cat_abbr(+);bob
Bruce Bantos wrote:
I know that this may seem like a stale topic. I am not complaining about
outer joins not being available yet in PostgreSQL. I just want to know how
you live without them. I am migrating a production system to PostgreSQL and
I do not know how to duplicate the functionality.For example, how can I live without outer joins in the example below:
In my current Oracle DB, I have a number of "lookup" tables that contain
something like this:TABLE company_category:
com_cat_abbr | com_cat_long
--------------------------------------------------
SB | Small Business
LB | Large Business
NP | Not for Profitetc.
Then in my main table, lets say the "company" table I have:
company_name | com_cat_abbr
------------------------------------------------------------
Microsoft | LB
United Way | NP
Coca Cola | NULLIf I allow nulls in my com_cat_abbr column above, then how could I do a
simple query to show the company table with the full com_cat_long
description? These alternatives do not appear attractive:- Don't allow nulls and force a default value in the com_cat_abbr column
- Don't do the query - if you want to display it that way handle it in the
client
- get rid of the lookup table and store the full text in the company tableI like to have the lookup tables because I use them in the front end client
to populate pulldowns, they save storage space, they allow some limited
flexibility in changing the definition for the abbreviation, and they allow
administrators to be able to see the abbreviation and understand what they
are looking at. When referential integrity becomes available, I will use
these lookup tables to enforce integrity.What are my alternatives? What is everyone else doing in their Postgres
system? Thanks.- B
************
--
rdavis@lillysoftware.com
rsdavis@mediaone.net
http://people.ne.mediaone.net/rsdavis************
--
rdavis@lillysoftware.com
rsdavis@mediaone.net
http://people.ne.mediaone.net/rsdavis************
--
rdavis@lillysoftware.com
rsdavis@mediaone.net
http://people.ne.mediaone.net/rsdavis
From bouncefilter Mon Jan 17 11:21:14 2000
Received: from candle.pha.pa.us (pgman@s5-03.ppp.op.net [209.152.195.67])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA39659
for <pgsql-general@hub.org>; Mon, 17 Jan 2000 11:20:03 -0500 (EST)
(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id
LAA29434;
Mon, 17 Jan 2000 11:19:28 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200001171619.LAA29434@candle.pha.pa.us>
Subject: Re: [GENERAL] System tables
In-Reply-To: <Pine.GSO.4.02A.10001171217560.8593-100000@Puma.DoCS.UU.SE> from
Peter Eisentraut at "Jan 17, 2000 12:20:03 pm"
To: Peter Eisentraut <peter_e@gmx.net>
Date: Mon, 17 Jan 2000 11:19:28 -0500 (EST)
CC: Fabian.Frederick@prov-liege.be, pgsql-general@hub.org
X-Mailer: ELM [version 2.4ME+ PL66 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
System tables are all tables starting with pg_. They store everything
about your database installation, such as name of the actual databases,
tables, attribute types, rules, indexes, what-have-you. Access statistics
as in "How many times did table x get accessed since then" are not stored
anywhere, at least not where you could make use of it.
psql \dS shows you the system tables.
--
Bruce Momjian | http://www.op.net/~candle
pgman@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 19026From bouncefilter Mon Jan 17 11:37:13 2000
Received: from smtp-2.nordnet.fr (smtp-2.nordnet.fr [194.206.126.252])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA46368
for <pgsql-general@hub.org>; Mon, 17 Jan 2000 11:36:18 -0500 (EST)
(envelope-from aflorent@iris-tech.fr)
Received: from scomm.iris-tech.fr (gate3-141.nordnet.fr [195.6.247.141])
by smtp-2.nordnet.fr (8.9.3/8.9.0) with ESMTP id RAA04214
for <pgsql-general@hub.org>; Mon, 17 Jan 2000 17:36:05 +0100
Received: from (mail@localhost)
by scomm.iris-tech.fr (8.8.5/jtpda-5.3) id QAA03270
for <pgsql-general@hub.org>; Mon, 17 Jan 2000 16:34:57 +0100
X-Authentication-Warning: Scomm.iris-tech.fr: mail set sender to
<aflorent@iris-tech.fr> using -f
Received: from siris.iris-tech.fr(192.168.0.100) by Scomm via smap (V2.1)
id xma003202; Mon, 17 Jan 00 16:26:40 +0100
Received: from iris-tech.fr (aflorent@afl.iris-tech.fr [192.168.0.5])
by siris.iris-tech.fr (8.9.3/jtpda-5.3) with ESMTP id RAA02506
for <pgsql-general@hub.org>; Mon, 17 Jan 2000 17:26:07 +0100
Message-ID: <388342A2.30DE6CD8@iris-tech.fr>
Date: Mon, 17 Jan 2000 17:26:10 +0100
From: Arnaud FLORENT <aflorent@iris-tech.fr>
X-Mailer: Mozilla 4.7 [fr] (Win95; I)
X-Accept-Language: fr,en
MIME-Version: 1.0
To: PostgreSQL general ML <pgsql-general@hub.org>
Subject: shell command from trigger
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Is it possible to execute shell command from a trigger?
--
______________________________
Arnaud FLORENT
IRIS Technologies
phone: (33) 03 20 65 85 80
fax: (33) 03 20 65 85 81
GSM: (33) 06 15 14 32 90
mailto:aflorent@iris-tech.fr
______________________________
From bouncefilter Mon Jan 17 11:30:05 2000
Received: from mail.utexas.edu (wb3-a.mail.utexas.edu [128.83.126.138])
by hub.org (8.9.3/8.9.3) with SMTP id LAA41661
for <pgsql-general@postgreSQL.org>;
Mon, 17 Jan 2000 11:30:02 -0500 (EST)
(envelope-from james@opubs.utexas.edu)
Received: (qmail 7753 invoked by uid 0); 17 Jan 2000 16:30:01 -0000
Received: from opubsnetadm.opubs.utexas.edu (HELO opubs.utexas.edu)
(128.83.53.50) by umbs-smtp-3 with SMTP; 17 Jan 2000 16:30:01 -0000
Sender: root
Message-ID: <38834389.1B838C13@opubs.utexas.edu>
Date: Mon, 17 Jan 2000 10:30:02 -0600
From: james <james@opubs.utexas.edu>
X-Mailer: Mozilla 4.7 [en] (X11; U; Linux 2.2.12-20 i586)
X-Accept-Language: en
MIME-Version: 1.0
To: pgsql-general@postgreSQL.org
Subject: (no subject)
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
subscribe
end
From bouncefilter Mon Jan 17 11:35:03 2000
Received: from rage.hub.org (root@nat193.192.mpoweredpc.net [142.177.193.192])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA45876
for <pgsql-general@hub.org>; Mon, 17 Jan 2000 11:34:02 -0500 (EST)
(envelope-from jeff@hub.org)
Received: from localhost (jeff@localhost)
by rage.hub.org (8.9.3/8.9.3) with ESMTP id MAA17949;
Mon, 17 Jan 2000 12:34:04 -0400 (AST) (envelope-from jeff@hub.org)
X-Authentication-Warning: rage.hub.org: jeff owned process doing -bs
Date: Mon, 17 Jan 2000 12:34:04 -0400 (AST)
From: Jeff MacDonald <jeff@hub.org>
Reply-To: Jeff MacDonald <jeff@hub.org>
To: Stephane Bortzmeyer <bortzmeyer@pasteur.fr>
cc: pgsql-general@hub.org
Subject: Re: [GENERAL] cgi with postgres
In-Reply-To: <200001171602.RAA17771@ezili.sis.pasteur.fr>
Message-ID: <Pine.BSF.4.10.10001171233520.428-100000@rage.hub.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
normal users are not allowed to chown files to oterh users..
On Mon, 17 Jan 2000, Stephane Bortzmeyer wrote:
On Monday 17 January 2000, at 11 h 18, the keyboard of Jeff MacDonald
<jeff@hub.org> wrote:My CGIs sources a config file, in mode 700, only readable by 'www' (the user
which executes Apache).this option works, but not well if the user isn't root.
Can you elaborate? Of course, it works well and of course, the actual user is not root.
Jeff MacDonald
jeff@hub.org
===================================================================
So long as the Universe had a beginning, we can suppose it had a
creator, but if the Universe is completly self contained , having
no boundry or edge, it would neither be created nor destroyed
It would simply be.
===================================================================
From bouncefilter Mon Jan 17 11:37:20 2000
Received: from thelab.hub.org (nat200.60.mpoweredpc.net [142.177.200.60])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA46413;
Mon, 17 Jan 2000 11:36:30 -0500 (EST) (envelope-from scrappy@hub.org)
Received: from localhost (scrappy@localhost)
by thelab.hub.org (8.9.3/8.9.1) with ESMTP id MAA13442;
Mon, 17 Jan 2000 12:36:10 -0400 (AST) (envelope-from scrappy@hub.org)
X-Authentication-Warning: thelab.hub.org: scrappy owned process doing -bs
Date: Mon, 17 Jan 2000 12:36:09 -0400 (AST)
From: The Hermit Hacker <scrappy@hub.org>
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: pgsql-general@postgreSQL.org, PostgreSQL-core <pgsql-core@postgreSQL.org>
Subject: Re: [ANNOUNCE] Banner Ads ...
In-Reply-To: <200001171623.LAA29746@candle.pha.pa.us>
Message-ID: <Pine.BSF.4.21.0001171229550.46499-100000@thelab.hub.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
On Mon, 17 Jan 2000, Bruce Momjian wrote:
Effective immediately, PostgreSQL, Inc is now offer banner ad space on the
mailing list and search facilities of http://www.postgresql.org ...At a rate of $50CDN (~$35US) per 1000 impressions, this provides you a
means of supporting the project (50% of all revenue generated goes back
into PostgreSQL), as well as a way of advertising your project...Current count for number of pages affected is >68 000, and >37 000 pages
viewed daily, the potential exposure is high ...Wow, that's a high number 37k/day. Is that real people or search
engines?
Everything ... and I fear I screwed up :( I forgot that our search
engine was being populated yesterday, so that would be included in that
number...
More accurate numbers (and realistic) is 953 so far today ... the cgi's
are installed on pgsql.com, and the search engine software appears
designed to not pick up banner ads as long as their base URL isn't the
same as the page itself, so 953 is what ppl are seeing, not what the
search engine as indexed ...
So, more accurately, we're seeing ~2k/day ... we're going to be tracking
all this, to watch growth and whatnot ...
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
From bouncefilter Mon Jan 17 12:28:00 2000
Received: from success.e-corp.net (IDENT:qmailr@success.e-corp.net
[216.94.152.2]) by hub.org (8.9.3/8.9.3) with SMTP id MAA59397
for <pgsql-general@postgresql.org>;
Mon, 17 Jan 2000 12:27:25 -0500 (EST)
(envelope-from astrope@e-corp.net)
Received: (qmail 32183 invoked from network); 17 Jan 2000 17:27:25 -0000
Received: from modemcable047.47-200-24.mtl.mc.videotron.net
(astrope@24.200.47.47)
by success.e-corp.net with SMTP; 17 Jan 2000 17:27:25 -0000
Date: Mon, 17 Jan 2000 12:27:21 -0500 (EST)
From: Trevor Astrope <astrope@e-corp.net>
X-Sender: astrope@gord.tabbweb.com
To: pgsql-general@postgresql.org
Subject: High Availability?
Message-ID: <Pine.LNX.4.10.10001171226350.6393-100000@gord.tabbweb.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Hi,
I take it from the mailing list archives that transaction logging is not
yet supported, or there is currently no way to view transaction logs? Like
others, I need to replicate or sync databases on multiple machines in a
high availability configuration. We've been using postgresql and have been
very happy with it, but before moving to the next level, I need to know
the status of transaction logging or replication...
I'd be interested to know if anyone else is using postgresql in a high
availability configuration and how you sync data...
Any help is appreciated.
Regards,
Trevor Astrope
astrope@e-corp.net
From bouncefilter Mon Jan 17 12:47:04 2000
Received: from falla.videotron.net (falla.videotron.net [205.151.222.106])
by hub.org (8.9.3/8.9.3) with ESMTP id MAA65699
for <pgsql-general@postgresql.org>;
Mon, 17 Jan 2000 12:46:29 -0500 (EST)
(envelope-from web@inter-resa.com)
Received: from inter-resa.com ([24.200.100.43])
by falla.videotron.net (Sun Internet Mail Server
sims.3.5.1999.12.14.10.29.p8)
with ESMTP id <0FOH00BUNQOQVD@falla.videotron.net> for
pgsql-general@postgresql.org; Mon, 17 Jan 2000 12:46:02 -0500 (EST)
Date: Mon, 17 Jan 2000 12:45:06 -0500
From: Web Manager <web@inter-resa.com>
Subject: retore from dump problem - query buffer full
Sender: www@falla.videotron.net
To: pgsql-general@postgresql.org
Message-id: <38835522.9A46EE56@inter-resa.com>
Organization: The INTER-Res@ tourism network
MIME-version: 1.0
X-Mailer: Mozilla 4.51 [en] (X11; I; FreeBSD 3.1-RELEASE i386)
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7bit
X-Accept-Language: en
Hello,
I am not an expert with Postgres (version 6.4.2) but I never had a
problem with using pg_dump and rebuilding a database before...
I have this new database working well under the same postgres version.
I make a dump:
pg_dump -d -o -z -f dump_db_name db_name
I then copy the file on my computer. I make:
createdb db_name
psql -f dump_db_name db_name
2 things:
I tried without the -o but I got many error on some tables like:
INSERT INTO "typehotel" values (0,'camping','camping site');
query buffer max length of 20000 exceeded
query line ignored
this db uses sequence for the first time. I read that the dump file
needed -o
So I did that...
the same restore command made:
box:~/data/base>$ psql -f dump_db_name db_name
CREATE TABLE pgdump_oid (dummy int4);
CREATE
COPY pgdump_oid WITH OIDS FROM stdin;
Enter info followed by a newline
End with a backslash and a period on a line by itself.
Since I don't know what this is... I put "\."
the file is read...
The result was the same as with no -o
The only difference I see between this new db and the other ones is the
sequence and some text field (quite big).
Any ideas?
Thank's!
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Marc Andre Paquin
From bouncefilter Mon Jan 17 15:41:04 2000
Received: from kiln.isn.net (root@kiln.isn.net [198.167.161.1])
by hub.org (8.9.3/8.9.3) with ESMTP id PAA10217
for <pgsql-general@hub.org>; Mon, 17 Jan 2000 15:40:57 -0500 (EST)
(envelope-from ctassell@isn.net)
Received: from niki (dunken05.isn.net [216.129.42.72])
by kiln.isn.net (8.9.3/8.9.3) with ESMTP id QAA04530
for <pgsql-general@hub.org>; Mon, 17 Jan 2000 16:40:20 -0400
Message-Id: <4.2.0.58.20000117163358.00a7cce0@mailer.isn.net>
X-Sender: ctassell@mailer.isn.net
X-Mailer: QUALCOMM Windows Eudora Pro Version 4.2.0.58
Date: Mon, 17 Jan 2000 16:39:35 -0400
To: pgsql-general@hub.org
From: Charles Tassell <ctassell@isn.net>
Subject: Re: [GENERAL] cgi with postgres
In-Reply-To: <200001171602.RAA17771@ezili.sis.pasteur.fr>
References: <Pine.BSF.4.10.10001171117200.428-100000@rage.hub.org>
<jeff@hub.org>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed
This really doesn't have anything to do with Postgres, but you guys may
want to look into the cgi-wrapper addon that comes with Apache. I haven't
used it myself, but from what I understand it does a setuid to the user
whose home directory the CGI is in before executing it, thus having the
same file access permissions as that user. Then it doesn't matter if other
people can run CGI scripts or have shell access, as unless they have the
password for the account of the CGI, they can't read it (as long as you
aren't an idiot in setting the file permissions.) PHP3 has a similar
ability built in that can be turned on via the php3.ini file.
The problem with CGI security isn't so much a matter of people getting
shell access and playing around, it's more along the lines of writing a CGI
that executes a program such as find or cat as the web user, which would
enable them to list and display all the CGI's on the system, and their
config files.
At 12:02 PM 1/17/00, Stephane Bortzmeyer wrote:
On Monday 17 January 2000, at 11 h 18, the keyboard of Jeff MacDonald
<jeff@hub.org> wrote:My CGIs sources a config file, in mode 700, only readable by 'www'
(the user
which executes Apache).
this option works, but not well if the user isn't root.
Can you elaborate? Of course, it works well and of course, the actual user
is not root.************
From bouncefilter Mon Jan 17 16:39:06 2000
Received: from mail.pbpower.co.nz (mail.designpower.co.nz [203.97.56.178])
by hub.org (8.9.3/8.9.3) with ESMTP id QAA24692
for <pgsql-general@hub.org>; Mon, 17 Jan 2000 16:38:59 -0500 (EST)
(envelope-from DerricuttM@pbworld.com)
Received: by mail.pbpower.co.nz with Internet Mail Service (5.5.2650.21)
id <DD1X1WJP>; Tue, 18 Jan 2000 10:47:23 +1300
Message-ID: <93314B6256A5D211BE8B006097B950271791D3@NZAM>
From: "Derricutt, Mark" <DerricuttM@pbworld.com>
To: pgsql-general@hub.org
Subject: Problems with pg_dump...
Date: Tue, 18 Jan 2000 10:40:09 +1300
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2650.21)
Content-Type: text/plain;
charset="iso-8859-1"
Hi, I've got a few problems with pg_dump that I was wondering if you guys
could help me out with.
I have my local database on my machine, and a database on a remote webserver
and I was wanting to move -one- table from my local db to the remote.
So I used "pg_dump -f mytable.sql -t cdcollection derricuttm" to dump just
that one table and uploaded it to my remote machine.
When I tried to import the table into the other database, it failed because
the used sequence didn't exist. Shouldn't the -t option in pg_dump include
everything to recreate that particular table, such as its sequences?
Anyway, I added those lines to mytable.sql and reran it, and now get:
COPY "cdcollection" FROM stdin;
ERROR: Bad date external representation '07-26-1999'
PQendcopy: resetting connection
CREATE UNIQUE INDEX "cdcollection_cd_id_key" on "cdcollection" using btree (
"cd_id" "int4 _ops" );
CREATE
EOF
The respective versions of PostgreSQL are:
Local: 6.4 (I could only find the version number in PG_VERSION)
Remote: [PostgreSQL 6.5.1 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3]
Have there been some changes in the way dates are handled between 6.4 anf
6.5.1? Or should I be doing something different?
Mark
From bouncefilter Mon Jan 17 16:55:04 2000
Received: from smtp.hccnet.nl (smtp.hccnet.nl [193.172.127.93])
by hub.org (8.9.3/8.9.3) with ESMTP id QAA26497
for <pgsql-general@postgresql.org>;
Mon, 17 Jan 2000 16:54:50 -0500 (EST)
(envelope-from e.bras@hccnet.nl)
Received: from pietjepuik by smtp.hccnet.nl
via uds136-116.dial.hccnet.nl [193.173.116.136] with SMTP for
<pgsql-general@postgresql.org>
id WAA22384 (8.8.5/1.11); Mon, 17 Jan 2000 22:55:03 +0100 (MET)
From: "Ed Bras" <e.bras@hccnet.nl>
To: <pgsql-general@postgresql.org>
Subject: Comments in postgres ?
Date: Mon, 17 Jan 2000 22:52:28 +0100
Message-ID: <002801bf6135$2636a6a0$1c969696@pietjepuik>
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="----=_NextPart_000_0029_01BF613D.87FB0EA0"
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook CWS, Build 9.0.2416 (9.0.2910.0)
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
This is a multi-part message in MIME format.
------=_NextPart_000_0029_01BF613D.87FB0EA0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
I am just new to postgres and I am putting al the commands like create
tabel and create sequence in one file, but want to join them with some
comment. What do I need to put in front of a comment so that postgres
ingnores the rest of the sentence ??
Regards,
Ed Bras
------=_NextPart_000_0029_01BF613D.87FB0EA0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 5.00.2314.1000" name=3DGENERATOR></HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2><SPAN =
class=3D223025021-17012000> I am just new=20
to postgres and I am putting al the commands like create tabel and =
create=20
sequence in one file, but want to join them with some comment. What do I =
need to=20
put in front of a comment so that postgres ingnores the rest of the =
sentence=20
??</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D223025021-17012000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D223025021-17012000>Regards,</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D223025021-17012000>Ed=20
Bras</SPAN></FONT></DIV></BODY></HTML>
------=_NextPart_000_0029_01BF613D.87FB0EA0--
From bouncefilter Mon Jan 17 17:13:06 2000
Received: from sakaki.communique.net (sakaki.communique.net [204.27.64.202])
by hub.org (8.9.3/8.9.3) with ESMTP id RAA31988
for <pgsql-general@postgreSQL.org>;
Mon, 17 Jan 2000 17:12:56 -0500 (EST)
(envelope-from davidb@vectormath.com)
From: davidb@vectormath.com
Received: from bullwinkle (adsl-204-1-67-149.indi.se.verio.net [204.1.67.149])
by sakaki.communique.net (8.8.8/8.8.8) with SMTP id QAA09726;
Mon, 17 Jan 2000 16:12:50 -0600 (CST)
Message-ID: <002601bf6137$b92ec990$0602010a@bullwinkle.vectormath>
To: "Ed Bras" <e.bras@hccnet.nl>, <pgsql-general@postgreSQL.org>
Subject: Re: [GENERAL] Comments in postgres ?
Date: Mon, 17 Jan 2000 16:10:51 -0600
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="----=_NextPart_000_0023_01BF6105.6CC565F0"
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_0023_01BF6105.6CC565F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
-- This is a one line comment.
/*
This is a multi-
line comment.
*/
-----Original Message-----
From: Ed Bras <e.bras@hccnet.nl>
To: pgsql-general@postgreSQL.org <pgsql-general@postgreSQL.org>
Date: Monday, January 17, 2000 4:06 PM
Subject: [GENERAL] Comments in postgres ?
=20
=20
I am just new to postgres and I am putting al the commands like =
create tabel and create sequence in one file, but want to join them with =
some comment. What do I need to put in front of a comment so that =
postgres ingnores the rest of the sentence ??
=20
Regards,
Ed Bras
------=_NextPart_000_0023_01BF6105.6CC565F0
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><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 =
Transitional//EN">
<META content=3D'"MSHTML 4.72.3110.7"' name=3DGENERATOR>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT size=3D2>-- This is a one line comment.</FONT></DIV>
<DIV><FONT size=3D2></FONT> </DIV>
<DIV><FONT size=3D2>/*</FONT></DIV>
<DIV><FONT size=3D2>This is a multi-</FONT></DIV>
<DIV><FONT size=3D2>line comment.</FONT></DIV>
<DIV><FONT size=3D2>*/</FONT></DIV>
<BLOCKQUOTE=20
style=3D"BORDER-LEFT: #000000 solid 2px; MARGIN-LEFT: 5px; PADDING-LEFT: =
5px">
<DIV><FONT face=3DArial size=3D2><B>-----Original =
Message-----</B><BR><B>From:=20
</B>Ed Bras <<A=20
href=3D"mailto:e.bras@hccnet.nl">e.bras@hccnet.nl</A>><BR><B>To: =
</B><A=20
=
href=3D"mailto:pgsql-general@postgreSQL.org">pgsql-general@postgreSQL.org=
</A>=20
<<A=20
=
href=3D"mailto:pgsql-general@postgreSQL.org">pgsql-general@postgreSQL.org=
</A>><BR><B>Date:=20
</B>Monday, January 17, 2000 4:06 PM<BR><B>Subject: </B>[GENERAL] =
Comments=20
in postgres ?<BR><BR></DIV></FONT>
<DIV><FONT face=3DArial size=3D2><SPAN =
class=3D223025021-17012000> I am just=20
new to postgres and I am putting al the commands like create tabel =
and=20
create sequence in one file, but want to join them with some =
comment. What=20
do I need to put in front of a comment so that postgres ingnores the =
rest of=20
the sentence ??</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D223025021-17012000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D223025021-17012000>Regards,</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D223025021-17012000>Ed =
Bras</SPAN></FONT></DIV></BLOCKQUOTE></BODY></HTML>
------=_NextPart_000_0023_01BF6105.6CC565F0--
From bouncefilter Mon Jan 17 18:03:06 2000
Received: from kuolema.infodrom.north.de (postfix@kuolema.Infodrom.North.DE
[194.94.254.227]) by hub.org (8.9.3/8.9.3) with ESMTP id SAA44839
for <pgsql-general@postgreSQL.org>;
Mon, 17 Jan 2000 18:02:54 -0500 (EST)
(envelope-from joey@finlandia.Infodrom.North.DE)
Received: from finlandia.Infodrom.North.DE (finlandia.Infodrom.North.DE
[194.94.254.226]) by kuolema.infodrom.north.de (Postfix) with ESMTP
id EECE6146E7E; Tue, 18 Jan 2000 00:02:45 +0100 (CET)
Received: at Infodrom Oldenburg (/\##/\ Smail-3.2.0.102 1998-Aug-2 #2)
by finlandia.Infodrom.North.DE via smail from stdin
id <m12ALAb-000bK3C@finlandia.Infodrom.North.DE>
for pgsql-general@postgreSQL.org; Tue, 18 Jan 2000 00:02:45 +0100 (CET)
Date: Tue, 18 Jan 2000 00:02:45 +0100
From: Martin Schulze <joey@finlandia.Infodrom.North.DE>
To: Ed Bras <e.bras@hccnet.nl>
Cc: pgsql-general@postgreSQL.org
Subject: Re: Comments in postgres ?
Message-ID: <20000118000245.D14247@finlandia.infodrom.north.de>
Reply-To: Martin Schulze <joey@infodrom.north.de>
References: <002801bf6135$2636a6a0$1c969696@pietjepuik>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Mutt/1.0i
In-Reply-To: <002801bf6135$2636a6a0$1c969696@pietjepuik>;
from e.bras@hccnet.nl on Mon, Jan 17, 2000 at 10:52:28PM +0100
Ed Bras wrote:
I am just new to postgres and I am putting al the commands like create
tabel and create sequence in one file, but want to join them with some
comment. What do I need to put in front of a comment so that postgres
ingnores the rest of the sentence ??
-- comment
create table foo ( -- 'nother comment
Regards,
Joey
--
Those who don't understand Unix are condemned to reinvent it, poorly.
From bouncefilter Mon Jan 17 18:35:18 2000
Received: from hermes.iol.cz (hermes.iol.cz [194.228.2.36])
by hub.org (8.9.3/8.9.3) with ESMTP id SAA51140
for <pgsql-general@postgreSQL.org>;
Mon, 17 Jan 2000 18:34:37 -0500 (EST)
(envelope-from robert@robert.cz)
Received: from robert.cz ([194.228.143.73]) by hermes.iol.cz
(Post.Office MTA v3.5.3 release 223
ID# 631-64078U55000L55000S0V35) with ESMTP id cz
for <pgsql-general@postgreSQL.org>; Tue, 18 Jan 2000 00:34:04 +0100
Message-ID: <3883A80A.C2F69C8D@robert.cz>
Date: Tue, 18 Jan 2000 00:38:50 +0100
From: Robert <robert@robert.cz>
X-Mailer: Mozilla 4.7 [en] (Win98; I)
X-Accept-Language: en
MIME-Version: 1.0
To: pgsql-general@postgreSQL.org
Subject: Two serial fields question
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit
Hi,
what's the best way to insert a new invoice to the following table
create table invoice_table (
id serial;
number int;
year int;
)
The problem is of course that 'number' is unique only in the given year
so I'd need to lock table and do something like
insert into invoice_table (number,year) values (max(...)+1,2000)
I guess this is fairly common problem, what's the best/standard PG way
of
doing it? Thanks for your help.
- Robert
From bouncefilter Mon Jan 17 23:45:10 2000
Received: from goa1.dot.net.in (goa1.dot.net.in [202.54.17.30])
by hub.org (8.9.3/8.9.3) with ESMTP id XAA17096
for <pgsql-general@postgreSQL.org>;
Mon, 17 Jan 2000 23:44:49 -0500 (EST)
(envelope-from anuradha@phil.com.sg)
Received: from email.philnet ([202.54.17.60])
by goa1.dot.net.in (8.9.2/8.9.2) with ESMTP id KAA20306
for <pgsql-general@postgreSQL.org>;
Tue, 18 Jan 2000 10:16:23 +0530 (GMT)
Received: from localhost (anuradha@localhost)
by email.philnet (8.9.3/8.8.7) with ESMTP id KAA05687
for <pgsql-general@postgreSQL.org>; Tue, 18 Jan 2000 10:16:43 +0530
Date: Tue, 18 Jan 2000 10:16:43 +0530 (IST)
From: ANURADHA PAIDARKAR <anuradha@phil.com.sg>
To: PostgreSQL-general <pgsql-general@postgreSQL.org>
Subject: Re: [GENERAL] confusion with vacuum commnad.
In-Reply-To: <387D94F8.9CB6F223@unica.edu>
Message-ID: <Pine.LNX.4.20.0001181006530.8324-100000@email.philnet>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Hi !
Can anybody help us.
When I run vacuum command it gives me following error:
(for table pend_cust)
NOTICE: Rel pend_cust : uninitialized page 1161 fixing
Most of the time pend_cust table is accessed for spooling the reports,
where in the records are continuously getting added and deleted whenever
report is being run.
What does the above error mean?
regards
AP.
From bouncefilter Tue Jan 18 01:54:13 2000
Received: from news.tht.net (news.hub.org [216.126.91.242])
by hub.org (8.9.3/8.9.3) with ESMTP id BAA49914
for <pgsql-general@postgresql.org>;
Tue, 18 Jan 2000 01:53:19 -0500 (EST)
(envelope-from news@news.tht.net)
Received: (from news@localhost) by news.tht.net (8.9.3/8.9.3) id BAA82674
for pgsql-general@postgresql.org; Tue, 18 Jan 2000 01:45:12 -0500 (EST)
(envelope-from news)
X-Authentication-Warning: news.tht.net: news set sender to <news> using -f
From: mr_hopkins@earthlink.net (Micheal H.)
X-Newsgroups: comp.databases.postgresql.questions
Subject: jdbc question
Message-ID: <38840cb4.7976208@news.earthlink.net>
X-Newsreader: Forte Free Agent 1.11/32.235
Lines: 52
X-Complaints-To: abuse@earthlink.net
X-Trace: newsread1.prod.itd.earthlink.net 948177608 209.179.129.226 (Mon,
17 Jan 2000 22:40:08 PST)
Organization: EarthLink Network, Inc.
X-ELN-Date: Mon Jan 17 22:40:08 2000
Date: Tue, 18 Jan 2000 06:40:09 GMT
To: pgsql-questions@postgreSQL.org
I got the pgsql jdbc drivers and their names were jdbc6.5-1.1.jar and
jdbc6.5-1.2.jar. OK everything I've read refers to postgresql.jar
I've tried adding the location of the files to the class path as well
as renaming them, one by one to postgresql.jar and adding the file
name to the classpath. Nothing seems to work. Now I'm not a java pro
so perhaps something's wrong with my code or my understanding of what
to do with the .jar file(s). Here's the code.....It compiles but
never gets past "Failed to load postgresql driver". Would greatly
appreacieate any assistance........
import java.sql.*;
public class SelectApp {
public static void main(String args[]) {
String url = "jdbc:postgresql://gina/testdb";
try {
Class.forName("postgresql.Driver");
}
catch(Exception e) {
System.out.println("Failed to load postgresql
driver.");
return;
}
System.out.println("Loaded driver successfully");
try {
Connection con =
DriverManager.getConnection(url, "", "");
Statement select = con.createStatement();
ResultSet result = select.executeQuery("select
* from cities");
System.out.println("Got results:");
while(result.next()) { // process results one
row at a time
int key = result.getInt(1);
String val = result.getString(2);
System.out.println("key = " + key);
System.out.println("val = " + val);
}
select.close();
con.close();
}
catch(Exception e) {
e.printStackTrace();
}
}
}
From bouncefilter Tue Jan 18 09:43:15 2000
Received: from hotmail.com (law-f27.hotmail.com [209.185.131.90])
by hub.org (8.9.3/8.9.3) with SMTP id JAA55822
for <pgsql-general@postgreSQL.org>;
Tue, 18 Jan 2000 09:42:22 -0500 (EST)
(envelope-from oomoomi@hotmail.com)
Received: (qmail 48604 invoked by uid 0); 18 Jan 2000 14:41:51 -0000
Message-ID: <20000118144151.48600.qmail@hotmail.com>
Received: from 195.200.226.110 by www.hotmail.com with HTTP;
Tue, 18 Jan 2000 06:41:51 PST
X-Originating-IP: [195.200.226.110]
From: "omid omoomi" <oomoomi@hotmail.com>
To: jose@sferacarta.com, lylyeoh@mecomb.com
Cc: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] select for insert?
Date: Tue, 18 Jan 2000 06:41:51 PST
Mime-Version: 1.0
Content-Type: text/plain; format=flowed
hi,
why not using "primary key (a , b) " clause at the end of your create table
statement?It would unsure you that "b" is unique for particular "a". I
suggest that you test the null value entries... if you are going to face
with.
regards,
omid.
From: Jose Soares <jose@sferacarta.com>
To: Lincoln Yeoh <lylyeoh@mecomb.com>
CC: "pgsql-general@postgreSQL.org" <pgsql-general@postgreSQL.org>
Subject: Re: [GENERAL] select for insert?
Date: Tue, 18 Jan 2000 14:41:34 +0100Lincoln Yeoh wrote:
Hi everyone,
say I have a table test
create table test (a int, b text);and I want to ensure that 'b' is unique for a particular 'a' ('a' not
being
unique).
Right now I can do something like
begin transaction
select count(*) from test where b =$value;
if count = 0 then insert into test (a,b) values ($a, $value);But there is still a chance of a duplicate being inserted.
use unique constraint on column a as in:
CREATE TABLE test (a char unique, b int);
Jos���
************
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com
From bouncefilter Tue Jan 18 02:30:10 2000
Received: from hermes.iol.cz (hermes.iol.cz [194.228.2.36])
by hub.org (8.9.3/8.9.3) with ESMTP id CAA56972
for <pgsql-general@postgreSQL.org>;
Tue, 18 Jan 2000 02:29:19 -0500 (EST)
(envelope-from robert@robert.cz)
Received: from robert.cz ([194.228.143.162]) by hermes.iol.cz
(Post.Office MTA v3.5.3 release 223
ID# 631-64078U55000L55000S0V35) with ESMTP id cz;
Tue, 18 Jan 2000 08:28:43 +0100
Message-ID: <38841751.2BE37AFE@robert.cz>
Date: Tue, 18 Jan 2000 08:33:37 +0100
From: Robert <robert@robert.cz>
X-Mailer: Mozilla 4.7 [en] (Win98; I)
X-Accept-Language: en
MIME-Version: 1.0
To: pgsql-general@postgreSQL.org, arthur@levelogic.com
Subject: Re: [GENERAL] Two serial fields question
References: <3883A80A.C2F69C8D@robert.cz>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
"Arthur M. Kang" wrote::
Create a unique index on the two fields you want tied.
Create a function that will increment number ( max(number)+1 ).
Create a trigger on the table for inserts that will automatically set the
number to the next appropriate value.Then, when you insert records, just do a "insert into table (year) value
(2000)" and the number will auto-increment. If you need specific examples,
let me know and I'll send you a copy of what I did. Actually, after doing
that, I decided to remove the serial field (id) since that would more than
likely someday be the limiting field. With the unique index tied to two
fields, you can always extract out the record you want.
Well, while sequences are session-protected, I'm not sure what happens
with you approach when two processes try to insert invoice at the same
time. I'd say the trigger fires fine, the function get max+1, but in the
same time the function from second trigger gets the same max+1 as the
first trigger/insert has not finished yet... and numbering is wrong.
Maybe I don't see something obvious, like triggers get session
protection the same way like sequences?
- Robert
Robert wrote:
Hi,
what's the best way to insert a new invoice to the following table
create table invoice_table (
id serial;
number int;
year int;
)The problem is of course that 'number' is unique only in the given year
so I'd need to lock table and do something likeinsert into invoice_table (number,year) values (max(...)+1,2000)
I guess this is fairly common problem, what's the best/standard PG way
of
doing it? Thanks for your help.- Robert
From bouncefilter Tue Jan 18 03:17:12 2000
Received: from mecomb.com (gw.mecomb.com [161.142.249.98])
by hub.org (8.9.3/8.9.3) with ESMTP id DAA71993
for <pgsql-general@postgreSQL.org>;
Tue, 18 Jan 2000 03:16:08 -0500 (EST)
(envelope-from lylyeoh@mecomb.com)
Received: (from mail@localhost) by mecomb.com (8.8.7/8.8.7) id QAA18467
for <pgsql-general@postgreSQL.org>; Tue, 18 Jan 2000 16:13:35 +0800
Received: from <lylyeoh@mecomb.com> (ilab2.mecomb.po.my [192.168.3.22]) by
ns.mecomb.com via smap (V2.1)
id xma018453; Tue, 18 Jan 00 16:13:00 +0800
Message-Id: <3.0.5.32.20000118161326.008f1b80@pop.mecomb.po.my>
X-Sender: lylyeoh@pop.mecomb.po.my
X-Mailer: QUALCOMM Windows Eudora Light Version 3.0.5 (32)
Date: Tue, 18 Jan 2000 16:13:26 +0800
To: "pgsql-general@postgreSQL.org" <pgsql-general@postgreSQL.org>
From: Lincoln Yeoh <lylyeoh@mecomb.com>
Subject: Re: [GENERAL] select for insert?
In-Reply-To: <3804F79A.440FA759@bwn.net>
References: <Pine.LNX.4.10.9910131914030.32422-100000@maidast.retep.org.uk>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Hi everyone,
say I have a table test
create table test (a int, b text);
and I want to ensure that 'b' is unique for a particular 'a' ('a' not being
unique).
Right now I can do something like
begin transaction
select count(*) from test where b =$value;
if count = 0 then insert into test (a,b) values ($a, $value);
But there is still a chance of a duplicate being inserted.
Is using a multicolumn unique index the best way to avoid such problems?
Any other suggestions?
Somehow I prefer to avoid getting exceptions from databases.
Is there a "select for insert" command?
e.g. while in transactions if a select for insert has the same criteria,
it's not executed until the other prior matching transactions are
completed. How yucky is that? <grin>
Thanks,
Link.
From bouncefilter Tue Jan 18 07:25:15 2000
Received: from ga.prestige.net (dns2.prestige.net [208.220.88.11] (may be
forged)) by hub.org (8.9.3/8.9.3) with ESMTP id HAA23433
for <pgsql-general@postgresql.org>;
Tue, 18 Jan 2000 07:24:48 -0500 (EST)
(envelope-from johnbr@undefined.com)
Received: from jb1 [208.246.188.22] by ga.prestige.net
(SMTPD32-6.00) id AB6651803B0; Tue, 18 Jan 2000 07:24:06 -0500
Message-Id: <4.1.20000118071212.00c8a660@pop.mindspring.com>
X-Sender: johnbr@pop.mindspring.com
X-Mailer: QUALCOMM Windows Eudora Pro Version 4.1
Date: Tue, 18 Jan 2000 07:20:00 -0500
To: pgsql-general@postgresql.org, thoth@incanta.net
From: John Brothers <johnbr@undefined.com>
Subject: Primary Key Indexing problem
In-Reply-To: <38833030.F6B9BA1A@lillysoftware.com>
References: <028f01bf5c95$0eb15b50$0200a8c0@rsdevelop>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
HI all,
I have a script which generates a simple table, fills it with
about 11000 unique numbers, in the primary key field,
and then at the end, shows that the index has been
lost for at least one of the values, allowing another
entry to be made that duplicates the index. This is an
abstract simplification of a problem we are experiencing in
our field database.
The compressed "script" is 64k - I didn't want to attach it to this
message, but I would be happy to send it to anyone who might
be able to use it to track down whatever bug(s) might be causing
it. (I am assuming that this is a bug. If not, please accept my
apologies).
[PostgreSQL 6.5.1 on i686-pc-linux-gnu, compiled by gcc 2.7.2.3]
Thanks,
John
------------
johnbr@incanta.net
From bouncefilter Tue Jan 18 08:41:14 2000
Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1])
by hub.org (8.9.3/8.9.3) with ESMTP id IAA41618
for <pgsql-general@postgresql.org>;
Tue, 18 Jan 2000 08:40:47 -0500 (EST)
(envelope-from jose@sferacarta.com)
Received: from proxy.sferacarta.com (sfcabop1.nettuno.it [193.207.10.213])
by bologna.nettuno.it (8.9.3/8.9.3/NETTuno 4.1) with ESMTP id OAA21591;
Tue, 18 Jan 2000 14:39:19 +0100 (MET)
Received: from rosso.sferacarta.com (sferacarta.com) [10.20.30.5]
by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian))
id 12AZm0-0000jW-00; Tue, 18 Jan 2000 14:38:20 +0000
Message-ID: <38846D8E.7B6D852E@sferacarta.com>
Date: Tue, 18 Jan 2000 14:41:34 +0100
From: Jose Soares <jose@sferacarta.com>
X-Mailer: Mozilla 4.6 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
To: Lincoln Yeoh <lylyeoh@mecomb.com>
CC: "pgsql-general@postgreSQL.org" <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] select for insert?
References: <Pine.LNX.4.10.9910131914030.32422-100000@maidast.retep.org.uk>
<3.0.5.32.20000118161326.008f1b80@pop.mecomb.po.my>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Lincoln Yeoh wrote:
Hi everyone,
say I have a table test
create table test (a int, b text);and I want to ensure that 'b' is unique for a particular 'a' ('a' not being
unique).Right now I can do something like
begin transaction
select count(*) from test where b =$value;
if count = 0 then insert into test (a,b) values ($a, $value);But there is still a chance of a duplicate being inserted.
use unique constraint on column a as in:
CREATE TABLE test (a char unique, b int);
Jos���
From bouncefilter Tue Jan 18 14:57:18 2000
Received: from pcr.ca (www.pcr.ca [207.139.158.13] (may be forged))
by hub.org (8.9.3/8.9.3) with ESMTP id OAA51648
for <pgsql-general@postgresql.org>;
Tue, 18 Jan 2000 14:56:35 -0500 (EST)
(envelope-from admin@wtbwts.com)
Received: by pcr.ca (Postfix, from userid 1000)
id B29741FEE; Tue, 18 Jan 2000 14:55:47 +0000 (GMT)
Received: from localhost (localhost [127.0.0.1])
by pcr.ca (Postfix) with ESMTP id A40DF1FEC
for <pgsql-general@postgresql.org>;
Tue, 18 Jan 2000 14:55:47 +0000 (GMT)
Date: Tue, 18 Jan 2000 14:55:47 +0000 (GMT)
From: Marc Tardif <admin@wtbwts.com>
X-Sender: admin@server.b0x.com
To: pgsql-general@postgresql.org
Subject: info about udmsearch
Message-ID: <Pine.BSF.4.10.10001181453320.7618-100000@server.b0x.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
How does udmsearch store messages and how does it search for specific
keywords? Does it happen to use the fulltextindex trigger in the contrib
directory? Is there maybe a webpage describing the details of udmsearch?
Marc
From bouncefilter Tue Jan 18 10:39:16 2000
Received: from mail.velenje.cx (root@[195.210.204.104])
by hub.org (8.9.3/8.9.3) with ESMTP id KAA75879
for <pgsql-general@postgreSQL.org>;
Tue, 18 Jan 2000 10:38:23 -0500 (EST)
(envelope-from mitja@mail.velenje.cx)
Received: from localhost (mitja@localhost)
by mail.velenje.cx (8.9.3/8.9.3) with ESMTP id QAA30280
for <pgsql-general@postgreSQL.org>; Tue, 18 Jan 2000 16:38:28 +0100
Date: Tue, 18 Jan 2000 16:38:27 +0100 (CET)
From: Mitja Novak <mitja@mail.velenje.cx>
To: pgsql-general@postgreSQL.org
Message-ID: <Pine.LNX.4.10.10001181638100.30204-100000@mail.velenje.cx>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
subscribe
end
From bouncefilter Tue Jan 18 10:59:17 2000
Received: from mail.velenje.cx (root@[195.210.204.104])
by hub.org (8.9.3/8.9.3) with ESMTP id KAA78763
for <pgsql-general@postgreSQL.org>;
Tue, 18 Jan 2000 10:58:23 -0500 (EST)
(envelope-from mitja@mail.velenje.cx)
Received: from localhost (mitja@localhost)
by mail.velenje.cx (8.9.3/8.9.3) with ESMTP id QAA02604
for <pgsql-general@postgreSQL.org>; Tue, 18 Jan 2000 16:58:27 +0100
Date: Tue, 18 Jan 2000 16:58:27 +0100 (CET)
From: Mitja Novak <mitja@mail.velenje.cx>
To: pgsql-general@postgreSQL.org
Subject: launguage...
Message-ID: <Pine.LNX.4.10.10001181655540.30204-100000@mail.velenje.cx>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
hello.
i`m wondering how to set up an launguage for a certain database or how to
set up a general launguage for the postgres?
the problem is that slovenia (as most other contries) has some special
charaters and i would like to sort the data containig this chars..
therefore i need the launguage setings.
thanks for your help!
Mitja.
From bouncefilter Tue Jan 18 12:24:17 2000
Received: from news.tht.net (news.hub.org [216.126.91.242])
by hub.org (8.9.3/8.9.3) with ESMTP id MAA99430
for <pgsql-general@postgresql.org>;
Tue, 18 Jan 2000 12:23:20 -0500 (EST)
(envelope-from news@news.tht.net)
Received: (from news@localhost) by news.tht.net (8.9.3/8.9.3) id MAA25163
for pgsql-general@postgresql.org; Tue, 18 Jan 2000 12:12:42 -0500 (EST)
(envelope-from news)
X-Authentication-Warning: news.tht.net: news set sender to <news> using -f
From: "Joe Foster" <fosterware@home.com>
X-Newsgroups: comp.databases.postgresql.questions
Subject: SQL 6.5 using views
Lines: 17
X-Newsreader: Microsoft Outlook Express 4.72.3155.0
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3155.0
Message-ID: <da1h4.2463$sO5.31702@news.rdc1.tn.home.com>
Date: Tue, 18 Jan 2000 17:12:41 GMT
X-Complaints-To: abuse@home.net
X-Trace: news.rdc1.tn.home.com 948215561 24.2.112.62 (Tue,
18 Jan 2000 09:12:41 PST)
Organization: @Home Network
To: pgsql-questions@postgresql.org
Via SQL Enterprise manager, I'm trying to use a view in a join update and
get SQL error "Msg 156, Level 15, State 1 - Incorrect syntax near the
keyword 'INNER'".
The syntax is
UPDATE TableName INNER JOIN ViewName
ON Tablename.Key = ViewName.Key
SET ...
Anybody know what at's wrong here?
Thanks in advance,
Joe Foster
From bouncefilter Tue Jan 18 12:34:18 2000
Received: from double.nlr.ru (double.nlr.ru [212.193.3.3])
by hub.org (8.9.3/8.9.3) with ESMTP id MAA04489
for <pgsql-general@postgreSQL.org>;
Tue, 18 Jan 2000 12:33:32 -0500 (EST) (envelope-from gav@nlr.ru)
Received: from GAV ([172.16.2.2])
by double.nlr.ru (8.9.3/8.9.3) with ESMTP id UAA23224
for <pgsql-general@postgreSQL.org>; Tue, 18 Jan 2000 20:25:50 +0300
Date: Tue, 18 Jan 2000 20:36:54 +0300
From: Alex Guryanow <gav@nlr.ru>
X-Mailer: The Bat! (v1.34a) UNREG / CD5BF9353B3B7091
Reply-To: gav <gav@nlr.ru>
X-Priority: 3 (Normal)
Message-ID: <7858.000118@nlr.ru>
To: pgsql-general@postgreSQL.org
Subject: Cannot create index
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Hi,
I have the table called actions, that contains the column "date" of
type timestamp. When I try to create the index on this column I
receive error:
pqReadData(): -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
I use Postgres 6.5.3 on RedHat 6.1 (kernel 2.2.13). The command I use
to cretate index is:
create index actions_date on actions using btree (date datetime_ops);
Why this happens?
Alex
From bouncefilter Tue Jan 18 13:45:19 2000
Received: from eth.net (pop3.eth.net [202.9.145.19])
by hub.org (8.9.3/8.9.3) with ESMTP id NAA31441
for <pgsql-general@postgresql.org>;
Tue, 18 Jan 2000 13:45:05 -0500 (EST)
(envelope-from easwaran@eth.net)
Received: from VSNL ([202.9.149.187]) by eth.net with Microsoft
SMTPSVC(5.5.1877.357.35); Wed, 19 Jan 2000 00:14:29 +0530
Message-ID: <000b01bf61e4$e73967c0$bb9509ca@VSNL>
Reply-To: "Easwaran" <easwaran@eth.net>
From: "Easwaran" <easwaran@eth.net>
To: <pgsql-general@postgresql.org>
Subject: Doubt regd PSQL
Date: Wed, 19 Jan 2000 00:20:32 +0530
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
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
Is Postgres Sql an active DBMS or not ?
If not can somebody pls tell me abt any commercially available active DBMS
package
Have a Nice Day....
Bye,
J. Easwaran
From bouncefilter Tue Jan 18 02:24:10 2000
Received: from mailserver.trigold.cngb.com ([203.93.6.66])
by hub.org (8.9.3/8.9.3) with ESMTP id CAA56495
for <pgsql-general@postgreSQL.org>;
Tue, 18 Jan 2000 02:23:46 -0500 (EST)
(envelope-from cbl@trigold.cngb.com)
Received: from 203.93.6.187 by mailserver.trigold.cngb.com with SMTP
(Microsoft Exchange Internet Mail Service Version 5.0.1457.7)
id C34Y1VAH; Tue, 18 Jan 2000 15:21:49 +0800
Sender: Mento
Message-ID: <3884CB24.5737E427@trigold.cngb.com>
Date: Tue, 18 Jan 2000 15:20:52 -0500
From: Mento Chan <cbl@trigold.cngb.com>
Reply-To: cbl@trigold.cngb.com
Organization: III Co. Ltd
X-Mailer: Mozilla 4.61 [en] (X11; I; Linux 2.2.12-20 i586)
X-Accept-Language: en
MIME-Version: 1.0
To: pgsql-general@postgreSQL.org
Content-Type: text/plain; charset=gb2312
Content-Transfer-Encoding: 7bit
subscribe
end
From bouncefilter Tue Jan 18 20:03:30 2000
Received: from ga.prestige.net (dns2.prestige.net [208.220.88.11] (may be
forged)) by hub.org (8.9.3/8.9.3) with ESMTP id UAA39762
for <pgsql-general@postgresql.org>;
Tue, 18 Jan 2000 20:02:35 -0500 (EST)
(envelope-from johnbr@mindspring.com)
Received: from jb1 [208.246.188.22] by ga.prestige.net
(SMTPD32-6.00) id AD0293C300AC; Tue, 18 Jan 2000 20:01:54 -0500
Message-Id: <4.1.20000118195710.00c926c0@pop.mindspring.com>
X-Sender: (Unverified)
X-Mailer: QUALCOMM Windows Eudora Pro Version 4.1
Date: Tue, 18 Jan 2000 19:57:40 -0500
To: pgsql-general@postgresql.org, thoth@incanta.net
From: John Brothers <johnbr@mindspring.com>
Subject: Primary Key Indexing problem
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
HI all,
I have a script which generates a simple table, fills it with
about 11000 unique numbers, in the primary key field,
and then at the end, shows that the index has been
lost for at least one of the values, allowing another
entry to be made that duplicates the index. This is an
abstract simplification of a problem we are experiencing in
our field database.
The compressed "script" is 64k - I didn't want to attach it to this
message, but I would be happy to send it to anyone who might
be able to use it to track down whatever bug(s) might be causing
it. (I am assuming that this is a bug. If not, please accept my
apologies).
[PostgreSQL 6.5.1 on i686-pc-linux-gnu, compiled by gcc 2.7.2.3]
Thanks,
John
------------
johnbr@incanta.net
From bouncefilter Wed Jan 19 00:23:33 2000
Received: from vpcit.ru (qmailr@dialup.vpcit.ru [195.38.52.246])
by hub.org (8.9.3/8.9.3) with SMTP id AAA10527
for <pgsql-general@postgreSQL.org>;
Wed, 19 Jan 2000 00:22:59 -0500 (EST) (envelope-from yura@vpcit.ru)
Received: (qmail 7826 invoked from network); 19 Jan 2000 05:22:54 -0000
Received: from gercon.vpcit.ru (195.38.52.226)
by dialup.vpcit.ru with SMTP; 19 Jan 2000 05:22:54 -0000
Date: Wed, 19 Jan 2000 10:22:18 +0500
From: yura <yura@vpcit.ru>
X-Mailer: The Bat! (v1.36) S/N F29DEE5D / Educational
Reply-To: yura <yura@vpcit.ru>
Organization: Gercon
X-Priority: 3 (Normal)
Message-ID: <18432.000119@vpcit.ru>
To: pgsql-general <pgsql-general@postgreSQL.org>
Subject: Re: [GENERAL] launguage...
In-reply-To: <Pine.LNX.4.10.10001181655540.30204-100000@mail.velenje.cx>
References: <Pine.LNX.4.10.10001181655540.30204-100000@mail.velenje.cx>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Hello Mitja,
Tuesday, January 18, 2000, 8:58:27 PM, you wrote:
MN> hello.
MN> i`m wondering how to set up an launguage for a certain database or how to
MN> set up a general launguage for the postgres?
MN> the problem is that slovenia (as most other contries) has some special
MN> charaters and i would like to sort the data containig this chars..
MN> therefore i need the launguage setings.
MN> thanks for your help!
MN> Mitja.
MN> ************
The postmaster.init file (in /etc/postgresql/ ) have a string
LANG=<your_language>, you have to write your locale there.
In order to use national characters, you have to compile postgres with
multibyte support and then use 'set client_encoding to ' statement in
applications.
Best regards,
Yury
From bouncefilter Wed Jan 19 00:46:33 2000
Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34])
by hub.org (8.9.3/8.9.3) with ESMTP id AAA18419
for <pgsql-general@postgreSQL.org>;
Wed, 19 Jan 2000 00:46:15 -0500 (EST) (envelope-from Inoue@tpf.co.jp)
Received: from cadzone ([126.0.1.40] (may be forged))
by sd.tpf.co.jp (2.5 Build 2640 (Berkeley 8.8.6)/8.8.4) with SMTP
id OAA02982; Wed, 19 Jan 2000 14:46:08 +0900
From: "Hiroshi Inoue" <Inoue@tpf.co.jp>
To: "John Brothers" <johnbr@mindspring.com>
Cc: <pgsql-general@postgreSQL.org>, <thoth@incanta.net>
Subject: RE: [GENERAL] Primary Key Indexing problem
Date: Wed, 19 Jan 2000 14:51:46 +0900
Message-ID: <001001bf6241$45359900$2801007e@tpf.co.jp>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Importance: Normal
In-Reply-To: <4.1.20000118195710.00c926c0@pop.mindspring.com>
-----Original Message-----
From: owner-pgsql-general@postgreSQL.org
[mailto:owner-pgsql-general@postgreSQL.org]On Behalf Of John BrothersHI all,
I have a script which generates a simple table, fills it with
about 11000 unique numbers, in the primary key field,
and then at the end, shows that the index has been
lost for at least one of the values, allowing another
entry to be made that duplicates the index. This is an
abstract simplification of a problem we are experiencing in
our field database.The compressed "script" is 64k - I didn't want to attach it to this
message, but I would be happy to send it to anyone who might
be able to use it to track down whatever bug(s) might be causing
it. (I am assuming that this is a bug. If not, please accept my
apologies).[PostgreSQL 6.5.1 on i686-pc-linux-gnu, compiled by gcc 2.7.2.3]
The following patch seems to fix your problem.
Regards.
Hiroshi Inoue
Inoue@tpf.co.jp
*** nbtcompare.c 2000/01/11 03:33:34 1.3
--- nbtcompare.c 2000/01/19 05:42:06
***************
*** 34,40 ****
int32
btint4cmp(int32 a, int32 b)
{
! return a - b;
} int32
--- 34,45 ----
int32
btint4cmp(int32 a, int32 b)
{
! if (a > b)
! return 1;
! else if (a == b)
! return 0;
! else
! return -1;
}int32
From bouncefilter Wed Jan 19 02:23:35 2000
Received: from news.tht.net (news.hub.org [216.126.91.242])
by hub.org (8.9.3/8.9.3) with ESMTP id CAA39014
for <pgsql-general@postgresql.org>;
Wed, 19 Jan 2000 02:23:23 -0500 (EST)
(envelope-from news@news.tht.net)
Received: (from news@localhost) by news.tht.net (8.9.3/8.9.3) id BAA81236
for pgsql-general@postgresql.org; Wed, 19 Jan 2000 01:55:39 -0500 (EST)
(envelope-from news)
X-Authentication-Warning: news.tht.net: news set sender to <news> using -f
From: mr_hopkins@earthlink.net (Micheal H.)
X-Newsgroups: comp.databases.postgresql.questions
Subject: examples not included
Message-ID: <38855eeb.10647304@news.earthlink.net>
X-Newsreader: Forte Free Agent 1.11/32.235
Lines: 18
X-Complaints-To: abuse@earthlink.net
X-Trace: newsread2.prod.itd.earthlink.net 948264869 209.178.128.79 (Tue,
18 Jan 2000 22:54:29 PST)
Organization: EarthLink Network, Inc.
X-ELN-Date: Tue Jan 18 22:54:29 2000
Date: Wed, 19 Jan 2000 06:54:29 GMT
To: pgsql-questions@postgresql.org
I recently installed postgresql-{server,devel,jdbc,odbc,perl}-6.5.3-2
and thought I see it referred to alot there was not a
/usr/doc/postgresql-6.5.3/examples directory included anywhere.
I just want a jdbc driver. I got the jdk1.1.8 that I'm running on my
win95 machine and I'd like to create a class that I can run anywhere.
postgresql-jdbc-6.5.3-2 did include jdbc6.5-1.2.jar and
jdbc6.5-1.1.jar based on rpm -ql postgresql-jdbc.
Can I get a clue as to where to get, preferrably a precompiled jdbc
driver and perhaps an indication as to how to use it. I've seen
places where the driver for postgresql is postgresql.Driver and others
where the name is postgresql95.Driver.
Thanks for the help,
I'm trying to help myself but I seem to be in need of a little nudge,
From bouncefilter Wed Jan 19 03:13:35 2000
Received: from mail.velenje.cx (root@[195.210.204.104])
by hub.org (8.9.3/8.9.3) with ESMTP id DAA78551
for <pgsql-general@postgreSQL.org>;
Wed, 19 Jan 2000 03:13:00 -0500 (EST)
(envelope-from mitja@mail.velenje.cx)
Received: from localhost (mitja@localhost)
by mail.velenje.cx (8.9.3/8.9.3) with ESMTP id JAA08233;
Wed, 19 Jan 2000 09:12:42 +0100
Date: Wed, 19 Jan 2000 09:12:41 +0100 (CET)
From: Mitja Novak <mitja@mail.velenje.cx>
To: yura <yura@vpcit.ru>
cc: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] launguage...
In-Reply-To: <18432.000119@vpcit.ru>
Message-ID: <Pine.LNX.4.10.10001190910070.29298-100000@mail.velenje.cx>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
ok.... but
if i want to use Norway language for one database and slovenian for other
and English again for the other one this isn`t posible to set ?
I`m looking for that kind of sulution.
On Wed, 19 Jan 2000, yura wrote:
Hello Mitja,
Tuesday, January 18, 2000, 8:58:27 PM, you wrote:
MN> hello.
MN> i`m wondering how to set up an launguage for a certain database or how to
MN> set up a general launguage for the postgres?
MN> the problem is that slovenia (as most other contries) has some special
MN> charaters and i would like to sort the data containig this chars..
MN> therefore i need the launguage setings.
MN> thanks for your help!MN> Mitja.
MN> ************
The postmaster.init file (in /etc/postgresql/ ) have a string
LANG=<your_language>, you have to write your locale there.
In order to use national characters, you have to compile postgres with
multibyte support and then use 'set client_encoding to ' statement in
applications.Best regards,
Yury************
From bouncefilter Wed Jan 19 03:46:35 2000
Received: from downtown.oche.de (root@downtown.oche.de [194.94.253.3])
by hub.org (8.9.3/8.9.3) with ESMTP id DAA86587
for <pgsql-general@postgresql.org>;
Wed, 19 Jan 2000 03:45:53 -0500 (EST)
(envelope-from mne@darwin.oche.de)
Received: (from uucp@localhost)
by downtown.oche.de (8.9.3/8.9.3/Debian/GNU) with UUCP id JAA21200
for postgresql.org!pgsql-general; Wed, 19 Jan 2000 09:42:08 +0100
Received: from localhost ([127.0.0.1] helo=darwin.oche.de ident=mne)
by darwin.oche.de with esmtp (Exim 3.12 #1 (Debian))
id 12AqNY-0002Dv-00
for <pgsql-general@postgreSQL.org>; Wed, 19 Jan 2000 09:22:13 +0100
Date: Wed, 19 Jan 2000 09:22:07 +0100 (CET)
From: Martin Neumann <mne@mne.de>
Subject: Unsigned Datatypes
To: pgsql-general@postgresql.org
MIME-Version: 1.0
Content-Type: TEXT/plain; CHARSET=US-ASCII
Message-Id: <E12AqNY-0002Dv-00@darwin.oche.de>
Are there any plans to implement unsigned datatypes like uint4 and
uint8?
In many cases I don't need the signed int4 numbers, but I would be happy
to have a larger range without using the twice as large int8 type.
--
Martin Neumann, Welkenrather Str. 118c, 52074 Aachen, Germany
mne@mne.de - http://www.mne.de/mne/ - sms@mne.de [eMail2SMS]
Tel. 0241 / 8876-080 - Mobil: 0173 / 27 69 632
.------.---------------------------------------------------------
| at | Inform GmbH - Abteilung Airport Logistics
| work | Pascalstr. 23 - 52076 Aachen - Tel. 02408 / 9456-0
|______| martin.neumann@inform-ac.com - http://www.inform-ac.com
From bouncefilter Wed Jan 19 07:09:37 2000
Received: from downtown.oche.de (root@downtown.oche.de [194.94.253.3])
by hub.org (8.9.3/8.9.3) with ESMTP id HAA34908
for <pgsql-general@postgresql.org>;
Wed, 19 Jan 2000 07:09:28 -0500 (EST) (envelope-from lists@mne.de)
Received: from darwin.oche.de (uucp@localhost)
by downtown.oche.de (8.9.3/8.9.3/Debian/GNU) with SMTP id NAA24052
for <pgsql-general@postgresql.org>; Wed, 19 Jan 2000 13:10:00 +0100
Received: from localhost ([127.0.0.1] helo=darwin.oche.de ident=mne)
by darwin.oche.de with smtp (Exim 3.12 #1 (Debian))
id 12Aqp2-0002HT-00
for <pgsql-general@postgresql.org>; Wed, 19 Jan 2000 09:50:58 +0100
From: Martin Neumann <mne@mne.de>
Subject: Unsigned Datatypes
To: pgsql-general@postgresql.org
Message-Id: <E12Aqp2-0002HT-00@darwin.oche.de>
Date: Wed, 19 Jan 2000 09:50:58 +0100
Are there any plans to implement unsigned datatypes like uint4 and
uint8?
In many cases I don't need the signed int4 numbers, but I would be happy
to have a larger range without using the twice as large int8 type.
--
Martin Neumann, Welkenrather Str. 118c, 52074 Aachen, Germany
mne@mne.de - http://www.mne.de/mne/ - sms@mne.de [eMail2SMS]
Tel. 0241 / 8876-080 - Mobil: 0173 / 27 69 632
------.---------------------------------------------------------
| at | Inform GmbH - Abteilung Airport Logistics
| work | Pascalstr. 23 - 52076 Aachen - Tel. 02408 / 9456-0
|______| martin.neumann@inform-ac.com - http://www.inform-ac.com
From bouncefilter Wed Jan 19 06:01:37 2000
Received: from vpcit.ru (qmailr@dialup.vpcit.ru [195.38.52.246])
by hub.org (8.9.3/8.9.3) with SMTP id GAA16953
for <pgsql-general@postgreSQL.org>;
Wed, 19 Jan 2000 06:01:16 -0500 (EST) (envelope-from yura@vpcit.ru)
Received: (qmail 13108 invoked from network); 19 Jan 2000 11:01:08 -0000
Received: from gercon.vpcit.ru (195.38.52.226)
by dialup.vpcit.ru with SMTP; 19 Jan 2000 11:01:08 -0000
Date: Wed, 19 Jan 2000 16:00:33 +0500
From: Yury Don <yura@vpcit.ru>
X-Mailer: The Bat! (v1.36) S/N F29DEE5D / Educational
Reply-To: Yury Don <yura@vpcit.ru>
Organization: Gercon
X-Priority: 3 (Normal)
Message-ID: <16667.000119@vpcit.ru>
To: pgsql-general <pgsql-general@postgreSQL.org>
Subject: Bug or my crooked hands?
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Hello All,
I have the following situation:
CREATE TABLE "Contacts" (
"Contact" Serial,
"StuffTheir" Text,
"TheDate" datetime DEFAULT TEXT 'now',
"Subj" Text,
"CustomerId" int4,
"Agent" int4,
"ActionType" int2,
"Done" bool default false not null,
"ExecutionDate" datetime,
"Whom" int4
);
Then I have inserted data from text file and do a query
mdb=> select Contact from Contacts;
Contacts
-----------
-2112563299
-2111287024
-2110307960
-2110004687
-2109773688
-2109611925
-2108616455
...
mdb=> select Contact from Contacts where Contact=-2112563299;
Contacts
-------
(0 rows)
What is this and how to treat it?
Best regards,
Yury ICQ 11831432
mailto:yura@vpcit.ru
From bouncefilter Wed Jan 19 06:20:37 2000
Received: from tango.SoftHome.net (tango.SoftHome.net [204.144.231.49])
by hub.org (8.9.3/8.9.3) with SMTP id GAA19418
for <pgsql-general@postgreSQL.org>;
Wed, 19 Jan 2000 06:20:12 -0500 (EST)
(envelope-from j.roeleveld@softhome.net)
Received: (qmail 30255 invoked by uid 417); 19 Jan 2000 11:22:10 -0000
Received: from sentec.demon.nl (HELO joost) (212.238.106.25)
by smtpb.softhome.net with SMTP; 19 Jan 2000 11:22:10 -0000
Message-ID: <012e01bf626f$055658a0$8402a8c0@sentec.demon.nl>
From: "J. Roeleveld" <j.roeleveld@softhome.net>
To: "Yury Don" <yura@vpcit.ru>, "pgsql-general" <pgsql-general@postgreSQL.org>
References: <16667.000119@vpcit.ru>
Subject: Re: [GENERAL] Bug or my crooked hands?
Date: Wed, 19 Jan 2000 12:19:12 +0100
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 5.00.2615.200
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200
Hello All,
I have the following situation:
CREATE TABLE "Contacts" (
"Contact" Serial,
"StuffTheir" Text,
"TheDate" datetime DEFAULT TEXT 'now',
"Subj" Text,
"CustomerId" int4,
"Agent" int4,
"ActionType" int2,
"Done" bool default false not null,
"ExecutionDate" datetime,
"Whom" int4
);Then I have inserted data from text file and do a query
mdb=> select Contact from Contacts;
Contacts
-----------
-2112563299
-2111287024
-2110307960
-2110004687
-2109773688
-2109611925
-2108616455
...
How did you get negative values in a Serial - field??
Serial-fields default to positive numbers....
From bouncefilter Wed Jan 19 06:33:37 2000
Received: from vpcit.ru (qmailr@dialup.vpcit.ru [195.38.52.246])
by hub.org (8.9.3/8.9.3) with SMTP id GAA24062
for <pgsql-general@postgreSQL.org>;
Wed, 19 Jan 2000 06:32:58 -0500 (EST) (envelope-from yura@vpcit.ru)
Received: (qmail 14029 invoked from network); 19 Jan 2000 11:32:49 -0000
Received: from gercon.vpcit.ru (195.38.52.226)
by dialup.vpcit.ru with SMTP; 19 Jan 2000 11:32:49 -0000
Date: Wed, 19 Jan 2000 16:32:12 +0500
From: Yury Don <yura@vpcit.ru>
X-Mailer: The Bat! (v1.36) S/N F29DEE5D / Educational
Reply-To: Yury Don <yura@vpcit.ru>
Organization: Gercon
X-Priority: 3 (Normal)
Message-ID: <19689.000119@vpcit.ru>
To: pgsql-general <pgsql-general@postgreSQL.org>
Subject: Re[2]Have services been enabled for port 5432? Check /etc/services. I have a pair of lines: postgres 5432/tcp # POSTGRES postgres 5432/udp # POSTGRES: [GENERAL] Bug or my crooked hands?
In-reply-To: <012e01bf626f$055658a0$8402a8c0@sentec.demon.nl>
References: <012e01bf626f$055658a0$8402a8c0@sentec.demon.nl>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Hello J.,
Once, Wednesday, January 19, 2000, 4:19:12 PM, you wrote:
Hello All,
I have the following situation:
CREATE TABLE "Contacts" (
"Contact" Serial,
"StuffTheir" Text,
"TheDate" datetime DEFAULT TEXT 'now',
"Subj" Text,
"CustomerId" int4,
"Agent" int4,
"ActionType" int2,
"Done" bool default false not null,
"ExecutionDate" datetime,
"Whom" int4
);Then I have inserted data from text file and do a query
mdb=> select Contact from Contacts;
Contacts
-----------
-2112563299
-2111287024
-2110307960
-2110004687
-2109773688
-2109611925
-2108616455
...
JR> How did you get negative values in a Serial - field??
JR> Serial-fields default to positive numbers....
I have copied the data from MS Access table and there where negative
values. I can't change data in field Contacts because they are using
in other tables.
--
Best regards,
Yury ICQ 11831432
mailto:yura@vpcit.ru
From bouncefilter Wed Jan 19 06:56:37 2000
Received: from mx-30.mail.knowledge.com (IDENT:root@office.knowledge.com
[195.40.167.196]) by hub.org (8.9.3/8.9.3) with ESMTP id GAA27022
for <pgsql-general@postgresql.org>;
Wed, 19 Jan 2000 06:56:04 -0500 (EST)
(envelope-from mark@office.knowledge.com)
Received: from mark (helo=localhost)
by mx-30.mail.knowledge.com with local-esmtp (Exim 3.12 #1)
id 12Athu-0006Ye-00; Wed, 19 Jan 2000 11:55:26 +0000
Date: Wed, 19 Jan 2000 11:55:26 +0000 (GMT)
From: Mark Jewiss <mark@knowledge.com>
To: Yury Don <yura@vpcit.ru>
cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Re[2]Have services been enabled for port 5432? Check /etc/services. I have a pair of lines: postgres 5432/tcp # POSTGRES postgres 5432/udp # POSTGRES: [GENERAL] Bug or my crooked hands?
In-Reply-To: <19689.000119@vpcit.ru>
Message-ID: <Pine.BSO.4.10.10001191153320.30635-100000@office.knowledge.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Sender: Mark Jewiss <mark@office.knowledge.com>
Hello,
On Wed, 19 Jan 2000, Yury Don wrote:
"Contact" Serial,
JR> How did you get negative values in a Serial - field??
JR> Serial-fields default to positive numbers....I have copied the data from MS Access table and there where negative
values. I can't change data in field Contacts because they are using
in other tables.
I'd go along with the previous statement - a serial should only be a
positive number.
What was the schema for the table in MS Access? You may have to change
that (by copying everything into a new table), and then moving it into
Postgres.
What are you using this column for anyway? Is it just an identifier?
Regards,
Mark.
--
Mark Jewiss
Knowledge Matters Limited
http://www.knowledge.com
Import Notes
Reply to msg id not found: 3BFA76DF.3DF40B3C@mmrd.com
I'm sort of a beginner myself so forgive me if this is wrong. But that
solution sort of assumes that data is being put into the tables at the
same time.
Wouldn't a better solution be to setup the first table like this.
people
------------------------------
| id | fname | lname | food_id | color_id |
------------------------------
| 1 | bob | smith | 2 | 3 |
------------------------------
Then you should be able to do a normal join type select and get the
right results no matter what.
Risko Peter wrote:
On Tue, 20 Nov 2001, Fran Fabrizio wrote:
This is a little off topic but this is the best source of SQL knowledge
I know about so hopefully this will be interesting enough for someone to
answer. :-)
I've got the following tables:
Table people
id fname lname
1 bob smith
2 tom jones
3 jane doe
4 mike porter
Table food
id favorite_food
2 eggrolls
3 ice cream
Table color
id color
1 red
3 blue
I want a query to produce the result set:
fname lname favorite_color favorite_food
bob smith red null
tom jones null eggrolls
jane doe blue ice cream
mike porter null null
I'm having lots of trouble getting the right result or knowing whether
this is even a valid usage of outer joins. Can somebody show me a
working query? Thanks!Hi Fran!
I'm a beginner, and maybe I will misinform you, but I think in the above
case you want your tables being joined by the ID column. In that case you
_should_ have a row in your auxiliary tables (color, food) for every
occuring IDs in the main table. It will solve your problem:
---
drop table people;
drop table food;
drop table color;
create table people(id int4,fname char(10),lname char(10));
create table food(id int4,favorite_food char(10));
create table color(id int4,color char(10));
copy food from stdin;
1
2 eggrolls
3 ice cream
4
\.
copy color from stdin;
1 red
2
3 blue
4
\.
copy people from stdin;
1 bob smith
2 tom jones
3 jane doe
4 mike porter
\.
select fname,lname,color,favorite_food from people,food,color where
people.id=food.id and people.id=color.id;Udv: rpetike
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
--
Brian Avis
SEARHC Medical Clinic
Juneau, AK 99801
(907) 463-4049
cd /pub
more beer
It would probably be better to set it up like this, if that's what you're
going to do:
id person_id food_id color_id
1 1 2 3
etc...
person
1 bob smith
etc...
this way if there are two people named bob smith you still can have a unique
person. Otherwise your table design breaks down at that point.
This is also probably the most normalized way to do it...
Note also that using this method, if you need to change a name, food, or
color, the changes will happen instantly across the whole table structure.
--Russell
----- Original Message -----
From: "Brian Avis" <brian.avis@searhc.org>
To: "Risko Peter" <rpetike@freemail.hu>
Cc: "Fran Fabrizio" <ffabrizio@mmrd.com>; <pgsql-general@postgresql.org>
Sent: Tuesday, November 20, 2001 9:13 AM
Subject: Re: [GENERAL] Slightly OT: outer joins
I'm sort of a beginner myself so forgive me if this is wrong. But that
solution sort of assumes that data is being put into the tables at the
same time.Wouldn't a better solution be to setup the first table like this.
people
------------------------------
| id | fname | lname | food_id | color_id |
------------------------------
| 1 | bob | smith | 2 | 3 |
------------------------------Then you should be able to do a normal join type select and get the
right results no matter what.Risko Peter wrote:
On Tue, 20 Nov 2001, Fran Fabrizio wrote:
This is a little off topic but this is the best source of SQL knowledge
I know about so hopefully this will be interesting enough for someone to
answer. :-)
I've got the following tables:
Table people
id fname lname
1 bob smith
2 tom jones
3 jane doe
4 mike porter
Table food
id favorite_food
2 eggrolls
3 ice cream
Table color
id color
1 red
3 blue
I want a query to produce the result set:
fname lname favorite_color favorite_food
bob smith red null
tom jones null eggrolls
jane doe blue ice cream
mike porter null null
I'm having lots of trouble getting the right result or knowing whether
this is even a valid usage of outer joins. Can somebody show me a
working query? Thanks!Hi Fran!
I'm a beginner, and maybe I will misinform you, but I think in the
above
case you want your tables being joined by the ID column. In that case you
_should_ have a row in your auxiliary tables (color, food) for every
occuring IDs in the main table. It will solve your problem:
---
drop table people;
drop table food;
drop table color;
create table people(id int4,fname char(10),lname char(10));
create table food(id int4,favorite_food char(10));
create table color(id int4,color char(10));
copy food from stdin;
1
2 eggrolls
3 ice cream
4
\.
copy color from stdin;
1 red
2
3 blue
4
\.
copy people from stdin;
1 bob smith
2 tom jones
3 jane doe
4 mike porter
\.
select fname,lname,color,favorite_food from people,food,color where
people.id=food.id and people.id=color.id;Udv: rpetike
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly--
Brian Avis
SEARHC Medical Clinic
Juneau, AK 99801
(907) 463-4049
cd /pub
more beer
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.298 / Virus Database: 161 - Release Date: 11/13/01
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com