[BUGS] New to Postgresql - Backend timeout /JDBC
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. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] Dates and daylight saving time
I got the following problem in PostgreSQL 7.1.3. When I need to get next day relative to another one, I do the following query: SELECT date(date ? + interval '1 day'). But on '2001-10-28', I get this: test=> SELECT date(date '2001-10-28' + interval '1 day'); date 2001-10-28 (1 row) It might look VERY strange if we don't know that the date of '2001-10-28' is the date of switching from daylight saving time to winter time, and before the following query is executed: test=> SELECT timestamp(date '2001-10-28' + interval '1 day'); timestamp 2001-10-28 23:00:00+03 (1 row) Why the addition of '1 day' to some date (or timestamp) is equal to addition of exactly 24 hours (regardless of real length of this day), while the addition of '1 month' to some date varies depending on month's length? And why no separate '+' operator for 'date' and 'interval' types? How to handle such situation? -- Fduch M. Pravking ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] Dates and daylight saving time
try 'set timezone to ' replacing with your timezone before your query. template1=# set timezone to est; SET VARIABLE template1=# SELECT date(date '2001-10-28' + interval '1 day'); date 2001-10-29 (1 row) -Original Message- From: Fduch the Pravking [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 31, 2002 11:04 AM To: [EMAIL PROTECTED] Subject: [BUGS] Dates and daylight saving time I got the following problem in PostgreSQL 7.1.3. When I need to get next day relative to another one, I do the following query: SELECT date(date ? + interval '1 day'). But on '2001-10-28', I get this: test=> SELECT date(date '2001-10-28' + interval '1 day'); date 2001-10-28 (1 row) It might look VERY strange if we don't know that the date of '2001-10-28' is the date of switching from daylight saving time to winter time, and before the following query is executed: test=> SELECT timestamp(date '2001-10-28' + interval '1 day'); timestamp 2001-10-28 23:00:00+03 (1 row) Why the addition of '1 day' to some date (or timestamp) is equal to addition of exactly 24 hours (regardless of real length of this day), while the addition of '1 month' to some date varies depending on month's length? And why no separate '+' operator for 'date' and 'interval' types? How to handle such situation? -- Fduch M. Pravking ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] New to Postgresql - Backend timeout /JDBC
"Peter V. Cooper" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Dates and daylight saving time
On Thu, Jan 31, 2002 at 11:44:53AM -0500, Tom Pfau wrote: > try 'set timezone to ' replacing with your timezone > before your query. > > template1=# set timezone to est; > SET VARIABLE > template1=# SELECT date(date '2001-10-28' + interval '1 day'); > date > > 2001-10-29 > (1 row) Yes, it works! But now postgres accepts input and returns output in GMT, not local time like before! Is it a feature? -- Fduch M. Pravking ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] New to Postgresql - Backend timeout /JDBC
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" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] New to Postgresql - Backend timeout /JDBC
"Peter V. Cooper" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] Dates and daylight saving time
This was just discussed yesterday on pgsql-novice, see http://archives.postgresql.org/pgsql-novice/2002-01/msg00177.php http://archives.postgresql.org/pgsql-novice/2002-01/msg00178.php regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] Dates and daylight saving time
... > Yes, it works! > But now postgres accepts input and returns output > in GMT, not local time like before! Is it a feature? This strategy will not work in general unless you *do* set the time zone to GMT (if it works at one boundary, say in the fall, then it will fail at the other boundary in the spring). It is likely that you set the time zone to one unrecognized by your system (maybe a typo?), so it reverts to GMT. And with GMT you do not have to worry about daylight savings time or offsets between dates and times of day in different time zones. But that is a workaround for the fundamental problem that you want to solve, which is to get exact *qualitative* date calculations around DST boundaries. In the long run, we should probably implement some exact date/interval arithmetic instead of relying on timestamp types in the intermediate calculations. In the meantime you can set time zones or, if you have a fixed query with date in and date out, and intervals which are multiples of a day, then you can simply add 12 hours in the query to get the rounding you expect: cast((date '2001-10-28' + interval '1 day' + interval '12 hours') as date) - Thomas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Dates and daylight saving time
Thomas Lockhart <[EMAIL PROTECTED]> writes: > In the long run, we should probably implement some exact date/interval > arithmetic instead of relying on timestamp types in the intermediate > calculations. AFAIK type "date" solves his problem just fine, and there's no need to mess with timestamps and intervals at all. But in the long run it'd be nice to handle this sort of situation more intuitively. I have suggested more than once that type interval ought to have three components not two: months, days, and seconds. Just as a month is not a fixed number of days, a day is not a fixed number of seconds. Not sure if we can get away with that when SQL92 believes otherwise, however. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] date format problem
>"Yan Bai" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[BUGS] Sun Solaris 2.5.1 Seg Faults PostgreSQL7.1.3 build commands
Your name : Mike Riendeau
Your email address : [EMAIL PROTECTED]
System Configuration
-
Architecture (example: Intel Pentium) : Sun Sparc 20
Operating System (example: Linux 2.0.26 ELF) : Solaris 2.5.1
PostgreSQL version (example: PostgreSQL-7.1.3): PostgreSQL-7.1.3
Compiler used (example: gcc 2.95.2) : gcc 2.95.2
Please enter a FULL description of your problem:
I am having a problem with v7.1.3 PostgreSQL commands generating
Seg. Faults on exit.
* I have built v7.0.2 from the sources, on Sun Solaris 2.5.1
and have been running this version with success.
I run postgres as follows:
postmaster -B 16 -N 8 -p8000 -i
I need the -B and -N options to allow IpcMemshare to work.
* I am running the server in my own account, not a root installation,
* not under a postgres account.
Version 7.1.3 build and exec issues:
--
- Built with the exact same config options as v7.0.2
** Configure process ***
./configure --prefix=/home/mriendea --with-tcl
--with-tclconfig=/home/mriendea/lib --with-tkconfig=/home/mriendea/lib
--with-includes=/home/mriendea/include --with-pgport=8000 --with-odbc
Configure did not report any fatal errors.
CONFIG.LOG ##
.
.
configure: In function `main':
configure:6562: `rl_completion_append_character' undeclared (first use in
this function)
configure:6562: (Each undeclared identifier is reported only once
configure:6562: for each function it appears in.)
configure: failed program was:
#line 6553 "configure"
#include "confdefs.h"
#include
#ifdef HAVE_READLINE_READLINE_H
# include
#elif defined(HAVE_READLINE_H)
# include
#endif
int main() {
rl_completion_append_character = 'x';
; return 0; }
configure:6584: checking for rl_completion_matches
configure:6639: checking for finite
configure:6648: gcc -o conftest -g -I/home/mriendea/include conftest.c
-lz -lresolv -lge
n -lnsl -lsocket -ldl -lm -lreadline -ltermcap 1>&5
.
.
.
### CONFIG.LOG #
* build process ***
'gmake' reports:
GMAKE #
Various warnings also reported in v7.0.2 build.
...All of PostgreSQL successfully made. Ready to install.
*** results *
- postmaster seems to run OK with the same options as v7.0.2
I am starting it with the pg_ctl command with the
-o "-B16 -N8 -i" arg and env PGHOST, PGPORT and PGDATA.
- initdb functions with no problem. It created the database files.
- createdb manages to create a database, but Seg Faults on exit.
- psql is able to access the database created with createdb,
but Seg Faults on exit.
- regression tests don't get past postmaster for the latter
reasons.
** GNU debugger ( configged w/ --enable-debug)
bash-2.02{temp}$ gdb ./psql
GDB is free software and you are welcome to distribute copies of it
under certain conditions; type "show copying" to see the conditions.
There is absolutely no warranty for GDB; type "show warranty" for details.
GDB 4.16 (sparc-sun-solaris2.5.1), Copyright 1996 Free Software Foundation,
Inc...
(gdb)
(gdb)
(gdb)
(gdb)
(gdb)
(gdb)
(gdb) run foo
Starting program: /home/mriendea/temp/./psql foo
warning: Unable to find dynamic linker breakpoint function.
warning: GDB will be unable to debug shared library initializers
warning: and track explicitly loaded dynamic code.
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
foo=#
foo=#
foo=#
foo=#
foo=#
foo=# \h
Available help:
ABORT CREATE TRIGGERGRANT
ALTER GROUP CREATE TYPE INSERT
ALTER USERCREATE VIEW LOAD
BEGIN DECLARE LOCK
CHECKPOINTDELETEMOVE
CLOSE DROP AGGREGATENOTIFY
CLUSTER DROP DATABASE REINDEX
COMMENT DROP FUNCTION RESET
COMMITDROP GROUPREVOKE
COPY DROP INDEXROLLBACK
CREATE AGGREGATE DROP LANGUAGE
Re: [BUGS] Sun Solaris 2.5.1 Seg Faults PostgreSQL7.1.3 build commands
"Riendeau, Mike" <[EMAIL PROTECTED]> writes: > - psql is able to access the database created with createdb, > but Seg Faults on exit. > [ and various derivative problems ] This has been reported before; IIRC the problem has to do with getting your linkage to the readline shared library set up correctly. Check the mailing list archives to see what people did about it ... I don't recall the solution at the moment. (Other than reconfiguring without readline, but you probably won't like that answer.) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] date format problem
> 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
