Bug in CEIL?
Is the following normal behaviour? I would have expected a result of 0.
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 6.5.3 on i586-pc-sco3.2v5.0.4, compiled by gcc 2.7.2.3]
type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: template1
template1=> select ceil(0);
ceil
----
1
(1 row)
"Mark Alliban" <MarkA@idnltd.com> writes:
template1=> select ceil(0);
ceil
----
1
(1 row)
Looks like a bug to me too. I've just been mucking in the NUMERIC
code, so will put this on my to-do list.
BTW, your mail would be easier for other folks to read if you turned
off all the MIME/HTML junk.
regards, tom lane
From bouncefilter Wed Jan 19 10:45:40 2000
Received: from sec.secrel.com.br ([200.194.96.34])
by hub.org (8.9.3/8.9.3) with SMTP id KAA91261
for <pgsql-general@postgresql.org>;
Wed, 19 Jan 2000 10:44:53 -0500 (EST)
(envelope-from luis@secrel.com.br)
Received: (qmail 12917 invoked from network); 19 Jan 2000 12:44:47 -0000
Received: from secnet.secrel.com.br (HELO secrel.com.br) (200.239.126.195)
by sec.secrel.com.br with SMTP; 19 Jan 2000 12:44:47 -0000
Message-ID: <3885DA61.C128038E@secrel.com.br>
Date: Wed, 19 Jan 2000 13:38:09 -0200
From: Luis Bezerra <luis@secrel.com.br>
Reply-To: luis@secrel.com.br
Organization: Secrelnet
X-Mailer: Mozilla 4.5 [en] (Win98; I)
X-Accept-Language: en
MIME-Version: 1.0
To: PostgreSQL-general <pgsql-general@postgresql.org>
Subject: (no subject)
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
How do I unsubscribe of this list
--
-----------------------------
Lu�s Bezerra de A. Junior
luis@secrel.com.br
SecrelNet Inform�tica LTDA
Fortaleza - Cear� - Brasil
Fone: 021852882090
-----------------------------
From bouncefilter Wed Jan 19 12:09:41 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 MAA28704
for <pgsql-general@postgreSQL.org>;
Wed, 19 Jan 2000 12:09:34 -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
MAA08625;
Wed, 19 Jan 2000 12:07:15 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200001191707.MAA08625@candle.pha.pa.us>
Subject: Re: [GENERAL] Re: [SQL] Bug in CEIL?
In-Reply-To: <002101bf6286$419e5b20$ce2a18c3@skillbrokers.bg> from Nikolay
Mijaylov at "Jan 19, 2000 04:05:33 pm"
To: Nikolay Mijaylov <nmmm@nmmm.nu>
Date: Wed, 19 Jan 2000 12:07:15 -0500 (EST)
CC: Mark Alliban <MarkA@idnltd.com>,
pgsql-general <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
[Charset iso-8859-1 unsupported, filtering to ASCII...]
PostgreSQL 6.4.2 :
---------------------
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQLtype \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: rootroot=> select ceil(0);
ERROR: No such function 'ceil' with the specified attributes
Current sources show:
test=> select ceil(0);
ceil
------
1
(1 row)
--
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 Wed Jan 19 12:44:41 2000
Received: from portal.west.saic.com (portal.west.saic.com [198.151.12.15])
by hub.org (8.9.3/8.9.3) with SMTP id MAA39023
for <pgsql-general@postgreSQL.org>;
Wed, 19 Jan 2000 12:44:00 -0500 (EST)
(envelope-from officers@aries.tucson.saic.com)
Received: from gatespass.tucson.saic.com by portal.west.saic.com
via smtpd (for postgresql.org [216.126.84.28]) with SMTP;
19 Jan 2000 17:43:59 UT
Received: (from adm@localhost) by gatespass.tucson.saic.com (8.8.2/8.8.2) id
KAA01726 for <pgsql-general@postgreSQL.org>;
Wed, 19 Jan 2000 10:43:57 -0700 (MST)
X-Authentication-Warning: gatespass.tucson.saic.com: adm set sender to
<officers@aries.tucson.saic.com> using -f
Received: from wasp.tucson.saic.com(139.121.3.171) by
gatespass.tucson.saic.com via smap (V2.0)
id xma001722; Wed, 19 Jan 00 10:43:30 -0700
Received: from aries.tucson.saic.com (localhost [127.0.0.1]) by
wasp.tucson.saic.com (980427.SGI.8.8.8/950213.SGI.AUTOCF) via
ESMTP id KAA59100 for <pgsql-general@postgreSQL.org>;
Wed, 19 Jan 2000 10:43:28 -0700 (MST)
Sender: officers@aries.tucson.saic.com
Message-ID: <3885F7BF.D348402A@aries.tucson.saic.com>
Date: Wed, 19 Jan 2000 10:43:27 -0700
From: Sarah Officer <officers@aries.tucson.saic.com>
X-Mailer: Mozilla 4.07C-SGI [en] (X11; I; IRIX 6.5 IP32)
MIME-Version: 1.0
To: pgsql-general@postgreSQL.org
Subject: psql problem describing tables
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
I am running postgres 6.5.3 on an SGI. I haven't done much except
create a few tables and indexes. When I enter psql and type '\d',
all my tables and indexes are listed. When I type '\d tablename'
however, I get the following message:
\d Images
ERROR: typeidTypeRelid: Invalid type - oid = 0
I can select from the table, but there's no data in it. I have the
same problem with all my tables, and I can't describe indexes
either. Any suggestions?
Thanks,
Sarah Officer
officers@aries.tucson.saic.com
From bouncefilter Wed Jan 19 13:06:41 2000
Received: from portal.west.saic.com (portal.west.saic.com [198.151.12.15])
by hub.org (8.9.3/8.9.3) with SMTP id NAA47502
for <pgsql-general@postgreSQL.org>;
Wed, 19 Jan 2000 13:06:01 -0500 (EST)
(envelope-from officers@aries.tucson.saic.com)
Received: from gatespass.tucson.saic.com by portal.west.saic.com
via smtpd (for postgresql.org [216.126.84.28]) with SMTP;
19 Jan 2000 18:06:00 UT
Received: (from adm@localhost) by gatespass.tucson.saic.com (8.8.2/8.8.2) id
LAA01838 for <pgsql-general@postgreSQL.org>;
Wed, 19 Jan 2000 11:05:58 -0700 (MST)
X-Authentication-Warning: gatespass.tucson.saic.com: adm set sender to
<officers@aries.tucson.saic.com> using -f
Received: from wasp.tucson.saic.com(139.121.3.171) by
gatespass.tucson.saic.com via smap (V2.0)
id xma001834; Wed, 19 Jan 00 11:05:46 -0700
Received: from aries.tucson.saic.com (localhost [127.0.0.1]) by
wasp.tucson.saic.com (980427.SGI.8.8.8/950213.SGI.AUTOCF) via
ESMTP id LAA59008; Wed, 19 Jan 2000 11:05:44 -0700 (MST)
Sender: officers@aries.tucson.saic.com
Message-ID: <3885FCF8.3E69D971@aries.tucson.saic.com>
Date: Wed, 19 Jan 2000 11:05:44 -0700
From: Sarah Officer <officers@aries.tucson.saic.com>
X-Mailer: Mozilla 4.07C-SGI [en] (X11; I; IRIX 6.5 IP32)
MIME-Version: 1.0
To: Sarah Officer <SARAH.M.OFFICER@saic.com>
CC: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] psql problem describing tables
References: <3885F7BF.D348402A@aries.tucson.saic.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Let me clarify. The reason there is not data in my table is because
I haven't inserted any yet. I inserted a row of data. It gets
selected correctly, but I still can't describe the table. So the
problem doesn't seem to be related to having an empty table.
Sarah
Sarah Officer wrote:
I am running postgres 6.5.3 on an SGI. I haven't done much except
create a few tables and indexes. When I enter psql and type '\d',
all my tables and indexes are listed. When I type '\d tablename'
however, I get the following message:\d Images
ERROR: typeidTypeRelid: Invalid type - oid = 0I can select from the table, but there's no data in it. I have the
same problem with all my tables, and I can't describe indexes
either. Any suggestions?Thanks,
Sarah Officer
officers@aries.tucson.saic.com************
From bouncefilter Wed Jan 19 13:28:42 2000
Received: from portal.west.saic.com (portal.west.saic.com [198.151.12.15])
by hub.org (8.9.3/8.9.3) with SMTP id NAA51865
for <pgsql-general@postgreSQL.org>;
Wed, 19 Jan 2000 13:28:02 -0500 (EST)
(envelope-from officers@aries.tucson.saic.com)
Received: from gatespass.tucson.saic.com by portal.west.saic.com
via smtpd (for postgresql.org [216.126.84.28]) with SMTP;
19 Jan 2000 18:28:01 UT
Received: (from adm@localhost) by gatespass.tucson.saic.com (8.8.2/8.8.2) id
LAA01961; Wed, 19 Jan 2000 11:27:59 -0700 (MST)
X-Authentication-Warning: gatespass.tucson.saic.com: adm set sender to
<officers@aries.tucson.saic.com> using -f
Received: from wasp.tucson.saic.com(139.121.3.171) by
gatespass.tucson.saic.com via smap (V2.0)
id xma001957; Wed, 19 Jan 00 11:27:55 -0700
Received: from aries.tucson.saic.com (localhost [127.0.0.1]) by
wasp.tucson.saic.com (980427.SGI.8.8.8/950213.SGI.AUTOCF) via
ESMTP id LAA59169; Wed, 19 Jan 2000 11:27:53 -0700 (MST)
Sender: officers@aries.tucson.saic.com
Message-ID: <38860229.27F4DCF4@aries.tucson.saic.com>
Date: Wed, 19 Jan 2000 11:27:53 -0700
From: Sarah Officer <officers@aries.tucson.saic.com>
X-Mailer: Mozilla 4.07C-SGI [en] (X11; I; IRIX 6.5 IP32)
MIME-Version: 1.0
To: pgsql-general@postgreSQL.org
CC: officers@aries.tucson.saic.com
Subject: Trigger problem
References: <3885F7BF.D348402A@aries.tucson.saic.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
I am trying to create a simple trigger function. With some help
from the mailing list, I managed to create a trigger and functions.
Unfortunately I get an error message when I delete from the table
which has the trigger. Can anyone help me spot the error? Here's
what I have done:
CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
'/achilles_usr12/mars/swl/IRIX6.5/lib/plpgsql.so' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';
create table Images (
id varchar(100) PRIMARY KEY,
title varchar(25) NOT NULL,
filepath varchar(256) NOT NULL UNIQUE,
status_code varchar(5) NOT NULL
) ;
create table Istatus (
status_code varchar(5) PRIMARY KEY,
status_desc varchar(100) NOT NULL
);
CREATE FUNCTION remove_status_func()
RETURNS opaque AS '
delete from Images
where Images.status_code = old.status_code ;
select 1 as val;
' LANGUAGE 'plpgsql' ;
CREATE TRIGGER Istatus_delete_trigger
AFTER DELETE ON Istatus
FOR EACH ROW
EXECUTE PROCEDURE remove_status_func() ;
Insert into Istatus(status_code, status_desc)
values('A1', 'A1 Desc');
Insert into Istatus(status_code, status_desc)
values('A2', 'A2 Desc');
Insert into Istatus(status_code, status_desc)
values('A3', 'A3 Desc');
Insert into Images(id, title, filepath, status_code)
values ('ID1', 'First Image', '/usr/local/foo.gif', 'A1');
Insert into Images(id, title, filepath, status_code)
values ('ID2', 'Another Image', '/usr/local/bar.gif', 'A2');
select * from istatus;
status_code|status_desc
-----------+-----------
A1 |A1 Desc
A2 |A2 Desc
A3 |A3 Desc
(3 rows)
select * from images;
id |title |filepath |status_code
---+-------------+------------------+-----------
ID1|First Image |/usr/local/foo.gif|A1
ID2|Another Image|/usr/local/bar.gif|A2
(2 rows)
delete from istatus where status_code = 'A1';
ERROR: fmgr_info: function 18848: cache lookup failed
What is the problem with the cache lookup? Any suggestions would be
appreciated.
Sarah Officer
officers@aries.tucson.saic.com
From bouncefilter Wed Jan 19 14:13:43 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 OAA66629
for <pgsql-general@postgresql.org>;
Wed, 19 Jan 2000 14:13:36 -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); Wed, 19 Jan 2000 13:04:10 -0600
Sender: ed
Message-ID: <38860D43.9B5C6782@austin.rr.com>
Date: Wed, 19 Jan 2000 13:15:15 -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: Sarah Officer <officers@aries.tucson.saic.com>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Trigger problem
References: <3885F7BF.D348402A@aries.tucson.saic.com>
<38860229.27F4DCF4@aries.tucson.saic.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Sarah Officer wrote:
delete from istatus where status_code = 'A1';
ERROR: fmgr_info: function 18848: cache lookup failed
What is the problem with the cache lookup? Any suggestions would be
appreciated.
I seem to recall that kind of message often shows up when you have
dropped and recreated the function, thinking that the trigger would be
able to find your new function, when in fact it cannot. If you
drop/recreate the function, you must then also drop/recreate the
trigger. Bummer, but I believe that is going to be fixed sometime
soon IIRC.
Cheers,
Ed Loehr
From bouncefilter Wed Jan 19 15:29:43 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 PAA01591
for <pgsql-general@postgresql.org>;
Wed, 19 Jan 2000 15:29: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 VAA32274
for <pgsql-general@postgresql.org>; Wed, 19 Jan 2000 21:30: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 12B1Zz-0002eo-00
for <pgsql-general@postgresql.org>; Wed, 19 Jan 2000 21:20:19 +0100
From: Martin Neumann <lists@mne.de>
Subject: Unsigned Datatypes
To: pgsql-general@postgresql.org
Message-Id: <E12B1Zz-0002eo-00@darwin.oche.de>
Date: Wed, 19 Jan 2000 21:20:19 +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 16:48:44 2000
Received: from gw3.spectrumtelecorp.com
(IDENT:root@dw-spec-t1-spec.mainstream.net [206.28.149.13])
by hub.org (8.9.3/8.9.3) with ESMTP id QAA27749
for <pgsql-general@postgresql.org>;
Wed, 19 Jan 2000 16:48:12 -0500 (EST)
(envelope-from mikeo@spectrumtelecorp.com)
Received: from spec34 (spec34.spectrumtelecorp.com [192.168.1.67])
by gw3.spectrumtelecorp.com (8.8.7/8.8.7) with SMTP id QAA13019
for <pgsql-general@postgresql.org>; Wed, 19 Jan 2000 16:48:10 -0500
Message-Id: <3.0.1.32.20000119164833.0091c7b0@pop.spectrumtelecorp.com>
X-Sender: mikeo@pop.spectrumtelecorp.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Wed, 19 Jan 2000 16:48:33 -0500
To: pgsql-general@postgresql.org
From: mikeo <mikeo@spectrumtelecorp.com>
Subject: sharing tables between databases
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
hi,
we're attempting to parallel postgres with our oracle database
as part of a decision on whether or not to also use postgres as
a database. one of the things that can be done in oracle is the
ability to see objects owned by another user, a database in postgres
as i understand it. so if i have database 'x' with table 'x1' and database
'y' with table 'y1' is there a way for 'x.x1' to be seen by 'y'?
TIA,
mikeo
From bouncefilter Wed Jan 19 17:01:44 2000
Received: from portal.west.saic.com (portal.west.saic.com [198.151.12.15])
by hub.org (8.9.3/8.9.3) with SMTP id RAA30807
for <pgsql-general@postgreSQL.org>;
Wed, 19 Jan 2000 17:00:44 -0500 (EST)
(envelope-from officers@aries.tucson.saic.com)
Received: from gatespass.tucson.saic.com by portal.west.saic.com
via smtpd (for postgresql.org [216.126.84.28]) with SMTP;
19 Jan 2000 22:00:43 UT
Received: (from adm@localhost) by gatespass.tucson.saic.com (8.8.2/8.8.2) id
PAA03981; Wed, 19 Jan 2000 15:00:42 -0700 (MST)
X-Authentication-Warning: gatespass.tucson.saic.com: adm set sender to
<officers@aries.tucson.saic.com> using -f
Received: from wasp.tucson.saic.com(139.121.3.171) by
gatespass.tucson.saic.com via smap (V2.0)
id xma003978; Wed, 19 Jan 00 15:00:28 -0700
Received: from aries.tucson.saic.com (localhost [127.0.0.1]) by
wasp.tucson.saic.com (980427.SGI.8.8.8/950213.SGI.AUTOCF) via
ESMTP id PAA59366; Wed, 19 Jan 2000 15:00:25 -0700 (MST)
Sender: officers@aries.tucson.saic.com
Message-ID: <388633F9.C3295390@aries.tucson.saic.com>
Date: Wed, 19 Jan 2000 15:00:25 -0700
From: Sarah Officer <officers@aries.tucson.saic.com>
X-Mailer: Mozilla 4.07C-SGI [en] (X11; I; IRIX 6.5 IP32)
MIME-Version: 1.0
To: pgsql-general@postgreSQL.org
CC: officers@aries.tucson.saic.com
Subject: Creating Triggers
References: <3885F7BF.D348402A@aries.tucson.saic.com>
<38860229.27F4DCF4@aries.tucson.saic.com>
<38860D43.9B5C6782@austin.rr.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Thanks to Ed Loehr and others on the group, I finally was able to
create triggers in my database. This is a summary of what I learned
in the process. For the most part, I didn't find this in the
documentation. If anything here is incorrect, please let me know.
If not, can it be put in documentation somewhere? or in the FAQ?
- The actual working code for a trigger must be put into a function
which is called by the trigger. [This *is* in the docs]
- If the trigger function needs access to rows which are affected by
the insert/update/delete, the trigger function must use plpgsql as a
language. A sql function cannot access the special 'old' and 'new'
rows.
- Before creating a function in plpgsql, a handler and trusted
language must be created. Example syntax:
CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
'/install/lib/path/plpgsql.so' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';
- The return type for a plpgsql function must be opaque.
- A value must be returned if a return type is specified. The old &
new records are available as return values from the plpgsql
function.
- The body of a plpgsql function looks like sql except for reference
to old and new. The SQL part of the function must be enclosed with
'begin' and 'end;' or there will be a compiler error at run time.
- Example triggers and plpgsql functions can be found in the
postgres subdirectory: src/test/regress/sql.
- If a trigger function is dropped and recreated, the corresponding
trigger must also be dropped and recreated. Otherwise postgres
6.5.3 will give a runtime error that the cache lookup failed.
From bouncefilter Wed Jan 19 17:38:44 2000
Received: from gauss.categoricalsolutions.com.au
(IDENT:root@p43-nas4.syd.ihug.com.au [206.17.112.107])
by hub.org (8.9.3/8.9.3) with ESMTP id RAA46647
for <pgsql-general@postgresql.org>;
Wed, 19 Jan 2000 17:38:32 -0500 (EST)
(envelope-from sean@categoricalsolutions.com.au)
Received: from Bernoulli (bernoulli.categoricalsolutions.com.au [192.168.1.3])
by gauss.categoricalsolutions.com.au (8.8.7/8.8.7) with SMTP id
JAA05066; Thu, 20 Jan 2000 09:34:35 +1100
From: "Sean Carmody" <sean@categoricalsolutions.com.au>
To: "Gabriel Fernandez" <gabi@unica.edu>
Cc: <pgsql-general@postgresql.org>
Subject: RE: [GENERAL] Problems with operator '%' within a select
Date: Thu, 20 Jan 2000 09:34:45 +1100
Message-ID: <001301bf62cd$62b4d860$0301a8c0@categoricalsolutions.com.au>
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.2232.26
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
In-reply-To: <3885CBCE.E29A42CB@unica.edu>
Importance: Normal
Gabriel Fernandez wrote:
[Snip]
I've just had some problems using the operator '%' within a select.
This is an example:"select codigo from codis where codigo like '%3'"
And i've found that all the rows which contains more than one three
won't be selected.
(For example: values 'a34ree3', '34fg3')It seems as if you put only one character with '%' it will select only
the rows wich contain only one time this character.
[Snip]
On the face of it, it looks as though the problem you were having was as a
result of the % acting in a very minimal fashion, matching the first
'xxxx3' and then failing because the 3 it matched was not the end of the
string. Having said that, that's not how I would expect % to behave, and in
fact I can't seem to reproduce your problem. On release 6.5.2, I had the
following results:
test=> select str from test;
str
-----
abc3
a3bc3
a3bc2
a3bcx
a4bcx
(5 rows)
test=> select str from test where str like '%3';
str
-----
abc3
a3bc3
(2 rows)
What release are you using? Either there's a bug in the version you're
using, or there's something else going on.
Sean.
From bouncefilter Wed Jan 19 17:43:45 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 RAA48421
for <pgsql-general@postgresql.org>;
Wed, 19 Jan 2000 17:43:23 -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); Wed, 19 Jan 2000 16:33:58 -0600
Sender: ed
Message-ID: <38863E6E.1D0BFADB@austin.rr.com>
Date: Wed, 19 Jan 2000 16:45:02 -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: Sarah Officer <officers@aries.tucson.saic.com>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Creating Triggers
References: <3885F7BF.D348402A@aries.tucson.saic.com>
<38860229.27F4DCF4@aries.tucson.saic.com>
<38860D43.9B5C6782@austin.rr.com>
<388633F9.C3295390@aries.tucson.saic.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Sarah Officer wrote:
- A value must be returned if a return type is specified. The old &
new records are available as return values from the plpgsql
function.
Unfortunately, OLD and NEW are only available in the function that's
directly called by the trigger, not subsequent functions in the call
chain, IIRC.
- The body of a plpgsql function looks like sql except for reference
to old and new. The SQL part of the function must be enclosed with
'begin' and 'end;' or there will be a compiler error at run time.
What goes between 'begin' and 'end' are PL/pgSQL statements, of which
SQL is almost a subset. PL/pgSQL also has a number of plain vanilla
programming language constructs (if-then, loops, etc.).
- If a trigger function is dropped and recreated, the corresponding
trigger must also be dropped and recreated. Otherwise postgres
6.5.3 will give a runtime error that the cache lookup failed.
More generally, any function that gets dropped/recreated requires all
the functions/triggers above it in the call chain to be recreated,
IIRC.
Cheers,
Ed Loehr
From bouncefilter Wed Jan 19 17:51:45 2000
Received: from inter.interservice.com.mx
(IDENT:randrade@inter.interservice.com.mx [200.15.12.20])
by hub.org (8.9.3/8.9.3) with ESMTP id RAA51299
for <pgsql-general@postgreSQL.org>;
Wed, 19 Jan 2000 17:51:22 -0500 (EST)
(envelope-from randrade@inter.interservice.com.mx)
Received: from localhost (randrade@localhost)
by inter.interservice.com.mx (8.8.7/8.8.7) with ESMTP id RAA14780
for <pgsql-general@postgreSQL.org>; Wed, 19 Jan 2000 17:08:44 -0600
Date: Wed, 19 Jan 2000 17:08:44 -0600 (CST)
From: "Ing. Roberto Andrade Fonseca" <randrade@interservice.com.mx>
To: pgsql-general@postgreSQL.org
Subject: Benchmarks for pgsql?
Message-ID:
<Pine.LNX.4.10.10001191706070.14726-100000@inter.interservice.com.mx>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Hi:
I'm looking for some benchmarks for pgsql, better if they use DBI/DBI, so we
could compare psqql versus others DBMS.
Is thete any on the net or someone working on it?
Saludos,
Roberto Andrade Fonseca
randrade@abl.com.mx
From bouncefilter Wed Jan 19 19:23:46 2000
Received: from mail.presys.com (mail.presys.com [64.5.0.150])
by hub.org (8.9.3/8.9.3) with SMTP id TAA88618
for <pgsql-general@postgreSQL.org>;
Wed, 19 Jan 2000 19:23:21 -0500 (EST)
(envelope-from hitesh@pciwest.net)
Received: (qmail 3519 invoked from network); 20 Jan 2000 00:24:02 -0000
Received: from thehog.bk.pciwest.net (HELO pciwest.net) (64.5.4.153)
by mail.presys.com with SMTP; 20 Jan 2000 00:24:02 -0000
Sender: root
Message-ID: <38865574.1464E648@pciwest.net>
Date: Wed, 19 Jan 2000 16:23:16 -0800
From: Hitesh Patel <hitesh@pciwest.net>
X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.14 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: "pgsql-general@postgreSQL.org" <pgsql-general@postgreSQL.org>
Subject: weird sum() results
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
I have a table with a filed named 'amount' and all the values are
positive values, but if i do this:
select sum(amount) from table where state = 'CA';
I get a negative number. Doing a
select name, amount from table where state = 'CA' and amount < '0';
Returns 0 rows. Any ideas as to why i'm getting a negative value for
the sum()?
From bouncefilter Wed Jan 19 19:30:46 2000
Received: from genomics1.biosourcetechnologies.com ([209.179.110.105])
by hub.org (8.9.3/8.9.3) with ESMTP id TAA90859
for <pgsql-general@postgresql.org>;
Wed, 19 Jan 2000 19:30:00 -0500 (EST)
(envelope-from gary.wolfe@biosourcetechnologies.com)
Received: from biosourcetechnologies.com (vaca2 [10.0.0.12]) by
genomics1.biosourcetechnologies.com with SMTP (Microsoft
Exchange Internet Mail Service Version 5.5.2448.0)
id C9Y0AA4Q; Wed, 19 Jan 2000 16:20:56 -0800
Sender: gwolfe
Message-ID: <388656FD.90EB18DB@biosourcetechnologies.com>
Date: Wed, 19 Jan 2000 16:29:49 -0800
From: "gary.wolfe" <gary.wolfe@biosourcetechnologies.com>
X-Mailer: Mozilla 4.04 [en] (X11; I; SunOS 5.6 sun4u)
MIME-Version: 1.0
To: pgsql-general@postgresql.org
Subject: xml
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
When will postgres give XML output?
Gary
From bouncefilter Wed Jan 19 16:29:44 2000
Received: from hercules.exen.cl (IDENT:root@hercules.exen.cl [200.29.20.3])
by hub.org (8.9.3/8.9.3) with ESMTP id QAA19363
for <pgsql-general@postgresql.org>;
Wed, 19 Jan 2000 16:29:19 -0500 (EST) (envelope-from ventas@exen.cl)
Received: from exen.cl (est1.exen.cl [200.29.20.5])
by hercules.exen.cl (8.9.3/8.9.3) with ESMTP id OAA00742
for <pgsql-general@postgresql.org>; Wed, 19 Jan 2000 14:35:14 -0300
Message-ID: <388657BB.E627108D@exen.cl>
Date: Wed, 19 Jan 2000 18:33:00 -0600
From: "Cristian Stuardo I." <ventas@exen.cl>
X-Mailer: Mozilla 4.06 [en] (Win98; I)
MIME-Version: 1.0
To: pgsql-general@postgresql.org
Subject: alter question
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
is there a way to alter a pgsql database to delete an entire column,?
a kind of opposite to "ALTER table ADD"?
please reply to rverdejo@exen.cl , i'm not suscribed to the mailing list
thanks in advance, hope you can understand my weird english
.
From bouncefilter Wed Jan 19 19:33:46 2000
Received: from mail.presys.com (mail.presys.com [64.5.0.150])
by hub.org (8.9.3/8.9.3) with SMTP id TAA94078
for <pgsql-general@postgreSQL.org>;
Wed, 19 Jan 2000 19:33:07 -0500 (EST)
(envelope-from hitesh@presys.com)
Received: (qmail 4345 invoked from network); 20 Jan 2000 00:33:51 -0000
Received: from thehog.bk.pciwest.net (HELO presys.com) (64.5.4.153)
by mail.presys.com with SMTP; 20 Jan 2000 00:33:51 -0000
Sender: root
Message-ID: <388657C2.2697A6CE@presys.com>
Date: Wed, 19 Jan 2000 16:33:06 -0800
From: Hitesh Patel <hitesh@presys.com>
X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.14 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: "pgsql-general@postgreSQL.org" <pgsql-general@postgreSQL.org>
Subject: weird sum() results
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
I have a table with a filed named 'amount' and all the values are
positive values, but if i do this:
select sum(amount) from table where state = 'CA';
I get a negative number. Doing a
select name, amount from table where state = 'CA' and amount < '0';
Returns 0 rows. Any ideas as to why i'm getting a negative value for
the sum()?
From bouncefilter Thu Jan 20 01:45: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 BAA43841
for <pgsql-general@postgresql.org>;
Thu, 20 Jan 2000 01:44:25 -0500 (EST)
(envelope-from admin@wtbwts.com)
Received: by pcr.ca (Postfix, from userid 1000)
id 93B521FEE; Thu, 20 Jan 2000 01:43:36 +0000 (GMT)
Received: from localhost (localhost [127.0.0.1])
by pcr.ca (Postfix) with ESMTP id 7DD271FEC
for <pgsql-general@postgresql.org>;
Thu, 20 Jan 2000 01:43:36 +0000 (GMT)
Date: Thu, 20 Jan 2000 01:43:36 +0000 (GMT)
From: Marc Tardif <admin@wtbwts.com>
X-Sender: admin@server.b0x.com
To: pgsql-general@postgresql.org
Subject: apache logs to pgsql
Message-ID: <Pine.BSF.4.10.10001200124250.12998-100000@server.b0x.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
In the contrib directory, there is an example on how to redirect apache
logs to a postgresql database. It says to create a table and change two
lines in the apache configuration file to use psql for copying the
logs to the database.
My question is: using an sql db is mostly compelling for very active
sites but, if this is the case, is it sensible to use psql?
First, I find that using the psql command could have too much overhead.
Seeing the executable is 46696 bytes and dynamically linked, I'm not
really sure what is the strain on the system for bringing up psql.
Second, I have tried to see the alternative of writing an apache module
or, more sensibly, changing the current module responsible for logging.
After reading mod_log_config.c, I'm not really sure how I can integrate
libpq and avoid having to connect and disconnect repeatedly. If I can't
somehow sustain a connection, I imagine the module would be just as
efficient as using psql.
Lastly, I'm confused and overwhelmed by the size of the task at hand. I'd
appreciate any suggestions or recommendations or thoughts on whether
writing a module is even worthwhile at all.
Thanks,
Marc
From bouncefilter Wed Jan 19 22:19:48 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 WAA81050
for <pgsql-general@postgresql.org>;
Wed, 19 Jan 2000 22:19:05 -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 XAA58231
for <pgsql-general@postgresql.org>;
Wed, 19 Jan 2000 23:19:41 -0400 (AST) (envelope-from jeffm@pgsql.com)
X-Authentication-Warning: rage.hub.org: jeffm owned process doing -bs
Date: Wed, 19 Jan 2000 23:19:40 -0400 (AST)
From: "Jeff MacDonald <jeff@pgsql.com>" <jeffm@pgsql.com>
X-Sender: jeffm@rage.hub.org
Reply-To: Jeff MacDonald <jeff@pgsql.com>
To: pgsql-general@postgresql.org
Subject: parser
Message-ID: <Pine.BSF.4.10.10001192318550.18995-100000@rage.hub.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
hi, got this question from a user, anyone up for some discussion on it
1) I have been using 6.3 and recently tried to
move to 6.5. I found that the word "offset" is now a reserved word and I have used it as a field name in
a table. 6.5 reports this as an error. I wonder if the parser should do this since the context is as a
field name.
======================================================
Jeff MacDonald
jeff@pgsql.com irc: bignose on EFnet
======================================================
From bouncefilter Thu Jan 20 00:01:22 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 AAA09024
for <pgsql-general@postgresql.org>;
Thu, 20 Jan 2000 00:00:40 -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); Wed, 19 Jan 2000 22:51:20 -0600
Sender: ed
Message-ID: <388696E0.FE4B3D7B@austin.rr.com>
Date: Wed, 19 Jan 2000 23:02:24 -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: Hitesh Patel <hitesh@presys.com>
CC: "pgsql-general@postgreSQL.org" <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] weird sum() results
References: <388657C2.2697A6CE@presys.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Hitesh Patel wrote:
I have a table with a filed named 'amount' and all the values are
positive values, but if i do this:select sum(amount) from table where state = 'CA';
I get a negative number. Doing a
select name, amount from table where state = 'CA' and amount < '0';
Returns 0 rows. Any ideas as to why i'm getting a negative value for
the sum()?
Sounds just like "integer overflow". Verify that your sum is not
exceeding the maximum values allowed for the type of 'amount' noted at
http://www.postgresql.org/docs/postgres/datatype.htm#AEN840
If it is, try casting it to a larger capacity type (example below)...
Cheers,
Ed Loehr
CREATE TABLE foo (id SERIAL, i INT4);
] NOTICE: CREATE TABLE will create implicit sequence 'foo_id_seq' for
SERIAL column 'foo.id'
] NOTICE: CREATE TABLE/UNIQUE will create implicit index 'foo_id_key'
for table 'foo'
CREATE
INSERT INTO foo (i) VALUES (2147483646);
INSERT 623743 1
INSERT INTO foo (i) VALUES (2147483646);
INSERT 623744 1
SELECT * FROM foo;
id| i
--+----------
1|2147483646
2|2147483646
(2 rows)
SELECT * FROM foo WHERE i < 0;
id|i
--+-
(0 rows)
SELECT SUM(i) FROM foo;
sum
---
-4
(1 row)
SELECT SUM(i::float8) FROM foo;
sum
----------
4294967292
(1 row)
EOF
From bouncefilter Wed Jan 19 16:37:44 2000
Received: from hotmail.com (f55.law7.hotmail.com [216.33.237.55])
by hub.org (8.9.3/8.9.3) with SMTP id QAA25087
for <pgsql-general@postgresql.org>;
Wed, 19 Jan 2000 16:36:44 -0500 (EST)
(envelope-from albertsql@hotmail.com)
Received: (qmail 68320 invoked by uid 0); 19 Jan 2000 21:36:13 -0000
Message-ID: <20000119213613.68319.qmail@hotmail.com>
Received: from 202.188.228.1 by www.hotmail.com with HTTP;
Wed, 19 Jan 2000 13:36:13 PST
X-Originating-IP: [202.188.228.1]
From: "Albert Loo" <albertsql@hotmail.com>
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] jdbc question
Date: Thu, 20 Jan 2000 05:36:13 MYT
Mime-Version: 1.0
Content-Type: text/plain; format=flowed
From: mr_hopkins@earthlink.net (Micheal H.)
Here's the code.....It compiles but never gets past "Failed to load
postgresql driver"
your classpath setting might be wrong, it must includes the jar filename as
well, such as :
CLASSPATH = /usr/local/lib/postgresql.jar
you might also want to try :
//String url = "jdbc:postgresql://gina/testdb";
String url = "jdbc:postgresql://127.0.0.1:5432/testdb";
....
//Connection con = DriverManager.getConnection(url, "", "");
Connection con = DriverManager.getConnection( url, "yourname", "");
hope that helps
albert
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com
From bouncefilter Thu Jan 20 02:34:22 2000
Received: from camus.cybercable.fr (camus.cybercable.fr [212.198.0.200])
by hub.org (8.9.3/8.9.3) with SMTP id CAA59932
for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 02:34:19 -0500 (EST)
(envelope-from vdv@dyomedea.com)
Received: (qmail 26474167 invoked from network); 20 Jan 2000 07:34:38 -0000
Received: from d011.paris-38.cybercable.fr (HELO dyomedea.com)
([212.198.38.11]) (envelope-sender <vdv@dyomedea.com>)
by camus.cybercable.fr (qmail-ldap-1.03) with SMTP
for <admin@wtbwts.com>; 20 Jan 2000 07:34:38 -0000
Message-ID: <3886BAAF.95806A78@dyomedea.com>
Date: Thu, 20 Jan 2000 08:35:11 +0100
From: Eric van der Vlist <vdv@dyomedea.com>
Organization: Dyomedea
X-Mailer: Mozilla 4.7 [en] (Win98; I)
X-Accept-Language: en
MIME-Version: 1.0
To: Marc Tardif <admin@wtbwts.com>
CC: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] apache logs to pgsql
References: <Pine.BSF.4.10.10001200124250.12998-100000@server.b0x.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Hi,
I am currently using a less elegant -but efficient- solution which is to
use the standard Apache log module and to post process the log files
into postgreSQL tables.
The advantage of this method is that you can perform this post
processing on another server, removing all the strain from the web
server.
If anyone is interested, I can contribute the scripts I have developed.
They are tailored to my specific needs but could easily be adapted.
Hope this helps.
Eric
Marc Tardif wrote:
In the contrib directory, there is an example on how to redirect apache
logs to a postgresql database. It says to create a table and change two
lines in the apache configuration file to use psql for copying the
logs to the database.My question is: using an sql db is mostly compelling for very active
sites but, if this is the case, is it sensible to use psql?First, I find that using the psql command could have too much overhead.
Seeing the executable is 46696 bytes and dynamically linked, I'm not
really sure what is the strain on the system for bringing up psql.Second, I have tried to see the alternative of writing an apache module
or, more sensibly, changing the current module responsible for logging.
After reading mod_log_config.c, I'm not really sure how I can integrate
libpq and avoid having to connect and disconnect repeatedly. If I can't
somehow sustain a connection, I imagine the module would be just as
efficient as using psql.Lastly, I'm confused and overwhelmed by the size of the task at hand. I'd
appreciate any suggestions or recommendations or thoughts on whether
writing a module is even worthwhile at all.Thanks,
Marc************
--
------------------------------------------------------------------------
Eric van der Vlist Dyomedea
http://www.dyomedea.com http://www.ducotede.com
------------------------------------------------------------------------
From bouncefilter Thu Jan 20 04:34:23 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 EAA93722
for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 04:34:10 -0500 (EST) (envelope-from yura@vpcit.ru)
Received: (qmail 25446 invoked from network); 20 Jan 2000 09:34:00 -0000
Received: from gercon.vpcit.ru (195.38.52.226)
by dialup.vpcit.ru with SMTP; 20 Jan 2000 09:33:59 -0000
Date: Thu, 20 Jan 2000 14:33:23 +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: <17606.000120@vpcit.ru>
To: pgsql-general <pgsql-general@postgreSQL.org>
Subject: Bug with indexing int4?
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Hello All,
I have created the table
CREATE TABLE "tt" (
"cc" int4);
COPY "tt" FROM stdin;
-2112563299
-2111287024
-2110307960
.....
2146589610
2146589611
2146589612
\.
About 30 000 records totally
Then I am doing the following:
mdb=> select cc from tt where cc = -2112563299;
cc
-----------
-2112563299
(1 row)
mdb=> create index i_tt_cc on tt (cc);
CREATE
mdb=> select cc from tt where cc = -2112563299;
cc
(0 rows)
mdb=> drop index i_tt_cc;
DROP
mdb=> select cc from tt where cc = -2112563299;
cc
-----------
-2112563299
(1 row)
Best regards,
Yury ICQ 11831432
mailto:yura@vpcit.ru
From bouncefilter Thu Jan 20 04:41:24 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 EAA95652
for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 04:40:41 -0500 (EST) (envelope-from yura@vpcit.ru)
Received: (qmail 25532 invoked from network); 20 Jan 2000 09:40:35 -0000
Received: from gercon.vpcit.ru (195.38.52.226)
by dialup.vpcit.ru with SMTP; 20 Jan 2000 09:40:35 -0000
Date: Thu, 20 Jan 2000 14:39:59 +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: <18611.000120@vpcit.ru>
To: pgsql-general <pgsql-general@postgreSQL.org>
Subject: Bug with indexing int4?
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Hello All,
I ma sorry, I have sent previous uncomplited e-mail accidentally.
I have created the table
CREATE TABLE "tt" (
"cc" int4);
COPY "tt" FROM stdin;
-2112563299
-2111287024
-2110307960
.....
2146589610
2146589611
2146589612
\.
About 30 000 records totally
Then I am doing the following:
mdb=> select cc from tt where cc = -2112563299;
cc
-----------
-2112563299
(1 row)
mdb=> create index i_tt_cc on tt (cc);
CREATE
mdb=> select cc from tt where cc = -2112563299;
cc
(0 rows)
mdb=> drop index i_tt_cc;
DROP
mdb=> select cc from tt where cc = -2112563299;
cc
-----------
-2112563299
(1 row)
So, whith index postgresql works incorectly in this situation.
But when there are less records in the table (about 12 000),
everything works well.
I tested this on 3 different computers on Debian Linux 2.1 with
postgresql 6.5.2 and 6.5.3.
Best regards,
Yury ICQ 11831432
mailto:yura@vpcit.ru
From bouncefilter Thu Jan 20 06:42:25 2000
Received: from www-uk-05.digiserve.com ([195.172.23.32])
by hub.org (8.9.3/8.9.3) with ESMTP id GAA30739
for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 06:41:32 -0500 (EST)
(envelope-from dev@archonet.com)
Received: from mainbox.archonet.com (IDENT:postfix@mwynhau.demon.co.uk
[193.237.186.96])
by www-uk-05.digiserve.com (8.9.1/8.9.1) with ESMTP id LAA18074
for <pgsql-general@postgreSQL.org>; Thu, 20 Jan 2000 11:47:56 GMT
Received: from client (unknown [192.168.1.16])
by mainbox.archonet.com (Postfix) with SMTP id 1D7D850884
for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 10:04:32 +0000 (GMT)
Message-ID: <00bb01bf632d$ca5046c0$1001a8c0@archonet.com>
From: "Richard Huxton" <dev@archonet.com>
To: "pgsql-general" <pgsql-general@postgreSQL.org>
References: <012e01bf626f$055658a0$8402a8c0@sentec.demon.nl>
<19689.000119@vpcit.ru>
Subject: Re[2]: [GENERAL] Bug or my crooked hands?
Date: Thu, 20 Jan 2000 10:04:43 -0000
Organization: Archonet Ltd
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.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
----- Original Message -----
From: Yury Don <yura@vpcit.ru>
To: pgsql-general <pgsql-general@postgreSQL.org>
Sent: Wednesday, January 19, 2000 11:32 AM
Subject: Re[2]: [GENERAL] Bug or my crooked hands?
I have the following situation:
CREATE TABLE "Contacts" (
"Contact" Serial,
[snipped fields]
);
Then I have inserted data from text file and do a query
mdb=> select Contact from Contacts;
Contacts
-----------
-2112563299
[snip]
...
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.
Speaking of which, what range have you got on that sequence (serial type =
sequence AFAIK)?
It couldn't be that postgres is looking at your WHERE and deciding that the
value is out of range, so there can't be any records to return.
What happens if you insert a new record and then try to look for that?
--
Richard Huxton
Archonet Ltd.
From bouncefilter Thu Jan 20 05:09:24 2000
Received: from tozsde.securities.hu (tozsde.securities.hu [194.152.155.2])
by hub.org (8.9.3/8.9.3) with ESMTP id FAA05708
for <pgsql-general@postgresql.org>;
Thu, 20 Jan 2000 05:08:13 -0500 (EST)
(envelope-from balazs@tozsde.securities.hu)
Received: from localhost (balazs@localhost)
by tozsde.securities.hu (8.8.8/8.8.8) with ESMTP id LAA23601
for <pgsql-general@postgresql.org>; Thu, 20 Jan 2000 11:06:30 +0100
Date: Thu, 20 Jan 2000 11:06:30 +0100 (CET)
From: Balazs Gyetvai <balazs@securities.hu>
To: pgsql-general@postgresql.org
Subject: Grant problems
Message-ID: <Pine.LNX.4.10.10001201101170.14019-100000@tozsde.securities.hu>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Hello
I like to create a user who can do the followings on a table: select,
insert, update
But he can't delete!
I tried this: grant INSERT, SELECT, UPDATE on table_name to testuser;
After this the test user can delete from the table_name table without any
problems. The testuser is not a superuser.
Plz help, Thanks, Bye
From bouncefilter Thu Jan 20 05:36:24 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 FAA13319
for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 05:35:25 -0500 (EST)
(envelope-from j.roeleveld@softhome.net)
Received: (qmail 1702 invoked by uid 417); 20 Jan 2000 10:37:27 -0000
Received: from sentec.demon.nl (HELO joost) (212.238.106.25)
by smtpa.softhome.net with SMTP; 20 Jan 2000 10:37:27 -0000
Message-ID: <00b101bf6331$eb731b80$8402a8c0@sentec.demon.nl>
From: "J. Roeleveld" <j.roeleveld@softhome.net>
To: "Balazs Gyetvai" <balazs@securities.hu>, <pgsql-general@postgreSQL.org>
References: <Pine.LNX.4.10.10001201101170.14019-100000@tozsde.securities.hu>
Subject: Re: [GENERAL] Grant problems
Date: Thu, 20 Jan 2000 11:34:19 +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
I like to create a user who can do the followings on a table: select,
insert, update
But he can't delete!
I tried this: grant INSERT, SELECT, UPDATE on table_name to testuser;
After this the test user can delete from the table_name table without any
problems. The testuser is not a superuser.Plz help, Thanks, Bye
Do the following first:
REVOKE ALL ON "table_name" FROM PUBLIC;
this takes all permissions away from the Public, eg. all users, including
the testuser....
hope this helps,
with kind regards,
Joost Roeleveld
From bouncefilter Thu Jan 20 06:04:25 2000
Received: from lugh.relay.co.uk (lugh.relay.co.uk [194.72.177.254])
by hub.org (8.9.3/8.9.3) with ESMTP id GAA17962
for <pgsql-general@postgresql.org>;
Thu, 20 Jan 2000 06:03:48 -0500 (EST)
(envelope-from rachel.greenham@enetgroup.co.uk)
Received: from enetgroup.co.uk ([194.72.178.7]) by
lugh.relay.co.uk (Netscape Messaging Server 4.15) with ESMTP id
FOMS1K00.K00 for <pgsql-general@postgresql.org>; Thu, 20 Jan
2000 11:03:21 +0000
Sender: rachel
Message-ID: <3886EB7B.4ED8E3DC@enetgroup.co.uk>
Date: Thu, 20 Jan 2000 11:03:23 +0000
From: Rachel Greenham <rachel.greenham@enetgroup.co.uk>
Organization: e-Net Software
X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.14 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: pgsql-general@postgresql.org
Subject: Back-end dying on timestamp value insert
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
I'm having a problem, both through JDBC and through psql (so I reckon
the problem is in the core database) whereby if I put a value into a
timestamp field which has a non-unique index on it, I get a "back-end
has died unexpectedly" error.
It's consistently happening on the same column in the same table, even
after destroying and recreating the databases. There is another
timestamp column, also indexed, in the same table which behaves
perfectly I'm flummoxed. Help!
More data:
The table is of the form:
create table my_table (id int not null, startdate timestamp, enddate
timestamp);
create unique index id_idx on my_table (id);
create index startdate_idx on my_table (startdate datetime_ops);
create index enddate_idx on my_table (enddate datetime_ops);
insert into my_table (id, startdate) values (1, 'now');
this works fine then:
insert into my_table (id, enddate) values (2, 'now');
this fails with backend-death. If I drop the index on enddate I can
insert values fine, but then the backend dies if I subsequently create
the index.
The debugging output, with debuglevel set to 3 is:
StartTransactionCommand
query: insert into cat_entries (ent_id, ent_code, ent_enddate,
ent_tmg_id, ent_is_group) values (455, 'wibble', 'now', 1, 0);
ProcessQuery
/usr/lib/pgsql/bin/postmaster: reaping dead processes...
/usr/lib/pgsql/bin/postmaster: CleanupProc: pid 11763 exited with status
11
/usr/lib/pgsql/bin/postmaster: CleanupProc: reinitializing shared memory
and semaphores
shmem_exit(0) [#0]
binding ShmemCreate(key=52e389, size=1063936)
and psql exits with:
mydb=> insert into cat_entries (ent_id, ent_code, ent_enddate,
ent_tmg_id, ent_is_group) values (455, 'wibble', 'now', 1, 0);
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
We have lost the connection to the backend, so further processing is
impossible.
Terminating.
What's happening? :-) And is it something known about fixed in a later
version?
(I'm on Postgresql 6.5.1 as supplied with SuSE Linux 6.3)
[PostgreSQL 6.5.1 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66]
--
Rachel
From bouncefilter Thu Jan 20 06:26:25 2000
Received: from imcwaw2.polkomtel.com.pl (imcwaw2.polkomtel.com.pl
[212.2.96.79]) by hub.org (8.9.3/8.9.3) with ESMTP id GAA24085
for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 06:25:44 -0500 (EST)
(envelope-from andrzej.mazurkiewicz@polkomtel.com.pl)
Received: by imcwaw2.plusnet with Internet Mail Service (5.5.2448.0)
id <Z35QDQYX>; Thu, 20 Jan 2000 12:25:04 +0100
Message-ID: <13288F4408ADD11186FF0060B06A431303648BD7@MSGWAW1>
From: Andrzej Mazurkiewicz <andrzej.mazurkiewicz@polkomtel.com.pl>
To: "'Balazs Gyetvai'" <balazs@securities.hu>, pgsql-general@postgreSQL.org
Subject: RE: [GENERAL] Grant problems
Date: Thu, 20 Jan 2000 12:24:58 +0100
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2448.0)
Content-Type: text/plain
test_table has to be created by different user than testuser. If the table
is created by testuser he is an owner of the table and can do everything
what he wants.
Regards,
Andrzej
-----Original Message-----
From: Balazs Gyetvai [SMTP:balazs@securities.hu]
Sent: 20 stycznia 2000 11:07
To: pgsql-general@postgreSQL.org
Subject: [GENERAL] Grant problemsHello
I like to create a user who can do the followings on a table: select,
insert, update
But he can't delete!
I tried this: grant INSERT, SELECT, UPDATE on table_name to testuser;
After this the test user can delete from the table_name table without any
problems. The testuser is not a superuser.Plz help, Thanks, Bye
************
From bouncefilter Thu Jan 20 06:42:25 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 GAA30754
for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 06:41:37 -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 MAA15243;
Thu, 20 Jan 2000 12:41:26 +0100 (MET)
Received: from localhost (e99re41@localhost) by Puma.DoCS.UU.SE
(8.6.12/8.6.12) with SMTP id MAA15559;
Thu, 20 Jan 2000 12:41:22 +0100
X-Authentication-Warning: Puma.DoCS.UU.SE: e99re41 owned process doing -bs
Date: Thu, 20 Jan 2000 12:41:22 +0100 (MET)
From: Peter Eisentraut <e99re41@DoCS.UU.SE>
Reply-To: Peter Eisentraut <peter_e@gmx.net>
To: Marc Tardif <admin@wtbwts.com>
cc: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] apache logs to pgsql
In-Reply-To: <Pine.BSF.4.10.10001200124250.12998-100000@server.b0x.com>
Message-ID: <Pine.GSO.4.02A.10001201237010.15489-100000@Puma.DoCS.UU.SE>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
On Thu, 20 Jan 2000, Marc Tardif wrote:
In the contrib directory, there is an example on how to redirect apache
logs to a postgresql database. It says to create a table and change two
lines in the apache configuration file to use psql for copying the
logs to the database.My question is: using an sql db is mostly compelling for very active
sites but, if this is the case, is it sensible to use psql?
That's definitely overkill and possibly too slow on busy sites. What you
might want to do is write a "psql light" that simply takes strings on the
standard input and sends them to the backend. You'd still have to link
that against libpq but you might find it to be more efficient.
First, I find that using the psql command could have too much overhead.
Seeing the executable is 46696 bytes and dynamically linked, I'm not
really sure what is the strain on the system for bringing up psql.
Not to mention the fact that every time you start it up psql does a whole
bunch of initializing, and every line of input you pass it is first parsed
before deciding what to do with it.
--
Peter Eisentraut Sernanders vaeg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
From bouncefilter Thu Jan 20 07:23:25 2000
Received: from edison.ioppublishing.com (edison.ioppublishing.com
[193.128.223.242]) by hub.org (8.9.3/8.9.3) with SMTP id HAA42199
for <pgsql-general@postgresql.org>;
Thu, 20 Jan 2000 07:22:42 -0500 (EST)
(envelope-from pmh@edison.ioppublishing.com)
Received: (qmail 24275 invoked from network); 20 Jan 2000 12:21:39 -0000
Received: from localhost (127.0.0.1)
by localhost with SMTP; 20 Jan 2000 12:21:39 -0000
Date: Thu, 20 Jan 2000 12:21:39 +0000 (GMT)
From: Peter Haworth <pmh@edison.ioppublishing.com>
Sender: pmh@edison.ioppublishing.com
Reply-To: Peter Haworth <pmh@edison.ioppublishing.com>
Subject: Creating simple type aliases
To: Postgres general list <pgsql-general@postgresql.org>
Message-ID: <ML-3.3.948370899.5758.pmh@edison.ioppublishing.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; CHARSET=US-ASCII
Is it possible to create an alias for an existing type, so that I can refer to
it with a simple name, and possibly change it in the future? Something like:
create type-alias issn as char(9)
Without that it looks like I have to do
create type issn (
input=charin, output=charout,
internallength=9, externallength=9,
element='char'
)
then insert a row to pg_opclass, after attempting to use the new type to find
its ID.
Surely there's an easier way?
--
Peter Haworth pmh@edison.ioppublishing.com
"Never tell people how to do things.
Tell them WHAT to do and they will surprise you with their ingenuity."
-- Gen. George S. Patton, Jr.
From bouncefilter Thu Jan 20 07:28:25 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 HAA43083
for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 07:27:49 -0500 (EST) (envelope-from yura@vpcit.ru)
Received: (qmail 27881 invoked from network); 20 Jan 2000 12:27:46 -0000
Received: from gercon.vpcit.ru (195.38.52.226)
by dialup.vpcit.ru with SMTP; 20 Jan 2000 12:27:46 -0000
Date: Thu, 20 Jan 2000 17:27:10 +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: <12727.000120@vpcit.ru>
To: pgsql-general <pgsql-general@postgreSQL.org>
Subject: Re[3]: [GENERAL] Bug or my crooked hands?
In-reply-To: <00bb01bf632d$ca5046c0$1001a8c0@archonet.com>
References: <00bb01bf632d$ca5046c0$1001a8c0@archonet.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Hello Richard,
Once, Thursday, January 20, 2000, 3:04:43 PM, you wrote:
RH> ----- Original Message -----
RH> From: Yury Don <yura@vpcit.ru>
RH> To: pgsql-general <pgsql-general@postgreSQL.org>
RH> Sent: Wednesday, January 19, 2000 11:32 AM
RH> Subject: Re[2]: [GENERAL] Bug or my crooked hands?
I have the following situation:
CREATE TABLE "Contacts" (
"Contact" Serial,
RH> [snipped fields]
);
Then I have inserted data from text file and do a query
mdb=> select Contact from Contacts;
Contacts
-----------
-2112563299
RH> [snip]
...
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.
RH> Speaking of which, what range have you got on that sequence (serial type =
RH> sequence AFAIK)?
RH> It couldn't be that postgres is looking at your WHERE and deciding that the
RH> value is out of range, so there can't be any records to return.
RH> What happens if you insert a new record and then try to look for that?
RH> --
RH> Richard Huxton
RH> Archonet Ltd.
RH> ************
I am already found that this is seems a bug in postgres with using of
indexes on int4.
I have created the table
CREATE TABLE "tt" (
"cc" int4);
COPY "tt" FROM stdin;
-2112563299
-2111287024
-2110307960
.....
2146589610
2146589611
2146589612
\.
About 30 000 records totally
Then I am doing the following:
mdb=> select cc from tt where cc = -2112563299;
cc
-----------
-2112563299
(1 row)
mdb=> create index i_tt_cc on tt (cc);
CREATE
mdb=> select cc from tt where cc = -2112563299;
cc
(0 rows)
mdb=> drop index i_tt_cc;
DROP
mdb=> select cc from tt where cc = -2112563299;
cc
-----------
-2112563299
(1 row)
So, whith index postgresql works incorectly in this situation.
But when there are less records in the table (about 12 000),
everything works well.
I tested this on 3 different computers on Debian Linux 2.1 with
postgresql 6.5.2 and 6.5.3.
Best regards,
Yury ICQ 11831432
mailto:yura@vpcit.ru
From bouncefilter Thu Jan 20 08:03:26 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 IAA55305
for <pgsql-general@postgresql.org>;
Thu, 20 Jan 2000 08:02:29 -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 12BHES-0003vD-00
for pgsql-general@postgresql.org; Thu, 20 Jan 2000 13:02:36 +0000
Received: from prlw1 by quartz.newn.cam.ac.uk with local (Exim 2.12 #1)
id 12BHEK-0004ET-00
for pgsql-general@postgresql.org; Thu, 20 Jan 2000 13:02:28 +0000
Date: Thu, 20 Jan 2000 13:02:28 +0000
From: Patrick Welche <prlw1@newn.cam.ac.uk>
To: pgsql-general@postgresql.org
Subject: vacuum analyze [table]
Message-ID: <20000120130228.D12966@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
Am I the only one who cannot vacuum a named table? (Does it make sense to
just vacuum a single table?)
regression=> \h vacuum
Command: VACUUM
Description: Clean and analyze a Postgres database
Syntax:
VACUUM [ VERBOSE ] [ ANALYZE ] [ table ]
VACUUM [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]
regression=> vacuum analyze num_result;
NOTICE: Vacuum: table not found
VACUUM
The same is true for me with any table, any database, using source cvs'd
yesterday.
Cheers,
Patrick
From bouncefilter Thu Jan 20 08:43:27 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 IAA66362
for <pgsql-general@postgresql.org>;
Thu, 20 Jan 2000 08:42:50 -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 OAA25066;
Thu, 20 Jan 2000 14:41:54 +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 12BIlY-00026p-00; Thu, 20 Jan 2000 14:40:52 +0000
Message-ID: <3887112B.ED5D544C@sferacarta.com>
Date: Thu, 20 Jan 2000 14:44:11 +0100
From: Jose Soares <jose@sferacarta.com>
X-Mailer: Mozilla 4.6 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
To: Yury Don <yura@vpcit.ru>
CC: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Bug with indexing int4?
References: <18611.000120@vpcit.ru>
Content-Type: multipart/alternative;
boundary="------------5D44977B4F7D3E1319777B13"
--------------5D44977B4F7D3E1319777B13
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Yury Don wrote:
Hello All,
I ma sorry, I have sent previous uncomplited e-mail accidentally.
I have created the tableCREATE TABLE "tt" (
"cc" int4);
COPY "tt" FROM stdin;
-2112563299
-2111287024
-2110307960
.....2146589610
2146589611
2146589612
\.About 30 000 records totally
Then I am doing the following:
mdb=> select cc from tt where cc = -2112563299;
cc
-----------
-2112563299
(1 row)mdb=> create index i_tt_cc on tt (cc);
CREATE
mdb=> select cc from tt where cc = -2112563299;
cc
(0 rows)mdb=> drop index i_tt_cc;
DROP
mdb=> select cc from tt where cc = -2112563299;
cc
-----------
-2112563299
(1 row)So, whith index postgresql works incorectly in this situation.
But when there are less records in the table (about 12 000),
everything works well.
I tested this on 3 different computers on Debian Linux 2.1 with
postgresql 6.5.2 and 6.5.3.Best regards,
Yury ICQ 11831432
mailto:yura@vpcit.ru************
I tried it on my Debian and it works...
hygea=> select version();
version
--------------------------------------------------------------
PostgreSQL 6.5.2 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
(1 row)
uname -a
Linux Debian 2.0.37 #1 Thu Sep 2 17:24:38 CEST 1999 i586 unknown
==================================================================
CREATE TABLE "tt" (
"cc" int4);
CREATE
COPY "tt" FROM stdin;
select cc from tt where cc = -2112563299;
cc
-----------
-2112563299
(1 row)
create index i_tt_cc on tt (cc);
CREATE
select cc from tt where cc = -2112563299;
cc
-----------
-2112563299
(1 row)
drop index i_tt_cc;
DROP
select cc from tt where cc = -2112563299;
cc
-----------
-2112563299
(1 row)
Jos�
--------------5D44977B4F7D3E1319777B13
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit
<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
<tt></tt> <tt></tt>
<p><tt>Yury Don wrote:</tt>
<blockquote TYPE=CITE><tt>Hello All,</tt><tt></tt>
<p><tt>I ma sorry, I have sent previous uncomplited e-mail accidentally.</tt>
<br><tt>I have created the table</tt><tt></tt>
<p><tt>CREATE TABLE "tt" (</tt>
<br><tt> "cc" int4);</tt>
<br><tt>COPY "tt" FROM stdin;</tt>
<br><tt>-2112563299</tt>
<br><tt>-2111287024</tt>
<br><tt>-2110307960</tt>
<br><tt>.....</tt><tt></tt>
<p><tt>2146589610</tt>
<br><tt>2146589611</tt>
<br><tt>2146589612</tt>
<br><tt>\.</tt><tt></tt>
<p><tt>About 30 000 records totally</tt><tt></tt>
<p><tt>Then I am doing the following:</tt><tt></tt>
<p><tt>mdb=> select cc from tt where cc = -2112563299;</tt>
<br><tt> cc</tt>
<br><tt>-----------</tt>
<br><tt>-2112563299</tt>
<br><tt>(1 row)</tt><tt></tt>
<p><tt>mdb=> create index i_tt_cc on tt (cc);</tt>
<br><tt>CREATE</tt>
<br><tt>mdb=> select cc from tt where cc = -2112563299;</tt>
<br><tt>cc</tt>
<br><tt>(0 rows)</tt><tt></tt>
<p><tt>mdb=> drop index i_tt_cc;</tt>
<br><tt>DROP</tt>
<br><tt>mdb=> select cc from tt where cc = -2112563299;</tt>
<br><tt> cc</tt>
<br><tt>-----------</tt>
<br><tt>-2112563299</tt>
<br><tt>(1 row)</tt><tt></tt>
<p><tt>So, whith index postgresql works incorectly in this situation.</tt>
<br><tt>But when there are less records in the table (about 12 000),</tt>
<br><tt>everything works well.</tt>
<br><tt>I tested this on 3 different computers on Debian Linux 2.1 with</tt>
<br><tt>postgresql 6.5.2 and 6.5.3.</tt><tt></tt>
<p><tt>Best regards,</tt>
<br><tt> Yury ICQ 11831432</tt>
<br><tt> <a href="mailto:yura@vpcit.ru">mailto:yura@vpcit.ru</a></tt><tt></tt>
<p><tt>************</tt></blockquote>
<tt>I tried it on my Debian and it works...</tt><tt></tt>
<p><tt>hygea=> select version();</tt>
<br><tt>version</tt>
<br><tt>--------------------------------------------------------------</tt>
<br><tt>PostgreSQL 6.5.2 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3</tt>
<br><tt>(1 row)</tt><tt></tt>
<p><tt>uname -a</tt>
<br><tt>Linux Debian 2.0.37 #1 Thu Sep 2 17:24:38 CEST 1999 i586 unknown</tt><tt></tt>
<p><tt>==================================================================</tt><tt></tt>
<p><tt>CREATE TABLE "tt" (</tt>
<br><tt> "cc" int4);</tt>
<br><tt>CREATE</tt>
<br><tt>COPY "tt" FROM stdin;</tt><tt></tt>
<p><tt>select cc from tt where cc = -2112563299;</tt>
<br><tt> cc</tt>
<br><tt>-----------</tt>
<br><tt>-2112563299</tt>
<br><tt>(1 row)</tt><tt></tt>
<p><tt>create index i_tt_cc on tt (cc);</tt>
<br><tt>CREATE</tt>
<br><tt>select cc from tt where cc = -2112563299;</tt>
<br><tt> cc</tt>
<br><tt>-----------</tt>
<br><tt>-2112563299</tt>
<br><tt>(1 row)</tt><tt></tt>
<p><tt>drop index i_tt_cc;</tt>
<br><tt>DROP</tt>
<br><tt>select cc from tt where cc = -2112563299;</tt>
<br><tt> cc</tt>
<br><tt>-----------</tt>
<br><tt>-2112563299</tt>
<br><tt>(1 row)</tt>
<br>
<p>José
<br> </html>
--------------5D44977B4F7D3E1319777B13--
From bouncefilter Thu Jan 20 09:58:27 2000
Received: from dreish.izzy.net (IDENT:qmailr@dreish.izzy.net [207.158.177.65])
by hub.org (8.9.3/8.9.3) with SMTP id JAA85366
for <pgsql-general@postgresql.org>;
Thu, 20 Jan 2000 09:57:47 -0500 (EST) (envelope-from dreish@izzy.net)
Received: (qmail 16904 invoked by uid 500); 20 Jan 2000 14:57:43 -0000
Date: Thu, 20 Jan 2000 09:57:43 -0500
From: Dan Reish <dreish@izzy.net>
To: pgsql-general@postgresql.org
Subject: R-trees supported?
Message-ID: <20000120095743.A16286@izzy.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Mailer: Mutt 0.95.4us
What's the status of R-tree indexing in PostgreSQL? Has anyone used them?
Thanks.
--
Dan
From bouncefilter Thu Jan 20 10:13:27 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 KAA90900
for <pgsql-general@postgresql.org>;
Thu, 20 Jan 2000 10:12:57 -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 12BJGl-0003yh-00
for pgsql-general@postgresql.org; Thu, 20 Jan 2000 15:13:07 +0000
Received: from prlw1 by quartz.newn.cam.ac.uk with local (Exim 2.12 #1)
id 12BJGb-0004I8-00
for pgsql-general@postgresql.org; Thu, 20 Jan 2000 15:12:57 +0000
Date: Thu, 20 Jan 2000 15:12:57 +0000
From: Patrick Welche <prlw1@newn.cam.ac.uk>
To: pgsql-general@postgresql.org
Subject: Variable case database names
Message-ID: <20000120151257.F12966@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
List of databases
Database | Owner
------------+----------
Newnham | prlw1
% psql Newnham
psql: connection to database "newnham" failed - FATAL 1: Database "newnham" does not exist in the system catalog.
template1=> \c 'Newnham'
FATAL 1: Database "newnham" does not exist in the system catalog.
Previous connection kept
How can I connect to a database with a variable case name?
Cheers,
Patrick
From bouncefilter Thu Jan 20 10:22:27 2000
Received: from dreish.izzy.net (IDENT:qmailr@dreish.izzy.net [207.158.177.65])
by hub.org (8.9.3/8.9.3) with SMTP id KAA92354
for <pgsql-general@postgresql.org>;
Thu, 20 Jan 2000 10:21:48 -0500 (EST) (envelope-from dreish@izzy.net)
Received: (qmail 17142 invoked by uid 500); 20 Jan 2000 15:21:47 -0000
Date: Thu, 20 Jan 2000 10:21:47 -0500
From: Dan Reish <dreish@izzy.net>
To: pgsql-general@postgresql.org
Subject: R-trees - any documentation?
Message-ID: <20000120102147.C16286@izzy.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Mailer: Mutt 0.95.4us
Does anyone know where I might find documentation on creating R-tree indexes
in PostgreSQL? The docs included in the standard distribution don't seem to
even mention the existence of R-trees.
Thanks.
--
Dan
From bouncefilter Thu Jan 20 10:44:28 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 KAA00803
for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 10:43:53 -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
KAA02898;
Thu, 20 Jan 2000 10:43:35 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200001201543.KAA02898@candle.pha.pa.us>
Subject: Re: [GENERAL] vacuum analyze [table]
In-Reply-To: <20000120130228.D12966@quartz.newn.cam.ac.uk> from Patrick Welche
at "Jan 20, 2000 01:02:28 pm"
To: prlw1@cam.ac.uk
Date: Thu, 20 Jan 2000 10:43:35 -0500 (EST)
CC: 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
Am I the only one who cannot vacuum a named table? (Does it make sense to
just vacuum a single table?)regression=> \h vacuum
Command: VACUUM
Description: Clean and analyze a Postgres database
Syntax:
VACUUM [ VERBOSE ] [ ANALYZE ] [ table ]
VACUUM [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]regression=> vacuum analyze num_result;
NOTICE: Vacuum: table not found
VACUUMThe same is true for me with any table, any database, using source cvs'd
yesterday.
Works here.
--
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 Thu Jan 20 10:44:37 2000
Received: from portal.west.saic.com (portal.west.saic.com [198.151.12.15])
by hub.org (8.9.3/8.9.3) with SMTP id KAA00805
for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 10:43:54 -0500 (EST)
(envelope-from officers@aries.tucson.saic.com)
Received: from gatespass.tucson.saic.com by portal.west.saic.com
via smtpd (for postgresql.org [216.126.84.28]) with SMTP;
20 Jan 2000 15:43:54 UT
Received: (from adm@localhost) by gatespass.tucson.saic.com (8.8.2/8.8.2) id
IAA00953; Thu, 20 Jan 2000 08:43:50 -0700 (MST)
X-Authentication-Warning: gatespass.tucson.saic.com: adm set sender to
<officers@aries.tucson.saic.com> using -f
Received: from wasp.tucson.saic.com(139.121.3.171) by
gatespass.tucson.saic.com via smap (V2.0)
id xma000951; Thu, 20 Jan 00 08:43:45 -0700
Received: from aries.tucson.saic.com (localhost [127.0.0.1]) by
wasp.tucson.saic.com (980427.SGI.8.8.8/950213.SGI.AUTOCF) via
ESMTP id IAA60987; Thu, 20 Jan 2000 08:43:43 -0700 (MST)
Sender: officers@aries.tucson.saic.com
Message-ID: <38872D2E.E2145A9D@aries.tucson.saic.com>
Date: Thu, 20 Jan 2000 08:43:42 -0700
From: Sarah Officer <officers@aries.tucson.saic.com>
X-Mailer: Mozilla 4.07C-SGI [en] (X11; I; IRIX 6.5 IP32)
MIME-Version: 1.0
To: Jeff MacDonald <jeff@pgsql.com>
CC: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] parser
References: <Pine.BSF.4.10.10001192318550.18995-100000@rage.hub.org>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
I had this problem a couple years ago. I changed the column name
before the upgrade by renaming the table to table_old, creating a
new table with the original table name by selecting from table_old.
For the column I needed to rename, I selected offset as
'x_offset'. I made sure everything was there, then dropped
table_old and exported the database prior to the upgrade. It's a
bit of trouble to find all the queries in the software and replace
the column name, but for my application it was workable. Or maybe
you already know how to do this but don't think you should have to?
I hope this helps.
Sarah Officer
officers
Jeff MacDonald wrote:
hi, got this question from a user, anyone up for some discussion on it
1) I have been using 6.3 and recently tried to
move to 6.5. I found that the word "offset" is now a reserved word and I have used it as a field name in
a table. 6.5 reports this as an error. I wonder if the parser should do this since the context is as a
field name.======================================================
Jeff MacDonald
jeff@pgsql.com irc: bignose on EFnet
======================================================************
From bouncefilter Thu Jan 20 11:35:28 2000
Received: from andie.ip23.net ([212.83.32.23])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA17007
for <pgsql-general@postgresql.org>;
Thu, 20 Jan 2000 11:34:59 -0500 (EST) (envelope-from sevo@ip23.net)
Received: from imap1.ip23.net (imap1.ip23.net [212.83.32.35])
by andie.ip23.net (8.9.3/8.9.3) with ESMTP id RAA26440;
Thu, 20 Jan 2000 17:36:13 +0100 (CET)
Received: from ip23.net (spc.ip23.net [212.83.32.122])
by imap1.ip23.net (8.9.3/8.9.3) with ESMTP id RAA64275;
Thu, 20 Jan 2000 17:34:27 +0100 (CET)
Sender: sevo@imap1.ip23.net
Message-ID: <38873A35.EA9EFFCA@ip23.net>
Date: Thu, 20 Jan 2000 17:39:17 +0100
From: Sevo Stille <sevo@ip23.net>
Organization: IP23
X-Mailer: Mozilla 4.61 [en] (X11; I; Linux 2.2.10 i686)
X-Accept-Language: en, de
MIME-Version: 1.0
To: Dan Reish <dreish@izzy.net>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] R-trees - any documentation?
References: <20000120102147.C16286@izzy.net>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Dan Reish wrote:
Does anyone know where I might find documentation on creating R-tree indexes
in PostgreSQL? The docs included in the standard distribution don't seem to
even mention the existence of R-trees.
There is some brief mention as to bugs in R-Trees in some part of the
documentation. I did not try any further after seeing that, but I'd be
willing to help in getting them operable, as I have an application which
would benefit considerably from indexed range matches.
Sevo
From bouncefilter Thu Jan 20 12:30:29 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 MAA34814
for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 12:29:31 -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 UAA01227
for <pgsql-general@postgreSQL.org>; Thu, 20 Jan 2000 20:21:14 +0300
Date: Thu, 20 Jan 2000 20:33:01 +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: <13856.000120@nlr.ru>
To: pgsql-general@postgreSQL.org
Subject: inserting timestamp values
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Hi,
I have troubles inserting the timestamp values. I use postgresql 6.5.3
on RedHat 6.1 (kernel 2.2.13). I do the following
sample==> create table t1 (d timestamp);
CREATE
sample==> create index t1_d on t1 using btree (d datetime_ops);
CREATE
sample==> insert into t1 (d) values ('1999-01-01 14:00:00+03');
INSERT 4668876 1
sample==> insert into t1 (d) values ('1999-01-01 14:00:00+03');
pqReadData() -- backend closed the channel unexpectedly.
This possible means the backend terminated abnormally
before or while processing the request.
We have lost the connection to the backend, so further processing is
impossible. Terminating.
Can anybody help me to solve this problem ?
Best regards,
Alex
P.S. Before this experiment I have the database in postgresql 6.5.3
too. One of tables in this database contains the timestamp field. I
used the pg_dump utility to backup this database and received the flat
file where all values of this field are in the format
'YYYY-mm-dd HH:MM:SS+04'. When I try to restore the database using
this backup-file:
psql mydata < <backup-file>
I receive the error as above.
From bouncefilter Thu Jan 20 12:39:29 2000
Received: from portal.west.saic.com (portal.west.saic.com [198.151.12.15])
by hub.org (8.9.3/8.9.3) with SMTP id MAA39535
for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 12:39:27 -0500 (EST)
(envelope-from officers@aries.tucson.saic.com)
Received: from gatespass.tucson.saic.com by portal.west.saic.com
via smtpd (for postgresql.org [216.126.84.28]) with SMTP;
20 Jan 2000 17:39:27 UT
Received: (from adm@localhost) by gatespass.tucson.saic.com (8.8.2/8.8.2) id
KAA01901 for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 10:39:25 -0700 (MST)
X-Authentication-Warning: gatespass.tucson.saic.com: adm set sender to
<officers@aries.tucson.saic.com> using -f
Received: from wasp.tucson.saic.com(139.121.3.171) by
gatespass.tucson.saic.com via smap (V2.0)
id xma001898; Thu, 20 Jan 00 10:39:02 -0700
Received: from aries.tucson.saic.com (localhost [127.0.0.1]) by
wasp.tucson.saic.com (980427.SGI.8.8.8/950213.SGI.AUTOCF) via
ESMTP id KAA64942 for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 10:38:59 -0700 (MST)
Sender: officers@aries.tucson.saic.com
Message-ID: <38874833.9276507F@aries.tucson.saic.com>
Date: Thu, 20 Jan 2000 10:38:59 -0700
From: Sarah Officer <officers@aries.tucson.saic.com>
X-Mailer: Mozilla 4.07C-SGI [en] (X11; I; IRIX 6.5 IP32)
MIME-Version: 1.0
To: pgsql-general@postgreSQL.org
Subject: psql parsing and variable problems
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
I have been looking at the psql documentation posted on the web
site. I am trying to set variables, but I can't seem to make that
happen. I also notice that psql doesn't seem to parse full
backslash commands. Are the documents out of date, or is the a bug?
\echo foo
is interpreted as \e and pops me into the editor.
\qecho foo
is interpreted as \q and exits psql.
When I try to use a variable, psql doesn't seem to recognize it.
create table status (
code varchar(5) not null,
stat_desc varchar(30) not null);
CREATE
insert into status(code, stat_desc) values ('A1', 'First Status');
INSERT 19082 1
select * from status;
code|stat_desc
----+------------
A1 |First Status
(1 row)
Following the example in the documentation, I try to set a psql
variable, but psql isn't interpreting it the way I expect it to.
\set foo 'status'
select * from :foo;
ERROR: parser: parse error at or near ":"
\set foo status
select * from :foo;
ERROR: parser: parse error at or near ":"
Any suggestions? Is the posted documentation out of date? How can
I use a psql variable? What is the alternative? What I really want
to do is use the 'psql --set' option so I can pass the path to the
plpgsql library from the command line.
CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
:plpgsqlpath LANGUAGE 'C';
Any help is appreciated,
Sarah Officer
officers@aries.tucson.saic.com
From bouncefilter Thu Jan 20 13:03:29 2000
Received: from mail.presys.com (mail.presys.com [64.5.0.150])
by hub.org (8.9.3/8.9.3) with SMTP id NAA48680
for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 13:03:21 -0500 (EST)
(envelope-from hitesh@pciwest.net)
Received: (qmail 2994 invoked from network); 20 Jan 2000 18:04:11 -0000
Received: from thehog.bk.pciwest.net (HELO pciwest.net) (64.5.4.153)
by mail.presys.com with SMTP; 20 Jan 2000 18:04:11 -0000
Sender: root
Message-ID: <38874DE9.3226343@pciwest.net>
Date: Thu, 20 Jan 2000 10:03:21 -0800
From: Hitesh Patel <hitesh@pciwest.net>
X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.14 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: "pgsql-general@postgreSQL.org" <pgsql-general@postgreSQL.org>
Subject: Re: [GENERAL] weird sum() results
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
I can understand that. What's weird is that I can do a sum(amount) on
the whole table and the value comes out to only My only problem is that
the amount field is the money type and doesn't like the casting now. A
SELECT SUM(amount::float8) FROM table where state = 'CA';
returns
ERROR: No such function 'float8' with the specified attributes
And again i'm stuck :-)
Thanks in advance for any help.
--
|---------------------------------|----------------------------|
| Hitesh Patel | Voice: (541) 759-3126 |
| Facilities Development Manager | Fax: (541) 759-3214 |
| Preferred Systems | Email: hitesh@presys.com |
|---------------------------------|----------------------------|
From bouncefilter Thu Jan 20 13:05:29 2000
Received: from mail.presys.com (mail.presys.com [64.5.0.150])
by hub.org (8.9.3/8.9.3) with SMTP id NAA49485
for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 13:05:20 -0500 (EST)
(envelope-from hitesh@presys.com)
Received: (qmail 3136 invoked from network); 20 Jan 2000 18:06:05 -0000
Received: from thehog.bk.pciwest.net (HELO presys.com) (64.5.4.153)
by mail.presys.com with SMTP; 20 Jan 2000 18:06:05 -0000
Sender: root
Message-ID: <38874E5B.8B581733@presys.com>
Date: Thu, 20 Jan 2000 10:05:15 -0800
From: Hitesh Patel <hitesh@presys.com>
X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.14 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: "pgsql-general@postgreSQL.org" <pgsql-general@postgreSQL.org>
Subject: Re: [GENERAL] weird sum() results
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
I can understand that. What's weird is that I can do a sum(amount) on
the whole table and the value comes out to only My only problem is that
the amount field is the money type and doesn't like the casting now. A
SELECT SUM(amount::float8) FROM table where state = 'CA';
returns
ERROR: No such function 'float8' with the specified attributes
And again i'm stuck :-)
Thanks in advance for any help.
--
|---------------------------------|----------------------------|
| Hitesh Patel | Voice: (541) 759-3126 |
| Facilities Development Manager | Fax: (541) 759-3214 |
| Preferred Systems | Email: hitesh@presys.com |
|---------------------------------|----------------------------|
From bouncefilter Thu Jan 20 13:31:35 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 NAA55366
for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 13:31:01 -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
NAA09382;
Thu, 20 Jan 2000 13:10:19 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200001201810.NAA09382@candle.pha.pa.us>
Subject: Re: [GENERAL] psql parsing and variable problems
In-Reply-To: <38874833.9276507F@aries.tucson.saic.com> from Sarah Officer at
"Jan 20, 2000 10:38:59 am"
To: Sarah Officer <officers@aries.tucson.saic.com>
Date: Thu, 20 Jan 2000 13:10:19 -0500 (EST)
CC: 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
I have been looking at the psql documentation posted on the web
site. I am trying to set variables, but I can't seem to make that
happen. I also notice that psql doesn't seem to parse full
backslash commands. Are the documents out of date, or is the a bug?\echo foo
is interpreted as \e and pops me into the editor.
\qecho foo
is interpreted as \q and exits psql.
The web page manual reflects the coming 7.0 release. We have talked
about splitting the docs so there is a stable and development version of
the docs, but we haven't do that yet.
--
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 Thu Jan 20 16:23:32 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 QAA31582
for <pgsql-general@postgresql.org>;
Thu, 20 Jan 2000 16:23:29 -0500 (EST)
(envelope-from news@news.tht.net)
Received: (from news@localhost) by news.tht.net (8.9.3/8.9.3) id PAA35486
for pgsql-general@postgresql.org; Thu, 20 Jan 2000 15:58:21 -0500 (EST)
(envelope-from news)
X-Authentication-Warning: news.tht.net: news set sender to <news> using -f
X-Newsgroups: comp.databases.postgresql.questions
Subject: PostgreSQL and GIS
Message-ID: <dL8ZZOjpRzMZ@vmsmail.gov.bc.ca>
From: "Terrence Branscombe" <Terrence.Branscombe@gems8.gov.bc.ca>
Date: Thu, 20 Jan 2000 11:02:34 -0800
X-Newsreader: Microsoft Outlook Express 4.72.3612.1700
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3612.1700
Lines: 12
To: pgsql-questions@postgresql.org
I would like to compile a list of links to sites where the use of PostgreSQL
is discussed as a backend for GIS applications. Our organization currently
runs its GIS from a set of Sun servers using Oracle and ArcINFO. The
maintenance cost for this arrangement is, as you can imagine, prohibitive.
Is anyone out there in the Open Source community developing comparable GIS
tools (to ArcINFO) that will allow us to migrate to Intel machines running
PostgreSQL as the database?
Any tips or leads are most appreciated. Thanks.
From bouncefilter Thu Jan 20 16:53:32 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 QAA39685;
Thu, 20 Jan 2000 16:53:29 -0500 (EST)
(envelope-from news@news.tht.net)
Received: (from news@localhost) by news.tht.net (8.9.3/8.9.3) id QAA37573;
Thu, 20 Jan 2000 16:29:36 -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,
comp.databases.postgresql.docs, comp.databases.postgresql.hackers
Subject: how to for postgres as website backend
Organization: Luna Consulting
User-Agent: MT-NewsWatcher/3.0 (PPC)
Message-ID: <pylqrp-7F0F31.16293620012000@news>
Lines: 15
Date: Thu, 20 Jan 2000 21:29:31 GMT
X-Trace: typhoon1.gnilink.net 948403771 151.196.11.187 (Thu,
20 Jan 2000 16:29:31 EST)
To:
pgsql-hackers@postgresql.org.pgsql-docs@postgresql.org.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 Thu Jan 20 16:48:32 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 QAA38451
for <pgsql-general@postgresql.org>;
Thu, 20 Jan 2000 16:48:14 -0500 (EST)
(envelope-from peter@localhost.its.uu.se)
Received: from regulus.its.uu.se ([130.238.7.19]:64259 "EHLO
regulus.its.uu.se")
by merganser.its.uu.se with ESMTP id <S278560AbQATVrk>;
Thu, 20 Jan 2000 22:47:40 +0100
Received: from peter (helo=localhost)
by regulus.its.uu.se with local-esmtp (Exim 3.02 #2)
id 12BPXj-000320-00; Thu, 20 Jan 2000 22:55:03 +0100
Date: Thu, 20 Jan 2000 22:55:03 +0100 (CET)
From: Peter Eisentraut <peter_e@gmx.net>
To: Martin Neumann <lists@mne.de>
cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Unsigned Datatypes
In-Reply-To: <E12B1Zz-0002eo-00@darwin.oche.de>
Message-ID: <Pine.LNX.4.21.0001202224160.349-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>
The standard doesn't provide for specifically unsigned types, so I do not
believe anyone would want to go through that trouble. Sorry.
On 2000-01-19, Martin Neumann mentioned:
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.
--
Peter Eisentraut Sernanders v�g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
From bouncefilter Thu Jan 20 16:48:39 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 QAA38474
for <pgsql-hackers@postgresql.org>;
Thu, 20 Jan 2000 16:48:25 -0500 (EST)
(envelope-from peter@localhost.its.uu.se)
Received: from regulus.its.uu.se ([130.238.7.19]:64466 "EHLO
regulus.its.uu.se")
by merganser.its.uu.se with ESMTP id <S268321AbQATVrx>;
Thu, 20 Jan 2000 22:47:53 +0100
Received: from peter (helo=localhost)
by regulus.its.uu.se with local-esmtp (Exim 3.02 #2)
id 12BPXr-000322-00; Thu, 20 Jan 2000 22:55:11 +0100
Date: Thu, 20 Jan 2000 22:55:11 +0100 (CET)
From: Peter Eisentraut <peter_e@gmx.net>
To: prlw1@cam.ac.uk
cc: PostgreSQL Development <pgsql-hackers@postgresql.org>
Subject: Re: [GENERAL] Variable case database names
In-Reply-To: <20000120151257.F12966@quartz.newn.cam.ac.uk>
Message-ID: <Pine.LNX.4.21.0001202242260.349-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>
There is some code in libpq which converts all database names to
lower-case, unless it's double quoted. That seems a little ill-conceived
to me, since you'd actually have to pass it something like
PGconnectdb("dbname=\"Newnham\"");
If anything, this would make it inconvenient it psql, because you'd have
to write
\c '"Newnham"'
since
\c "Newnham"
is interpreted differently.
Does anyone have an explanation for this? Why not leave the name as is?
On 2000-01-20, Patrick Welche mentioned:
List of databases
Database | Owner
------------+----------
Newnham | prlw1% psql Newnham
psql: connection to database "newnham" failed - FATAL 1: Database "newnham" does not exist in the system catalog.template1=> \c 'Newnham'
FATAL 1: Database "newnham" does not exist in the system catalog.
Previous connection keptHow can I connect to a database with a variable case name?
Cheers,
Patrick
************
--
Peter Eisentraut Sernanders v�g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
From bouncefilter Thu Jan 20 17:12:32 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 RAA48114
for <pgsql-hackers@postgresql.org>;
Thu, 20 Jan 2000 17:12:15 -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
RAA20758;
Thu, 20 Jan 2000 17:04:45 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200001202204.RAA20758@candle.pha.pa.us>
Subject: Re: [HACKERS] Re: [GENERAL] Variable case database names
In-Reply-To: <Pine.LNX.4.21.0001202242260.349-100000@localhost.localdomain>
from Peter Eisentraut at "Jan 20, 2000 10:55:11 pm"
To: Peter Eisentraut <peter_e@gmx.net>
Date: Thu, 20 Jan 2000 17:04:45 -0500 (EST)
CC: prlw1@cam.ac.uk, PostgreSQL Development <pgsql-hackers@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
[Charset ISO-8859-1 unsupported, filtering to ASCII...]
There is some code in libpq which converts all database names to
lower-case, unless it's double quoted. That seems a little ill-conceived
to me, since you'd actually have to pass it something like
PGconnectdb("dbname=\"Newnham\"");If anything, this would make it inconvenient it psql, because you'd have
to write
\c '"Newnham"'
since
\c "Newnham"
is interpreted differently.Does anyone have an explanation for this? Why not leave the name as is?
We do the same thing with queries, right? We force identifiers to lower
case unless quoted.
--
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 Thu Jan 20 17:19:32 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 RAA49441
for <pgsql-general@postgresql.org>;
Thu, 20 Jan 2000 17:18:53 -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
RAA23082;
Thu, 20 Jan 2000 17:18:36 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200001202218.RAA23082@candle.pha.pa.us>
Subject: Re: [GENERAL] Unsigned Datatypes
In-Reply-To: <Pine.LNX.4.21.0001202224160.349-100000@localhost.localdomain>
from Peter Eisentraut at "Jan 20, 2000 10:55:03 pm"
To: Peter Eisentraut <peter_e@gmx.net>
Date: Thu, 20 Jan 2000 17:18:36 -0500 (EST)
CC: Martin Neumann <lists@mne.de>, 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
[Charset ISO-8859-1 unsupported, filtering to ASCII...]
The standard doesn't provide for specifically unsigned types, so I do not
believe anyone would want to go through that trouble. Sorry.
Actually, oid data type is an unsigned int4. If it does not behave that
way, it is a bug.
--
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 Thu Jan 20 17:42:33 2000
Received: from mailbox.reptiles.org (IDENT:root@mailbox.reptiles.org
[198.96.117.155]) by hub.org (8.9.3/8.9.3) with ESMTP id RAA61706
for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 17:41:43 -0500 (EST)
(envelope-from jim@reptiles.org)
Received: from localhost (1349 bytes) by mailbox.reptiles.org
via sendmail with P:stdio/R:bind_hosts/T:inet_zone_bind_smtp
(sender: <jim>) (ident <jim> using unix)
id <m12BQFw-00080QC@mailbox.reptiles.org>
for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 17:40:44 -0500 (EST)
(Smail-3.2.0.108 1999-Sep-19 #3 built 1999-Oct-27)
Date: Thu, 20 Jan 2000 17:40:44 -0500
From: Jim Mercer <jim@reptiles.org>
To: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: Peter Eisentraut <peter_e@gmx.net>, Martin Neumann <lists@mne.de>,
pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] Unsigned Datatypes
Message-ID: <20000120174044.D4188@reptiles.org>
References: <Pine.LNX.4.21.0001202224160.349-100000@localhost.localdomain>
<200001202218.RAA23082@candle.pha.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Mailer: Mutt 1.0i
In-Reply-To: <200001202218.RAA23082@candle.pha.pa.us>;
from pgman@candle.pha.pa.us on Thu, Jan 20, 2000 at 05:18:36PM
-0500
On Thu, Jan 20, 2000 at 05:18:36PM -0500, Bruce Momjian wrote:
The standard doesn't provide for specifically unsigned types, so I do not
believe anyone would want to go through that trouble. Sorry.Actually, oid data type is an unsigned int4. If it does not behave that
way, it is a bug.
is this just in pgsql, or in SQL in general?
--
[ Jim Mercer jim@reptiles.org +1 416 506-0654 ]
[ Reptilian Research -- Longer Life through Colder Blood ]
[ Don't be fooled by cheap Finnish imitations; BSD is the One True Code. ]
From bouncefilter Thu Jan 20 17:52:33 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 RAA64033
for <pgsql-hackers@postgresql.org>;
Thu, 20 Jan 2000 17:52:09 -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 RAA25189;
Thu, 20 Jan 2000 17:51:43 -0500 (EST)
To: Peter Eisentraut <peter_e@gmx.net>
cc: prlw1@cam.ac.uk, PostgreSQL Development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Re: [GENERAL] Variable case database names
In-reply-to: <Pine.LNX.4.21.0001202242260.349-100000@localhost.localdomain>
References: <Pine.LNX.4.21.0001202242260.349-100000@localhost.localdomain>
Comments: In-reply-to Peter Eisentraut <peter_e@gmx.net>
message dated "Thu, 20 Jan 2000 22:55:11 +0100"
Date: Thu, 20 Jan 2000 17:51:43 -0500
Message-ID: <25186.948408703@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
Peter Eisentraut <peter_e@gmx.net> writes:
There is some code in libpq which converts all database names to
lower-case, unless it's double quoted. That seems a little ill-conceived
to me,
I think you are probably right. The backend might try to lowercase the
name when it gets it, but it seems like libpq shouldn't be doing so
(any more than it's responsible for downcasing identifiers used in
SQL commands).
If the backend *does* lowercase the DB name used in a connect command,
is there any way to use a mixed-case DB name? I'm not sure there is...
regards, tom lane
From bouncefilter Thu Jan 20 17:57:33 2000
Received: from portal.west.saic.com (portal.west.saic.com [198.151.12.15])
by hub.org (8.9.3/8.9.3) with SMTP id RAA65193
for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 17:56:57 -0500 (EST)
(envelope-from officers@aries.tucson.saic.com)
Received: from gatespass.tucson.saic.com by portal.west.saic.com
via smtpd (for postgresql.org [216.126.84.28]) with SMTP;
20 Jan 2000 22:56:56 UT
Received: (from adm@localhost) by gatespass.tucson.saic.com (8.8.2/8.8.2) id
PAA05677 for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 15:56:54 -0700 (MST)
X-Authentication-Warning: gatespass.tucson.saic.com: adm set sender to
<officers@aries.tucson.saic.com> using -f
Received: from wasp.tucson.saic.com(139.121.3.171) by
gatespass.tucson.saic.com via smap (V2.0)
id xma005674; Thu, 20 Jan 00 15:56:27 -0700
Received: from aries.tucson.saic.com (localhost [127.0.0.1]) by
wasp.tucson.saic.com (980427.SGI.8.8.8/950213.SGI.AUTOCF) via
ESMTP id PAA65792 for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 15:56:24 -0700 (MST)
Sender: officers@aries.tucson.saic.com
Message-ID: <38879298.DBEBDF94@aries.tucson.saic.com>
Date: Thu, 20 Jan 2000 15:56:24 -0700
From: Sarah Officer <officers@aries.tucson.saic.com>
X-Mailer: Mozilla 4.07C-SGI [en] (X11; I; IRIX 6.5 IP32)
MIME-Version: 1.0
To: pgsql-general@postgreSQL.org
Subject: < and > for datetime
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Hi all,
I'm trying to find less than and greater than functions for
datetime types. It doesn't look the the time interval operators
would be appropriate, but I haven't found anything else in the
docs. Here's my snippet of plpgsql which fails:
select count(*) into rescount
from currentresults cr, masters m
where cr.ben = new.ben
and m.mi_id = cr.mi_id
and m.idate > mydate;
rescount & mydate are variables. How do I compare dates? I'd be
glad to read the documentation if I could find the right page.
Thanks,
Sarah
officers@aries.tucson.saic.com
From bouncefilter Thu Jan 20 18:00:33 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 SAA66591
for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 18:00:31 -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
RAA26330;
Thu, 20 Jan 2000 17:58:47 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200001202258.RAA26330@candle.pha.pa.us>
Subject: Re: [GENERAL] Unsigned Datatypes
In-Reply-To: <20000120174044.D4188@reptiles.org> from Jim Mercer at "Jan 20,
2000 05:40:44 pm"
To: Jim Mercer <jim@reptiles.org>
Date: Thu, 20 Jan 2000 17:58:47 -0500 (EST)
CC: Peter Eisentraut <peter_e@gmx.net>, Martin Neumann <lists@mne.de>,
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
On Thu, Jan 20, 2000 at 05:18:36PM -0500, Bruce Momjian wrote:
The standard doesn't provide for specifically unsigned types, so I do not
believe anyone would want to go through that trouble. Sorry.Actually, oid data type is an unsigned int4. If it does not behave that
way, it is a bug.is this just in pgsql, or in SQL in general?
Just PostgreSQL. Others don't have oids.
--
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 Thu Jan 20 18:16:33 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 SAA76507
for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 18:15:58 -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 7824E146E7C; Fri, 21 Jan 2000 00:15:46 +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 <m12BQnq-000bKLC@finlandia.Infodrom.North.DE>
for pgsql-general@postgreSQL.org; Fri, 21 Jan 2000 00:15:46 +0100 (CET)
Date: Fri, 21 Jan 2000 00:15:46 +0100
From: Martin Schulze <joey@finlandia.Infodrom.North.DE>
To: Sarah Officer <officers@aries.tucson.saic.com>
Cc: pgsql-general@postgreSQL.org
Subject: Re: < and > for datetime
Message-ID: <20000121001546.F24508@finlandia.infodrom.north.de>
Reply-To: Martin Schulze <joey@infodrom.north.de>
References: <38879298.DBEBDF94@aries.tucson.saic.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Mutt/1.0i
In-Reply-To: <38879298.DBEBDF94@aries.tucson.saic.com>;
from officers@aries.tucson.saic.com on Thu, Jan 20, 2000 at
03:56:24PM -0700
Sarah Officer wrote:
Hi all,
I'm trying to find less than and greater than functions for
datetime types. It doesn't look the the time interval operators
would be appropriate, but I haven't found anything else in the
docs. Here's my snippet of plpgsql which fails:select count(*) into rescount
from currentresults cr, masters m
where cr.ben = new.ben
and m.mi_id = cr.mi_id
and m.idate > mydate;rescount & mydate are variables. How do I compare dates? I'd be
glad to read the documentation if I could find the right page.
\do shows:
<= |datetime |datetime |bool |less-than-or-equal
<> |datetime |datetime |bool |not equal
= |datetime |datetime |bool |equal
|datetime |datetime |bool |greater-than
< |datetime |datetime |bool |less-than
Thus, your WHERE-clause should be proper.
Regards,
Joey
--
This is Linux Country. On a quiet night, you can hear Windows reboot.
From bouncefilter Thu Jan 20 18:30:33 2000
Received: from portal.west.saic.com (portal.west.saic.com [198.151.12.15])
by hub.org (8.9.3/8.9.3) with SMTP id SAA79488
for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 18:29:58 -0500 (EST)
(envelope-from officers@aries.tucson.saic.com)
Received: from gatespass.tucson.saic.com by portal.west.saic.com
via smtpd (for postgresql.org [216.126.84.28]) with SMTP;
20 Jan 2000 23:29:57 UT
Received: (from adm@localhost) by gatespass.tucson.saic.com (8.8.2/8.8.2) id
QAA05906; Thu, 20 Jan 2000 16:29:26 -0700 (MST)
X-Authentication-Warning: gatespass.tucson.saic.com: adm set sender to
<officers@aries.tucson.saic.com> using -f
Received: from wasp.tucson.saic.com(139.121.3.171) by
gatespass.tucson.saic.com via smap (V2.0)
id xma005902; Thu, 20 Jan 00 16:29:08 -0700
Received: from aries.tucson.saic.com (localhost [127.0.0.1]) by
wasp.tucson.saic.com (980427.SGI.8.8.8/950213.SGI.AUTOCF) via
ESMTP id QAA62244; Thu, 20 Jan 2000 16:29:06 -0700 (MST)
Sender: officers@aries.tucson.saic.com
Message-ID: <38879A41.A8A3AF4E@aries.tucson.saic.com>
Date: Thu, 20 Jan 2000 16:29:05 -0700
From: Sarah Officer <officers@aries.tucson.saic.com>
X-Mailer: Mozilla 4.07C-SGI [en] (X11; I; IRIX 6.5 IP32)
MIME-Version: 1.0
To: Martin Schulze <joey@infodrom.north.de>, pgsql-general@postgreSQL.org
Subject: Re: < and > for datetime
References: <38879298.DBEBDF94@aries.tucson.saic.com>
<20000121001546.F24508@finlandia.infodrom.north.de>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Thanks. Oops. Now I see that I had one datetime and one date.
I can't do \do on my machine. Many of the psql backslash commands
cause an error. Does anyone know what could cause this error? I am
running version 6.5.3 on an IRIX 6.5.
db=> \do
ERROR: typeidTypeRelid: Invalid type - oid = 0
Thanks,
Sarah Officer
officers@aries.tucson.saic.com
Martin Schulze wrote:
Sarah Officer wrote:
Hi all,
I'm trying to find less than and greater than functions for
datetime types. It doesn't look the the time interval operators
would be appropriate, but I haven't found anything else in the
docs. Here's my snippet of plpgsql which fails:select count(*) into rescount
from currentresults cr, masters m
where cr.ben = new.ben
and m.mi_id = cr.mi_id
and m.idate > mydate;rescount & mydate are variables. How do I compare dates? I'd be
glad to read the documentation if I could find the right page.\do shows:
<= |datetime |datetime |bool |less-than-or-equal
<> |datetime |datetime |bool |not equal
= |datetime |datetime |bool |equal|datetime |datetime |bool |greater-than
< |datetime |datetime |bool |less-than
Thus, your WHERE-clause should be proper.
Regards,
Joey
--
This is Linux Country. On a quiet night, you can hear Windows reboot.
From bouncefilter Thu Jan 20 20:42:35 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 UAA24619
for <pgsql-general@postgresql.org>;
Thu, 20 Jan 2000 20:42:08 -0500 (EST)
(envelope-from e.bras@hccnet.nl)
Received: from pietjepuik by smtp.hccnet.nl
via uds144-117.dial.hccnet.nl [193.173.117.144] with SMTP for
<pgsql-general@postgresql.org>
id CAA16728 (8.8.5/1.11); Fri, 21 Jan 2000 02:42:22 +0100 (MET)
From: "Ed Bras" <e.bras@hccnet.nl>
To: <pgsql-general@postgresql.org>
Subject: help ?: "stat failed on /usr/lib/pgsql/plpgsql.so;
no file or directory" ???
Date: Fri, 21 Jan 2000 02:39:35 +0100
Message-ID: <004801bf63b0$603fefa0$e0ecf1c3@pietjepuik>
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="----=_NextPart_000_0049_01BF63B8.C20457A0"
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook CWS, Build 9.0.2416 (9.0.2910.0)
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Importance: Normal
This is a multi-part message in MIME format.
------=_NextPart_000_0049_01BF63B8.C20457A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
I am using psql for about the first time and created some tables and
triggers with their functions written in PL/pgSQL... No errors so far.
I loaded the plpgsql.so (which I wrote down at het bottom of the mail) with
neither an error.
However when I ecxute a very simple short function which has the language
set to plpgsql it gives me the following error: "stat failed on
/usr/lib/pgsql/plpgsql.so; no file or directory"
Anyone an idea what the problem is ?? The directory /usr/lib/pgsql/ excits
but has the owner set to root. I changed the owner of the file to postgres,
but as postgres executing the small program it still gave me the same error
(postgres had read and write error to the file but not to the directory)
It is probably a "right error" but which rights should I give the directory
without everyone accessing them ?
But when it can locate the file, then why doen't it give the same error when
you declare the pl/pgsql handler ?
Can someone tell me where he has this file located and who are the owner of
the file and path as well as the rights ?
I noticed that in the example in the documentation they locate the
plpgsql.so file in /usr/local/pgsql/lib/, which is not were it resides on my
machine !!
Regards,
Ed Bras
CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
'/usr/lib/pgsql/plpgsql.so ' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';----
------=_NextPart_000_0049_01BF63B8.C20457A0
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>
<P><FONT size=3D2>I am using psql for about the first time and created =
some tables=20
and triggers with their functions written in PL/pgSQL... No errors so=20
far.</FONT></P>
<P><FONT size=3D2>I loaded the plpgsql.so (which I wrote down at het =
bottom of the=20
mail) with neither an error.</FONT></P>
<P><FONT size=3D2>However when I ecxute a very simple short function =
which has the=20
language set to plpgsql it gives me the following error: "stat failed on =
/usr/lib/pgsql/plpgsql.so; no file or directory"</FONT></P>
<P><FONT size=3D2>Anyone an idea what the problem is ?? The directory=20
/usr/lib/pgsql/ excits but has the owner set to root. I changed the =
owner of the=20
file to postgres, but as postgres executing the small program it still =
gave me=20
the same error (postgres had read and write error to the file but not to =
the=20
directory)</FONT></P>
<P><FONT size=3D2>It is probably a "right error" but which rights should =
I give=20
the directory without everyone accessing them ?</FONT></P>
<P><FONT size=3D2>But when it can locate the file, then why doen't it =
give the=20
same error when you declare the pl/pgsql handler ?</FONT></P>
<P><FONT size=3D2>Can someone tell me where he has this file located and =
who are=20
the owner of the file and path as well as the rights ?</FONT></P>
<P><FONT size=3D2>I noticed that in the example in the documentation =
they locate=20
the plpgsql.so file in /usr/local/pgsql/lib/, which is not were it =
resides on my=20
machine !!</FONT></P>
<P><FONT size=3D2>Regards,</FONT></P>
<P><FONT size=3D2>Ed Bras</FONT></P>
<P> </P>
<P><FONT size=3D2>CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE =
AS</FONT></P>
<P><FONT size=3D2>'/usr/lib/pgsql/plpgsql.so ' LANGUAGE 'C';</FONT></P>
<P><FONT size=3D2>CREATE TRUSTED PROCEDURAL LANGUAGE =
'plpgsql'</FONT></P>
<P><FONT size=3D2>HANDLER plpgsql_call_handler</FONT></P>
<P><FONT size=3D2>LANCOMPILER 'PL/pgSQL';----</FONT></P></BODY></HTML>
------=_NextPart_000_0049_01BF63B8.C20457A0--
From bouncefilter Thu Jan 20 20:43:35 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 UAA24700
for <pgsql-general@postgresql.org>;
Thu, 20 Jan 2000 20:42:45 -0500 (EST)
(envelope-from e.bras@hccnet.nl)
Received: from pietjepuik by smtp.hccnet.nl
via uds144-117.dial.hccnet.nl [193.173.117.144] with SMTP for
<pgsql-general@postgresql.org>
id CAA16770 (8.8.5/1.11); Fri, 21 Jan 2000 02:43:00 +0100 (MET)
From: "Ed Bras" <e.bras@hccnet.nl>
To: <pgsql-general@postgresql.org>
Subject: Where is the Database log file ?
Date: Fri, 21 Jan 2000 02:40:14 +0100
Message-ID: <004d01bf63b0$76f64c30$e0ecf1c3@pietjepuik>
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="----=_NextPart_000_004E_01BF63B8.D8BAB430"
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook CWS, Build 9.0.2416 (9.0.2910.0)
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Importance: Normal
This is a multi-part message in MIME format.
------=_NextPart_000_004E_01BF63B8.D8BAB430
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
In th documentation thay talk about a database log file which is used in for
example RAISE NOTICE.
Where can I find this (the log file) ? and how can I see this kind of
information in the system tables ?
Regards,
Ed Bras
------=_NextPart_000_004E_01BF63B8.D8BAB430
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=3D188182822-18012000>In th =
documentation=20
thay talk about a database log file which is used in for example RAISE=20
NOTICE.</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D188182822-18012000>Where =
can I find=20
this (the log file) ? and how can I see this kind of information in =
the=20
system tables ?</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D188182822-18012000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D188182822-18012000>Regards,</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D188182822-18012000>Ed=20
Bras</SPAN></FONT></DIV></BODY></HTML>
------=_NextPart_000_004E_01BF63B8.D8BAB430--
From bouncefilter Thu Jan 20 20:43:35 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 UAA24727
for <pgsql-general@postgresql.org>;
Thu, 20 Jan 2000 20:42:56 -0500 (EST)
(envelope-from e.bras@hccnet.nl)
Received: from pietjepuik by smtp.hccnet.nl
via uds144-117.dial.hccnet.nl [193.173.117.144] with SMTP for
<pgsql-general@postgresql.org>
id CAA16790 (8.8.5/1.11); Fri, 21 Jan 2000 02:43:10 +0100 (MET)
From: "Ed Bras" <e.bras@hccnet.nl>
To: <pgsql-general@postgresql.org>
Subject: Datatype serial ?
Date: Fri, 21 Jan 2000 02:40:25 +0100
Message-ID: <005201bf63b0$7d46f760$e0ecf1c3@pietjepuik>
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="----=_NextPart_000_0053_01BF63B8.DF0B5F60"
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook CWS, Build 9.0.2416 (9.0.2910.0)
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Importance: Normal
This is a multi-part message in MIME format.
------=_NextPart_000_0053_01BF63B8.DF0B5F60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
To me it is not realy clear form the documentation what is exactly the
datatype "serial" ?
Can somenone give me an small example of when and how to use it ?
Regards,
Ed Bras
------=_NextPart_000_0053_01BF63B8.DF0B5F60
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=3D188182822-18012000>To me =
it is not=20
realy clear form the documentation what is exactly the datatype "serial" =
?</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D188182822-18012000>Can =
somenone give me=20
an small example of when and how to use it ?</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D188182822-18012000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D188182822-18012000>Regards,</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D188182822-18012000>Ed=20
Bras</SPAN></FONT></DIV></BODY></HTML>
------=_NextPart_000_0053_01BF63B8.DF0B5F60--
From bouncefilter Thu Jan 20 20:58:35 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 UAA27503
for <pgsql-general@postgresql.org>;
Thu, 20 Jan 2000 20:58:12 -0500 (EST)
(envelope-from e.bras@hccnet.nl)
Received: from pietjepuik by smtp.hccnet.nl
via uds144-117.dial.hccnet.nl [193.173.117.144] with SMTP for
<pgsql-general@postgresql.org>
id CAA18225 (8.8.5/1.11); Fri, 21 Jan 2000 02:58:26 +0100 (MET)
From: "Ed Bras" <e.bras@hccnet.nl>
To: <pgsql-general@postgresql.org>
Subject: stat failed on /usr/lib/pgsql/plpgsql.so; no file or directory
Date: Fri, 21 Jan 2000 02:55:40 +0100
Message-ID: <006501bf63b2$9efc5a10$e0ecf1c3@pietjepuik>
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="----=_NextPart_000_0066_01BF63BB.00C0C210"
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook CWS, Build 9.0.2416 (9.0.2910.0)
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Importance: Normal
This is a multi-part message in MIME format.
------=_NextPart_000_0066_01BF63BB.00C0C210
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
I am using psql for about the first time and created some tables and
triggers with their functions written in PL/pgSQL... No errors so far.
I loaded the plpgsql.so (which I wrote down at het bottom of the mail) with
neither an error.
However when I ecxute a very simple short function which has the language
set to plpgsql it gives me the following error: "stat failed on
/usr/lib/pgsql/plpgsql.so; no file or directory"
Anyone an idea what the problem is ?? The directory /usr/lib/pgsql/ excits
but has the owner set to root. I changed the owner of the file to postgres,
but as postgres executing the small program it still gave me the same error
(postgres had read and write error to the file but not to the directory)
It is probably a "right error" but which rights should I give the directory
without everyone accessing them ?
But when it can locate the file, then why doen't it give the same error when
you declare the pl/pgsql handler ?
Can someone tell me where he has this file located and who are the owner of
the file and path as well as the rights ?
I noticed that in the example in the documentation they locate the
plpgsql.so file in /usr/local/pgsql/lib/, which is not were it resides on my
machine !!
Regards,
Ed Bras
CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
'/usr/lib/pgsql/plpgsql.so ' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';----
------=_NextPart_000_0066_01BF63BB.00C0C210
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>
<P><FONT size=3D2>I am using psql for about the first time and created =
some tables=20
and triggers with their functions written in PL/pgSQL... No errors so=20
far.</FONT></P>
<P><FONT size=3D2>I loaded the plpgsql.so (which I wrote down at het =
bottom of the=20
mail) with neither an error.</FONT></P>
<P><FONT size=3D2>However when I ecxute a very simple short function =
which has the=20
language set to plpgsql it gives me the following error: "stat failed on =
/usr/lib/pgsql/plpgsql.so; no file or directory"</FONT></P>
<P><FONT size=3D2>Anyone an idea what the problem is ?? The directory=20
/usr/lib/pgsql/ excits but has the owner set to root. I changed the =
owner of the=20
file to postgres, but as postgres executing the small program it still =
gave me=20
the same error (postgres had read and write error to the file but not to =
the=20
directory)</FONT></P>
<P><FONT size=3D2>It is probably a "right error" but which rights should =
I give=20
the directory without everyone accessing them ?</FONT></P>
<P><FONT size=3D2>But when it can locate the file, then why doen't it =
give the=20
same error when you declare the pl/pgsql handler ?</FONT></P>
<P><FONT size=3D2>Can someone tell me where he has this file located and =
who are=20
the owner of the file and path as well as the rights ?</FONT></P>
<P><FONT size=3D2>I noticed that in the example in the documentation =
they locate=20
the plpgsql.so file in /usr/local/pgsql/lib/, which is not were it =
resides on my=20
machine !!</FONT></P>
<P><FONT size=3D2>Regards,</FONT></P>
<P><FONT size=3D2>Ed Bras</FONT></P>
<P> </P>
<P><FONT size=3D2>CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE =
AS</FONT></P>
<P><FONT size=3D2>'/usr/lib/pgsql/plpgsql.so ' LANGUAGE 'C';</FONT></P>
<P><FONT size=3D2>CREATE TRUSTED PROCEDURAL LANGUAGE =
'plpgsql'</FONT></P>
<P><FONT size=3D2>HANDLER plpgsql_call_handler</FONT></P>
<P><FONT size=3D2>LANCOMPILER 'PL/pgSQL';----</FONT></P></BODY></HTML>
------=_NextPart_000_0066_01BF63BB.00C0C210--
From bouncefilter Thu Jan 20 21:07:35 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 VAA32496
for <pgsql-general@postgreSQL.org>;
Thu, 20 Jan 2000 21:07:15 -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
VAA06119;
Thu, 20 Jan 2000 21:07:05 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200001210207.VAA06119@candle.pha.pa.us>
Subject: Re: [GENERAL] Datatype serial ?
In-Reply-To: <005201bf63b0$7d46f760$e0ecf1c3@pietjepuik> from Ed Bras at "Jan
21, 2000 02:40:25 am"
To: Ed Bras <e.bras@hccnet.nl>
Date: Thu, 20 Jan 2000 21:07:04 -0500 (EST)
CC: 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
See my book on the main web site under documentation.
[Charset iso-8859-1 unsupported, filtering to ASCII...]
To me it is not realy clear form the documentation what is exactly the
datatype "serial" ?
Can somenone give me an small example of when and how to use it ?Regards,
Ed Bras
--
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 Thu Jan 20 21:13:35 2000
Received: from castle (firewall-user@ns1.amgen.com [138.133.17.5])
by hub.org (8.9.3/8.9.3) with ESMTP id VAA33493
for <pgsql-general@hub.org>; Thu, 20 Jan 2000 21:12:55 -0500 (EST)
(envelope-from mdalphin@amgen.com)
Received: by castle; id SAA23965; Thu, 20 Jan 2000 18:12:54 -0800 (PST)
Received: from pacific.amgen.com(138.133.10.88) by castle.amgen.com via smap
(V4.2) id xma022020; Thu, 20 Jan 00 18:12:01 -0800
Received: from pacific.amgen.com (pacific [138.133.10.88])
by pacific.amgen.com (8.8.8+Sun/8.8.8) with ESMTP id SAA00611
for <pgsql-general@hub.org>; Thu, 20 Jan 2000 18:12:00 -0800 (PST)
Received: from mailbag.amgen.com (mailbag.amgen.com [138.133.10.78])
by pacific.amgen.com (8.8.8+Sun/8.8.8) with ESMTP id SAA00607
for <pgsql-general@hub.org>; Thu, 20 Jan 2000 18:12:00 -0800 (PST)
Received: from amgen.com (mahunui [138.133.147.23])
by mailbag.amgen.com (8.8.5/8.8.5) with ESMTP id SAA23572
for <pgsql-general@hub.org>; Thu, 20 Jan 2000 18:11:57 -0800 (PST)
Sender: mdalphin@amgen.com
Message-ID: <3887C06F.5D661E@amgen.com>
Date: Thu, 20 Jan 2000 18:12:00 -0800
From: Mark Dalphin <mdalphin@amgen.com>
Organization: Amgen, Inc.
X-Mailer: Mozilla 4.6 [en] (X11; U; IRIX64 6.5 IP30)
X-Accept-Language: en
MIME-Version: 1.0
To: pgsql-general@hub.org
Subject: Re: weird sum() results
References: <200001200401.XAA92351@hub.org>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
On Wed, 19 Jan 2000 16:33:06, Hitesh Patel wrote:
I have a table with a filed named 'amount' and all the values are
positive values, but if i do this:
select sum(amount) from table where state = 'CA';
I get a negative number. Doing a
select name, amount from table where state = 'CA' and amount < '0';
Returns 0 rows. Any ideas as to why i'm getting a negative value for
the sum()?
If the sum() is larger than the largest possible integer, the values will "wrap around" into the negative
integers. I don't know how many values you are adding, but, assuming you are storing your amount as an
'int4', your sum will wrap at about 2 billion (2,147,483,647).
HTH,
Mark
--
Mark Dalphin email: mdalphin@amgen.com
Mail Stop: 29-2-A phone: +1-805-447-4951 (work)
One Amgen Center Drive +1-805-375-0680 (home)
Thousand Oaks, CA 91320 fax: +1-805-499-9955 (work)
From bouncefilter Thu Jan 20 21:53:36 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 VAA42908
for <pgsql-general@postgresql.org>;
Thu, 20 Jan 2000 21:53:30 -0500 (EST)
(envelope-from news@news.tht.net)
Received: (from news@localhost) by news.tht.net (8.9.3/8.9.3) id VAA59459
for pgsql-general@postgresql.org; Thu, 20 Jan 2000 21:46:22 -0500 (EST)
(envelope-from news)
X-Authentication-Warning: news.tht.net: news set sender to <news> using -f
From: "LKembel" <SPAMSUCKSlkembel@cgocable.net>
X-Newsgroups: comp.databases.postgresql.questions
Subject: MySQL
Lines: 20
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2615.200
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200
Message-ID: <0MPh4.11151$632.480649@news1.rdc2.on.home.com>
Date: Fri, 21 Jan 2000 02:46:20 GMT
X-Complaints-To: abuse@home.net
X-Trace: news1.rdc2.on.home.com 948422780 24.141.47.94 (Thu,
20 Jan 2000 18:46:20 PST)
Organization: @Home Network Canada
To: pgsql-questions@postgresql.org
Is there a MySQL newsgroup? Or even a newsgroup for general SQL
questions? I couldn't find one. I'd really appreciate it if someone could
point me towards one, or even better answer my question (I hope the fact
that this is off topic doesn't bother anyone). I'm trying to prepare a
string to be inserted into a MySQL server using quote(). What it does is
escape characters like ' with a backslash, so I end up with 'Lee\'s Info'.
The problem is that it's inserting the \ into the DB, when I call up that
entry I get a \ in it. If I leave out the \ then I get an error. Any help?
--
Lee Kembel
Freelance graphic design - http://www.hermthesperm.com/leekembel
I also handle HTML, Perl CGI, SQL databases, Shockwave/Flash, and animation.
From bouncefilter Thu Jan 20 21:53: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 VAA42907
for <pgsql-general@postgresql.org>;
Thu, 20 Jan 2000 21:53:30 -0500 (EST)
(envelope-from news@news.tht.net)
Received: (from news@localhost) by news.tht.net (8.9.3/8.9.3) id VAA59858
for pgsql-general@postgresql.org; Thu, 20 Jan 2000 21:52:59 -0500 (EST)
(envelope-from news)
X-Authentication-Warning: news.tht.net: news set sender to <news> using -f
From: "LKembel" <SPAMSUCKSlkembel@cgocable.net>
X-Newsgroups: comp.databases.postgresql.questions
References: <0MPh4.11151$632.480649@news1.rdc2.on.home.com>
Subject: Re: MySQL
Lines: 10
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2615.200
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200
Message-ID: <cSPh4.11162$632.481189@news1.rdc2.on.home.com>
Date: Fri, 21 Jan 2000 02:52:56 GMT
X-Complaints-To: abuse@home.net
X-Trace: news1.rdc2.on.home.com 948423176 24.141.47.94 (Thu,
20 Jan 2000 18:52:56 PST)
Organization: @Home Network Canada
To: pgsql-questions@postgresql.org
I should mention that when I insert the quoted string with the INSERT
command it removes the \, but when I use the UPDATE command it always leaves
the \ in.
--
Lee Kembel
Freelance graphic design - http://www.hermthesperm.com/leekembel
I also handle HTML, Perl CGI, SQL databases, Shockwave/Flash, and animation.
From bouncefilter Fri Jan 21 03:23:39 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 DAA33718
for <pgsql-general@postgresql.org>;
Fri, 21 Jan 2000 03:23:31 -0500 (EST)
(envelope-from news@news.tht.net)
Received: (from news@localhost) by news.tht.net (8.9.3/8.9.3) id CAA81630
for pgsql-general@postgresql.org; Fri, 21 Jan 2000 02:57:16 -0500 (EST)
(envelope-from news)
X-Authentication-Warning: news.tht.net: news set sender to <news> using -f
Message-ID: <38881322.7D5C6F39@imap3.asu.edu>
From: Benjamin Cochran <bdc33@imap3.asu.edu>
Reply-To: benjamin.cochran@asu.edu
X-Mailer: Mozilla 4.7 [en]C-CCK-MCD {TLC;RETAIL} (Win98; I)
X-Accept-Language: en
MIME-Version: 1.0
X-Newsgroups: comp.databases.postgresql.questions
Subject: trouble with the postmaster
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 20
X-Trace: tw12.nn.bcandid.com 948441434 209.210.32.36 (Fri,
21 Jan 2000 00:57:14 MST)
Organization: bCandid - Powering the world's discussions - http://bCandid.com
Date: Fri, 21 Jan 2000 07:57:14 GMT
To: pgsql-questions@postgresql.org
I'm having trouble starting the postmaster on ports other then the
default port with the -i option. It starts fine and everything runs well
if I type:
postmaster -i
And if I type:
postmaster -p 1234
it also works
but if I try:
postmaster -i -p 42
I get the error:
FATAL: StreamServerPort: bind() failed: Permission denied
Is another postmaster already running on that port?
If not wait a few seconds and retry.
/usr/local/pgsql/bin/postmaster: cannot create INET stream port
Can anyone give me any insight into my problem?
Thanks,
Ben
From bouncefilter Fri Jan 21 08:17:43 2000
Received: from infbssys.ips.cs.tu-bs.de (root@infbssys.ips.cs.tu-bs.de
[134.169.32.1]) by hub.org (8.9.3/8.9.3) with ESMTP id IAA06585
for <pgsql-general@postgresql.org>;
Fri, 21 Jan 2000 08:16:48 -0500 (EST)
(envelope-from mack@ips.cs.tu-bs.de)
Received: from infbssys.ips.cs.tu-bs.de (infbsdb1.idb.cs.tu-bs.de
[134.169.32.131])
by infbssys.ips.cs.tu-bs.de (8.9.3/8.9.3) with ESMTP id OAA08063
for <pgsql-general@postgresql.org>; Fri, 21 Jan 2000 14:16:45 +0100
Sender: mack@ips.cs.tu-bs.de
Message-ID: <38885C3D.E20C0468@infbssys.ips.cs.tu-bs.de>
Date: Fri, 21 Jan 2000 14:16:45 +0100
From: Thomas Mack <mack@ips.cs.tu-bs.de>
X-Mailer: Mozilla 4.7 [en] (X11; I; SunOS 5.6 sun4u)
X-Accept-Language: en
MIME-Version: 1.0
To: pgsql-general@postgresql.org
Subject: Too many open files...
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Well, we got a problem with postgres 6.5.2 on SOlaris 2.6, when:
1) Executing a query with a trigger behind it, making quite a big amount
of
queries on different relations,
2) and when this query is executed from an applet via a servlet on
Apache 1.3.3
using postgresql.jar as db driver.
So, the message is:
ERROR: Load of file /usr/dblocal/pgsql/lib/plpgsql.so failed: ld.so.1:
/usr/dblocal/pgsql/bin/postmaster: fatal: /dev/zero: open failed: Too
many open files
(or similar).
The problem does not exist, if we execute it via psql.
So I tracked it down to dlopen(filename,1). But really, I do not know,
what might
be the real reason for the failure: is it postgres (probably not),
Apache, restricting
the environment "somehow", or postgresql.jar (it should just give the
query to the
backend, so probably not as well), or Solaris itself (but why do I not
get an error,
if I execute it via psql?) or where should I check further?
I am a little confused on how to repair the problem, as it is most
probably not
a problem with postgres. Anybody had similar experiences or even a
solution (besides
not using the trigger)?
Thanks,
Thomas Mack
TU Braunschweig, Abt. Informationssysteme
From bouncefilter Fri Jan 21 08:48:43 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 IAA15174
for <pgsql-general@hub.org>; Fri, 21 Jan 2000 08:47:54 -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 OAA11050;
Fri, 21 Jan 2000 14:47:45 +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 12BfLE-0000eQ-00; Fri, 21 Jan 2000 14:47:12 +0000
Message-ID: <3888640A.62BF764E@sferacarta.com>
Date: Fri, 21 Jan 2000 14:50:02 +0100
From: Jose Soares <jose@sferacarta.com>
X-Mailer: Mozilla 4.6 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
To: Mark Dalphin <mdalphin@amgen.com>
CC: pgsql-general@hub.org
Subject: Re: [GENERAL] Re: weird sum() results
References: <200001200401.XAA92351@hub.org> <3887C06F.5D661E@amgen.com>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
The accumulator of SUM has the same type of the given argument and it may result in an overflow;
this kind of behavior is identical also for AVG function.
In the case of a SMALLINT (int2) or an INTEGER (int4) the overflow is silent.
In the case of a FLOAT PostgreSQL gives an error message like:
ERROR: Bad float8 input format -- overflow
To avoid undesired silent overflows you should use float8 function as in:
SUM ( float8 (argument) )
or SUM(int2*1.0)
SUM(int4*1.0)
SUM(int8*1.0)
Otherwise you may create your own SUM()/AVG() functions using int84pl as accumulator fot int4 and int42pl as
accumulator for int2. If you want an example I can send it to you.
Jos�
Mark Dalphin wrote:
On Wed, 19 Jan 2000 16:33:06, Hitesh Patel wrote:
I have a table with a filed named 'amount' and all the values are
positive values, but if i do this:
select sum(amount) from table where state = 'CA';
I get a negative number. Doing a
select name, amount from table where state = 'CA' and amount < '0';
Returns 0 rows. Any ideas as to why i'm getting a negative value for
the sum()?If the sum() is larger than the largest possible integer, the values will "wrap around" into the negative
integers. I don't know how many values you are adding, but, assuming you are storing your amount as an
'int4', your sum will wrap at about 2 billion (2,147,483,647).HTH,
Mark--
Mark Dalphin email: mdalphin@amgen.com
Mail Stop: 29-2-A phone: +1-805-447-4951 (work)
One Amgen Center Drive +1-805-375-0680 (home)
Thousand Oaks, CA 91320 fax: +1-805-499-9955 (work)************
From bouncefilter Fri Jan 21 09:06:44 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 JAA22031
for <pgsql-general@postgresql.org>;
Fri, 21 Jan 2000 09:06:12 -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 PAA05146;
Fri, 21 Jan 2000 15:05:54 +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 12Bfcn-0000eo-00; Fri, 21 Jan 2000 15:05:21 +0000
Message-ID: <3888684A.35294653@sferacarta.com>
Date: Fri, 21 Jan 2000 15:08:11 +0100
From: Jose Soares <jose@sferacarta.com>
X-Mailer: Mozilla 4.6 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
To: Hitesh Patel <hitesh@presys.com>
CC: "pgsql-general@postgreSQL.org" <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] weird sum() results
References: <38874E5B.8B581733@presys.com>
Content-Type: multipart/mixed; boundary="------------44D0DE2C0E8CC13BC70E48E6"
This is a multi-part message in MIME format.
--------------44D0DE2C0E8CC13BC70E48E6
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
You have to create a function to cast money to float.
Try the attached functions.
Jos�
Hitesh Patel wrote:
I can understand that. What's weird is that I can do a sum(amount) on
the whole table and the value comes out to only My only problem is that
the amount field is the money type and doesn't like the casting now. ASELECT SUM(amount::float8) FROM table where state = 'CA';
returns
ERROR: No such function 'float8' with the specified attributes
And again i'm stuck :-)
Thanks in advance for any help.
--
|---------------------------------|----------------------------|
| Hitesh Patel | Voice: (541) 759-3126 |
| Facilities Development Manager | Fax: (541) 759-3214 |
| Preferred Systems | Email: hitesh@presys.com |
|---------------------------------|----------------------------|************
--------------44D0DE2C0E8CC13BC70E48E6
Content-Type: text/plain; charset=us-ascii;
name="money.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="money.sql"
--cast text to money...
drop function money(unknown);
create function money(unknown) returns money as
'
begin
return $1;
end;
' language 'plpgsql';
--cast text to money...
drop function money(text);
create function money(text) returns money as
'
begin
return $1;
end;
' language 'plpgsql';
--cast int4 to money...
drop function money(int4);
create function money(int4) returns money as
'
begin
return money(float8($1));
end;
' language 'plpgsql';
--cast money to text...
drop function text(money);
create function text(money) returns text as
'
begin
return $1;
end;
' language 'plpgsql';
--cast float8 to money...
drop function money(float8);
create function money(float8) returns money as
'
declare
f2 float8;
m money;
i2 int2;
i1 int4;
txt text;
begin
if $1 isnull then
return NULL;
end if;
--integer part...
i1:= dtrunc($1);
-- decimal part...
i2:= dround(datetime_part(''millisecond'',$1));
-- cut 3th digit...
txt:= dround(i2/10.0);
if textlen(txt) = 1 then
txt:= ''0'' || txt;
end if;
m:= i1 || (''.'' || txt);
return m;
end;
' language 'plpgsql';
create table a ( f4 float, a money);
insert into a values ('1','1.23');
select cast(89123 as money);
select cast(f4 as money) from a;
select * from a;
select *,cast(f4 as money),cast(a as text) from a;
select cast(float8(-12.3) as money);
select cast( 123/10.0 as money);
select cast( 3.4e+38 as money);
select money( 12.3 );
select money( '12.3'::text );
select money( '12.3' );
select money( 12 );
drop table a;
-- +++++++ crea operatori per money...
drop function cash_pl(float8,money);
drop operator + (float8,money);
create function cash_pl(float8,money) returns money as
'
declare
m money;
begin
m:= $1;
return $2 + m;
end;
' language 'plpgsql';
create operator + (
leftarg=float8,
rightarg=money,
procedure=cash_pl,
commutator='+',
negator='-',
restrict=eqsel,
join=eqjoinsel
);
drop function cash_pl(money,float8);
drop operator + (money,float8);
create function cash_pl(money,float8) returns money as
'
declare
m money;
begin
m:= $2;
return $1 + m;
end;
' language 'plpgsql';
create operator + (
leftarg=money,
rightarg=float8,
procedure=cash_pl,
commutator='+',
negator='-',
restrict=eqsel,
join=eqjoinsel
);
select sal,comm+1.0 from emp;
select sal,1.0+comm from emp;
-----------crea operatori per money...
drop function cash_mi(float8,money);
drop operator - (float8,money);
create function cash_mi(float8,money) returns money as
'
declare
m money;
begin
m:= $1;
return m - $2;
end;
' language 'plpgsql';
create operator - (
leftarg=float8,
rightarg=money,
procedure=cash_mi,
commutator='-',
negator='+',
restrict=eqsel,
join=eqjoinsel
);
drop function cash_mi(money,float8);
drop operator - (money,float8);
create function cash_mi(money,float8) returns money as
'
declare
m money;
begin
m:= $2;
return $1 - m;
end;
' language 'plpgsql';
create operator - (
leftarg=money,
rightarg=float8,
procedure=cash_mi,
commutator='-',
negator='+',
restrict=eqsel,
join=eqjoinsel
);
select comm,comm-1.0 from emp;
select comm,1.0-comm from emp;
-- /////// crea operatori per money...
drop function float8_div_cash(float8,money);
drop operator / (float8,money);
create function float8_div_cash(float8,money) returns money as
'
declare
f2 float8;
begin
if $1 isnull then
return NULL;
end if;
f2:= float8($2);
f2:= $1 / f2;
return money(f2);
end;
' language 'plpgsql';
create operator / (
leftarg=float8,
rightarg=money,
procedure=float8_div_cash,
commutator='/',
negator='*',
restrict=eqsel,
join=eqjoinsel
);
drop function cash_div_cash(money,money);
drop operator / (money,money);
create function cash_div_cash(money,money) returns money as
'
declare
f2 float;
begin
if $1 IS NOT NULL THEN
f2:= float8($2);
return cash_div_flt8($1,f2);
else
return NULL;
end if;
end;
' language 'plpgsql';
create operator / (
leftarg=money,
rightarg=money,
procedure=cash_div_cash,
commutator='/',
negator='*',
restrict=eqsel,
join=eqjoinsel
);
select comm,comm/1500.00 from emp ;
select comm,comm/(comm-100) from emp ;
select comm,1601.01/comm from emp;
select comm,'$1,601.01'/comm from emp;
select comm,'$1,601.01'/comm from emp;
select 1655111.33 / comm from emp;
select '$1655111.33' / comm from emp;
-- ******* crea operatori per money...
drop function cash_mul_cash(money,money);
drop operator * (money,money);
create function cash_mul_cash(money,money) returns money as
'
declare
f2 float;
f3 text;
m money;
begin
if $1 IS NULL THEN
return NULL;
end if;
f2:= float8($2);
f3:= cash_mul_flt8($1,f2);
if f3 = ''($./,,),,(-*.,()'' then
return NULL;
else
return cash_mul_flt8($1,f2);
end if;
end;
' language 'plpgsql';
create operator * (
leftarg=money,
rightarg=money,
procedure=cash_mul_cash,
commutator='*',
negator='/',
restrict=eqsel,
join=eqjoinsel
);
select comm,comm*'$1.0'::money from emp;
select comm,comm*1.0 from emp;
select comm,sal * (sal+sal+sal) from emp;
-- CAST money to float8:
drop function float8(money);
create function float8(money) returns float8 as
'
declare
stringa text;
part1 text;
part2 text;
i int2;
begin
stringa:= $1;
--checks for negative sign (if enclosed by parens)...
if substr(stringa,1,1) = ''('' then
i:= textlen(stringa);
stringa:= ''-'' || substr(stringa,3,i - 3);
else
stringa:= substr(stringa,2); --skip currency sign
end if;
loop
--strip commas...
i:= textpos(stringa,'','');
if i = 0 then
return stringa;
end if;
part1:= substr(stringa,1,i - 1);
part2:= substr(stringa,i + 1);
stringa:= part1 || part2;
end loop;
end;
' language 'plpgsql';
create table a (m money);
insert into a values('1.22');
select cast('($100.12)' as money);
select cast(m as float) from a;
select * from a;
drop table a;
-- crea operatori per money e float...
drop function cash_eq(money,float8);
drop operator = (money,float8);
create function cash_eq(money,float8) returns bool as
'
declare
m2 money;
begin
m2:= $2;
return $1 = m2;
end;
' language 'plpgsql';
create operator = (
leftarg=money,
rightarg=float8,
procedure=cash_eq,
commutator='=',
negator='<>',
restrict=eqsel,
join=eqjoinsel
);
drop function cash_ne(money,float8);
drop operator <> (money,float8);
create function cash_ne(money,float8) returns bool as
'
declare
m2 money;
begin
m2:= $2;
return $1 <> m2;
end;
' language 'plpgsql';
create operator <> (
leftarg=money,
rightarg=float8,
procedure=cash_ne,
commutator='<>',
negator='=',
restrict=eqsel,
join=eqjoinsel
);
drop function cash_gt(money,float8);
drop operator > (money,float8);
create function cash_gt(money,float8) returns bool as
'
declare
m2 money;
begin
m2:= $2;
return $1 > m2;
end;
' language 'plpgsql';
create operator > (
leftarg=money,
rightarg=float8,
procedure=cash_gt,
commutator='>',
negator='<=',
restrict=eqsel,
join=eqjoinsel
);
-- crea operatori per float e money...
drop function cash_eq(float8,money);
drop operator = (float8,money);
create function cash_eq(float8,money) returns bool as
'
declare
m1 money;
begin
m1:= $1;
return $2 = m1;
end;
' language 'plpgsql';
create operator = (
leftarg=float8,
rightarg=money,
procedure=cash_eq,
commutator='=',
negator='<>',
restrict=eqsel,
join=eqjoinsel
);
drop function cash_lt(float8,money);
drop operator < (float8,money);
create function cash_lt(float8,money) returns bool as
'
declare
m1 money;
begin
m1:= $1;
return m1 < $2;
end;
' language 'plpgsql';
create operator < (
leftarg=float8,
rightarg=money,
procedure=cash_lt,
commutator='<',
negator='>=',
restrict=eqsel,
join=eqjoinsel
);
drop function cash_lt(money,float8);
drop operator < (money,float8);
create function cash_lt(money,float8) returns bool as
'
declare
m2 money;
begin
m2:= $2;
return $1 < m2;
end;
' language 'plpgsql';
create operator < (
leftarg=money,
rightarg=float8,
procedure=cash_lt,
commutator='<',
negator='>=',
restrict=eqsel,
join=eqjoinsel
);
drop function cash_ne(float8,money);
drop operator <> (float8,money);
create function cash_ne(float8,money) returns bool as
'
declare
m1 money;
begin
m1:= $1;
return $2 <> m1;
end;
' language 'plpgsql';
create operator <> (
leftarg=float8,
rightarg=money,
procedure=cash_ne,
commutator='<>',
negator='=',
restrict=eqsel,
join=eqjoinsel
);
drop function cash_gt(float8,money);
drop operator > (float8,money);
create function cash_gt(float8,money) returns bool as
'
declare
m1 money;
begin
m1:= $1;
return m1 > $2;
end;
' language 'plpgsql';
create operator > (
leftarg=float8,
rightarg=money,
procedure=cash_gt,
commutator='>',
negator='<=',
restrict=eqsel,
join=eqjoinsel
);
drop table emp;
create table emp( comm money);
insert into emp values('1700');
insert into emp values('1600');
insert into emp values('1500');
insert into emp values('1400');
select comm,comm + comm from emp;
select comm,comm + 1 from emp;
select comm,1 + comm from emp;
select comm,comm - comm from emp;
select comm,comm - 1 from emp;
select comm,1 - comm from emp;
select comm,comm * comm from emp;
select comm,comm * -1 as meno from emp;
select comm,-1 * comm as meno from emp;
select comm,comm / comm from emp;
select comm,comm / -1 as meno from emp;
select comm,-1 / comm as meno from emp;
select * from emp where comm = 1600;
select * from emp where comm = '$1,600.00';
select * from emp where '$1,600.00' = comm;
select * from emp where 1600.00 = comm;
-------------------
select * from emp where comm <> 1600;
select * from emp where comm <> '$1,600.00';
select * from emp where 1600.00 <> comm;
select * from emp where '$1,600.00' <> comm;
-------------------
select * from emp where comm > 1600;
select * from emp where comm > '$1,600.00';
select * from emp where '$1,600.00' < comm;
select * from emp where 1600.00 < comm;
-------------------
select * from emp where comm < 1600;
select * from emp where comm < '$1,600.00';
select * from emp where 1600.00 > comm;
select * from emp where '$1,600.00' > comm;
--------------44D0DE2C0E8CC13BC70E48E6--
From bouncefilter Fri Jan 21 10:13:44 2000
Received: from rusfw.rsd.de (rusfw.rsd.de [195.232.115.66])
by hub.org (8.9.3/8.9.3) with SMTP id KAA41139
for <pgsql-general@postgresql.org>;
Fri, 21 Jan 2000 10:13:38 -0500 (EST)
(envelope-from Konrad.Pscheidl@RSD.rsd.de)
From: Konrad.Pscheidl@RSD.rsd.de
Received: from rus11.rsd.de by rusfw.rsd.de
via smtpd (for postgresql.org [216.126.84.28]) with SMTP;
21 Jan 2000 16:13:39 UT
Received: by mail.rsd.de(Lotus SMTP MTA v4.6.6 (890.1 7-16-1999)) id
C125686D.0053A7D1 ; Fri, 21 Jan 2000 16:13:44 +0100
X-Lotus-FromDomain: RSD@RUS
Sender: Konrad.Pscheidl@RSD.rsd.de
To: pgsql-general@postgresql.org
Message-ID: <C125686D.00539A5D.00@mail.rsd.de>
Date: Fri, 21 Jan 2000 16:11:20 +0100
Subject: pg_log
Mime-Version: 1.0
Content-type: text/plain; charset=us-ascii
Content-Disposition: inline
Hello,
is the file pg_log in the $PGDATA directory a transaction log file? Are there
any tools to deal with it
( e.g. to delete it while postgres is running )
What's about the log file under var/log. Do I have to delete this manually?
(We use postgreSQL 6.4-9)
Regards
Konrad Pscheidl
From bouncefilter Fri Jan 21 10:24:45 2000
Received: from w3.unica.edu (unica.edu [194.133.59.58] (may be forged))
by hub.org (8.9.3/8.9.3) with ESMTP id KAA43798
for <pgsql-general@postgreSQL.org>;
Fri, 21 Jan 2000 10:23:52 -0500 (EST) (envelope-from gabi@unica.edu)
Received: from alpha.fert.es (alpha.unica.edu [172.20.1.1])
by w3.unica.edu (8.9.3/8.9.3) with SMTP id PAA17174
for <pgsql-general@postgreSQL.org>; Fri, 21 Jan 2000 15:45:55 GMT
Received: from unica.edu ([172.20.2.2]) by alpha.fert.es (4.1/1.11) Fri,
21 Jan 00 16:23:30 GMT
Message-Id: <388879E9.9148DA69@unica.edu>
Date: Fri, 21 Jan 2000 16:23:22 +0100
From: Gabriel Fernandez <gabi@unica.edu>
X-Mailer: Mozilla 4.7 [en-gb] (Win98; I)
X-Accept-Language: en
Mime-Version: 1.0
To: PostgreSQL-general <pgsql-general@postgreSQL.org>
Subject: Problems with select ... into ...
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Hi !
I've just had problems trying to use the 'select into' command.
For example (a very simple one):
-I have one small table:
CREATE TABLE idioma (
contador int4 NOT NULL,
nombre varchar(200),
codigo varchar(5) DEFAULT '' NOT NULL,
PRIMARY KEY (contador)
);
- It has 105 rows. And then I try:
select nombre into table aux from idioma \g
- and i obtain the following message:
biblio=> select nombre into table aux from idioma \g
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
We have lost the connection to the backend, so further processing is
impossible. Terminating.
- Things happen exacctly the same way if it try the select into TEMP ...
Does it run Ok in your versions ? Is there any known problem with this ?
Thanks a million.
Gabi :-)
From bouncefilter Fri Jan 21 11:14:46 2000
Received: from datmail03.dat.com ([209.241.199.3])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA65338
for <pgsql-general@postgreSQL.org>;
Fri, 21 Jan 2000 11:14:27 -0500 (EST)
(envelope-from philip.culberson@dat.com)
Received: by datmail03.dat.com with Internet Mail Service (5.5.2448.0)
id <XF5B52ZP>; Fri, 21 Jan 2000 08:13:45 -0800
Message-ID: <A95EFC3B707BD311986C00A0C9E95B6A9DE33F@datmail03.dat.com>
From: "Culberson, Philip" <philip.culberson@dat.com>
To: "'Thomas Mack'" <mack@ips.cs.tu-bs.de>, pgsql-general@postgreSQL.org
Subject: RE: [GENERAL] Too many open files...
Date: Fri, 21 Jan 2000 08:13:37 -0800
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2448.0)
Content-Type: text/plain;
charset="iso-8859-1"
From your description I would hazard a guess that your servlet is
misbehaving. On Solaris, the default maximum number of open file handles
for a process is 64. Unless if your servlet is opening a BUNCH of files, I
would look for it being in some sort of loop, opening a file, but not
closing it. Do this enough times and you run out of file handles.
-----Original Message-----
From: Thomas Mack [mailto:mack@ips.cs.tu-bs.de]
Sent: Friday, January 21, 2000 5:17 AM
To: pgsql-general@postgreSQL.org
Subject: [GENERAL] Too many open files...
Well, we got a problem with postgres 6.5.2 on SOlaris 2.6, when:
1) Executing a query with a trigger behind it, making quite a big amount
of
queries on different relations,
2) and when this query is executed from an applet via a servlet on
Apache 1.3.3
using postgresql.jar as db driver.
So, the message is:
ERROR: Load of file /usr/dblocal/pgsql/lib/plpgsql.so failed: ld.so.1:
/usr/dblocal/pgsql/bin/postmaster: fatal: /dev/zero: open failed: Too
many open files
(or similar).
The problem does not exist, if we execute it via psql.
So I tracked it down to dlopen(filename,1). But really, I do not know,
what might
be the real reason for the failure: is it postgres (probably not),
Apache, restricting
the environment "somehow", or postgresql.jar (it should just give the
query to the
backend, so probably not as well), or Solaris itself (but why do I not
get an error,
if I execute it via psql?) or where should I check further?
I am a little confused on how to repair the problem, as it is most
probably not
a problem with postgres. Anybody had similar experiences or even a
solution (besides
not using the trigger)?
Thanks,
Thomas Mack
TU Braunschweig, Abt. Informationssysteme
************
From bouncefilter Fri Jan 21 13:01:46 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 NAA99268
for <pgsql-general@postgresql.org>;
Fri, 21 Jan 2000 13:01:38 -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 OAA67802
for <pgsql-general@postgresql.org>;
Fri, 21 Jan 2000 14:01:41 -0400 (AST) (envelope-from scrappy@hub.org)
X-Authentication-Warning: thelab.hub.org: scrappy owned process doing -bs
Date: Fri, 21 Jan 2000 14:01:41 -0400 (AST)
From: The Hermit Hacker <scrappy@hub.org>
To: pgsql-general@postgresql.org
Subject: BEGIN/END question ...
Message-ID: <Pine.BSF.4.21.0001211358420.23487-100000@thelab.hub.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
In v6.5.3, if I do:
BEGIN;
SELECT * FROM <table> WHERE <datefield> < now();
DELETE FROM <table> WHERE <datafield> < now();
END;
and, between the SELECT/DELETE, someone inserts data into the table that
matches the WHERE clause, will the DELETE pick that up also?
I know its an odd question, but if I have any faint understanding of MVCC,
it won't, because when I issue the BEGIN/SELECT, it will "timestamp" my
transaction and only affect those tuples that were in existence when I
started ...
... but I want to confirm :)
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 21 13:53:47 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 NAA13205
for <pgsql-general@postgresql.org>;
Fri, 21 Jan 2000 13:53:24 -0500 (EST)
(envelope-from e.bras@hccnet.nl)
Received: from pietjepuik by smtp.hccnet.nl
via uds211-117.dial.hccnet.nl [193.173.117.211] with SMTP
id TAA13971 (8.8.5/1.11); Fri, 21 Jan 2000 19:53:36 +0100 (MET)
From: "Ed Bras" <e.bras@hccnet.nl>
To: <mack@ips.cs.tu-bs.de>,
"Postgres General newsgroep (E-mail)" <pgsql-general@postgresql.org>
Subject: RE: stat failed on /usr/lib/pgsql/plpgsql.so; no file or directory
Date: Fri, 21 Jan 2000 19:47:07 +0100
Message-ID: <001601bf6440$6e0d8e90$1c969696@pietjepuik>
MIME-Version: 1.0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook CWS, Build 9.0.2416 (9.0.2910.0)
Importance: Normal
In-Reply-To: <38885DE5.F5EF44A5@infbssys.ips.cs.tu-bs.de>
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
I can do a ls /usr/lib/pgsql/plpgsql.so with no problem.
By the way:
1: I am using Red hat 6.0 with kernel 2.2.52.
2: What shloud be the rights and the ownership of the plpgsql file and those
of the directories where it in ? (I haven't changed any of these after the
installation).
Please let me know if anyone has any ideas ?
Regards,
Ed Bras
-----Original Message-----
From: mack@ips.cs.tu-bs.de [mailto:mack@ips.cs.tu-bs.de]
Sent: vrijdag 21 januari 2000 14:24
To: e.bras@hccnet.nl
Subject: Re: stat failed on /usr/lib/pgsql/plpgsql.so; no file or
directory
I am using psql for about the first time and created some tables and
triggers with their functions written in PL/pgSQL... No errors so far.
I loaded the plpgsql.so (which I wrote down at het bottom of the mail)
with neither an error.
However when I ecxute a very simple short function which has the language
set to plpgsql it gives me the following error: "stat failed on
/usr/lib/pgsql/plpgsql.so; no file or directory"
Try: ls /usr/lib/pgsql/plpgsql.so as user postgres.
If it doesn't show you the file, the problem should be clear.
Otherwise? Don't know for the moment...
Thomas Mack
TU Braunschweig, Abt. Informationssysteme
From bouncefilter Fri Jan 21 14:07:48 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 OAA19417
for <pgsql-hackers@postgresql.org>;
Fri, 21 Jan 2000 14:07:06 -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 12BjOp-000500-00; Fri, 21 Jan 2000 19:07:11 +0000
Received: from prlw1 by quartz.newn.cam.ac.uk with local (Exim 2.12 #1)
id 12BjOd-0004hu-00; Fri, 21 Jan 2000 19:06:59 +0000
Date: Fri, 21 Jan 2000 19:06:59 +0000
From: Patrick Welche <prlw1@newn.cam.ac.uk>
To: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: Peter Eisentraut <peter_e@gmx.net>, prlw1@cam.ac.uk,
PostgreSQL Development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Re: [GENERAL] Variable case database names
Message-ID: <20000121190659.D18047@quartz.newn.cam.ac.uk>
References: <Pine.LNX.4.21.0001202242260.349-100000@localhost.localdomain>
<200001202204.RAA20758@candle.pha.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Mutt/1.1.1i
In-Reply-To: <200001202204.RAA20758@candle.pha.pa.us>;
from pgman@candle.pha.pa.us on Thu, Jan 20, 2000 at 05:04:45PM
-0500
On Thu, Jan 20, 2000 at 05:04:45PM -0500, Bruce Momjian wrote:
[Charset ISO-8859-1 unsupported, filtering to ASCII...]
There is some code in libpq which converts all database names to
lower-case, unless it's double quoted. That seems a little ill-conceived
to me, since you'd actually have to pass it something like
PGconnectdb("dbname=\"Newnham\"");If anything, this would make it inconvenient it psql, because you'd have
to write
\c '"Newnham"'
since
\c "Newnham"
is interpreted differently.Does anyone have an explanation for this? Why not leave the name as is?
We do the same thing with queries, right? We force identifiers to lower
case unless quoted.
The point was: the database name was quoted. I didn't think to quote it
a second time. (single quoting for the create was sufficient, and the export
from access didn't mind about the case)
Cheers,
Patrick
From bouncefilter Fri Jan 21 16:10:48 2000
Received: from smtp.zebra.net (mail@smtp.zebra.net [208.5.19.14])
by hub.org (8.9.3/8.9.3) with ESMTP id QAA57388
for <pgsql-general@postgresql.org>;
Fri, 21 Jan 2000 16:10:32 -0500 (EST)
(envelope-from brianlmoon@iname.com)
Received: from ppp073-pool1a.bham.zebra.net ([209.12.6.136] helo=moon1)
by smtp.zebra.net with smtp (Custom zebra.net smtp server (Debian))
for pgsql-general@postgresql.org
id 12BlQ6-0001P4-00; Fri, 21 Jan 2000 15:16:38 -0600
Message-ID: <000501bf6454$0a6e6260$020000c5@moon1>
From: "Brian Moon" <brianlmoon@iname.com>
To: <pgsql-general@postgresql.org>
Subject: Not enough connections on Solaris
Date: Fri, 21 Jan 2000 15:11:08 -0600
MIME-Version: 1.0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Hi,
I have just got PG up and going on our Solaris box and had to start
postmaster with -B 32 -N 16 to get it to work. I now understand that the -N
is the number of PG processes that are available. I keep getting a "Too
many clients" error. The problem is that I will be connecting to PG from
PHP and need a possible 200 concurrent connections. Is there anything I can
do here?
Please reply to me. I am not on the list. (Don't you hate it when people
put that :)
Brian.
------------------------------
http://brian.threadnet.com
From bouncefilter Fri Jan 21 16:36:51 2000
Received: from linux01.gremmelspacher.de (root@[145.253.109.214])
by hub.org (8.9.3/8.9.3) with ESMTP id QAA65290
for <pgsql-general@postgresql.org>;
Fri, 21 Jan 2000 16:36:06 -0500 (EST)
(envelope-from gremmelspacher@compuserve.com)
Received: from compuserve.com (roland@localhost [127.0.0.1])
by linux01.gremmelspacher.de (8.9.3/8.9.3) with ESMTP id WAA00523
for <pgsql-general@postgresql.org>; Fri, 21 Jan 2000 22:35:32 +0100
Sender: roland@linux01.gremmelspacher.de
Message-ID: <3888D122.A8FDCE7D@compuserve.com>
Date: Fri, 21 Jan 2000 22:35:30 +0100
From: Roland Gremmelspacher <gremmelspacher@compuserve.com>
X-Mailer: Mozilla 4.61 [en] (X11; I; Linux 2.2.10 i586)
X-Accept-Language: de, en
MIME-Version: 1.0
To: pgsql-general@postgresql.org
Subject: jdbc connect in StarOffice
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
X-MIME-Autoconverted: from quoted-printable to 8bit by hub.org id QAB65391
Hello,
does anybody now, how it is posible not only to connect to a
postgresql-database via the jdbc-option in star-office (it works) but
how to create and display tables for example.
i think the matter is wether in the configured "code_page" or that
postgres doesnt support ANSI-SQL.
when i try to create a table (interactive with StarOffice - not in the
sql-window from starOffice) i�ll get a error message like:
parser: parser error at or near "("
the message is postet at stdout from postmaster-process and as
staroffice error-msg too.
thanx
roland
From bouncefilter Fri Jan 21 23:11:53 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 XAA71351
for <pgsql-general@postgresql.org>;
Fri, 21 Jan 2000 23:11:30 -0500 (EST)
(envelope-from admin@wtbwts.com)
Received: by pcr.ca (Postfix, from userid 1000)
id 8A60F1FEE; Fri, 21 Jan 2000 23:10:46 +0000 (GMT)
Received: from localhost (localhost [127.0.0.1])
by pcr.ca (Postfix) with ESMTP id 67B171FEC
for <pgsql-general@postgresql.org>;
Fri, 21 Jan 2000 23:10:46 +0000 (GMT)
Date: Fri, 21 Jan 2000 23:10:46 +0000 (GMT)
From: Marc Tardif <admin@wtbwts.com>
X-Sender: admin@server.b0x.com
To: pgsql-general@postgresql.org
Subject: max(oid)
Message-ID: <Pine.BSF.4.10.10001212308020.19731-100000@server.b0x.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Is there a way to use the max aggregate on an oid field? When I try on
6.5.3, I get the following error message:
test=> select max(uid) from user_base;
ERROR: Unable to select an aggregate function max(oid)
If there's any work-around, please let me know.
Marc
From bouncefilter Sat Jan 22 00:15:54 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 AAA85873
for <pgsql-general@postgresql.org>;
Sat, 22 Jan 2000 00:15:12 -0500 (EST)
(envelope-from admin@wtbwts.com)
Received: by pcr.ca (Postfix, from userid 1000)
id 0A2861FEE; Sat, 22 Jan 2000 00:14:31 +0000 (GMT)
Received: from localhost (localhost [127.0.0.1])
by pcr.ca (Postfix) with ESMTP id DE9EA1FEC
for <pgsql-general@postgresql.org>;
Sat, 22 Jan 2000 00:14:26 +0000 (GMT)
Date: Sat, 22 Jan 2000 00:14:26 +0000 (GMT)
From: Marc Tardif <admin@wtbwts.com>
X-Sender: admin@server.b0x.com
To: pgsql-general@postgresql.org
Subject: implementing a psql daemon
Message-ID: <Pine.BSF.4.10.10001212356001.19785-100000@server.b0x.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
I have come to like postgresql and I have written many utilities for
internal usage, mostly related to my mailing list manager. The actual
mailinst list program is written in c and opens and closes only a single
connection for each email received. The problem is mostly with the
utilities which are shell scripts using psql between 1 and 5 times per
script. Needless to say, I can expect to waste many resources if/when
traffic becomes more important.
The solution to this problem could be to write a daemon which would leave
a connection open to a postgresql database. Then, when data is needed, I
could pass the query using shared memory and perhaps semaphores to avoid
concurrent access to the daemon therefore avoiding the overhead of
creating a connection each time. I could then use this same method for my
mailing list manager and each utility (which would have to be re-written
in c) all of which would use the same connection provided by the daemon.
Unfortunately, my understanding of the subject is somewhat limited. I can
manage to write the daemon using books and sample source code, but I'm not
in a position to weigh the pros and cons of such a solution. Therefore, I
would like to sollicit your feedback to gain a better understanding of the
problem at hand.
Looking forward to your insight,
Marc
From bouncefilter Fri Jan 21 19:53:51 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 TAA21749
for <pgsql-general@postgresql.org>;
Fri, 21 Jan 2000 19:53:34 -0500 (EST)
(envelope-from news@news.tht.net)
Received: (from news@localhost) by news.tht.net (8.9.3/8.9.3) id TAA51128
for pgsql-general@postgresql.org; Fri, 21 Jan 2000 19:32:12 -0500 (EST)
(envelope-from news)
X-Authentication-Warning: news.tht.net: news set sender to <news> using -f
Sender: ckempf@singularity.enigami.com
X-Newsgroups: comp.databases.postgresql.questions
Subject: Re: trouble with the postmaster
References: <38881322.7D5C6F39@imap3.asu.edu>
Mime-Version: 1.0 (generated by tm-edit 7.108)
Content-Type: text/plain; charset=US-ASCII
From: Cory Kempf <ckempf@enigami.com>
Message-ID: <5fsnzruet0.fsf@singularity.enigami.com>
Lines: 20
X-Newsreader: Gnus v5.6.45/XEmacs 21.1 - "Big Bend"
X-Trace: tw11.nn.bcandid.com 948501131 216.204.32.42 (Fri,
21 Jan 2000 17:32:11 MST)
Organization: bCandid - Powering the world's discussions - http://bCandid.com
Date: Sat, 22 Jan 2000 00:32:11 GMT
To: pgsql-questions@postgresql.org
Benjamin Cochran <bdc33@imap3.asu.edu> writes:
I'm having trouble starting the postmaster on ports other then the
default port with the -i option. It starts fine and everything runs well
And if I type:
postmaster -p 1234
but if I try:
postmaster -i -p 42
Only root can assign sockets below 1k. Postmaster doesn't
run as root.
+C
--
Have you signed up to be a bone marrow doner? All it takes is a simple
blood test, and it can save a life. <http://www.marrow.org>
Cory Kempf Macintosh / Unix Consulting & Software Development
ckempf@enigami.com <http://www.enigami.com/~ckempf/>
From bouncefilter Fri Jan 21 23:29:53 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 XAA73377
for <pgsql-general@postgreSQL.org>;
Fri, 21 Jan 2000 23:29:24 -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
XAA28444;
Fri, 21 Jan 2000 23:29:10 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200001220429.XAA28444@candle.pha.pa.us>
Subject: Re: [GENERAL] max(oid)
In-Reply-To: <Pine.BSF.4.10.10001212308020.19731-100000@server.b0x.com> from
Marc Tardif at "Jan 21, 2000 11:10:46 pm"
To: Marc Tardif <admin@wtbwts.com>
Date: Fri, 21 Jan 2000 23:29:09 -0500 (EST)
CC: 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
Is there a way to use the max aggregate on an oid field? When I try on
6.5.3, I get the following error message:test=> select max(uid) from user_base;
ERROR: Unable to select an aggregate function max(oid)If there's any work-around, please let me know.
Marc
Added to TODO:
* allow aggregates on oid
--
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 Sat Jan 22 03:02:56 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 DAA29159
for <pgsql-general@postgreSQL.org>;
Sat, 22 Jan 2000 03:02:34 -0500 (EST)
(envelope-from oprabhu@phil.com.sg)
Received: from email.philnet ([202.54.17.55])
by goa1.dot.net.in (8.9.2/8.9.2) with ESMTP id NAA05111
for <pgsql-general@postgreSQL.org>;
Sat, 22 Jan 2000 13:34:07 +0530 (GMT)
Received: from localhost (oprabhu@localhost)
by email.philnet (8.9.3/8.8.7) with ESMTP id NAA07854
for <pgsql-general@postgreSQL.org>; Sat, 22 Jan 2000 13:33:23 +0530
Date: Sat, 22 Jan 2000 13:33:23 +0530 (IST)
From: Omkar Prabhu <oprabhu@phil.com.sg>
To: pgsql-general@postgreSQL.org
Message-ID: <Pine.LNX.4.20.0001221327040.4654-100000@email.philnet>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
I am having problem with CLUSTER command.
Suppose a CLUSTER is created on index,if a record from that table is
deleted,a Cluster command i fire on same index again then backend (version
6.5.1) exit abnormally,if I drop Index ,an recreate the same ,then Cluster
command works succesfully.
Is this a Bug?
Wait for your reply
--
OMKAR B. PRABHU
PHIL SYSTEMS LTD.
Contact me at:
oprabhu@phil.com.sg
omprab@yahoo.com
From bouncefilter Sat Jan 22 03:29:56 2000
Received: from sapphire.albourne.com (root@sapphire.albourne.com
[195.212.241.227]) by hub.org (8.9.3/8.9.3) with ESMTP id DAA32681
for <pgsql-general@postgreSQL.org>;
Sat, 22 Jan 2000 03:29:01 -0500 (EST)
(envelope-from a.joubert@albourne.com)
Received: from albourne.com (akamas.albourne.com [195.212.241.254])
by sapphire.albourne.com (8.9.3/8.9.3/Albourne/CYS/1.8/MAPS) with ESMTP
id KAA18871; Sat, 22 Jan 2000 10:28:53 +0200 (EET)
Sender: a.joubert@albourne.com
Message-ID: <38896A3B.452AA4ED@albourne.com>
Date: Sat, 22 Jan 2000 08:28:43 +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: Marc Tardif <admin@wtbwts.com>
CC: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] implementing a psql daemon
References: <Pine.BSF.4.10.10001212356001.19785-100000@server.b0x.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Marc Tardif wrote:
The solution to this problem could be to write a daemon which would leave
a connection open to a postgresql database. Then, when data is needed, I
could pass the query using shared memory and perhaps semaphores to avoid
concurrent access to the daemon therefore avoiding the overhead of
creating a connection each time. I could then use this same method for my
mailing list manager and each utility (which would have to be re-written
in c) all of which would use the same connection provided by the daemon.
A lot of this type of code has been written before. I found in similar
situations that the easiest way to do this is to use an Apache web server
with mod_perl or fastcgi (mod_perl is a more robust when it comes to database
links, or rather, it requires less work on your part). Using DBI and DBD-Pg
you get your database access quite easily, and as it is a web server it is
trivial to knock up an interface with perl and CGI. You immediately have an
interface to the thing and all the hard work is available for free.
Adriaan
From bouncefilter Sat Jan 22 12:41:06 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 MAA67348
for <pgsql-general@postgresql.org>;
Sat, 22 Jan 2000 12:40:40 -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 MAA21645
for pgsql-general@postgreSQL.org; Sat, 22 Jan 2000 12:34:58 -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 KAA20025
for <maillist@candle.pha.pa.us>; Sat, 22 Jan 2000 10:31:00 -0500 (EST)
Received: from m1smtpsp02.wanadoo.es (m1smtpisp02.wanadoo.es [62.36.220.22])
by renoir.op.net (o1/$Revision: 1.18 $) with ESMTP id KAA23438
for <maillist@candle.pha.pa.us>;
Sat, 22 Jan 2000 10:11:08 -0500 (EST)
Received: from wanadoo.es (usuario3-36-61-115.dialup.uni2.es [62.36.61.115])
by m1smtpsp02.wanadoo.es (8.9.3/8.9.3) with SMTP id QAA28204
for <maillist@candle.pha.pa.us>; Sat, 22 Jan 2000 16:07:16 +0100 (MET)
Message-ID: <000501bf64eb$56f433c0$733d243e@es>
From: "Familia Cerezo" <cerezo@wanadoo.es>
To: <maillist@candle.pha.pa.us>
Subject: libpq library
Date: Sat, 22 Jan 2000 13:16:04 +0100
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="----=_NextPart_000_000F_01BF64DA.D6241400"
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
Sender: pgman@candle.pha.pa.us
This is a multi-part message in MIME format.
------=_NextPart_000_000F_01BF64DA.D6241400
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Estimated sirs:
I am a Computers Engineering student. Currently I am doing a program =
in C which uses the PostgreSQL's library "libpq" and I have a problem:
I can't introduce in the data base a simple quote. For example:
char *sent_sql;=20
char *dato;
strcpy(dato, "grocery's");
sprintf(sent_sql, "insert into table values ('%s')", dato);
However, I don't have problems with the SQL interpreter, eg.:
insert into table values ('grocery\'s');
In this case, the data will be stored as "grocery's" (OK!), but I =
don't find the way I could do that using the library.=20
I just can't introduce the '\' character previous to the simple =
quote because I will loose the final character of the string. I can't =
define the field as type text because I need that the field had a =
defined size (I got to store encrypted data).
If you know a solution, I would thank you.
Sorry for my english and thanks for paying attention to this e-mail,
DAVID.
------=_NextPart_000_000F_01BF64DA.D6241400
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 content=3D"text/html; charset=3Diso-8859-1" =
http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2314.1000" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>
<DIV><FONT face=3DArial size=3D2>Estimated sirs:</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2> I am a Computers =
Engineering=20
student. Currently I am doing a program in C which uses the PostgreSQL's =
library=20
"libpq" and I have a problem:</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2> I can't introduce in =
the data=20
base a simple quote. For example:</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2> <FONT =
face=3DArial=20
size=3D2>char *sent_sql;</FONT>=20
<DIV><FONT face=3DArial size=3D2> char =
*dato;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2> strcpy(dato,=20
"grocery's");</FONT></DIV>
<DIV><FONT face=3DArial size=3D2> sprintf(sent_sql, =
"insert=20
into table values ('%s')", dato);</FONT></DIV>
<DIV> </DIV>
<DIV> However, I don't have problems with the SQL =
interpreter,=20
eg.:</DIV>
<DIV> insert into table values=20
('grocery\'s');</DIV>
<DIV> In this case, the data will be stored as=20
"grocery's" (OK!), but I don't find the way I could do that using =
the=20
library. </DIV>
<DIV> </DIV>
<DIV> I just can't introduce the '\' character =
previous to the=20
simple quote because I will loose the final character of the string. I =
can't=20
define the field as type text because I need that the field had a =
defined=20
size (I got to store encrypted data).</DIV>
<DIV> </DIV>
<DIV> If you know a solution, I would thank you.</DIV>
<DIV> </DIV>
<DIV> Sorry for my english and thanks for paying =
attention to=20
this e-mail,</DIV>
<DIV> </DIV>
<DIV> =
=20
=20
DAVID.</DIV></FONT></DIV></FONT></DIV></BODY></HTML>
------=_NextPart_000_000F_01BF64DA.D6241400--
From bouncefilter Sat Jan 22 09:48:00 2000
Received: from tele-post-20.mail.demon.net (tele-post-20.mail.demon.net
[194.217.242.20]) by hub.org (8.9.3/8.9.3) with ESMTP id JAA12739
for <pgsql-general@postgresql.org>;
Sat, 22 Jan 2000 09:47:16 -0500 (EST)
(envelope-from peter@retep.org.uk)
Received: from maidast.demon.co.uk ([158.152.22.37] helo=maidast.retep.org.uk)
by tele-post-20.mail.demon.net with esmtp (Exim 2.12 #2)
id 12C1oo-000Mby-0K; Sat, 22 Jan 2000 14:47:15 +0000
Received: from localhost (peter@localhost [127.0.0.1])
by maidast.retep.org.uk (8.9.3/8.9.3) with ESMTP id OAA04752;
Sat, 22 Jan 2000 14:44:30 GMT
Date: Sat, 22 Jan 2000 14:44:28 +0000 (GMT)
From: Peter Mount <peter@retep.org.uk>
To: Roland Gremmelspacher <gremmelspacher@compuserve.com>
cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] jdbc connect in StarOffice
In-Reply-To: <3888D122.A8FDCE7D@compuserve.com>
Message-ID: <Pine.LNX.4.10.10001221430490.4161-100000@maidast.retep.org.uk>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=X-UNKNOWN
Content-Transfer-Encoding: 8bit
X-MIME-Autoconverted: from QUOTED-PRINTABLE to 8bit by hub.org id JAA12804
On Fri, 21 Jan 2000, Roland Gremmelspacher wrote:
Hello,
does anybody now, how it is posible not only to connect to a
postgresql-database via the jdbc-option in star-office (it works) but
how to create and display tables for example.
You've got further than I have. Out of interest, what platform are you
running StarOffice under?
i think the matter is wether in the configured "code_page" or that
postgres doesnt support ANSI-SQL.when i try to create a table (interactive with StarOffice - not in the
sql-window from starOffice) i�ll get a error message like:parser: parser error at or near "("
the message is postet at stdout from postmaster-process and as
staroffice error-msg too.
I was going to try to get staroffice working with the next version, mainly
as I use it here. If you can send me how you got it working so far, I'd be
grateful, as it will help me in getting it to work.
Thanks, Peter
--
Peter T Mount peter@retep.org.uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf
From bouncefilter Sat Jan 22 13:39:03 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 NAA85303
for <pgsql-general@postgreSQL.org>;
Sat, 22 Jan 2000 13:38:07 -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 <0FOR003J52FHLS@falla.videotron.net> for
pgsql-general@postgreSQL.org; Sat, 22 Jan 2000 13:38:05 -0500 (EST)
Date: Sat, 22 Jan 2000 13:36:54 -0500
From: Web Manager <web@inter-resa.com>
Subject: Repost: restore from dump problem - query buffer full
Sender: www@falla.videotron.net
To: pgsql-general@postgreSQL.org
Message-id: <3889F8C6.E2A92ABE@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
(nobody could help me a week ago... any new volunteers?)
Hello,
I am not an expert with Postgres (version 6.4.2) but I never had a
problem with using pg_dump on a remote server and rebuilding a local
database before...
I have this new database working well under the same postgres version
(server side).
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 Sat Jan 22 13:40:03 2000
Received: from mail2.svr.pol.co.uk (mail2.svr.pol.co.uk [195.92.193.210])
by hub.org (8.9.3/8.9.3) with ESMTP id NAA85605
for <pgsql-general@postgresql.org>;
Sat, 22 Jan 2000 13:39:53 -0500 (EST)
(envelope-from aaron@nudgeltd.freeserve.co.uk)
Received: from modem-174.longnose-hawk.dialup.pol.co.uk ([62.137.40.174]
helo=drag) by mail2.svr.pol.co.uk with smtp (Exim 3.13 #0)
id 12C5Rw-0007qq-00
for pgsql-general@postgreSQL.org; Sat, 22 Jan 2000 18:39:52 +0000
From: "Aaron Walker" <aaron@nudgeltd.freeserve.co.uk>
To: <pgsql-general@postgresql.org>
Subject:
Date: Sat, 22 Jan 2000 18:43:55 -0000
Message-ID: <NDBBJDNFGLGBBDBHHIEPGECMCAAA.aaron@nudgeltd.freeserve.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)
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200
subscribe
end
From bouncefilter Sat Jan 22 16:50:05 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 QAA36286
for <pgsql-general@postgresql.org>;
Sat, 22 Jan 2000 16:49:48 -0500 (EST)
(envelope-from e.bras@hccnet.nl)
Received: from pietjepuik by smtp.hccnet.nl
via uds212-117.dial.hccnet.nl [193.173.117.212] with SMTP for
<pgsql-general@postgresql.org.>
id WAA16372 (8.8.5/1.11); Sat, 22 Jan 2000 22:49:59 +0100 (MET)
From: "Ed Bras" <e.bras@hccnet.nl>
To: <pgsql-general@postgresql.org>
Subject: Two "little" things
Date: Sat, 22 Jan 2000 22:47:05 +0100
Message-ID: <000001bf6522$39f4ef30$d475adc1@pietjepuik>
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="----=_NextPart_000_0001_01BF652A.9BBADDD0"
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_0001_01BF652A.9BBADDD0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
The simple thing first:
1: In the perl:pg module the talk about the OID ??. Can someone tell me what
this is ?
2: Ok , so I think I figgeres out what the "stat failed on
/usr/lib/pgsql/plpgsql.so; no file or directory" error was:
I noticed that there was an extra space in the string
"/usr/lib/pgsql/plpgsql.so " at the end, as I removed it but now it comes up
with the following error:
"fmgr_info: function 20224:cache lookup dalied"
Does anyone knows what this error means ?
And in general where can I look up these postgres errors ??
Regards,
Ed Bras
------=_NextPart_000_0001_01BF652A.9BBADDD0
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=3D354554121-22012000>The =
simple thing=20
first:</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D354554121-22012000>1: In =
the perl:pg=20
module the talk about the OID ??. Can someone tell me what this is=20
?</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D354554121-22012000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D354554121-22012000>2: Ok =
, so I think I=20
figgeres out what the "stat failed on /usr/lib/pgsql/plpgsql.so; no file =
or=20
directory" error was:</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D354554121-22012000>I =
noticed that there=20
was an extra space in the string "/usr/lib/pgsql/plpgsql.so " at the =
end, as I=20
removed it but now it comes up with the following =
error:</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN =
class=3D354554121-22012000>"fmgr_info: function=20
20224:cache lookup dalied"</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D354554121-22012000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D354554121-22012000>Does =
anyone knows=20
what this error means ?</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D354554121-22012000>And in =
general where=20
can I look up these postgres errors ??</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D354554121-22012000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D354554121-22012000>Regards,</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D354554121-22012000>Ed=20
Bras</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D354554121-22012000></SPAN></FONT> </DIV></BODY></HTML>
------=_NextPart_000_0001_01BF652A.9BBADDD0--
From bouncefilter Sat Jan 22 18:24:06 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 SAA61735
for <pgsql-general@postgresql.org>;
Sat, 22 Jan 2000 18:23:51 -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, 22 Jan 2000 17:14:24 -0600
Sender: ed
Message-ID: <388A3C66.DE53CD3E@austin.rr.com>
Date: Sat, 22 Jan 2000 17:25:26 -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: Ed Bras <e.bras@hccnet.nl>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Two "little" things
References: <000001bf6522$39f4ef30$d475adc1@pietjepuik>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Ed Bras wrote:
"fmgr_info: function 20224:cache lookup dalied"
Does anyone knows what this error means ?
http://www.deja.com/getdoc.xp?AN=574815108
And in general where can I look up these postgres errors ??
http://www.deja.com/home_ps.shtml
Cheers,
Ed Loehr
From bouncefilter Sat Jan 22 20:23:08 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 UAA36102
for <pgsql-general@postgresql.org>;
Sat, 22 Jan 2000 20:22:15 -0500 (EST)
(envelope-from peter@localhost.its.uu.se)
Received: from regulus.its.uu.se ([130.238.7.19]:64887 "EHLO
regulus.its.uu.se")
by merganser.its.uu.se with ESMTP id <S391203AbQAWBVp>;
Sun, 23 Jan 2000 02:21:45 +0100
Received: from peter (helo=localhost)
by regulus.its.uu.se with local-esmtp (Exim 3.02 #2)
id 12CBpr-0003vN-00; Sun, 23 Jan 2000 02:28:59 +0100
Date: Sun, 23 Jan 2000 02:28:59 +0100 (CET)
From: Peter Eisentraut <peter_e@gmx.net>
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: Marc Tardif <admin@wtbwts.com>, pgsql-general@postgresql.org
Subject: Re: [GENERAL] max(oid)
In-Reply-To: <200001220429.XAA28444@candle.pha.pa.us>
Message-ID: <Pine.LNX.4.21.0001221730360.3007-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-21, Bruce Momjian mentioned:
Is there a way to use the max aggregate on an oid field? When I try on
6.5.3, I get the following error message:test=> select max(uid) from user_base;
ERROR: Unable to select an aggregate function max(oid)If there's any work-around, please let me know.
MarcAdded to TODO:
* allow aggregates on oid
We already had a TODO item for this and came to the conclusion that
* Make type equivalency apply to aggregates
will solve this.
For right now the user could do the following:
INSERT INTO pg_aggregate VALUES ('max', <your user id>, 'int4larger', '-',
'-', 26, 26, 0, 26, NULL, NULL);
--
Peter Eisentraut Sernanders v�g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
From bouncefilter Sat Jan 22 20:24:08 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 UAA36221
for <pgsql-hackers@postgresql.org>;
Sat, 22 Jan 2000 20:23:14 -0500 (EST)
(envelope-from peter@localhost.its.uu.se)
Received: from regulus.its.uu.se ([130.238.7.19]:61605 "EHLO
regulus.its.uu.se")
by merganser.its.uu.se with ESMTP id <S393221AbQAWBWm>;
Sun, 23 Jan 2000 02:22:42 +0100
Received: from peter (helo=localhost)
by regulus.its.uu.se with local-esmtp (Exim 3.02 #2)
id 12CBqx-0003ws-00; Sun, 23 Jan 2000 02:30:07 +0100
Date: Sun, 23 Jan 2000 02:30:07 +0100 (CET)
From: Peter Eisentraut <peter_e@gmx.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
cc: prlw1@cam.ac.uk, PostgreSQL Development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Re: [GENERAL] Variable case database names
In-Reply-To: <25186.948408703@sss.pgh.pa.us>
Message-ID: <Pine.LNX.4.21.0001230145500.3007-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-20, Tom Lane mentioned:
Peter Eisentraut <peter_e@gmx.net> writes:
There is some code in libpq which converts all database names to
lower-case, unless it's double quoted. That seems a little ill-conceived
to me,I think you are probably right. The backend might try to lowercase the
name when it gets it, but it seems like libpq shouldn't be doing so
(any more than it's responsible for downcasing identifiers used in
SQL commands).If the backend *does* lowercase the DB name used in a connect command,
is there any way to use a mixed-case DB name? I'm not sure there is...
The backend doesn't lower case it. I removed that part in libpq and now
it works fine.
--
Peter Eisentraut Sernanders v�g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
From bouncefilter Sat Jan 22 22:19:09 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 WAA64564
for <pgsql-general@postgreSQL.org>;
Sat, 22 Jan 2000 22:19:04 -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
WAA02830;
Sat, 22 Jan 2000 22:18:13 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200001230318.WAA02830@candle.pha.pa.us>
Subject: Re: [GENERAL] max(oid)
In-Reply-To: <Pine.LNX.4.21.0001221730360.3007-100000@localhost.localdomain>
from Peter Eisentraut at "Jan 23, 2000 02:28:59 am"
To: Peter Eisentraut <peter_e@gmx.net>
Date: Sat, 22 Jan 2000 22:18:13 -0500 (EST)
CC: Marc Tardif <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
Got it. TODO updated.
[Charset ISO-8859-1 unsupported, filtering to ASCII...]
On 2000-01-21, Bruce Momjian mentioned:
Is there a way to use the max aggregate on an oid field? When I try on
6.5.3, I get the following error message:test=> select max(uid) from user_base;
ERROR: Unable to select an aggregate function max(oid)If there's any work-around, please let me know.
MarcAdded to TODO:
* allow aggregates on oid
We already had a TODO item for this and came to the conclusion that
* Make type equivalency apply to aggregates
will solve this.For right now the user could do the following:
INSERT INTO pg_aggregate VALUES ('max', <your user id>, 'int4larger', '-',
'-', 26, 26, 0, 26, NULL, NULL);--
Peter Eisentraut Sernanders v_g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
--
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 Sat Jan 22 22:54:09 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 WAA72461;
Sat, 22 Jan 2000 22:53:38 -0500 (EST)
(envelope-from news@news.tht.net)
Received: (from news@localhost) by news.tht.net (8.9.3/8.9.3) id WAA66781;
Sat, 22 Jan 2000 22:33:29 -0500 (EST) (envelope-from news)
X-Authentication-Warning: news.tht.net: news set sender to <news> using -f
Message-ID: <388A771B.FC374194@namsi.com>
From: D Herssein <denny@namsi.com>
Reply-To: denny@namsi.com
X-Mailer: Mozilla 4.7 [en] (Win95; U)
X-Accept-Language: en
MIME-Version: 1.0
X-Newsgroups: comp.databases.postgresql.questions,
comp.databases.postgresql.hackers
Subject: [HELP] - Installing Postgres
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 31
Date: Sat, 22 Jan 2000 22:35:55 -0500
X-Trace: news3.mia 948598397 208.61.25.12 (Sat, 22 Jan 2000 22:33:17 EST)
To: pgsql-hackers@postgresql.org.pgsql-questions@postgresql.org
Installed the binaries with pkgadd -d <...>
When I try to run I get:
$ bin/pg_version
ld.so.1: bin/pg_version: fatal: libgen.so.1: open failed: No such file
or directory
Killed
$ uname -a
SunOS namsb 5.6 Generic_105181-16 sun4u sparc SUNW,Ultra-4
$ env
HOME=/usr/local/pgsql
HZ=100
LOGNAME=pgsql
PATH=/usr/bin::/usr/local/bin:/usr/opt/SUNWmd/sbin
SHELL=/bin/sh
TERM=vt100
TZ=US/Eastern
What am I missing?
--
Privileged/Confidential Information may be contained in this message.
If you are not the addressee indicated in this message
(or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the
sender by reply email.
Please advise immediately if you or your employer does not consent to
email
for messages of this kind. Opinions, conclusions and other information
in this message
that do not relate to the official business of New Age Media Systems
shall be understood
as neither given nor endorsed by it.
From bouncefilter Sun Jan 23 02:46:13 2000
Received: from web3001.mail.yahoo.com (web3001.mail.yahoo.com
[204.71.202.164])
by hub.org (8.9.3/8.9.3) with SMTP id CAA35290
for <pgsql-general@postgresql.org>;
Sun, 23 Jan 2000 02:46:12 -0500 (EST)
(envelope-from jeff95350@yahoo.com)
Received: (qmail 10882 invoked by uid 60001); 23 Jan 2000 07:45:38 -0000
Message-ID: <20000123074538.10881.qmail@web3001.mail.yahoo.com>
Received: from [209.95.162.57] by web3001.mail.yahoo.com;
Sat, 22 Jan 2000 23:45:38 PST
Date: Sat, 22 Jan 2000 23:45:38 -0800 (PST)
From: Jeff Davis <jeff95350@yahoo.com>
Subject: Non atomic data types and quantity pricing
To: pgsql-general@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
I am making a database to store product information. I
am going to need to use quantity pricing breaks
(different prices for buying a lot).
I was first thinking that I should use a seperate
table and, for each product have several rows, which
is how many people do that. But that method seems
somewhat awkward, so I thought an array type would be
a better solution.
However, when I thought about it some more and read
more documentation, I found that the select statements
I would need would be nearly impossible. For example:
I want to get the price for quantity X so i need to
select the greatest value in the array that is less
than X as the quantity break value that applies to
them.
Could someone help me find a solution involving
arrays?
Thanks,
Jeff Davis
__________________________________________________
Do You Yahoo!?
Talk to your friends online with Yahoo! Messenger.
http://im.yahoo.com
From bouncefilter Sun Jan 23 05:49:14 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 FAA74667
for <pgsql-general@postgreSQL.org>;
Sun, 23 Jan 2000 05:48:46 -0500 (EST)
(envelope-from ctassell@isn.net)
Received: from niki (luke14.isn.net [216.129.42.115])
by kiln.isn.net (8.9.3/8.9.3) with ESMTP id GAA09306;
Sun, 23 Jan 2000 06:48:03 -0400
Message-Id: <4.2.0.58.20000123063930.00aae1e0@mailer.isn.net>
X-Sender: ctassell@mailer.isn.net
X-Mailer: QUALCOMM Windows Eudora Pro Version 4.2.0.58
Date: Sun, 23 Jan 2000 06:50:58 -0400
To: Jeff Davis <jeff95350@yahoo.com>, pgsql-general@postgreSQL.org
From: Charles Tassell <ctassell@isn.net>
Subject: Re: [GENERAL] Non atomic data types and quantity pricing
In-Reply-To: <20000123074538.10881.qmail@web3001.mail.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed
I've never used arrays in Postgres (or any other RDBMS for that matter),
but it would seem that the best way to do it would be to create a function
that would take the quantity as an argument, and loop through all the
elements of the array and return the value that most closely matches
the specified quantity. So your query would look something like this:
select product_id, DiscountPrice(quantity), FROM ....
But, here is where you run into a small problem: Arrays are not hashes,
they have elements 1,2,3,4,5... So you have to use a multi-dimensioned
array, which is going to be even more complicated. I'd recommend the
multiple entries in a separate pricing table, it seems much simpler (yeah,
I know, it's not as cool... :-)
At 03:45 AM 1/23/00, Jeff Davis wrote:
I am making a database to store product information. I
am going to need to use quantity pricing breaks
(different prices for buying a lot).I was first thinking that I should use a seperate
table and, for each product have several rows, which
is how many people do that. But that method seems
somewhat awkward, so I thought an array type would be
a better solution.However, when I thought about it some more and read
more documentation, I found that the select statements
I would need would be nearly impossible. For example:I want to get the price for quantity X so i need to
select the greatest value in the array that is less
than X as the quantity break value that applies to
them.Could someone help me find a solution involving
arrays?Thanks,
Jeff Davis
__________________________________________________
Do You Yahoo!?
Talk to your friends online with Yahoo! Messenger.
http://im.yahoo.com************
From bouncefilter Sun Jan 23 08:37:16 2000
Received: from qube.dsdeurne.nl (1Cust75.tnt6.rtm1.nl.uu.net [212.153.214.75])
by hub.org (8.9.3/8.9.3) with ESMTP id IAA10934
for <pgsql-general@postgreSQL.org>;
Sun, 23 Jan 2000 08:37:07 -0500 (EST)
(envelope-from w.aarts@dsdeurne.nl)
Received: from dsd21 ([10.254.211.2])
by qube.dsdeurne.nl (8.9.2/8.9.2) with SMTP id OAA31354
for <pgsql-general@postgreSQL.org>;
Sun, 23 Jan 2000 14:33:28 +0100 (CET)
Message-ID: <001a01bf65a6$61813f20$02d3fe0a@dsd21.dsdeurne.nl>
From: "DSDeurne Automatisering, Wim Aarts" <w.aarts@dsdeurne.nl>
To: <pgsql-general@postgreSQL.org>
Subject: PAM authentication
Date: Sun, 23 Jan 2000 14:33:06 +0100
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="----=_NextPart_000_0017_01BF65AE.C3134C80"
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
This is a multi-part message in MIME format.
------=_NextPart_000_0017_01BF65AE.C3134C80
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi all,
I've a situation where there are Win NT domain servers (PDC). We're =
planning to run a postgreSQL database on a linux platform in this =
environment. The database will be accessed by the users via Apache and =
mod_php3.
What we would like to achieve:
Users have to log on once with the same loginname/password as they use =
in the Win NT environment to gain acces to there database functionality.
User password changes are done once on the Win NT environment.
We plan on using the PAM module pam_ntdom but are not sure if it will =
work.=20
Is there someone who tried this before?
Thanks Wim.
DSD automatisering B.V.
Wim Aarts
Dr. Hub van Doorneweg 14
Postbus 293
5750 AG Deurne
t. 0493 351320
f. 0493 351319
mail: mail@dsdeurne.nl
http://www.dsdeurne.nl
------=_NextPart_000_0017_01BF65AE.C3134C80
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 content=3D"text/html; charset=3Dwindows-1252" =
http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2314.1000" name=3DGENERATOR></HEAD>
<BODY bgColor=3D#fff8e0>
<DIV><FONT size=3D2>Hi all,</FONT></DIV>
<DIV> </DIV>
<DIV><FONT size=3D2>I've a situation where there are Win NT domain =
servers=20
(PDC). We're planning to run a postgreSQL database on a linux =
platform in=20
this environment. The database will be accessed by the users via=20
Apache and mod_php3.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT size=3D2>What we would like to achieve:</FONT></DIV>
<DIV><FONT size=3D2>Users have to log on once with the same=20
loginname/password as they use in the Win NT environment to gain acces =
to there=20
database functionality.</FONT></DIV>
<DIV><FONT size=3D2>User password changes are done once on the Win NT=20
environment.</FONT></DIV>
<DIV><FONT size=3D2>We plan on using the PAM module pam_ntdom but are =
not sure if=20
it will work. </FONT></DIV>
<DIV><FONT size=3D2></FONT> </DIV>
<DIV><FONT size=3D2>Is there someone who tried this before?</FONT></DIV>
<DIV> </DIV>
<DIV><FONT size=3D2>Thanks Wim.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT size=3D2>DSD automatisering B.V.<BR>Wim Aarts<BR>Dr. Hub van =
Doorneweg=20
14<BR>Postbus 293<BR>5750 AG Deurne<BR>t. 0493 351320<BR>f. 0493 =
351319<BR>mail:=20
<A href=3D"mailto:mail@dsdeurne.nl">mail@dsdeurne.nl</A><BR><A=20
href=3D"http://www.dsdeurne.nl">http://www.dsdeurne.nl</A></FONT></DIV></=
BODY></HTML>
------=_NextPart_000_0017_01BF65AE.C3134C80--
From bouncefilter Sun Jan 23 08:42:16 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 IAA11319;
Sun, 23 Jan 2000 08:41:45 -0500 (EST) (envelope-from gav@nlr.ru)
Received: from 212.193.3.33 ([212.193.3.33])
by double.nlr.ru (8.9.3/8.9.3) with ESMTP id QAA21338;
Sun, 23 Jan 2000 16:32:35 +0300
Date: Sun, 23 Jan 2000 16:44:12 +0300
From: gav <gav@nlr.ru>
X-Mailer: The Bat! (v1.34a) UNREG / CD5BF9353B3B7091
Reply-To: gav <gav@nlr.ru>
X-Priority: 3 (Normal)
Message-ID: <13697.000123@nlr.ru>
To: pgsql-general@postgresql.org, pgsql-sql@postgresql.org
Subject: pqReadData...
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Hi,
I have the database created with the postgresql 6.5.3. I make many
inserts (10-30 thousands) in one of tables in one transaction.
Sometimes I receive the error:
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request
What is it? Is this a bug? Or I do something incorrect? Has anybody
received this message?
Best regards,
Alex
From bouncefilter Sun Jan 23 13:51:19 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 NAA78320
for <pgsql-general@postgresql.org>;
Sun, 23 Jan 2000 13:50:24 -0500 (EST)
(envelope-from admin@wtbwts.com)
Received: by pcr.ca (Postfix, from userid 1000)
id 6A81D1FEC; Sun, 23 Jan 2000 13:49:48 +0000 (GMT)
Received: from localhost (localhost [127.0.0.1])
by pcr.ca (Postfix) with ESMTP id 469271F91
for <pgsql-general@postgresql.org>;
Sun, 23 Jan 2000 13:49:48 +0000 (GMT)
Date: Sun, 23 Jan 2000 13:49:48 +0000 (GMT)
From: Marc Tardif <admin@wtbwts.com>
X-Sender: admin@server.b0x.com
To: pgsql-general@postgresql.org
Subject: estimating size of db
Message-ID: <Pine.BSF.4.10.10001231346320.30519-100000@server.b0x.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Is there any way to estimate the size of a db for x rows, considering each
row uses y bytes (no varchar) and uses index z? Basically, I'm looking for
the order of growth for tables and indices in general, simply to get a
idea of the storage requirements for a particular db.
Marc
From bouncefilter Sun Jan 23 11:19:18 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 LAA44978;
Sun, 23 Jan 2000 11:18:39 -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 LAA06055;
Sun, 23 Jan 2000 11:18:13 -0500 (EST)
To: gav <gav@nlr.ru>
cc: pgsql-general@postgreSQL.org, pgsql-sql@postgreSQL.org
Subject: Re: [SQL] pqReadData...
In-reply-to: <13697.000123@nlr.ru>
References: <13697.000123@nlr.ru>
Comments: In-reply-to gav <gav@nlr.ru>
message dated "Sun, 23 Jan 2000 16:44:12 +0300"
Date: Sun, 23 Jan 2000 11:18:13 -0500
Message-ID: <6052.948644293@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
gav <gav@nlr.ru> writes:
I have the database created with the postgresql 6.5.3. I make many
inserts (10-30 thousands) in one of tables in one transaction.
Sometimes I receive the error:
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request
What is it? Is this a bug?
It's a bug, but we'll need a lot more info than that to fix it.
For example: what were the last few commands you issued before
the crash? Is there anything in the postmaster log file?
(6.5.* libpq has a bad habit of dropping backend error messages
if the channel is closed immediately after the message arrives.
So if the backend reports a FATAL error and quits, the above is
all you see. But the FATAL message ought to be logged in the
postmaster log file.) Is there a core dump file from the failed
backend? (It'd be in .../data/base/yourdatabasename/core) If
so, can you use gdb to get a backtrace from the core file?
regards, tom lane
From bouncefilter Sun Jan 23 14:46:20 2000
Received: from candle.pha.pa.us (pgman@s5-03.ppp.op.net [209.152.195.67] (may
be forged)) by hub.org (8.9.3/8.9.3) with ESMTP id OAA04854
for <pgsql-general@postgreSQL.org>;
Sun, 23 Jan 2000 14:45:34 -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
OAA00431;
Sun, 23 Jan 2000 14:45:10 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200001231945.OAA00431@candle.pha.pa.us>
Subject: Re: [GENERAL] estimating size of db
In-Reply-To: <Pine.BSF.4.10.10001231346320.30519-100000@server.b0x.com> from
Marc Tardif at "Jan 23, 2000 01:49:48 pm"
To: Marc Tardif <admin@wtbwts.com>
Date: Sun, 23 Jan 2000 14:45:09 -0500 (EST)
CC: 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
See FAQ.
Is there any way to estimate the size of a db for x rows, considering each
row uses y bytes (no varchar) and uses index z? Basically, I'm looking for
the order of growth for tables and indices in general, simply to get a
idea of the storage requirements for a particular db.Marc
************
--
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 Sun Jan 23 17:53:22 2000
Received: from web3003.mail.yahoo.com (web3003.mail.yahoo.com
[204.71.202.166])
by hub.org (8.9.3/8.9.3) with SMTP id RAA73589
for <pgsql-general@postgresql.org>;
Sun, 23 Jan 2000 17:52:44 -0500 (EST)
(envelope-from jeff95350@yahoo.com)
Received: (qmail 21253 invoked by uid 60001); 23 Jan 2000 22:52:13 -0000
Message-ID: <20000123225213.21252.qmail@web3003.mail.yahoo.com>
Received: from [209.95.161.53] by web3003.mail.yahoo.com;
Sun, 23 Jan 2000 14:52:13 PST
Date: Sun, 23 Jan 2000 14:52:13 -0800 (PST)
From: Jeff Davis <jeff95350@yahoo.com>
Subject: Writing C functions
To: pgsql-general@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
This is a very quick question regarding C functions:
After you have written a functions.c file, how do you
compile it how postgres wants it with gcc?
The tutorial just said that here is an example, you
compile it as a .so file, then import it with these
sql commands. It didn't say how to make it a .so file
(it assumed we would know I guess).
Thanks (and please excuse my ignorance),
Jeff Davis
__________________________________________________
Do You Yahoo!?
Talk to your friends online with Yahoo! Messenger.
http://im.yahoo.com
From bouncefilter Sun Jan 23 18:36:23 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 SAA84504
for <pgsql-general@postgresql.org>;
Sun, 23 Jan 2000 18:36:22 -0500 (EST)
(envelope-from e.bras@hccnet.nl)
Received: from pietjepuik by smtp.hccnet.nl
via uds231-117.dial.hccnet.nl [193.173.117.231] with SMTP
id AAA03384 (8.8.5/1.11); Mon, 24 Jan 2000 00:36:35 +0100 (MET)
From: "Ed Bras" <e.bras@hccnet.nl>
To: <mack@ips.cs.tu-bs.de>,
"Postgres General newsgroep (E-mail)" <pgsql-general@postgresql.org>
Subject: RE: Where is the Database log file ?
Date: Mon, 24 Jan 2000 00:32:21 +0100
Message-ID: <003001bf65fa$4742c210$e875adc1@pietjepuik>
MIME-Version: 1.0
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook CWS, Build 9.0.2416 (9.0.2910.0)
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
In-reply-to: <38885F51.55A62905@infbssys.ips.cs.tu-bs.de>
Importance: Normal
In th documentation thay talk about a database log file which is used in
for example RAISE NOTICE.
Where can I find this (the log file) ? and how can I see this kind of
information in the system tables ?
Postmaster writes its messages to stdout (stderr?).
So we start it in /etc/init.d/postgres with:
su - postgres -c "source /usr/dblocal/pgsql/.login;
/usr/dblocal/pgsql/bin/postmaster -i -o -e >>&
/usr/dblocal/pgsql/server.log&"
and the output will be in /usr/dblocal/pgsql/server.log.
Howwww, I am sorry but you are going a bit too fast I think.
I don't really understand which part is responsible for the logging ? Please
give a bit more info ?
The only thing I have in /etc/rc.d/init.d/postgres is (the starting of the
postmaster:
su -l postgres -c '/usr/bin/postmaster -i -S -D/var/lib/pgsql'
and nothing about some logging commands (neither does server.log excits) !!
Regards,
Ed Bras
From bouncefilter Sun Jan 23 18:38:23 2000
Received: from mail.piter-press.ru (mail.piter-press.ru [195.239.142.35])
by hub.org (8.9.3/8.9.3) with ESMTP id SAA84585
for <pgsql-general@postgresql.org>;
Sun, 23 Jan 2000 18:37:23 -0500 (EST)
(envelope-from alexey@byte-unix.piter-press.ru)
Received: from byte-unix.piter-press.ru ([195.239.142.37]) by
mail.piter-press.ru with SMTP (Microsoft Exchange Internet Mail
Service Version 5.5.2650.21)
id CG8MZ0SZ; Mon, 24 Jan 2000 02:33:15 +0300
Received: from alexey by byte-unix.piter-press.ru with local (Exim 3.12 #1
(Debian)) id 12CWYE-0004Un-00; Mon, 24 Jan 2000 02:36:10 +0300
Date: Mon, 24 Jan 2000 02:36:10 +0300
From: Alexey Vyskubov <alexey@byte-unix.piter-press.ru>
To: Jeff Davis <jeff95350@yahoo.com>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Writing C functions
Message-ID: <20000124023610.A17280@byte-unix.piter-press.ru>
References: <20000123225213.21252.qmail@web3003.mail.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Mutt/1.0i
In-Reply-To: <20000123225213.21252.qmail@web3003.mail.yahoo.com>;
from jeff95350@yahoo.com on Sun, Jan 23, 2000 at 02:52:13PM -0800
Organization: BYTE/Russia magazine
After you have written a functions.c file, how do you
compile it how postgres wants it with gcc?
Well. It depends on your OS.
Under OpenBSD works
gcc -fpic -c -o file.o file.c
ld -Bshareable -o file.so file.c
info gcc should help you.
--
Alexey
From bouncefilter Sun Jan 23 19:42:24 2000
Received: from web3004.mail.yahoo.com (web3004.mail.yahoo.com
[204.71.202.167])
by hub.org (8.9.3/8.9.3) with SMTP id TAA98400
for <pgsql-general@postgresql.org>;
Sun, 23 Jan 2000 19:41:38 -0500 (EST)
(envelope-from jeff95350@yahoo.com)
Received: (qmail 20175 invoked by uid 60001); 24 Jan 2000 00:41:07 -0000
Message-ID: <20000124004107.20174.qmail@web3004.mail.yahoo.com>
Received: from [209.95.161.53] by web3004.mail.yahoo.com;
Sun, 23 Jan 2000 16:41:07 PST
Date: Sun, 23 Jan 2000 16:41:07 -0800 (PST)
From: Jeff Davis <jeff95350@yahoo.com>
Subject: More C function questions
To: pgsql-general@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Thanks for the advice so far, but I ran into another
problem:
I made what I thought was a fine object file that I
wanted to import as a function with:
CREATE FUNCTION addtwo(int4) returns int4 as
'/var/lib/pgsql/functions/tmp.so' language 'c';
Everything seemed to be fine until I go to use the
function, called addtwo(int4):
select addtwo(7) as number;
ERROR: Load of file /var/lib/pgsql/functions/tmp.so
failed: /var/lib/pgsql/functions/tmp.so: ELF file's
phentsize not the expected size
here is my C code (tmp.c):
#include <pgsql/postgres.h>
int4 addtwo(int4);
int4 addtwo(int4 arg)
{
return(arg+2);
}
and I used:
gcc -c tmp.c -o tmp.so
to compile.
To make sure it was working outside of PG I did a
seperate C file which called the function and that
worked by compiling the test C file like:
gcc test.c tmp.so
and the function call defined in tmp.so worked fine
Thanks again,
Jeff Davis
__________________________________________________
Do You Yahoo!?
Talk to your friends online with Yahoo! Messenger.
http://im.yahoo.com
From bouncefilter Sun Jan 23 20:10:24 2000
Received: from web3002.mail.yahoo.com (web3002.mail.yahoo.com
[204.71.202.165])
by hub.org (8.9.3/8.9.3) with SMTP id UAA06776
for <pgsql-general@postgresql.org>;
Sun, 23 Jan 2000 20:10:10 -0500 (EST)
(envelope-from jeff95350@yahoo.com)
Received: (qmail 10990 invoked by uid 60001); 24 Jan 2000 01:09:39 -0000
Message-ID: <20000124010939.10989.qmail@web3002.mail.yahoo.com>
Received: from [209.95.161.53] by web3002.mail.yahoo.com;
Sun, 23 Jan 2000 17:09:39 PST
Date: Sun, 23 Jan 2000 17:09:39 -0800 (PST)
From: Jeff Davis <jeff95350@yahoo.com>
Subject: It works now :)
To: pgsql-general@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Thanks very much to the people that helped me write
those C functions.
-Jeff Davis
__________________________________________________
Do You Yahoo!?
Talk to your friends online with Yahoo! Messenger.
http://im.yahoo.com
From bouncefilter Mon Jan 24 01:16:28 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 BAA92833
for <pgsql-general@postgresql.org>;
Mon, 24 Jan 2000 01:16:11 -0500 (EST)
(envelope-from admin@wtbwts.com)
Received: by pcr.ca (Postfix, from userid 1000)
id 5EA3F1FEC; Mon, 24 Jan 2000 01:15:32 +0000 (GMT)
Received: from localhost (localhost [127.0.0.1])
by pcr.ca (Postfix) with ESMTP id 41CFF1F91
for <pgsql-general@postgresql.org>;
Mon, 24 Jan 2000 01:15:32 +0000 (GMT)
Date: Mon, 24 Jan 2000 01:15:32 +0000 (GMT)
From: Marc Tardif <admin@wtbwts.com>
X-Sender: admin@server.b0x.com
To: pgsql-general@postgresql.org
Subject: another questoin about COPY
Message-ID: <Pine.BSF.4.10.10001240107001.31619-100000@server.b0x.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Are rules and triggers on insert still executed when using COPY? I did a
search in chapters 8 and 13 of the programmer's guide, but I couldn't find
any mention of copy being recognised by either rules or triggers. I guess
it is assumed copy isn't the same as insert, but I would like to
double-check with the mailing list.
Thanks,
Marc
From bouncefilter Sun Jan 23 21:35:25 2000
Received: from web3006.mail.yahoo.com (web3006.mail.yahoo.com
[204.71.202.169])
by hub.org (8.9.3/8.9.3) with SMTP id VAA29582
for <pgsql-general@postgresql.org>;
Sun, 23 Jan 2000 21:34:47 -0500 (EST)
(envelope-from jeff95350@yahoo.com)
Received: (qmail 22319 invoked by uid 60001); 24 Jan 2000 02:34:16 -0000
Message-ID: <20000124023416.22318.qmail@web3006.mail.yahoo.com>
Received: from [209.95.161.53] by web3006.mail.yahoo.com;
Sun, 23 Jan 2000 18:34:16 PST
Date: Sun, 23 Jan 2000 18:34:16 -0800 (PST)
From: Jeff Davis <jeff95350@yahoo.com>
Subject: looping arrays with a function
To: pgsql-general@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
How can you pass an array to a function, or loop it in
any way? All arrays, no matter how many dimensions,
are listed as _vartype.
I am trying to make a C function (or any language,
really) that would take a number as one argument, the
array as the other, and look at the array to find an
index number for the array argument based on the first
argument.
Thanks,
Jeff Davis
__________________________________________________
Do You Yahoo!?
Talk to your friends online with Yahoo! Messenger.
http://im.yahoo.com
From bouncefilter Sun Jan 23 20:39:24 2000
Received: from arka.poznan.mtl.pl (root@arka.poznan.mtl.pl [195.116.164.132])
by hub.org (8.9.3/8.9.3) with ESMTP id UAA15251
for <pgsql-general@postgreSQL.org>;
Sun, 23 Jan 2000 20:38:26 -0500 (EST)
(envelope-from m.mazurek@multinet.pl)
Received: from localhost (mazek@localhost)
by arka.poznan.mtl.pl (8.9.3/8.9.3) with ESMTP id CAA54164;
Mon, 24 Jan 2000 02:37:39 GMT
X-Authentication-Warning: arka.poznan.mtl.pl: mazek owned process doing -bs
Date: Mon, 24 Jan 2000 02:37:39 +0000 (GMT)
From: Marcin Mazurek - Multinet SA - Poznan <m.mazurek@multinet.pl>
X-Sender: mazek@arka.poznan.mtl.pl
To: Jeff Davis <jeff95350@yahoo.com>
cc: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] It works now :)
In-Reply-To: <20000124010939.10989.qmail@web3002.mail.yahoo.com>
Message-ID: <Pine.BSF.4.21.0001240236390.39734-100000@arka.poznan.mtl.pl>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
On Sun, 23 Jan 2000, Jeff Davis wrote:
Thanks very much to the people that helped me write
those C functions.
Can You send a solution to the list. It may help somebody in the
future. It would be good to get to the archiveres at least:).
mazek
From bouncefilter Sun Jan 23 22:00:25 2000
Received: from web3005.mail.yahoo.com (web3005.mail.yahoo.com
[204.71.202.168])
by hub.org (8.9.3/8.9.3) with SMTP id WAA32744
for <pgsql-general@postgresql.org>;
Sun, 23 Jan 2000 22:00:17 -0500 (EST)
(envelope-from jeff95350@yahoo.com)
Received: (qmail 19335 invoked by uid 60001); 24 Jan 2000 02:59:46 -0000
Message-ID: <20000124025946.19334.qmail@web3005.mail.yahoo.com>
Received: from [209.95.161.53] by web3005.mail.yahoo.com;
Sun, 23 Jan 2000 18:59:46 PST
Date: Sun, 23 Jan 2000 18:59:46 -0800 (PST)
From: Jeff Davis <jeff95350@yahoo.com>
Subject: Solution
To: pgsql-general@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Of course, here is the solution I used (to create a C
function to link to with PG):
'$' means done at command prompt
'=>' means done at psql prompt
this was done in Linux (RedHat 6.1) with PG 6.5 and
gcc
1) I made the C source file, file.c, which included
postgres.h
2) compiled as object:
$ gcc -fpic -c -o file.o file.c
$ ld -Bshareable -o file.so file.o
3) added function with SQL:
=> create function function_name(int4,float) returns
float AS '/path/to/file.so' language 'c';
4) test function with SQL:
=> select function_name(7,8.3) as number;
Hope this helps someone,
Jeff Davis
PS: If I get a good array function written (which I am
working on now) I will contribute that as well.
__________________________________________________
Do You Yahoo!?
Talk to your friends online with Yahoo! Messenger.
http://im.yahoo.com
From bouncefilter Sun Jan 23 22:20:25 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 WAA39056
for <pgsql-general@postgreSQL.org>;
Sun, 23 Jan 2000 22:20:17 -0500 (EST)
(envelope-from lamar.owen@wgcr.org)
Received: from wgcr.org (dial-1.r13.ncbrvr.infoave.net [207.144.84.131] (may
be forged))
by www.wgcr.org (8.9.3/8.9.3/WGCR) with ESMTP id WAA27363;
Sun, 23 Jan 2000 22:20:15 -0500
Message-ID: <388BC4E3.5B31D44A@wgcr.org>
Date: Sun, 23 Jan 2000 22:20:03 -0500
From: Lamar Owen <lamar.owen@wgcr.org>
Organization: WGCR Internet Radio
X-Mailer: Mozilla 4.61 [en] (Win95; I)
X-Accept-Language: en
MIME-Version: 1.0
To: Ed Bras <e.bras@hccnet.nl>
CC: mack@ips.cs.tu-bs.de,
"Postgres General newsgroep (E-mail)" <pgsql-general@postgreSQL.org>
Subject: Re: [GENERAL] RE: Where is the Database log file ?
References: <003001bf65fa$4742c210$e875adc1@pietjepuik>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Ed Bras wrote:
The only thing I have in /etc/rc.d/init.d/postgres is (the starting of the
postmaster:su -l postgres -c '/usr/bin/postmaster -i -S -D/var/lib/pgsql'
and nothing about some logging commands (neither does server.log excits) !!
RedHat RPM, by chance?
Drop the '-S' and redirect appropriately. This will be fixed in a
future RPM release, with log rotating etc.
And a note to anyone posting about problems with PostgreSQL -- if it is
an RPM issue, cc: me on it and specify that the RPM distribution is at
fault. I'll try to get to it as soon as I can.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11