Conversation
… hash based partitioning.
plproxy_setup_tuplestore belongs in main.c Fix memory leak with creating tuples from text by introducing a new memory context for temporary storage.
Binary mode is currently disabled, but let's be future proof.
I removed too much of the original code...
Instead, the code used to check "!func->ret_scalar" by mistake.
When streaming results, the order in which rows show up can vary. Add ORDER BY clauses to the regression tests to make the result repeatable.
This requires moving result_map from ProxyFunction to ProxyConnection, so that we can have one per connection. That was not necessary before, because we got all results from one connection in a single PGresult, and we could afford to initialize the mapping whenever we got a result. Now, with single-row mode enabled, the result rows don't arrive connection by connection, so we have to maintain one result_map per connection, because setting up the mapping for every single result would be way too expensive. The result_map is now allocated in the persistent cluster_mem context, so we have to take care to free it whenever the ProxyConnection is cleaned or freed. In passing, rearrange the code in plproxy_tuple_from_result to have less indentation. Add a regression test for the column name mapping functionality that was previously missing.
Stream results
|
How is this different from current dynamic queries? |
|
Sorry for late response, did not notice the response here. The main difference is that instead of running a function on the partition side it runs the query directly. This was needed in this case specifically because PostgreSQL parallel execution does not work within functions. It's more efficient to let PostgreSQL handle intra node parallelism because that allows for aggregating things before sending them over the wire. |
|
Updated on top of latest version. Outstanding issues - 9.6 and earlier are missing tuplestore_tuple_count(), probably needs to be worked around by keeping the count on plproxy side. Also the 2.9 base release doesn't support 9.5 and earlier due to missing |
|
Sorry for delays, I only occasionally have time for pl/proxy maintenance. I took bit deeper look and have now more questions:
|
|
I'm sorry, but this PR is not reviewable, the branch contains mixed devlopment commits for two different features - EXECUTE and streaming resultset. Please submit them separately. Development branch as PR is kind of acceptable only if it contains only one feature and commits can be squashed, but not in this case. I did add support for non-power-of-2 clusters to 2.10, so this part is taken care of. |
Currently PostgreSQL parallel execution does not work when query is run with plpgsql
RETURN QUERY. To work around this it would be good to be able to send dynamically generated SQL from plproxy to remote servers. To enable this I propose to add aEXECUTEcommand to plproxy language. It fetches query from the argument list and sends to servers. Interacts nicely withRUN ONandSPLIT.Example:
Somewhat unrelatedly, optionally lift power-of-2 restriction from number of partitions when
disable_hashingis specified. Responsibility of mapping to partition numbers is then delegated to the user. Plproxy only error checks the resulting partition number.