Thank you very much for answering my question, Tom.
Yes, I have always assumed that returning from function without
calling SPI_freetuptable is not good idea, but I do not know another
way to achieve same result.
As I am not expert in PostgreSQL internals and mostly work according
to official documentation, I never know about tuplestore before.
This look like pretty interesting idea.
I have searched for tuplestore over Internet and found some
topics, relative to subject:
1. https://postgres.cz/wiki/Iter%C3%A1tor_pole
2. https://www.postgresql.org/message-id/1073862553.1475.93.camel%40jeff
3.
https://www.postgresql.org/message-id/753432.21663.qm%40web65511.mail.ac4.yahoo.com
(with your comment)
4. http://web.mit.edu/ghudson/trac/attic/src/postgresql-7.4.5/contrib/tablefunc/tablefunc.c
I have examined this topics briefly and got some questions:
1. As I understand there are tests for SFRM_Materialize in code above.
In my case a caller of my function is PL/pgSQL procedure.
Does it accept this returning mode?
2. Our current production server versions is 9.1 and 9.6.
Do this versions support returning of tuplestore?
3. Currently my function defined as "RETURNS SETOF".
Does definition of the function need to be changed if I rewrite code to
return tuplestore?
> Andrey Sychev <andrey.sychev@cifrasoft.com> writes:
>> I have written C-language function that returns
>> multiple composite rows.
>> Generally function works as expected, but sometimes problem takes place.
>> At rough guess the problem occurs when number of returning rows
>> relatively large (more than 100K - 1M).
> I do not think it's valid to return from your function with the SPI
> context still open. Probably, it seems to accidentally sort of work
> as long as you don't return enough rows to cause the outer query to
> do anything interesting like spill to disk.
> Probably you should re-code this to execute just once and return
> a tuplestore.
> regards, tom lane
--
Best regards,
Andrey Sychev
andrey.sychev@cifrasoft.com