Re: Can't Figure Out Where Rows Are Going

Thanks for the suggestion. I will try to see if we can do this.

One issue is that we don't know the minute this occurs (the rows disappear).
We haven't been able to pinpoint it to any specific event or timing.

We're stil trying to narrow that down.

> From: Andrus <eetasoft(at)online(dot)ee>
> Organization: Hub.Org Networking Services
> Date: Sun, 7 May 2006 21:23:32 +0300
> To: <pgsql-general(at)postgresql(dot)org>
> Subject: Re: [GENERAL] Can't Figure Out Where Rows Are Going
>
> Turn PITR on
> Restore database to the state immediately after transaction is commited and
> look for rows presence.
>
> Andrus.
>
>
> "HH" <lists(at)lastonepicked(dot)com> wrote in message
> news:C082766B(dot)DD7B9%lists(at)lastonepicked(dot)com(dot)(dot)(dot)
>> Hello,
>>
>> I am running 8.1.3, built from source, on RHEL4/x64.
>>
>> I have a Web application that uses this database to sell products. We have
>> an order table and an order lines table with a one to many relationship
>> between them.
>>
>> For the past few months I have had a strange problem where sometimes
>> (about
>> 2% of orders), the line item rows disappear. By disappear I mean that they
>> are committed to the database and then when I go to look at the order
>> later,
>> there are no line items. The row in the 'order' table still exists, it is
>> just the line(s) that vanish.
>>
>> As I started looking into this problem I assumed that it was a bug in my
>> Web
>> application. I did some extensive testing and logging to no avail. I
>> turned
>> up the logging on my Web app and I can see the INSERTs but I never saw any
>> DELETE statements though I can't find the child row(s) in the DB.
>>
>> I've been perplexed for quite some time so a few days ago I turned on the
>> following PG logging:
>>
>> log_statement = 'mod'
>>
>> Today, I found an order that has this problem. Grepping my serverlog, I
>> see
>> the following:
>>
>> The line item is inserted:
>>
>> serverlog:LOG: statement: INSERT INTO order_lines ("order_description",
>> "order_id", "updated_at", "band_name", "order_item", "product_id",
>> "line_source", "order_quantity", "extended_price", "unit_price",
>> "catalog_number", "created_at") VALUES('FW "Not My Pres" Trucker Hat',
>> 16899, '2006-05-06 14:43:38', NULL, 'Baseball Cap ', 165, 'Merch', 1,
>> NULL,
>> 7.0, 94, '2006-05-06 14:43:38')
>>
>> Then, I do a SELECT:
>>
>> fw_production=# SELECT COUNT(*) FROM order_lines WHERE order_id = 16899;
>> count
>> -------
>> 0
>>
>> There was about 3 hours between when the line was inserted and when I went
>> to look for it. There is no DELETE in the serverlog for this row.
>>
>> I can't figure out where this row went and why it disappeared! Is it
>> getting
>> deleted some other way that wouldn't be logged? Am I missing something
>> rudimentary?
>>
>> Any help is appreciated.
>>
>> Hunter
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>> choose an index scan if your joining column's datatypes do not
>> match
>>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq