Re: Can't Figure Out Where Rows Are Going

Thanks for replying.

The only logfile I have is the 'serverlog' file that I have. Looking at that
file, I don't see any indications that there was an error.

The INSERT does return successfully in psql.

Any other possibilities?

> From: Rodrigo Gonzalez <rjgonzale(at)gmail(dot)com>
> Date: Sat, 06 May 2006 20:33:29 -0300
> To: HH <lists(at)lastonepicked(dot)com>
> Cc: PostgreSQL <pgsql-general(at)postgresql(dot)org>
> Subject: Re: [GENERAL] Can't Figure Out Where Rows Are Going
>
> Just to be sure....
>
> Any error msg in log?
>
> Maybe you can run this query in psql and see if it return any error msg
>
> Best regards
>
> Rodrigo
>
> HH wrote:
>> 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
>>
>>