In processing DDL, when does pg_catalog get updated?
От | Ken Winter |
---|---|
Тема | In processing DDL, when does pg_catalog get updated? |
Дата | |
Msg-id | 006c01c60cf7$576f3880$6603a8c0@kenxp обсуждение исходный текст |
Ответы |
Re: In processing DDL, when does pg_catalog get updated?
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: