Skip site navigation (1) Skip section navigation (2)

Re: SRF patch (was Re: [HACKERS] troubleshooting pointers)

From: Joe Conway <mail(at)joeconway(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-patches(at)postgresql(dot)org
Subject: Re: SRF patch (was Re: [HACKERS] troubleshooting pointers)
Date: 2002-05-14 22:27:47
Message-ID: 3CE18F63.9090805@joeconway.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
Tom Lane wrote:
> I've committed this with some revisions.  The VIEW cases you were
> worried about seem to work now.  I think you'll find that
> single-FROM-item cases generally work, and it's time to start worrying
> about joins (ie, rescans).

Hi Tom,

I've been looking through the SRF patch as-committed, and I think I 
understand most of your changes, but I have a question: FunctionNext() 
now seems to *always* use a tuplestore instead of conditionally using 
the store only for rescans, or if the function was explicitly marked as 
PM_MATERIALIZE. Do you still think there should be an option to project 
tuples without first storing them, or should we eliminate the notion of 
function mode and always materialize?


> 
> Parameters also need thought.  This should be rejected:
> 
> regression=# select * from foo, foot(fooid) z where foo.f2 = z.f2;
> server closed the connection unexpectedly

I don't understand why this should be rejected, but it does fail for me 
also, due to a NULL slot pointer. At what point should it be rejected?


> 
> On the other hand, IMHO this should work:
> 
> regression=# select * from foo where f2 in
> regression-# (select f2 from foot(foo.fooid) z where z.fooid = foo.fooid);
> server closed the connection unexpectedly

This also fails in (based on a quick look) exactly the same way -- a 
NULL slot pointer (econtext->ecxt_scantuple) passed to ExecEvalVar().


BTW, The test cases I was using previously now all pass (copy below).

Thanks,

Joe


DROP TABLE foo;
CREATE TABLE foo (fooid int, foosubid int, fooname text, primary 
key(fooid,foosubid));
INSERT INTO foo VALUES(1,1,'Joe');
INSERT INTO foo VALUES(1,2,'Ed');
INSERT INTO foo VALUES(2,1,'Mary');

-- sql, proretset = f, prorettype = b
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
DROP VIEW vw_getfoo;
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;

-- sql, proretset = t, prorettype = b
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo 
WHERE fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
DROP VIEW vw_getfoo;
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;

-- sql, proretset = t, prorettype = b
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS setof text AS 'SELECT fooname FROM 
foo WHERE fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
DROP VIEW vw_getfoo;
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;

-- sql, proretset = f, prorettype = c
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE 
fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
DROP VIEW vw_getfoo;
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;

-- sql, proretset = t, prorettype = c
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM foo 
WHERE fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
DROP VIEW vw_getfoo;
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;

-- C, proretset = f, prorettype = b
SELECT * FROM dblink_replace('123456789987654321', '99', 'HelloWorld');
DROP VIEW vw_dblink_replace;
CREATE VIEW vw_dblink_replace AS SELECT * FROM 
dblink_replace('123456789987654321', '99', 'HelloWorld');
SELECT * FROM vw_dblink_replace;

-- C, proretset = t, prorettype = b
SELECT dblink_get_pkey FROM dblink_get_pkey('foo');
DROP VIEW vw_dblink_get_pkey;
CREATE VIEW vw_dblink_get_pkey AS SELECT dblink_get_pkey FROM 
dblink_get_pkey('foo');
SELECT * FROM vw_dblink_get_pkey;



In response to

Responses

pgsql-hackers by date

Next:From: Scott RoystonDate: 2002-05-14 23:02:41
Subject: Re: [SQL] can't cast varchar as integer?
Previous:From: Tom LaneDate: 2002-05-14 21:50:32
Subject: Re: 7.2.2 ?

pgsql-patches by date

Next:From: Tom LaneDate: 2002-05-14 23:15:14
Subject: Re: SRF patch (was Re: [HACKERS] troubleshooting pointers)
Previous:From: Tatsuo IshiiDate: 2002-05-14 04:20:07
Subject: Re: getopt_long patch for pg_dump and psql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group