Skip site navigation (1) Skip section navigation (2)

Re: Question when to use BEGIN / END

From: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>
To: Rikard Bosnjakovic <rikard(dot)bosnjakovic(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Question when to use BEGIN / END
Date: 2009-11-30 20:07:09
Message-ID: 1259611629.24380.19.camel@nycwxp2622 (view raw or flat)
Thread:
Lists: pgsql-novice
This is the wrong place for a view. Use "CREATE LOCAL TEMPORARY TABLE"
instead.

On Mon, 2009-11-30 at 10:32 +0100, Rikard Bosnjakovic wrote:

> I have a case where I want to forbid a race condition. Basically, I have this:
> 
> $q = "CREATE VIEW foo AS ( SELECT * FROM foo WHERE [...different
> conditions every call ...]  )";
> @pg_query($db, $q);
> $q = "SELECT * FROM foo";
> $res = pg_query($db, $q);
> $row = pg_fetch_object($res);
> ... change table foo...
> $q = "DROP VIEW foo";
> @pg_query($db, $q);
> 
> It is of utmost importance that there is not another client doing the
> same thing while the above is running. When the DROP VIEW is done,
> another client can start its work but not until then.
> 
> I'm reading about BEGIN/END on
> http://www.postgresql.org/docs/7.4/interactive/sql-begin.html but I'm
> not sure this is the solution. What I'm wondering is this: If I
> prepend BEGIN on CREATE VIEW above, and append END after DROP VIEW,
> can I still access the table foo in between? Or is BEGIN/END only
> supposed to be used on queries that do INSERT or UPDATE, i.e. no
> data-reading?
> 
> 
> -- 
> - Rikard
> 



VMS
Mladen Gogala 
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com 




The Leader in Integrated Media
Intelligence Solutions




In response to

pgsql-novice by date

Next:From: MikeDate: 2009-12-02 11:12:47
Subject: Beginner Question: "Running Notes" or "Diary-like"
Previous:From: Josh KupershmidtDate: 2009-11-30 15:41:53
Subject: Re: Question when to use BEGIN / END

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group