Thread: date format problem
Hello all, I am using postgresql 7.1.3. I met a problem when i was loading data from a text file to the tables. The table has the following attributes: Tid, Pid, borrowdate, duedate, returndate all the borrowdate < returndate. And the content of the text file used is as: 8,4,27/8/2000,6/9/2000,28/8/2000 33,48,12/11/2000,22/11/2000,21/11/2000 ... But After loading, the records in the table is as: 8 4 2000-08-27 2000-06-09 2000-08-28 33 4 2000-12-11 2000-11-22 2000-11-21 ... Say, 27/8 could be recognized as 08-27, while 6/9 was recognized as 06-09 So, some of the records have borrowdate > returndate, which is very weird. Annie _________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.
"Yan Bai" <annie_job@hotmail.com> writes:
> I met a problem when i was loading data from a text file to the tables.
You need to set DateStyle to tell the system the format of your date
data before you load the file. Evidently the default (US style) is
not what you are expecting.
regards, tom lane
I am told that the backend to postgresql will automatically timeout and close a JDBC client connection. In, addition the client code in JDBC connection.isClosed() will wait up to 30 seconds (SUN Java 1.3.1) to report the connection being closed. I normally use Oracle and the server does not automatically close connections. Am I off base here and if not then: 1. Is there a flag to turn off timeouts or 2. Can someone point me in the direction of the code base to possibly never timeout connections on sockets.
"Peter V. Cooper" <pvcooper@gte.net> writes:
> I am told that the backend to postgresql will automatically
> timeout and close a JDBC client connection.
There is most certainly no such timeout in the backend. Who told
you that?
We do run TCP connections with SO_KEEPALIVE set, which will cause
the connection to be timed out if the far-end kernel stops responding
for a sufficiently long time (IIRC, the RFC-mandated timeout is on
the order of an hour). I doubt this would be likely to cause any
problems, though. Certainly a slow application would not cause a
failure at the TCP level.
regards, tom lane
I was told that by a reasonably proficient programmer who has UNIX kernel level experience as do I. I am not trying to toot mine or anyone's horn, merely trying to set the stage for a reasonably technical discussion. It seems you know exactly what I am talking about. I make the assertion that a servlet which has a connection open to the database at all times (a servlet/tomcat connection object) and is physically located on the same LAN as the database could easily have a Internet user connected remotely step away from his/her desk for an hour or more and then return to run a JDBC statement. This would cause the JDBC driver to attempt to use the connection object. In using this connection object I am told, and will verify personally, that the information on the closed connection may take 30 seconds to inform the JDBC client running in the middle tier that the connection is closed and that code needs to be run to reconnect this connection. In my oracle implementation I keep one to four connection objects open at all times for immediate service to the database knowing that I have auto-commit on and that (at least empirically under Oracle) the connection object will be valid and not broken prematurely due to SO_KEEPALIVE being set. If the JDBC client responded quickly to the invalid connection via the isClosed() method on the connection (something I need more statistics on) then I would not have posed this connection question. In addition, Oracle allows prepared statements and statements in general to be processed simultaneously and therefore I really only need one connection if auto-commit is on. I am sorry if I was long winded on this issue. I have a need to have a very scaleable middle tier and am willing to code around this issue with 1) a thread to run every 1/2 to 1 hour on each connection object to keep it alive or 2) open a connection every time (only scaleable by killing it with multiple middle tier machines/hardware). One final question: are you saying that I can reset the SO_KEEPALIVE to a longer value to keep my connection object vaild? If so could you point me in the proper source direction to set this value. I will start by searching for the KEEPALIVE code. TIA as I very much appreciate any info on this subject. I like postgresql very much and besides the sysdate issue from Oracle to postgresql I was done with the port within, including installation of the database and creation of my db objects, a couple of hours. This impressed me very much. I am sure I will find further things to address but am very pleased so far. At 11:55 AM 1/31/2002 -0500, Tom Lane wrote: >"Peter V. Cooper" <pvcooper@gte.net> writes: > > I am told that the backend to postgresql will automatically > > timeout and close a JDBC client connection. > >There is most certainly no such timeout in the backend. Who told >you that? > >We do run TCP connections with SO_KEEPALIVE set, which will cause >the connection to be timed out if the far-end kernel stops responding >for a sufficiently long time (IIRC, the RFC-mandated timeout is on >the order of an hour). I doubt this would be likely to cause any >problems, though. Certainly a slow application would not cause a >failure at the TCP level. > > regards, tom lane
"Peter V. Cooper" <pvcooper@gte.net> writes:
> I make the assertion that a servlet which has a connection open
> to the database at all times (a servlet/tomcat connection object)
> and is physically located on the same LAN as the database could
> easily have a Internet user connected remotely step away from
> his/her desk for an hour or more and then return to run a
> JDBC statement. This would cause the JDBC driver to attempt
> to use the connection object. In using this connection object
> I am told, and will verify personally, that the information on the
> closed connection may take 30 seconds to inform the JDBC
> client running in the middle tier that the connection is closed
> and that code needs to be run to reconnect this connection.
If true (which I doubt), the problem is *entirely* on the client side.
Neither the backend nor the TCP transport layer would have the
slightest difficulty with this scenario. The KEEPALIVE timeout I
mentioned would only come into play given an hour-long connectivity
failure of your LAN, or a system-level crash of your client machine,
neither of which are likely to result from a user taking a lunch break.
However, I'm not very familiar with Java and so I cannot dismiss the
possibility that some layer inside the JVM might take it upon itself
to close an open TCP connection after a period of inactivity.
If you are able to reproduce a problem of this sort then you need to be
looking inside the Java code. The backend is not causing it.
The pgsql-jdbc list might be a better place to ask if anyone knows of
such problems in a JDBC context. Not sure how many JDBC people read
pgsql-bugs.
regards, tom lane
>"Yan Bai" <annie_job@hotmail.com> writes: > > I met a problem when i was loading data from a text file to the tables. > >You need to set DateStyle to tell the system the format of your date >data before you load the file. Evidently the default (US style) is >not what you are expecting. Could you please tell me how to set DateStyle? or where can I get the instruction about it? Thanks, Annie _________________________________________________________________ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com
> Could you please tell me how to set DateStyle? or where can I get the > instruction about it? In the reference page docs on your machine or at: http://www.ca.postgresql.org/users-lounge/docs/7.1/reference/sql-set.html I'm not sure I agree with every nuance of the recommendations for SET DATESTYLE in that doc; in particular, the claim that DATESTYLE is really intended only to help with porting applications is a bit misleading imho. I'll check the wording for the upcoming release... - Thomas
At 12.57 31/01/02 -0500, you wrote:
>However, I'm not very familiar with Java and so I cannot dismiss the
>possibility that some layer inside the JVM might take it upon itself
>to close an open TCP connection after a period of inactivity.
I have a Connection Pool active 365/24/7 on a site I manage, and the
connections are opened, reused, but not closed, and I never had such a
problem; HTH.
>The pgsql-jdbc list might be a better place to ask if anyone knows of
>such problems in a JDBC context. Not sure how many JDBC people read
>pgsql-bugs.
Well, at least one ;)
Ciao,
Stefano
This is definitely not a problem with the server Tom Lane has told me and I have verified the with my sources. It seems to be in the JDBC driver. It only seems to happen when the JDBC client and the postgresql server are on seperate machines and possibly on different networks. I have further talked with the site where it happens and researched the isClosed() JDBC call. The isClosed() call according to my sources is only required to tell you if you have closed the connection not if the connection has been closed by a timeout. The isClosed() call in Oracle is non-functional about timeouts across a wan. I should have read the JDBC spec and not just the documentation. I will not make that same mistake again when it comes to JDBC. I have verified that the connections stay active when the JDBC client and the postgresql server are on the same machine. If your connections are across a LAN and not the same machine then that would be helpful as I will only have to research a problem with a wan and that might not be a problem at all for my configuration. I will start my research with two machines on the same LAN unless you can tell me that your configuration is across a LAN. TIA for any info. At 11:49 AM 2/1/2002 +0100, Stefano Reksten wrote: >At 12.57 31/01/02 -0500, you wrote: >>However, I'm not very familiar with Java and so I cannot dismiss the >>possibility that some layer inside the JVM might take it upon itself >>to close an open TCP connection after a period of inactivity. > >I have a Connection Pool active 365/24/7 on a site I manage, and the >connections are opened, reused, but not closed, and I never had such a >problem; HTH. > >>The pgsql-jdbc list might be a better place to ask if anyone knows of >>such problems in a JDBC context. Not sure how many JDBC people read >>pgsql-bugs. > >Well, at least one ;) > >Ciao, > Stefano > > >---------------------------(end of broadcast)--------------------------- >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org