In processing DDL, when does pg_catalog get updated?

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

Responses

Browse pgsql-general by date

  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?