Обсуждение: how to synchronize database operations?

Hi,

when I (re)create my db from within C using libpq I do the following:
I open "template1" and execute "drop database ..." and "create database ...".
This worked fine for some time. However, I encountered some synchronization
problem:

When recreating, which includes the drop command, I get immediately before the
create command the error "template1 is accessed by other users". Then, when
doing the same thing again, there is no db to drop, and the create call
succeeds.

It seems that the create command comes too early for the postmaster. Simply
adding a delay of some seconds would not be that good.

How can I synchronize that? Just trying to open template1 again and again?

A code snippet would help very much.

Thank you,
Markus

Markus Wagner <magnus@gmx.de> writes:
> Hi,
> when I (re)create my db from within C using libpq I do the following:
> I open "template1" and execute "drop database ..." and "create database ...".
> This worked fine for some time. However, I encountered some synchronization
> problem:

> When recreating, which includes the drop command, I get immediately before the
> create command the error "template1 is accessed by other users". Then, when
> doing the same thing again, there is no db to drop, and the create call
> succeeds.

My guess is that you are closing the connection to the backend that did
the DROP and opening a new one for the CREATE.  The trouble with this is
that it takes a few milliseconds for the old backend to clean up and go
away.  It's quite possible for the new backend to see the old one as
still active, and since it doesn't know what the other one might be
doing to template1, it disallows the CREATE.

If you issue the DROP and the CREATE in the same backend process,
I suspect your problem will go away.

> How can I synchronize that? Just trying to open template1 again and again?

That would probably make it worse ...

            regards, tom lane

Hi Tom,

I believe that you are right. But to keep my things simple, I would like to
not extend my application specific database interface, which knows create and
delete, with a new function, which could be called "recreate", just to face
this synchronization problem. Of course, I would do that, if no other
solution exists. But I am thinking of the following: What about making my
application specific functions create/delete more robust, by adding the
following (pseudo-) code to each of them?

...
PQfinish();

while (postmaster_still_active())
 think_about_beautiful_things();
...

Well, the only thing I need to get happy would be the implementation of
postmaster_still_active (for think_about_beautiful_things i already have a
great idea!).

Greetings,
Markus



> My guess is that you are closing the connection to the backend that did
> the DROP and opening a new one for the CREATE.  The trouble with this is
> that it takes a few milliseconds for the old backend to clean up and go
> away.  It's quite possible for the new backend to see the old one as
> still active, and since it doesn't know what the other one might be
> doing to template1, it disallows the CREATE.
>
> If you issue the DROP and the CREATE in the same backend process,
> I suspect your problem will go away.

Markus Wagner <magnus@gmx.de> writes:
> Well, the only thing I need to get happy would be the implementation of
> postmaster_still_active

I'd just do sleep(1) and assume that the backend is gone by then.

If you are on the same machine as the backend then you could remember
the backend PID (libpq can tell you this before you close the
connection) and watch to see when that process exits.  But from a remote
machine I don't think you can really tell, and it's not worth trying
hard IMHO.

            regards, tom lane

Hi Tom!

Works!
Thanks!
PG rules!

Markus

> I'd just do sleep(1) and assume that the backend is gone by then.