Skip site navigation (1) Skip section navigation (2)

Transaction in the funtions

From: Vladimir Calmic <kalmik(at)optimum-web(dot)com>
To: PostgreSQL List - Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Transaction in the funtions
Date: 2008-02-03 11:08:00
Message-ID: 200802031308.00783.kalmik@optimum-web.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hello all !!

Can anyone help me please ?? I would like to get 
the execution of each the statement in the LOOP ( in the function 
change_owner() below) each in its own transaction.  

the construction like the folowing errors out  with 
ERROR:  syntax error at or near "LOOP"

LOOP
	BEGIN;
            PERFORM change_schema_owner( sch_rec.nspname );		
	COMMIT;
END LOOP;

getting same errors when tried to start the transaction into the function 
change_schema_owner()
tried something similar to 
BEGIN
	BEGIN;
       	EXECUTE 'ALTER SCHEMA ' ||sch_name || ' OWNER TO new_role' ;
	COMMIT;
RETURN ;
END;

called SET TRANSACTION ISOLATION LEVEL SERIALIZABLE before the calls .. 
but looks like all the LOOP calls are bieng done in one transaction since I am 
getting an error like : 

ERROR:  out of shared memory
HINT:  You may need to increase max_locks_per_transaction.
CONTEXT:  SQL statement "ALTER TABLE "test"."test_ages" OWNER TO "test""
PL/pgSQL function "change_schema_owner" line 24 at execute statement



-- functions I am using for that 

CREATE OR REPLACE FUNCTION owner_migration()
.. 
$BODY$
DECLARE
	sch_rec RECORD;
BEGIN
        FOR sch_rec IN SELECT * FROM pg_catalog.pg_namespace
        LEFT JOIN pg_catalog.pg_user  ON 		
	pg_user.usesysid=pg_namespace.nspowner
        WHERE pg_user.usename = 'test'
        LOOP
            PERFORM change_schema_owner( sch_rec.nspname );
        END LOOP;
RETURN ;
END;

CREATE OR REPLACE FUNCTION change_schema_owner(sch_name character varying)
...
... 
BEGIN
	-- here I would like to have a transaction and commit it on return 
       EXECUTE 'ALTER SCHEMA ' ||sch_name || ' OWNER TO new_role' ;
	{ statements to change owner for all the objects in the schema }

RETURN ;
END;



-----------------------------------
Vladimir Calmic
Systems Analyst
OPTIMUM-WEB
http://www.optimum-web.com
+373 22 571458
+373 79 573313 (mobile)

pgsql-novice by date

Next:From: Didier Gasser-MorlayDate: 2008-02-03 17:58:47
Subject: Re: Where clause...
Previous:From: Alex du PlessisDate: 2008-02-03 10:53:35
Subject: Trouble with libpq.dll

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group