Re: How to trace client sql requests?

On Fri, July 10, 2009 14:58, hubert depesz lubaczewski wrote:
> You can enable by database:
>
> alter database x set log_min_duration_statement = 0;

Many, many thanks. Now of course I need more help...

The situation is that data inserted into the DB is not being found
on a subsequent select and I am unaware of any deletes being done.
So, I am hoping to find where the data is going or why the select is
not working.

This is the critical insert:

...
2009-07-10 15:13:00 EDT hll_theheart_test 216.185.71.24(57637)
hll_theheart_db_admin : LOCATION: exec_simple_query,
postgres.c:1105

2009-07-10 15:13:00 EDT hll_theheart_test 216.185.71.24(57637)
hll_theheart_db_admin : LOG: 00000: duration: 1.366 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', 1.0, 1.0, '1858-01-01 04:56:02.000000', E'Canadian
Dollar', NULL, '2009-07-10 19:13:00', E'CAD', '2009-07-10
19:13:00.151885')

2009-07-10 15:13:00 EDT hll_theheart_test 216.185.71.24(57637)
hll_theheart_db_admin : LOCATION: exec_simple_query,
postgres.c:1105

2009-07-10 15:13:00 EDT hll_theheart_test 216.185.71.24(57637)
hll_theheart_db_admin : LOG: 00000: duration: 0.379 ms statement:
SELECT currval('currencies_id_seq')

2009-07-10 15:13:00 EDT hll_theheart_test 216.185.71.24(57637)
hll_theheart_db_admin : LOCATION: exec_simple_query,
postgres.c:1105

2009-07-10 15:13:00 EDT hll_theheart_test 216.185.71.24(57637)
hll_theheart_db_admin : LOG: 00000: duration: 0.073 ms statement:
RELEASE SAVEPOINT active_record_1
...

This seems to have worked. Would the log show if it did not?

The I see a bunch of these:

2009-07-10 15:13:02 EDT hll_theheart_test 216.185.71.24(57638)
hll_theheart_db_admin : LOG: 00000: duration: 0.082 ms statement:
SET client_min_messages TO 'notice'

2009-07-10 15:13:02 EDT hll_theheart_test 216.185.71.24(57638)
hll_theheart_db_admin : LOCATION: exec_simple_query,
postgres.c:1105

2009-07-10 15:13:02 EDT hll_theheart_test 216.185.71.24(57638)
hll_theheart_db_admin : LOG: 00000: duration: 6.155 ms statement:
SELECT a.attname, format_type(a.atttypid, a.atttypmod),
d.adsrc, a.attnotnull

2009-07-10 15:13:02 EDT hll_theheart_test 216.185.71.24(57638)
hll_theheart_db_admin : LOCATION: exec_simple_query,
postgres.c:1105

2009-07-10 15:13:02 EDT hll_theheart_test 216.185.71.24(57638)
hll_theheart_db_admin : LOG: 00000: duration: 1.285 ms statement:
SELECT a.attname, format_type(a.atttypid, a.atttypmod),
d.adsrc, a.attnotnull

and finally, I get a long list of these:

2009-07-10 15:13:02 EDT hll_theheart_test 216.185.71.24(57638)
hll_theheart_db_admin : LOG: 00000: duration: 1.779 ms statement:
SELECT * FROM "currencies" WHERE ("currencies"."currency_code" =
E'CAD') LIMIT 1
...

I believe that this is what I want to examine. Is there a server
side technique that I can use which will tell me what data this
statement returned or if it found nothing?

In any case, I see the INSERTS and I can find NO DELETES at all. Is
there any other way to remove some or all data from a table?

--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne mailto:ByrneJB(at)Harte-Lyne(dot)ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3