Hello, as the discuttion on async fetching on postgres_fdw, FETCH
with data-size limitation would be useful to get memory usage
stability of postgres_fdw.
Is such a feature and syntax could be allowed to be added?
==
Postgres_fdw fetches tuples from remote servers using cursor. The
transfer gets faster as the number of fetch decreases. On the
other hand buffer size for the fetched tuples widely varies
according to their average length. 100 tuples per fetch is quite
small for short tuples but larger fetch size will easily cause
memory exhaustion. However, there's no way to know it in advance.
One means to settle the contradiction would be a FETCH which
sends result limiting by size, not the number of tuples. So I'd
like to propose this.
This patch is a POC for the feature. For exapmle,
FETCH 10000 LIMIT 1000000 FROM c1;
This FETCH retrieves up to 10000 tuples but cut out just after
the total tuple length exceeds 1MB. (It does not literally
"LIMIT" in that sense)
The syntax added by this patch is described as following.
FETCH [FORWARD|BACKWARD] <ALL|SignedIconst> LIMIT Iconst [FROM|IN] curname
The "data size" to be compared with the LIMIT size is the
summation of the result of the following expression. The
appropriateness of it should be arguable.
[if tupleslot has tts_tuple] HEAPTUPLESIZE + slot->tts_tuple->t_len
[else] HEAPTUPLESIZE + heap_compute_data_size(slot->tts_tupleDescriptor,
slot->tts_values, slot->tts_isnull);
========================
This patch does following changes,
- This patch adds the parameter "size" to following functions (standard_)ExecutorRun / ExecutePlan / RunFromStore
PortalRun / PortalRunSelect / PortalRunFetch / DoPortalRunFetch
- The core is in StandardExecutorRun and RunFromStore. Simplly sum up the sent tuple length and compare against the
givenlimit.
- struct FetchStmt and EState has new member.
- The modifications in gram.y affects on ecpg parser. I think I could fix them but with no confidence :(
- Modified the corespondence parts of the changes above in auto_explain and pg_stat_statments only in parameter list.
regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center