Issue with a control structure in pl/pgsql script loop

Question

I made a little pl/pgsql script to rename some sequences (prefixes adding) and set their schema to 'public'. However I don't understand why but my 'ELSE' instructions are executed only once in the loop, which is not logical because I have many rows whose value of 'nspname' is other than 'docuprocess' :

CREATE OR REPLACE FUNCTION move_schemas_to_public(target_schemas text[]) RETURNS integer AS $procedure$
DECLARE
    rec RECORD;
    sql text;
    newname text;
    nbreq integer := 0;
    tabsize integer := array_length(target_schemas, 1);
    i integer := 1;
    debug boolean := false;
BEGIN

    -- [...]

    FOR rec in
        select nspname, c.relname
        from pg_class c 
        inner join pg_namespace ns 
        on (c.relnamespace = ns.oid) 
        where c.relkind = 'S'
        and ns.nspname = any(target_schemas)
        order by 1, 2
    LOOP
        IF rec.nspname = 'docuprocess' THEN 
            newname := rec.relname;
        ELSE
            -- Why these instructions are executed only once : -----
            newname := rec.nspname||'_'||rec.relname;
            sql := 'ALTER SEQUENCE '||rec.nspname||'.'||rec.relname||' RENAME TO '||newname;
            RAISE NOTICE '%', sql;
            IF debug is not true THEN
                EXECUTE sql;
            END IF;
            nbreq := nbreq + 1;
            --------------------------------------------------------
        END IF;

        sql := 'ALTER SEQUENCE '||rec.nspname||'.'||newname||' SET SCHEMA public';
        RAISE NOTICE '%', sql;
        IF debug is not true THEN
            EXECUTE sql;
        END IF;
        nbreq := nbreq + 1;

    END LOOP;

    -- [...]

    RETURN nbreq;
END;

select move_schemas_to_public(
    -- schemas list
    ARRAY[
        'docufacture',
        'docuprocess',
        'formulaire',
        'notification'
    ]
);

Here is the result for the loop's SQL query :

        [nspname];[relname]

    "docufacture";"exportdoc_idexportdoc_seq"  
    "docufacture";"tableau_idcolonne_seq" 
    "docuprocess";"dp_action_champsdocuged_seq" 
    "docuprocess";"dp_action_commentaire_seq" 
    "docuprocess";"dp_action_docuged_seq" 
    "docuprocess";"dp_action_email_id_seq" 
    "docuprocess";"dp_action_formulaire_seq" 
    "docuprocess";"dp_action_id_seq" 
    "docuprocess";"dp_action_imprimer_id_seq" 
    "docuprocess";"dp_action_lancer_processus_id_seq"
    "docuprocess";"dp_action_lancer_programme_id_seq" 
    "docuprocess";"dp_action_seq" 
    "docuprocess";"dp_action_transfert_fichier_id_seq" 
    "docuprocess";"dp_deroulement_etape_seq" 
    "docuprocess";"dp_deroulement_processus_seq" 
    "docuprocess";"dp_etape_seq" 
    "docuprocess";"dp_indisponibilite_seq" 
    "docuprocess";"dp_intervenant_seq" 
    "docuprocess";"dp_processus_seq" 
    "docuprocess";"dp_type_action_seq" 
    "formulaire";"champ_id_seq" 
    "formulaire";"fond_id_seq" 
    "formulaire";"formulaire_id_seq" 
    "formulaire";"modele_id_seq" 
    "notification";"notification_id_seq"

Thanks in advance for precious help.

Answer 1

I finally found the source of the problem! In the beginning of my function (masked part "[...]"), I have a loop which rename tables in schemas passed as parameters, and move these tables to schema 'public'. At this time, sequences owned by tables present in 'docufacture' and 'notification' schemas are automatically moved into public schema.

So, I just have to rename sequences for these schemas, not moving them. However I don't really understand why sequences of 'docuprocess' and 'formulaire' aren't moved in the same manner!

Indeed, if I try to execute the following request after tables shifting...

ALTER SEQUENCE docufacture.exportdoc_idexportdoc_seq RENAME TO docufacture_exportdoc_idexportdoc_seq

...I got this error :

ERROR:  relation "docufacture.exportdoc_idexportdoc_seq" does not exist

...because "exportdoc_idexportdoc_seq" has been moved to public schema.

And if I I try to execute the following request after tables shifting...

ALTER SEQUENCE exportdoc_idexportdoc_seq SET SCHEMA public;

...I got this error :

ERROR:  cannot move an owned sequence into another schema

If someone has some explanations about that, it will be really appreciated. Thanks a lot!

EDIT :

So, one solution is to proceed in 3 steps :

• Rename all sequences
• Move tables
• Move remaining sequences

Here is the code :

CREATE OR REPLACE FUNCTION move_schemas_to_public(target_schemas text[]) RETURNS integer AS $procedure$
DECLARE
    rec RECORD;
    sql text;
    newname text;
    nbreq integer := 0;
    tabsize integer := array_length(target_schemas, 1);
    i integer := 1;
    debug boolean := false;
