[BUGS] Bug #735: Server crash when using dynamic SQL and disabling recursion
Konstantin Katuev ([EMAIL PROTECTED]) reports a bug with a severity of 1
The lower the number the more severe it is.
Short Description
Server crash when using dynamic SQL and disabling recursion
Long Description
When trying to develop trigger on plpgsql, that will update the own relation,
i met server (backend) crash due to signal 11.
Have tryed this both on RedHat 7.0 with gcc 2.96 and
Solaris 8 on sparc with gcc 3.1
PostgreSQL 7.2.1 was compiled myself from sources.
Following is the example of sql script that crashes server if you
remove marked comments (this is not real code, just test one).
May be i am doing something wrong???
Sincerely,
Konstantin Katuev,
Vladivostok, RUSSIA
Sample Code
--CREATE FUNCTION "plpgsql_call_handler" () RETURNS opaque AS
'/usr/local/pgsql/lib/plpgsql.so', 'plpgsql_call_handler' LANGUAGE 'C';
--CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
--HANDLER "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL';
DROP TABLE test1;
DROP TABLE test2;
CREATE TABLE test1 ( cnam char(3), nm1 char(1), nm2 char(2) );
INSERT INTO test1 VALUES('NM1','+','-');
INSERT INTO test1 VALUES('NM2','-','+');
CREATE TABLE test2 ( numb int PRIMARY KEY, cname char(3), cval char(1) );
DROP VIEW test3;
CREATE VIEW test3 AS SELECT * FROM test2;
CREATE OR REPLACE FUNCTION trgfun() RETURNS opaque AS '
DECLARE
curs1 refcursor;
rec1 record;
rec2 record;
rpgClass record;
BEGIN
NEW.cval=''*'';
RAISE NOTICE ''Trigger called'';
-- disable triggers
-- DROP TRIGGER trg1 ON test2;
-- another way
SELECT INTO rpgClass * FROM pg_class WHERE relname=''test2'';
UPDATE pg_class SET reltriggers = 0 WHERE relname=''test2'';
RAISE NOTICE ''Reltriggers=%'',rpgClass.reltriggers;
-- Find previous value:
-- if here will be FROM test2 trigger will crash server immediately.
SELECT INTO rec1 * FROM test3 WHERE numbNEW.numb;
IF FOUND THEN
OPEN curs1 FOR EXECUTE ''select ''
|| rec1.cname
|| '' AS snm FROM test1 WHERE cnam=''
|| NEW.cname || ;
FETCH curs1 INTO rec2;
IF FOUND THEN
// I need to disable triggers in order to prevent them
// to work for this statement
UPDATE test2 SET cval=rec2.snm WHERE numb=rec1.numb;
END IF;
CLOSE curs1;
END IF;
*/
-- enable triggers
UPDATE pg_class SET reltriggers = (select count(*) from pg_trigger
where pg_class.oid = tgrelid)
WHERE relname=''test2'';
-- I know it is exactly 1
-- UPDATE pg_class SET reltriggers = rpgClass.reltriggers WHERE relname=''test2'';
-- another way: same results
--CREATE TRIGGER trg1 BEFORE UPDATE ON test2
--FOR EACH ROW EXECUTE PROCEDURE trgfun();
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
INSERT INTO test2(numb,cname) VALUES(1,'NM1');
INSERT INTO test2(numb,cname) VALUES(2,'NM1');
INSERT INTO test2(numb,cname) VALUES(3,'NM2');
INSERT INTO test2(numb,cname) VALUES(4,'NM2');
INSERT INTO test2(numb,cname) VALUES(5,'NM1');
CREATE TRIGGER trg1 BEFORE UPDATE ON test2
FOR EACH ROW EXECUTE PROCEDURE trgfun();
UPDATE test2 SET cname='NM1' WHERE numb=3;
UPDATE test2 SET cname='NM2' WHERE numb=3;
No file was uploaded with this report
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] Bug #735: Server crash when using dynamic SQL and disabling recursion
[EMAIL PROTECTED] writes: > Following is the example of sql script that crashes server if you > remove marked comments (this is not real code, just test one). > May be i am doing something wrong??? Changing the trigger set on a relation from within a trigger on that same relation is not a good plan. When you return from the trigger, the trigger-calling code is now holding a dangling pointer to no-longer-valid trigger data. Instant core dump. I'm not clear on why you feel you need to do that, but I'd suggest finding another way. Perhaps a rule instead of a trigger? Or redesign your table structure to make the problem go away in the first place? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] Bug #736: Old bug is back!! Drop database bug
Chester Chee ([EMAIL PROTECTED]) reports a bug with a severity of 2 The lower the number the more severe it is. Short Description Old bug is back!! Drop database bug Long Description PostgreSQL version 7.2.1 running on RedHat 7.3 with all the latest patches installed. Step to reproduce the problem: 0) create database junk; 1) create table more_junk ; 2) drop database junk; 3) create database junk; 4) I am expecting to see database junk do not have any tables, but if list the tables... all the previous tables are back with data in it!! Sample Code No file was uploaded with this report ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] ineffiency of pg_restore
Jie Liang <[EMAIL PROTECTED]> writes: > pg_restore give admin a lot of flexabilities, it can restore any object > from a single dump file. But if the table is very big, e.g. 2M records, > it will take a long time to restore because it use INSERTs. This is demonstrably not the case --- at least not by default. You get the same commands you would've gotten from pg_dump. Sure you didn't create the dump file with "-d" ? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] [ADMIN] ineffiency of pg_restore
Jie Liang <[EMAIL PROTECTED]> writes: > I use: > pg_dump -Fc -d urldb > urldb.out.020810 ^^ There you have it. Better read the pg_dump help output again. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Bug #736: Old bug is back!! Drop database bug
[EMAIL PROTECTED] wrote: > Chester Chee ([EMAIL PROTECTED]) reports a bug with a severity > of 2 The lower the number the more severe it is. > > Short Description Old bug is back!! Drop database bug > > Long Description PostgreSQL version 7.2.1 running on RedHat 7.3 with > all the latest patches installed. > > Step to reproduce the problem: 0) create database junk; 1) create > table more_junk ; 2) drop database junk; 3) create database junk; > 4) I am expecting to see database junk do not have any tables, but > if list the tables... all the previous tables are back with data in > it!! Try connecting to the template1 database, and then do \dt. Do you see the tables in question? Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
