Thread: org.postgresql.util.PSQLException: An I/O error occured while sending to the backend

 Hi,
~
 I am having a problem with PG I don't quite understand. I know the
server is running but I am getting exceptions as it is not. The
exception is thrown exactly after setting the parameters of a
preparedStatement, when I try to execute the query
~
    ResultSet RS = PSt.executeQuery();
~
org.postgresql.util.PSQLException: An I/O error occured while sending
to the backend.
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:218)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
        at JDBCPrepStSelWhr06.getRx(JDBCPrepStSelWhr06.java:223)
        at JDBCPrepStSelWhr06Test.main(JDBCPrepStSelWhr06Test.java:72)
Caused by: java.io.IOException: Stream closed
        at sun.nio.cs.StreamEncoder.ensureOpen(StreamEncoder.java:26)
        at sun.nio.cs.StreamEncoder.flush(StreamEncoder.java:121)
        at java.io.OutputStreamWriter.flush(OutputStreamWriter.java:212)
        at org.postgresql.core.PGStream.flush(PGStream.java:507)
        at org.postgresql.core.v3.QueryExecutorImpl.sendSync(QueryExecutorImpl.java:676)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
        ... 5 more
~
 I do know PG is running in my box:
~
sh-3.1# telnet localhost 5432
Trying 127.0.0.1...
Connected to Knoppix.
Escape character is '^]'.
~
pgtest@Knoppix:~/bin$ ./pgpostmaster.sh
pgtest@Knoppix:~/bin$ psql -l
       List of databases
   Name    | Owner  | Encoding
-----------+--------+-----------
 postgres  | pgtest | SQL_ASCII
 template0 | pgtest | SQL_ASCII
 template1 | pgtest | SQL_ASCII
 testdb    | pgtest | SQL_ASCII
(4 rows)

