Ability to Prepare multi-statement

[oliverkofoed]

FEATURE REQUEST
The database/sql driver lib/pg prepares all statements before executing. This however fails when trying to do two inserts to different tables in one go:

if _, err := db.Exec("insert into a(id) values ($1); insert into b(id) values ($2)", "value_for_a", "value_for_b"); err != nil {
	panic(err)
}

This will panic with:

panic: pq: prepared statement had 2 statements, expected 1 [recovered]

It would be nice-to-have if Cockroach was able to prepare multi-statements so the above would work.

[jordanlewis]

Interesting - thanks for the report. I wasn't aware that it was possible to prepare compound statements like that.

[petermattis]

I recall the Postgres wire protocol doesn't support multiple statements in a prepared statement. I could be mistaken about that.

[bdarnell]

I don't think it's usually possible to prepare multiple statements like that - the PREPARE statement couldn't be used since it would be terminated by the first semicolon. The wire-level prepare functionality could theoretically support this, but I don't think it does (perhaps partially due to sql injection concerns, but mainly due to a lack of demand since other databases aren't as sensitive to what's in a single "statement").

For comparison, mysql supports multiple statements like this, but only if you opt in by setting the CLIENT_MULTI_STATEMENTS option on the session. Oracle supports multiple INTO/VALUES clauses on a single INSERT ALL statement, which would allow this to be written as one statement.

In current versions of CockroachDB, you could use UNION and INSERT RETURNING to combine both inserts into the same statement. Something like SELECT * FROM [INSERT INTO A... RETURNING 1] UNION ALL SELECT * FROM [INSERT INTO B... RETURNING 1]. The newly-supported WITH statement could also be used. Finally, the wire protocol supports pipelining, so sending multiple statements doesn't necessarily mean waiting for multiple round trips. However, few client drivers are able to take advantage of this (and neither does the server side of cockroachdb)

[jordanlewis]

I've confirmed Postgres doesn't support this. It's unlikely that we will either.

[oliverkofoed]

Thank you guys for the detailed response, and i'm sorry I did not check the Postgres behavior before posting.

I appreciate the SELECT/INSERT/UNION/RETURNING workaround, but it feels a bit hacky...

Out of curiosity: If I have a bunch of statements to execute together, and I want to avoid an RTT from client->cockroach for each, is there no smart way to do that?

if not, feel free to close this issue, and thanks again for the responses!

[knz]

@oliverkofoed this is a known good use case for a user-defined function or procedure. We actually plan to support these at some point (#17511). I'll close this issue for now, and feel free to track #17511 for a more durable solution. Cheers!

[kocoten1992]

There are (valid?) use cases for this, for example, use cockroachdb session variable for a certain queries, and still want to avoid RTT:

set default_transaction_priority = ?; set statement_timeout = ?; select 1;

[kocoten1992]

Hi @knz

What do you think about the use case (above)?

UDF/Stored Procedure may not be the answer for adhoc query like this? (mainly because it introduce maintenance cost - unsuitable for start-up?).

[knz]

This issue is closed; can you open a new issue to discuss? Or better yet, head to our community Slack.