Thread: RE: [HACKERS] PL/pgSQL a great procedural language for PostgreSQL

RE: [HACKERS] PL/pgSQL a great procedural language for PostgreSQL

From
"Jackson, DeJuan"
Date:
> > It appears that when a function is called if any of the paramaters
> are
> > NULL all of the parameters are NULL.
> > try:
> >  drop function nvl(int, int);
> >  create function nvl(int, int) returns boolean as '
> >   declare
> >     nonullo alias as $1;
> >     nullo alias as $2;
> >   begin
> >    return (nonullo IS NULL) AND (nullo IS NULL);
> >   end;' language 'plpgsql';
> >  select nvl(i,0) from a;
> > you should get:
> > nvl
> > ---
> > t
> > t
> > f
> > t
> > (4 rows)
> 
>     Don't blame PL/pgSQL for that.  There is only one bool isNull
>     pointer given to PL handlers. How should the PL handler know,
>     which  of  the arguments are null then?  As I said on another
>     thread, the function call interface needs to get  redesigned.
Well, Jan, don't get sensitive.  I love PL/pgSQL. And I had no illusions
that it was your HANDLER causing the problem.  I feel that a function
call interface redesign is also needed.  But, I do have a quick
question, why does it matter which one is NULL if you can still obtain
the parameters in the order they were passed why would one become NULL
that wasn't before?   I'm asking totally from ignorance here.-DEJ

> Jan
> 


Re: [HACKERS] PL/pgSQL a great procedural language for PostgreSQL

From
jwieck@debis.com (Jan Wieck)
Date:
> >     Don't blame PL/pgSQL for that.  There is only one bool isNull
> >     pointer given to PL handlers. How should the PL handler know,
> >     which  of  the arguments are null then?  As I said on another
> >     thread, the function call interface needs to get  redesigned.
> Well, Jan, don't get sensitive.  I love PL/pgSQL. And I had no illusions
> that it was your HANDLER causing the problem.  I feel that a function
> call interface redesign is also needed.  But, I do have a quick
> question, why does it matter which one is NULL if you can still obtain
> the parameters in the order they were passed why would one become NULL
> that wasn't before?   I'm asking totally from ignorance here.

    It might be possible, that even if *isNull is true to look at
    the actual arguments given to the PL handler.  For  datatypes
    passed  by  reference, a NULL value has to get passed as null
    pointer. I'm not 100% sure if that  is  really  true  in  all
    cases where PL functions can get called, and we all know what
    happens when accessing a pointer  that  points  to  something
    else than a memory location. For arguments passed by value it
    is totally impossible to know if it's a NULL  by  looking  at
    the value itself.

    Summary  is,  that the PL handler cannot be sure which of the
    arguments the function caller meant when calling with *isNull
    = TRUE.  And I decided for now to be safe and assume he meant
    all.

    When accessing data from a specific table, it is possible  to
    call  the  function  with  a  complex  type.  This  time, the
    PL/pgSQL function gets the complete tuple and can look at the
    information there which attributes are NULLs.

        CREATE TABLE a (k integer, i integer);

        CREATE FUNCTION a_i_checknull(a) RETURNS bool AS '
        DECLARE
            row_a ALIAS FOR $1; -- The dot-notation $1.i does not work!
        BEGIN
            IF row_a.k ISNULL THEN
                RAISE NOTICE ''attribute k has NULL value'';
            END IF;
            IF row_a.i ISNULL THEN
                RAISE NOTICE ''attribute i has NULL value'';
            END IF;
            IF row_a.k ISNULL OR row_a.i ISNULL THEN
                RETURN ''t'';
            END IF;
            RETURN ''f'';
        END;
        ' LANGUAGE 'plpgsql';


Jan

--

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

Re: [HACKERS] PL/pgSQL a great procedural language for PostgreSQL

From
"Thomas G. Lockhart"
Date:
>     For arguments passed by value it
>     is totally impossible to know if it's a NULL  by  looking  at
>     the value itself.

Is this a sufficient reason to move toward having all user-oriented data
types be pass-by-reference? If we don't do that, then we would need to
pass a null flag for every parameter, or an array of flags, or have some
global array which contains the null flags (which we might get away with
since Postgres is pretty much single threaded and non-reentrant in the
backend code). What other options might there be?
                      - Tom


Re: [HACKERS] PL/pgSQL a great procedural language for PostgreSQL

From
Hannu Krosing
Date:
Thomas G. Lockhart wrote:
> 
> >     For arguments passed by value it
> >     is totally impossible to know if it's a NULL  by  looking  at
> >     the value itself.
> 
> Is this a sufficient reason to move toward having all user-oriented data
> types be pass-by-reference? If we don't do that, then we would need to
> pass a null flag for every parameter, or an array of flags, or have some
> global array which contains the null flags (which we might get away with
> since Postgres is pretty much single threaded and non-reentrant in the
> backend code). What other options might there be?

Perhaps make the isNull flag a bitmap ?

