Re: checking if sequence exists - Mailing list pgsql-admin

From Thara Vadakkeveedu
Subject Re: checking if sequence exists
Date
Msg-id 1384549763.68859.YahooMailNeo@web125005.mail.ne1.yahoo.com
Whole thread Raw
In response to Re: checking if sequence exists  (Kevin Grittner <kgrittn@ymail.com>)
Responses Re: checking if sequence exists
List pgsql-admin
"First, is this code in a plpgsql contex"?
 
No, that is my problem.
 
Does it have to be inside a Create function block or can just wrapping the if  with a BEGIN END; suffice ?
 
Thanks.
From: Kevin Grittner <kgrittn@ymail.com>
To: Thara Vadakkeveedu <tharagv@yahoo.com>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Friday, November 15, 2013 3:06 PM
Subject: Re: [ADMIN] 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

pgsql-admin by date:

Previous
From: Kevin Grittner
Date:
Subject: Re: checking if sequence exists
Next
From: Elliot
Date:
Subject: Re: checking if sequence exists