Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VhQdv-00071b-Eb for pgsql-admin@arkaria.postgresql.org; Fri, 15 Nov 2013 21:09:31 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VhQdu-0008Vp-R7 for pgsql-admin@arkaria.postgresql.org; Fri, 15 Nov 2013 21:09:30 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VhQdt-0008VG-KU for pgsql-admin@postgresql.org; Fri, 15 Nov 2013 21:09:29 +0000 Received: from nm27.bullet.mail.ne1.yahoo.com ([98.138.90.90]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VhQdp-0006uN-O2 for pgsql-admin@postgresql.org; Fri, 15 Nov 2013 21:09:29 +0000 Received: from [98.138.100.111] by nm27.bullet.mail.ne1.yahoo.com with NNFMP; 15 Nov 2013 21:09:25 -0000 Received: from [98.138.87.10] by tm100.bullet.mail.ne1.yahoo.com with NNFMP; 15 Nov 2013 21:09:24 -0000 Received: from [127.0.0.1] by omp1010.mail.ne1.yahoo.com with NNFMP; 15 Nov 2013 21:09:24 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 801480.5391.bm@omp1010.mail.ne1.yahoo.com Received: (qmail 68880 invoked by uid 60001); 15 Nov 2013 21:09:24 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1384549764; bh=Pjh0uLpaF3UKcbtJuTas744jLRxhv9oH4AWAr61FgN8=; 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; b=upAoPqJk/3mMI/HkBH45H2SYulzbMNUiI8S684pa0AJOeUiS37VL4a/iiqzgpe10b4pLxpPfzD6+SPkCBM8DD+I7IEBRQeUlzmIEz0ZxE546NTEM4aadufFmiDvkQA2thNzsGnxpkE3FF0zoUp0rulQKoR3dl3Ml5aFoDzKqlPg= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.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; b=gvaAM1xhLq7E6gX2+4L3/Ww9OZ9gxVugRzM18fTEnn6g0szBuTf/9wj6HrXh3VVi5f4J2Eng4CSBQGgxvpslF4tPNJlS4G8vvxNvEu0NTljLCy3di4Obc8J32Y/FNsvcA3KhmwZwBDewhTsttE6TYbxqtwXt4MJ8jSHPtpFeJQI=; X-YMail-OSG: MwZYMZkVM1lXHc4pPt8sFssCpTxrdsfr1GY2Y7pdC5cnUoo 3gZS0lp8t.Y6NEt9fikkIUtncdhg38G8HSEgTfGjCO15QQgUaFBE_NN7V_TJ RMtCJgQDn7O1rvynFYCTChHH48hqlr2l_EPT1Nu7oyZt1ii.pwxKNu33vwWM VivcJqFvZv.P8X1h1z_jDAep8trTCnDgf8RZpBclpwHKcabRSpcXd4R7mrh_ t5EykIpRLmO7X4b1uCJnmKiPz41LFP8M.yhgTdM_jioE1WfYQqLZ9UWUf0oh M.LMnzaROz3nJ_8M4fH5Ly10Vtv37WnUgIP57extq7W4QjyUf66QUUJJFjiX D05vGaj97UlBHbcWGM6eW8eRD78kkG.otyJcffsc5.z5HPbK2X8AOgukY9.K etBOq0rNQg8SlvciHH_IJbrGertvvLUvL.Qhe_7iRGDmGOJJ7NXxSJ7ZPtSB 5vQWoWzgb6EidTceC2xEjHRECu6iO88wxEkYsoqs1ZeVv0tc.HPHaNBDH9UQ JntqK_.5yEV4ia_nSL7UWVQ427lqn68yHW2bFJ9qecQ15xy3Jl.VY49KHHh1 SIPV52tve6MfAyegTMeghjB0hPMkfY6jV1n.yo2YqtGknpuiczVfJyTzV7r7 OQhPtbfB7sUMIQ0Ur.AFio.ZMhsYoiZE77EyDudn8Kr8sE1ETzQY- Received: from [50.241.80.25] by web125005.mail.ne1.yahoo.com via HTTP; Fri, 15 Nov 2013 13:09:23 PST X-Rocket-MIMEInfo: 002.001, IkZpcnN0LCBpcyB0aGlzIGNvZGUgaW4gYSBwbHBnc3FsIGNvbnRleCI_CsKgCk5vLCB0aGF0IGlzIG15IHByb2JsZW0uCsKgCkRvZXMgaXQgaGF2ZSB0byBiZSBpbnNpZGUgYSBDcmVhdGUgZnVuY3Rpb24gYmxvY2sgb3IgY2FuIGp1c3R3cmFwcGluZyB0aGUgaWbCoCB3aXRoIGEgQkVHSU4gRU5EOyBzdWZmaWNlID8KwqAKVGhhbmtzLgpGcm9tOiBLZXZpbiBHcml0dG5lciA8a2dyaXR0bkB5bWFpbC5jb20.ClRvOiBUaGFyYSBWYWRha2tldmVlZHUgPHRoYXJhZ3ZAeWFob28uY29tPjsgInBnc3FsLWFkbWluQHABMAEBAQE- X-Mailer: YahooMailWebService/0.8.163.597 References: <1384543840.72953.YahooMailNeo@web125002.mail.ne1.yahoo.com> <1384544163.79352.YahooMailNeo@web125002.mail.ne1.yahoo.com> <1384545985.96990.YahooMailNeo@web162906.mail.bf1.yahoo.com> Message-ID: <1384549763.68859.YahooMailNeo@web125005.mail.ne1.yahoo.com> Date: Fri, 15 Nov 2013 13:09:23 -0800 (PST) From: Thara Vadakkeveedu Reply-To: Thara Vadakkeveedu Subject: Re: checking if sequence exists To: Kevin Grittner , "pgsql-admin@postgresql.org" In-Reply-To: <1384545985.96990.YahooMailNeo@web162906.mail.bf1.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="1988236031-1992284764-1384549763=:68859" X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --1988236031-1992284764-1384549763=:68859 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable "First, is this code in a plpgsql contex"?=0A=A0=0ANo, that is my problem.= =0A=A0=0ADoes it have to be inside a Create function block or can justwrapp= ing the if=A0 with a BEGIN END; suffice ?=0A=A0=0AThanks.=0AFrom: Kevin Gri= ttner =0ATo: Thara Vadakkeveedu ; "pg= sql-admin@postgresql.org" =0ASent: Friday, Nov= ember 15, 2013 3:06 PM=0ASubject: Re: [ADMIN] checking if sequence exists= =0A=0A=0AThara Vadakkeveedu wrote:=0A=0A> I tried this = from pg_admin, but I get a syntax error (unexpected=0A> character)=0A=0A> = =0A> IF EXISTS (SELECT 0 FROM pg_class=0A>=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0 WHERE relkind =3D 'S'=0A>=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 AND oid::regclass::text =3D 'public.' || quote_ident('hibernate_sequenc= e'))=0A>=A0=A0 THEN=0A>=A0=A0=A0=A0 RAISE EXCEPTION 'sequence public.% alre= ady exists!', 'hibernate_sequence=0A> ENF IF;=0A=0AFirst, is this code in a= plpgsql context (like a function=0Adefinition or a DO command)?=A0 If not,= that IF is not going to work.=0A=A0If it *is*, you seem to have a typo whe= re you meant END IF.=0A=0AAlso note that if you force the oid to text for t= he comparison, it=0Ais both more fragile and slower than if you convert the= text to=0Aregclass for the comparison.=0A=0Aexplain analyze=0ASELECT 0 FRO= M pg_class=0A=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 WHERE relkind =3D 'S'=0A= =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');=0A=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=0A-----------------------------------------= --------------------------------------------------------------=0A=A0Seq Sca= n on pg_class=A0 (cost=3D0.00..13.86 rows=3D1 width=3D0) (actual time=3D0.1= 97..0.197 rows=3D0 loops=3D1)=0A=A0=A0 Filter: ((relkind =3D 'S'::"char") A= ND (((oid)::regclass)::text =3D 'public.hibernate_sequence'::text))=0A=A0= =A0 Rows Removed by Filter: 295=0A=A0Total runtime: 0.221 ms=0A(4 rows)=0A= =0Aexplain analyze=0ASELECT 0 FROM pg_class=0A=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0 WHERE relkind =3D 'S'=0A=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 AND oid =3D ('public.' || quote_ident('hibernate_sequence'))::regclass;= =0A=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=0A--------------------= ---------------------------------------------------------------------------= ------------------------------=0A=A0Index Scan using pg_class_oid_index on = pg_class=A0 (cost=3D0.28..8.29 rows=3D1 width=3D0) (actual time=3D0.031..0.= 032 rows=3D1 loops=3D1)=0A=A0=A0 Index Cond: (oid =3D (('public.hibernate_s= equence'::text)::regclass)::oid)=0A=A0=A0 Filter: (relkind =3D 'S'::"char")= =0A=A0Total runtime: 0.062 ms=0A(4 rows)=0A=0ANotice that there was a table= scan, which was slower, and the=0Arecord was not found because the cast of= the oid to regclass and=0Athen text did not include the 'public." part.=A0= The other way used=0Aan index and matched as you probably intended.=0A=0A-= -=0AKevin Grittner=0AEDB: http://www.enterprisedb.com/=0AThe Enterprise Pos= tgreSQL Company=0A=0A=0A-- =0ASent via pgsql-admin mailing list (pgsql-admi= n@postgresql.org)=0ATo make changes to your subscription:=0Ahttp://www.post= gresql.org/mailpref/pgsql-admin --1988236031-1992284764-1384549763=:68859 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
"First= , is this code in a plpgsql contex"?
 
No, that is my problem.
 
Does it have to be i= nside a Create function block or can just wrapping the if  with a BEGIN END; suffice ?
 
Thanks.
From: Kevin Grittner <kgrittn@ymail.com>
<= SPAN style=3D"FONT-WEIGHT: bold">To: Thara Vadakkeveedu <thar= agv@yahoo.com>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.= org>
Sent: Friday, N= ovember 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
> = ;             W= HERE relkind =3D 'S'
>     &nbs= p;          AND oid::regclass:= :text =3D 'public.' || quote_ident('hibernate_sequence'))
&= gt;   THEN
>     RAISE EXC= EPTION '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 I= F is not going to work.
 If it *is*, you seem to have = a typo where you meant END IF.

Also note t= hat 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 ana= lyze
SELECT 0 FROM pg_class
  &nb= sp;          WHERE relkind =3D= 'S'
         =       AND oid::regclass::text =3D 'public.' || quo= te_ident('hibernate_sequence');
          &= nbsp;           &nbs= p;            &= nbsp;          QUERY PLAN = ;            &n= bsp;            = ;            &n= bsp;       
-----------------= ---------------------------------------------------------------------------= -----------
 Seq Scan on pg_class  (cost=3D0.00..= 13.86 rows=3D1 width=3D0) (actual time=3D0.197..0.197 rows=3D0 loops=3D1)   Filter: ((relkind =3D 'S'::"char") AND (((oid):= :regclass)::text =3D 'public.hibernate_sequence'::text))
&n= bsp;  Rows Removed by Filter: 295
 Total runtime: 0.221 ms
(4 rows)

explain analyze
SELECT 0 FROM pg_class
    &n= bsp;        WHERE relkind =3D 'S'
           = ;    AND oid =3D ('public.' || quote_ident('hibernate_sequen= ce'))::regclass;
       =             &nb= sp;            =             &nb= sp;            QUERY PLAN           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;       
----------------= ---------------------------------------------------------------------------= ----------------------------------
 Index Scan using p= g_class_oid_index on pg_class  (cost=3D0.28..8.29 rows=3D1 width=3D0) = (actual time=3D0.031..0.032 rows=3D1 loops=3D1)
  = ; Index Cond: (oid =3D (('public.hibernate_sequence'::text)::regclass)::oid= )
   Filter: (relkind =3D '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 intend= ed.

--
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 you= r subscription:
http://www.postgresql.org/mail= pref/pgsql-admin

--1988236031-1992284764-1384549763=:68859--