> -----Original Message-----
> From: Zeugswetter Andreas SB SD [mailto:ZeugswetterA@spardat.at] 
> Sent: 12 March 2003 09:50
> To: Hiroshi Inoue; Tom Lane
> Cc: Bruce Momjian; pgsql-hackers@postgresql.org; 
> pgsql-interfaces@postgresql.org
> Subject: Re: [HACKERS] Roadmap for FE/BE protocol redesign
> 
> > The ODBC function SQLDescribeCol or SQLColAttribute
> > requires various kind of fields' info.
> 
> RETCODE SQL_API SQLDescribeCol(
>     HSTMT       hstmt,
>     UWORD       icol,
>     UCHAR  FAR *szColName,
>     SWORD       cbColNameMax,
>     SWORD  FAR *pcbColName,
>     SWORD  FAR *pfSqlType,
>     UDWORD FAR *pcbColDef,
>     SWORD  FAR *pibScale,
>     SWORD  FAR *pfNullable);
> 
> So you see it does not need tablename or schema.

No, but with them we can avoid cluttering the wire protocol with fields
for all this, and the JDBC required data. With 2 numeric columns
(attrelid, attnum), any application/interface can query the system
catalogs easily for whatever extra info they like.

Regards, Dave.
Wed, 12 Mar 2003 14:49:15 -0000
"Dave Page" <dpage@vale-housing.co.uk>
Re: Roadmap for FE/BE protocol redesign
This may be problematic in the future if we change attrelid, attnum.

My preference would be to be able to query the backend for the
information if I need it. It is rarely required.

ie give me the meta data for the last resultset.

Dave

On Wed, 2003-03-12 at 09:49, Dave Page wrote:
> > -----Original Message-----
> > From: Zeugswetter Andreas SB SD [mailto:ZeugswetterA@spardat.at] 
> > Sent: 12 March 2003 09:50
> > To: Hiroshi Inoue; Tom Lane
> > Cc: Bruce Momjian; pgsql-hackers@postgresql.org; 
> > pgsql-interfaces@postgresql.org
> > Subject: Re: [HACKERS] Roadmap for FE/BE protocol redesign
> > 
> > > The ODBC function SQLDescribeCol or SQLColAttribute
> > > requires various kind of fields' info.
> > 
> > RETCODE SQL_API SQLDescribeCol(
> >     HSTMT       hstmt,
> >     UWORD       icol,
> >     UCHAR  FAR *szColName,
> >     SWORD       cbColNameMax,
> >     SWORD  FAR *pcbColName,
> >     SWORD  FAR *pfSqlType,
> >     UDWORD FAR *pcbColDef,
> >     SWORD  FAR *pibScale,
> >     SWORD  FAR *pfNullable);
> > 
> > So you see it does not need tablename or schema.
> 
> No, but with them we can avoid cluttering the wire protocol with fields
> for all this, and the JDBC required data. With 2 numeric columns
> (attrelid, attnum), any application/interface can query the system
> catalogs easily for whatever extra info they like.
> 
> Regards, Dave.
-- 
Dave Cramer <Dave@micro-automation.net>
-- 
Dave Cramer <dave@fastcrypt.com>
Cramer Consulting


12 Mar 2003 09:55:49 -0500
Dave Cramer <dave@fastcrypt.com>
Re: Roadmap for FE/BE protocol redesign
"Dave Page" <dpage@vale-housing.co.uk> writes:
> No, but with them we can avoid cluttering the wire protocol with fields
> for all this, and the JDBC required data. With 2 numeric columns
> (attrelid, attnum), any application/interface can query the system
> catalogs easily for whatever extra info they like.

This is my feeling also.  We shouldn't try to guess in the protocol
exactly what set of information will be wanted by a frontend; we should
just provide the catalog keys needed to look up whatever is wanted.

I like attrelid/attnum because (a) it's compact --- not much added
overhead for apps that don't care; and (b) it's a primary key, which
names are not.  The relid/attnum identity of a column will remain the
same as long as it exists.  A name-based API breaks the moment some
other user renames things; similar objections could be raised against
attlognum, if we had it.

The point about apps breaking if the system catalog layout changes is a
fair one, but that situation would be no worse than it's ever been; ODBC
and JDBC have coped well enough so far.  In any case the best solution
would be to offer functions that hide the details of where to find any
given bit of info.  We've got a few like that already (format_type() for
example) and I have no problem with adding more.

			regards, tom lane

