DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=ymail.com;
 h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
 b=rkLaCRTN8z+KbbdC16U7k8GjOrupyuNGHSYDNDzPgNFF7l3P98Hxf5PwEc19FQQZEfvna17pSXUkY4aELgPI3viYWmcy45wkbl/NqDEUq/uAX7dBABULkKrKFtowyh0LwM+4zD1nH4TsEnVOV74XwVzP9xoUQGdvWbrSdGu7u3U=;
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>
Message-ID: <1384545985.96990.YahooMailNeo@web162906.mail.bf1.yahoo.com>
Date: Fri, 15 Nov 2013 12:06:25 -0800 (PST)
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: checking if sequence exists
To: Thara Vadakkeveedu <tharagv@yahoo.com>,
 "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
In-Reply-To: <1384544163.79352.YahooMailNeo@web125002.mail.ne1.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Thara Vadakkeveedu <tharagv@yahoo.com> wrote:

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

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

First, is this code in a plpgsql context (like a function
definition or a DO command)?=A0 If not, that IF is not going to work.
=A0If 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
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 WHERE relkind =3D 'S'
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 AND oid::regclass::text =3D 'pub=
lic.' || quote_ident('hibernate_sequence');
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 QUERY PLAN=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =A0
---------------------------------------------------------------------------=
----------------------------
=A0Seq Scan on pg_class=A0 (cost=3D0.00..13.86 rows=3D1 width=3D0) (actual =
time=3D0.197..0.197 rows=3D0 loops=3D1)
=A0=A0 Filter: ((relkind =3D 'S'::"char") AND (((oid)::regclass)::text =3D =
'public.hibernate_sequence'::text))
=A0=A0 Rows Removed by Filter: 295
=A0Total runtime: 0.221 ms
(4 rows)

explain analyze
SELECT 0 FROM pg_class
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 WHERE relkind =3D 'S'
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 AND oid =3D ('public.' || quote_=
ident('hibernate_sequence'))::regclass;
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0 QUERY PLAN=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =A0
---------------------------------------------------------------------------=
--------------------------------------------------
=A0Index Scan using pg_class_oid_index on pg_class=A0 (cost=3D0.28..8.29 ro=
ws=3D1 width=3D0) (actual time=3D0.031..0.032 rows=3D1 loops=3D1)
=A0=A0 Index Cond: (oid =3D (('public.hibernate_sequence'::text)::regclass)=
::oid)
=A0=A0 Filter: (relkind =3D 'S'::"char")
=A0Total 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.=A0 The other way used
an index and matched as you probably intended.

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


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