troubleshoot Invalid concurrent usage of PostgreSQL connection detected.

[mudrz]

I wanted to apply a few .sql files

apply:

• read file
• execute file_contents as SQL query
• read next file ..

if anything fails - rollback -> wanted everything to run as a transaction:

• I start a connection with Connection.start ()
• execute apply for a list of files
  • if nothing failed -> Connection.commit ()
  • if something failed -> Connection.rollback ()

To execute apply I used:

  let query = Caqti_request.exec Caqti_type.unit file_contents in
  Connection.exec query ()

Error1: Request to ... failed: ERROR: cannot insert multiple commands into a prepared statement

Not sure how to insert multiple commands, found that oneshot: true moves past this error, (but I couldn't find any information on what is oneshot, are there any docs?)

Error2: Unexpected result from ... More than one response received.

Does this mean that because there are multiple results Caqti_type.unit does not work? Tried this as a workaround:

  let typ =
    Caqti_type.custom
      ~encode:(fun _ -> failwith "no parameters expected")
      ~decode:(fun _ -> Ok ())
      Caqti_type.unit
  in
  let query = Caqti_request.exec ~oneshot:true typ contents in
  Connection.exec query ()

But found Error3: Invalid concurrent usage of PostgreSQL connection detected.

I'm not sure what is invalid and what this means? Are there any resources with documentation and examples that can help with these issues?

[paurkedal]

In general you can only execute one statement at the time. Some backends may support compound statements, but since there is no way of attaching an identity to a statement, it would pose the issue of identifying which result belongs to which statement. Even if we can assume the results arrive in the correct order, this would not be supported by the Caqti API, since it does not provide a way of typing the result. As you noticed, PostgreSQL, at least, does not support compound prepared statements, so I don't think it is relevant to attempt to extend the result type to support it.

There is a bit documentation of ~oneshot in the preamble of the module: "However, an optional oneshot parameter may be passed to indicate a dynamically generated query." In effect it means that the query will not be prepared for backends which supports it, though in the case of the MariaDB backend, it is anyway, since it only supports prepared queries. However, in that case it the prepared statement will be freed after use. So, the main use of ~oneshot:true is to prevent memory leaks if queries are generated dynamically, since with the default ~oneshot:false, the query will be prepared, and the handle cached permanently on the connection object. The ~oneshot parameter could use more documentation.

However, I think your use case is very relevant. I do this myself in Subsocia, see the implementation. The implementation uses Caqti_sql_io to read an split the statements. This is the only option provided by Caqti if don't do the splitting yourself, or resort to invoking the database command-line tool. But note that, I indent to make a concurrency-agnostic replacement of Caqti_sql_io once I get around to rewriting the query parser, which has at least one limitation, that it does not handle dollar-quotations, which makes function definitions infeasibly ugly. But if you don't have dollar-quotations, Caqti_sql_io should be an option for now. The new interface will likely be a pure parser with API similar to Angstrom, i.e. you do that IO yourself and feed the data incrementally to it, will pulling off the individual statements in the form of Caqti_query.t fragments.

[paurkedal]

I am not sure why you get the concurrent-connections error though. I may be missing some context. It means that the Connection already has an unfinished query. But the corresponding code will not work for another reason: The gathering of results will check for the correct multiplicity before the conversion is used.

[paurkedal]

There is now more elaborate documentation of ~oneshot in under the create function of Caqti_request.

[mudrz]

thanks paurkedal, oneshot is much clearer now

In general you can only execute one statement at the time.

I think this might have been the issue, because I have files with contents:

CREATE TABLE public.z
(
    id CHAR(11) COLLATE pg_catalog.default NOT NULL
);

CREATE INDEX z_index
    ON public.z USING btree
    (id COLLATE pg_catalog.default ASC NULLS LAST);

I will split each file into multiple queries and try again

But the corresponding code will not work for another reason: The gathering of results will check for the correct multiplicity before the conversion is used.

I can confirm that it doesn't work

I am not sure why you get the concurrent-connections error though. I may be missing some context. It means that the Connection already has an unfinished query.

I will check, it could be that I am not waiting for the first query to finish before starting the next one

thanks again for the response and the clarification on oneshot!

[cemerick]

@paurkedal just wanted to say 'thank you' for the ddl-related example you linked to, it was incredibly helpful for understanding how to effectively use caqti for migrations (and ofc externally-sourced ddl operations in general).

(FWIW, it seems like you have 80% of a general-purpose schema migration library that would be a fine addition to the caqti-* family.)