pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedInspect values of prepared statements
11+ messages / 5 participants
[nested] [flat]
* Inspect values of prepared statements
@ 2016-05-02 17:51 Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 17:54 ` Re: Inspect values of prepared statements Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 11+ messages in thread
From: Natalie Wenz @ 2016-05-02 17:51 UTC (permalink / raw)
To: pgsql-admin
Is there any way for an administrator to view the values of a prepared statement that is currently executing?
Thanks!
Natalie
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Inspect values of prepared statements
2016-05-02 17:51 Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
@ 2016-05-02 17:54 ` Joshua D. Drake <jd@commandprompt.com>
2016-05-02 18:00 ` Re: Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
0 siblings, 1 reply; 11+ messages in thread
From: Joshua D. Drake @ 2016-05-02 17:54 UTC (permalink / raw)
To: Natalie Wenz <nataliewenz@ebureau.com>; pgsql-admin
On 05/02/2016 10:51 AM, Natalie Wenz wrote:
> Is there any way for an administrator to view the values of a prepared statement that is currently executing?
statement logging will do it.
>
> Thanks!
> Natalie
>
--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Inspect values of prepared statements
2016-05-02 17:51 Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 17:54 ` Re: Inspect values of prepared statements Joshua D. Drake <jd@commandprompt.com>
@ 2016-05-02 18:00 ` Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 18:06 ` Re: Inspect values of prepared statements Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 11+ messages in thread
From: Natalie Wenz @ 2016-05-02 18:00 UTC (permalink / raw)
To: pgsql-admin; Joshua D. Drake <jd@commandprompt.com>
Is it possible to turn that on without interrupting current traffic?
> On May 2, 2016, at 12:54 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
>
> On 05/02/2016 10:51 AM, Natalie Wenz wrote:
>> Is there any way for an administrator to view the values of a prepared statement that is currently executing?
>
> statement logging will do it.
>
>>
>> Thanks!
>> Natalie
>>
>
>
> --
> Command Prompt, Inc. http://the.postgres.company/
> +1-503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
> Everyone appreciates your honesty, until you are honest with them.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Inspect values of prepared statements
2016-05-02 17:51 Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 17:54 ` Re: Inspect values of prepared statements Joshua D. Drake <jd@commandprompt.com>
2016-05-02 18:00 ` Re: Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
@ 2016-05-02 18:06 ` Joshua D. Drake <jd@commandprompt.com>
2016-05-02 18:41 ` Re: Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
0 siblings, 1 reply; 11+ messages in thread
From: Joshua D. Drake @ 2016-05-02 18:06 UTC (permalink / raw)
To: Natalie Wenz <nataliewenz@ebureau.com>; pgsql-admin
On 05/02/2016 11:00 AM, Natalie Wenz wrote:
> Is it possible to turn that on without interrupting current traffic?
Yes with a HUP or reload.
JD
>
>
>> On May 2, 2016, at 12:54 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
>>
>> On 05/02/2016 10:51 AM, Natalie Wenz wrote:
>>> Is there any way for an administrator to view the values of a prepared statement that is currently executing?
>>
>> statement logging will do it.
>>
>>>
>>> Thanks!
>>> Natalie
>>>
>>
>>
>> --
>> Command Prompt, Inc. http://the.postgres.company/
>> +1-503-667-4564
>> PostgreSQL Centered full stack support, consulting and development.
>> Everyone appreciates your honesty, until you are honest with them.
>
>
>
--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Inspect values of prepared statements
2016-05-02 17:51 Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 17:54 ` Re: Inspect values of prepared statements Joshua D. Drake <jd@commandprompt.com>
2016-05-02 18:00 ` Re: Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 18:06 ` Re: Inspect values of prepared statements Joshua D. Drake <jd@commandprompt.com>
@ 2016-05-02 18:41 ` Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 19:13 ` Re: Inspect values of prepared statements Joshua D. Drake <jd@commandprompt.com>
2016-05-02 21:40 ` Re: Inspect values of prepared statements Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 11+ messages in thread
From: Natalie Wenz @ 2016-05-02 18:41 UTC (permalink / raw)
To: pgsql-admin; +Cc: Joshua D. Drake <jd@commandprompt.com>
Oh, interesting. I didn't realize you could change that on the fly. That's helpful.
So, I tried turning statement logging on and that didn't give me any more detail. I think that is because the query I want to inspect is generating an error (which I failed to mention in my original question).
ERROR: invalid input syntax for type double precision: "A"
STATEMENT: insert into x (a,b,c,d,e,f) VALUES ($1,$2,$3,$4,$5,$6)
columns c, d, e are all double precision, so I'm not sure which one has the bad data. If I knew what values $1 and $2 were, I could pinpoint the record that is generating the error.
So, my revised question: is there any way to inspect the values from a prepared statement when the query contains an error? Or some combination of logging settings that would give me that detail?
Thanks!
> On May 2, 2016, at 1:06 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
>
> On 05/02/2016 11:00 AM, Natalie Wenz wrote:
>> Is it possible to turn that on without interrupting current traffic?
>
> Yes with a HUP or reload.
>
> JD
>
>
>>
>>
>>> On May 2, 2016, at 12:54 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
>>>
>>> On 05/02/2016 10:51 AM, Natalie Wenz wrote:
>>>> Is there any way for an administrator to view the values of a prepared statement that is currently executing?
>>>
>>> statement logging will do it.
>>>
>>>>
>>>> Thanks!
>>>> Natalie
>>>>
>>>
>>>
>>> --
>>> Command Prompt, Inc. http://the.postgres.company/
>>> +1-503-667-4564
>>> PostgreSQL Centered full stack support, consulting and development.
>>> Everyone appreciates your honesty, until you are honest with them.
>>
>>
>>
>
>
> --
> Command Prompt, Inc. http://the.postgres.company/ <http://the.postgres.company/;
> +1-503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
> Everyone appreciates your honesty, until you are honest with them.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Inspect values of prepared statements
2016-05-02 17:51 Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 17:54 ` Re: Inspect values of prepared statements Joshua D. Drake <jd@commandprompt.com>
2016-05-02 18:00 ` Re: Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 18:06 ` Re: Inspect values of prepared statements Joshua D. Drake <jd@commandprompt.com>
2016-05-02 18:41 ` Re: Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
@ 2016-05-02 19:13 ` Joshua D. Drake <jd@commandprompt.com>
2016-05-02 21:10 ` Re: Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
1 sibling, 1 reply; 11+ messages in thread
From: Joshua D. Drake @ 2016-05-02 19:13 UTC (permalink / raw)
To: Natalie Wenz <nataliewenz@ebureau.com>; pgsql-admin
On 05/02/2016 11:41 AM, Natalie Wenz wrote:
> Oh, interesting. I didn't realize you could change that on the fly.
> That's helpful.
>
> So, my revised question: is there any way to inspect the values from a
> prepared statement when the query contains an error? Or some combination
> of logging settings that would give me that detail?
>
What version are you running? Also check log_min_error_statement
Sincerely,
JD
--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Inspect values of prepared statements
2016-05-02 17:51 Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 17:54 ` Re: Inspect values of prepared statements Joshua D. Drake <jd@commandprompt.com>
2016-05-02 18:00 ` Re: Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 18:06 ` Re: Inspect values of prepared statements Joshua D. Drake <jd@commandprompt.com>
2016-05-02 18:41 ` Re: Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 19:13 ` Re: Inspect values of prepared statements Joshua D. Drake <jd@commandprompt.com>
@ 2016-05-02 21:10 ` Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 21:17 ` Re: Inspect values of prepared statements Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 11+ messages in thread
From: Natalie Wenz @ 2016-05-02 21:10 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; pgsql-admin
Thanks, I did check log_min_error_statement; no additional information. I also tried dialing log_min_duration_statement to 0, same result. Both things *did* show the values used for other, successful, queries that were using prepared statements. But in the case of the query that was triggering the error, none of those options logged the values used in the prepared statement. Is that expected behavior?
We're running postgres 9.3.11 on that server.
Thanks again,
Natalie
> On May 2, 2016, at 2:13 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
>
> On 05/02/2016 11:41 AM, Natalie Wenz wrote:
>> Oh, interesting. I didn't realize you could change that on the fly.
>> That's helpful.
>
>>
>> So, my revised question: is there any way to inspect the values from a
>> prepared statement when the query contains an error? Or some combination
>> of logging settings that would give me that detail?
>>
>
> What version are you running? Also check log_min_error_statement
>
> Sincerely,
>
> JD
>
> --
> Command Prompt, Inc. http://the.postgres.company/
> +1-503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
> Everyone appreciates your honesty, until you are honest with them.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Inspect values of prepared statements
2016-05-02 17:51 Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 17:54 ` Re: Inspect values of prepared statements Joshua D. Drake <jd@commandprompt.com>
2016-05-02 18:00 ` Re: Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 18:06 ` Re: Inspect values of prepared statements Joshua D. Drake <jd@commandprompt.com>
2016-05-02 18:41 ` Re: Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 19:13 ` Re: Inspect values of prepared statements Joshua D. Drake <jd@commandprompt.com>
2016-05-02 21:10 ` Re: Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
@ 2016-05-02 21:17 ` Joshua D. Drake <jd@commandprompt.com>
0 siblings, 0 replies; 11+ messages in thread
From: Joshua D. Drake @ 2016-05-02 21:17 UTC (permalink / raw)
To: Natalie Wenz <nataliewenz@ebureau.com>; pgsql-admin
On 05/02/2016 02:10 PM, Natalie Wenz wrote:
> Thanks, I did check log_min_error_statement; no additional information. I also tried dialing log_min_duration_statement to 0, same result. Both things *did* show the values used for other, successful, queries that were using prepared statements. But in the case of the query that was triggering the error, none of those options logged the values used in the prepared statement. Is that expected behavior?
>
> We're running postgres 9.3.11 on that server.
Can you attach your postgresql.conf?
--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Inspect values of prepared statements
2016-05-02 17:51 Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 17:54 ` Re: Inspect values of prepared statements Joshua D. Drake <jd@commandprompt.com>
2016-05-02 18:00 ` Re: Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 18:06 ` Re: Inspect values of prepared statements Joshua D. Drake <jd@commandprompt.com>
2016-05-02 18:41 ` Re: Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
@ 2016-05-02 21:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-05-02 23:17 ` Re: Inspect values of prepared statements bricklen <bricklen@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Tom Lane @ 2016-05-02 21:40 UTC (permalink / raw)
To: Natalie Wenz <nataliewenz@ebureau.com>; +Cc: pgsql-admin; Joshua D. Drake <jd@commandprompt.com>
Natalie Wenz <nataliewenz@ebureau.com> writes:
> So, I tried turning statement logging on and that didn't give me any more detail. I think that is because the query I want to inspect is generating an error (which I failed to mention in my original question).
> ERROR: invalid input syntax for type double precision: "A"
> STATEMENT: insert into x (a,b,c,d,e,f) VALUES ($1,$2,$3,$4,$5,$6)
> columns c, d, e are all double precision, so I'm not sure which one has the bad data. If I knew what values $1 and $2 were, I could pinpoint the record that is generating the error.
Ah. You're out of luck on that, I'm afraid, because what the
parameter-value logging code does is to print from the converted internal
forms of the parameters. If you get an error while converting the values
to internal form, which is evidently what happened here, no log for you.
Also, that conversion happens on-the-fly as each Bind message field is
read. Thus, for example, if the bad input is for parameter $2 then we
simply can't print the remaining parameter values, because we have not
seen them yet. (This could be fixed, perhaps, but it would involve a
lot of restructuring and probably slow things down. Also it wouldn't
work for parameters arriving in binary form; which is probably what led
us to make the choice to do parameter logging this way.)
What I think we could do without too much trouble or overhead is to set
up errcontext data so that a failure like that could come with a CONTEXT
line like
ERROR: invalid input syntax for type double precision: "A"
STATEMENT: insert into x (a,b,c,d,e,f) VALUES ($1,$2,$3,$4,$5,$6)
CONTEXT: reading parameter $2 for statement "foo"
I'm curious whether that would be enough information to solve your
problem.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Inspect values of prepared statements
2016-05-02 17:51 Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 17:54 ` Re: Inspect values of prepared statements Joshua D. Drake <jd@commandprompt.com>
2016-05-02 18:00 ` Re: Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 18:06 ` Re: Inspect values of prepared statements Joshua D. Drake <jd@commandprompt.com>
2016-05-02 18:41 ` Re: Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 21:40 ` Re: Inspect values of prepared statements Tom Lane <tgl@sss.pgh.pa.us>
@ 2016-05-02 23:17 ` bricklen <bricklen@gmail.com>
2016-05-03 00:13 ` Re: Inspect values of prepared statements David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: bricklen @ 2016-05-02 23:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
On Mon, May 2, 2016 at 2:40 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> What I think we could do without too much trouble or overhead is to set
> up errcontext data so that a failure like that could come with a CONTEXT
> line like
>
> ERROR: invalid input syntax for type double precision: "A"
> STATEMENT: insert into x (a,b,c,d,e,f) VALUES ($1,$2,$3,$4,$5,$6)
> CONTEXT: reading parameter $2 for statement "foo"
>
> I'm curious whether that would be enough information to solve your
> problem.
>
I ran into this same problem a couple weeks ago, your suggestion above
would have supplied enough information to debug the invalid input without
having to jump through hoops at the application side.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Inspect values of prepared statements
2016-05-02 17:51 Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 17:54 ` Re: Inspect values of prepared statements Joshua D. Drake <jd@commandprompt.com>
2016-05-02 18:00 ` Re: Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 18:06 ` Re: Inspect values of prepared statements Joshua D. Drake <jd@commandprompt.com>
2016-05-02 18:41 ` Re: Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 21:40 ` Re: Inspect values of prepared statements Tom Lane <tgl@sss.pgh.pa.us>
2016-05-02 23:17 ` Re: Inspect values of prepared statements bricklen <bricklen@gmail.com>
@ 2016-05-03 00:13 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: David G. Johnston @ 2016-05-03 00:13 UTC (permalink / raw)
To: bricklen <bricklen@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
On Mon, May 2, 2016 at 4:17 PM, bricklen <bricklen@gmail.com> wrote:
>
> On Mon, May 2, 2016 at 2:40 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> What I think we could do without too much trouble or overhead is to set
>> up errcontext data so that a failure like that could come with a CONTEXT
>> line like
>>
>> ERROR: invalid input syntax for type double precision: "A"
>> STATEMENT: insert into x (a,b,c,d,e,f) VALUES ($1,$2,$3,$4,$5,$6)
>> CONTEXT: reading parameter $2 for statement "foo"
>>
>> I'm curious whether that would be enough information to solve your
>> problem.
>>
>
> I ran into this same problem a couple weeks ago, your suggestion above
> would have supplied enough information to debug the invalid input without
> having to jump through hoops at the application side.
>
​Agreed. The times I encounter this I get hung up is figuring out which of
5 identically typed columns might be presenting the problem - typically
because of an empty string error so searching is somewhat more tedious​.
If reporting the number is low-hanging fruit it is definitely worth picking
off.
David J.
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2016-05-03 00:13 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-05-02 17:51 Inspect values of prepared statements Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 17:54 ` Joshua D. Drake <jd@commandprompt.com>
2016-05-02 18:00 ` Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 18:06 ` Joshua D. Drake <jd@commandprompt.com>
2016-05-02 18:41 ` Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 19:13 ` Joshua D. Drake <jd@commandprompt.com>
2016-05-02 21:10 ` Natalie Wenz <nataliewenz@ebureau.com>
2016-05-02 21:17 ` Joshua D. Drake <jd@commandprompt.com>
2016-05-02 21:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-05-02 23:17 ` bricklen <bricklen@gmail.com>
2016-05-03 00:13 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox