pl/{perl, pgsql} (was Re: AW: [HACKERS] triggers, views and rules (not instead))

On Sun, 22 February 1998, at 18:26:45, Vadim B. Mikheev wrote:

> ??? Old instance rules system was removed by Jolly & Andrew and so
> it never supported SQL. I hope that Jan will give us PL/pgSQL soon
> and it will be used for triggers, without changing current trigger
> implementation...

Is develemopment being done for PL/pgSQL? What are peoples ideas for
this? I've never used a commercial db before, and the free ones don't
usualle have a stored PL language. What sort of things are you guys
anticipating? In the writing of PL/perl i've been tempted to give
lots of access to the backend internals from perl (why not, it should
have all the facilities C function programmers have!) What do you think?

Also, as far as argument passing goes: strings & numbers get passed as
perl scalars, and most other types get passed as a Posgres::Type
object (with methods for conversion etc). Right now I've got a switch
block on the type oid and I have many case statements and a few bodies
for these conversions.

The conversions are hard-coded in the .c file (via the case
statements). The only reason any particular input type shows up in
perl any particular way (as a scalar (string/int) or Postgres::Type)
is because there's a hardcoded case statement for it. Of course, the
default is a Postgres::Type. Which means new integer types show up as
a Postgres::Type, which could be considered a bad thing. Right now
part of what i'm doing is checking the typbyval and then passing that
type as an integer scalar (excluding selected types that don't make
sense as ints).. Maybe the default case should be the types ouptut
function?

I hope I make sense..

--brett

>
> > 3. the CURRENT keyword in the i/u/d rewrite rules is stupid
> > and should be disabled, destroyed and burned in hell
>
> Agreed, if standard hasn't it. I know that OLD & NEW are in standard,
> for triggers atleast.
>
> > 4. To stick to the mainstream we should enhance the trigger
> > syntax, and forget the rule stuff for i/u/d
>
> Yes. Statement level triggers give the same functionality as rewrite
> i/u/d rules. We could let them to return something special to skip
> user' i/u/d itself, isn't it the same as INSTEAD ?
>
> Vadim
On Sun, 22 February 1998, at 03:33:07, root(at)bigfoot(dot)speakeasy(dot)org wrote:

