Re: problem calling psql multiple times from a script ?

I am executing a script which contains multiple executions of psql.
The last execution of psql renames the database. It looks something
like the following

psql -f create_tables.sql db_name1

psql -f modify_tables.sql db_name1

psql -f add_indexes.sql db_name1

psql template1 <<XX_rename
ALTER DATABASE db_name1 RENAME TO db_name2;
XX_rename

I notice sometimes that the "ALTER DATABASE ... RENAME ..." statement
fails with an error that a user has the database (db_name1) open.

I am wondering if one of the previous executions of psql is doing some
"back room" work in the database while allowing the script to continue.
I am wondering if this "back room" work prevents the database from being
renamed. Which system table could I check to see if the database is open?

I have seen this type of "back room" behavior with our Informix
databases. Multiple calls to dbaccess followed by a database rename
caused the rename to fail in some cases. I had to add a loop with a
"sleep" followed by a check if the database was open.

We are using postgres Version 7.4.8.

TIA.

Paul Tilles

Paul Tilles <Paul(dot)Tilles(at)noaa(dot)gov> writes:
> I am wondering if one of the previous executions of psql is doing some
> "back room" work in the database while allowing the script to continue.

It takes finite time for the backend to quit, and psql doesn't wait
around for that to happen. I've noticed that on many systems it seems
that the kernel scheduler actively discriminates against an exiting
backend --- maybe it thinks it's a low-priority background process?
The amount of work needed to start a new psql and a new backend
vastly exceeds what it takes to quit (unless you've got lots of temp
tables to drop, or some such), and yet people report cases like this
pretty often.

We could fix it by making PQfinish() wait for the connection to drop,
but that cure could be worse than the disease; most apps would just
see this as useless delay.

In the meantime, a sleep(1) or some such before trying to drop a
recently-used database ought to be a usable workaround.

regards, tom lane

On 05/30/07 10:23, Tom Lane wrote:
> Paul Tilles <Paul(dot)Tilles(at)noaa(dot)gov> writes:
>> I am wondering if one of the previous executions of psql is doing some
>> "back room" work in the database while allowing the script to continue.
>
> It takes finite time for the backend to quit, and psql doesn't wait
> around for that to happen. I've noticed that on many systems it seems
> that the kernel scheduler actively discriminates against an exiting
> backend --- maybe it thinks it's a low-priority background process?
> The amount of work needed to start a new psql and a new backend
> vastly exceeds what it takes to quit (unless you've got lots of temp
> tables to drop, or some such), and yet people report cases like this
> pretty often.
>
> We could fix it by making PQfinish() wait for the connection to drop,
> but that cure could be worse than the disease; most apps would just
> see this as useless delay.
>
> In the meantime, a sleep(1) or some such before trying to drop a
> recently-used database ought to be a usable workaround.

The proprietary DB that I use at work implemented something similar
as a command-line qualifier, and I guarantee you that it's been
tremendously useful.

--
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!