SRF rescan testing - Mailing list pgsql-hackers

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

Tom Lane wrote:
>
> Now that I think about it, it's possible that ExecFunctionReScan is
> correct now, at least given the simplistic always-materialize policy
> that we've implemented so far.  But it hasn't gotten much testing.

OK -- the attached (stand alone) test script exercises
ExecFunctionReScan, including cases with chgParam != NULL. I'll try to
come up with one or two more variants for the latter, but so far I have
not found any misbehavior.

Joe
DROP TABLE foorescan;
CREATE TABLE foorescan (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
INSERT INTO foorescan values(5000,1,'abc.5000.1');
INSERT INTO foorescan values(5001,1,'abc.5001.1');
INSERT INTO foorescan values(5002,1,'abc.5002.1');
INSERT INTO foorescan values(5003,1,'abc.5003.1');
INSERT INTO foorescan values(5004,1,'abc.5004.1');
INSERT INTO foorescan values(5005,1,'abc.5005.1');
INSERT INTO foorescan values(5006,1,'abc.5006.1');
INSERT INTO foorescan values(5007,1,'abc.5007.1');
INSERT INTO foorescan values(5008,1,'abc.5008.1');
INSERT INTO foorescan values(5009,1,'abc.5009.1');

INSERT INTO foorescan values(5000,2,'abc.5000.2');
INSERT INTO foorescan values(5001,2,'abc.5001.2');
INSERT INTO foorescan values(5002,2,'abc.5002.2');
INSERT INTO foorescan values(5003,2,'abc.5003.2');
INSERT INTO foorescan values(5004,2,'abc.5004.2');
INSERT INTO foorescan values(5005,2,'abc.5005.2');
INSERT INTO foorescan values(5006,2,'abc.5006.2');
INSERT INTO foorescan values(5007,2,'abc.5007.2');
INSERT INTO foorescan values(5008,2,'abc.5008.2');
INSERT INTO foorescan values(5009,2,'abc.5009.2');

INSERT INTO foorescan values(5000,3,'abc.5000.3');
INSERT INTO foorescan values(5001,3,'abc.5001.3');
INSERT INTO foorescan values(5002,3,'abc.5002.3');
INSERT INTO foorescan values(5003,3,'abc.5003.3');
INSERT INTO foorescan values(5004,3,'abc.5004.3');
INSERT INTO foorescan values(5005,3,'abc.5005.3');
INSERT INTO foorescan values(5006,3,'abc.5006.3');
INSERT INTO foorescan values(5007,3,'abc.5007.3');
INSERT INTO foorescan values(5008,3,'abc.5008.3');
INSERT INTO foorescan values(5009,3,'abc.5009.3');

INSERT INTO foorescan values(5000,4,'abc.5000.4');
INSERT INTO foorescan values(5001,4,'abc.5001.4');
INSERT INTO foorescan values(5002,4,'abc.5002.4');
INSERT INTO foorescan values(5003,4,'abc.5003.4');
INSERT INTO foorescan values(5004,4,'abc.5004.4');
INSERT INTO foorescan values(5005,4,'abc.5005.4');
INSERT INTO foorescan values(5006,4,'abc.5006.4');
INSERT INTO foorescan values(5007,4,'abc.5007.4');
INSERT INTO foorescan values(5008,4,'abc.5008.4');
INSERT INTO foorescan values(5009,4,'abc.5009.4');

INSERT INTO foorescan values(5000,5,'abc.5000.5');
INSERT INTO foorescan values(5001,5,'abc.5001.5');
INSERT INTO foorescan values(5002,5,'abc.5002.5');
INSERT INTO foorescan values(5003,5,'abc.5003.5');
INSERT INTO foorescan values(5004,5,'abc.5004.5');
INSERT INTO foorescan values(5005,5,'abc.5005.5');
INSERT INTO foorescan values(5006,5,'abc.5006.5');
INSERT INTO foorescan values(5007,5,'abc.5007.5');
INSERT INTO foorescan values(5008,5,'abc.5008.5');
INSERT INTO foorescan values(5009,5,'abc.5009.5');

DROP FUNCTION foorescan(int,int);
CREATE FUNCTION foorescan(int,int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid >= $1 and fooid < $2
;'LANGUAGE SQL; 
DROP VIEW vw_foorescan;
CREATE VIEW vw_foorescan AS SELECT * FROM foorescan(5002,5004);

--invokes ExecFunctionReScan
SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM foorescan(5002,5004)) ORDER BY 1,2;

DROP TABLE barrescan;
CREATE TABLE barrescan (fooid int primary key);
INSERT INTO barrescan values(5003);
INSERT INTO barrescan values(5004);
INSERT INTO barrescan values(5005);
INSERT INTO barrescan values(5006);
INSERT INTO barrescan values(5007);
INSERT INTO barrescan values(5008);

--invokes ExecFunctionReScan
SELECT * FROM random(), foorescan(5000,5010) f JOIN barrescan b ON b.fooid = f.fooid WHERE f.foosubid = 9;
SELECT * FROM foorescan(5000,5000 + (random() * 10)::int) f JOIN barrescan b ON b.fooid = f.fooid WHERE f.foosubid = 9;

DROP FUNCTION foorescan(int);
CREATE FUNCTION foorescan(int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid = $1;' LANGUAGE SQL;

--invokes ExecFunctionReScan with chgParam != NULL
SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid))
ORDERBY 1,2; 
SELECT b.fooid, max(f.foosubid) FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM
foorescan(b.fooid))GROUP BY b.fooid ORDER BY 1,2; 

DROP VIEW fooview1;
CREATE VIEW fooview1 AS SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid
FROMfoorescan(b.fooid)) ORDER BY 1,2; 
SELECT * FROM fooview1 AS fv WHERE fv.fooid = 5004;

DROP VIEW fooview2;
CREATE VIEW fooview2 AS SELECT b.fooid, max(f.foosubid) AS maxsubid FROM barrescan b, foorescan f WHERE f.fooid =
b.fooidAND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) GROUP BY b.fooid ORDER BY 1,2; 
SELECT * FROM fooview2 AS fv WHERE fv.maxsubid = 5;