Re: Can't Figure Out Where Rows Are Going

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@sss.pgh.pa.us>
>>Date: Sat, 06 May 2006 22:04:56 -0400
>>To: HH <lists@lastonepicked.com>
>>Cc: PostgreSQL <pgsql-general@postgresql.org>
>>Subject: Re: [GENERAL] Can't Figure Out Where Rows Are Going
>>
>>HH <lists@lastonepicked.com> 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