I have several function queries in postgresql. To avoid multiple resquest from the frontend to the server I has grouped all functions in a single funtion like:

select * "function_name"() 
union select * "function_name2"() 
union select * "function_name3"()

The problem here is that all queries wait to previous query to run, I want to run all queries in asyncronous mode and then maybe make a union of the each reasult.

How I can to do this in postgresql?

  • Which version? 9.6 has added parallel execution plans for some types of queries. – ypercubeᵀᴹ Dec 13 '16 at 17:57

First, a few things,

  • UNION [DISTINCT] is the default. This means all of those functions have to be executed and buffered so it knows it is not adding duplicate rows.
  • UNION ALL does not have these requirements.

Will UNION ALL return the first row before function_name3 is completed? I'm not sure. I would guess so, but I think it would depend on buffering, libraries, and other implementations details. But, moreover, I think you're doing this wrong. This is a horrible abomination and a contradiction of logic..

To avoid multiple resquest from the frontend to the server I has grouped all functions in a single funtion like:

This is not what UNION does. It doesn't pipeline a request. It joins a result set. You do it when it makes sense for your data. You're taking pre-optimizing to the extreme. This just slows down the query planner, and forces you to front-end parse the query.

Unless it makes sense with the data to UNION, all of these requests should go separately to the server. If you're interfacing with any reasonably library, this should not be an issue. If you're using a library or language that has async/IO, you should use the async IO library for PostgreSQL.

Pg 9.6 Parallel Functions

You can always try to use PARALLEL functions in your UNION I'm not sure they've added the optimization here but you can try it.

PARALLEL PARALLEL UNSAFE indicates that the function can't be executed in parallel mode and the presence of such a function in an SQL statement forces a serial execution plan. This is the default. PARALLEL RESTRICTED indicates that the function can be executed in parallel mode, but the execution is restricted to parallel group leader. PARALLEL SAFE indicates that the function is safe to run in parallel mode without restriction.

Functions should be labeled parallel unsafe if they modify any database state, or if they make changes to the transaction such as using sub-transactions, or if they access sequences or attempt to make persistent changes to settings (e.g. setval). They should be labeled as parallel restricted if they access temporary tables, client connection state, cursors, prepared statements, or miscellaneous backend-local state which the system cannot synchronize in parallel mode (e.g. setseed cannot be executed other than by the group leader because a change made by another process would not be reflected in the leader). In general, if a function is labeled as being safe when it is restricted or unsafe, or if it is labeled as being restricted when it is in fact unsafe, it may throw errors or produce wrong answers when used in a parallel query. C-language functions could in theory exhibit totally undefined behavior if mislabeled, since there is no way for the system to protect itself against arbitrary C code, but in most likely cases the result will be no worse than for any other function. If in doubt, functions should be labeled as UNSAFE, which is the default.

  • Thanks @Evan, firstly I has grouped all query cuz I have to much queries, around 50 or more, second, I need order the results by one field returned by each query in one table, the second is the mainly reazon to grouped all queries in one result set. – Resc6356 Dec 13 '16 at 18:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.