Thread: triggers and functions in pgsql 7.0.2

triggers and functions in pgsql 7.0.2

From

Marc Britten

Date:

13 June 2000, 17:45:39

I'm going through your docs trying to create a function and a trigger that
calls said function, what I've gotten so far is

CREATE FUNCTION create_count_cache()
       RETURNS opaque AS
       '
     BEGIN;
     DELETE FROM SnipCount;
     INSERT INTO SnipCount SELECT LangID, COUNT(LangID) AS CNT FROM
snippet GROUP BY LangID;
     COMMIT;
       '
       LANGUAGE 'sql';

CREATE TRIGGER count_change
       AFTER DELETE OR INSERT ON snippet FOR EACH ROW
       EXECUTE PROCEDURE create_count_cache();

however LANGUAGE sql cannot return opaque and plpgsql is not a known lang
type.

it seems that all your docs are a bit out of date, can you give me some help
please?

marc britten

Re: triggers and functions in pgsql 7.0.2

From

Mike Mascari

Date:

13 June 2000, 17:58:49

Marc Britten wrote:
>
> I'm going through your docs trying to create a function and a trigger that
> calls said function, what I've gotten so far is
>
> CREATE FUNCTION create_count_cache()
>        RETURNS opaque AS
>        '
>          BEGIN;
>          DELETE FROM SnipCount;
>          INSERT INTO SnipCount SELECT LangID, COUNT(LangID) AS CNT FROM
> snippet GROUP BY LangID;
>          COMMIT;
>        '
>        LANGUAGE 'sql';
>
> CREATE TRIGGER count_change
>        AFTER DELETE OR INSERT ON snippet FOR EACH ROW
>        EXECUTE PROCEDURE create_count_cache();
>
> however LANGUAGE sql cannot return opaque and plpgsql is not a known lang
> type.
>
> it seems that all your docs are a bit out of date, can you give me some help
> please?
>
> marc britten

The link below might help:

http://www.postgresql.org/docs/programmer/xplang.htm#XPLANG-TITLE

Hope that helps,

Mike Mascari

Re: triggers and functions in pgsql 7.0.2

From

Ed Loehr

Date:

13 June 2000, 18:05:33

Marc Britten wrote:
>
> I'm going through your docs trying to create a function and a trigger that
> calls said function, what I've gotten so far is...
>
> however ... plpgsql is not a known lang type.
>
> it seems that all your docs are a bit out of date, can you give me some help
> please?

http://www.deja.com/getdoc.xp?AN=606916743

Regards,
Ed Loehr

Re: triggers and functions in pgsql 7.0.2

From

philip@adhesivemedia.com (Philip Hallstrom)

Date:

13 June 2000, 18:29:42

I had this same problem... plpgsql is not built be default (at least for
7.0).  take a look at src/pl/plpgsql/src/INSTALL.  You just need to add
it... then switch your language back to plpgsql and it should work
(well, maybe it won't, but it shouldn't complain about not finding
plpgsql)
good luck!
-philip
In article <am.pgsql.general.960922068.691@illiad.adhesivemedia.com>,
Marc Britten  <mbritten@cybernet-usa.com> wrote:
>I'm going through your docs trying to create a function and a trigger that
>calls said function, what I've gotten so far is
>CREATE FUNCTION create_count_cache()
>       RETURNS opaque AS
>       '
>     BEGIN;
>     DELETE FROM SnipCount;
>     INSERT INTO SnipCount SELECT LangID, COUNT(LangID) AS CNT FROM
>snippet GROUP BY LangID;
>     COMMIT;
>       '
>       LANGUAGE 'sql';
>CREATE TRIGGER count_change
>       AFTER DELETE OR INSERT ON snippet FOR EACH ROW
>       EXECUTE PROCEDURE create_count_cache();
>however LANGUAGE sql cannot return opaque and plpgsql is not a known lang
>type.
>it seems that all your docs are a bit out of date, can you give me some help
>please?
>marc britten

initlocation and createdb

From

"Barnes"

Date:

13 June 2000, 19:56:05

I'm having trouble setting up databases in a new location.  In particular, I
do the following:

[postgres@whopper pgdata]$ export PGDATA2=/home/pgdata
[postgres@whopper pgdata]$ initlocation PGDATA2
The location will be initialized with username "postgres".
This user will own all the files and must also own the server process.

Fixing permissions on pre-existing directory /home/pgdata
Creating directory /home/pgdata/base

initlocation is complete.
You can now create a database using
  CREATE DATABASE <name> WITH LOCATION = 'PGDATA2'
in SQL, or
  createdb <name> -D 'PGDATA2'
from the shell.

[postgres@whopper pgdata]$ createdb optodb -D 'PGDATA2'
ERROR:  The database path 'PGDATA2' is invalid. This may be due to a
character that is not allowed or because the chosen path isn't permitted for
databases
createdb: database creation failed
[postgres@whopper pgdata]$

The permissions for /home/pgdata are

drwx------   3 postgres postgres     4096 Jun 13 16:41 pgdata

What am I doing wrong?  Is /home not permitted for databases?  If so, why
not, and what is permitted?

Thank you for any help.

David Barnes

Re: initlocation and createdb

From

Ed Loehr

Date:

13 June 2000, 20:10:18

Barnes wrote:
>
> I'm having trouble setting up databases in a new location.  In particular, I
> do the following:
>
> [postgres@whopper pgdata]$ export PGDATA2=/home/pgdata
> [postgres@whopper pgdata]$ initlocation PGDATA2
> The location will be initialized with username "postgres".
> This user will own all the files and must also own the server process.
>
> Fixing permissions on pre-existing directory /home/pgdata
> Creating directory /home/pgdata/base
>
> initlocation is complete.
> You can now create a database using
>   CREATE DATABASE <name> WITH LOCATION = 'PGDATA2'
> in SQL, or
>   createdb <name> -D 'PGDATA2'
> from the shell.
>
> [postgres@whopper pgdata]$ createdb optodb -D 'PGDATA2'
> ERROR:  The database path 'PGDATA2' is invalid. This may be due to a
> character that is not allowed or because the chosen path isn't permitted for
> databases
> createdb: database creation failed
> [postgres@whopper pgdata]$
>
> The permissions for /home/pgdata are
>
> drwx------   3 postgres postgres     4096 Jun 13 16:41 pgdata
>
> What am I doing wrong?  Is /home not permitted for databases?  If so, why
> not, and what is permitted?

I don't know what's wrong, but it seems strange that you would pass
'PGDATA2' and not $PGDATA2 to initlocation (i.e., strange to let an
application interpret a passed-in shell variable rather than shell
interpretation).  Not sure it's *wrong*, though, 'cuz it seems to
interpret it as '/home/pgdata', suggesting maybe it does interpret the
shell variable.  But the latter "CREATE DATABASE" command suggests it is
bogus...

Regards,
Ed Loehr

Re: initlocation and createdb

From

Ed Loehr

Date:

13 June 2000, 20:14:27

Ed Loehr wrote:
>
> Barnes wrote:
> >
> > I'm having trouble setting up databases in a new location.  In particular, I
> > do the following:
> >
> > [postgres@whopper pgdata]$ export PGDATA2=/home/pgdata
> > [postgres@whopper pgdata]$ initlocation PGDATA2
> > [postgres@whopper pgdata]$ createdb optodb -D 'PGDATA2'
> > ERROR:  The database path 'PGDATA2' is invalid. This may be due to a
> > character that is not allowed or because the chosen path isn't permitted for
> > databases
> > createdb: database creation failed
> > [postgres@whopper pgdata]$
> >
> > What am I doing wrong?  Is /home not permitted for databases?  If so, why
> > not, and what is permitted?

Looking at the documentation, David is apparently following this one,
which seems wrong...

    http://www.postgresql.org/docs/postgres/app-initlocation.htm

Seems like it should be updated to the following:

$ export PGDATA2=/opt/postgres/data
$ initlocation $PGDATA2
$ createdb testdb -D $PGDATA2

Regards,
Ed Loehr

Re: initlocation and createdb

From

Ed Loehr

Date:

13 June 2000, 20:21:16

Ed Loehr wrote:
>
> Ed Loehr wrote:
> >
> > Barnes wrote:
> > >
> > > I'm having trouble setting up databases in a new location.  In particular, I
> > > do the following:
> > >
> > > [postgres@whopper pgdata]$ export PGDATA2=/home/pgdata
> > > [postgres@whopper pgdata]$ initlocation PGDATA2
> > > [postgres@whopper pgdata]$ createdb optodb -D 'PGDATA2'
> > > ERROR:  The database path 'PGDATA2' is invalid. This may be due to a
> > > character that is not allowed or because the chosen path isn't permitted for
> > > databases
> > > createdb: database creation failed
> > > [postgres@whopper pgdata]$
> > >
> > > What am I doing wrong?  Is /home not permitted for databases?  If so, why
> > > not, and what is permitted?
>
> Looking at the documentation, David is apparently following this one,
> which seems wrong...
>
>         http://www.postgresql.org/docs/postgres/app-initlocation.htm
>
> Seems like it should be updated to the following:
>
> $ export PGDATA2=/opt/postgres/data
> $ initlocation $PGDATA2
> $ createdb testdb -D $PGDATA2