In case it is an int (I haven't looked), we get room for flagging 32
arguments

Also I hope that most current code will run unchanged, in case it tests
for 
0 / not 0

----------------
Hannu


linux libc6 & pgsql 6.4

From
Zsolt Varga
Date:
have anyone tested 6.4 on a libc6 based linux (for example debian hamm) ?

because  I tested it on an older system (libc5 based) and it's working
well.

but on libc6 it's compiled without errors, 
I can make the initdb, and start the postmaster,
but psql -l for example not working
every postgres backend exits after postmaster forked..

any idea?    redax

.----------------------------------------------------------.
|Zsolt Varga            | tel/fax:   +36 36 422811         |
| AgriaComputer LTD     | email:     redax@agria.hu        |
| System Administrator  | URL:       http://www.agria.hu/  |
`----------------------------------------------------------'



Re: [HACKERS] linux libc6 & pgsql 6.4

From
Hannu Krosing
Date:
Zsolt Varga wrote:
> 
> have anyone tested 6.4 on a libc6 based linux (for example debian hamm) ?
> 
> because  I tested it on an older system (libc5 based) and it's working
> well.
> 
> but on libc6 it's compiled without errors,
> I can make the initdb, and start the postmaster,
> but psql -l for example not working
> every postgres backend exits after postmaster forked..
> 
> any idea?

Does createdb work ?

-----------
Hannu


Re: [HACKERS] PL/pgSQL a great procedural language for PostgreSQL

From
"Thomas G. Lockhart"
Date:
> Perhaps make the isNull flag a bitmap ?
> In case it is an int (I haven't looked), we get room for flagging 32
> arguments

Hmm. Well, how about if we look at what we would prefer if we were
allowed to start from the beginning, and then consider this as an
alternative. Doing this with a bitmap might be a bit arcane.

> Also I hope that most current code will run unchanged, in case it 
> tests for 0 / not 0

Good point.
                     - Thomas


Re: [HACKERS] linux libc6 & pgsql 6.4

From
Michael Meskes
Date:
[Since my work email didn't go through on the list here's a short answer
again. ]

On Fri, Nov 06, 1998 at 10:33:48AM +0100, Zsolt Varga wrote:
> have anyone tested 6.4 on a libc6 based linux (for example debian hamm) ?

Yes, me. That is my debian system is newer than hamm.

> but on libc6 it's compiled without errors, 
> I can make the initdb, and start the postmaster,
> but psql -l for example not working
> every postgres backend exits after postmaster forked..

Works fine for me. No problem at all.

Michael
-- 
Dr. Michael Meskes, Leiter Niederlassung West, Datenrevision GmbH
business: Cuxhavener Str. 36, D-21149 Hamburgprivate:  Th.-Heuss-Str. 61, D-41812 Erkelenz, Michael.Meskes@usa.net
Go SF49ers! Go Rhein Fire! Use Debian GNU/Linux!




Re: [HACKERS] linux libc6 & pgsql 6.4

From
Terry Mackintosh
Date:
Hi all

On Fri, 6 Nov 1998, Michael Meskes wrote:

> On Fri, Nov 06, 1998 at 10:33:48AM +0100, Zsolt Varga wrote:
> > have anyone tested 6.4 on a libc6 based linux (for example debian hamm) ?
> 
> Yes, me. That is my debian system is newer than hamm.
> 
> > but on libc6 it's compiled without errors, 
> > I can make the initdb, and start the postmaster,
> > but psql -l for example not working
> > every postgres backend exits after postmaster forked..
> 
> Works fine for me. No problem at all.

This may or may not be related to your problem, but...
On my server I did only the min. install of Red Hat 4.2 that I needed to
run a server, then custom built most of the stuff such as PHP, Apache,
PostgreSQL ... etc.  I also change the permissions on many directories and
files.

And now I find that a normal user can not do some things that involve
'fork()'.  But all works fine as root, so it is a permission problem,
which I still have not pinned down, not even shure how to find it.:(

Example: Pine/ispell or Pine/vim
Any attemp to run an external program from pine, such as ispell, will not
work, pine will say that the program returned 255.  So I did some debuging
stuff and found the problem happens at the fork() call.

Does this sound like your problem?
If so, then start looking very closely at permissions.

Have a great day, and hope that helped.
Terry Mackintosh <terry@terrym.com>          http://www.terrym.com
sysadmin/owner  Please! No MIME encoded or HTML mail, unless needed.

Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3
-------------------------------------------------------------------
Success Is A Choice ... book by Rick Patino, get it, read it!



Re: [HACKERS] linux libc6 & pgsql 6.4

From
Michael Meskes
Date:
On Sat, Nov 07, 1998 at 08:37:51AM -0500, Terry Mackintosh wrote:
> > > but on libc6 it's compiled without errors, 
> > > I can make the initdb, and start the postmaster,
> > > but psql -l for example not working
> > > every postgres backend exits after postmaster forked..
> > 
> > Works fine for me. No problem at all.
> 
> This may or may not be related to your problem, but...

Ehem, I do not have that problem. I was the one answering that my
installation is okay. :-9

> And now I find that a normal user can not do some things that involve
> 'fork()'.  But all works fine as root, so it is a permission problem,
> which I still have not pinned down, not even shure how to find it.:(

I wonder which permissions have to do with fork().

Michael
-- 
Dr. Michael Meskes, Leiter Niederlassung West, Datenrevision GmbH
business: Cuxhavener Str. 36, D-21149 Hamburgprivate:  Th.-Heuss-Str. 61, D-41812 Erkelenz, Michael.Meskes@usa.net
Go SF49ers! Go Rhein Fire! Use Debian GNU/Linux!




Re: [HACKERS] linux libc6 & pgsql 6.4

From
Zsolt Varga
Date:
On Fri, 6 Nov 1998, Hannu Krosing wrote:

|Does createdb work ?
yeah worked, 
somebody sent me the solution from the list,
I have to remove termcap-compat from debian/hamm
and recompile... after it works well

great work, it's 3-4 times faster than 6.3.2 was
thanks        redax

.----------------------------------------------------------.
|Zsolt Varga            | tel/fax:   +36 36 422811         |
| AgriaComputer LTD     | email:     redax@agria.hu        |
| System Administrator  | URL:       http://www.agria.hu/  |
`----------------------------------------------------------'