I am using postgresql 8.4 in backend. In backend I made a postgres function to get some data. The function won't write any data to DB so just read data from other tables. The function internally will create a temp table then return a set of records.
When I monitoring the server I found out this function is blocking other connections doing data insertion.
So just wondering creating temp table could block data insertion from other connections?
Further question. I have a function A, inside this function there is a function call to B, function B will return a set of records (the record has two columns say "name","email"). Then I need a variable in function A to hold the data returned from function B. When data returned from B I will use the first column to do something then use the second col to other staff. So at the moment I use temp table to hold the returned setof records from function B. Is there another way to hold returned records?
Found this not sure helpful or not: https://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions
You should def avoid creating such kind of functions, as in multi-session environment, or even just using multi-row requests from the same user session, server will try to create the temp table per each user session + per each queried row. As multiple requests need to create the same resource (your temp table) - sure, server can handle just one row of one request at the same time.
See more about postgresql explicit-locking
Consider using view instead of creating temp table.
P.S. Your statement that query does not write any data, just reads, is not true. Query actually does DDL and DML operations during its execution.