Обсуждение: triggers and functions in pgsql 7.0.2
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
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
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
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
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
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
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
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
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
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 /
"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
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 #
> 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