Oleg Lebedev wrote:
> Ok, here are all the files.
> 

I'm now seeing the problem you reported. It is a bug in the new table function 
code. Basically, you are trying to do this:

DELETE FROM tablea
WHERE NOT EXISTS
(
   SELECT remoteid
   FROM
   (
     SELECT remoteid
     FROM dblink('hostaddr=1.23.45.6 port=5432 dbname=webspec user=user
                  password=pass',
                 'SELECT objectid  FROM tablea WHERE objectid = ' ||
                  tablea.objectid)
     AS dblink_rec(remoteid int8)
   ) AS t1
);

But if you try:

SELECT remoteid
FROM
(
   SELECT remoteid
   FROM dblink('hostaddr=1.23.45.6 port=5432 dbname=webspec user=user
                password=pass',
               'SELECT objectid  FROM tablea WHERE objectid = ' ||
                tablea.objectid)
   AS dblink_rec(remoteid int8)
) AS t1;

you'll get:

ERROR:  FROM function expression may not refer to other relations of same 
query level

which is what you're supposed to get. Apparently the error is not getting 
generated as it should when this query is run as a subquery.

What you should actually be doing is:

DELETE FROM tablea
WHERE NOT EXISTS
(
   SELECT remoteid
   FROM dblink('hostaddr=1.23.45.6 port=5432 dbname=webspec user=user
                password=pass',
               'SELECT objectid  FROM tablea WHERE objectid = ' ||
                tablea.objectid)
   AS dblink_rec(remoteid int8)
);
DELETE 0

This should make your function work on 7.3beta, but I still need to track down 
a fix for the bug. Thanks for the report!

Joe


Mon, 23 Sep 2002 20:36:35 -0700
Joe Conway <mail@joeconway.com>
Re: DBLink: interesting issue
Joe Conway wrote:
 > Oleg Lebedev wrote:
 >
 >> Ok, here are all the files.
 >>

This dblink thread on GENERAL led me to a bug in the planner subselect code.
Here is an example query that triggers it (independent of dblink and/or table
functions):

replica=# create table foo(f1 int);
CREATE TABLE
replica=# SELECT * FROM foo t WHERE NOT EXISTS (SELECT remoteid FROM (SELECT
f1 as remoteid FROM foo WHERE f1 = t.f1) AS t1);
server closed the connection unexpectedly
          This probably means the server terminated abnormally
          before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

It doesn't matter how foo is defined.

I'm just starting to dig in to this, but was hoping for any thoughts or
guidance I can get.

Thanks,

Joe

p.s. Below is a backtrace:

#3  0x081797a1 in ExceptionalCondition () at assert.c:46
#4  0x0810e102 in replace_var (var=0x82f73a8) at subselect.c:81
#5  0x0811293c in expression_tree_mutator (node=0x82f7438, mutator=0x810e96c
<replace_correlation_vars_mutator>,
      context=0x0) at clauses.c:2314
#6  0x0810e9a5 in replace_correlation_vars_mutator (node=0x82f7438,
context=0x0) at subselect.c:540
#7  0x08112718 in expression_tree_mutator (node=0x82f7454, mutator=0x810e96c
<replace_correlation_vars_mutator>,
      context=0x0) at clauses.c:2179
#8  0x0810e9a5 in replace_correlation_vars_mutator (node=0x82f7454,
context=0x0) at subselect.c:540
#9  0x0811293c in expression_tree_mutator (node=0x82f7480, mutator=0x810e96c
<replace_correlation_vars_mutator>,
      context=0x0) at clauses.c:2314
#10 0x0810e9a5 in replace_correlation_vars_mutator (node=0x82f7480,
context=0x0) at subselect.c:540
#11 0x0810e968 in SS_replace_correlation_vars (expr=0x82f7480) at subselect.c:525
#12 0x0810cef5 in preprocess_expression (parse=0x82f6830, expr=0x82f7064,
kind=1) at planner.c:725
#13 0x0810cf7e in preprocess_qual_conditions (parse=0x82f6830,
jtnode=0x82f6d70) at planner.c:775
#14 0x0810c75c in subquery_planner (parse=0x82f6830, tuple_fraction=1) at
planner.c:168
#15 0x0810e260 in make_subplan (slink=0x82f6698) at subselect.c:185
#16 0x0811293c in expression_tree_mutator (node=0x82f6780, mutator=0x810e9bc
<process_sublinks_mutator>, context=0x0)
      at clauses.c:2314
#17 0x0810ea35 in process_sublinks_mutator (node=0x82f6780, context=0x0) at
subselect.c:586
#18 0x08112718 in expression_tree_mutator (node=0x82f6754, mutator=0x810e9bc
<process_sublinks_mutator>, context=0x0)
      at clauses.c:2179
#19 0x0810ea35 in process_sublinks_mutator (node=0x82f6754, context=0x0) at
subselect.c:586
#20 0x0811293c in expression_tree_mutator (node=0x82f679c, mutator=0x810e9bc
<process_sublinks_mutator>, context=0x0)
      at clauses.c:2314
