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

Brett wrote:
>
>
> 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?

No actual development - just have something in mind how I
would implement it. I'll get into details after 6.3 release.
PL/pgSQL will have at least the following capabilities:

- local variable
- local records
- access to the database over SPI
- control structures (if/else/while/loop)
- elog messages
- triggers can modify new tuple
- triggers can skip operation

Why not handing many backend internals through a PL? Just to
let ordinary users use the language without breaking
security. The implementation of PL/Tcl uses a safe Tcl
interpreter for the evaluation of the Tcl functions/trigger-
procedures. A safe Tcl interpreter has very limited command
set. No access to filesystem, no access to networking, no
loading of other packages so nothing dangerous.

If perl doesn't have such a restricted interpreter facility,
then perl might never become a TRUSTED procedural language
like Tcl is. Remember, if the PL/perl implementation gives
an ordinary user the right just to open a file for writing, a
function independent who created it could damage database
files directly. This MUST be restricted to superusers and
this is the reason why the C language isn't TRUSTED.

>
> 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?

Whatever perl scalars are - don't know. Tcl is happy with
string representation of anything on the evaluation level and
Tcl_Eval() leaves a string in the interpreter result. So I
used the registered input/output functions to convert
anything from/into strings to communicate with the
interpreter.

Whatever is given to or returned from a Tcl function in
PL/Tcl will be in the external representation. This is what
the user sees if he selects from a table in psql and what has
to be given on insert/update. So on the PL/Tcl level,
anything is the external representation as defined by the
types registered input/output functions. This is the values
format humans deal with best.

I know that the way I choose isn't the performance optimized
one. But blowing up the functions code by handling binary
data wouldn't be either.

When implementing perl or Tcl as a procedural language, we
must satisfy the need of perl/Tcl programmers. These
programmers must not have any C skill, but they sure will
know how to read/create a string in the external
representation. For some types (especially for user created
types) it might be hard to figure out what each bit in the
binary Datum is for if you aren't familiar with C.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck(at)debis(dot)com (Jan Wieck) #