Thread: In processing DDL, when does pg_catalog get updated?
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
"Ken Winter" <ken@sunward.org> writes:
> Is it possible that the pg_catalog is not updated with the results of a DDL
> script until the whole script has executed?
No, the serial sequence should exist as soon as the CREATE TABLE is done
... unless you are doing something weird like wrapping the whole thing
in a transaction and expecting uncommitted transaction results to be
visible from another session. Could you show us a complete test case
instead of an extract?
regards, tom lane
Tom ~
Good idea. The grisly details are as follows.
****************************
Here is the DDL script (generated from PowerDesigner 10.1.0.1134):
/*==============================================================*/
/* DBMS name: PostgreSQL 7.3 */
/* Created on: 12/30/2005 11:08:02 AM */
/*==============================================================*/
SET search_path TO public;
/*==============================================================*/
/* Table: e_mail_address */
/*==============================================================*/
create table e_mail_address (
pop_id INT8 not null default
nextval('pop_seq'),
effective_date_and_time TIMESTAMP WITH TIME ZONE not null default
CURRENT_TIMESTAMP,
invisible_id BIGSERIAL not null,
e_mail_type VARCHAR(255) null,
expiration_date_and_time TIMESTAMP WITH TIME ZONE null default 'infinity',
user_name VARCHAR(255) not null,
domain_name VARCHAR(255) not null,
use_this_e_mail_for_administrative_matters BOOL null,
use_this_e_mail_for_teaching_and_practice BOOL null,
use_this_e_mail_for_personal_messages BOOL null,
omit_this_e_mail_from_school_directory BOOL null,
comments VARCHAR(4000) null,
constraint PK_e_mail_address_priority_pk primary key (pop_id,
effective_date_and_time, invisible_id),
constraint fk_contact___e_mail_address foreign key (pop_id)
references pop (pop_id)
on delete cascade on update cascade,
constraint fk_e_mail_type___e_mail_address foreign key (e_mail_type)
references e_mail_type (e_mail_type)
on delete cascade on update cascade
)
INHERITS (when_and_who)
WITH OIDS;
SELECT gen_sequences('e_mail_address', 'public');
****************************
Here is the function "gen_sequences" that is evoking the error:
CREATE OR REPLACE FUNCTION gen_sequences ( VARCHAR, VARCHAR ) RETURNS
VARCHAR AS
'
DECLARE
table_name ALIAS FOR $1;
schema_name ALIAS FOR $2;
this_table RECORD;
dummy RECORD;
sequence_name VARCHAR;
cre_seq_arr VARCHAR [] := ''{}'';
cre_seq_code VARCHAR := '''';
BEGIN
EXECUTE ''SET search_path TO '' || schema_name;
/* Create a CREATE SEQUENCE statement for the sequence
of each sequence-assigned column,
if the sequence doesnt exist already. */
FOR this_table IN
SELECT c.column_name AS sub_idcol,
c.column_default AS default_expr
FROM information_schema.columns c
WHERE c.table_name = table_name
AND c.table_schema = schema_name
AND c.column_default LIKE ''nextval%''
LOOP
sequence_name := split_part(this_table.default_expr, '''''''',
2);
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
IF array_upper(cre_seq_arr, 1) IS NULL THEN
cre_seq_arr[1] := ''CREATE SEQUENCE '' || sequence_name
|| '';'' ;
ELSE
cre_seq_arr[array_upper(cre_seq_arr, 1) + 1] := ''CREATE
SEQUENCE '' || sequence_name || '';'' ;
END IF;
cre_seq_code := cre_seq_code ||
cre_seq_arr[array_upper(cre_seq_arr, 1)] || ''
'';
END IF;
END LOOP;
/* Execute the CREATE SEQUENCE statements, if any. */
IF array_upper(cre_seq_arr, 1) IS NOT NULL THEN
FOR n IN 1..array_upper(cre_seq_arr, 1) LOOP
EXECUTE cre_seq_arr[n];
END LOOP;
END IF;
RETURN cre_seq_code;
END;
'
LANGUAGE plpgsql
;
****************************
And here is the error message from phpPgAdmin:
SQL error:
ERROR: relation "e_mail_address_invisible_id_seq" already exists
CONTEXT: PL/pgSQL function "gen_sequences" line 45 at execute statement
****************************
Line 45 is the line that contains the execute statement.
Those are the raw facts.
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?
~ Thanks again
~ Ken
"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
Tom ~ Right you are! I added some code to trim off the schema name, and it works fine. You also alerted me to the NOTICE facility. (I'm new to PostgreSQL, and have been learning it in "wade right in" mode rather than properly studying the whole environment.) I have a "PostgreSQL for Dummies" question about RAISE NOTICE: Where do I find its output? According to the documentation (http://www.postgresql.org/docs/7.4/static/plpgsql-errors-and-messages.htmlh ttp://www.postgresql.org/docs/7.4/static/plpgsql-errors-and-messages.html), the messages are either "reported to the client, written to the server log, or both". My database is on a web host (zettai.net), and I'm working on it via phpPgAdmin. I don't know where to find messages "reported to the client", and I don't know how to access the system log. Can anyone help? ~ Thanks! ~ Ken > -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Friday, December 30, 2005 12:10 PM > To: Ken Winter > Cc: 'PostgreSQL pg-general List' > Subject: Re: [GENERAL] In processing DDL, when does pg_catalog get > updated? > > "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