Re: 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
>
>