pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[pgjdbc/pgjdbc] issue #1136: Show SQL text (and/or binds) in SQLException message
4+ messages / 4 participants
[nested] [flat]
* [pgjdbc/pgjdbc] issue #1136: Show SQL text (and/or binds) in SQLException message
@ 2018-03-08 13:13 "vlsi (@vlsi)" <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: vlsi (@vlsi) @ 2018-03-08 13:13 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Here's how an SQLException looks like:
```
setEncodingAscii[allowEncodingChanges=true](org.postgresql.test.jdbc2.ClientEncodingTest) Time elapsed: 0.026 sec <<< ERROR!
org.postgresql.util.PSQLException: ERROR: syntax error at or near "x"
Position: 14
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:224)
at org.postgresql.test.jdbc2.ClientEncodingTest.checkConnectionSanity(ClientEncodingTest.java:73)
at org.postgresql.test.jdbc2.ClientEncodingTest.setEncodingAscii(ClientEncodingTest.java:68)
```
I wish the message did include the SQL in question.
It would be so much easier to debug in case the statement did include SQL text, etc.
What do you think?
Is there a security issue with that?
Can the feature be enabled by default?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #1136: Show SQL text (and/or binds) in SQLException message
@ 2019-07-24 01:46 "mshajarrazip (@mshajarrazip)" <[email protected]>
2 siblings, 0 replies; 4+ messages in thread
From: mshajarrazip (@mshajarrazip) @ 2019-07-24 01:46 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
The error message is parsed in `org/posgresql/util/ServerErrorMessage.java:146`
I wanted to include the SQL text somewhere after line 183
```
message = mesgParts.get(POSITION);
if (message != null) {
totalMessage.append("\n ").append("SQL text here"); //here
totalMessage.append("\n ").append("Pointer here ^"); //here
totalMessage.append("\n ").append(GT.tr("Position: {0}", message));
}
```
so it will print the SQL text with the position, like this:
```
org.postgresql.util.PSQLException: ERROR: testsyntax error at or near "x"
SQL text here
Pointer here ^
Position: 19
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2468)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2211)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:309)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:311)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:297)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:274)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:225)
at org.postgresql.test.jdbc2.ClientEncodingTest.checkConnectionSanity(ClientEncodingTest.java:73)
at org.postgresql.test.jdbc2.ClientEncodingTest.setEncodingAscii(ClientEncodingTest.java:68)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runners.Suite.runChild(Suite.java:128)
at org.junit.runners.Suite.runChild(Suite.java:27)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:89)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:41)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:541)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:763)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:463)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:209)
```
The server error message looks like this:
Received at `org/postgresql/core/v3/QueryExecutorImpl.java:2461` :
````
EncodingPredictor.DecodeResult totalMessage = pgStream.receiveErrorString(elen - 4);
ServerErrorMessage errorMsg = new ServerErrorMessage(totalMessage);
````
`totalMessage` is
```
SERROR VERROR C42601 Msyntax error at or near "x" P19 Fscan.I L1134 Rscanner_yyerror
```
in `errorMsg`, the value becomes
```
{P=19, R=scanner_yyerror, S=ERROR, C=42601, V=ERROR, F=scan.l, L=1134, M=syntax error at or near "x"}
```
Other details that the error message can include are as follows, in org/posgresql/util/ServerErrorMessage.java:20;
```
private static final Character SEVERITY = 'S';
private static final Character MESSAGE = 'M';
private static final Character DETAIL = 'D';
private static final Character HINT = 'H';
private static final Character POSITION = 'P';
private static final Character WHERE = 'W';
private static final Character FILE = 'F';
private static final Character LINE = 'L';
private static final Character ROUTINE = 'R';
private static final Character SQLSTATE = 'C';
private static final Character INTERNAL_POSITION = 'p';
private static final Character INTERNAL_QUERY = 'q';
private static final Character SCHEMA = 's';
private static final Character TABLE = 't';
private static final Character COLUMN = 'c';
private static final Character DATATYPE = 'd';
private static final Character CONSTRAINT = 'n';
```
**Are any of these mapped to the SQL text in question?**
Also, it says in `org/posgresql/util/ServerErrorMessage.java:146`:
```
public String toString() {
// Now construct the message from what the server sent
// The general format is:
// SEVERITY: Message \n
// Detail: \n
// Hint: \n
// Position: \n
// Where: \n
// Internal Query: \n
// Internal Position: \n
// Location: File:Line:Routine \n
// SQLState: \n
//
// Normally only the message and detail is included.
// If INFO level logging is enabled then detail, hint, position and where are
// included. If DEBUG level logging is enabled then all information
// is included.
```
I've set the `log_min_error_statement` and `log_min_messages` in `postgresql.conf` to `debug5`, and further set `loggerLevel` property to `DEBUG` in `logging.properties` and set `java.util.logging.config.file` to the `logging.properties` file, but the server error message is still `{P=19, R=scanner_yyerror, S=ERROR, C=42601, V=ERROR, F=scan.l, L=1134, M=syntax error at or near "x"}`. In this case, are more details not possible to be retrieved from the server?
Am I missing some steps?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #1136: Show SQL text (and/or binds) in SQLException message
@ 2019-07-24 05:06 "maimai3478 (@maimai3478)" <[email protected]>
2 siblings, 0 replies; 4+ messages in thread
From: maimai3478 (@maimai3478) @ 2019-07-24 05:06 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> In this case, are more details not possible to be retrieved from the server?
I saw the PostgreSQL codes and it seems me it only generate the error message like "syntax error at or near...". I couldn't find the logic which add more details to error message by the server settings during seeing codes.
That error is generated in during parsing SQL statement.
https://github.com/postgres/postgres/blob/7d81bdc8c0ce838efa248928065e9b2da829f981/src/backend/parse...
```
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
/* translator: first %s is typically the translation of "syntax error" */
errmsg("%s at or near \"%s\"", _(message), loc),
lexer_errposition()));
```
ereport() is defined like this.
https://github.com/postgres/postgres/blob/fd7d387e0548fd371c06a91d75bc4632541ccfdd/src/include/utils...
```
#ifdef HAVE__BUILTIN_CONSTANT_P
#define ereport_domain(elevel, domain, rest) \
do { \
pg_prevent_errno_in_scope(); \
if (errstart(elevel, __FILE__, __LINE__, PG_FUNCNAME_MACRO, domain)) \
errfinish rest; \
if (__builtin_constant_p(elevel) && (elevel) >= ERROR) \
pg_unreachable(); \
} while(0)
#else /* !HAVE__BUILTIN_CONSTANT_P */
#define ereport_domain(elevel, domain, rest) \
do { \
const int elevel_ = (elevel); \
pg_prevent_errno_in_scope(); \
if (errstart(elevel_, __FILE__, __LINE__, PG_FUNCNAME_MACRO, domain)) \
errfinish rest; \
if (elevel_ >= ERROR) \
pg_unreachable(); \
} while(0)
#endif /* HAVE__BUILTIN_CONSTANT_P */
#define ereport(elevel, rest) \
ereport_domain(elevel, TEXTDOMAIN, rest)
```
errstart() creates an error stack entry and store the given parameters in it.
https://github.com/postgres/postgres/blob/fd7d387e0548fd371c06a91d75bc4632541ccfdd/src/backend/utils...
```
bool
errstart(int elevel, const char *filename, int lineno,
const char *funcname, const char *domain)
{
...
/* Initialize data for this error frame */
edata = &errordata[errordata_stack_depth];
MemSet(edata, 0, sizeof(ErrorData));
edata->elevel = elevel;
edata->output_to_server = output_to_server;
edata->output_to_client = output_to_client;
```
The message "syntax error at or near..." is stored in stack entry at errmsg().
https://github.com/postgres/postgres/blob/fd7d387e0548fd371c06a91d75bc4632541ccfdd/src/backend/utils...
```
errmsg(const char *fmt,...)
{
...
edata->message_id = fmt;
```
errfinish() actually process the error report.
https://github.com/postgres/postgres/blob/fd7d387e0548fd371c06a91d75bc4632541ccfdd/src/backend/utils...
https://github.com/postgres/postgres/blob/fd7d387e0548fd371c06a91d75bc4632541ccfdd/src/backend/utils...
Process jump to Postgresmain and finally EmitErrorReport() will send the error message to client.
https://github.com/postgres/postgres/blob/fd7d387e0548fd371c06a91d75bc4632541ccfdd/src/backend/tcop/...
```
PostgresMain(int argc, char *argv[],
const char *dbname,
const char *username)
{
...
EmitErrorReport();
```
(I'm sorry for not knowing how to cite other repositories source code correctly...)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #1136: Show SQL text (and/or binds) in SQLException message
@ 2025-06-26 16:15 "nartamonov (@nartamonov)" <[email protected]>
2 siblings, 0 replies; 4+ messages in thread
From: nartamonov (@nartamonov) @ 2025-06-26 16:15 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
It would be super helpful! Really inconvenient when I get some obscure errors in logs, but can't find out what SQL query caused them.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-06-26 16:15 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-03-08 13:13 [pgjdbc/pgjdbc] issue #1136: Show SQL text (and/or binds) in SQLException message "vlsi (@vlsi)" <[email protected]>
2019-07-24 01:46 ` "mshajarrazip (@mshajarrazip)" <[email protected]>
2019-07-24 05:06 ` "maimai3478 (@maimai3478)" <[email protected]>
2025-06-26 16:15 ` "nartamonov (@nartamonov)" <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox