* Martijn van Oosterhout (kleptog@svana.org) wrote:
> Right, I didn't understand that you meant to be doing this
> synchronously, as the data came in. I thought it was just another way
> of retreiving the data already received. But given that a stated reason
> that psqlODBC didn't use the libpq interface was due to the copying of
> all the data, it would be nice if we had something for that. From
> looking at your declaration:
>
> int PQgetTuples(PGresult *res, // Returns number of tuples populated
> const int max_ntuples, // Basically buffer size
> char *result_set, // Destination buffer
> const int *columnOffsets, // integer array of offsets
> const int *columnLengths, // integer array of lengths, for checks
> const int record_len, // Length of each structure
> int *columnNulls, // 0/1 for is not null / is null
> int resultFormat); // Or maybe just binary?
>
> you seem to be suggesting that all the data be stored in one big memory
> block at resultset.

The current block would be stored in one big memory block, yes.
Basically a malloc(BUF_SIZE*sizeof(my_structure));

> What do you do if the data is longer than the given length? What does
> record_len mean (what structures)? Also, you can't specify
> binary/non-binary here, that's done in the query request. libpq doesn't
> handle the data differently depending on binaryness. Also, how can you
> find out the actual length of each value after the call?

hmm, ok, binary/non-binary can be dropped then. If the data is longer
than the length then you return and let the caller figure out what it
wants to do (realloc, malloc another area, etc). Record_len is just the
size of each record, so the amount to skip from the start to get to
record #2. libpq just needs it in getAnotherTuple to calculate the
place to put the next value. Finding the actual length is a good point,
I should have included (as execParams has) an integer array for this,
which could actually replace columnNulls and have a special indication
when it's a Null value in the column.

> Frankly I'm not seeing much improvement over normal processing. It just
> seems like yet another data-model that won't fit most users. The
> definition of PQgetvalue is merely:
>
> return res->tuples[tup_num][field_num].value;
>
> So we could acheive the same effect by letting people look into
> PQresult before the query is finished. The function you suggest would
> be especially difficult for something like psqlODBC which has no idea
> beforehand how long a value could be.

I don't think it's quite the same effect... :P I don't think it's
exactly uncommon for people to know their data structure and to have
defined a struct for it, allocate a set of memory and then want to just
loop through the memory in a for() loop based on the structure size.
This has been pretty common in standalone application I've seen and it's
really nice to be able to have a database just dump the results of a
query into such a structure.

> I'm still of the opinion that letting people supply an alternative to
> pqAddTuple would be cleaner. The interface would look like:
>
> typedef struct pgresAttValue
> {
> int len; /* length in bytes of the value */
> char *value; /* actual value, plus terminating zero byte */
> } PGresAttValue;
>
> typedef int (*PQtuplecallback)( PQresult *res, PGresAttValue *fields );
> int PQsettuplecallback( PQresult *res, PQtuplecallback cb );
>
> fields is simply a pointer to an array of nfields such structures.
> Users can do whatever they want with the info, store it in their own
> structure, parse it, throw it away, send it over a network, etc. With
> this callback I could probably implement your function above fairly
> straightforwardly.

Sure you could but you're forced to do more copying around of the data
(copy into the PGresAttValue, copy out of it into your structure array).
If you want something more complex then a callback makes more sense but
I'm of the opinion that we're talking about a 90/10 or 80/20 split here
in terms of dump-into-memory array vs. do-something-more-complicated.
And that opinion isn't *solely* based on Oracle providing a similar
mechanism such that probably quite a few Oracle apps are written exactly
as I suggest (I don't think OCI8 has a callback like you're proposing at
all...).

Just to point out, we could do what you're proposing by letting people
look at PQresult during an async too.. Except, of course, libpq
would need to allocate/deallocate all the PQresults and have some way of
knowing which have been used by the caller and which havn't.

Thanks,

Stephen

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)

iD8DBQFEPnQ5rzgMPqB3kigRApA4AJwNebfByJZ7crSNcVdRnF YFvAjr6ACfSPVf
6RKXXG8/qcYIok2+GL6v8rA=
=17C3
-----END PGP SIGNATURE-----

Martijn van Oosterhout <kleptog@svana.org> writes:
> ...you seem to be suggesting that all the data be stored in one big memory
> block at resultset.