From the same page:  "If the argument does not contain a slash and is not
valid as a path, it is assumed to be an environment variable, which is
referenced."  Ya know, sometimes I just can't help but embarrass myself.

I think the problem is that, while initlocation does accept the name of
an environment variable, createdb may not, and needs the $.  I'll sit
down now.

Regards,
Ed Loehr

RE: initlocation and createdb

From

"Barnes"

Date:

13 June 2000, 20:30:05

Hi, Ed.  When I try it with the $, I get:

[postgres@whopper /home]$ createdb optodb -D $PGDATA2
ERROR:  The database path '/home/pgdata' is invalid. This may be due to a
character that is not allowed or because the chosen path isn't permitted for
databases
createdb: database creation failed

which looks like a variation on the same problem??

Thanks again.

-----Original Message-----
From: ed [mailto:ed]On Behalf Of Ed Loehr
Sent: Tuesday, June 13, 2000 5:27 PM
To: Barnes; pgsql-general@postgresql.org
Subject: Re: [GENERAL] initlocation and createdb

Ed Loehr wrote:
>
> Ed Loehr wrote:
> >
> > Barnes wrote:
> > >
> > > I'm having trouble setting up databases in a new location.  In
particular, I
> > > do the following:
> > >
> > > [postgres@whopper pgdata]$ export PGDATA2=/home/pgdata
> > > [postgres@whopper pgdata]$ initlocation PGDATA2
> > > [postgres@whopper pgdata]$ createdb optodb -D 'PGDATA2'
> > > ERROR:  The database path 'PGDATA2' is invalid. This may be due to a
> > > character that is not allowed or because the chosen path isn't
permitted for
> > > databases
> > > createdb: database creation failed
> > > [postgres@whopper pgdata]$
> > >
> > > What am I doing wrong?  Is /home not permitted for databases?  If so,
why
> > > not, and what is permitted?
>
> Looking at the documentation, David is apparently following this one,
> which seems wrong...
>
>         http://www.postgresql.org/docs/postgres/app-initlocation.htm
>
> Seems like it should be updated to the following:
>
> $ export PGDATA2=/opt/postgres/data
> $ initlocation $PGDATA2
> $ createdb testdb -D $PGDATA2

From the same page:  "If the argument does not contain a slash and is not
valid as a path, it is assumed to be an environment variable, which is
referenced."  Ya know, sometimes I just can't help but embarrass myself.

I think the problem is that, while initlocation does accept the name of
an environment variable, createdb may not, and needs the $.  I'll sit
down now.

Regards,
Ed Loehr

initlocation and createdb

From

Richard J Kuhns

Date:

13 June 2000, 20:39:53

Barnes writes:
 > I'm having trouble setting up databases in a new location.  In particular, I
 > do the following:
 >
 > [postgres@whopper pgdata]$ export PGDATA2=/home/pgdata
 > [postgres@whopper pgdata]$ initlocation PGDATA2
 > The location will be initialized with username "postgres".
 > This user will own all the files and must also own the server process.
 >
 > Fixing permissions on pre-existing directory /home/pgdata
 > Creating directory /home/pgdata/base
 >
 > initlocation is complete.
 > You can now create a database using
 >   CREATE DATABASE <name> WITH LOCATION = 'PGDATA2'
 > in SQL, or
 >   createdb <name> -D 'PGDATA2'
 > from the shell.
 >
 > [postgres@whopper pgdata]$ createdb optodb -D 'PGDATA2'
 > ERROR:  The database path 'PGDATA2' is invalid. This may be due to a
 > character that is not allowed or because the chosen path isn't permitted for
 > databases
 > createdb: database creation failed
 > [postgres@whopper pgdata]$
 >
 > The permissions for /home/pgdata are
 >
 > drwx------   3 postgres postgres     4096 Jun 13 16:41 pgdata
 >
 > What am I doing wrong?  Is /home not permitted for databases?  If so, why
 > not, and what is permitted?
 >
 > Thank you for any help.
 >
 > David Barnes
 >

You need to stop the backend and restart it with "PGDATA2=/home/pgdata" in
it's environment.

            - Rich

--
Richard Kuhns            rjk@grauel.com
PO Box 6249            Tel: (765)477-6000 \
100 Sawmill Road                    x319
Lafayette, IN  47903             (800)489-4891 /

Re: initlocation and createdb

