Обсуждение: Inspect values of prepared statements
Is there any way for an administrator to view the values of a prepared statement that is currently executing? Thanks! Natalie
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.
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.
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.
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.
JDOn 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.
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.
Thanks, I did check log_min_error_statement; no additional information. I also tried dialing log_min_duration_statementto 0, same result. Both things *did* show the values used for other, successful, queries that wereusing prepared statements. But in the case of the query that was triggering the error, none of those options logged thevalues 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.
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_statementto 0, same result. Both things *did* show the values used for other, successful, queries that wereusing prepared statements. But in the case of the query that was triggering the error, none of those options logged thevalues 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.
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
wantto 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
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.
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.