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

Passing on a q ... Alter schema

From: "Mike Ellsworth" <younicycle(at)gmail(dot)com>
To: "PostgreSQL List - Novice" <pgsql-novice(at)postgresql(dot)org>
Subject: Passing on a q ... Alter schema
Date: 2008-02-01 13:00:27
Message-ID: 219951fd0802010500q4085af48n4ede887cd20f5e44@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
Someone (a new subscriber) asked me to pass this question on.  Taking
awhile to get through Moderation.  I believe it's been several hours.

"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 following errors out with
ERROR: syntax error at or near "LOOP"

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

getting the same errors when I 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;"

Thanks for any help

pgsql-novice by date

Next:From: Robert M. BernabeDate: 2008-02-01 16:20:33
Subject: Re: Group Roles are now Login Roles in pgAdmin?
Previous:From: Robert BernabeDate: 2008-02-01 07:48:34
Subject: Re: Group Roles are now Login Roles in pgAdmin?

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