BUG #2129: dblink problem

We have significantly improved dblink since 7.4. Would you try 8.1 and
see if you can reproduce the problem?

---------------------------------------------------------------------------

Akio Iwaasa wrote:

The following bug has been logged online:

Bug reference: 2129
Logged by: Akio Iwaasa
Email address: iwaasa@mxs.nes.nec.co.jp
PostgreSQL version: 7.4.10
Operating system: Redhat EL ES 3.0
Description: dblink problem
Details:

I'm very sorry for my poor English.

"postgres" process terminated with "signal 11"
because of my wrong SQL statement using "dblink".

--- SQL statement(Select statement a function) ---
select into RET *
from dblink(''select C1,C2,C3 from TABLE01 where ... '') <<<<< 3 column
as LINK_TABLE01(LC1 varchar(5),LC2 varchar(5),
LC3 varchar(5),LC4 varchar(5)) ;        <<<<< 4 column
---------------------------------------------------

Backtrace is below.

---
(gdb) core /usr/local/pgsql74a/data/base/10218530/core.20823
Core was generated by `postgres: postgres nwops [local] CO'.
Program terminated with signal 11, Segmentation fault.
:
(gdb) bt
#0 0x00575ffb in strlen () from /lib/tls/libc.so.6
#1 0x081f222a in varcharin (fcinfo=0xbfffbf70) at
varchar.c:368
#2 0x0821b86e in FunctionCall3 (flinfo=0x89f5b60,
arg1=29795, arg2=0, arg3=64) at fmgr.c:1016
#3 0x08120647 in BuildTupleFromCStrings (attinmeta=
0x89f5b00, values=0x8a2e2f0) at execTuples.c:730
#4 0x00b5173d in dblink_record (fcinfo=0xbfffc160)
at dblink.c:699
#5 0x0811c8c8 in ExecMakeTableFunctionResult
(funcexpr=0x89f5058, econtext=0x89f4c70,
expectedDesc=0x89f4e08, returnDesc=0xbfffc244)
at execQual.c:1057
#6 0x0812882c in FunctionNext (node=0x89f4be8) at
nodeFunctionscan.c:78
#7 0x0811fba6 in ExecScan (node=0x89f4be8,
accessMtd=0x81287dc <FunctionNext>) at
execScan.c:98
#8 0x08128906 in ExecFunctionScan (node=0x89f4be8) at
nodeFunctionscan.c:128
#9 0x0811aa0c in ExecProcNode (node=0x89f4be8) at
execProcnode.c:322
#10 0x081190b8 in ExecutePlan (estate=0x89f4ad8,
planstate=0x89f4be8, operation=CMD_SELECT,
numberTuples=1, direction=ForwardScanDirection,
dest=0x82b4734) at execMain.c:1103
#11 0x081182f5 in ExecutorRun (queryDesc=0x89e66b0,
direction=ForwardScanDirection, count=1) at
execMain.c:249
#12 0x0812fb65 in _SPI_pquery (queryDesc=0x89e66b0,
runit=1 '\001', useCurrentSnapshot=0 '\0',
tcount=1) at spi.c:1254
#13 0x0812fa5c in _SPI_execute_plan (plan=0x8a1b728,
ValuNulls=0x8a19398 " 1111112", useCurrentSnapshot=0
'\0', tcount=1) at spi.c:1201
#14 0x0812da75 in SPI_execp (plan=0x8a1b728, Values=
0x8a193e0, Nulls=0x8a19398 " 1111112", tcount=1) at
spi.c:241
#15 0x00ed97a3 in exec_run_select (estate=0xbfffc620,
expr=0x89e9170, maxtuples=1, portalP=0x0) at
pl_exec.c:3223
#16 0x00ed6abe in exec_stmt_select (estate=0xbfffc620,
stmt=0x89e9250) at pl_exec.c:1519
#17 0x00ed5eb6 in exec_stmt (estate=0xbfffc620,
stmt=0x89e9250) at pl_exec.c:967
#18 0x00ed5d3e in exec_stmts (estate=0xbfffc620,
stmts=0x89e8ed0) at pl_exec.c:903
#19 0x00ed5c28 in exec_stmt_block (estate=0xbfffc620,
block=0x89ebfd8) at pl_exec.c:859
#20 0x00ed56f0 in plpgsql_exec_trigger (func=0x89e86a8,
trigdata=0xbfffc830) at pl_exec.c:645
#21 0x00ed149f in plpgsql_call_handler
(fcinfo=0xbfffc700) at pl_handler.c:121
#22 0x0810442b in ExecCallTriggerFunc
(trigdata=0xbfffc830, finfo=0x89e26d8,
per_tuple_context=0x89e0500) at trigger.c:1150
#23 0x0810562c in DeferredTriggerExecute
(event=0x89eeb10, itemno=0, rel=0xb5549300,
trigdesc=0x89e2370, finfo=0x89e26c0,
per_tuple_context=0x89e0500) at trigger.c:1867
#24 0x0810589a in deferredTriggerInvokeEvents (
immediate_only=1 '\001') at trigger.c:2008
#25 0x08105a38 in DeferredTriggerEndQuery ()
at trigger.c:2143
#26 0x0819d61a in finish_xact_command () at
postgres.c:1757
#27 0x0819c427 in exec_simple_query
(query_string=0x89dcfb8 "COPY user_info_tbl FROM
STDIN ;") at postgres.c:946
#28 0x0819eb0e in PostgresMain (argc=4, argv=0x8990430,
username=0x89903a0 "postgres") at postgres.c:2918
#29 0x081728b8 in BackendFork (port=0x899cd70) at
postmaster.c:2564
#30 0x08171fe2 in BackendStartup (port=0x899cd70) at
postmaster.c:2207
#31 0x08170661 in ServerLoop () at postmaster.c:1119
#32 0x08170100 in PostmasterMain (argc=1, argv=0x898f538)
at postmaster.c:897
#33 0x08137ccb in main (argc=1, argv=0xbfffd9e4)
at main.c:214
(gdb) up
#1 0x081f222a in varcharin (fcinfo=0xbfffbf70)
at varchar.c:368
(gdb) p s
$10 = 0x7463 <Address 0x7463 out of bounds>
(gdb) up
#2 0x0821b86e in FunctionCall3 (flinfo=0x89f5b60,
arg1=29795, arg2=0, arg3=64) at fmgr.c:1016
(gdb) up
#3 0x08120647 in BuildTupleFromCStrings (attinmeta=
0x89f5b00, values=0x8a2e2f0) at execTuples.c:730
(gdb) p i
$12 = 3
(gdb) p values[0]
$13 = 0x8a2066c "11111112"
(gdb) p values[1]
$16 = 0x8a20675 "11111112"
(gdb) p values[2]
$14 = 0x8a2067e "1"
(gdb) p values[3]
$15 = 0x7463 <Address 0x7463 out of bounds>
(gdb) up
#4 0x00b5173d in dblink_record (fcinfo=0xbfffc160)
at dblink.c:699
(gdb) p *((PGresult *)funcctx->user_fctx)
$9 = {
ntups = 1,
numAttributes = 3,
attDescs = 0x8a205dc,
tuples = 0x8a21470,
tupArrSize = 128,
resultStatus = PGRES_TUPLES_OK,
cmdStatus = "SELECT", '\0' <repeats 33 times>,
binary = 0,
noticeHooks = {
noticeRec = 0xac0635 <defaultNoticeReceiver>,
noticeRecArg = 0x0,
noticeProc = 0xac0679 <defaultNoticeProcessor>,
noticeProcArg = 0x0
},
client_encoding = 1,
errMsg = 0x0,
errFields = 0x0,
null_field = "",
curBlock = 0x8a205d8,
curOffset = 168,
spaceLeft = 1880
}
(gdb) p *(funcctx->attinmeta->tupdesc)
$18 = {
natts = 4,
attrs = 0x89f58a0,
constr = 0x0,
tdhasoid = 0 '\0'
}
(gdb)
---

Of course this trouble occurred because of my
mistake, but I think that it is better if "dblink"
returns an error for a wrong SQL statement.

If "numAttributes"*1 was smaller than "natts"*2
before "dblink_record" calls "BuildTupleFromCStrings",
dblink can not return error ?

*1 funcctx->user_fctx->numAttributes
*2 funcctx->attinmeta->tupdesc->natts

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Akio Iwaasa wrote:

The following bug has been logged online:

Bug reference: 2129
Logged by: Akio Iwaasa

"postgres" process terminated with "signal 11"
because of my wrong SQL statement using "dblink".

--- SQL statement(Select statement a function) ---
select into RET *
from dblink(''select C1,C2,C3 from TABLE01 where ... '') <<<<< 3 column
as LINK_TABLE01(LC1 varchar(5),LC2 varchar(5),
LC3 varchar(5),LC4 varchar(5)) ;        <<<<< 4 column

The attached patch (against cvs HEAD) fixes the reported issue.

However, there is a remaining oddity with dblink_fetch(). Basically,
each time dblink_fetch() is called, the named cursor is advanced, even
though an error is thrown before returning any rows. Is there a simple
way to get the number of columns in the result, without actually
advancing the cursor?

If no one thinks the above is a problem, I'll commit the attached
against HEAD and stable branches back to 7.3.

Joe

Joe Conway wrote:

However, there is a remaining oddity with dblink_fetch(). Basically,
each time dblink_fetch() is called, the named cursor is advanced, even
though an error is thrown before returning any rows. Is there a simple
way to get the number of columns in the result, without actually
advancing the cursor?

I thought I could work around this issue by obtaining the count returned
for the FETCH using PQcmdTuples(), and then issuing a "MOVE BACWARD
n..." in the case where the return tuple doesn't match. However I get an
empty string:

(gdb) p str->data
$34 = 0x8a4e5a8 "FETCH 2 FROM rmt_foo_cursor"
(gdb) p PQcmdStatus(res)
$35 = 0x8a447c8 "FETCH"
(gdb) p PQcmdTuples(res)
$36 = 0x29dada ""

Any ideas why this isn't working?

Thanks,

Joe

Joe Conway wrote:

Joe Conway wrote:

However, there is a remaining oddity with dblink_fetch(). Basically,
each time dblink_fetch() is called, the named cursor is advanced, even
though an error is thrown before returning any rows. Is there a simple
way to get the number of columns in the result, without actually
advancing the cursor?

I thought I could work around this issue by obtaining the count returned
for the FETCH using PQcmdTuples(), and then issuing a "MOVE BACWARD
n..." in the case where the return tuple doesn't match. However I get an
empty string:

The attached seems to work OK, but I'm concerned about these passages
from the docs:

"SCROLL specifies that the cursor may be used to retrieve rows in a
nonsequential fashion (e.g., backward). Depending upon the complexity of
the query's execution plan, specifying SCROLL may impose a performance
penalty on the query's execution time. NO SCROLL specifies that the
cursor cannot be used to retrieve rows in a nonsequential fashion."

" The SCROLL option should be specified when defining a cursor that will
be used to fetch backwards. This is required by the SQL standard.
However, for compatibility with earlier versions, PostgreSQL will allow
backward fetches without SCROLL, if the cursor's query plan is simple
enough that no extra overhead is needed to support it. However,
application developers are advised not to rely on using backward fetches
from a cursor that has not been created with SCROLL. If NO SCROLL is
specified, then backward fetches are disallowed in any case."

So it seems, to fix the cursor issue properly I'd have to force the
SCROLL option to be used, thereby imposing a performance penalty.

Should I just accept that the cursor advances on a row type mismatch
error, fix using the attached patch and adding SCROLL to dblink_open(),
or something else? Any opinions out there?

Thanks,

Joe

Joe Conway <mail@joeconway.com> writes:

Should I just accept that the cursor advances on a row type mismatch
error, fix using the attached patch and adding SCROLL to dblink_open(),
or something else? Any opinions out there?

I would go with accepting (and documenting) the cursor advance. Trying
to undo it seems too risky, and it's not like this is a situation that
would arise in a properly-debugged application anyway. I can't see
expending a great amount of effort on it --- protecting against a crash
seems sufficient.

regards, tom lane

Tom Lane wrote:

Joe Conway <mail@joeconway.com> writes:

Should I just accept that the cursor advances on a row type mismatch
error, fix using the attached patch and adding SCROLL to dblink_open(),
or something else? Any opinions out there?

I would go with accepting (and documenting) the cursor advance. Trying
to undo it seems too risky, and it's not like this is a situation that
would arise in a properly-debugged application anyway. I can't see
expending a great amount of effort on it --- protecting against a crash
seems sufficient.

OK -- applied back to 7.3.

Thanks,

Joe