Обсуждение: Libpq enhancement
<div class="WordSection1"><p class="MsoNormal">I am wondering If I am missing something obvious. If not, I have a suggestionfor plpgsql.<p class="MsoNormal"> <p class="MsoNormal">Stored procedures can accept rows.<p class="MsoNormal">Libpqcan receive rows (PQResult).<p class="MsoNormal"> <p class="MsoNormal">Wouldn’t it be a great interfaceif PQResult was “bi-directional”? Create a result set on the client then call the database with a command. <p class="MsoNormal"> <pclass="MsoNormal">Perhaps…<p class="MsoNormal"> PQinsert(PQResult,”schema.table”); //iterate thru rowsinserting<p class="MsoNormal"> PQupdate(PQResult,”schema.table”); //iterate thru rows updateing<p class="MsoNormal"> <pclass="MsoNormal"> PQexec(connection,”scheme.function”,PQResult) //iterate thru rows passing row asarg to stored procedure.<p class="MsoNormal"> </div>
On 6/19/2011 11:04 AM, Jeff Shanab wrote: > I am wondering If I am missing something obvious. If not, I have a suggestion > for plpgsql. > > Stored procedures can accept rows. > > Libpq can receive rows (PQResult). > > Wouldn’t it be a great interface if PQResult was “bi-directional”? Create a > result set on the client then call the database with a command. > > Perhaps… > > PQinsert(PQResult,”schema.table”); //iterate thru rows inserting > > PQupdate(PQResult,”schema.table”); //iterate thru rows updateing > > PQexec(connection,”scheme.function”,PQResult) //iterate thru rows passing row as > arg to stored procedure. > Have you looked into libpqtypes? It allows you to pack nested structures/arrays and pass them as query/function parameters. http://pgfoundry.org/projects/libpqtypes/ http://libpqtypes.esilo.com/ (docs) -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/
Hey Jeff,
--
// Dmitriy.
2011/6/19 Jeff Shanab <jshanab@smartwire.com>
IMO, mapping C functions to SQL operators is bad idea.
If I understood you correctly, you want to make libpq ORM. But
without implementing a functional like C++ virtual functions on
the _backend_ side, it is impossible or ugly.
I am wondering If I am missing something obvious. If not, I have a suggestion for plpgsql.
Stored procedures can accept rows.
Libpq can receive rows (PQResult).
Wouldn’t it be a great interface if PQResult was “bi-directional”? Create a result set on the client then call the database with a command.
Perhaps…
PQinsert(PQResult,”schema.table”); //iterate thru rows inserting
PQupdate(PQResult,”schema.table”); //iterate thru rows updateing
IMO, mapping C functions to SQL operators is bad idea.
If I understood you correctly, you want to make libpq ORM. But
without implementing a functional like C++ virtual functions on
the _backend_ side, it is impossible or ugly.
--
// Dmitriy.
On Sun, Jun 19, 2011 at 11:04 AM, Jeff Shanab <jshanab@smartwire.com> wrote: > I am wondering If I am missing something obvious. If not, I have a > suggestion for plpgsql. > > Stored procedures can accept rows. > > Libpq can receive rows (PQResult). > > Wouldn’t it be a great interface if PQResult was “bi-directional”? Create a > result set on the client then call the database with a command. For insert, we have something like this already - this is what "copy" is for. For update, it's a bit more complex - we don't have a "replace into" operator... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Sun, Jun 19, 2011 at 8:08 PM, Robert Haas <robertmhaas@gmail.com> wrote: > On Sun, Jun 19, 2011 at 11:04 AM, Jeff Shanab <jshanab@smartwire.com> wrote: >> I am wondering If I am missing something obvious. If not, I have a >> suggestion for plpgsql. >> >> Stored procedures can accept rows. >> >> Libpq can receive rows (PQResult). >> >> Wouldn’t it be a great interface if PQResult was “bi-directional”? Create a >> result set on the client then call the database with a command. > > For insert, we have something like this already - this is what "copy" is for. 'copy' is a *bulk* insert statement -- it's great for the very specific case when you are dumbly stuffing data into the database, especially if performance is critical and sane error handling is not. It is not suitable for anything else: feeding data into functions, update/upsert/delete, insert with join, pre-post process, etc. Also copy runs through libpq textually at the line level, not at the field level like the rest of libpq. > For update, it's a bit more complex - we don't have a "replace into" operator... Actually, we do. 9.1 supports data modifying CTE around which it's possible to rig a perfectly reasonable upsert...barring that, you could trivially do something similar in a hand rolled backend upsert function that takes a row or a set of rows (fed in as a composite array). Point being, the server has the necessary features -- it's the client that's the (solved) problem. At the risk of sounding 'broken record repetitive', let me echo andrew's comment upthread that libpqtypes solves the OP's problem completely in a very elegant way. The basic M.O. is to: 1. register the type you are using for transport (can either be the table or a composite type) 2. for each record you want to send, PQputf that record, and if you are sending more than one, PQputf the record into it's array 3. PQparamExec() a query that might look like one of: /* straight up insert */ PQparamExec(conn, param, "INSERT INTO foo SELECT (unnest(%foo[])).* FROM f", resfmt); /* send to function */ PQparamExec(conn, param, "SELECT do_stuff(%foo[]) ", resfmt); /* upsert -- pre 9.1 this could be done in plpgsql loop, etc */ WITH foos AS (SELECT (UNNEST(%foo[])).*) updated as (UPDATE foo SET foo.a = foos.a ... RETURNING foo.id) INSERT INTO foo SELECT foos.* FROM foos LEFT JOIN updated USING(id) WHERE updated.id IS NULL; Basically, the trick is to exploit the server's composite array type features on the client side to do exactly what the OP is gunning for. You can send anything from simple arrays to entire complex nested structures that way -- although the complex stuff would typically go a to a function. Performance wise, it's faster than traditional query methods (everything is sent in binary) but slower than 'copy'. merlin
On Tue, Jun 21, 2011 at 3:55 PM, Merlin Moncure <mmoncure@gmail.com> wrote: >> For update, it's a bit more complex - we don't have a "replace into" operator... > > Actually, we do. 9.1 supports data modifying CTE around which it's > possible to rig a perfectly reasonable upsert...barring that, you > could trivially do something similar in a hand rolled backend upsert > function that takes a row or a set of rows (fed in as a composite > array). I don't believe that any of the solutions we have today are guaranteed to behave correctly in the face of concurrent activity. Because of the way snapshot isolation works, you can try to update an existing record, find that there isn't one, and then fail when you go to insert because some other backend has meanwhile inserted one that isn't visible to your snapshot. Doing the operations in the other order is no better. I'm not saying this is the biggest problem in the entire world, but I do think it's a non-imaginary problem. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company