From: Kevin Grittner <kgrittn@ymail.com>
To: Thara Vadakkeveedu <tharagv@yahoo.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: checking if sequence exists
Date: Fri, 15 Nov 2013 12:06:25 -0800 (PST)
Message-ID: <1384545985.96990.YahooMailNeo@web162906.mail.bf1.yahoo.com> (raw)
In-Reply-To: <1384544163.79352.YahooMailNeo@web125002.mail.ne1.yahoo.com>
References: <CA+yMuNq+unyKgCrpfmmb2WK0=toMzqrx7491n=upM8Rrj3KtwQ@mail.gmail.com>
<CAKoxK+5DvJYF3tor6=kJKdfhpxoui6PAFjuhy9WzHjZbngGDPQ@mail.gmail.com>
<CA+yMuNqxZu+xZeGiD-fdC+QZK-x9+zg03X8xzDpBvQcVTncYxg@mail.gmail.com>
<1384543840.72953.YahooMailNeo@web125002.mail.ne1.yahoo.com>
<1384544163.79352.YahooMailNeo@web125002.mail.ne1.yahoo.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: kgrittn@ymail.com, tharagv@yahoo.com
Subject: Re: checking if sequence exists
In-Reply-To: <1384545985.96990.YahooMailNeo@web162906.mail.bf1.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox