Undeliverable Message - Mailing list pgsql-sql

To:            ISMTP@SFRA0046@Servers[<pgsql-sql-digest@hub.org>]
Cc:            
Subject:       pgsql-sql-digest V1 #239

Message not delivered to recipients below.  Press F1 for help with VNM
error codes.               
VNM3043:  DE_VOLDER Fabrice@ATR_EXPL_LYON1@SFR_DO_CNTR_EST



VNM3043 -- MAILBOX IS FULL
  The message cannot be delivered because the  recipient's mailbox contains the maximum number of   messages, as set by
thesystem administrator.  The  recipient must delete some messages before any  other messages can be delivered.   The
maximummessage limit for a user's mailbox is   10,000.  The default message limit is 1000 messages.    Administrators
canset message limits using the   Mailbox  Settings function available in the   Manage User menu  (MUSER). 
 
  When a user's mailbox reaches the limit, the   user must delete some of the messages before   the mailbox can accept
anymore incoming messages.
 

----------------------  Original Message Follows  ----------------------


pgsql-sql-digest         Monday, May 24 1999         Volume 01 : Number 239



Index:

Undeliverable Message
[none]
RE: [SQL] Problems with refint.so and 6.5 Beta
Re: [SQL] Re: pgsql-sql-digest V1 #225
Update of two tables in a trigger
Fatal process interaction
Re: [SQL] Re: pgsql-sql-digest V1 #225
index on int8 in PG 6.4.2
Re: [SQL] Re: pgsql-sql-digest V1 #225 
Re: [SQL] Fatal process interaction 
Re: [SQL] index on int8 in PG 6.4.2 

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

Date: Mon, 24 May 99 5:35:26 +0100
From: <MAILER-DAEMON@mail1.sfr.fr>
Subject: Undeliverable Message

To:            ISMTP@SFRA0046@Servers[<pgsql-sql-digest@hub.org>]
Cc:            
Subject:       pgsql-sql-digest V1 #238

Message not delivered to recipients below.  Press F1 for help with VNM
error codes.               
VNM3043:  DE_VOLDER Fabrice@ATR_EXPL_LYON1@SFR_DO_CNTR_EST



VNM3043 -- MAILBOX IS FULL
  The message cannot be delivered because the  recipient's mailbox contains the maximum number of   messages, as set by
thesystem administrator.  The  recipient must delete some messages before any  other messages can be delivered.   The
maximummessage limit for a user's mailbox is   10,000.  The default message limit is 1000 messages.    Administrators
canset message limits using the   Mailbox  Settings function available in the   Manage User menu  (MUSER). 
 
  When a user's mailbox reaches the limit, the   user must delete some of the messages before   the mailbox can accept
anymore incoming messages.
 

- ----------------------  Original Message Follows  ----------------------


pgsql-sql-digest         Sunday, May 23 1999         Volume 01 : Number 238



Index:

Re: [SQL] Problems with refint.so and 6.5 Beta 

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

Date: Sun, 23 May 1999 21:58:34 +0200
From: "Michael Scheuner" <scheuner@timeworld.com>
Subject: Re: [SQL] Problems with refint.so and 6.5 Beta 

Hi !



>"Michael Scheuner" <scheuner@timeworld.com> writes:
>> ERROR: check_primary_key: even number of arguments shoud be specified
>
>That was a known bug at the time the last beta release was made.
>It's fixed.  Grab current sources instead --- either the nightly
>snapshot tarfile, or get 'em directly from the CVS server.
>
> regards, tom lane
>

Thankyou, i installed an actual snapshot and now it works fine!

Michael Scheuner

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

End of pgsql-sql-digest V1 #238
*******************************

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

Date: Mon, 24 May 1999 13:42:31 -0400 (EDT)
From: Subject: [none]
 I want to fill an array in my database with the content of a file. I
know the number of value I have to add (written in the begining of the
file), but I'd like not to load all the file in memory. So I can't put the
value within the INSERT command. Is it possible to insert an array with a
fixed size and to fill at after?
 I have tried things like :
CREATE TABLE timeseries (seriesid int4 PRIMARY KEY,         samples float8 []) ;
INSERT INTO timeseries (seriesid, samples[1000]) VALUES (1, '{}') ;
UPDATE timeseries SET samples[1]^RE seriesid  -> ERROR : array_set: array bound exceeded
 Any help?
 Guillaume.

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

Date: Mon, 24 May 1999 13:53:17 -0500
From: "Jackson, DeJuan" <djackson@cpsgroup.com>
Subject: RE: [SQL] Problems with refint.so and 6.5 Beta

