Re: In processing DDL, when does pg_catalog get updated?

["Ken Winter" <ken@xxxxxxxxxxx>]

Tom ~

Good idea.  The grisly details are as follows.

****************************

Here is the DDL script (generated from PowerDesigner 10.1.0.1134):

/*==============================================================*/
/* DBMS name:      PostgreSQL 7.3                               */
/* Created on:     12/30/2005 11:08:02 AM                       */
/*==============================================================*/

SET search_path TO public;

/*==============================================================*/
/* Table: e_mail_address                                        */
/*==============================================================*/
create table e_mail_address (
pop_id               INT8                 not null default
nextval('pop_seq'),
effective_date_and_time TIMESTAMP WITH TIME ZONE not null default
CURRENT_TIMESTAMP,
invisible_id         BIGSERIAL            not null,
e_mail_type          VARCHAR(255)         null,
expiration_date_and_time TIMESTAMP WITH TIME ZONE null default 'infinity',
user_name            VARCHAR(255)         not null,
domain_name          VARCHAR(255)         not null,
use_this_e_mail_for_administrative_matters BOOL                 null,
use_this_e_mail_for_teaching_and_practice BOOL                 null,
use_this_e_mail_for_personal_messages BOOL                 null,
omit_this_e_mail_from_school_directory BOOL                 null,
comments             VARCHAR(4000)        null,
constraint PK_e_mail_address_priority_pk primary key (pop_id,
effective_date_and_time, invisible_id),
constraint fk_contact___e_mail_address foreign key (pop_id)
   references pop (pop_id)
on delete cascade on update cascade,
constraint fk_e_mail_type___e_mail_address foreign key (e_mail_type)
   references e_mail_type (e_mail_type)
on delete cascade on update cascade
)
INHERITS (when_and_who)
WITH OIDS;

SELECT gen_sequences('e_mail_address', 'public');

****************************

Here is the function "gen_sequences" that is evoking the error:

CREATE OR REPLACE FUNCTION gen_sequences ( VARCHAR, VARCHAR ) RETURNS
VARCHAR AS
    '
    DECLARE
        table_name ALIAS FOR $1; 
        schema_name ALIAS FOR $2;
        this_table RECORD;
        dummy RECORD;
        sequence_name VARCHAR;
        cre_seq_arr VARCHAR [] := ''{}'';
        cre_seq_code VARCHAR := '''';
    BEGIN
        EXECUTE ''SET search_path TO '' || schema_name;
        
        /* Create a CREATE SEQUENCE statement for the sequence
        of each sequence-assigned column, 
        if the sequence doesnt exist already. */
        FOR this_table IN 
            SELECT c.column_name AS sub_idcol, 
                c.column_default AS default_expr
            FROM information_schema.columns c
            WHERE c.table_name = table_name
                AND c.table_schema = schema_name
                AND c.column_default LIKE ''nextval%''
            LOOP
            sequence_name := split_part(this_table.default_expr, '''''''',
2);
            IF NOT EXISTS (SELECT 1 
                FROM pg_catalog.pg_class AS t, 
                    pg_catalog.pg_namespace AS s
                WHERE t.relname = sequence_name
                    AND s.nspname = schema_name
                    AND t.relnamespace = s.oid
                    AND t.relkind = ''S'') 
            THEN
                IF array_upper(cre_seq_arr, 1) IS NULL THEN
                    cre_seq_arr[1] := ''CREATE SEQUENCE '' || sequence_name
|| '';'' ;  
                ELSE      
                    cre_seq_arr[array_upper(cre_seq_arr, 1) + 1] := ''CREATE
SEQUENCE '' || sequence_name || '';'' ;        
                END IF;
                cre_seq_code := cre_seq_code ||
cre_seq_arr[array_upper(cre_seq_arr, 1)] || ''
                    ''; 
            END IF;
        END LOOP;
        
        /* Execute the CREATE SEQUENCE statements, if any. */
        IF array_upper(cre_seq_arr, 1) IS NOT NULL THEN
            FOR n IN 1..array_upper(cre_seq_arr, 1) LOOP
                EXECUTE cre_seq_arr[n];
            END LOOP;
        END IF;
        RETURN  cre_seq_code;
    END;
    '
    LANGUAGE plpgsql
;

****************************

And here is the error message from phpPgAdmin:

SQL error:

ERROR:  relation "e_mail_address_invisible_id_seq" already exists
CONTEXT:  PL/pgSQL function "gen_sequences" line 45 at execute statement

****************************

Line 45 is the line that contains the execute statement.

Those are the raw facts.  

My question is: Why didn't the chunk of "gen_sequences" code that consults
pg_catalog find a record of "e_mail_address_invisible_id_seq", and thereby
refrain from trying to create it again?

~ Thanks again
~ Ken