From: | "Ken Winter" <ken(at)sunward(dot)org> |
---|---|
To: | "'PostgreSQL pg-general List'" <pgsql-general(at)postgresql(dot)org> |
Subject: | In processing DDL, when does pg_catalog get updated? |
Date: | 2005-12-30 04:13:07 |
Message-ID: | 006c01c60cf7$576f3880$6603a8c0@kenxp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-12-30 04:39:46 | Re: In processing DDL, when does pg_catalog get updated? |
Previous Message | Bricklen Anderson | 2005-12-29 23:36:02 | Re: Simple Accumulating Number Loop? |