triggers and functions in pgsql 7.0.2

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

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 #

Thank you. This works, and is close to what I need, but if I don't launch
the postmaster from a window with PGDATA2 defined, such as is done with
"/etc/init.d/postgresql start" or on a system boot, then it doesn't work. I
tried to export PGDATA2 within /etc/init.d/postgresql, but that didn't work
for me.

Can I make this work from within /etc/init.d/postgresql, or do have to start
the postmaster from a shell with the PGDATA2 already defined?

Thanks again.

-----Original Message-----
From: Jeff Hoffmann [mailto:jeff@propertykey.com]
Sent: Tuesday, June 13, 2000 5:29 PM
To: Barnes
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] initlocation and createdb

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

restart the postmaster & it should work fine. the environment variable
has to be defined before the postmaster is started in order for the
alternet locations to work. this has been covered a number of times
recently. wasn't it also added to the FAQ or some other documentation
not too long ago?

--

Jeff Hoffmann
PropertyKey.com

"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

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

Barnes wrote:

Thank you. This works, and is close to what I need, but if I don't launch
the postmaster from a window with PGDATA2 defined, such as is done with
"/etc/init.d/postgresql start" or on a system boot, then it doesn't work. I
tried to export PGDATA2 within /etc/init.d/postgresql, but that didn't work
for me.

Can I make this work from within /etc/init.d/postgresql, or do have to start
the postmaster from a shell with the PGDATA2 already defined?

Thanks again.

i'm not 100% sure that this works in all environments, but if your
startup script does a "su -l <username> -c ...", that's likely your
problem. on the systems that i'm familiar with, the -l flag on su will
reset the environment & read the shell startup files (e.g., .profile).
so if you set the variable earlier in the script, it will be cleared out
before the program is actually run. the .profile should be read when
the su is invoked & then variable should be available to you then.

hope this helps,

jeff