Re: In processing DDL, when does pg_catalog get updated? - Mailing list pgsql-general

"Ken Winter" <ken@sunward.org> writes:
> 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?

I added a few "raise notice" commands to your function, and got this:

NOTICE:  sub_idcol = invisible_id
NOTICE:  default_exp = nextval('public.e_mail_address_invisible_id_seq'::text)
NOTICE:  sequence_name = public.e_mail_address_invisible_id_seq
NOTICE:  not found
NOTICE:  sub_idcol = pop_id
NOTICE:  default_exp = nextval('pop_seq'::text)
NOTICE:  sequence_name = pop_seq
NOTICE:  found
ERROR:  relation "e_mail_address_invisible_id_seq" already exists
CONTEXT:  SQL statement "CREATE SEQUENCE public.e_mail_address_invisible_id_seq;"
PL/pgSQL function "gen_sequences" line 51 at execute statement

The problem seems to be that you're not accounting for a schema name
possibly appearing in nextval's argument.

            regards, tom lane