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

Hello,
I've installed postgresql-v6.4 on BSDI-3.1
But, there are two problems with PL/pgSQL:
First: I have to install plpgsql language on every database,
I wonder, if I can install it for everyone.

Second: When I install plpgsql language, and prepare one test:
=>CREATE FUNCTION f_test () RETURNS abstime AS '
=>        BEGIN
=>                RETURN \'now\';
=>        END;
=>' LANGUAGE 'plpgsql';
CREATE
EOF
Then I do:
=> select f_test();
It tells me, that:
pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally before or
while pr
ocessing the request.
We have lost the connection to the backend, so further processing is
impossible.
  Terminating.
and exit me to the shell. What is wrong ?

Thank you,
---------------------------
Sergei Chernev
Internet: ser@nsu.ru
Phone: +7-3832-397354

Hello Sergei,

mercoledм, 11 novembre 98, you wrote:

SC> Hello,
SC> I've installed postgresql-v6.4 on BSDI-3.1
SC> But, there are two problems with PL/pgSQL:
SC> First: I have to install plpgsql language on every database,
SC> I wonder, if I can install it for everyone.
If you install PL/pgSQL on template1 it will automatically be copied to every
database you will create. This is not valid for databases created
before you install it on template1.

SC> Second: When I install plpgsql language, and prepare one test:
=>>CREATE FUNCTION f_test () RETURNS abstime AS '
=>>        BEGIN
=>>                RETURN \'now\';
=>>        END;
=>>' LANGUAGE 'plpgsql';
SC> CREATE
SC> EOF
SC> Then I do:
=>> select f_test();
SC> It tells me, that:
SC> pqReadData() -- backend closed the channel unexpectedly.
SC>         This probably means the backend terminated abnormally before or
SC> while pr
SC> ocessing the request.
SC> We have lost the connection to the backend, so further processing is
SC> impossible.
SC>   Terminating.
SC> and exit me to the shell. What is wrong ?


On my Linux box it works:

CREATE FUNCTION f_test () RETURNS abstime AS '
        BEGIN
                RETURN \'now\';
        END;
' LANGUAGE 'plpgsql';
CREATE

select f_test();
f_test
----------------------
1998-11-11 18:57:46+01
(1 row)

-Jose'-

[Charset koi8-r unsupported, filtering to ASCII...]
> Hello,
> I've installed postgresql-v6.4 on BSDI-3.1
> But, there are two problems with PL/pgSQL:
> First: I have to install plpgsql language on every database,
> I wonder, if I can install it for everyone.

If you put it in template1, then every new database gets it.

>
> Second: When I install plpgsql language, and prepare one test:
> =>CREATE FUNCTION f_test () RETURNS abstime AS '
> =>        BEGIN
> =>                RETURN \'now\';
> =>        END;
> =>' LANGUAGE 'plpgsql';
> CREATE
> EOF
> Then I do:
> => select f_test();
> It tells me, that:
> pqReadData() -- backend closed the channel unexpectedly.
>         This probably means the backend terminated abnormally before or
> while pr
> ocessing the request.
> We have lost the connection to the backend, so further processing is
> impossible.
>   Terminating.
> and exit me to the shell. What is wrong ?

It shouldn't be doing this, but it is.  Something wrong about the way
bsdi 3.* is doing dynamic linking, I think.  bsdi 4.0, which I have here
works.

Can you do it in gdb and send the backtrace of the crash?

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

Bruce Momjian wrote:
  >> First: I have to install plpgsql language on every database,
  >> I wonder, if I can install it for everyone.
  >
  >If you put it in template1, then every new database gets it.

As a contribution, here is a script to do this for existing databases
(including template1):

=================== begin script enable_pgpl ==============================
#!/bin/sh

# Enable the PL procedural language for PostgreSQL in one or more
# existing databases.
#
# This script should be run by the PostgreSQL superuser

enable_database() {
    if ! psql -d $1 -qtc "select count(*) from pg_language where lanname='plpgsql'" >$TMPFIL2 2>&1
    then
        echo "Cannot connect to $1"
        exit 2
    fi
    if [ `cat $TMPFIL2` -eq 0 ]
    then
        if ! psql -d $1 <$sqlfile
        then
            echo "Failed to add PL to $1"
            exit 2
        fi
        echo "PL added to $1"
    else
        echo "PL is already enabled in $1"
    fi

}

# Execution starts here

TMPFILE=`mktemp /tmp/enable_pgpl.XXXXXX`
TMPFIL2=`mktemp /tmp/enable_pgpl.XXXXXX`
trap "rm $TMPFILE $TMPFIL2" EXIT

sqlfile=${PGLIB:=/usr/local/pgsql/lib}/mklang_pl.sql
if [ ! -f $sqlfile ]
then
    echo "Cannot find mklang_pl.sql"
    exit 2
fi

if [ -z "$1" ]
then
    echo "Syntax: $0 --all | database ..."
    exit 1
fi

if [ $1 = "--all" ]
then
    if ! psql -t -c "select datname from pg_database order by datname" >$TMPFILE
    then
        echo Cannot select databases
        exit 2
    fi
    for db in `cat $TMPFILE`
    do
        enable_database $db
    done
else
    while [ -n "$1" ]
    do
        db=$1
        enable_database $db
        shift
    done
fi
========================= end ================================

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
               PGP key from public servers; key ID 32B8FAA1
                 ========================================
     "Be of good courage, and he shall strengthen your
      heart, all ye that hope in the LORD."
                                  Psalms 31:24

Added to src/pl/plpgsql in both trees.  It is not installed
automatically.  Can someone figure out how this should be handled?



> Bruce Momjian wrote:
>   >> First: I have to install plpgsql language on every database,
>   >> I wonder, if I can install it for everyone.
>   >
>   >If you put it in template1, then every new database gets it.
>
> As a contribution, here is a script to do this for existing databases
> (including template1):
>
> =================== begin script enable_pgpl ==============================
> #!/bin/sh
>
> # Enable the PL procedural language for PostgreSQL in one or more
> # existing databases.
> #
> # This script should be run by the PostgreSQL superuser
>
> enable_database() {
>     if ! psql -d $1 -qtc "select count(*) from pg_language where lanname='plpgsql'" >$TMPFIL2 2>&1
>     then
>         echo "Cannot connect to $1"
>         exit 2
>     fi
>     if [ `cat $TMPFIL2` -eq 0 ]
>     then
>         if ! psql -d $1 <$sqlfile
>         then
>             echo "Failed to add PL to $1"
>             exit 2
>         fi
>         echo "PL added to $1"
>     else
>         echo "PL is already enabled in $1"
>     fi
>
> }
>
> # Execution starts here
>
> TMPFILE=`mktemp /tmp/enable_pgpl.XXXXXX`
> TMPFIL2=`mktemp /tmp/enable_pgpl.XXXXXX`
> trap "rm $TMPFILE $TMPFIL2" EXIT
>
> sqlfile=${PGLIB:=/usr/local/pgsql/lib}/mklang_pl.sql
> if [ ! -f $sqlfile ]
> then
>     echo "Cannot find mklang_pl.sql"
>     exit 2
> fi
>
> if [ -z "$1" ]
> then
>     echo "Syntax: $0 --all | database ..."
>     exit 1
> fi
>
> if [ $1 = "--all" ]
> then
>     if ! psql -t -c "select datname from pg_database order by datname" >$TMPFILE
>     then
>         echo Cannot select databases
>         exit 2
>     fi
>     for db in `cat $TMPFILE`
>     do
>         enable_database $db
>     done
> else
>     while [ -n "$1" ]
>     do
>         db=$1
>         enable_database $db
>         shift
>     done
> fi
> ========================= end ================================
>
> --
> Oliver Elphick                                Oliver.Elphick@lfix.co.uk
> Isle of Wight                              http://www.lfix.co.uk/oliver
>                PGP key from public servers; key ID 32B8FAA1
>                  ========================================
>      "Be of good courage, and he shall strengthen your
>       heart, all ye that hope in the LORD."
>                                   Psalms 31:24
>
>
>


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