Thread: Question when to use BEGIN / END

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

On Mon, Nov 30, 2009 at 4:32 AM, Rikard Bosnjakovic
<rikard.bosnjakovic@gmail.com> wrote:
> 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?

This depends. If you're worried about another client concurently
executing the same command *only* because you're worried about the
view names colliding you have nothing to worry about:  Postgres
supports what's called "Transactional DDL", see e.g:
http://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis

which means that you can run CREATE and DROP commands, with a few
exceptions (e.g. creating or dropping entire databases) inside a
transaction. Other concurrent clients won't see any of your changes
until after you've committed, and you won't see any of their changes
until after they've committed. *However*, if you really can not have
another client running the same commands at the same time for some
other reason, you'll have to resort to using some sort of lock --
perhaps run the CREATE VIEW inside its own transaction, and then bail
out entirely if the view can't be created because another client has
created it already. Or you can use advisory locks explicitly.

Having said this, I think there are likely more elegant ways to do
what you're trying to accomplish. For example, look at  "CREATE
TEMPORARY VIEW .... " , so you don't have to worry about dropping the
view after you're done, or colliding with an existing view of the same
name (assuming multiple clients doing the same work at the same time
is actually alright). Also, make sure you're looking at the most
up-to-date docs online -- your URL was to the 7.4 documentation which
hopefully you're not actually stuck with.

Josh

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

Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions