Turn PITR on Restore database to the state immediately after transaction is commited and look for rows presence. Andrus. "HH" <lists@xxxxxxxxxxxxxxxxx> wrote in message news:C082766B.DD7B9%lists@xxxxxxxxxxxxxxxxxxxx > 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 >
