Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id B45D263293E
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 30 Mar 2010 04:58:06 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 38264-05 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 30 Mar 2010 07:57:56 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.kickstone.com (87-194-183-70.dsl.cnl.uk.net
	[87.194.183.70])
	by mail.postgresql.org (Postfix) with ESMTP id 320BB6334CF
	for <pgsql-admin@postgresql.org>; Tue, 30 Mar 2010 04:57:56 -0300 (ADT)
Received: from 87-194-183-69.dsl.cnl.uk.net ([87.194.183.69]:2658
 helo=MADRAS2)
	by mail.kickstone.com with esmtpa (Exim 4.69)
	(envelope-from <john.lister-ps@kickstone.com>) id 1NwWKD-0002tX-Ul
	for pgsql-admin@postgresql.org; Tue, 30 Mar 2010 08:57:25 +0100
Message-ID: <D29C98141027402A85C7801C69954C11@squarepi.com>
From: "John Lister" <john.lister-ps@kickstone.com>
To: <pgsql-admin@postgresql.org>
Subject: Strange deletion problem
Date: Tue, 30 Mar 2010 08:57:52 +0100
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_00C6_01CACFE7.14D35670"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.009 tagged_above=-10 required=5
	tests=BAYES_05=-1.11, HTML_MESSAGE=0.001, RDNS_DYNAMIC=0.1
X-Spam-Level: 
X-Archive-Number: 201003/234
X-Sequence-Number: 34033

This is a multi-part message in MIME format.

------=_NextPart_000_00C6_01CACFE7.14D35670
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi, I have a table which is constantly updated through out the day with =
no problems, I'm running Postgresql 8.3.8 in ubuntu karmic. However, =
within the last week for some reason overnight it is being emptied and I =
can't work out why. I've set log_min_duration_statement to 0 so that =
postgresql dumps out every query made to the db but it hasn't brought =
anything up. During the period between from when I know the table is ok =
to the next morning when it is empty I can see entries in the logs for =
the expected 200 or so deletions, but during this period approx 15k rows =
are removed. The odd thing is that there is nothing else in the logs =
that references the table (I've looked for the obvious deletion and =
trunctate statements).

The table is modified using JDBC prepared statements so I see 3 entries =
for each of the expected delete statements (parse, bind, execute) and =
the statement is as follows:

delete from product_list where retailer_id=3D? and product_id=3D?


Therefore I have a few questions:
- Is there any other statements that could be causing the rows to be =
removed that I've missed
- Is there anything that could be deleting them without generating a log =
entry for the statement?
- Is it possible that data corruption of either the index/table is =
making my delete statements removed more rows?
- Is it a possibly bug?

Thanks

John
--

Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/

------=_NextPart_000_00C6_01CACFE7.14D35670
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.6000.16981" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi, I have a table which is constantly =
updated=20
through out the day with no problems, I'm&nbsp;running&nbsp;Postgresql =
8.3.8 in=20
ubuntu karmic. However, within the last week for some reason overnight =
it is=20
being emptied and I can't work out why. I've set =
log_min_duration_statement to 0=20
so that postgresql dumps out every query made to the db but it hasn't =
brought=20
anything up. During the period between from when I know the table is ok =
to the=20
next morning when it&nbsp;is empty&nbsp;I can see entries in the logs =
for the=20
expected 200 or so deletions, but during this period approx 15k rows are =

removed. The odd thing is that there is nothing else in the logs that =
references=20
the table (I've looked for the obvious deletion and trunctate=20
statements).</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>The table is modified using JDBC =
prepared=20
statements so I see 3 entries for each of the expected delete statements =
(parse,=20
bind, execute) and the statement&nbsp;is as follows:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>delete from product_list where =
retailer_id=3D? and=20
product_id=3D?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Therefore I have a few =
questions:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>- Is there any other statements that =
could be=20
causing the rows to be removed that I've missed</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>- Is there anything that could be =
deleting them=20
without generating a log entry for the statement?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>- Is it possible that data corruption =
of either the=20
index/table is making my delete statements removed more =
rows?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>- Is it a possibly bug?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Thanks</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>John</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>--</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Got needs? Get Goblin'! - <A=20
href=3D"http://www.pricegoblin.co.uk/">http://www.pricegoblin.co.uk/</A><=
BR></FONT></DIV></BODY></HTML>

------=_NextPart_000_00C6_01CACFE7.14D35670--