Re: Inserted data is disappearing - Mailing list pgsql-general

In response to "James B. Byrne" <byrnejb@harte-lyne.ca>:
>
> This is a portion of the log for the most recent run that exhibits
> the problem:
>
> ...
> 2009-07-10 15:59:17 EDT hll_theheart_test 216.185.71.24(49133)
> hll_theheart_db_admin : LOG:  00000: duration: 0.446 ms  statement:
> INSERT INTO "currencies" ("is_invoicable", "is_payable",
> "changed_by", "created_by", "premium_factor", "discount_factor",
> "effective_from", "currency_name", "superseded_after", "changed_at",
> "currency_code", "created_at") VALUES('f', 'f', E'not available',
> E'not available', 0.9, 1.1, '1785-07-06 04:56:02.000000', E'United
> States Dollar', NULL, '2009-07-10 19:59:17', E'USD', '2009-07-10
> 19:59:17.634473')
>
> That seems a valid insert.
>
> 2009-07-10 15:59:17 EDT hll_theheart_test 216.185.71.24(49133)
> hll_theheart_db_admin : LOCATION:  exec_simple_query,
> postgres.c:1105
> 2009-07-10 15:59:17 EDT hll_theheart_test 216.185.71.24(49133)
> hll_theheart_db_admin : LOG:  00000: duration: 0.172 ms  statement:
> SELECT currval('currencies_id_seq')
> 2009-07-10 15:59:17 EDT hll_theheart_test 216.185.71.24(49133)
> hll_theheart_db_admin : LOCATION:  exec_simple_query,
> postgres.c:1105
> 2009-07-10 15:59:17 EDT hll_theheart_test 216.185.71.24(49133)
> hll_theheart_db_admin : LOG:  00000: duration: 0.067 ms  statement:
> RELEASE SAVEPOINT active_record_1
>
> This seems ok but the absence of proof does not ensure the absence
> of error.  If the insert failed would I see this fact reflected in a
> log entry?

Unless you have some really bizarre config in your postgresql.conf,
then a failure of that insert would result in a logged error message.

Why not just intentionally try an invalid insert statement to be
sure that it logs.

> 2009-07-10 15:59:17 EDT hll_theheart_test 216.185.71.24(49133)
> hll_theheart_db_admin : LOCATION:  exec_simple_query,
> postgres.c:1105
> 2009-07-10 15:59:17 EDT hll_theheart_test 216.185.71.24(49133)
> hll_theheart_db_admin : LOG:  00000: duration: 0.782 ms  statement:
> SELECT * FROM "currencies"
>
> The client program that receives this result reports that there are
> no rows returned. So where did they go"?

What happens between the INSERT and the SELECT?  Are there DELETE,
TRUNCATE, or ROLLBACK statements?

Also, look for a BEGIN statement that is never COMMITed.  If the client
starts a transaction, INSERTs a bunch of stuff, then disconnects without
issuing a COMMIT, Postgres will rollback the transaction, thus it will
be as if the data was never inserted.

HTH.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/