I'm running a DDL script that does the following (in this order):
1. Creates a table containing a BIGSERIAL primary key column declaration,
which apparently automatically creates a sequence to populate this column.
2. Runs a "gen_sequences" function that I wrote, which executes CREATE
SEQUENCE statements for all columns in the table that have defaults like
'nextval%' but that don't already have sequences. The part of the function
that checks that the sequence doesn't already exist consults the pg_catalog,
as follows:
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
<execute the CREATE SEQUENCE statement>
END IF;
This script aborts with a message like this:
ERROR: relation "my_table_id_seq" already exists
...which implies that the code above is not finding "my_table_id_seq" in the
catalog. I know that the code works OK in detecting sequences that
pre-existed the execution of this script. So the only explanation that I
can come up with is that, at step 2, the pg_catalog has not yet been updated
to reflect the results of step 1 - namely, that the new sequence has been
created.
Is it possible that the pg_catalog is not updated with the results of a DDL
script until the whole script has executed?
If this is so, is there any way to force the pg_catalog to be updated along
the way?
~ TIA
~ Ken