BEGIN

    SET lc_messages TO 'en_US.UTF-8';   

    -- sequences renamming

    FOR rec in
        select ns.nspname, c.relname
        from pg_class c 
        inner join pg_namespace ns 
        on (c.relnamespace = ns.oid) 
        where c.relkind = 'S'
        and ns.nspname = any(target_schemas)
    LOOP
        IF rec.nspname != 'docuprocess' THEN
            newname := quote_ident(rec.nspname||'_'||rec.relname);
            sql := 'ALTER SEQUENCE '||quote_ident(rec.nspname)||'.'||quote_ident(rec.relname)||' RENAME TO '||newname;
            RAISE NOTICE '%', sql;
            IF debug is not true THEN
                EXECUTE sql;
            END IF;
            nbreq := nbreq + 1;
        END IF;
    END LOOP;

    -- END sequences


    -- tables

    FOR rec in
        SELECT table_schema, table_name
        from information_schema.tables
        where table_type = 'BASE TABLE'
        and table_schema = any(target_schemas)
    LOOP
        IF rec.table_schema = 'docuprocess' THEN
            newname := rec.table_name;
        ELSE
            newname := rec.table_schema||'_'||rec.table_name;
            sql := 'ALTER TABLE '||rec.table_schema||'.'||rec.table_name||' RENAME TO '||newname;
            RAISE NOTICE '%', sql;
            IF debug is not true THEN
                EXECUTE sql;
            END IF;
            nbreq := nbreq + 1;
        END IF;

        sql := 'ALTER TABLE '||rec.table_schema||'.'||newname||' SET SCHEMA public';
        RAISE NOTICE '%', sql;
        IF debug is not true THEN
            EXECUTE sql;
        END IF;
        nbreq := nbreq + 1;

    END LOOP;

    -- END tables


    -- remaining sequences shifting

    FOR rec in
        select ns.nspname, c.relname
        from pg_class c 
        inner join pg_namespace ns 
        on (c.relnamespace = ns.oid) 
        where c.relkind = 'S'
        and ns.nspname = any(target_schemas)
    LOOP
        sql := 'ALTER SEQUENCE '||quote_ident(rec.nspname)||'.'||quote_ident(rec.relname)||' SET SCHEMA public';
        RAISE NOTICE '%', sql;
        IF debug is not true THEN
            EXECUTE sql;
        END IF;
        nbreq := nbreq + 1;
    END LOOP;

    -- END sequences


    -- [...] Move functions, drop empty schemas


    RETURN nbreq;
END;

$procedure$ 
LANGUAGE plpgsql;

select move_schemas_to_public(
    -- schemas list
    ARRAY[
        'docufacture',
        'docuprocess',
        'formulaire',
        'notification'
    ]
);

To finish, I would like to address special thanks to "Erwin Brandstetter" for his advanced help and advices.

Answer 2

Naming collision?

I notice you did not table-qualify nspname in the SELECT list of your SQL statement:

select nspname, c.relname
from pg_class c 
inner join pg_namespace ns 
on (c.relnamespace = ns.oid) 
where c.relkind = 'S'
and ns.nspname = any(target_schemas)
order by 1, 2

While the one in the WHERE clause is table table-qualified.

You did not provide the function header, but if there is a variable or function parameter of the same name nspname it takes precedence. Then you would have the constant value of that variable in the result of your query, which would explain the observed behaviour.

It's a bad idea to allow such naming collisions in the first place. I make it a happit to prepend variables and parameters with _. Like _nspname.
But if you have such collisions, you need to be unambiguous in your SQL statements and always table-qualify ambiguous column names. Or, for simplicity, all column names.

select ns.nspname, c.relname
from pg_class c ...

Similar case:

• Postgres Function NULL value for row that references NEW

Privileges?

If that's not the problem, it might be a case of missing privileges. Per pg 8.4 documentation:

You must own the sequence to use ALTER SEQUENCE. To change a sequence's schema, you must also have CREATE privilege on the new schema.

There should be an error message! Check your db logs ...

Always sanitize identifiers

And you need to sanitize identifiers when used in dynamic SQL:

...

  newname := quote_ident(rec.relname);
    ELSE

...
        newname := quote_ident(rec.nspname||'_'||rec.relname);


sql := 'ALTER SEQUENCE ' || quote_ident(rec.nspname) || '.' || quote_ident(rec.relname)
    || ' RENAME TO ' || newname;

Etc. - in all instances. Else, your statements break if any of your identifiers are non-standard (mixed case, reserved words, space, ...). Even allows SQL injection. (!)
Careful not to apply quote_ident() before you build the new name.

Postgres 8.4 is somewhat limited in this regard. Version 9.1 introduces format(). More details here:

• Table name as a PostgreSQL function parameter

It may be time to start thinking about an upgrade to a current version.

Maximum length of identifiers

Finally, your identifiers are getting rather long. Bear in mind, the typical maximum length is 63 bytes:

• Maximum characters in labels (table names, columns etc)
• Postgres Database Error: relation does not exist

Answer 3

There is a space missing in this line after RENAME TO:

sql := 'ALTER SEQUENCE '||rec.nspname||'.'||rec.relname||' RENAME TO '||newname;

As a result, upon the first sequence that is not in schema docuprocess the sql statement is executed and raises an error which aborts the loop.

Note also that you do not have to ORDER BY the rec query because you are evaluating record properties in the loop and not using the ordering of the qualifying records.