If you're using a (java) web app with a connection pool is there a
possibility that some connections are configured with auto-commit=false
and that some future transaction issues a rollback which may include the
insert of the order items?
Perhaps some kind of persistance manager is doing this without your
realising it (hibernate, JDO etc)
Or that the connection pool recycled the connection without issuing a
commit.
Although that doesn't necessarily explain why you can see the rows
outside of that particular transaction...
If this is a java app, try asking the postgres-jdbc list.
John
HH wrote:
Thanks Tom.
I don't use any PL language functions in my app explicitly but perhaps
something implicit when using one of the command line tools or something
like that? I don't have anything pointing in that direction.
I double checked the definition for the 'orders' table (header) and also the
'order_lines', just to make sure there wasn't something in there that could
be throwing me off.
I didn't see anything in 'orders' of any interest at all. I've included both
below in case I missed something in there.
I appreciate everyone trying to help and any other ideas are very much
appreciated. I hope to be able to trace the cause at some point.
-
db_production=# \d orders
Table "public.orders"
Column | Type |
Modifiers
------------------------+-----------------------------+---------------------
--------------------------------
id | integer | not null default
nextval('orders_id_seq'::regclass)
billing_address_1 | character varying(255) |
billing_address_2 | character varying(255) |
billing_city | character varying(255) |
billing_state_province | character varying(255) |
billing_postal_code | character varying(255) |
billing_country | character varying(255) |
phone_number | character varying(255) |
email_address | character varying(255) |
store_name | character varying(255) |
cardholders_name | character varying(255) |
card_type | character varying(255) |
card_number | character varying(255) |
card_security_code | character varying(255) |
expires_month | character varying(255) |
expires_year | character varying(255) |
sent_to_gateway | timestamp without time zone |
gateway_confirmation | character varying(255) |
avs_address | character varying(255) |
avs_zip_code | character varying(255) |
created_at | timestamp without time zone |
updated_at | timestamp without time zone |
billing_first_name | character varying(255) |
billing_last_name | character varying(255) |
shipping_cost | double precision |
sales_tax | double precision |
order_status | character varying(255) |
processor_type | character varying(255) |
ipn_notification | character varying(255) |
ipn_date | timestamp without time zone |
ipn_email | character varying(255) |
ip_address | character varying(255) |
Indexes:
"orders_pkey" PRIMARY KEY, btree (id)
"orders_order_status_index" btree (order_status)
'order_lines'
fatwreck_production=# \d order_lines;
Table "public.order_lines"
Column | Type |
Modifiers
-------------------+-----------------------------+--------------------------
--------------------------------
id | integer | not null default
nextval('order_lines_id_seq'::regclass)
order_id | integer |
order_item | character varying(255) |
order_description | character varying(255) |
order_quantity | integer |
unit_price | numeric |
extended_price | numeric |
created_at | timestamp without time zone |
updated_at | timestamp without time zone |
band_name | character varying(255) |
catalog_number | character varying(255) |
product_id | character varying(255) |
line_source | character varying(255) |
Indexes:
"order_lines_pkey" PRIMARY KEY, btree (id)
"order_lines_order_id_index" btree (order_id)
Foreign-key constraints:
"order_lines_order_id_fkey" FOREIGN KEY (order_id) REFERENCES orders(id)
From: Tom Lane <tgl@xxxxxxxxxxxxx>
Date: Sat, 06 May 2006 22:04:56 -0400
To: HH <lists@xxxxxxxxxxxxxxxxx>
Cc: PostgreSQL <pgsql-general@xxxxxxxxxxxxxx>
Subject: Re: [GENERAL] Can't Figure Out Where Rows Are Going
HH <lists@xxxxxxxxxxxxxxxxx> writes:
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?
Aside from the suggestion already made that the inserting transaction
got rolled back instead of committed, I'd wonder about indirect
deletions due to ON DELETE CASCADE foreign keys, or deletions executed
by PL-language functions. Neither of those would produce obvious log
entries for their effects...
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