This has been fixed in the CVS.-DEJ

> -----Original Message-----
> Hi,
> 
> i just installed the 6.5 Beta (on a fresh Suse 6.0 System) and it works
> fine, but i don't get refint.so (from the contrib/spi-Diretory of
> 6.5-tar-gz) working with it. Running the refint.example, Postgres shows
> me:
> 
> ERROR: check_primary_key: even number of arguments shoud be specified
> 
> fort all the inserts : "INSERT INTO B VALUES (1)" thru the last "INSERT
> INTO
> C VALUES (30)"
> 
> Can somebody help me ?
> 
> Michael Scheuner
> 
> 

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

Date: Mon, 24 May 1999 14:04:58 -0500
From: "Steven M. Wheeler" <swheeler@sabre.com>
Subject: Re: [SQL] Re: pgsql-sql-digest V1 #225

Tom,

I have a little more information.

Version of Postgres: 6.4.2

Regarding your request for a backtrace, I recompiled with debugging and
profiling options on.  Subsequently I have attached the debugger and
interrupted the backend a number of times.  I keep coming up in mcount() and
a couple of hash functions.  BTW: I had let the query run for over 50+ hours
without it returning a value.  The offending SQL: select count(*) from
currnt;  Is there something more definitive you would like me to do?

Using the syntax you suggested, "statdate >works like a champ.  So does using "between" as suggested by another user.
However, issuing "select count(*) form currnt where statdate still returns an error telling me that I must use a cast
sincethere is more
 
than one possible function.

A funny note.  Issuing the SQL: select count(*) from currnt where statdate
>00:07:30:81, nice and fast) and returns the count 2956630.  The laugh is
that this date range covers everything in the database, the same as the
above offending syntax should.

Thanks for the assist!
- --
Steven M. Wheeler
UNIX Engineering
The SABRE Group
(918) 292-4119
(918) 292-4165 FAX

Tom Lane wrote:

> "Steven M. Wheeler" <swheeler@sabre.com> writes:
> > First my configuration:
>
> Er, what version of Postgres?
>
> > Using psql in interactive mode, I issue "select count(*) from
> > currnt;".  The postmaster starts to read data in, as seen in vmstat by
> > the bi stat jumping sharply.  Very rapidly, CPU goes to 0% idle,
> > postmaster is using 99.4%, bi stat runs about 12K for 60-80 seconds,
> > and then goes to 0, and everything stays there.  I never get a return
> > and the system stays maxed.  When the row count in this table was
> > below 2M, I would get a return count.
>
> This is odd, all right.  Can you attach to the backend with a debugger
> and see where it is (get a backtrace)?  It sounds like the backend's
> going into an infinite loop, but I've never seen that sort of behavior
> on such a simple query...
>
> > Running psql in interactive mode, I issue "select * from currnt where
> > cast(statdate as text) like '03-%-1999);".  This runs for quite
> > awhile, eats into swap to the tune of 670MB+ and then bombs out with a
> > palloc error.
>
> This is a known problem that I hope to see fixed in 6.6, but it will
> not be fixed for 6.5.   An expression involving any datatype more
> complex than integers consumes temporary memory for each evaluation,
> and currently the backend doesn't try to recover that memory until
> end of statement.  So if you process enough tuples in one statement,
> you run out of memory :-(.  We know how to fix this but it's too large
> a change to make at this late stage of the 6.5 release cycle.
>
> It sounds like this expression is consuming several hundred bytes per
> iteration, which is more than I would've expected --- a couple dozen
> bytes for the temporary text value should've been enough.  It could be
> that the "like" operator itself is wasting memory internally, which
> might be fixable now; I'll look into it.
>
> In the meantime, you might be able to work around the problem by using
> a less memory-hungry form of the WHERE expression --- for example, the
> above query could probably be written without any text temporary as
>         statdate >> This way only requires boolean intermediate values, which don't require
> extra memory to be allocated.  (Haven't actually tried it, but I think
> it should work.)
>
> > Am I trying to run too large a DB?
>
> No; there are people running Postgres DBs with individual tables larger
> than 2Gb without trouble.  But it does depend on working around some of
> the known limitations :-(.  The developers plan to address these
> limitations in future releases, but there are only so many hours in the
> day...
>
>                         regards, tom lane

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

Date: Mon, 24 May 1999 20:59:56 +0200 (MEST)
From: Christian Ullrich <ChrUllrich@gmx.de>
Subject: Update of two tables in a trigger

Hello world,

I am currently playing with postgres to learn a bit about it. I
created a database to contain information about manpages i printed.
(I am going to print a lot of them, and I don't want to do it
twice).

For some reason, I split the output in two tables, one to contain
the command and the section number, one for the number of pages and
sheets of paper, and the date I printed it.

I connected these two tables by creating a sequence, using the
nextval() as default value for a field in the first table and the
currval() as default in the second.

I also created a view (named v_manpages) that joins these two tables
together, based on the mentioned fields.

After failing to get it to work with rules, I am now trying to use
triggers and PL/pgSQL-functions for updating the view, but I am
experiencing rather strange things. The inserting of rows works
fine, but deleting...

Here's my problem:

manpagecmd       |section|pages|sheets|      when
- ----------+-------+-----+------+----------
procmailsc|5      |    5|     3|1999-05-24
sox       |1      |   11|     6|1999-05-24
chgrp     |1      |    1|     1|1999-05-24
procmailrc|5      |   14|     7|1999-05-24  

manpageINSERT 20036 1   
manpageINSERT 20039 1

manpagecmd       |section|pages|sheets|      when
- ----------+-------+-----+------+----------
procmailsc|5      |    5|     3|1999-05-24
sox       |1      |   11|     6|1999-05-24
chgrp     |1      |    1|     1|1999-05-24
procmailrc|5      |   14|     7|1999-05-24      
update    |sql    |    1|     1|1999-05-24
test      |1      |    2|     1|1999-05-24     

manpageDELETE 1
manpagecmd       |section|pages|sheets|      when
- ----------+-------+-----+------+----------
procmailsc|5      |    5|     3|1999-05-24
sox       |1      |   11|     6|1999-05-24
chgrp     |1      |    1|     1|1999-05-24
procmailrc|5      |   14|     7|1999-05-24     

Two are gone, sox is still present.

manpageDELETE 0    

Now I can't delete anything more.

These are my tables, triggers and functions:

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

create table manpages (cmd text, section varchar(5), prnr int4
unique default nextval('seq_printnr'));

create table info (prnr int4 unique default currval('seq_printnr'),
pages int2, sheets int2, when date default current_date);

create view v_manpages as select cmd,section,pages,sheets,when from
manpages,info where manpages.prnr 
create function get_prnrfromcmd(text) returns int4 as '
select prnr from manpages where cmd ' language 'sql';

create function tp_v_manpages () returns opaque as '
declareprintnr int4;
beginif TG_OP         printnr         if printnr ISNULL then        return NULL;    end if;        delete from manpages
whereprnr         delete from info where prnr         return OLD;end if;if TG_OP         insert into manpages values
(NEW.cmd,   NEW.section);
 
    printnr :        if printnr ISNULL then        return NULL;    end if;    insert into info values (printnr,
NEW.pages,   NEW.sheets);    return NEW;end if;return NULL;
 
end;
' language 'plpgsql';

create trigger t_v_manpages
before insert or delete on v_manpages for each row
execute procedure tp_v_manpages();

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

PostgreSQL version is 6.4.2.

What mistake did I make?

(If this mail is too long, or if there is information missing,
or if I am a complete fool, I'm very sorry. But please help me.)

- -- 
Christian Ullrich

(I am a student, I live in Germany)

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

Date: Mon, 24 May 1999 14:41:23 -0500
From: "Steven M. Wheeler" <swheeler@sabre.com>
Subject: Fatal process interaction

The situation:
In xterm #1: psql running, connected to database pssdb, executing an SQL select
statement.

In xterm #2: A shell script is run that pipes another select statement throug psql.

When the select statement in xterm #2 completes, the select statement being executed
in xterm #1 aborts and the following error message is output:

NOTICE:  Message from PostgreSQL backend:       The Postmaster has informed me that some other backend died abnormally
and
possibly corrupted shared memory.       I have rolled back the current transaction and am going to terminate your
database system connection and exit.       Please reconnect to the database system and repeat your query.
pqReadData() -- backend closed the channel unexpectedly.       This probably means the backend terminated abnormally
beforeor while
 
processing the request.
We have lost the connection to the backend, so further processing is impossible.
Terminating.

This problem is very repeatable and does not seem to depend on which UID is running
which session.  They can both be postgres, or me (smw) or one of each.  The one
common factor appears to be that psql is involved in both.  If psql is run in
interactive mode in both windows, then \q is issued in one of the windows, both psql
sessions will terminate.

Any ideas?
- --
Steven M. Wheeler
UNIX Engineering
The SABRE Group
(918) 292-4119
(918) 292-4165 FAX

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

Date: Mon, 24 May 1999 17:25:33 -0400 (EDT)
From: Bruce Momjian <maillist@candle.pha.pa.us>
Subject: Re: [SQL] Re: pgsql-sql-digest V1 #225

> Tom,
> 
> I have a little more information.
> 
> Version of Postgres: 6.4.2
> 
> Regarding your request for a backtrace, I recompiled with debugging and
> profiling options on.  Subsequently I have attached the debugger and
> interrupted the backend a number of times.  I keep coming up in mcount() and
> a couple of hash functions.  BTW: I had let the query run for over 50+ hours
> without it returning a value.  The offending SQL: select count(*) from
> currnt;  Is there something more definitive you would like me to do?
> 
> Using the syntax you suggested, "statdate >> works like a champ.  So does using "between" as suggested by another
user.
> However, issuing "select count(*) form currnt where statdate > still returns an error telling me that I must use a
castsince there is more
 
> than one possible function.

I hate to say this but 6.5 is just about to be released, and has major
hashjoin fixes that may fix this problem.  Can you try the most recent
snapshot on ftp.postgresql.org.

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

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

Date: Tue, 25 May 1999 01:46:29 +0200 (MET DST)
From: Mirek Budzanowski <mirekb@tcs.uni.wroc.pl>
Subject: index on int8 in PG 6.4.2

Hello,

I got a table createtd with command:
create table test (t int8);

When I try:
create index i_test on test(t);
I get error:
ERROR:  Can't find a default operator class for type 20.

How can I define a default operator for int8???
When I am doing select * from test where t > 4, etc.
it works. 

Anyone have any idea how to define a index ont int8 field?

Mirek

- --
Mirek Budzanowski; P.O. box 1739; 51-161 Wroclaw 8; Poland
Tel. +48 602 306674     http://www.tcs.uni.wroc.pl/~mirekb

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

Date: Mon, 24 May 1999 19:59:32 -0400
From: Tom Lane <tgl@sss.pgh.pa.us> 
Subject: Re: [SQL] Re: pgsql-sql-digest V1 #225 

"Steven M. Wheeler" <swheeler@sabre.com> writes:
> Regarding your request for a backtrace, I recompiled with debugging and
> profiling options on.  Subsequently I have attached the debugger and
> interrupted the backend a number of times.  I keep coming up in mcount() and
> a couple of hash functions.  BTW: I had let the query run for over 50+ hours
> without it returning a value.  The offending SQL: select count(*) from
> currnt;  Is there something more definitive you would like me to do?

If you could interrupt the backend a few times and provide a full
backtrace (gdb "bt" command) each time, we could maybe form a picture of
what the heck it's doing.  This report does seem *very* odd, especially
your discovery that adding a "where" clause speeds it up.  (That'd be
fine if the where clause eliminated many rows, but since it doesn't...)

Also, it would be useful to know what "explain" says about how the query
will be executed.  I'd expect an index scan for the "select ... where"
case, and a plain sequential scan for the case without where; if it's
doing something else that would be important to know.

One more thing --- exactly what is the declaration of the currnt table,
and of its indexes if any?

BTW, I concur with Bruce's suggestion to try a recent 6.5 snapshot.
I don't see any hashjoin going on here, but it is true that we've
squashed a remarkable number of bugs between 6.4.* and 6.5.  Perhaps
you are hitting one of them.
        regards, tom lane

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

Date: Mon, 24 May 1999 20:33:56 -0400
From: Tom Lane <tgl@sss.pgh.pa.us> 
Subject: Re: [SQL] Fatal process interaction 

"Steven M. Wheeler" <swheeler@sabre.com> writes:
> If psql is run in interactive mode in both windows, then \q is issued
> in one of the windows, both psql sessions will terminate.

Wow, that's pretty bizarre.  What platform are you on?  I think the FAQ
mentions that problems like this have been seen on systems where SysV
semaphore support wasn't configured or didn't work right ...
        regards, tom lane

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

Date: Mon, 24 May 1999 20:38:05 -0400
From: Tom Lane <tgl@sss.pgh.pa.us> 
Subject: Re: [SQL] index on int8 in PG 6.4.2 

Mirek Budzanowski <mirekb@tcs.uni.wroc.pl> writes:
> Anyone have any idea how to define a index ont int8 field?

Postgres 6.4 doesn't have support for indexes on int8.

In theory you could add it for yourself (there is some documentation
about index operators in the Developer's Guide) but it's not a simple
job.  You would probably be better off to wait for 6.5, which does
have int8 index support...
        regards, tom lane

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

End of pgsql-sql-digest V1 #239
*******************************