From

Tom Lane

Date:

13 June 2000, 21:14:24

"Barnes" <aardvark@ibm.net> writes:
> I'm having trouble setting up databases in a new location.  In particular, I
> do the following:

> [postgres@whopper pgdata]$ export PGDATA2=/home/pgdata
> [postgres@whopper pgdata]$ initlocation PGDATA2
> The location will be initialized with username "postgres".
> This user will own all the files and must also own the server process.

> Fixing permissions on pre-existing directory /home/pgdata
> Creating directory /home/pgdata/base

> initlocation is complete.
> You can now create a database using
>   CREATE DATABASE <name> WITH LOCATION = 'PGDATA2'
> in SQL, or
>   createdb <name> -D 'PGDATA2'
> from the shell.

> [postgres@whopper pgdata]$ createdb optodb -D 'PGDATA2'
> ERROR:  The database path 'PGDATA2' is invalid. This may be due to a
> character that is not allowed or because the chosen path isn't permitted for
> databases
> createdb: database creation failed
> [postgres@whopper pgdata]$

I believe the problem here is that you haven't made the environment
variable PGDATA2 visible to the postmaster.  initlocation is just a
shell script and will run fine as long as the environment variable
is in its environment --- but when you try to access that location
in a backend operation, the environment variable must be present in
the postmaster's environment.

In short: restart the postmaster with PGDATA2=/home/pgdata in its
environment.

This whole initlocation mechanism is pretty bizarre and confusing IMHO.
I believe the motivation for it is to allow the dbadmin to control what
locations users are allowed to create databases in, but an explicit
system table of allowed locations would be a lot better...

            regards, tom lane

Re: triggers and functions in pgsql 7.0.2

From

JanWieck@t-online.de (Jan Wieck)

Date:

13 June 2000, 21:31:55

Philip Hallstrom wrote:
> I had this same problem... plpgsql is not built be default (at least for
> 7.0).  take a look at src/pl/plpgsql/src/INSTALL.  You just need to add
> it... then switch your language back to plpgsql and it should work
> (well, maybe it won't, but it shouldn't complain about not finding
> plpgsql)
> good luck!
> -philip
> In article <am.pgsql.general.960922068.691@illiad.adhesivemedia.com>,
> Marc Britten  <mbritten@cybernet-usa.com> wrote:
> >I'm going through your docs trying to create a function and a trigger that
> >calls said function, what I've gotten so far is
> >CREATE FUNCTION create_count_cache()
> >       RETURNS opaque AS
> >       '
> >   BEGIN;
> >   DELETE FROM SnipCount;
> >   INSERT INTO SnipCount SELECT LangID, COUNT(LangID) AS CNT FROM
> >snippet GROUP BY LangID;
> >   COMMIT;
> >       '
> >       LANGUAGE 'sql';

    Yepp, won't work.

        CREATE FUNCTION create_count_cache() RETURNS opaque AS '
        BEGIN
            DELETE FROM SnipCount;
            INSERT ...
            RETURN NEW;
        END;'
        LANGUAGE 'plpgsql';

    In PL/pgSQL, BEGIN isn't the begin of a separate transaction.
    It's a keyword for putting groups of statements into separate
    variable   visibility.   A   little  confusing,  but  so  far
    compatible to Oracle's PL/SQL.

    And BTW: This trigger  will  purge  out  and  repopulate  the
    entire  SnipCount  relation  for  each  single row touched in
    snippet. If that's what you want, it's OK.

    But  I  bet  you  want  to   make   it   smarter   and   only
    create/update/delete  snipcount  records  that are touched by
    the operation - don't you?

> >CREATE TRIGGER count_change
> >       AFTER DELETE OR INSERT ON snippet FOR EACH ROW
> >       EXECUTE PROCEDURE create_count_cache();
> >however LANGUAGE sql cannot return opaque and plpgsql is not a known lang
> >type.
> >it seems that all your docs are a bit out of date, can you give me some help
> >please?
> >marc britten
>


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Re: initlocation and createdb

From

Bruce Momjian

Date:

14 June 2000, 01:51:03

> Looking at the documentation, David is apparently following this one,
> which seems wrong...
>
>     http://www.postgresql.org/docs/postgres/app-initlocation.htm
>
> Seems like it should be updated to the following:
>
> $ export PGDATA2=/opt/postgres/data
> $ initlocation $PGDATA2
> $ createdb testdb -D $PGDATA2

I have fixed the docs source, but I don't think the docs have been
regenerated since the fix weeks ago.  The postmaster must have PGDATA2
defined in its environment.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@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