How to store intermediate computation results

[theochr72]

Hello,

I’m working on an SQL page that makes several calls to the same table, with about ten columns, joins, and filters. This table is quite large, and I’m wondering if it’s possible to store this SELECT in a variable or call it recursively. Additionally, I’d like to be able to apply further filters afterwards. I want to avoid creating a temporary table. I’m working with a PostgreSQL database. If you have any ideas, I’d be happy to hear them!

[lovasoa]

Hello !
Depending on your use case, the volume and the kind of data your are working with, the best solution may be

• using a single query to generate data for multiple components (using CTEs and the dynamic component)
• storing the results in a sqlpage variable containing a json array
• storing the results in a temporary table

Temporary tables are really designed for this kind of things, do you have something in particular in your setup preventing you from being able to use them ?

[theochr72]

Actually, I'm not sure how to manage temporary tables in my context. We use sqlpage for handling HTTPD connections and LDAP authentication with the function sqlpage.basic_auth_username(). But I’m wondering how temporary tables behave in this context:

1. Are they created every time a user accesses the page?

2. Does each user have their own temporary table?

3. Since sqlpage manages the connections, how are these temporary tables handled in the background?

Let’s say I do this:

SET run_sql_result = sqlpage.run_sql('fetch_schedule.sql');

And fetch_schedule.sql contains:

DROP TABLE IF EXISTS temp_schedule_table;
CREATE TEMP TABLE temp_schedule_table AS
SELECT * FROM schedule;

Then I can call this table like this:

FROM temp_schedule_table;

But how will this behave for each user? Will each user have their own instance of temp_schedule_table? When will this table be dropped, especially in a multi-user environment?

I’m trying to better understand this mechanism in the context of using authentication and session management via LDAP.

CTEs are not a solution in my case, as they would need to be repeated for each query on the same page.

[lovasoa]

Thank you for the clarification !

These are good questions.

Temporary tables and database connections

Temporary tables are tied to the lifetime of network connections to the database:

• the table only exists in the connection from which it was created. Multiple versions of the same temp table can exist at the same time with different data if multiple connections are opened simultaneously.
• the table is automatically dropped when the connection is closed.

How SQLPage handles the database connection lifetime

SQLPage gives the following guarantees for database connections:

• a single http query will always be entirely managed by a single database connection.
  • Even when using sqlpage.run_sql('...'), the included sql file will run on the same connection as the caller.
• connections may be reused: For performance reasons, sqlpage will not automatically close the database connection after a web page has been served. The same connection can be used to serve a new http query afterwards.
  • As a consequence, a temporary table created in one request may or may not be visible from the next request, depending on whether sqlpage has chosen to use the same connection for both requests or not.
• you can run custom sql ...
  • ... when a new connection is opened with sqlpage/on_connect.sql
  • ... after a web page has been served, before the connection is reused: sqlpage/on_reset.sql
    • if it is important that the temporary table contents are not visible from the next request (it often isn't), you can drop it in on_reset.sql.

In conclusion

Your code seems to be correct.

-- In case the connection is not fresh, we may have a temp table from a previous request. Just drop it
DROP TABLE IF EXISTS temp_schedule_table;

-- Create a new temporary table in order to store the results of a heavy computation.
-- We are guaranteed to be able to access this table from all other sql
-- queries that will run during the rendering of the current web page. 
CREATE TEMP TABLE temp_schedule_table AS SELECT * FROM schedule where some_condition;

[kryskool]

Hi @theochr72

You use PostgreSQL as database, you can also use Materialized Views

Materialized views in PostgreSQL use the rule system like views do, but persist the results in a table-like form

[theochr72]

Thank you for all your advice, I have chosen the temporary table. It looks good, I will see depending on the number of users if it impacts the database performance. Since I'm not exactly sure how the connections will be managed with httpd and basic auth for each user, as well as the lifespan of the temporary tables and their behavior in my context, it seems perfect for my use case. @kryskool, thanks for your suggestion, but these will be quite similar tables, and I find it less clean in my case than using temporary tables. Thanks again!