Thread: ERROR: type "foo_20110307_id_seq" already exists (expected relation "foo_20110307_id_seq" already exists)

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;