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
