Bug #650: using a trigger like a sequence doesn't work
Wolfgang Messingschlager (w.mess@freenet.de) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
using a trigger like a sequence doesn't work
Long Description
The purpose of my trigger is to build the same as a sequence, but it is not necessary to use every time nextval('<sequence name>')
Sample Code
createlang plpgsql test
psql test
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
test=# drop table person ;
DROP
test=# create table person (person_id integer PRIMARY KEY,
test(# vorname VARCHAR(40), name VARCHAR(40) );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'person_pkey' for table 'person'
CREATE
test=# drop table person_id ;
DROP
test=# create table person_id ( person_id integer);
CREATE
test=# insert into person_id values (0);
INSERT 16701 1
test=# create or replace function before_insert_person_id () returns opaque
test-# as 'Begin
test'# update person_id set person_id = person_id + 1;
test'# select into NEW.person_id person_id from person_id;
test'# End;'
test-# LANGUAGE 'plpgsql';
CREATE
test=# create trigger person_id before insert on person for each row
test-# EXECUTE PROCEDURE before_insert_person_id ();
CREATE
test=# insert into person (vorname, name) values ('John', 'Miller');
NOTICE: Error occurred while executing PL/pgSQL function before_insert_person_id
NOTICE: at END of toplevel PL block
ERROR: control reaches end of trigger procedure without RETURN
test=#
No file was uploaded with this report
Hello,
I tryed postgresql7.3. I install databse on clean redhat with
--enable-recode --enable-multibyte --enable-nls. I have two questions.
1. parametr -E encoding of createdb haven't any efect.
createdb aopk -ELATIN2
CREATE
COMMENT
psql -l
...
aopk |stehule|SQL_ASCII
If I created db via SQLcmd CREATE DATABASE aopk WITH encoding = 'LATIN2',
then databese is created with encoding LATIN2, but if databese is with
encoding other than SQL_ASCII, than I can't use SET CLIENT_ENCODING =
'WINDOWS1250'. I get error ERROR: Conversion between windows1250 and
LATIN2 is not supported. When database has def. encoding SQL_ASCII, I can
use cmd SET CLIENT_ENCODING. Why?
2. I can't as owner of database create functions in plpgsql. Implicit
triggers are ok. With my function I get message ERROR: plpgsql: permission
denied. Again, why? What is a new, and what I forgot set.
Bye
Pavel
Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:
2. I can't as owner of database create functions in plpgsql. Implicit
triggers are ok. With my function I get message ERROR: plpgsql: permission
denied. Again, why? What is a new, and what I forgot set.
I wonder whether the createlang script shouldn't do a "GRANT ALL" on the
created language ... at least by default. Without that, we're going to
be seeing a lot of questions like this one...
regards, tom lane
Tom Lane writes:
I wonder whether the createlang script shouldn't do a "GRANT ALL" on the
created language ... at least by default. Without that, we're going to
be seeing a lot of questions like this one...
It runs GRANT USAGE. I suspect the language was created manually without
using createlang.
--
Peter Eisentraut peter_e@gmx.net
Hello
I'm sorry. I have not a true, I didn't use plain database. I started with
removing detabase, creating new template, but I loaded oldest database,
which I dumped before .
on 7.2.1 pg_dumpall > old
on 7.3 psql -f old template1
This steps created language with bad privilegies. On truely "virgin"
template1 language works fine.
Thank you
Pavel Stehule
Oh, so createlang is fine, it is CREATE LANGUAGE that doesn't have the
GRANT, but that makes sense. The problem is that 7.2.X pg_dump doesn't
add the GRANT to the load script because we didn't have such permissions
in 7.2.X.
Wonder how we are going to fix that?
---------------------------------------------------------------------------
Pavel Stehule wrote:
Hello
I'm sorry. I have not a true, I didn't use plain database. I started with
removing detabase, creating new template, but I loaded oldest database,
which I dumped before .on 7.2.1 pg_dumpall > old
on 7.3 psql -f old template1This steps created language with bad privilegies. On truely "virgin"
template1 language works fine.Thank you
Pavel Stehule---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Bruce Momjian | http://candle.pha.pa.us
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 19026Bruce Momjian <pgman@candle.pha.pa.us> writes:
Oh, so createlang is fine, it is CREATE LANGUAGE that doesn't have the
GRANT, but that makes sense. The problem is that 7.2.X pg_dump doesn't
add the GRANT to the load script because we didn't have such permissions
in 7.2.X.
Right. Not only CREATE LANGUAGE, but CREATE FUNCTION, and for that
matter CREATE DATABASE, are going to have some issues of this sort
for people loading old dumps.
Wonder how we are going to fix that?
I think we're gonna tell 'em to issue some manual GRANTs.
regards, tom lane
If I created db via SQLcmd CREATE DATABASE aopk WITH encoding = 'LATIN2',
then databese is created with encoding LATIN2, but if databese is with
encoding other than SQL_ASCII, than I can't use SET CLIENT_ENCODING =
'WINDOWS1250'. I get error ERROR: Conversion between windows1250 and
LATIN2 is not supported. When database has def. encoding SQL_ASCII, I can
use cmd SET CLIENT_ENCODING. Why?
That's because some of encoding conversions have not been implemented
yet. Please wait till the beta freeze.
--
Tatsuo Ishii
Should we provide a script that goes through the dump file and spits out
the GRANT's they are going to need to perform? They can them pipe that
into psql.
---------------------------------------------------------------------------
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Oh, so createlang is fine, it is CREATE LANGUAGE that doesn't have the
GRANT, but that makes sense. The problem is that 7.2.X pg_dump doesn't
add the GRANT to the load script because we didn't have such permissions
in 7.2.X.Right. Not only CREATE LANGUAGE, but CREATE FUNCTION, and for that
matter CREATE DATABASE, are going to have some issues of this sort
for people loading old dumps.Wonder how we are going to fix that?
I think we're gonna tell 'em to issue some manual GRANTs.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Bruce Momjian | http://candle.pha.pa.us
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 19026Bruce Momjian writes:
Oh, so createlang is fine, it is CREATE LANGUAGE that doesn't have the
GRANT, but that makes sense. The problem is that 7.2.X pg_dump doesn't
add the GRANT to the load script because we didn't have such permissions
in 7.2.X.Wonder how we are going to fix that?
They can use the 7.3 pg_dump, which will add the required GRANT
statements.
--
Peter Eisentraut peter_e@gmx.net
On Tue, 6 Aug 2002, Peter Eisentraut wrote:
Bruce Momjian writes:
Oh, so createlang is fine, it is CREATE LANGUAGE that doesn't have the
GRANT, but that makes sense. The problem is that 7.2.X pg_dump doesn't
add the GRANT to the load script because we didn't have such permissions
in 7.2.X.Wonder how we are going to fix that?
They can use the 7.3 pg_dump, which will add the required GRANT
statements.
For dumping old database I can't use 7.3 pg_dump. In this time I have not
instaled new version yet. And I can't start 7.3 becouse I have older
format od database files.
PS
Is this something we need to address for 7.3?
---------------------------------------------------------------------------
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Oh, so createlang is fine, it is CREATE LANGUAGE that doesn't have the
GRANT, but that makes sense. The problem is that 7.2.X pg_dump doesn't
add the GRANT to the load script because we didn't have such permissions
in 7.2.X.Right. Not only CREATE LANGUAGE, but CREATE FUNCTION, and for that
matter CREATE DATABASE, are going to have some issues of this sort
for people loading old dumps.Wonder how we are going to fix that?
I think we're gonna tell 'em to issue some manual GRANTs.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Bruce Momjian <pgman@candle.pha.pa.us> writes:
Is this something we need to address for 7.3?
Peter pointed out that he'd already hacked pg_dump to do the right
thing (or at least what is arguably the right thing) against older
databases. That covers LANGUAGE, FUNCTION, and other objects-within-
a-database cases. pg_dumpall doesn't yet do the right thing at the
database level, but I believe he's planning to take care of that.
A larger problem is that this only works if you use 7.3 pg_dump
to dump your older database. If you dump with your then-current
pg_dump, you'll be unhappy with the permissions that result after
reloading into your new 7.3 server.
I'm not sure there is anything we can do about this without making
life worse. But it's annoying. Any thoughts?
regards, tom lane
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Is this something we need to address for 7.3?
Peter pointed out that he'd already hacked pg_dump to do the right
thing (or at least what is arguably the right thing) against older
databases. That covers LANGUAGE, FUNCTION, and other objects-within-
a-database cases. pg_dumpall doesn't yet do the right thing at the
database level, but I believe he's planning to take care of that.A larger problem is that this only works if you use 7.3 pg_dump
to dump your older database. If you dump with your then-current
pg_dump, you'll be unhappy with the permissions that result after
reloading into your new 7.3 server.I'm not sure there is anything we can do about this without making
life worse. But it's annoying. Any thoughts?
Yep, it better be on our radar screen. Added to open items:
Fix db, function, language permissions on 7.2 database loads
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Tom Lane writes:
pg_dumpall doesn't yet do the right thing at the database level, but I
believe he's planning to take care of that.
I have never heard of database privileges, except that people occasionally
expect me to fix something about them. :-/
I'm not sure there is anything we can do about this without making
life worse. But it's annoying. Any thoughts?
There are a couple of simple recipes we can give to users, such as
UPDATE pg_language SET lanacl = '{"=U"}' WHERE lanpltrusted;
UPDATE pg_proc SET proacl = '{"=X"}' WHERE proacl IS NULL;
The trick is that you'd need to run the first line before creating any
functions. Blech.
--
Peter Eisentraut peter_e@gmx.net
Peter Eisentraut wrote:
Tom Lane writes:
pg_dumpall doesn't yet do the right thing at the database level, but I
believe he's planning to take care of that.I have never heard of database privileges, except that people occasionally
expect me to fix something about them. :-/I'm not sure there is anything we can do about this without making
life worse. But it's annoying. Any thoughts?There are a couple of simple recipes we can give to users, such as
UPDATE pg_language SET lanacl = '{"=U"}' WHERE lanpltrusted;
UPDATE pg_proc SET proacl = '{"=X"}' WHERE proacl IS NULL;The trick is that you'd need to run the first line before creating any
functions. Blech.
OK, I have removed this open item:
Fix db, function, language permissions on 7.2 database loads
and added this documentation item:
Document need to add permissions to loaded functions and
languages
Current open items page is at:
ftp://candle.pha.pa.us/pub/postgresql/open_items.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073