Re: [BUGS] Deadlock in PostgreSQL 7.3.4
Am Montag, 18. August 2003 15:38 schrieb Tom Lane: > Philipp Reisner <[EMAIL PROTECTED]> writes: > > Now if the applications issues one delete statement concurrently on > > two connections both block forever. > > > > Please correct me if I am wrong, but should not one of the statements > > succeed and the other simply fail ? > > > > Aug 18 10:34:25 nut1 postgres[4934]: [44389] LOG: > > query: delete from Calls where objID = 2268645 > > > > Aug 18 10:34:25 nut1 postgres[4933]: [44071] LOG: > > query: delete from Calls where objID = 2268645 > > > > 4933 ?S 5:20 postgres: sd sd 10.2.1.5 idle in transaction > > 4934 ?S 5:08 postgres: sd sd 10.2.1.5 DELETE waiting > > I see no deadlock. 4933 is waiting for its client to issue another > command. 4934 is waiting to see if it can delete the row. > > regards, tom lane Hi Tom, Right, thanks for pointing it out for me. It seems to be somwhere in the application code or PostgreSQL's jdbc driver. Finally we are able to reproduce the "Lockup" and we will continue to work out if it is in the App or in the jdbc driver. -Philipp -- : Dipl-Ing Philipp Reisner Tel +43-1-8178292-50 : : LINBIT Information Technologies GmbH Fax +43-1-8178292-82 : : Schönbrunnerstr 244, 1120 Vienna, Austriahttp://www.linbit.com : ---(end of broadcast)--- TIP 8: explain analyze is your friend
[BUGS] postgresql 7.3.2 bug on date '1901-12-13' and '1901-12-13' - PK violation
Title: Message
Hi
Your
name
: Arnold Mavromatis
Your email address : [EMAIL PROTECTED]
System Configuration
-
PostgreSQL info
Server version PostgreSQL 7.3.2 on hppa2.0w-hp-hpux11.11
Engine Version 7.3.2
Compiled by
GCC 3.2.1
Please enter a FULL description of your problem:
In postgresql 7.3.2 trying to insert a
specific day
As
db1=> insert into sfc_days2 values (86071, to_date('1901-12-13',
'-MM-DD'), 3.0);
INSERT 1591881 1
db1=> insert into sfc_days2 values (86071, to_date('1901-12-14',
'-MM-DD'), 3.0);
ERROR: Cannot insert a duplicate key into unique index
sfc_days2_pkey
db1=>
gives a Primary Key Violation even though
the day in question is unique and not currently loaded in the table..
Please describe a way to repeat the problem. Please try to
provide a
concise reproducible
example, if at all possible:
Create sample table
CREATE TABLE "public"."sfc_days2"
(
"stn_num"
numeric(6,0) NOT NULL,
"lsd"
date NOT NULL,
"max_air_temp"
numeric(7,1),
CONSTRAINT
"sfc_days2_pkey" PRIMARY KEY ("stn_num", "lsd"),
CONSTRAINT
"sfc_days_max_air_ck" CHECK max_air_temp IS NOT NULL) AND ((max_air_temp
>= -70.0) AND (max_air_temp <= 60.0))) OR (max_air_temp IS NULL)))
);
Table info
"public.sfc_days2"
Column |
Type |
Modifiers
---+---+--
stn_num |
numeric(6,0) |
not null
lsd |
date |
not null
max_air_temp
| numeric(7,1) |
Indexes: sfc_days2_pkey primary key btree (stn_num, lsd)
Check constraints: "sfc_days_max_air_ck" (((max_air_temp IS
NOT NULL) AND ((max_air_temp >= -70.0) AND (max_air_temp <= 60.0))) OR (max_air_temp
IS NULL))
Sample insert data log
db1=> delete from sfc_days2;
DELETE 1
db1=> commit;
WARNING: COMMIT: no transaction in progress
COMMIT
db1=> select count(*) from sfc_days2;
count
---
0
(1 row)
db1=> insert into sfc_days2 values (86071, to_date('1901-12-13',
'-MM-DD'), 3.0);
INSERT 1591881 1
db1=> insert into sfc_days2 values (86071, to_date('1901-12-14',
'-MM-DD'), 3.0);
ERROR: Cannot insert a duplicate key into unique index
sfc_days2_pkey
db1=>
Any help would be greatly appreciated as this
bug is stopping us from importing data that spans more than 200 years from 1800..
That we wish to use for an internet
application using servlets..
Re: [BUGS] postgresql 7.3.2 bug on date '1901-12-13' and '1901-12-13'
On Wed, 20 Aug 2003, Arnold Mavromatis wrote:
> PostgreSQL info
> Server version PostgreSQL 7.3.2 on hppa2.0w-hp-hpux11.11
> Engine Version 7.3.2
> Compiled by GCC 3.2.1
I couldn't duplicate on Redhat9 using 7.3.4 or 7.4 beta.
What does a plain select to_date('1901-12-13', '-MM-DD')
and select to_date('1901-12-14', '-MM-DD') give you?
And did you give --enable-integer-datetimes to configure, and
what compilation options were used on the files (specifically was
-O2 used or a higher level, was -ffast-math used, etc...)?
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
FW: [BUGS] postgresql 7.3.2 bug on date '1901-12-13' and '1901-12-13'
Hi
A test of recommended selects is as follows..
db1=> select * from sfc_days2;
stn_num |lsd | max_air_temp
-++--
86071 | 1901-12-13 | 3.0
(1 row)
db1=> select * from sfc_days2 where lsd = to_date('1901-12-13',
'-MM-DD');
stn_num |lsd | max_air_temp
-++--
86071 | 1901-12-13 | 3.0
(1 row)
db1=> select * from sfc_days2 where lsd = to_date('1901-12-14',
'-MM-DD');
stn_num |lsd | max_air_temp
-++--
86071 | 1901-12-13 | 3.0
(1 row)
As for compiler options I believe everything was left as default for HP-UX
11.11 (Ie the options that you have recommended I do not know if they were
set...) without further investigation...
Will try and investigate and let you know...
Thanks in advance...
Cheers
Arn
-Original Message-
From: Stephan Szabo [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 20, 2003 4:08 PM
To: Arnold Mavromatis
Cc: [EMAIL PROTECTED]; Lan Tran
Subject: Re: [BUGS] postgresql 7.3.2 bug on date '1901-12-13' and
'1901-12-13'
On Wed, 20 Aug 2003, Arnold Mavromatis wrote:
> PostgreSQL info
> Server version PostgreSQL 7.3.2 on hppa2.0w-hp-hpux11.11
> Engine Version 7.3.2
> Compiled by GCC 3.2.1
I couldn't duplicate on Redhat9 using 7.3.4 or 7.4 beta.
What does a plain select to_date('1901-12-13', '-MM-DD')
and select to_date('1901-12-14', '-MM-DD') give you?
And did you give --enable-integer-datetimes to configure, and
what compilation options were used on the files (specifically was
-O2 used or a higher level, was -ffast-math used, etc...)?
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] postgresql 7.3.2 bug on date '1901-12-13' and '1901-12
Hi again
We've actually determined that we didn't compile it but just downloaded a
binary from the web...(for HP-UX 11.11) so I've got no idea as to compiler
settings used on this binary...
Cheers
Arn
-Original Message-
From: Arnold Mavromatis [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 20, 2003 4:11 PM
To: '[EMAIL PROTECTED]'
Cc: '[EMAIL PROTECTED]'; Lan Tran; '[EMAIL PROTECTED]'
Subject: FW: [BUGS] postgresql 7.3.2 bug on date '1901-12-13' and '1901-12
-13'
Hi
A test of recommended selects is as follows..
db1=> select * from sfc_days2;
stn_num |lsd | max_air_temp
-++--
86071 | 1901-12-13 | 3.0
(1 row)
db1=> select * from sfc_days2 where lsd = to_date('1901-12-13',
'-MM-DD');
stn_num |lsd | max_air_temp
-++--
86071 | 1901-12-13 | 3.0
(1 row)
db1=> select * from sfc_days2 where lsd = to_date('1901-12-14',
'-MM-DD');
stn_num |lsd | max_air_temp
-++--
86071 | 1901-12-13 | 3.0
(1 row)
As for compiler options I believe everything was left as default for HP-UX
11.11 (Ie the options that you have recommended I do not know if they were
set...) without further investigation...
Will try and investigate and let you know...
Thanks in advance...
Cheers
Arn
-Original Message-
From: Stephan Szabo [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 20, 2003 4:08 PM
To: Arnold Mavromatis
Cc: [EMAIL PROTECTED]; Lan Tran
Subject: Re: [BUGS] postgresql 7.3.2 bug on date '1901-12-13' and
'1901-12-13'
On Wed, 20 Aug 2003, Arnold Mavromatis wrote:
> PostgreSQL info
> Server version PostgreSQL 7.3.2 on hppa2.0w-hp-hpux11.11
> Engine Version 7.3.2
> Compiled by GCC 3.2.1
I couldn't duplicate on Redhat9 using 7.3.4 or 7.4 beta.
What does a plain select to_date('1901-12-13', '-MM-DD')
and select to_date('1901-12-14', '-MM-DD') give you?
And did you give --enable-integer-datetimes to configure, and
what compilation options were used on the files (specifically was
-O2 used or a higher level, was -ffast-math used, etc...)?
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: FW: [BUGS] postgresql 7.3.2 bug on date '1901-12-13' and '1901-12
On Wed, 20 Aug 2003, Arnold Mavromatis wrote:
> db1=> select * from sfc_days2 where lsd = to_date('1901-12-13',
> '-MM-DD');
> stn_num |lsd | max_air_temp
> -++--
>86071 | 1901-12-13 | 3.0
> (1 row)
>
> db1=> select * from sfc_days2 where lsd = to_date('1901-12-14',
> '-MM-DD');
> stn_num |lsd | max_air_temp
> -++--
>86071 | 1901-12-13 | 3.0
> (1 row)
I don't have a particularly good idea of why that would be happening, but
does select (date '1901/12/13' = date '1901/12/14'); return true for you
(attempting to see if you get the same results from a different method
of getting the date fields).
And what does select timestamp '1901/12/13' + interval '1 day'; give you?
> As for compiler options I believe everything was left as default for HP-UX
> 11.11 (Ie the options that you have recommended I do not know if they were
> set...) without further investigation...
Actually it looks like the option I was remembering that broke some of the
datetime functionality is at least checked for explicitly in 7.3.4 and
gives a compile time error.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
