Skip site navigation (1) Skip section navigation (2)

Re: tuple maximum size; plpgsql to sendmail?

From: Andrew McMillan <Andrew(at)catalyst(dot)net(dot)nz>
To: ERIC Lawson - x52010 <eric(at)bioeng(dot)washington(dot)edu>
Cc: PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: tuple maximum size; plpgsql to sendmail?
Date: 2000-07-13 19:41:52
Message-ID: 396E1B80.A912DD60@catalyst.net.nz (view raw or flat)
Thread:
Lists: pgsql-novice
ERIC Lawson - x52010 wrote:
> 
> Hi, and TIA,
> 
> Can anybody here answer these questions:
> 
> 1) I've seen references to the maximum size of a tuple as being
> configurable within the range of 8 to 32K, yet the incorporation of blobs
> seems to contradict any maximum size constraint.  Is there a maximum tuple
> size, and if so, what is it?  (To be sure, the server environment limits
> the size of a tuple, attribute and class, but does postgreSQL imposes
> limits itself?)

A bit less than 8k is the 'default' limit (8k less tuple overhead).  You
can use the 'LZTEXT' type to compress large text fields though, as a
first approach.  You can recompile PostgreSQL with the block size set
larger (up to 32k) so the limit increases to a bit less than 32k.

Coming in version 7.1 is a thing called 'TOAST' (it's in current CVS)
which will let you store larger tuples - have a read about it on the
website.


> 2) I'd like to use a plpgsql procedure to send mail when a trigger event
> or condition pertains (e.g., when the interval between the system date and
> a date stored in an attribute reaches 14 days).  Is this possible with
> plpgsql, and how would it be achieved?

A PL/PGSQL procedure won't run by itself.  You would have to set
something up to do a pass through the database looking for such records
periodically.

	SELECT * FROM tbl WHERE tbl.c_time < (now() - timespan('14 days'));

would be a suitable SQL query.  If the c_time field is indexed it would
use an indexed lookup and so should be efficient.

You could run such a query in a shell script like:

IDLIST=`psql -d database -c "SELECT my_id FROM tbl WHERE tbl.c_time <
(now() - timespan('14 days'));"`
for ID in $IDLIST
do
  echo "$ID expired" | mail -s "$ID expired" user(at)email(dot)dom
done


If you're doing much more complex stuff than that I'd go for a
higher-level language for the scripting such as Perl, Python or C.

Hope this is some assistance,
				Andrew.
-- 
_____________________________________________________________________
            Andrew McMillan, e-mail: Andrew(at)cat-it(dot)co(dot)nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267

In response to

pgsql-novice by date

Next:From: Stuart UrbanDate: 2000-07-13 20:01:29
Subject: Re: jdbc driver not recognized
Previous:From: Lilly SarkisDate: 2000-07-13 16:38:39
Subject: RE: pgsql setup

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group