PostgreSQL Discussion - In processing DDL, when does pg

Author

In processing DDL, when does pg_catalog get updated?

Ken Winter

2005-12-30, 3:23 am

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Tom Lane

2005-12-30, 3:23 am

"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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Ken Winter

2005-12-30, 11:23 am

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_t
ime 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_matte
rs BOOL null,
use_this_e_mail_for_
teaching_and_practic
e BOOL null,
use_this_e_mail_for_
personal_messages BOOL null,
omit_this_e_mail_fro
m_school_directory BOOL null,
comments VARCHAR(4000) null,
constraint PK_e_mail_address_pr
iority_pk primary key (pop_id,
effective_date_and_t
ime, 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_m
ail_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_mai
l_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_tabl
e.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& #91;array_upper(cre_
seq_arr, 1) + 1] := ''CREATE
SEQUENCE '' || sequence_name || '';'' ;
END IF;
cre_seq_code := cre_seq_code ||
cre_seq_arr& #91;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_invis
ible_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_invis
ible_id_seq", and thereby
refrain from trying to create it again?

~ Thanks again
~ Ken

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Tom Lane

2005-12-30, 11:23 am

"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_invis
ible_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_invis
ible_id_seq'::text)
NOTICE: sequence_name = public. e_mail_address_invis
ible_id_seq
NOTICE: not found
NOTICE: sub_idcol = pop_id
NOTICE: default_exp = nextval('pop_seq'::t
ext)
NOTICE: sequence_name = pop_seq
NOTICE: found
ERROR: relation " e_mail_address_invis
ible_id_seq" already exists
CONTEXT: SQL statement "CREATE SEQUENCE public. e_mail_address_invis
ible_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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Ken Winter

2005-12-31, 1:23 pm

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/...-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:
> consults
> thereby
>
> 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_invis
ible_id_seq'::text)
> NOTICE: sequence_name = public. e_mail_address_invis
ible_id_seq
> NOTICE: not found
> NOTICE: sub_idcol = pop_id
> NOTICE: default_exp = nextval('pop_seq'::t
ext)
> NOTICE: sequence_name = pop_seq
> NOTICE: found
> ERROR: relation " e_mail_address_invis
ible_id_seq" already exists
> CONTEXT: SQL statement "CREATE SEQUENCE
> public. e_mail_address_invis
ible_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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Sponsored Links