Re: Question when to use BEGIN / END

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