Thread: createdb problem

createdb problem

From
Gary Stainburn
Date:
Hi folks,

I've got two totally seperate databases that I'm working on at the moment, the
only thing they have in common is the server that they're on (RH7.3+Errata)

For each of these databases I have a file containing the SQL commands to
create and populate the various tables, and bash script which simply calls

dropdb $dbname
createdb $dbname
psql  <$dbname.sql

However, for the last few days, this script seems to be alternating between
working and not working, the sequence being:

[gary@eddie gary]$ dbreset
ERROR:  DROP DATABASE: database "service" does not exist
dropdb: database removal failed
CREATE DATABASE
0 errors
[gary@eddie gary]$ dbreset
DROP DATABASE
ERROR:  CREATE DATABASE: source database "template1" is being accessed by
other users
createdb: database creation failed
0 errors
[gary@eddie gary]$ dbreset
ERROR:  DROP DATABASE: database "service" does not exist
dropdb: database removal failed
CREATE DATABASE
0 errors
[gary@eddie gary]$

Can anyone tell me why the createdb seems to fail if the preceeding dropdb
worked.
--
Gary Stainburn

This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000


Re: createdb problem

From
Gary Stainburn
Date:
On Tuesday 14 Oct 2003 4:32 pm, Gary Stainburn wrote:
> Hi folks,
>
> I've got two totally seperate databases that I'm working on at the moment,
> the only thing they have in common is the server that they're on
> (RH7.3+Errata)
>
> For each of these databases I have a file containing the SQL commands to
> create and populate the various tables, and bash script which simply calls
>
> dropdb $dbname
> createdb $dbname
> psql  <$dbname.sql
>
> However, for the last few days, this script seems to be alternating between
> working and not working, the sequence being:
>
[snip]

Me again,

I've found that if I add a 'sleep 1' between the dropdb and createdb, that the
script works every time.

Am I right in thinking that dropdb actually connects using the template1
database to enable it to work, and that the connection is not closing quick
enough when dropdb finishes, thus causing the problem?  If so:

1) why does this connection not fully drop before dropdb exits?
2) as createdb is only cloning template1, why does it need exclusive access?
3) other than the frig used above, how can this be fixed?

Also, I still don't know why this has just started happening, as nothing major
has happened to the server for a while, and the workload hasn't significantly
increased.

Gary

--
Gary Stainburn

This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000


Re: createdb problem

From
Tom Lane
Date:
Gary Stainburn <gary.stainburn@ringways.co.uk> writes:
> Am I right in thinking that dropdb actually connects using the template1
> database to enable it to work, and that the connection is not closing quick
> enough when dropdb finishes, thus causing the problem?

Yes.  I'd suggest issuing the DROP and CREATE commands in the same psql
session, instead of relying on dropdb's backend to terminate before
createdb's has run.  Because the backends are background processes in
the eyes of the kernel, they can easily not get scheduled when there is
anything else to do.

            regards, tom lane