> X-VM-v5-Data: ([t nil nil nil nil nil nil nil nil]
> ["9840" "Sun" "22" "February" "1998" "03:33:07" "-0800" "root(at)bigfoot(dot)speakeasy(dot)org" "root(at)bigfoot(dot)speakeasy(dot)org" nil "273" "cron: /etc/dailyback " nil nil nil "2" nil nil (number " " mark "N root(at)bigfoot(dot)spea Feb 22 273/9840 " thread-indent "\"cron: /etc/dailyback \"\n") nil nil]
> nil)
> Return-Path: <root(at)bigfoot(dot)speakeasy(dot)org>
> Received: from eve.speakeasy.org (root(at)eve(dot)speakeasy(dot)org [199.238.226.1])
> by abraxas.scene.com (8.8.8/8.8.5) with ESMTP id DAA16378
> for <brett(at)work(dot)chicken(dot)org>; Sun, 22 Feb 1998 03:27:21 -0800
> Received: from bigfoot.speakeasy.org (bigfoot.speakeasy.org [199.238.226.54]) by eve.speakeasy.org (8.8.5/8.7.3) with ESMTP id DAA03076; Sun, 22 Feb 1998 03:27:09 -0800 (PST)
> From: root(at)bigfoot(dot)speakeasy(dot)org
> Received: (from root(at)localhost)
> by bigfoot.speakeasy.org (8.8.7/8.8.7) id DAA23856;
> Sun, 22 Feb 1998 03:33:07 -0800
> Date: Sun, 22 Feb 1998 03:33:07 -0800
> Message-Id: <199802221133(dot)DAA23856(at)bigfoot(dot)speakeasy(dot)org>
> To: root(at)bigfoot(dot)speakeasy(dot)org
> Subject: cron: /etc/dailyback
>
> ARCHIVING: eve
> Begin time: Sun Feb 22 02:14:21 PST 1998
>
> DUMP: Date of this level 7 dump: Sun Feb 22 02:14:21 1998
> DUMP: Date of last level 3 dump: Sat Feb 21 04:26:20 1998
> DUMP: Dumping /dev/rsd3a (/home) to /dev/nrmt0 on host bigfoot
> DUMP: mapping (Pass I) [regular files]
> DUMP: mapping (Pass II) [directories]
> DUMP: mapping (Pass II) [directories]
> DUMP: mapping (Pass II) [directories]
> DUMP: estimated 127906 blocks (62.45MB) on 0.01 tape(s).
> DUMP: dumping (Pass III) [directories]
> DUMP: dumping (Pass IV) [regular files]
> DUMP: level 7 dump on Sun Feb 22 02:14:21 1998
> DUMP: Tape rewinding
> DUMP: 128490 blocks (62.74MB) on 1 volume
> DUMP: DUMP IS DONE
> DUMP: Date of this level 7 dump: Sun Feb 22 02:17:28 1998
> DUMP: Date of last level 3 dump: Sat Feb 21 04:31:51 1998
> DUMP: Dumping /dev/rsd3d (/sp1) to /dev/nrmt0 on host bigfoot
> DUMP: mapping (Pass I) [regular files]
> DUMP: mapping (Pass II) [directories]
> DUMP: mapping (Pass II) [directories]
> DUMP: mapping (Pass II) [directories]
> DUMP: mapping (Pass II) [directories]
> DUMP: estimated 84136 blocks (41.08MB) on 0.00 tape(s).
> DUMP: dumping (Pass III) [directories]
> DUMP: dumping (Pass IV) [regular files]
> DUMP: level 7 dump on Sun Feb 22 02:17:28 1998
> DUMP: Tape rewinding
> DUMP: 84222 blocks (41.12MB) on 1 volume
> DUMP: DUMP IS DONE
> DUMP: Date of this level 7 dump: Sun Feb 22 02:20:38 1998
> DUMP: Date of last level 3 dump: Sat Feb 21 04:38:31 1998
> DUMP: Dumping /dev/rsd2b (/sp2) to /dev/nrmt0 on host bigfoot
> DUMP: mapping (Pass I) [regular files]
> DUMP: mapping (Pass II) [directories]
> DUMP: mapping (Pass II) [directories]
> DUMP: mapping (Pass II) [directories]
> DUMP: mapping (Pass II) [directories]
> DUMP: estimated 109538 blocks (53.49MB) on 0.01 tape(s).
> DUMP: dumping (Pass III) [directories]
> DUMP: dumping (Pass IV) [regular files]
> DUMP: level 7 dump on Sun Feb 22 02:20:38 1998
> DUMP: Tape rewinding
> DUMP: 109562 blocks (53.50MB) on 1 volume
> DUMP: DUMP IS DONE
> DUMP: Date of this level 7 dump: Sun Feb 22 02:25:28 1998
> DUMP: Date of last level 3 dump: Sat Feb 21 04:48:03 1998
> DUMP: Dumping /dev/rsd3e (/sp3) to /dev/nrmt0 on host bigfoot
> DUMP: mapping (Pass I) [regular files]
> DUMP: mapping (Pass II) [directories]
> DUMP: mapping (Pass II) [directories]
> DUMP: mapping (Pass II) [directories]
> DUMP: mapping (Pass II) [directories]
> DUMP: estimated 121746 blocks (59.45MB) on 0.01 tape(s).
> DUMP: dumping (Pass III) [directories]
> DUMP: dumping (Pass IV) [regular files]
> DUMP: level 7 dump on Sun Feb 22 02:25:28 1998
> DUMP: Tape rewinding
> DUMP: 121760 blocks (59.45MB) on 1 volume
> DUMP: DUMP IS DONE
> DUMP: Date of this level 7 dump: Sun Feb 22 02:31:30 1998
> DUMP: Date of last level 3 dump: Sat Feb 21 05:01:18 1998
> DUMP: Dumping /dev/rsd3f (/usr/spool/mail) to /dev/nrmt0 on host bigfoot
> DUMP: mapping (Pass I) [regular files]
> DUMP: mapping (Pass II) [directories]
> DUMP: estimated 709654 blocks (346.51MB) on 0.04 tape(s).
> DUMP: dumping (Pass III) [directories]
> DUMP: dumping (Pass IV) [regular files]
> DUMP: 42.60% done, finished in 0:06
> DUMP: 84.00% done, finished in 0:01
> DUMP: level 7 dump on Sun Feb 22 02:31:30 1998
> DUMP: Tape rewinding
> DUMP: 710144 blocks (346.75MB) on 1 volume
> DUMP: DUMP IS DONE
> DUMP: Date of this level 7 dump: Sun Feb 22 02:45:06 1998
> DUMP: Date of last level 3 dump: Sat Feb 21 05:17:49 1998
> DUMP: Dumping /dev/rsd1a (/usr/local/etc/httpd) to /dev/nrmt0 on host bigfoot
> DUMP: mapping (Pass I) [regular files]
> DUMP: mapping (Pass II) [directories]
> DUMP: mapping (Pass II) [directories]
> DUMP: mapping (Pass II) [directories]
> DUMP: estimated 12236 blocks (5.97MB) on 0.00 tape(s).
> DUMP: dumping (Pass III) [directories]
> DUMP: dumping (Pass IV) [regular files]
> DUMP: level 7 dump on Sun Feb 22 02:45:06 1998
> DUMP: Tape rewinding
> DUMP: 12454 blocks (6.08MB) on 1 volume
> DUMP: DUMP IS DONE
> DUMP: Date of this level 7 dump: Sun Feb 22 02:46:59 1998
> DUMP: Date of last level 3 dump: Sat Feb 21 05:20:04 1998
> DUMP: Dumping /dev/rsd2a (/usr/local) to /dev/nrmt0 on host bigfoot
> DUMP: mapping (Pass I) [regular files]
> DUMP: mapping (Pass II) [directories]
> DUMP: mapping (Pass II) [directories]
> DUMP: mapping (Pass II) [directories]
> DUMP: estimated 576498 blocks (281.49MB) on 0.03 tape(s).
> DUMP: dumping (Pass III) [directories]
> DUMP: dumping (Pass IV) [regular files]
> DUMP: 97.17% done, finished in 0:00
> DUMP: level 7 dump on Sun Feb 22 02:46:59 1998
> DUMP: Tape rewinding
> DUMP: 577062 blocks (281.77MB) on 1 volume
> DUMP: DUMP IS DONE
> DUMP: Date of this level 7 dump: Sun Feb 22 02:54:18 1998
> DUMP: Date of last level 3 dump: Sat Feb 21 05:28:41 1998
> DUMP: Dumping /dev/rsd0a (/) to /dev/nrmt0 on host bigfoot
> DUMP: mapping (Pass I) [regular files]
> DUMP: mapping (Pass II) [directories]
> DUMP: mapping (Pass II) [directories]
> DUMP: estimated 4658 blocks (2.27MB) on 0.00 tape(s).
> DUMP: dumping (Pass III) [directories]
> DUMP: dumping (Pass IV) [regular files]
> DUMP: level 7 dump on Sun Feb 22 02:54:18 1998
> DUMP: Tape rewinding
> DUMP: 4666 blocks (2.28MB) on 1 volume
> DUMP: DUMP IS DONE
> DUMP: Date of this level 7 dump: Sun Feb 22 02:54:28 1998
> DUMP: Date of last level 3 dump: Sat Feb 21 05:29:03 1998
> DUMP: Dumping /dev/rsd0g (/usr) to /dev/nrmt0 on host bigfoot
> DUMP: mapping (Pass I) [regular files]
> DUMP: mapping (Pass II) [directories]
> DUMP: mapping (Pass II) [directories]
> DUMP: mapping (Pass II) [directories]
> DUMP: mapping (Pass II) [directories]
> DUMP: estimated 4516 blocks (2.21MB) on 0.00 tape(s).
> DUMP: dumping (Pass III) [directories]
> DUMP: dumping (Pass IV) [regular files]
> DUMP: level 7 dump on Sun Feb 22 02:54:28 1998
> DUMP: Tape rewinding
> DUMP: 4512 blocks (2.20MB) on 1 volume
> DUMP: DUMP IS DONE
> End time: Sun Feb 22 02:54:59 PST 1998
> ARCHIVING: gemini
> gemini:/
>
> **** bru: execution summary ****
>
> Started: Sun Feb 22 02:57:09 1998
> Completed: Sun Feb 22 02:57:50 1998
> Archive id: 34f004852588
> Messages: 0 warnings, 0 errors
> Archive I/O: 6920 blocks (13840Kb) written
> Archive I/O: 0 blocks (0Kb) read
> Files written: 18 files (15 regular, 3 other)
> Files read: 0 files (0 regular, 0 other)
> Write errors: 0 soft, 0 hard
> Read errors: 0 soft, 0 hard
> Checksum errors: 0
> gemini:/usr
>
> **** bru: execution summary ****
>
> Started: Sun Feb 22 02:57:50 1998
> Completed: Sun Feb 22 02:58:34 1998
> Archive id: 34f004ae2595
> Messages: 0 warnings, 0 errors
> Archive I/O: 10 blocks (20Kb) written
> Archive I/O: 0 blocks (0Kb) read
> Files written: 1 files (1 regular, 0 other)
> Files read: 0 files (0 regular, 0 other)
> Write errors: 0 soft, 0 hard
> Read errors: 0 soft, 0 hard
> Checksum errors: 0
> gemini:/usr/local
>
> **** bru: execution summary ****
>
> Started: Sun Feb 22 02:58:34 1998
> Completed: Sun Feb 22 03:00:45 1998
> Archive id: 34f004da259d
> Messages: 0 warnings, 0 errors
> Archive I/O: 22980 blocks (45960Kb) written
> Archive I/O: 0 blocks (0Kb) read
> Files written: 419 files (419 regular, 0 other)
> Files read: 0 files (0 regular, 0 other)
> Write errors: 0 soft, 0 hard
> Read errors: 0 soft, 0 hard
> Checksum errors: 0
> gemini:/usr/local/apache/sites
>
> **** bru: execution summary ****
>
> Started: Sun Feb 22 03:00:45 1998
> Completed: Sun Feb 22 03:14:29 1998
> Archive id: 34f0055d25ba
> Messages: 0 warnings, 0 errors
> Archive I/O: 48900 blocks (97800Kb) written
> Archive I/O: 0 blocks (0Kb) read
> Files written: 814 files (814 regular, 0 other)
> Files read: 0 files (0 regular, 0 other)
> Write errors: 0 soft, 0 hard
> Read errors: 0 soft, 0 hard
> Checksum errors: 0
> gemini:/mnt/logs
>
> **** bru: execution summary ****
>
> Started: Sun Feb 22 03:14:29 1998
> Completed: Sun Feb 22 03:17:10 1998
> Archive id: 34f00895260f
> Messages: 0 warnings, 0 errors
> Archive I/O: 20 blocks (40Kb) written
> Archive I/O: 0 blocks (0Kb) read
> Files written: 5 files (5 regular, 0 other)
> Files read: 0 files (0 regular, 0 other)
> Write errors: 0 soft, 0 hard
> Read errors: 0 soft, 0 hard
> Checksum errors: 0
> ARCHIVING: betty
> betty:/usr
>
> **** bru: execution summary ****
>
> Started: Sun Feb 22 03:15:42 1998
> Completed: Sun Feb 22 03:16:48 1998
> Archive id: 34f008de0465
> Messages: 0 warnings, 0 errors
> Archive I/O: 3980 blocks (7960Kb) written
> Archive I/O: 0 blocks (0Kb) read
> Files written: 8 files (8 regular, 0 other)
> Files read: 0 files (0 regular, 0 other)
> Write errors: 0 soft, 0 hard
> Read errors: 0 soft, 0 hard
> Checksum errors: 0
> betty:/usr/local/samba
>
> **** bru: execution summary ****
>
> Started: Sun Feb 22 03:16:48 1998
> Completed: Sun Feb 22 03:17:46 1998
> Archive id: 34f00920046c
> Messages: 0 warnings, 0 errors
> Archive I/O: 3630 blocks (7260Kb) written
> Archive I/O: 0 blocks (0Kb) read
> Files written: 55 files (55 regular, 0 other)
> Files read: 0 files (0 regular, 0 other)
> Write errors: 0 soft, 0 hard
> Read errors: 0 soft, 0 hard
> Checksum errors: 0
> ARCHIVING: bigfoot
> bigfoot:/var/log0
>
> **** bru: execution summary ****
>
> Started: Sun Feb 22 03:23:13 1998
> Completed: Sun Feb 22 03:30:26 1998
> Archive id: 34f00aa15d23
> Messages: 0 warnings, 0 errors
> Archive I/O: 272880 blocks (545760Kb) written
> Archive I/O: 0 blocks (0Kb) read
> Files written: 937 files (937 regular, 0 other)
> Files read: 0 files (0 regular, 0 other)
> Write errors: 0 soft, 0 hard
> Read errors: 0 soft, 0 hard
> Checksum errors: 0
> ARCHIVING: ella.pscs.org
> ella:/var
>
> **** bru: execution summary ****
>
> Started: Sun Feb 22 03:27:39 1998
> Completed: Sun Feb 22 03:28:46 1998
> Archive id: 34f00bab08e0
> Messages: 0 warnings, 0 errors
> Archive I/O: 13830 blocks (27660Kb) written
> Archive I/O: 0 blocks (0Kb) read
> Files written: 58 files (58 regular, 0 other)
> Files read: 0 files (0 regular, 0 other)
> Write errors: 0 soft, 0 hard
> Read errors: 0 soft, 0 hard
> Checksum errors: 0
On Sun, 22 February 1998, at 17:47:07, Vadim B. Mikheev wrote:

> X-VM-v5-Data: ([t nil nil nil nil nil nil nil nil]
> ["1080" "Sun" "22" "February" "1998" "17:47:07" "+0700" "Vadim B. Mikheev" "vadim(at)sable(dot)krasnoyarsk(dot)su" nil "32" "Re: [HACKERS] How To free resources used by large object Relations?" nil nil nil "2" nil nil (number " " mark "N Vadim B. Mikheev Feb 22 32/1080 " thread-indent "\"Re: [HACKERS] How To free resources used by large object Relations?\"\n") nil nil]
> nil)
> Return-Path: <owner-pgsql-hackers(at)hub(dot)org>
> Received: from hub.org (hub.org [209.47.148.200])
> by abraxas.scene.com (8.8.8/8.8.5) with ESMTP id CAA16288
> for <brett(at)work(dot)chicken(dot)org>; Sun, 22 Feb 1998 02:51:20 -0800
> Received: from localhost (majordom(at)localhost) by hub.org (8.8.8/8.7.5) with SMTP id FAA28144; Sun, 22 Feb 1998 05:45:06 -0500 (EST)
> Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Sun, 22 Feb 1998 05:44:57 -0500 (EST)
> Received: (from majordom(at)localhost) by hub.org (8.8.8/8.7.5) id FAA28023 for pgsql-hackers-outgoing; Sun, 22 Feb 1998 05:44:49 -0500 (EST)
> Received: from dune.krasnet.ru (dune.krasnet.ru [193.125.44.86]) by hub.org (8.8.8/8.7.5) with ESMTP id FAA27920 for <pgsql-hackers(at)postgreSQL(dot)org>; Sun, 22 Feb 1998 05:44:29 -0500 (EST)
> Received: from sable.krasnoyarsk.su (dune.krasnet.ru [193.125.44.86])
> by dune.krasnet.ru (8.8.7/8.8.7) with ESMTP id RAA00809;
> Sun, 22 Feb 1998 17:47:19 +0700 (KRS)
> (envelope-from vadim(at)sable(dot)krasnoyarsk(dot)su)
> Message-ID: <34F0022B(dot)60E59681(at)sable(dot)krasnoyarsk(dot)su>
> Date: Sun, 22 Feb 1998 17:47:07 +0700
> From: "Vadim B. Mikheev" <vadim(at)sable(dot)krasnoyarsk(dot)su>
> Organization: ITTS (Krasnoyarsk)
> X-Mailer: Mozilla 4.04 [en] (X11; I; FreeBSD 2.2.5-RELEASE i386)
> MIME-Version: 1.0
> To: Maurice Gittens <mgittens(at)gits(dot)nl>
> CC: pgsql-hackers(at)postgreSQL(dot)org
> Subject: Re: [HACKERS] How To free resources used by large object Relations?
> References: <018001bd3e01$7651be80$fcf3b2c2(at)caleb(dot)(dot)gits(dot)nl>
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
> Sender: owner-pgsql-hackers(at)hub(dot)org
> Precedence: bulk
>
> Maurice Gittens wrote:
> >
> > Hi,
> >
> > I've changed the large object memory managment strategy to ensure that
> > each large object has it's own memory context.
> > This way I can free all memory for a large object when I lo_close
> > is called for it's oid. This seems to work.
> >
> > I've noticed that the heap_close function used to close the heap used
> > by a large object doesn't really do any thing. (It calls RelationClose
> > which decrements some reference count).
> >
> > Somehow I have to free the relation from the cache in the following
> > situations:
> > 1. In a transaction I must free the stuff when the transaction is
> > commited/aborted.
>
> Backend does it, don't worry.
>
> > 2. Otherwise it must happen when lo_close is called.
>
> It seems that you can't remove relation from cache untill
> commit/abort, currently: backend uses local cache to unlink
> files of relations created in transaction if abort...
> We could change relcache.c:RelationPurgeLocalRelation()
> to read from pg_class directly...
>
> But how many LO do you create in single xact ?
> Is memory allocated for cache so big ?
>
> Vadim
On Sun, 22 February 1998, at 02:49:37, Eve Arden wrote:

> X-VM-v5-Data: ([t nil nil nil nil nil nil nil nil]
> ["129" "Sun" "22" "February" "1998" "02:49:37" "-0800" "Eve Arden" "root(at)eve(dot)speakeasy(dot)org" nil "2" "" nil nil nil "2" nil nil (number " " mark "N Eve Arden Feb 22 2/129 " thread-indent "\"\"\n") nil nil]
> nil)
> Return-Path: <root(at)eve(dot)speakeasy(dot)org>
> Received: from eve.speakeasy.org (root(at)eve(dot)speakeasy(dot)org [199.238.226.1])
> by abraxas.scene.com (8.8.8/8.8.5) with ESMTP id CAA16276
> for <brett(at)work(dot)chicken(dot)org>; Sun, 22 Feb 1998 02:49:50 -0800
> Received: (from root(at)localhost) by eve.speakeasy.org (8.8.5/8.7.3) id CAA01899; Sun, 22 Feb 1998 02:49:37 -0800 (PST)
> Date: Sun, 22 Feb 1998 02:49:37 -0800 (PST)
> From: Eve Arden <root(at)eve(dot)speakeasy(dot)org>
> Message-Id: <199802221049(dot)CAA01899(at)eve(dot)speakeasy(dot)org>
>
> # Checking accounts from NIS.
> >--WARN-- [acc001w] Login ID a-ron is disabled, but still has a valid shell (/usr/local/bin/tcsh).