Re: Libpq enhancement

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Jeff Shanab <jshanab(at)smartwire(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Libpq enhancement
Date: 2011-06-21 19:55:51
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Sun, Jun 19, 2011 at 8:08 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Sun, Jun 19, 2011 at 11:04 AM, Jeff Shanab <jshanab(at)smartwire(dot)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
INSERT INTO foo SELECT foos.* FROM foos LEFT JOIN updated USING(id)

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'.


In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Andrew Gierth 2011-06-21 20:50:50 Re: hstore - Implementation and performance issues around its operators
Previous Message Steve Singer 2011-06-21 19:23:04 Re: patch for 9.2: enhanced errors