pgtest@Knoppix:~/bin$ select current_timestamp;
> ;
bash: syntax error near unexpected token `;'
pgtest@Knoppix:~/bin$ psql testdb;
Welcome to psql 8.3.1, 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

testdb=# select current_timestamp;
              now
-------------------------------
 2008-05-06 21:50:52.618359-04
(1 row)

testdb=# select * from testtbl;
 testid |  usr  |  pw  |  usrid  |      ctm      |     laxtm
--------+-------+------+---------+---------------+---------------
      1 | usr00 | pw00 | usrpw00 | 1068457566818 | 1068457579818
      2 | usr02 | pw02 | usrpw02 | 1068455566818 | 1068455566999
      3 | usr04 | pw04 | usrpw04 | 1068447566818 | 1068447566865
(3 rows)

testdb=#
~

 Also, in my logs I see mistakes I made (and fixed), so server is
alive and kicking
~
LOG:  database system was shut down at 2008-05-06 21:21:36 EDT
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections
FATAL:  database ":" does not exist
~
 lbrtchx

Albretch Mueller wrote:
>  I am having a problem with PG I don't quite understand. I know the
> server is running but I am getting exceptions as it is not. The
> exception is thrown exactly after setting the parameters of a
> preparedStatement, when I try to execute the query
> ~
>     ResultSet RS = PSt.executeQuery();
> ~
> org.postgresql.util.PSQLException: An I/O error occured while sending to the backend.
>         at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:218)

>  I do know PG is running in my box:

That sounds like your backend is dying or something...

I would set the following in postgresql.conf on the server and reload:

log_statement=all
log_min_error_statement=ERROR
log_min_messages=ERROR
log_connections=on
log_disconnections=on

Then look what shows up in the server log.

Yours,
Laurenz Albe

PS: Really "Albretch"? Not "Albrecht"?

OK this is what I got after making the changes you suggested to me:
~
// __
 First I opened a window and run a little script in order to become the pguser:
~
#!/bin/bash

_G='web'
_PGDIR='/media/sda2/inst/sw/PG/8.3.1'
_USR='pgtest'

# creating pg user
groupadd $_G
adduser --quiet --home $_PGDIR --ingroup $_G --no-create-home
--disabled-password --disabled-login --shell "/bin/bash"  $_USR
xhost local:$_USR # if you want to run x apps such as konqueror
su $_USR
~
sh-3.1# sh ./pgtest_user.sh
Changing the user information for pgtest
Enter the new value, or press ENTER for the default
        Full Name []:
        Room Number []:
        Work Phone []:
        Home Phone []:
        Other []:
Is the information correct? [y/N] y
non-network local connections being added to access control list
pgtest@Knoppix:/media/sda2/inst/sw/PG$
~
// __
 then I run the postmaster as pgtest user using another script and
tested that the table I set up was there (access was logged)
~
#!/bin/bash

_PGDATA='/media/sda2/inst/sw/PG/8.3.1/local/data'
_DATE=`date +%Y%m%d%H%M%S`
_LOGFL='/media/sda2/inst/sw/PG/8.3.1/local/logs/'$_DATE'_pg8.3.1.log'

postmaster -D $_PGDATA > $_LOGFL 2>&1 &
~
pgtest@Knoppix:~/bin$ sh ./pgpostmaster.sh
pgtest@Knoppix:~/bin$ ICE default IO error handler doing an exit(),
pid = 3911, errno = 0
pgtest@Knoppix:~/bin$ psql testdb
Welcome to psql 8.3.1, 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

testdb=# select current_timestamp;
              now
-------------------------------
 2008-05-07 05:19:47.818483-04
(1 row)

testdb=# select * from testtbl;
 testid |  usr  |  pw  |  usrid  |      ctm      |     laxtm
--------+-------+------+---------+---------------+---------------
      1 | usr00 | pw00 | usrpw00 | 1068457566818 | 1068457579818
      2 | usr02 | pw02 | usrpw02 | 1068455566818 | 1068455566999
      3 | usr04 | pw04 | usrpw04 | 1068447566818 | 1068447566865
(3 rows)

testdb=#
~
// __
 But then when I try my java code I get exactly the same error and
this is what gets logged
~
LOG:  database system was shut down at 2008-05-06 23:51:37 EDT
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections
LOG:  connection received: host=[local]
LOG:  connection authorized: user=pgtest database=testdb
LOG:  statement: select current_timestamp;
LOG:  statement: select * from testtbl;
LOG:  connection received: host=127.0.0.1 port=35036
LOG:  connection authorized: user=pgtest database=testdb
LOG:  execute <unnamed>: SELECT

n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description,t.typbasetype,t.typtype
 FROM pg_catalog.pg_namespace n  JOIN pg_catalog.pg_class c ON
(c.relnamespace = n.oid)  JOIN pg_catalog.pg_attribute a ON
(a.attrelid=c.oid)  JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid)
LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND
a.attnum = def.adnum)  LEFT JOIN pg_catalog.pg_description dsc ON
(c.oid=dsc.objoid AND a.attnum = dsc.objsubid)  LEFT JOIN
pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND
dc.relname='pg_class')  LEFT JOIN pg_catalog.pg_namespace dn ON
(dc.relnamespace=dn.oid AND dn.nspname='pg_catalog')  WHERE a.attnum >
0 AND NOT a.attisdropped  AND c.relname LIKE 'testtbl'  ORDER BY
nspname,relname,attnum
LOG:  disconnection: session time: 0:00:00.289 user=pgtest
database=testdb host=127.0.0.1 port=35036
~
 Something I don't know about is this error:
~
pgtest@Knoppix:~/bin$ ICE default IO error handler doing an exit(),
pid = 3911, errno = 0
~
 but then I accessed my testdb.testtbl without any problems
~
 What exactly am I stumbling on? I wonder if knoppix is creating some problems
~
 How can I fix this problem?
~
 lbrtchx
~
 PS: Well, yes it should be Albrecht (like Duerer's and Gisselle's)
AlbreTch I mangled (lbrtchx) as a way to easily tell myself apart an
an online persona

Albretch Mueller wrote:
> adduser [...]

Why you create an operating system user is beyond me, but that
doesn't matter.

You start the database server as user "postgres", and you don't have
to be a specific OS user to use the client.
Maybe you are confusing database users and OS users.

However, that's not important for the problem at hand.

>  But then when I try my java code I get exactly the same error and
> this is what gets logged
> ~
> LOG:  database system was shut down at 2008-05-06 23:51:37 EDT
> LOG:  autovacuum launcher started
> LOG:  database system is ready to accept connections
> LOG:  connection received: host=[local]
> LOG:  connection authorized: user=pgtest database=testdb
> LOG:  statement: select current_timestamp;
> LOG:  statement: select * from testtbl;
> LOG:  connection received: host=127.0.0.1 port=35036
> LOG:  connection authorized: user=pgtest database=testdb
> LOG:  execute <unnamed>: SELECT
>
n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description,t.typbasetype,
t.typtype
>  FROM pg_catalog.pg_namespace n  JOIN pg_catalog.pg_class c ON
> (c.relnamespace = n.oid)  JOIN pg_catalog.pg_attribute a ON
> (a.attrelid=c.oid)  JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid)
> LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND
> a.attnum = def.adnum)  LEFT JOIN pg_catalog.pg_description dsc ON
> (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)  LEFT JOIN
> pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND
> dc.relname='pg_class')  LEFT JOIN pg_catalog.pg_namespace dn ON
> (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog')  WHERE a.attnum >
> 0 AND NOT a.attisdropped  AND c.relname LIKE 'testtbl'  ORDER BY
> nspname,relname,attnum
> LOG:  disconnection: session time: 0:00:00.289 user=pgtest
> database=testdb host=127.0.0.1 port=35036

I think the case is clear.

You close the database connection before you issue your statement!

Two things speak in favor of that:
- There is a disconnection message in your log.
- I can reproduce the error message by close()ing the connection before
  execute()ing a statement.

That one statement that gets logged retrieves details about the table
definition of "testtbl".
Is it your JDBC code that issues that statement? If not, what is it?
Is there additional software involved?

Examine the code for a Connection.close() that takes place before the
Statement.execute().

Yours,
Laurenz Albe