#21 0x0810ea35 in process_sublinks_mutator (node=0x82f679c, context=0x0) at
subselect.c:586
#22 0x0810e9b8 in SS_process_sublinks (expr=0x82f679c) at subselect.c:553
#23 0x0810cede in preprocess_expression (parse=0x82f46d4, expr=0x82fc164,
kind=1) at planner.c:721
#24 0x0810cf7e in preprocess_qual_conditions (parse=0x82f46d4,
jtnode=0x82fc36c) at planner.c:775
#25 0x0810c75c in subquery_planner (parse=0x82f46d4, tuple_fraction=-1) at
planner.c:168
#26 0x0810c68c in planner (parse=0x82f46d4) at planner.c:96



Mon, 23 Sep 2002 22:33:51 -0700
Joe Conway <mail@joeconway.com>
subselect bug (was Re: [GENERAL] DBLink: interesting issue)
Joe Conway <mail@joeconway.com> writes:
> replica=# create table foo(f1 int);
> CREATE TABLE
> replica=# SELECT * FROM foo t WHERE NOT EXISTS (SELECT remoteid FROM (SELECT
> f1 as remoteid FROM foo WHERE f1 = t.f1) AS t1);
> server closed the connection unexpectedly

Ick.

> I'm just starting to dig in to this, but was hoping for any thoughts or
> guidance I can get.

I can look at this, unless you really want to solve it yourself ...

> p.s. Below is a backtrace:

The debug output:

TRAP: FailedAssertion("!(var->varlevelsup > 0 && var->varlevelsup < PlannerQueryLevel)", File: "subselect.c", Line: 81)

suggests that the problem is with variable depth --- I'm guessing that
we're not adjusting varlevelsup correctly at some step of the planning
process.  Offhand I'd expect the innermost "select" to be pulled up into
the parent select (the argument of EXISTS) and probably something is
going wrong with that.

			regards, tom lane

Tue, 24 Sep 2002 12:17:11 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: subselect bug (was Re: [GENERAL] DBLink: interesting issue) 
Tom Lane wrote:
>>I'm just starting to dig in to this, but was hoping for any thoughts or
>>guidance I can get.
> 
> I can look at this, unless you really want to solve it yourself ...
> 

I'll look into it a bit for my own edification, but if you have the time to 
solve it, I wouldn't want to get in the way. In any case, if you think it 
should be fixed before beta2, I'd give you better odds than me ;-)

Joe


Tue, 24 Sep 2002 09:48:02 -0700
Joe Conway <mail@joeconway.com>
Re: subselect bug (was Re: [GENERAL] DBLink: interesting
Joe Conway <mail@joeconway.com> writes:
> replica=# create table foo(f1 int);
> CREATE TABLE
> replica=# SELECT * FROM foo t WHERE NOT EXISTS (SELECT remoteid FROM (SELECT
> f1 as remoteid FROM foo WHERE f1 = t.f1) AS t1);
> server closed the connection unexpectedly

Got it --- this bug has been there awhile :-(, ever since we had the
pull-up-subquery logic, which was in 7.1 IIRC.  The pullup code
neglected to adjust references to uplevel Vars.  Surprising that no one
reported this sooner.

The attached patch is against CVS tip.  It will not apply cleanly to
7.2 because pull_up_subqueries() has been modified since then, but if
anyone's desperate for a fix in 7.2 it could probably be adapted.

			regards, tom lane

*** src/backend/optimizer/plan/planner.c.orig	Wed Sep  4 17:30:30 2002
--- src/backend/optimizer/plan/planner.c	Tue Sep 24 14:02:54 2002
***************
*** 337,352 ****
  
  			/*
  			 * Now make a modifiable copy of the subquery that we can run
! 			 * OffsetVarNodes on.
  			 */
  			subquery = copyObject(subquery);
  
  			/*
! 			 * Adjust varnos in subquery so that we can append its
  			 * rangetable to upper query's.
  			 */
  			rtoffset = length(parse->rtable);
  			OffsetVarNodes((Node *) subquery, rtoffset, 0);
  
  			/*
  			 * Replace all of the top query's references to the subquery's
--- 337,358 ----
  
  			/*
  			 * Now make a modifiable copy of the subquery that we can run
! 			 * OffsetVarNodes and IncrementVarSublevelsUp on.
  			 */
  			subquery = copyObject(subquery);
  
  			/*
! 			 * Adjust level-0 varnos in subquery so that we can append its
  			 * rangetable to upper query's.
  			 */
  			rtoffset = length(parse->rtable);
  			OffsetVarNodes((Node *) subquery, rtoffset, 0);
+ 
+ 			/*
+ 			 * Upper-level vars in subquery are now one level closer to their
+ 			 * parent than before.
+ 			 */
+ 			IncrementVarSublevelsUp((Node *) subquery, -1, 1);
  
  			/*
  			 * Replace all of the top query's references to the subquery's

Tue, 24 Sep 2002 14:48:32 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: subselect bug (was Re: [GENERAL] DBLink: interesting issue) 