Horton
Menu

Thread

  1. Re: [HACKERS] Re: [INTERFACES] retrieving varchar size

    Tom Lane <tgl@sss.pgh.pa.us> — 1998-04-26T23:13:28Z

    Bruce Momjian <maillist@candle.pha.pa.us> writes:
> My idea is to make a PQexecv() just like PQexec, except it returns an
> array of results, with the end of the array terminated with a NULL,
> [ as opposed to my idea of returning PGresults one at a time ]

Hmm.  I think the one-at-a-time approach is probably better, mainly
because it doesn't require libpq to have generated all the PGresult
objects before it can return the first one.

Here is an example in which the array approach doesn't work very well:

	QUERY: copy stdin to relation ; select * from relation

What we want is for the application to receive a PGRES_COPY_IN result,
perform the data transfer, call PQendcopy, and then receive a PGresult
for the select.

I don't see any way to make this work if the library has to give back
an array of results right off the bat.  With the other method, PQendcopy
will read the select command's output and stuff it into the (hidden)
result queue.  Then when the application calls PQnextResult, presto,
there it is.  Correct logic for an application that submits multi-
command query strings would be something like

	result = PQexec(conn, query);

	while (result) {
		switch (PQresultStatus(result)) {
		...
		case PGRES_COPY_IN:
			// ... copy data here ...
			if (PQendcopy(conn))
				reportError();
			break;
		...
		}

		PQclear(result);
		result = PQnextResult(conn);
	}


Another thought: we might consider making PQexec return as soon as it's
received the first query result, thereby allowing the frontend to
overlap its processing of this result with the backend's processing of
the rest of the query string.  Then, PQnextResult would actually read a
new result (or the "I'm done" message), rather than just return a result
that had already been stored.  I wasn't originally thinking of
implementing it that way, but it seems like a mighty attractive idea.
No way to do it if we return results as an array.

>> I'd really like to see is PQendcopy returning a PGresult that indicates
>> success or failure of the copy, and then additional results could be
>> queued up behind that for retrieval with PQnextResult.

> Not sure on this one.  If we change the API, we have to have a good
> reason to do it.  API additions are OK.

Well, we can settle for having PQendcopy return 0 or 1 as it does now.
It's not quite as clean as having it return a real PGresult, but it's
probably not worth breaking existing apps just to improve the
consistency of the API.  It'd still be possible to queue up subsequent
commands' results (if any) in the result queue.

>> 2. Copy In and Copy Out data ought to be part of the protocol, that
>> is every line of copy in/out data ought to be prefixed with a message
>> type code.  Fixing this might be more trouble than its worth however,
>> if there are any applications that don't go through PQgetline/PQputline.

> Again, if we clearly document the change, we are far enough from 6.4
> that perl and other people will handle the change by the time 6.4 is
> released.  Changes the affect user apps is more difficult.

I have mixed feelings about this particular item.  It would make the
protocol more robust, but it's not clear that the gain is worth the
risk of breaking any existing apps.  I'm willing to drop it if no one
else is excited about it.

			regards, tom lane

  2. Re: [HACKERS] Re: [INTERFACES] retrieving varchar size

    Bruce Momjian <maillist@candle.pha.pa.us> — 1998-04-26T23:41:33Z

    > 
> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > My idea is to make a PQexecv() just like PQexec, except it returns an
> > array of results, with the end of the array terminated with a NULL,
> > [ as opposed to my idea of returning PGresults one at a time ]
> 
> Hmm.  I think the one-at-a-time approach is probably better, mainly
> because it doesn't require libpq to have generated all the PGresult
> objects before it can return the first one.
> 
> Here is an example in which the array approach doesn't work very well:
> 
> 	QUERY: copy stdin to relation ; select * from relation
> 
> What we want is for the application to receive a PGRES_COPY_IN result,
> perform the data transfer, call PQendcopy, and then receive a PGresult
> for the select.
> 
> I don't see any way to make this work if the library has to give back
> an array of results right off the bat.  With the other method, PQendcopy
> will read the select command's output and stuff it into the (hidden)
> result queue.  Then when the application calls PQnextResult, presto,
> there it is.  Correct logic for an application that submits multi-
> command query strings would be something like

OK, you just need to remember to throw away any un-called-for results if
they do another PQexec without retrieving all the results returned by
the backend.

> Another thought: we might consider making PQexec return as soon as it's
> received the first query result, thereby allowing the frontend to
> overlap its processing of this result with the backend's processing of
> the rest of the query string.  Then, PQnextResult would actually read a
> new result (or the "I'm done" message), rather than just return a result
> that had already been stored.  I wasn't originally thinking of
> implementing it that way, but it seems like a mighty attractive idea.
> No way to do it if we return results as an array.

Yep.


> Well, we can settle for having PQendcopy return 0 or 1 as it does now.
> It's not quite as clean as having it return a real PGresult, but it's
> probably not worth breaking existing apps just to improve the
> consistency of the API.  It'd still be possible to queue up subsequent
> commands' results (if any) in the result queue.

OK.

> > Again, if we clearly document the change, we are far enough from 6.4
> > that perl and other people will handle the change by the time 6.4 is
> > released.  Changes the affect user apps is more difficult.
> 
> I have mixed feelings about this particular item.  It would make the
> protocol more robust, but it's not clear that the gain is worth the
> risk of breaking any existing apps.  I'm willing to drop it if no one
> else is excited about it.

It's up to you.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

  3. Re: retrieving varchar size

    Michael Hirohama <kamesan@ricochet.net> — 1998-04-27T00:55:54Z

    The historical reason why the POSTGRES backend is required to send multiple
result sets is to support cursors on queries involving type inheritance and
anonymous target lists.

	begin
	declare c cursor for
		select e.oid, e.* from EMP* e
	fetch 10 in c
	...

To handle the command sequence above, frontend applications would need to
be provided with a new result descriptor when the "fetch 10 in c" crosses a
result set boundary.

  4. Re: [HACKERS] Re: [INTERFACES] retrieving varchar size

    David Gould <dg@illustra.com> — 1998-04-27T01:46:42Z

    > Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > My idea is to make a PQexecv() just like PQexec, except it returns an
> > array of results, with the end of the array terminated with a NULL,
> > [ as opposed to my idea of returning PGresults one at a time ]
> 
> Hmm.  I think the one-at-a-time approach is probably better, mainly
> because it doesn't require libpq to have generated all the PGresult
> objects before it can return the first one.
> 
> Here is an example in which the array approach doesn't work very well:
> 
> 	QUERY: copy stdin to relation ; select * from relation
> 
> What we want is for the application to receive a PGRES_COPY_IN result,
> perform the data transfer, call PQendcopy, and then receive a PGresult
> for the select.
> 
> I don't see any way to make this work if the library has to give back
> an array of results right off the bat.  With the other method, PQendcopy
> will read the select command's output and stuff it into the (hidden)
> result queue.  Then when the application calls PQnextResult, presto,
> there it is.  Correct logic for an application that submits multi-
> command query strings would be something like
> 
> 	result = PQexec(conn, query);
> 
> 	while (result) {
> 		switch (PQresultStatus(result)) {
> 		...
> 		case PGRES_COPY_IN:
> 			// ... copy data here ...
> 			if (PQendcopy(conn))
> 				reportError();
> 			break;
> 		...
> 		}
> 
> 		PQclear(result);
> 		result = PQnextResult(conn);
> 	}
> 
> 
> Another thought: we might consider making PQexec return as soon as it's
> received the first query result, thereby allowing the frontend to
> overlap its processing of this result with the backend's processing of
> the rest of the query string.  Then, PQnextResult would actually read a
> new result (or the "I'm done" message), rather than just return a result
> that had already been stored.  I wasn't originally thinking of
> implementing it that way, but it seems like a mighty attractive idea.
> No way to do it if we return results as an array.


Or we might even make PQexec return as soon as the query is sent and parsed.
It could ruturn a handle to the query that could be used to get results later.
This is pretty much exactly in line with the way the Perl DBI stuff works and
I think also odbc.

     queryhandle = PQexec(conn, querystring);

     while (result = PQgetresult(queryhandle)) {
         do stuff with result;
         PQclear(result);
     }

This protocol allows for multiple results per query, and asynchronous operation
before getting the result.

Perhaps a polling form might be added too:

    queryhandle = PQexec(conn, querystring);

    while (1) {
        handle_user_interface_events();

        if (PQready(queryhandle)) {
             result = PQgetresult(queryhandle);
             if (result == NULL)
                 break;
             do stuff with result;
             PQclear(result);
        }
    }

-dg

David Gould            dg@illustra.com           510.628.3783 or 510.305.9468 
Informix Software  (No, really)         300 Lakeside Drive  Oakland, CA 94612
"(Windows NT) version 5.0 will build on a proven system architecture
 and incorporate tens of thousands of bug fixes from version 4.0."
                 -- <http://www.microsoft.com/y2k.asp?A=7&B=5>

  5. Re: [HACKERS] Re: retrieving varchar size

    Tom Lane <tgl@sss.pgh.pa.us> — 1998-04-30T20:36:05Z

    Michael Hirohama <kamesan@ricochet.net> wrote:
> The historical reason why the POSTGRES backend is required to send multiple
> result sets is to support cursors on queries involving type inheritance and
> anonymous target lists.
> 	begin
> 	declare c cursor for
> 		select e.oid, e.* from EMP* e
> 	fetch 10 in c
> 	...
> To handle the command sequence above, frontend applications would need to
> be provided with a new result descriptor when the "fetch 10 in c" crosses a
> result set boundary.

I tried this and was unable to produce a failure.  It looks like the
select only returns the set of fields applicable to the base class,
regardless of what additional fields may be possessed by some
subclasses.  Which, in fact, is more or less what I'd expect.

Is Michael remembering some old behavior that is no longer implemented?
And if so, is the old or new behavior the correct one?

			regards, tom lane

  6. Re: retrieving varchar size

    Michael Hirohama <kamesan@ricochet.net> — 1998-05-14T23:51:23Z

    Tom Lane <tgl@sss.pgh.pa.us> wrote on Thu, 30 Apr 1998:
>Michael Hirohama <kamesan@ricochet.net> wrote:
>> The historical reason why the POSTGRES backend is required to send multiple
>> result sets is to support cursors on queries involving type inheritance and
>> anonymous target lists.
>> 	begin
>> 	declare c cursor for
>> 		select e.oid, e.* from EMP* e
>> 	fetch 10 in c
>> 	...
>> To handle the command sequence above, frontend applications would need to
>> be provided with a new result descriptor when the "fetch 10 in c" crosses a
>> result set boundary.
>
>I tried this and was unable to produce a failure.  It looks like the
>select only returns the set of fields applicable to the base class,
>regardless of what additional fields may be possessed by some
>subclasses.  Which, in fact, is more or less what I'd expect.
>
>Is Michael remembering some old behavior that is no longer implemented?
>And if so, is the old or new behavior the correct one?
>
>			regards, tom lane

Forgive me for my slow memory: I remember now that there was a decision
made to not support exploding the expansion of anonymous target lists
because of the extra complexity it would introduce into the parser and
executor.  Thus, Postgres would return at most result set per query
processed.  Smart users and smart applications would be able to navigate
the inheritance hierarchy by explicitly specifying tables and columns as
needed.

  ~~ <kamesan@ricochet.net>