How should users manage transactions?

[bever1337]

Following example 11: https://sql.datapage.app/component.sql?component=form

I've created a sql script that creates a temporary table, copies to it, and then merges from the temporary table to the permanent table.

Typically, I use 'ON COMMIT DROP' when making temporary tables. On my second upload, sqlpage throws the error that my temporary table already exists. I kill the server, then put begin and commit as the first and last lines of my sql script. I test again, and get a similar error. Sometimes I receive postgres transaction warnings, ex beginning before committing.

Finally, I add commit; begin and commit as the first and last lines. This works, but means I've thrown away a transaction I wasn't managing.

How should users work with sqlpage transactions? Is there a way to align transactions with request lifecycles?

This pseudo code seems to work:

COMMIT;

BEGIN;

CREATE TEMPORARY TABLE example_temp (
  LIKE example
) ON COMMIT DROP;

COPY example_temp (my_id)
FROM
  'example_form_input' (FORMAT 'csv', HEADER);

MERGE INTO example from example_temp;

SELECT
  'redirect' AS component,
  '/example' AS link;

COMMIT;

Judiciously beginning and ending every sqlpage script with begin and commit negates the need for commit;begin; and commit; in some files. Not sure which is best.

[lovasoa]

Hello and thank you for the well-written report and reproduction.

Currently, SQLPage does connection pooling, but does not do anything special at the end of an HTTP request, so it can put back a connection with an open transaction to the pool.

I do agree that this behavior is not desirable. I opened issue #711 to track this.

In the meantime, I think what you are currently doing (commit; begin; at the beginning then commit at the end) is the best solution.

[bever1337]

@lovasoa thanks for your continued support. Sqlpage is a real delight

[lovasoa]

Thanks! What are you building with it?

[bever1337]

I wish I had something exciting and worthy of a case study for you. I'm building a traditional CRUD app where most users have read-only access and admin functionality is behind authentication. It will be graph heavy (related data, not dataviz) with lots of tables and navigation between entities. I found SQLPage because I saw a ycombinator mention of htmx and postgrest and thought "That seems like too much work."

It's not a money maker, but it is time-sensitive, so I'm testing if SQLPage speeds up my fullstack workflow. It's just too much work for a single individual to release a fullstack product in a reasonable timeline. I don't always want to flex my system design muscles. The SQLPage approach works because I spend my energy where it matters and understand frontends come and go.

I don't see pushing SQLPage at work, but it's just the wrong domain and not a reflection of SQLPage.