Re: Can't Figure Out Where Rows Are Going

Thanks.

Actually, the Web app is a Ruby on Rails app and RoR doesn't use connection
pooling in its current implementation.

I'll do some more investigation here to double check but this is an area
where Rails is pretty simple and straightforward.

Yes, since I can see the rows outside of that transaction, I (incorrectly?)
assumed that meant it was committed.

I will keep looking for other reasons.

Hunter

> From: John Sidney-Woollett <johnsw(at)wardbrook(dot)com>
> Date: Sun, 07 May 2006 09:11:10 +0100
> To: HH <lists(at)lastonepicked(dot)com>
> Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL <pgsql-general(at)postgresql(dot)org>
> Subject: Re: [GENERAL] 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(at)sss(dot)pgh(dot)pa(dot)us>
>>> Date: Sat, 06 May 2006 22:04:56 -0400
>>> To: HH <lists(at)lastonepicked(dot)com>
>>> Cc: PostgreSQL <pgsql-general(at)postgresql(dot)org>
>>> Subject: Re: [GENERAL] Can't Figure Out Where Rows Are Going
>>>
>>> HH <lists(at)lastonepicked(dot)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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend