Thread: BUG #4203: perform dblink() in begin/exception returns wrong SQLSTATE code

The following bug has been logged online:

Bug reference:      4203
Logged by:          Henry Combrinck
Email address:      henry@zen.co.za
PostgreSQL version: 8.2.6 and 8.3.1
Operating system:   Linux
Description:        perform dblink() in begin/exception returns wrong
SQLSTATE code
Details:

Apologies if this is the wrong forum to report this (perhaps it needs to go
to the dblink() maintainer?)

In a function on a machine using 8.2.6, the following returns a strange
SQLSTATE code:

begin
  perform dblink ('host=other_machine ...',
        'insert into table...');
exception when others then
  raise notice 'SQLSTATE: %', SQLSTATE;
end;

The code returned is always 42601 (syntax_error) irrespective of the actual
error (eg, unique_violation).

"Henry Combrinck" <henry@zen.co.za> writes:
> Description:        perform dblink() in begin/exception returns wrong
> SQLSTATE code

> The code returned is always 42601 (syntax_error) irrespective of the actual
> error (eg, unique_violation).

Yeah, the dblink code should probably try a bit harder to propagate the
original error fields.  I'm inclined to think that it should propagate
sqlstate/message/detail/hint verbatim, and indicate the fact that this
happened on a dblink connection as CONTEXT, rather than structuring the
ereport the way it does now.  Joe, what do you think?

            regards, tom lane

Tom Lane wrote:
> "Henry Combrinck" <henry@zen.co.za> writes:
>> Description:        perform dblink() in begin/exception returns wrong
>> SQLSTATE code
>
>> The code returned is always 42601 (syntax_error) irrespective of the actual
>> error (eg, unique_violation).
>
> Yeah, the dblink code should probably try a bit harder to propagate the
> original error fields.  I'm inclined to think that it should propagate
> sqlstate/message/detail/hint verbatim, and indicate the fact that this
> happened on a dblink connection as CONTEXT, rather than structuring the
> ereport the way it does now.  Joe, what do you think?

Sounds reasonable. Do you think this is a bug fix or an 8.4 enhancement?
I will try to take a closer look at the specific fix this weekend.

Joe

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.

            regards, tom lane