Thread: Re: [BUGS] BUG #4203: perform dblink() in begin/exception returns wrong SQLSTATE code
Re: [BUGS] BUG #4203: perform dblink() in begin/exception returns wrong SQLSTATE code
From
Joe Conway
Date:
Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>> Tom Lane wrote:
>>> Yeah, the dblink code should probably try a bit harder to propagate the
>>> original error fields.
>
>> Sounds reasonable. Do you think this is a bug fix or an 8.4 enhancement?
>
> 8.4 enhancement I think, since a behavioral change here could pose a
> compatibility issue for applications.
>
Here is my proposed patch -- as suggested for cvs tip only.
I haven't been around enough lately to be sure I understand the process
these days. Should I be posting this to the wiki and waiting for the
next commit fest, or just apply myself in a day or two assuming no
objections?
Thanks,
Joe
Index: dblink.c
===================================================================
RCS file: /opt/src/cvs/pgsql/contrib/dblink/dblink.c,v
retrieving revision 1.73
diff -c -r1.73 dblink.c
*** dblink.c 4 Apr 2008 17:02:56 -0000 1.73
--- dblink.c 1 Jun 2008 18:50:04 -0000
***************
*** 135,158 ****
#define DBLINK_RES_ERROR(p2) \
do { \
! msg = pstrdup(PQerrorMessage(conn)); \
if (res) \
PQclear(res); \
! ereport(ERROR, \
! (errcode(ERRCODE_SYNTAX_ERROR), \
! errmsg("%s", p2), \
! errdetail("%s", msg))); \
} while (0)
#define DBLINK_RES_ERROR_AS_NOTICE(p2) \
do { \
! msg = pstrdup(PQerrorMessage(conn)); \
if (res) \
PQclear(res); \
! ereport(NOTICE, \
! (errcode(ERRCODE_SYNTAX_ERROR), \
! errmsg("%s", p2), \
! errdetail("%s", msg))); \
} while (0)
#define DBLINK_CONN_NOT_AVAIL \
--- 135,182 ----
#define DBLINK_RES_ERROR(p2) \
do { \
! char *sqlstate = PQresultErrorField(res, PG_DIAG_SQLSTATE); \
! char *pg_diag_message_primary = PQresultErrorField(res, PG_DIAG_MESSAGE_PRIMARY); \
! char *pg_diag_message_detail = PQresultErrorField(res, PG_DIAG_MESSAGE_DETAIL); \
! char *pg_diag_message_hint = PQresultErrorField(res, PG_DIAG_MESSAGE_HINT); \
! char *pg_diag_context = PQresultErrorField(res, PG_DIAG_CONTEXT); \
if (res) \
PQclear(res); \
! errstart(ERROR, __FILE__, __LINE__, PG_FUNCNAME_MACRO); \
! errcode(MAKE_SQLSTATE(sqlstate[0],sqlstate[1],sqlstate[2],sqlstate[3],sqlstate[4])); \
! errmsg("%s", pg_diag_message_primary); \
! if (pg_diag_message_detail) \
! errdetail("%s", pg_diag_message_detail); \
! if (pg_diag_message_hint) \
! errhint("%s", pg_diag_message_hint); \
! if (pg_diag_context) \
! errcontext("%s", pg_diag_context); \
! if (p2) \
! errcontext("error on dblink connection: %s", p2); \
! errfinish(0); \
} while (0)
#define DBLINK_RES_ERROR_AS_NOTICE(p2) \
do { \
! char *sqlstate = PQresultErrorField(res, PG_DIAG_SQLSTATE); \
! char *pg_diag_message_primary = PQresultErrorField(res, PG_DIAG_MESSAGE_PRIMARY); \
! char *pg_diag_message_detail = PQresultErrorField(res, PG_DIAG_MESSAGE_DETAIL); \
! char *pg_diag_message_hint = PQresultErrorField(res, PG_DIAG_MESSAGE_HINT); \
! char *pg_diag_context = PQresultErrorField(res, PG_DIAG_CONTEXT); \
if (res) \
PQclear(res); \
! errstart(NOTICE, __FILE__, __LINE__, PG_FUNCNAME_MACRO); \
! errcode(MAKE_SQLSTATE(sqlstate[0],sqlstate[1],sqlstate[2],sqlstate[3],sqlstate[4])); \
! errmsg("%s", pg_diag_message_primary); \
! if (pg_diag_message_detail) \
! errdetail("%s", pg_diag_message_detail); \
! if (pg_diag_message_hint) \
! errhint("%s", pg_diag_message_hint); \
! if (pg_diag_context) \
! errcontext("%s", pg_diag_context); \
! if (p2) \
! errcontext("error on dblink connection: %s", p2); \
! errfinish(0); \
} while (0)
#define DBLINK_CONN_NOT_AVAIL \
***************
*** 397,406 ****
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
if (fail)
! DBLINK_RES_ERROR("sql error");
else
{
! DBLINK_RES_ERROR_AS_NOTICE("sql error");
PG_RETURN_TEXT_P(cstring_to_text("ERROR"));
}
}
--- 421,430 ----
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
if (fail)
! DBLINK_RES_ERROR("unable to open cursor");
else
{
! DBLINK_RES_ERROR_AS_NOTICE("unable to open cursor");
PG_RETURN_TEXT_P(cstring_to_text("ERROR"));
}
}
***************
*** 471,480 ****
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
if (fail)
! DBLINK_RES_ERROR("sql error");
else
{
! DBLINK_RES_ERROR_AS_NOTICE("sql error");
PG_RETURN_TEXT_P(cstring_to_text("ERROR"));
}
}
--- 495,504 ----
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
if (fail)
! DBLINK_RES_ERROR("unable to close cursor");
else
{
! DBLINK_RES_ERROR_AS_NOTICE("unable to close cursor");
PG_RETURN_TEXT_P(cstring_to_text("ERROR"));
}
}
***************
*** 513,519 ****
int call_cntr;
int max_calls;
AttInMetadata *attinmeta;
- char *msg;
PGresult *res = NULL;
MemoryContext oldcontext;
char *conname = NULL;
--- 537,542 ----
***************
*** 591,600 ****
PQresultStatus(res) != PGRES_TUPLES_OK))
{
if (fail)
! DBLINK_RES_ERROR("sql error");
else
{
! DBLINK_RES_ERROR_AS_NOTICE("sql error");
SRF_RETURN_DONE(funcctx);
}
}
--- 614,623 ----
PQresultStatus(res) != PGRES_TUPLES_OK))
{
if (fail)
! DBLINK_RES_ERROR("unable to fetch from cursor");
else
{
! DBLINK_RES_ERROR_AS_NOTICE("unable to fetch from cursor");
SRF_RETURN_DONE(funcctx);
}
}
***************
*** 847,856 ****
PQresultStatus(res) != PGRES_TUPLES_OK))
{
if (fail)
! DBLINK_RES_ERROR("sql error");
else
{
! DBLINK_RES_ERROR_AS_NOTICE("sql error");
if (freeconn)
PQfinish(conn);
SRF_RETURN_DONE(funcctx);
--- 870,879 ----
PQresultStatus(res) != PGRES_TUPLES_OK))
{
if (fail)
! DBLINK_RES_ERROR("unable to execute query");
else
{
! DBLINK_RES_ERROR_AS_NOTICE("unable to execute query");
if (freeconn)
PQfinish(conn);
SRF_RETURN_DONE(funcctx);
***************
*** 1181,1189 ****
PQresultStatus(res) != PGRES_TUPLES_OK))
{
if (fail)
! DBLINK_RES_ERROR("sql error");
else
! DBLINK_RES_ERROR_AS_NOTICE("sql error");
/* need a tuple descriptor representing one TEXT column */
tupdesc = CreateTemplateTupleDesc(1, false);
--- 1204,1212 ----
PQresultStatus(res) != PGRES_TUPLES_OK))
{
if (fail)
! DBLINK_RES_ERROR("unable to execute command");
else
! DBLINK_RES_ERROR_AS_NOTICE("unable to execute command");
/* need a tuple descriptor representing one TEXT column */
tupdesc = CreateTemplateTupleDesc(1, false);
Index: expected/dblink.out
===================================================================
RCS file: /opt/src/cvs/pgsql/contrib/dblink/expected/dblink.out,v
retrieving revision 1.23
diff -c -r1.23 dblink.out
*** expected/dblink.out 6 Apr 2008 16:54:48 -0000 1.23
--- expected/dblink.out 1 Jun 2008 18:53:06 -0000
***************
*** 125,133 ****
-- open a cursor with bad SQL and fail_on_error set to false
SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foobar',false);
! NOTICE: sql error
! DETAIL: ERROR: relation "foobar" does not exist
!
dblink_open
-------------
ERROR
--- 125,132 ----
-- open a cursor with bad SQL and fail_on_error set to false
SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foobar',false);
! NOTICE: relation "foobar" does not exist
! CONTEXT: error on dblink connection: unable to open cursor
dblink_open
-------------
ERROR
***************
*** 194,202 ****
-- intentionally botch a fetch
SELECT *
FROM dblink_fetch('rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]);
! NOTICE: sql error
! DETAIL: ERROR: cursor "rmt_foobar_cursor" does not exist
!
a | b | c
---+---+---
(0 rows)
--- 193,200 ----
-- intentionally botch a fetch
SELECT *
FROM dblink_fetch('rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]);
! NOTICE: cursor "rmt_foobar_cursor" does not exist
! CONTEXT: error on dblink connection: unable to fetch from cursor
a | b | c
---+---+---
(0 rows)
***************
*** 210,218 ****
-- close the wrong cursor
SELECT dblink_close('rmt_foobar_cursor',false);
! NOTICE: sql error
! DETAIL: ERROR: cursor "rmt_foobar_cursor" does not exist
!
dblink_close
--------------
ERROR
--- 208,215 ----
-- close the wrong cursor
SELECT dblink_close('rmt_foobar_cursor',false);
! NOTICE: cursor "rmt_foobar_cursor" does not exist
! CONTEXT: error on dblink connection: unable to close cursor
dblink_close
--------------
ERROR
***************
*** 221,235 ****
-- should generate 'cursor "rmt_foo_cursor" not found' error
SELECT *
FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! ERROR: sql error
! DETAIL: ERROR: cursor "rmt_foo_cursor" does not exist
!
-- this time, 'cursor "rmt_foo_cursor" not found' as a notice
SELECT *
FROM dblink_fetch('rmt_foo_cursor',4,false) AS t(a int, b text, c text[]);
! NOTICE: sql error
! DETAIL: ERROR: cursor "rmt_foo_cursor" does not exist
!
a | b | c
---+---+---
(0 rows)
--- 218,230 ----
-- should generate 'cursor "rmt_foo_cursor" not found' error
SELECT *
FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! ERROR: cursor "rmt_foo_cursor" does not exist
! CONTEXT: error on dblink connection: unable to fetch from cursor
-- this time, 'cursor "rmt_foo_cursor" not found' as a notice
SELECT *
FROM dblink_fetch('rmt_foo_cursor',4,false) AS t(a int, b text, c text[]);
! NOTICE: cursor "rmt_foo_cursor" does not exist
! CONTEXT: error on dblink connection: unable to fetch from cursor
a | b | c
---+---+---
(0 rows)
***************
*** 291,299 ****
-- bad remote select
SELECT *
FROM dblink('SELECT * FROM foobar',false) AS t(a int, b text, c text[]);
! NOTICE: sql error
! DETAIL: ERROR: relation "foobar" does not exist
!
a | b | c
---+---+---
(0 rows)
--- 286,293 ----
-- bad remote select
SELECT *
FROM dblink('SELECT * FROM foobar',false) AS t(a int, b text, c text[]);
! NOTICE: relation "foobar" does not exist
! CONTEXT: error on dblink connection: unable to execute query
a | b | c
---+---+---
(0 rows)
***************
*** 316,324 ****
-- botch a change to some other data
SELECT dblink_exec('UPDATE foobar SET f3[2] = ''b99'' WHERE f1 = 11',false);
! NOTICE: sql error
! DETAIL: ERROR: relation "foobar" does not exist
!
dblink_exec
-------------
ERROR
--- 310,317 ----
-- botch a change to some other data
SELECT dblink_exec('UPDATE foobar SET f3[2] = ''b99'' WHERE f1 = 11',false);
! NOTICE: relation "foobar" does not exist
! CONTEXT: error on dblink connection: unable to execute command
dblink_exec
-------------
ERROR
***************
*** 378,386 ****
SELECT *
FROM dblink('myconn','SELECT * FROM foobar',false) AS t(a int, b text, c text[])
WHERE t.a > 7;
! NOTICE: sql error
! DETAIL: ERROR: relation "foobar" does not exist
!
a | b | c
---+---+---
(0 rows)
--- 371,378 ----
SELECT *
FROM dblink('myconn','SELECT * FROM foobar',false) AS t(a int, b text, c text[])
WHERE t.a > 7;
! NOTICE: relation "foobar" does not exist
! CONTEXT: error on dblink connection: unable to execute query
a | b | c
---+---+---
(0 rows)
***************
*** 416,424 ****
-- open a cursor incorrectly
SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foobar',false);
! NOTICE: sql error
! DETAIL: ERROR: relation "foobar" does not exist
!
dblink_open
-------------
ERROR
--- 408,415 ----
-- open a cursor incorrectly
SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foobar',false);
! NOTICE: relation "foobar" does not exist
! CONTEXT: error on dblink connection: unable to open cursor
dblink_open
-------------
ERROR
***************
*** 503,511 ****
-- this should fail because there is no open transaction
SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM foo');
! ERROR: sql error
! DETAIL: ERROR: DECLARE CURSOR can only be used in transaction blocks
!
-- reset remote transaction state
SELECT dblink_exec('myconn','ABORT');
dblink_exec
--- 494,501 ----
-- this should fail because there is no open transaction
SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM foo');
! ERROR: DECLARE CURSOR can only be used in transaction blocks
! CONTEXT: error on dblink connection: unable to execute command
-- reset remote transaction state
SELECT dblink_exec('myconn','ABORT');
dblink_exec
***************
*** 554,562 ****
-- fetch some data incorrectly
SELECT *
FROM dblink_fetch('myconn','rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]);
! NOTICE: sql error
! DETAIL: ERROR: cursor "rmt_foobar_cursor" does not exist
!
a | b | c
---+---+---
(0 rows)
--- 544,551 ----
-- fetch some data incorrectly
SELECT *
FROM dblink_fetch('myconn','rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]);
! NOTICE: cursor "rmt_foobar_cursor" does not exist
! CONTEXT: error on dblink connection: unable to fetch from cursor
a | b | c
---+---+---
(0 rows)
***************
*** 571,579 ****
-- should generate 'cursor "rmt_foo_cursor" not found' error
SELECT *
FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! ERROR: sql error
! DETAIL: ERROR: cursor "rmt_foo_cursor" does not exist
!
-- close the named persistent connection
SELECT dblink_disconnect('myconn');
dblink_disconnect
--- 560,567 ----
-- should generate 'cursor "rmt_foo_cursor" not found' error
SELECT *
FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! ERROR: cursor "rmt_foo_cursor" does not exist
! CONTEXT: error on dblink connection: unable to fetch from cursor
-- close the named persistent connection
SELECT dblink_disconnect('myconn');
dblink_disconnect
Re: [BUGS] BUG #4203: perform dblink() in begin/exception returns wrong SQLSTATE code
From
Tom Lane
Date:
Joe Conway <mail@joeconway.com> writes:
> Here is my proposed patch -- as suggested for cvs tip only.
A few comments:
Don't use errstart/errfinish directly. A reasonable way to deal with
the type of situation you have here is
ereport(ERROR,
(errcode(...),
errmsg(...),
det_msg ? errdetail("%s", det_msg) : 0,
hint_msg ? errhint("%s", hint_msg) : 0,
...));
You can't expect the result of PQresultErrorField to still be valid
after you've PQclear'd the PGresult. I think you'll need to pstrdup
the non-null results first. Or maybe use a PG_TRY block to free the
PGresult on the way out after the error escape ... but pstrdup is
probably cleaner.
This code doesn't cope with the possibility that no SQLSTATE
is available (a distinct possibility for libpq-detected errors).
You'll need to use some generic error code in that case. I'm tempted
to suggest ERRCODE_CONNECTION_FAILURE, on the assumption that if it's
libpq-detected then it's a connection problem.
It would probably be useful to show the name of the dblink connection
in the context.
I'm thinking that you are getting well past what is reasonable to
put in a macro. Time to use an out-of-line function.
Don't use "unable to..." --- this is against the message style guide.
"could not" is approved style. Also note the expectation that context
entries be complete sentences.
> I haven't been around enough lately to be sure I understand the process
> these days. Should I be posting this to the wiki and waiting for the
> next commit fest, or just apply myself in a day or two assuming no
> objections?
No, you can apply it yourself when you feel it's ready. The wiki queue
is just to keep track of stuff that is submitted by non-committers or
that a committer wants extra review of.
regards, tom lane
Re: [BUGS] BUG #4203: perform dblink() in begin/exception returns wrong SQLSTATE code
From
Joe Conway
Date:
Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>> Here is my proposed patch -- as suggested for cvs tip only.
>
> A few comments:
[...great comments as usual...]
Thanks for the excellent feedback! I think the attached addresses it all.
>> I haven't been around enough lately to be sure I understand the process
>> these days. Should I be posting this to the wiki and waiting for the
>> next commit fest, or just apply myself in a day or two assuming no
>> objections?
>
> No, you can apply it yourself when you feel it's ready. The wiki queue
> is just to keep track of stuff that is submitted by non-committers or
> that a committer wants extra review of.
Great. Assuming no other objections I'll commit in a day or three.
Joe
Index: dblink.c
===================================================================
RCS file: /opt/src/cvs/pgsql/contrib/dblink/dblink.c,v
retrieving revision 1.73
diff -c -r1.73 dblink.c
*** dblink.c 4 Apr 2008 17:02:56 -0000 1.73
--- dblink.c 1 Jun 2008 21:58:46 -0000
***************
*** 94,99 ****
--- 94,100 ----
static Oid get_relid_from_relname(text *relname_text);
static char *generate_relation_name(Oid relid);
static void dblink_security_check(PGconn *conn, remoteConn *rconn);
+ static void dblink_res_error(const char *conname, PGresult *res, const char *dblink_context_msg, bool fail);
/* Global */
static remoteConn *pconn = NULL;
***************
*** 125,158 ****
} \
} while (0)
! #define DBLINK_RES_INTERNALERROR(p2) \
! do { \
! msg = pstrdup(PQerrorMessage(conn)); \
! if (res) \
! PQclear(res); \
! elog(ERROR, "%s: %s", p2, msg); \
! } while (0)
!
! #define DBLINK_RES_ERROR(p2) \
do { \
! msg = pstrdup(PQerrorMessage(conn)); \
! if (res) \
! PQclear(res); \
! ereport(ERROR, \
! (errcode(ERRCODE_SYNTAX_ERROR), \
! errmsg("%s", p2), \
! errdetail("%s", msg))); \
} while (0)
! #define DBLINK_RES_ERROR_AS_NOTICE(p2) \
do { \
msg = pstrdup(PQerrorMessage(conn)); \
if (res) \
PQclear(res); \
! ereport(NOTICE, \
! (errcode(ERRCODE_SYNTAX_ERROR), \
! errmsg("%s", p2), \
! errdetail("%s", msg))); \
} while (0)
#define DBLINK_CONN_NOT_AVAIL \
--- 126,145 ----
} \
} while (0)
! #define xpstrdup(var_c, var_) \
do { \
! if (var_ != NULL) \
! var_c = pstrdup(var_); \
! else \
! var_c = NULL; \
} while (0)
! #define DBLINK_RES_INTERNALERROR(p2) \
do { \
msg = pstrdup(PQerrorMessage(conn)); \
if (res) \
PQclear(res); \
! elog(ERROR, "%s: %s", p2, msg); \
} while (0)
#define DBLINK_CONN_NOT_AVAIL \
***************
*** 396,408 ****
res = PQexec(conn, buf.data);
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
! if (fail)
! DBLINK_RES_ERROR("sql error");
! else
! {
! DBLINK_RES_ERROR_AS_NOTICE("sql error");
PG_RETURN_TEXT_P(cstring_to_text("ERROR"));
- }
}
PQclear(res);
--- 383,391 ----
res = PQexec(conn, buf.data);
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
! dblink_res_error(conname, res, "could not open cursor", fail);
! if (!fail)
PG_RETURN_TEXT_P(cstring_to_text("ERROR"));
}
PQclear(res);
***************
*** 470,482 ****
res = PQexec(conn, buf.data);
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
! if (fail)
! DBLINK_RES_ERROR("sql error");
! else
! {
! DBLINK_RES_ERROR_AS_NOTICE("sql error");
PG_RETURN_TEXT_P(cstring_to_text("ERROR"));
- }
}
PQclear(res);
--- 453,461 ----
res = PQexec(conn, buf.data);
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
! dblink_res_error(conname, res, "could not close cursor", fail);
! if (!fail)
PG_RETURN_TEXT_P(cstring_to_text("ERROR"));
}
PQclear(res);
***************
*** 513,519 ****
int call_cntr;
int max_calls;
AttInMetadata *attinmeta;
- char *msg;
PGresult *res = NULL;
MemoryContext oldcontext;
char *conname = NULL;
--- 492,497 ----
***************
*** 590,602 ****
(PQresultStatus(res) != PGRES_COMMAND_OK &&
PQresultStatus(res) != PGRES_TUPLES_OK))
{
! if (fail)
! DBLINK_RES_ERROR("sql error");
! else
! {
! DBLINK_RES_ERROR_AS_NOTICE("sql error");
SRF_RETURN_DONE(funcctx);
- }
}
else if (PQresultStatus(res) == PGRES_COMMAND_OK)
{
--- 568,576 ----
(PQresultStatus(res) != PGRES_COMMAND_OK &&
PQresultStatus(res) != PGRES_TUPLES_OK))
{
! dblink_res_error(conname, res, "could not fetch from cursor", fail);
! if (!fail)
SRF_RETURN_DONE(funcctx);
}
else if (PQresultStatus(res) == PGRES_COMMAND_OK)
{
***************
*** 846,856 ****
(PQresultStatus(res) != PGRES_COMMAND_OK &&
PQresultStatus(res) != PGRES_TUPLES_OK))
{
! if (fail)
! DBLINK_RES_ERROR("sql error");
! else
{
- DBLINK_RES_ERROR_AS_NOTICE("sql error");
if (freeconn)
PQfinish(conn);
SRF_RETURN_DONE(funcctx);
--- 820,828 ----
(PQresultStatus(res) != PGRES_COMMAND_OK &&
PQresultStatus(res) != PGRES_TUPLES_OK))
{
! dblink_res_error(conname, res, "could not execute query", fail);
! if (!fail)
{
if (freeconn)
PQfinish(conn);
SRF_RETURN_DONE(funcctx);
***************
*** 1180,1201 ****
(PQresultStatus(res) != PGRES_COMMAND_OK &&
PQresultStatus(res) != PGRES_TUPLES_OK))
{
! if (fail)
! DBLINK_RES_ERROR("sql error");
! else
! DBLINK_RES_ERROR_AS_NOTICE("sql error");
!
! /* need a tuple descriptor representing one TEXT column */
! tupdesc = CreateTemplateTupleDesc(1, false);
! TupleDescInitEntry(tupdesc, (AttrNumber) 1, "status",
! TEXTOID, -1, 0);
! /*
! * and save a copy of the command status string to return as our
! * result tuple
! */
! sql_cmd_status = cstring_to_text("ERROR");
}
else if (PQresultStatus(res) == PGRES_COMMAND_OK)
{
--- 1152,1172 ----
(PQresultStatus(res) != PGRES_COMMAND_OK &&
PQresultStatus(res) != PGRES_TUPLES_OK))
{
! dblink_res_error(conname, res, "could not execute command", fail);
! if (!fail)
! {
! /* need a tuple descriptor representing one TEXT column */
! tupdesc = CreateTemplateTupleDesc(1, false);
! TupleDescInitEntry(tupdesc, (AttrNumber) 1, "status",
! TEXTOID, -1, 0);
+ /*
+ * and save a copy of the command status string to return as our
+ * result tuple
+ */
+ sql_cmd_status = cstring_to_text("ERROR");
+ }
}
else if (PQresultStatus(res) == PGRES_COMMAND_OK)
{
***************
*** 2288,2290 ****
--- 2259,2312 ----
}
}
}
+
+ static void
+ dblink_res_error(const char *conname, PGresult *res, const char *dblink_context_msg, bool fail)
+ {
+ int level;
+ char *pg_diag_sqlstate = PQresultErrorField(res, PG_DIAG_SQLSTATE);
+ char *pg_diag_message_primary = PQresultErrorField(res, PG_DIAG_MESSAGE_PRIMARY);
+ char *pg_diag_message_detail = PQresultErrorField(res, PG_DIAG_MESSAGE_DETAIL);
+ char *pg_diag_message_hint = PQresultErrorField(res, PG_DIAG_MESSAGE_HINT);
+ char *pg_diag_context = PQresultErrorField(res, PG_DIAG_CONTEXT);
+ int sqlstate;
+ char *message_primary;
+ char *message_detail;
+ char *message_hint;
+ char *message_context;
+ char *dblink_context_conname = "unnamed";
+
+ if (fail)
+ level = ERROR;
+ else
+ level = NOTICE;
+
+ if (pg_diag_sqlstate)
+ sqlstate = MAKE_SQLSTATE(pg_diag_sqlstate[0],
+ pg_diag_sqlstate[1],
+ pg_diag_sqlstate[2],
+ pg_diag_sqlstate[3],
+ pg_diag_sqlstate[4]);
+ else
+ sqlstate = ERRCODE_CONNECTION_FAILURE;
+
+ xpstrdup(message_primary, pg_diag_message_primary);
+ xpstrdup(message_detail, pg_diag_message_detail);
+ xpstrdup(message_hint, pg_diag_message_hint);
+ xpstrdup(message_context, pg_diag_context);
+
+ if (res)
+ PQclear(res);
+
+ if (conname)
+ dblink_context_conname = (char *) conname;
+
+ ereport(level,
+ (errcode(sqlstate),
+ errmsg(message_primary),
+ message_detail ? errdetail("%s", message_detail) : 0,
+ message_hint ? errhint("%s", message_hint) : 0,
+ message_context ? errcontext("%s", message_context) : 0,
+ errcontext("Error occurred on dblink connection named \"%s\": %s.",
+ dblink_context_conname, dblink_context_msg)));
+ }
Index: expected/dblink.out
===================================================================
RCS file: /opt/src/cvs/pgsql/contrib/dblink/expected/dblink.out,v
retrieving revision 1.23
diff -c -r1.23 dblink.out
*** expected/dblink.out 6 Apr 2008 16:54:48 -0000 1.23
--- expected/dblink.out 1 Jun 2008 21:59:11 -0000
***************
*** 125,133 ****
-- open a cursor with bad SQL and fail_on_error set to false
SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foobar',false);
! NOTICE: sql error
! DETAIL: ERROR: relation "foobar" does not exist
!
dblink_open
-------------
ERROR
--- 125,132 ----
-- open a cursor with bad SQL and fail_on_error set to false
SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foobar',false);
! NOTICE: relation "foobar" does not exist
! CONTEXT: Error occurred on dblink connection named "unnamed": could not open cursor.
dblink_open
-------------
ERROR
***************
*** 194,202 ****
-- intentionally botch a fetch
SELECT *
FROM dblink_fetch('rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]);
! NOTICE: sql error
! DETAIL: ERROR: cursor "rmt_foobar_cursor" does not exist
!
a | b | c
---+---+---
(0 rows)
--- 193,200 ----
-- intentionally botch a fetch
SELECT *
FROM dblink_fetch('rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]);
! NOTICE: cursor "rmt_foobar_cursor" does not exist
! CONTEXT: Error occurred on dblink connection named "unnamed": could not fetch from cursor.
a | b | c
---+---+---
(0 rows)
***************
*** 210,218 ****
-- close the wrong cursor
SELECT dblink_close('rmt_foobar_cursor',false);
! NOTICE: sql error
! DETAIL: ERROR: cursor "rmt_foobar_cursor" does not exist
!
dblink_close
--------------
ERROR
--- 208,215 ----
-- close the wrong cursor
SELECT dblink_close('rmt_foobar_cursor',false);
! NOTICE: cursor "rmt_foobar_cursor" does not exist
! CONTEXT: Error occurred on dblink connection named "unnamed": could not close cursor.
dblink_close
--------------
ERROR
***************
*** 221,235 ****
-- should generate 'cursor "rmt_foo_cursor" not found' error
SELECT *
FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! ERROR: sql error
! DETAIL: ERROR: cursor "rmt_foo_cursor" does not exist
!
-- this time, 'cursor "rmt_foo_cursor" not found' as a notice
SELECT *
FROM dblink_fetch('rmt_foo_cursor',4,false) AS t(a int, b text, c text[]);
! NOTICE: sql error
! DETAIL: ERROR: cursor "rmt_foo_cursor" does not exist
!
a | b | c
---+---+---
(0 rows)
--- 218,230 ----
-- should generate 'cursor "rmt_foo_cursor" not found' error
SELECT *
FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! ERROR: cursor "rmt_foo_cursor" does not exist
! CONTEXT: Error occurred on dblink connection named "unnamed": could not fetch from cursor.
-- this time, 'cursor "rmt_foo_cursor" not found' as a notice
SELECT *
FROM dblink_fetch('rmt_foo_cursor',4,false) AS t(a int, b text, c text[]);
! NOTICE: cursor "rmt_foo_cursor" does not exist
! CONTEXT: Error occurred on dblink connection named "unnamed": could not fetch from cursor.
a | b | c
---+---+---
(0 rows)
***************
*** 291,299 ****
-- bad remote select
SELECT *
FROM dblink('SELECT * FROM foobar',false) AS t(a int, b text, c text[]);
! NOTICE: sql error
! DETAIL: ERROR: relation "foobar" does not exist
!
a | b | c
---+---+---
(0 rows)
--- 286,293 ----
-- bad remote select
SELECT *
FROM dblink('SELECT * FROM foobar',false) AS t(a int, b text, c text[]);
! NOTICE: relation "foobar" does not exist
! CONTEXT: Error occurred on dblink connection named "unnamed": could not execute query.
a | b | c
---+---+---
(0 rows)
***************
*** 316,324 ****
-- botch a change to some other data
SELECT dblink_exec('UPDATE foobar SET f3[2] = ''b99'' WHERE f1 = 11',false);
! NOTICE: sql error
! DETAIL: ERROR: relation "foobar" does not exist
!
dblink_exec
-------------
ERROR
--- 310,317 ----
-- botch a change to some other data
SELECT dblink_exec('UPDATE foobar SET f3[2] = ''b99'' WHERE f1 = 11',false);
! NOTICE: relation "foobar" does not exist
! CONTEXT: Error occurred on dblink connection named "unnamed": could not execute command.
dblink_exec
-------------
ERROR
***************
*** 378,386 ****
SELECT *
FROM dblink('myconn','SELECT * FROM foobar',false) AS t(a int, b text, c text[])
WHERE t.a > 7;
! NOTICE: sql error
! DETAIL: ERROR: relation "foobar" does not exist
!
a | b | c
---+---+---
(0 rows)
--- 371,378 ----
SELECT *
FROM dblink('myconn','SELECT * FROM foobar',false) AS t(a int, b text, c text[])
WHERE t.a > 7;
! NOTICE: relation "foobar" does not exist
! CONTEXT: Error occurred on dblink connection named "unnamed": could not execute query.
a | b | c
---+---+---
(0 rows)
***************
*** 416,424 ****
-- open a cursor incorrectly
SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foobar',false);
! NOTICE: sql error
! DETAIL: ERROR: relation "foobar" does not exist
!
dblink_open
-------------
ERROR
--- 408,415 ----
-- open a cursor incorrectly
SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foobar',false);
! NOTICE: relation "foobar" does not exist
! CONTEXT: Error occurred on dblink connection named "myconn": could not open cursor.
dblink_open
-------------
ERROR
***************
*** 503,511 ****
-- this should fail because there is no open transaction
SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM foo');
! ERROR: sql error
! DETAIL: ERROR: DECLARE CURSOR can only be used in transaction blocks
!
-- reset remote transaction state
SELECT dblink_exec('myconn','ABORT');
dblink_exec
--- 494,501 ----
-- this should fail because there is no open transaction
SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM foo');
! ERROR: DECLARE CURSOR can only be used in transaction blocks
! CONTEXT: Error occurred on dblink connection named "unnamed": could not execute command.
-- reset remote transaction state
SELECT dblink_exec('myconn','ABORT');
dblink_exec
***************
*** 554,562 ****
-- fetch some data incorrectly
SELECT *
FROM dblink_fetch('myconn','rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]);
! NOTICE: sql error
! DETAIL: ERROR: cursor "rmt_foobar_cursor" does not exist
!
a | b | c
---+---+---
(0 rows)
--- 544,551 ----
-- fetch some data incorrectly
SELECT *
FROM dblink_fetch('myconn','rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]);
! NOTICE: cursor "rmt_foobar_cursor" does not exist
! CONTEXT: Error occurred on dblink connection named "myconn": could not fetch from cursor.
a | b | c
---+---+---
(0 rows)
***************
*** 571,579 ****
-- should generate 'cursor "rmt_foo_cursor" not found' error
SELECT *
FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! ERROR: sql error
! DETAIL: ERROR: cursor "rmt_foo_cursor" does not exist
!
-- close the named persistent connection
SELECT dblink_disconnect('myconn');
dblink_disconnect
--- 560,567 ----
-- should generate 'cursor "rmt_foo_cursor" not found' error
SELECT *
FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! ERROR: cursor "rmt_foo_cursor" does not exist
! CONTEXT: Error occurred on dblink connection named "myconn": could not fetch from cursor.
-- close the named persistent connection
SELECT dblink_disconnect('myconn');
dblink_disconnect
Re: [BUGS] BUG #4203: perform dblink() in begin/exception returns wrong SQLSTATE code
From
Tom Lane
Date:
Joe Conway <mail@joeconway.com> writes:
> [ improved patch ]
Still a couple quibbles:
> + ereport(level,
> + (errcode(sqlstate),
> + errmsg(message_primary),
This *must* be errmsg("%s", message_primary), else you have big problems
with % in the text. Also, I think it's at least theoretically possible
for message_primary to be null, in which case you'd better substitute
"unknown error" or some such.
You could avoid the ugly cast-away-const by making
dblink_context_conname be const char *, no?
Since dblink_res_error isn't going to return if fail = true, seems
like you could skip the "if (!fail)" tests occurring after calls to it.
regards, tom lane
Re: [BUGS] BUG #4203: perform dblink() in begin/exception returns wrong SQLSTATE code
From
Joe Conway
Date:
Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>> [ improved patch ]
>
> Still a couple quibbles:
[ more good feedback ]
All valid complaints, and noticeably improved/simplified code as a
result. Third patch attached.
Joe
Index: dblink.c
===================================================================
RCS file: /opt/src/cvs/pgsql/contrib/dblink/dblink.c,v
retrieving revision 1.73
diff -c -r1.73 dblink.c
*** dblink.c 4 Apr 2008 17:02:56 -0000 1.73
--- dblink.c 1 Jun 2008 23:52:39 -0000
***************
*** 94,99 ****
--- 94,100 ----
static Oid get_relid_from_relname(text *relname_text);
static char *generate_relation_name(Oid relid);
static void dblink_security_check(PGconn *conn, remoteConn *rconn);
+ static void dblink_res_error(const char *conname, PGresult *res, const char *dblink_context_msg, bool fail);
/* Global */
static remoteConn *pconn = NULL;
***************
*** 125,158 ****
} \
} while (0)
! #define DBLINK_RES_INTERNALERROR(p2) \
! do { \
! msg = pstrdup(PQerrorMessage(conn)); \
! if (res) \
! PQclear(res); \
! elog(ERROR, "%s: %s", p2, msg); \
! } while (0)
!
! #define DBLINK_RES_ERROR(p2) \
do { \
! msg = pstrdup(PQerrorMessage(conn)); \
! if (res) \
! PQclear(res); \
! ereport(ERROR, \
! (errcode(ERRCODE_SYNTAX_ERROR), \
! errmsg("%s", p2), \
! errdetail("%s", msg))); \
} while (0)
! #define DBLINK_RES_ERROR_AS_NOTICE(p2) \
do { \
msg = pstrdup(PQerrorMessage(conn)); \
if (res) \
PQclear(res); \
! ereport(NOTICE, \
! (errcode(ERRCODE_SYNTAX_ERROR), \
! errmsg("%s", p2), \
! errdetail("%s", msg))); \
} while (0)
#define DBLINK_CONN_NOT_AVAIL \
--- 126,145 ----
} \
} while (0)
! #define xpstrdup(var_c, var_) \
do { \
! if (var_ != NULL) \
! var_c = pstrdup(var_); \
! else \
! var_c = NULL; \
} while (0)
! #define DBLINK_RES_INTERNALERROR(p2) \
do { \
msg = pstrdup(PQerrorMessage(conn)); \
if (res) \
PQclear(res); \
! elog(ERROR, "%s: %s", p2, msg); \
} while (0)
#define DBLINK_CONN_NOT_AVAIL \
***************
*** 396,408 ****
res = PQexec(conn, buf.data);
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
! if (fail)
! DBLINK_RES_ERROR("sql error");
! else
! {
! DBLINK_RES_ERROR_AS_NOTICE("sql error");
! PG_RETURN_TEXT_P(cstring_to_text("ERROR"));
! }
}
PQclear(res);
--- 383,390 ----
res = PQexec(conn, buf.data);
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
! dblink_res_error(conname, res, "could not open cursor", fail);
! PG_RETURN_TEXT_P(cstring_to_text("ERROR"));
}
PQclear(res);
***************
*** 470,482 ****
res = PQexec(conn, buf.data);
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
! if (fail)
! DBLINK_RES_ERROR("sql error");
! else
! {
! DBLINK_RES_ERROR_AS_NOTICE("sql error");
! PG_RETURN_TEXT_P(cstring_to_text("ERROR"));
! }
}
PQclear(res);
--- 452,459 ----
res = PQexec(conn, buf.data);
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
! dblink_res_error(conname, res, "could not close cursor", fail);
! PG_RETURN_TEXT_P(cstring_to_text("ERROR"));
}
PQclear(res);
***************
*** 513,519 ****
int call_cntr;
int max_calls;
AttInMetadata *attinmeta;
- char *msg;
PGresult *res = NULL;
MemoryContext oldcontext;
char *conname = NULL;
--- 490,495 ----
***************
*** 590,602 ****
(PQresultStatus(res) != PGRES_COMMAND_OK &&
PQresultStatus(res) != PGRES_TUPLES_OK))
{
! if (fail)
! DBLINK_RES_ERROR("sql error");
! else
! {
! DBLINK_RES_ERROR_AS_NOTICE("sql error");
! SRF_RETURN_DONE(funcctx);
! }
}
else if (PQresultStatus(res) == PGRES_COMMAND_OK)
{
--- 566,573 ----
(PQresultStatus(res) != PGRES_COMMAND_OK &&
PQresultStatus(res) != PGRES_TUPLES_OK))
{
! dblink_res_error(conname, res, "could not fetch from cursor", fail);
! SRF_RETURN_DONE(funcctx);
}
else if (PQresultStatus(res) == PGRES_COMMAND_OK)
{
***************
*** 846,860 ****
(PQresultStatus(res) != PGRES_COMMAND_OK &&
PQresultStatus(res) != PGRES_TUPLES_OK))
{
! if (fail)
! DBLINK_RES_ERROR("sql error");
! else
! {
! DBLINK_RES_ERROR_AS_NOTICE("sql error");
! if (freeconn)
! PQfinish(conn);
! SRF_RETURN_DONE(funcctx);
! }
}
if (PQresultStatus(res) == PGRES_COMMAND_OK)
--- 817,826 ----
(PQresultStatus(res) != PGRES_COMMAND_OK &&
PQresultStatus(res) != PGRES_TUPLES_OK))
{
! dblink_res_error(conname, res, "could not execute query", fail);
! if (freeconn)
! PQfinish(conn);
! SRF_RETURN_DONE(funcctx);
}
if (PQresultStatus(res) == PGRES_COMMAND_OK)
***************
*** 1180,1189 ****
(PQresultStatus(res) != PGRES_COMMAND_OK &&
PQresultStatus(res) != PGRES_TUPLES_OK))
{
! if (fail)
! DBLINK_RES_ERROR("sql error");
! else
! DBLINK_RES_ERROR_AS_NOTICE("sql error");
/* need a tuple descriptor representing one TEXT column */
tupdesc = CreateTemplateTupleDesc(1, false);
--- 1146,1152 ----
(PQresultStatus(res) != PGRES_COMMAND_OK &&
PQresultStatus(res) != PGRES_TUPLES_OK))
{
! dblink_res_error(conname, res, "could not execute command", fail);
/* need a tuple descriptor representing one TEXT column */
tupdesc = CreateTemplateTupleDesc(1, false);
***************
*** 1195,1201 ****
* result tuple
*/
sql_cmd_status = cstring_to_text("ERROR");
-
}
else if (PQresultStatus(res) == PGRES_COMMAND_OK)
{
--- 1158,1163 ----
***************
*** 2288,2290 ****
--- 2250,2303 ----
}
}
}
+
+ static void
+ dblink_res_error(const char *conname, PGresult *res, const char *dblink_context_msg, bool fail)
+ {
+ int level;
+ char *pg_diag_sqlstate = PQresultErrorField(res, PG_DIAG_SQLSTATE);
+ char *pg_diag_message_primary = PQresultErrorField(res, PG_DIAG_MESSAGE_PRIMARY);
+ char *pg_diag_message_detail = PQresultErrorField(res, PG_DIAG_MESSAGE_DETAIL);
+ char *pg_diag_message_hint = PQresultErrorField(res, PG_DIAG_MESSAGE_HINT);
+ char *pg_diag_context = PQresultErrorField(res, PG_DIAG_CONTEXT);
+ int sqlstate;
+ char *message_primary;
+ char *message_detail;
+ char *message_hint;
+ char *message_context;
+ const char *dblink_context_conname = "unnamed";
+
+ if (fail)
+ level = ERROR;
+ else
+ level = NOTICE;
+
+ if (pg_diag_sqlstate)
+ sqlstate = MAKE_SQLSTATE(pg_diag_sqlstate[0],
+ pg_diag_sqlstate[1],
+ pg_diag_sqlstate[2],
+ pg_diag_sqlstate[3],
+ pg_diag_sqlstate[4]);
+ else
+ sqlstate = ERRCODE_CONNECTION_FAILURE;
+
+ xpstrdup(message_primary, pg_diag_message_primary);
+ xpstrdup(message_detail, pg_diag_message_detail);
+ xpstrdup(message_hint, pg_diag_message_hint);
+ xpstrdup(message_context, pg_diag_context);
+
+ if (res)
+ PQclear(res);
+
+ if (conname)
+ dblink_context_conname = conname;
+
+ ereport(level,
+ (errcode(sqlstate),
+ message_primary ? errmsg("%s", message_primary) : errmsg("unknown error"),
+ message_detail ? errdetail("%s", message_detail) : 0,
+ message_hint ? errhint("%s", message_hint) : 0,
+ message_context ? errcontext("%s", message_context) : 0,
+ errcontext("Error occurred on dblink connection named \"%s\": %s.",
+ dblink_context_conname, dblink_context_msg)));
+ }
Index: expected/dblink.out
===================================================================
RCS file: /opt/src/cvs/pgsql/contrib/dblink/expected/dblink.out,v
retrieving revision 1.23
diff -c -r1.23 dblink.out
*** expected/dblink.out 6 Apr 2008 16:54:48 -0000 1.23
--- expected/dblink.out 1 Jun 2008 21:59:11 -0000
***************
*** 125,133 ****
-- open a cursor with bad SQL and fail_on_error set to false
SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foobar',false);
! NOTICE: sql error
! DETAIL: ERROR: relation "foobar" does not exist
!
dblink_open
-------------
ERROR
--- 125,132 ----
-- open a cursor with bad SQL and fail_on_error set to false
SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foobar',false);
! NOTICE: relation "foobar" does not exist
! CONTEXT: Error occurred on dblink connection named "unnamed": could not open cursor.
dblink_open
-------------
ERROR
***************
*** 194,202 ****
-- intentionally botch a fetch
SELECT *
FROM dblink_fetch('rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]);
! NOTICE: sql error
! DETAIL: ERROR: cursor "rmt_foobar_cursor" does not exist
!
a | b | c
---+---+---
(0 rows)
--- 193,200 ----
-- intentionally botch a fetch
SELECT *
FROM dblink_fetch('rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]);
! NOTICE: cursor "rmt_foobar_cursor" does not exist
! CONTEXT: Error occurred on dblink connection named "unnamed": could not fetch from cursor.
a | b | c
---+---+---
(0 rows)
***************
*** 210,218 ****
-- close the wrong cursor
SELECT dblink_close('rmt_foobar_cursor',false);
! NOTICE: sql error
! DETAIL: ERROR: cursor "rmt_foobar_cursor" does not exist
!
dblink_close
--------------
ERROR
--- 208,215 ----
-- close the wrong cursor
SELECT dblink_close('rmt_foobar_cursor',false);
! NOTICE: cursor "rmt_foobar_cursor" does not exist
! CONTEXT: Error occurred on dblink connection named "unnamed": could not close cursor.
dblink_close
--------------
ERROR
***************
*** 221,235 ****
-- should generate 'cursor "rmt_foo_cursor" not found' error
SELECT *
FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! ERROR: sql error
! DETAIL: ERROR: cursor "rmt_foo_cursor" does not exist
!
-- this time, 'cursor "rmt_foo_cursor" not found' as a notice
SELECT *
FROM dblink_fetch('rmt_foo_cursor',4,false) AS t(a int, b text, c text[]);
! NOTICE: sql error
! DETAIL: ERROR: cursor "rmt_foo_cursor" does not exist
!
a | b | c
---+---+---
(0 rows)
--- 218,230 ----
-- should generate 'cursor "rmt_foo_cursor" not found' error
SELECT *
FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! ERROR: cursor "rmt_foo_cursor" does not exist
! CONTEXT: Error occurred on dblink connection named "unnamed": could not fetch from cursor.
-- this time, 'cursor "rmt_foo_cursor" not found' as a notice
SELECT *
FROM dblink_fetch('rmt_foo_cursor',4,false) AS t(a int, b text, c text[]);
! NOTICE: cursor "rmt_foo_cursor" does not exist
! CONTEXT: Error occurred on dblink connection named "unnamed": could not fetch from cursor.
a | b | c
---+---+---
(0 rows)
***************
*** 291,299 ****
-- bad remote select
SELECT *
FROM dblink('SELECT * FROM foobar',false) AS t(a int, b text, c text[]);
! NOTICE: sql error
! DETAIL: ERROR: relation "foobar" does not exist
!
a | b | c
---+---+---
(0 rows)
--- 286,293 ----
-- bad remote select
SELECT *
FROM dblink('SELECT * FROM foobar',false) AS t(a int, b text, c text[]);
! NOTICE: relation "foobar" does not exist
! CONTEXT: Error occurred on dblink connection named "unnamed": could not execute query.
a | b | c
---+---+---
(0 rows)
***************
*** 316,324 ****
-- botch a change to some other data
SELECT dblink_exec('UPDATE foobar SET f3[2] = ''b99'' WHERE f1 = 11',false);
! NOTICE: sql error
! DETAIL: ERROR: relation "foobar" does not exist
!
dblink_exec
-------------
ERROR
--- 310,317 ----
-- botch a change to some other data
SELECT dblink_exec('UPDATE foobar SET f3[2] = ''b99'' WHERE f1 = 11',false);
! NOTICE: relation "foobar" does not exist
! CONTEXT: Error occurred on dblink connection named "unnamed": could not execute command.
dblink_exec
-------------
ERROR
***************
*** 378,386 ****
SELECT *
FROM dblink('myconn','SELECT * FROM foobar',false) AS t(a int, b text, c text[])
WHERE t.a > 7;
! NOTICE: sql error
! DETAIL: ERROR: relation "foobar" does not exist
!
a | b | c
---+---+---
(0 rows)
--- 371,378 ----
SELECT *
FROM dblink('myconn','SELECT * FROM foobar',false) AS t(a int, b text, c text[])
WHERE t.a > 7;
! NOTICE: relation "foobar" does not exist
! CONTEXT: Error occurred on dblink connection named "unnamed": could not execute query.
a | b | c
---+---+---
(0 rows)
***************
*** 416,424 ****
-- open a cursor incorrectly
SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foobar',false);
! NOTICE: sql error
! DETAIL: ERROR: relation "foobar" does not exist
!
dblink_open
-------------
ERROR
--- 408,415 ----
-- open a cursor incorrectly
SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foobar',false);
! NOTICE: relation "foobar" does not exist
! CONTEXT: Error occurred on dblink connection named "myconn": could not open cursor.
dblink_open
-------------
ERROR
***************
*** 503,511 ****
-- this should fail because there is no open transaction
SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM foo');
! ERROR: sql error
! DETAIL: ERROR: DECLARE CURSOR can only be used in transaction blocks
!
-- reset remote transaction state
SELECT dblink_exec('myconn','ABORT');
dblink_exec
--- 494,501 ----
-- this should fail because there is no open transaction
SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM foo');
! ERROR: DECLARE CURSOR can only be used in transaction blocks
! CONTEXT: Error occurred on dblink connection named "unnamed": could not execute command.
-- reset remote transaction state
SELECT dblink_exec('myconn','ABORT');
dblink_exec
***************
*** 554,562 ****
-- fetch some data incorrectly
SELECT *
FROM dblink_fetch('myconn','rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]);
! NOTICE: sql error
! DETAIL: ERROR: cursor "rmt_foobar_cursor" does not exist
!
a | b | c
---+---+---
(0 rows)
--- 544,551 ----
-- fetch some data incorrectly
SELECT *
FROM dblink_fetch('myconn','rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]);
! NOTICE: cursor "rmt_foobar_cursor" does not exist
! CONTEXT: Error occurred on dblink connection named "myconn": could not fetch from cursor.
a | b | c
---+---+---
(0 rows)
***************
*** 571,579 ****
-- should generate 'cursor "rmt_foo_cursor" not found' error
SELECT *
FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! ERROR: sql error
! DETAIL: ERROR: cursor "rmt_foo_cursor" does not exist
!
-- close the named persistent connection
SELECT dblink_disconnect('myconn');
dblink_disconnect
--- 560,567 ----
-- should generate 'cursor "rmt_foo_cursor" not found' error
SELECT *
FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! ERROR: cursor "rmt_foo_cursor" does not exist
! CONTEXT: Error occurred on dblink connection named "myconn": could not fetch from cursor.
-- close the named persistent connection
SELECT dblink_disconnect('myconn');
dblink_disconnect
Re: Re: [BUGS] BUG #4203: perform dblink() in begin/exception returns wrong SQLSTATE code
From
Joe Conway
Date:
Joe Conway wrote: > Tom Lane wrote: >> Joe Conway <mail@joeconway.com> writes: >>> [ improved patch ] >> >> Still a couple quibbles: > > [ more good feedback ] > > All valid complaints, and noticeably improved/simplified code as a > result. Third patch attached. Patch applied to HEAD. Joe