Re: checking if sequence exists

Thara Vadakkeveedu <tharagv@yahoo.com> wrote:

> I tried this from pg_admin, but I get a syntax error (unexpected
> character)

>
> IF EXISTS (SELECT 0 FROM pg_class
>              WHERE relkind = 'S'
>                AND oid::regclass::text = 'public.' || quote_ident('hibernate_sequence'))
>   THEN
>     RAISE EXCEPTION 'sequence public.% already exists!', 'hibernate_sequence
> ENF IF;

First, is this code in a plpgsql context (like a function
definition or a DO command)?  If not, that IF is not going to work.
 If it *is*, you seem to have a typo where you meant END IF.

Also note that if you force the oid to text for the comparison, it
is both more fragile and slower than if you convert the text to
regclass for the comparison.

explain analyze
SELECT 0 FROM pg_class
             WHERE relkind = 'S'
               AND oid::regclass::text = 'public.' || quote_ident('hibernate_sequence');
                                              QUERY PLAN                                              
-------------------------------------------------------------------------------------------------------
 Seq Scan on pg_class  (cost=0.00..13.86 rows=1 width=0) (actual time=0.197..0.197 rows=0 loops=1)
   Filter: ((relkind = 'S'::"char") AND (((oid)::regclass)::text = 'public.hibernate_sequence'::text))
   Rows Removed by Filter: 295
 Total runtime: 0.221 ms
(4 rows)

explain analyze
SELECT 0 FROM pg_class
             WHERE relkind = 'S'
               AND oid = ('public.' || quote_ident('hibernate_sequence'))::regclass;
                                                         QUERY PLAN                                                         
-----------------------------------------------------------------------------------------------------------------------------
 Index Scan using pg_class_oid_index on pg_class  (cost=0.28..8.29 rows=1 width=0) (actual time=0.031..0.032 rows=1 loops=1)
   Index Cond: (oid = (('public.hibernate_sequence'::text)::regclass)::oid)
   Filter: (relkind = 'S'::"char")
 Total runtime: 0.062 ms
(4 rows)

Notice that there was a table scan, which was slower, and the
record was not found because the cast of the oid to regclass and
then text did not include the 'public." part.  The other way used
an index and matched as you probably intended.

--
Kevin Grittner
EDB: http://www.enterprisedb.com/
The Enterprise PostgreSQL Company


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin