Re: Connections not closing

I have a batch file that calls two separate sql scripts as follows:

ECHO ******| psql -h host_name -p 5432 db_name -f script1.sql user_name
ECHO ******| psql -h host_name -p 5432 db_name -f script2.sql user_name
EXIT

script1.sql contains something like this:

BEGIN;
TRUNCATE TABLE table2;
INSERT INTO table2 SELECT * FROM table1;
END;

script2.sql contains something like this:

BEGIN;
TRUNCATE TABLE table1;
INSERT INTO table1 (field1) VALUES (1);
INSERT INTO table1 (field1) VALUES (2);
INSERT INTO table1 (field1) VALUES (3);
END;
BEGIN;
INSERT INTO table1 (field1) VALUES (4);
INSERT INTO table1 (field1) VALUES (5);
INSERT INTO table1 (field1) VALUES (6);
END;

The problem is that this batch file seems to be generating two
connections to the database and closing only one of them. This can be
seen in the connection log:

2006-01-18 04:34:50 [unknown] LOG: connection received: host=client1
port=4982
2006-01-18 04:34:50 [unknown] LOG: connection received: host=client1
port=4983
2006-01-18 04:34:50 user_name LOG: connection authorized:
user=user_name database=db_name
2006-01-18 04:34:51 user_name LOG: disconnection: session time:
0:00:00.21 user=user_name database=db_name host=client1 port=4983

So two connections are generated immediately on two different ports.
Only the second connection on the second port gets closed. Eventually
this generates too many connections and it kills my database. Do I need
to explicitly close connections in the scripts somehow? I thought after
the psql call and file execution connection would be automatically
closed.

Is there a way that I can see current connections to the database?

I am running PostgreSQL 8.0.6 on Windows 2003.

Ok, I simplified the problem. I tried just running psql from the
command line, and I noticed that it opens two connection on two
different ports, and it closes only one.
For example I do the following in the command prompt:
---------------------------------------------------------------------------------------------
C:\> psql -h host_name -p 5432 -d db_name -U user_name
Password:
Welcome to psql 8.0.4, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

Warning: Console code page (437) differs from Windows code page (1252)
8-bit characters may not work correctly. See psql reference
page "Notes for Windows users" for details.

db_name=> \q

C:\>
---------------------------------------------------------------------------------------------

The corresponding entry in the log file is as follows:
---------------------------------------------------------------------------------------------
2006-01-19 09:50:29 [unknown] LOG: connection received: host=client1
port=3775

2006-01-19 09:50:31 [unknown] LOG: connection received: host=client1
port=3778

2006-01-19 09:50:31 test LOG: connection authorized: user=user_name
database=db_name

2006-01-19 09:51:00 test LOG: disconnection: session time: 0:00:29.29
user=user_name database=db_name host=client1 port=3778
---------------------------------------------------------------------------------------------

Is this normal behavior? Is the second port being closed as well just
not logged? Could this cause open connections to stick around and
eventually reach the limit and shut down the DB?

When I query pg_stat_activity it does show only one connection.

"Nik" <XLPizza(at)gmail(dot)com> writes:
> Ok, I simplified the problem. I tried just running psql from the
> command line, and I noticed that it opens two connection on two
> different ports, and it closes only one.
> For example I do the following in the command prompt:

> C:\> psql -h host_name -p 5432 -d db_name -U user_name
> Password:

> 2006-01-19 09:50:29 [unknown] LOG: connection received: host=client1
> port=3775

> 2006-01-19 09:50:31 [unknown] LOG: connection received: host=client1
> port=3778

> 2006-01-19 09:50:31 test LOG: connection authorized: user=user_name
> database=db_name

It tries to connect, gets told it needs a password (the log verbosity
level is not high enough to record the rejection), asks you for the
password, and connects again. I don't see anything funny here.

regards, tom lane