Postgres 8.3.13
Hello,
I have a function to generate some tables with serial primary keys. (see below).
Due to our multithreading, I can't avoid concurerent calls, so I just catch the corresponding errors.
This works fine most of the time, but I sometimes get an error like in $subject.
The issue is not about the table, but about the related sequence.
By trying to create a sequence that already exists, I'd expect an error like
relation "foo_20110307_id_seq" already exists,
and not
type "foo_20110307_id_seq" already
I guess the latter matches the duplicate_object exception and I'll try to catch it as well, but I wonder where this comes from.
Cheers,
Marc Mamin
CREATE OR REPLACE FUNCTION public.createtable_foo(varchar,INT)
RETURNS int4 AS
$BODY$
BEGIN
EXECUTE 'CREATE TABLE '||$1||'.foo_'||$2||
'( id serial NOT NULL,
foo varchar,
CONSTRAINT foo_'||$2||'_pk PRIMARY KEY (id)
)
';
--handle concurent calls
EXCEPTION WHEN duplicate_table THEN null;
END;
RETURN 0;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;