Обсуждение: Connection lost
Hello! I am using postgress 8.4. I am trying to save to my DB a Large Binary Object, in localhost, let's say 1.4MB. I read that LargeObjectAPI should be used.
I have a problem that sometimes i can store the file and some others i get an exception of
>>org.postgresql.util.PSQLException: An I/O error occured while sending to the backend.
>>java.net.SocketException: Socket closed
I try to create a connection for each object but the errors persist with big files. For smaller files like 13KB this example works great and several in simultaneous. Does anybody have this problem??? I debug the applcation and I am getting the socket close exception during obj.write (....) here is a bit of the code
mDbConnector.setAutoCommit(false);
// Get the Large Object Manager to perform operations with
LargeObjectManager lobj = ((org.postgresql.PGConnection)mDbConnector).getLargeObjectAPI();
// Create a new large object
int oid = lobj.create(LargeObjectManager.READ | LargeObjectManager.WRITE);
// Open the large object for writing
LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);
byte []data = cont.getData();
obj.write(data,0,data.length);
obj.close();
PreparedStatement statement = mDbConnector.prepareStatement("INSERT INTO publicitiescontent (IdPublicities,MimeType,FileName,Title,Data) VALUES (?,?,?,?,?)");
statement.setBigDecimal(1, new BigDecimal(publicityId));
statement.setString(2, cont.getMimeType());
statement.setString(3, cont.getFileName());
statement.setString(4, cont.getNombre());
statement.setInt(5, oid);
statement.execute();
statement.close();
mDbConnector.commit();
Thanks in advance,
Hernan
On Tue, May 18, 2010 at 1:18 PM, Hernan Danielan <hernandanielan@gmail.com> wrote: > Hello! I am using postgress 8.4. I am trying to save to my DB a Large Binary > Object, in localhost, let's say 1.4MB. I read that LargeObjectAPI should be > used. > I have a problem that sometimes i can store the file and some others i get > an exception of >>>org.postgresql.util.PSQLException: An I/O error occured while sending to >>> the backend. >>>java.net.SocketException: Socket closed Do the PostgreSQL logs include any useful information? -- Joshua Tolley / eggyknap End Point Corporation
Hi everyone, I have a column of type CIDR in a table that I am querying that contains the values of 10/8, 10.1/16,10.1.28/24, and 10.1.28.95. I am trying to return only the one record that's most specific compared to the IP address I'm currently on as this is being done in a CGI script. If there's no specific match for the IP of the originating workstation then it should return the /24 if it's there, then the /16 if it's there, etc. I have never worked with the CIDR type, and a novice when it comes to SQL query language, so I have no idea how to approach this. Thanks for any help. Scot Kreienkamp skreien@la-z-boy.com
On May 19, 2010, at 10:32 AM, Scot Kreienkamp wrote: > Hi everyone, > > I have a column of type CIDR in a table that I am querying that contains > the values of 10/8, 10.1/16,10.1.28/24, and 10.1.28.95. I am trying to > return only the one record that's most specific compared to the IP > address I'm currently on as this is being done in a CGI script. If > there's no specific match for the IP of the originating workstation then > it should return the /24 if it's there, then the /16 if it's there, etc. > I have never worked with the CIDR type, and a novice when it comes to > SQL query language, so I have no idea how to approach this. Something like this (untested): select foo from table where foo >>= '10.1.28.14' order by masklen(foo) desc limit 1; You likely want to look at http://pgfoundry.org/projects/ip4r/ as an alternative, if the table is likely to grow beyond a few dozen rows. It's usefully indexable for "contains" queries, unlike the native cidr type, Cheers, Steve
Yes, the most interesting thing is that the log says
unexpected EOF on client connection or could not send data to client: Broken pipe
But this shouldn't be happening according to the code i sent because i do NOT close the socket. Moreover the transaction only takes up a few seconds.
I read a person who had this problem and he fixed changing th cache size but I wasn't successful.
Any ideas?
On Tue, May 18, 2010 at 11:34 PM, Joshua Tolley <eggyknap@gmail.com> wrote:
On Tue, May 18, 2010 at 1:18 PM, Hernan DanielanDo the PostgreSQL logs include any useful information?
<hernandanielan@gmail.com> wrote:
> Hello! I am using postgress 8.4. I am trying to save to my DB a Large Binary
> Object, in localhost, let's say 1.4MB. I read that LargeObjectAPI should be
> used.
> I have a problem that sometimes i can store the file and some others i get
> an exception of
>>>org.postgresql.util.PSQLException: An I/O error occured while sending to
>>> the backend.
>>>java.net.SocketException: Socket closed
--
Joshua Tolley / eggyknap
End Point Corporation
On Wed, May 19, 2010 at 1:46 PM, Hernan Danielan <hernandanielan@gmail.com> wrote: > Yes, the most interesting thing is that the log says > unexpected EOF on client connection or could not send data to client: > Broken pipe That log mostly just says something disconnected uncleanly, but doesn't say PostgreSQL closed the connection on purpose. Any JDBC driver people reading this, with ideas? -- Joshua Tolley / eggyknap End Point Corporation
On Wed, May 19, 2010 at 8:57 PM, Joshua Tolley <eggyknap@gmail.com> wrote: > On Wed, May 19, 2010 at 1:46 PM, Hernan Danielan > <hernandanielan@gmail.com> wrote: >> Yes, the most interesting thing is that the log says >> unexpected EOF on client connection or could not send data to client: >> Broken pipe > > That log mostly just says something disconnected uncleanly, but > doesn't say PostgreSQL closed the connection on purpose. Any JDBC > driver people reading this, with ideas? This sounds like a networking problem to me. Are the any useful entries in the system logs of either machine? Can you routine scp / rsync large files without network errors between these two boxes? How are they connected?
Thanks Steve, that works nicely in the testing I've done so far. I'll keep in mind about the pgfoundry project. I don't see this growing overly large, but you never know. I didn't realize the CIDR type couldn't be indexed. Scot Kreienkamp skreien@la-z-boy.com -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Steve Atkins Sent: Wednesday, May 19, 2010 2:18 PM To: pgsql-general General Subject: Re: [GENERAL] CIDR data type query help On May 19, 2010, at 10:32 AM, Scot Kreienkamp wrote: > Hi everyone, > > I have a column of type CIDR in a table that I am querying that contains > the values of 10/8, 10.1/16,10.1.28/24, and 10.1.28.95. I am trying to > return only the one record that's most specific compared to the IP > address I'm currently on as this is being done in a CGI script. If > there's no specific match for the IP of the originating workstation then > it should return the /24 if it's there, then the /16 if it's there, etc. > I have never worked with the CIDR type, and a novice when it comes to > SQL query language, so I have no idea how to approach this. Something like this (untested): select foo from table where foo >>= '10.1.28.14' order by masklen(foo) desc limit 1; You likely want to look at http://pgfoundry.org/projects/ip4r/ as an alternative, if the table is likely to grow beyond a few dozen rows. It's usefully indexable for "contains" queries, unlike the native cidr type, Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
The server and the client are running in the same machine.That is why a network connection problem is almost imposible. I do not know if i am doing something wrong, my code is in my fist message. It is a extrange thing because with files less than 500KB works great. However with a file of 1.4MB it fails most of the time. Does the JDBC has a timeOut or something? does anybody knows another connector that i could use in order to test it?
I am really desperate because i have been with this problem for several days now.
Thank in advance,
Hernan
On Thu, May 20, 2010 at 12:15 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Wed, May 19, 2010 at 8:57 PM, Joshua Tolley <eggyknap@gmail.com> wrote:This sounds like a networking problem to me. Are the any useful
> On Wed, May 19, 2010 at 1:46 PM, Hernan Danielan
> <hernandanielan@gmail.com> wrote:
>> Yes, the most interesting thing is that the log says
>> unexpected EOF on client connection or could not send data to client:
>> Broken pipe
>
> That log mostly just says something disconnected uncleanly, but
> doesn't say PostgreSQL closed the connection on purpose. Any JDBC
> driver people reading this, with ideas?
entries in the system logs of either machine? Can you routine scp /
rsync large files without network errors between these two boxes? How
are they connected?
Joshua Tolley <eggyknap@gmail.com> writes:
> On Wed, May 19, 2010 at 1:46 PM, Hernan Danielan
> <hernandanielan@gmail.com> wrote:
>> Yes, the most interesting thing is that the log says
>> unexpected EOF on client connection or��could not send data to client:
>> Broken pipe
> That log mostly just says something disconnected uncleanly, but
> doesn't say PostgreSQL closed the connection on purpose.
Yeah, it looks like the client closed the connection, not the server.
> Any JDBC driver people reading this, with ideas?
You'd have better luck getting their attention if you asked on
pgsql-jdbc.
regards, tom lane
On Thu, May 20, 2010 at 7:49 AM, Hernan Danielan <hernandanielan@gmail.com> wrote:
The server and the client are running in the same machine.That is why a network connection problem is almost imposible. I do not know if i am doing something wrong, my code is in my fist message. It is a extrange thing because with files less than 500KB works great. However with a file of 1.4MB it fails most of the time. Does the JDBC has a timeOut or something? does anybody knows another connector that i could use in order to test it?I am really desperate because i have been with this problem for several days now.Thank in advance,Hernan
If you can duplicate the problem on demand, then I would try this:
1) Disable SSL (so that TCPDUMP capture files are readable).
2) I don't remember if TCPDUMP can sniff traffic on "lo" (loopback) adapater on Linux, but I'm pretty sure that you can't sniff loopback on Windows (wireshark) or Solaris (with either tcpdump or snoop, the kernel won't support it). If you are using a unix socket then configure your client to use an IP bound to a NIC on the server.
3) Sniff the traffic between client and server. If using TCPDUMP use the options "-s 0" (capture all bytes of the packet) and "-n" (don't do DNS lookups). If using "snoop" on Solaris the options are different.
4) Run your client using "strace" (Linux) or "truss" (BSD and Solaris) to record all syscalls. You probably want to use the "-o" option (strace) to log to a file (instead of STDOUT).
5) If you can connect to the backend and then pause, connect a "strace" or "truss" to the PID of the backend (use the "-p pid" argument).
6) Let it run until it crashes.
7) Compare the network and syscall traces with each other. The network trace should show you who hung up (who sent the TCP RST or TCP FIN) packet, and if it was preceded by a delay or not. The syscall trace will should you what the kernel though that your process(es) were doing. Your answer is hopefully in there somewhere. However, it is entirely possible that the client hung up on the server on its own volition and neither trace will show you why. :( But you might get lucky too.