Wed, 12 Mar 2003 10:49:54 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Roadmap for FE/BE protocol redesign 
Tom Lane wrote:
> 
> "Dave Page" <dpage@vale-housing.co.uk> writes:
> > No, but with them we can avoid cluttering the wire protocol with fields
> > for all this, and the JDBC required data. With 2 numeric columns
> > (attrelid, attnum), any application/interface can query the system
> > catalogs easily for whatever extra info they like.
> 
> This is my feeling also.  We shouldn't try to guess in the protocol
> exactly what set of information will be wanted by a frontend; we should
> just provide the catalog keys needed to look up whatever is wanted.

Does looking up by the catalog keys take no cost ?

regards,
Hiroshi Inoue
	http://www.geocities.jp/inocchichichi/psqlodbc/

Thu, 13 Mar 2003 15:29:38 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: Roadmap for FE/BE protocol redesign
It's rumoured that Hiroshi Inoue once said:
> Tom Lane wrote:
>>
>> "Dave Page" <dpage@vale-housing.co.uk> writes:
>> > No, but with them we can avoid cluttering the wire protocol with
>> > fields for all this, and the JDBC required data. With 2 numeric
>> > columns (attrelid, attnum), any application/interface can query the
>> > system catalogs easily for whatever extra info they like.
>>
>> This is my feeling also.  We shouldn't try to guess in the protocol
>> exactly what set of information will be wanted by a frontend; we
>> should just provide the catalog keys needed to look up whatever is
>> wanted.
>
> Does looking up by the catalog keys take no cost ?

Obviously there is cost, but doing a lookup only on demand, has got to be
cheaper in the long run than including the entire column definition in the
message whether it's wanted or not?
Regards, Dave.



Thu, 13 Mar 2003 09:52:36 -0000 (GMT)
"Dave Page" <dpage@vale-housing.co.uk>
Re: Roadmap for FE/BE protocol redesign
Dave Page wrote:
> 
> It's rumoured that Hiroshi Inoue once said:
> > Tom Lane wrote:
> >>
> >> "Dave Page" <dpage@vale-housing.co.uk> writes:
> >> > No, but with them we can avoid cluttering the wire protocol with
> >> > fields for all this, and the JDBC required data. With 2 numeric
> >> > columns (attrelid, attnum), any application/interface can query the
> >> > system catalogs easily for whatever extra info they like.
> >>
> >> This is my feeling also.  We shouldn't try to guess in the protocol
> >> exactly what set of information will be wanted by a frontend; we
> >> should just provide the catalog keys needed to look up whatever is
> >> wanted.
> >
> > Does looking up by the catalog keys take no cost ?
> 
> Obviously there is cost, but doing a lookup only on demand, has got to be
> cheaper in the long run than including the entire column definition in the
> message whether it's wanted or not?

So if there are 100 fields, should we ask the backend 
the column name 100 times ?

regards,
Hiroshi Inoue
	http://www.geocities.jp/inocchichichi/psqlodbc/

Thu, 13 Mar 2003 19:03:44 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: Roadmap for FE/BE protocol redesign
Hiroshi Inoue kirjutas N, 13.03.2003 kell 12:03:
> Dave Page wrote:
> > 
> > > Does looking up by the catalog keys take no cost ?
> > 
> > Obviously there is cost, but doing a lookup only on demand, has got to be
> > cheaper in the long run than including the entire column definition in the
> > message whether it's wanted or not?
> 
> So if there are 100 fields, should we ask the backend 
> the column name 100 times ?

or once for all columns if you prefer using IN.

> regards,
> Hiroshi Inoue
> 	http://www.geocities.jp/inocchichichi/psqlodbc/
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

13 Mar 2003 12:07:28 +0200
Hannu Krosing <hannu@tm.ee>
Re: Roadmap for FE/BE protocol redesign
> > Obviously there is cost, but doing a lookup only on demand, has got to be
> > cheaper in the long run than including the entire column definition in the
> > message whether it's wanted or not?
>
> So if there are 100 fields, should we ask the backend
> the column name 100 times ?

No, you do a single select that returns 100 results...

Then you cache them in your frontend...

Chris