I didn't like that either; it assumes far too much about what the
application needs to do. I think what's wanted is a callback hook
that lets the app decide where and how to store the data. Not sure
what the hook's API should be exactly, though.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Stephen Frost <sfrost@snowman.net> writes:
> I can see how having a callback would be useful though I think for a
> good number of cases it's just going to be populating a memory region
> with it and we could cover that common case by providing an API for
> exactly that.

We already have that: it's called the existing libpq API.

The only reason I can see for offering any new feature in this area is
to cater to apps that want to transform the data representation
on-the-fly, not merely dump it into an area that will be the functional
equivalent of a PGresult. So it really has to be a callback.

> The other issue with a callback is that libpq would have
> to either call the callback for each value (not my preference)

Why not? That would eliminate a number of problems.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Martijn van Oosterhout <kleptog@svana.org> writes:

> On Thu, Apr 13, 2006 at 11:14:57AM -0400, Greg Stark wrote:
> > That could be useful for applications but I think a driver really wants to
> > retain control of the flow of control. To make use of a callback it would have
> > to have an awkward dance of calling whatever function gives libpq license to
> > call the callback, having the callback stuff the data in a temporary space,
> > then checking for new data in the temporary space, and returning it to the
> > user.
>
> We have an asyncronous interface. I was thinking like:
>
> sub mycallback(res,data)
> {
> /* stuff data in memory structure */
> if( row_count > 5 )
> gotenough = TRUE;
> }
>
> If you set non-blocking you can even go off and do other things while
> waiting. No need for temporary space...
>
> Does this seem too complex?

There's nothing wrong with a callback interface for applications. They can
generally have the callback function update the display or output to a file or
whatever they're planning to do with the data.

However drivers don't generally work that way. Drivers have functions like:

$q = prepare("select ...");
$q->execute();
while ($row = $q->fetch()) {
print $row->{column};
}

To handle that using a callback interface would require that $q->fetch invoke
some kind of pqCheckForData() which would upcall to the callback with the
available data. The callback would have to stuff the data somewhere. Then
fetch() would check to see if there was data there and return it to the user.

It's doable but dealing with this impedance mismatch between the interfaces
necessitates extra steps. That means extra copying and extra function calls.



--
greg


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > I can see how having a callback would be useful though I think for a
> > good number of cases it's just going to be populating a memory region
> > with it and we could cover that common case by providing an API for
> > exactly that.
>
> We already have that: it's called the existing libpq API.

Right, and it sucks for doing large amounts of transfer through it.

> The only reason I can see for offering any new feature in this area is
> to cater to apps that want to transform the data representation
> on-the-fly, not merely dump it into an area that will be the functional
> equivalent of a PGresult. So it really has to be a callback.

It's only the functional equivalent when you think all the world is a
Postgres app, which is just not the case.

> > The other issue with a callback is that libpq would have
> > to either call the callback for each value (not my preference)
>
> Why not? That would eliminate a number of problems.

For one thing, it's certainly possible the callback (to do a data
transform like you're suggesting) would want access to the other
information in a given tuple. Having to store a partial tuple in a
temporary area which has to be built up to the full tuple before you can
actually process it wouldn't be all that great. This is much less true
for the contents of an entire table (that you would need it all before
being able to perform the transforms). It would also be an awful lot of
calls.

Thanks,

Stephen

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)

iD8DBQFEPnefrzgMPqB3kigRAv49AKCAyTURxQuKSy+kL20z7v GrWS5zawCfchcy
EiHqUrng2hl6YaTgkDoaOq4=
=mBQE
-----END PGP SIGNATURE-----

Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> The only reason I can see for offering any new feature in this area is
>> to cater to apps that want to transform the data representation
>> on-the-fly, not merely dump it into an area that will be the functional
>> equivalent of a PGresult. So it really has to be a callback.

> It's only the functional equivalent when you think all the world is a
> Postgres app, which is just not the case.

If we are dumping data into a simple memory block in a format dictated
by libpq, then we haven't done a thing to make the app's use of that
data independent of libpq. Furthermore, because that format has to be
generalized (variable-length fields, etc), it will not be noticeably
easier to use than the existing PQresult API.

What I would envision as a typical use of a callback is to convert the
data and store it in a C struct designed specifically for a particular
query's known result structure (say, a few ints, a string of a known
maximum length, etc). libpq can't do that, but a callback could do it
easily.

The fixed-memory-block approach also falls over when considering results
of uncertain maximum size. Lastly, it doesn't seem to me to respond at
all to the ODBC needs that started this thread: IIUC, they want each row
separately malloc'd so that they can free selected rows from the
completed resultset.

