Temp tables available to included sql? Control of transactions?

[djyotta]

I've been trying to do some particularly crazy nesting of sql usong run_sql and found that temporary tables created in the calling sql may or may not be available to included sql.

I'd like to use temp tables to avoid persisting state. Currently I store query results in a concrete table and reference that from included sql. I have to nuke the table each page load to ensure clean slate...

I'm also mildly concerned that in a multi user situation this could cause weird things to happen... ie, Time of Check to Time of Use bug. I guess if we can serve the entire page on one connection, then we can use temp tables and sidestep the multi user problem.

However, there is still probably a case where session data really does need to be materialised but only visible to a particular session/user. Storing a session id in each row gets messy. There is no nice way to create tables with session in name (you can create the .sql dynamically and then call it - but that's nuts... I certainly haven't tried that approach cough).

So far it seems that when I populate a concrete table, the included sql sees correct data. I have seen occaisonally "database locked" (SQLite) when starting another request before first is finished, but yet to hit second request dropping and changing the table before the other request is done. I haven't tested on dbs with better concurrent access such as postgresql. I have implemented an app on postgresql but I store a bunch of stuff in user cookies so less of an issues. Queries are also quite fast and small data in that app, so less likely to hit race conditions in multiuser env anyway.

I wonder how transactions are managed in SQLPage; one transaction per .sql or per request? It seems each .sql can make a new connection (I hit in-memory SQLIte connection limit often when use run_sql a lot - but it defaults to 1 and I've had no trouble with 16 as max connection).

Finally, regarding multiple databases (I think there may be a separate discussion for that already): I've also tried using some ATTACH DATABASE in SQLite, which sort of work, but again, the included sql may or may not see it. Also DETACH DATABASE doesn't seem work. I workaround this by first merging the dbs into one. Slow to merge but works.

I've been toying with the idea of spinning up a cluster of sqlpage docker containers behind a reverse proxy for the multi db case, but while the UI will appear unified, still can not do cross db queries. But in the use case where cross db queries aren't needed, this should be faster that a massive merged db.

Sorry for the mind dump. It's late and I didn't want to forget to start this discussion.

[lovasoa]

Hi ! This is all related to SQLPage acquiring a new database connection for each sqlpage.run_sql call. This also has performance implications, and we should optimize that.

I created an issue to track this: #338