Thread: 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('<sequencename>')
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 template1 > > This 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 19026
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
> 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 19026
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. -- 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 19073
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?
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 19073
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.
--
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