Re: Can't Figure Out Where Rows Are Going

Thanks for taking the time to reply.

If that was occurring, would the log show it? I don't see any sign of a
roll-back occurring in the log and when I run the queries in psql, they seem
to complete properly.

> From: Terry Fielder <terry(at)ashtonwoodshomes(dot)com>
> Date: Sat, 06 May 2006 19:43:19 -0400
> To: Rodrigo Gonzalez <rjgonzale(at)gmail(dot)com>
> Cc: HH <lists(at)lastonepicked(dot)com>, PostgreSQL <pgsql-general(at)postgresql(dot)org>
> Subject: Re: [GENERAL] Can't Figure Out Where Rows Are Going
>
> Is there an chance the rows are being inserted in a transaction that
> fails and rolls back? Maybe look at all the lines that were inserted
> with that order, and try them manually in psql, character for character,
> and see if an error pops up for any of the lines?
>
> Terry Fielder
> terry(at)greatgulfhomes(dot)com
> Associate Director Software Development and Deployment
> Great Gulf Homes / Ashton Woods Homes
> Fax: (416) 441-9085
>
>
>
> Rodrigo Gonzalez wrote:
>> 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
>>>
>>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: don't forget to increase your free space map settings
>>