Обсуждение: PL/pgSQL - args passing

Is it possible to pass the NULL value to PL/pgSQL function? More precisely,
I have a function like

CREATE FUNCTION test (text,int4) RETURNS int4 AS '
   Begin
      ...
   End;
' LANGUAGE 'plpgsql';

and typical call is

select test('Word',1);

which works fine. But sometimes I need to call this function with
arguments like

select test(NULL,1);    or
select test('Word',NULL);

Now such calls lead to
ERROR:  typeidTypeRelid: Invalid type - oid = 0

Any ideas?
_________________________________________________________________________
Evgeny Rodichev                          Sternberg Astronomical Institute
System/Net Admin                                  Moscow State University
email: er@sai.msu.su
Phone: 007 (095) 939 2383
Fax:   007 (095) 932 8841                       http://www.sai.msu.su/~er

I am getting

FATAL 1:  Memory exhausted in AllocSetAlloc()

when I vacuum;

Red Hat Linux release 5.2 (Apollo) Kernel 2.0.36 on an i686
postgresql-6.5
256Mb Memory

What do I need to set to get round this?  Is it simply a question of more
memory.

Cheers

Tim Joyce
Chief Enthusiast
tim@hoop.co.uk
HOOP Ltd
http://www.hoop.co.uk
01202 251 816

HOOP is proud to be a member of the Paneris community (www.paneris.co.uk)

sorry, forgot to add:

I have just deleted 1,200,000 records

timj


> I am getting
>
> FATAL 1:  Memory exhausted in AllocSetAlloc()
>
> when I vacuum;
>
> Red Hat Linux release 5.2 (Apollo) Kernel 2.0.36 on an i686
> postgresql-6.5
> 256Mb Memory
>
> What do I need to set to get round this?  Is it simply a question of more
> memory.
>

Tim,

I've been trying to get around the same problem for the last
week but haven't been able to trace it down.  Here's what
I found so far:

1) I have had the "Memory exhausted" in both vacuum and copy.
   It is not obviously reproducible; that is, if I repeat
   the same command it may not fail, or fail in a different
   place

2) The AllocSet...() routines are in src//backend/utils/mmgr/aset.c.
   They are wrappers around malloc.

3) My machine has 256Mb and the postgres user has unlimited memory
   limit.  The backend does not appear to be growing at all when
   the condition occurs.

4) This seems to rear it's head for large databases only.  The
   tables I am trying to vacuum and copy have about 50 million
   records each.  Small databases seem to be fine.

All of this leads me to suspect a memory bug somewhere.

I have set PGBUFFERS to 3500 to maximize use of available shmem.  The
backend does not seem to grow beyond the resulting ~30Mb.  I do not
know if this is related.

I am running 6.5.1 (and have tried 6.5.2) with Linux 2.2.10
under the Debian 2.1 (slink) distribution.  I compiled
Oliver Elphick's 6.5.1 source package against slink which up
to floating point issues, passes the regression tests
successfully.  Perhaps Oliver can tell us if this might have
introduced a problem?

Maybe a pgsql hacker/guru has a clue?  I'm not sufficiently well-
versed with the guts to know what else to do.

--Martin

===========================================================================

Martin Weinberg                      Phone: (413) 545-3821
Dept. of Physics and Astronomy       FAX:   (413) 545-2117/0648
530 Graduate Research Tower         weinberg@astro.umass.edu
University of Massachusetts         http://www.astro.umass.edu/~weinberg/
Amherst, MA  01003-4525

[Charset iso-8859-1 unsupported, filtering to ASCII...]
> sorry, forgot to add:
>
> I have just deleted 1,200,000 records

It will be faster to dump out the table and reload.

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

Hi greeting,
  I am new to Linux.
  Appreciate advise and comment on choosing between PosytgreSQL vs. MySQL.
Both to be run under Linux.  How does these two database compare with MS SQL
7.0?

 There are two options to configure the data base,
a) The data base will have a few million rows, each row is about 100 bytes.
One of the column (field) will be a pathname (filename) to index to an
external files for storing images. Each image belongs to a particular row
and image is 3 to 20 kb (variable size jepg files).

b) same as the about, but the image will be stored as one filed in the data
base. In this case, the data base will be much larger and wonder if it is
size is okay (speed, capacity, etc...)

Many thansk in advance. Appreciate email reply also.

Simon