Pgx: question: does this library support batch insert & update?

[badoet]

does this library support multi statement and multi result?

[jackc]

No. What exactly are you trying to do?

[badoet]

batch insert and batch update.
very common sql query optimisation.

[jackc]

I guess I'm still not understanding. The PostgreSQL wire protocol is inherently serial -- send a query then get a result. You can insert multiple rows in a single statement if that is what you mean.

insert into widgets(a, b, c)
values (1,2,3), (4,5,6), (7,8,9);

My typical understanding of batching in SQL would be a single insert or update that touches multiple records or wrapping multiple insert/updates in a transaction.

Can you give me an example in another language/library/pseudo-code?

[badoet]

https://github.com/go-sql-driver/mysql/pull/339
i currently have a pending pull request in the myql driver.
the code example is stated there too.
this example is using the gocraft/dbr library

query := "UPDATE user_resource SET mineral=mineral+1 WHERE id=" + id + ";"
query += "UPDATE user SET xp=xp+1 WHERE id=" + id + ";"
res, err := sess.UpdateBySql(query).Exec()

if err != nil {
    ReturnErr(w, err.Error())
    return
}

n, _ := res.RowsAffected()
fmt.Printf("ROWS AFFECTED: %v \n", n)

[jackc]

Okay, I think I understand what you're asking here. The Exec function can do some of what you want. Exec will use prepared statements if there are any arguments to the SQL. PostgreSQL prepared statements only work with one statement at a time. But if you just pass in a blob of SQL without any arguments it will pass it directly to PostgreSQL. However, you also will only get a succeed or fail back, you won't get all the results (I think you will also get the last statement's command tag back (e.g. "UPDATE 3"), but off-hand I'm not sure).

So given the restrictions that you cannot use arguments, and that you only get a succeed or fail result back, you can fire multiple inserts/updates at once.

[badoet]

awesome!
thank you for the detailed explanation!

[tsingson]

+1
thanks

[Zamiell]

For future readers of this thread, you should not manually build the SQL string using concatenation like badoet does above.
See my helper function here: https://github.com/jackc/pgx/issues/764#issuecomment-685249471

[achempak]

@Zamiell is there a reason we shouldn't be manually building the SQL string using concatenation? Is it just to prevent against a SQL injection, or are there other issues?

[Zamiell]

Yes, it prevents SQL injection. But beyond that, it is very, very difficult to account for every type of quote, every type of whitespace, every type of special character, etc. Let the SQL library do that for you - that's what it's designed to do.