Обсуждение: Fw: Re: GROUP_CONCAT in PostgreSQL

----- Original Message -----
From: "Barry Lind" <blind@xythos.com>
To: <m.popp@db4free.net>
Sent: Thursday, January 05, 2006 8:37 PM
Subject: RE: Re: GROUP_CONCAT in PostgreSQL


Markus,

Please send this to the postgres jdbc mailing list.

--Barry

-----Original Message-----
From: m.popp@db4free.net [mailto:m.popp@db4free.net]
Sent: Thursday, January 05, 2006 12:37 AM
To: Barry Lind
Cc: chriskl@familyhealth.com.au
Subject: Fw: Re: GROUP_CONCAT in PostgreSQL

Hi Barry,

looks like I've found a bug in the PostgreSQL JDBC driver
(postgresql-8.0-314.jdbc3.jar and postgresql-8.1-404.jdbc3.jar).

I have installed the MySQL compatibility functions
(http://pgfoundry.org/projects/mysqlcompat/) in a schema named mysql.
Running the query (which you find below in my mail to Christopher
Kings-Lynne and in the attached source file) works fine with psql and
other client tools, but from a java application, I get the error
message "org.postgresql.util.PSQLException: Eingabe/Ausgabe-Fehler {0}
beim Senden an das Backend.".

I have attached the source file where the error occurs, on line 92,
maybe it helps you for debugging ;-).

Thank you!

Regards,
Markus Popp


Am 05.01.2006 um 02:34 Uhr schrieb Christopher Kings-Lynne:
> Hi Markus,
>
> I guess it must be some bug in the PostgreSQL JDBC driver or something
:(
>
> Ask them perhaps: http://jdbc.postgresql.org/
>
> Chris
>
>
> Markus Popp wrote:
> > Hi,
> >
> > thank you very much - that's great. I think it's good if there's an
amicable
> > relationship between PostgreSQL and MySQL and their Communities :-).
> >
> > There's a little problem that I have, not with the group_concat
function, as
> > it works perfectly well from psql and other client tools, but if I
run this
> > query in a Java program:
> >
> > SELECT a.constraint_name, a.table_name, a.constraint_type,
> > mysql.group_concat(b.column_name), c.table_name, c.column_name
> > FROM information_schema.table_constraints a INNER JOIN
> > information_schema.key_column_usage b
> > ON a.constraint_name = b.constraint_name LEFT JOIN
> > information_schema.constraint_column_usage c
> > ON a.constraint_name = c.constraint_name AND a.constraint_type =
'FOREIGN
> > KEY'
> > GROUP BY a.constraint_name, a.table_name, a.constraint_type,
c.table_name,
> > c.column_name
> > ORDER BY a.constraint_name, a.table_name
> >
> > ... I get the Exception:
> >
> > "org.postgresql.util.PSQLException: Eingabe/Ausgabe-Fehler {0} beim
Senden
> > an das Backend."
> >
> > which means something like "Input/Output-Error {0} sending (what?)
to the
> > backend".
> >
> > The connection seems to be OK, but this query fails (only in Java)
for some
> > reason that I can't find out why.
> >
> > Do you have an idea? Thank you!
> >
> > Cheers,
> > Markus

Markus Popp wrote:

> I have installed the MySQL compatibility functions
> (http://pgfoundry.org/projects/mysqlcompat/) in a schema named mysql.
> Running the query (which you find below in my mail to Christopher
> Kings-Lynne and in the attached source file) works fine with psql and
> other client tools, but from a java application, I get the error
> message "org.postgresql.util.PSQLException: Eingabe/Ausgabe-Fehler {0}
> beim Senden an das Backend.".

Can you run with debug logging on (append '&loglevel=2' to your jdbc
URL) and send me the output off-list?

-O

Markus Popp wrote:

> FE=> Terminate

> FE=> Parse(stmt=null,query="SELECT a.constraint_name, a.table_name,
> a.constraint_type, array_to_string( array( SELECT column_name::varchar FROM
> information_schema.key_column_usage WHERE constraint_name =
> a.constraint_name ORDER BY ordinal_position ), '_' ) as column_list,
> c.table_name, c.column_name FROM information_schema.table_constraints a
> INNER JOIN information_schema.key_column_usage b ON a.constraint_name =
> b.constraint_name LEFT JOIN information_schema.constraint_column_usage c ON
> a.constraint_name = c.constraint_name AND a.constraint_type = 'FOREIGN KEY'
> GROUP BY a.constraint_name, a.table_name, a.constraint_type, c.table_name,
> c.column_name ORDER BY a.table_name, a.constraint_name",oids={})

Ok, at a glance it looks like something is closing the connection before
you manage to actually execute the query, and the I/O error occurs
because the server is shutting down the connection in response to that
Terminate message when the real query arrives.

Can you check that your code does not call Connection.close() before the
query is run? I notice from your stacktrace that you are running queries
from an AWT event-dispatch thread, is it possible that there is another
thread that is using the Connection concurrently?

If that's not the problem, can you send me a selfcontained testcase that
shows the problem?

If you *are* calling Connection.close(), I'd be interested in exactly
what the timing is, since in theory the driver should realise the
connection is dead and tell you so rather than giving an obscure I/O error.

-O