Thu, 13 Mar 2003 18:07:34 +0800 (WST)
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Roadmap for FE/BE protocol redesign
> -----Original Message-----
> From: Christopher Kings-Lynne [mailto:chriskl@familyhealth.com.au] 
> 
> > > Obviously there is cost, but doing a lookup only on 
> demand, has got to be
> > > cheaper in the long run than including the entire column 
> definition in the
> > > message whether it's wanted or not?
> >
> > So if there are 100 fields, should we ask the backend
> > the column name 100 times ?
> 
> No, you do a single select that returns 100 results...

Does a single select mean a fast select ?

regards,
Hiroshi Inoue 


Fri, 14 Mar 2003 00:05:22 +0900
"Hiroshi Inoue" <inoue@tpf.co.jp>
Re: Roadmap for FE/BE protocol redesign
"Dave Page" <dpage@vale-housing.co.uk> writes:
> It's rumoured that Hiroshi Inoue once said:
>> Does looking up by the catalog keys take no cost ?

> Obviously there is cost, but doing a lookup only on demand, has got to be
> cheaper in the long run than including the entire column definition in the
> message whether it's wanted or not?

More to the point, the cost is paid by applications that want the
functionality, and not by those that don't.

It'd probably be reasonable for client libraries to maintain a cache
of column info, so that they only have to query the backend about a
particular column ID once per connection.  (This is one reason why
I want attrelid/attnum to be the info given on the wire: it's stable
enough to be used over a whole session to index such a cache.  Names
could not safely be used that way.)

			regards, tom lane

Thu, 13 Mar 2003 10:19:27 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Roadmap for FE/BE protocol redesign 
> > No, you do a single select that returns 100 results...
> 
> Does a single select mean a fast select ?

Provably always?  Perhaps not.

But requesting data via a single select statement returning 100 rows
means that you only have to submit the request once, and it means that
the result set only has to be set up once, which is quite likely to save
some work.

If you're going to read 100 lines of data from a file, would it make
more sense to:

 a) Open the file 100 times, fseek() to the right spot, and then close
    the file, or

 b) Open the file once, and do 100 fseek() calls?

If you're just opening a file, there's not a huge amount of overhead
associated with that.  Setting up a DBMS query is quite a bit more
costly, so that the effort looks like:

 a) Open a session connection, submit a query for one record, and
    process it, then close the connection, and do this 100 times, 
    versus

 b) Open a session connection, submit a query returning 100 records,
    and process them all.

The overhead surrounding submitting and processing the query is quite a
bit more than that involved in opening/closing files, so cutting down on
it should be pretty worthwhile...
--
(reverse (concatenate 'string "gro.gultn@" "enworbbc"))
http://cbbrowne.com/info/nonrdbms.html
"When we understand knowledge-based systems, it will be as before 
-- except our fingertips will have been singed."  -- Alan Perlis

Thu, 13 Mar 2003 10:35:43 -0500
cbbrowne@cbbrowne.com
Re: Roadmap for FE/BE protocol redesign 
Tom Lane wrote:
> 
> "Dave Page" <dpage@vale-housing.co.uk> writes:
> > It's rumoured that Hiroshi Inoue once said:
> >> Does looking up by the catalog keys take no cost ?
> 
> > Obviously there is cost, but doing a lookup only on demand, has got to be
> > cheaper in the long run than including the entire column definition in the
> > message whether it's wanted or not?
> 
> More to the point, the cost is paid by applications that want the
> functionality, and not by those that don't.
> 
> It'd probably be reasonable for client libraries to maintain a cache
> of column info, so that they only have to query the backend about a
> particular column ID once per connection.

Is it a kind of thing that the server forces the clients
easily ?
Hmm as for PREPAREd statements, it seems much better to
implement functions which returns fields info for the
statement than relying on such a protocol level change.
 
regards,
Hiroshi Inoue
	http://www.geocities.jp/inocchichichi/psqlodbc/

Fri, 14 Mar 2003 08:58:01 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: Roadmap for FE/BE protocol redesign
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> Hmm as for PREPAREd statements, it seems much better to
> implement functions which returns fields info for the
> statement than relying on such a protocol level change.

Well, we're changing the protocol anyway for other purposes, so the
extra burden of a change here doesn't seem large.  I think it's more
useful to worry about what functionality we want than to worry about
whether it's changed...

			regards, tom lane

Thu, 13 Mar 2003 19:11:23 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Roadmap for FE/BE protocol redesign 