Re: Can't Figure Out Where Rows Are Going - Mailing list pgsql-general

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@greatgulfhomes.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
>