>>> The other issue with a callback is that libpq would have
>>> to either call the callback for each value (not my preference)
>>
>> Why not? That would eliminate a number of problems.

> For one thing, it's certainly possible the callback (to do a data
> transform like you're suggesting) would want access to the other
> information in a given tuple. Having to store a partial tuple in a
> temporary area which has to be built up to the full tuple before you can
> actually process it wouldn't be all that great.

So instead, you'd prefer to *always* store partial tuples in a temporary
area, thereby making sure the independent-field-conversions case has
performance just as bad as the dependent-conversions case.
I can't follow that reasoning.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Thu, Apr 13, 2006 at 11:54:33AM -0400, Stephen Frost wrote:

<snip>

> Sure you could but you're forced to do more copying around of the data
> (copy into the PGresAttValue, copy out of it into your structure array).
> If you want something more complex then a callback makes more sense but
> I'm of the opinion that we're talking about a 90/10 or 80/20 split here
> in terms of dump-into-memory array vs. do-something-more-complicated.

I think we're talking cross-purposes here. You seem to be interested in
making another way to get the data. What I'm trying to do is create an
interface flexible enough that no-one would ever want write their own
wire-protocol parser because they can get libpq to do it. This probably
falls into the 10% portion.

The use of PGresAttValue was deliberate. libpq already uses this so it
costs nothing. Also, the memory pointed to is allocated very cheaply
within libpq. The intention is that users can either choose to use that
(great for read-only, i.e. 90% of the time) or copy it *only* if they
want to (what psqlODBC wants to do).

Basically, your solution doesn't handle the use case of psqlODBC which
is specifically what I'm aiming at here...

> Just to point out, we could do what you're proposing by letting people
> look at PQresult during an async too.. Except, of course, libpq
> would need to allocate/deallocate all the PQresults and have some way of
> knowing which have been used by the caller and which havn't.

Eh? You only need one PQresult...

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFEPoniIB7bNG8LQkwRAnBcAJ0UPAOTs+LfjF0jZNrwBX D2VHMBswCgiZ62
qI2DFyeYZDI/mBxkcu5hFxo=
=ircX
-----END PGP SIGNATURE-----

On Thu, Apr 13, 2006 at 12:02:56PM -0400, Greg Stark wrote:
> There's nothing wrong with a callback interface for applications. They can
> generally have the callback function update the display or output to a file or
> whatever they're planning to do with the data.
>
> However drivers don't generally work that way. Drivers have functions like:

As I pointed out in another email, this change is not aimed at
applications doing fetch_next, but specifically at drivers like
psqlODBC which have a very special way of handling resultsets, in this
case, updateable resultsets. The aim is to work out why people are
writing their own wire-protocol parsers. To find out the deficiency in
libpq that prevents them using it.

I agree, for what you're talking about I don't think a callback is at
all relevent.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFEPosfIB7bNG8LQkwRAsrLAJ0fQ/bgCTYd870/neZ/T1b0/SlDJgCff0TV
wt5QQtwsHgmo2jFmaJspk8o=
=ubgA
-----END PGP SIGNATURE-----

Tom Lane <tgl@sss.pgh.pa.us> writes:

> So instead, you'd prefer to *always* store partial tuples in a temporary
> area, thereby making sure the independent-field-conversions case has
> performance just as bad as the dependent-conversions case.
> I can't follow that reasoning.

I think there's some confusion about what problem this is aiming to solve. I
thought the primary problem ODBC and other drivers have is just that they want
to be able to fetch whatever records are available instead of waiting for the
entire query results to be ready.

All it sounded like to me was a need for a function that would wait until n
records were available (or perhaps n bytes worth of records) then return.

You seem to be talking about a much broader set of problems to solve.

--
greg


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Greg Stark <gsstark@mit.edu> writes:
> I think there's some confusion about what problem this is aiming to solve. I
> thought the primary problem ODBC and other drivers have is just that they want
> to be able to fetch whatever records are available instead of waiting for the
> entire query results to be ready.

No, that's not what I'm thinking about at all, and I don't think Martijn
is either. The point here is that ODBC wants to store the resultset in
a considerably different format from what libpq natively provides, and
we'd like to avoid the conversion overhead.

Now, a callback function could be (ab)used for the purpose of not
waiting, very easily: either do real processing on each row for itself,
or signal the main app via some outside-the-API mechanism whenever it
has stored N rows. The question the app author would have to ask
himself is whether he needs to undo that processing if the query fails
further on, and if so how to do that. But that need not be our problem.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly