Re: [HACKERS] Roadmap for FE/BE protocol redesign
* Backend should pass its version number, database encoding, default client encoding, and possibly other data (any ideas?) to frontend during startup, to avoid need for explicit queries to get this info. We could also consider eliminating SET commands sent by libpq in favor of adding variable settings to startup packet's PGOPTIONS field. Ideally we could get back to the point where a standard connection startup takes only one packet in each direction. This handles the JDBC needs (currently on startup the jdbc driver selects the database encoding and version number and sets the datestyle and autocommit parameters). One addition I would personally like to see (it comes up in my apps code) is the ability to detect wheather the server is big endian or little endian. When using binary cursors this is necessary in order to read int data. Currently I issue a 'select 1' statement at connection startup to determine what format the server is using. Other things I would like to see to help jdbc: 1) More information about the attributes selected in a query (I see there is an entire thread on this already) to minimize the work necessary to implement updateable result sets as defined by the jdbc spec. 2) Better support for domains. Currently the jdbc driver is broken with regards to domains (although no one has reported this yet). The driver will treat a datatype that is a domain as an unknown/unsupported datatype. It would be great if the T response included the 'base' datatype for a domain attribute so that the driver would know what parsing routines to call to convert to/from the text representation the backend expects. 3) Protocol level support for CURSORs. It would be nice if cursor support was done at the protocol level and not as a SQL command. The current default behavior of returning all results from a query in the query response message is often a problem (can easily lead to out of memory problems for poorly written queries). So it is desirable to use cursors. But with the current implementation in SQL, cursors are not the appropriate choice if a query is only going to return one or a few rows. The reason is that using a cursor requires a minimum of three SQL statements: DECLARE, FETCH, CLOSE. The jdbc driver issues the DECLARE and FETCH in one server call, but the CLOSE needs to be a second call. Thus for simple one row selects (which in many cases are the majority of selects issued) using CURSORS requires two roundtrips to the server vs. one for the nonCursor case. This leaves me with a problem in the jdbc driver, I can either use standard fast/performant queries for single row selects that blowup with out of memory errors for large results, or I can use cursors and avoid large memory usage but hurt overall performance. What I have currently done is require that the developer call an extra method to turn on the use of cursors when they know that the cursor is going to return a large number of rows and leave the default be the non-cursor case. This works but requires that developers who are writing code to interact with multiple different databases, code differently for the postgres jdbc driver. And this is a problem since one of the goals of jdbc is to be able to write code that works against multiple different databases. So I would request the ability of the client to set a max rows parameter for query results. If a query were to return more than the max number of rows, the client would be given a handle (essentially a cursor name) that it could use to fetch additional sets of rows. 4) Protocol level support of PREPARE. In jdbc and most other interfaces, there is support for parameterized SQL. If you want to take advantage of the performance benefits of reusing parsed plans you have to use the PREPARE SQL statement. My complaint on doing this at the SQL level vs the protocol level is similar to the problem with cursors above. To use prepare you need to issue three SQL statements: PREPARE, EXCECUTE, DEALLOCATE. If you know ahead of time that you are going to reuse a statement many times doing PREPARE, EXECUTE, EXECUTE, ..., DEALLOCATE makes sense and can be a big win in performance. However if you only ever execute the statement once then you need to use two round trips (one for the PREPARE, EXECUTE and another for the DEALLOCATE) versus one round trip to execute the statement 'normally'. So it decreases performance to use prepares for all parameterized sql statements. So the current implementation in jdbc requires the user to issue a postgres specific call to turn on the use of prepared statements for those cases the developer knows will be a performance win. But this requires coding differently for postgres jdbc than for other databases. So being better able to handle this in the protocol would be nice. 5) Better support for "large values". Generally I recommend that users of jdbc use bytea to store large binary v
Re: [HACKERS] Roadmap for FE/BE protocol redesign
Dave Page wrote:
I don't know about JDBC, but ODBC could use it, and it would save a heck
of a lot of pain in apps like pgAdmin that need to figure out if a column
in an arbitrary resultset might be updateable.
At the moment there is some nasty code in pgAdmin II that attempts to
parse the SQL statement to figure out if the the resultset is updateable
by trying to figure out the number of relations in the query, whether any
of them is a view or sequence, whether there are any function calls or
expressions in the attribute list and so on. It then has to try to figure
out if there is a complete pkey in the resultset that can be used for the
update, or whether it should attempt an update based on all existing
values. That code is just plain nasty in VB. In pgAdmin III we've already
mentioned stealing bits of the PostgreSQL parser.
I will just add a "me to" here. This would be very useful for JDBC as
well. We go through the same hoops to support the jdbc spec that Dave
does. The jdbc spec has two features that require this level of
information:
1) For every result set you can ask for a ResultSetMetaData object.
This object provides you with the following methods:
getColumnCount()
isAutoIncrement(int column)
isCaseSensitive(int column)
isSearchable(int column)
isNullable(int column)
getColumnDisplaySize(int column)
getColumnLabel(int column)
getColumnName(int column)
getSchemaName(int column)
getPrecision(int column)
getScale(int column)
getTableName(int column)
getColumnTypeName(int column)
isReadOnly(int column)
isWritable(int column)
isDefinitelyWritable(int column)
Now one can state the spec is broken and it doesn't make sense to ask
this type of information about a query (and frankly I would agree with
you), but that doesn't mean that I don't need to support it anyway. So
anything that the server can do to make this easier is greatly
appreciated. And I believe ODBC has almost the exact same issue since
in general the JDBC spec was copied from the ODBC spec.
2) Updateable result sets. The jdbc spec allows the user to declare any
select statement to be updateable. This means that as you scroll
through the result (the ResultSet object) you can issue modify the data
and expect the jdbc driver to reflect that change back to the base
tables. The following if from the JDBC API doc:
* A set of updater methods were added to this interface
* in the JDBC 2.0 API (JavaTM 2 SDK,
* Standard Edition, version 1.2). The comments regarding parameters
* to the getter methods also apply to parameters to the
* updater methods.
*
* The updater methods may be used in two ways:
*
* to update a column value in the current row. In a scrollable
* ResultSet object, the cursor can be moved backwards
* and forwards, to an absolute position, or to a position
* relative to the current row.
* The following code fragment updates the NAME column
* in the fifth row of the ResultSet object
* rs and then uses the method updateRow
* to update the data source table from which rs was derived.
*
*
* rs.absolute(5); // moves the cursor to the fifth row of rs
* rs.updateString("NAME", "AINSWORTH"); // updates the
* // NAME column of row 5 to be AINSWORTH
* rs.updateRow(); // updates the row in the data source
*
*
* to insert column values into the insert row. An updatable
* ResultSet object has a special row associated with
* it that serves as a staging area for building a row to be inserted.
* The following code fragment moves the cursor to the insert row,
builds
* a three-column row, and inserts it into rs and into
* the data source table using the method insertRow.
*
*
* rs.moveToInsertRow(); // moves cursor to the insert row
* rs.updateString(1, "AINSWORTH"); // updates the
* // first column of the insert row to be AINSWORTH
* rs.updateInt(2,35); // updates the second column to be 35
* rs.updateBoolean(3, true); // updates the third row to true
* rs.insertRow();
* rs.moveToCurrentRow();
*
Now application developers love this functionality. It allows them to
implement fairly complex apps with very little sql knowledge. They only
need to know how to do a simple select statement and that is it. The
jdbc driver handles the rest for them automatically (updates, inserts,
deletes). As a jdbc maintainer I personally hate this functionality as
it is a real pain to implement, and can't work in any but the most
simple select statements. But is is part of the spec and needs to be
supported in the best manner possible.
thanks,
--Barry
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Roadmap for FE/BE protocol redesign
> > I'm still unclear on exactly what your needs are. In the first place,
> > are you expecting to obtain data from arbitrary SELECT statements, or
> > only from statements of the form "SELECT * FROM single_table"? You've
> > also been confusing as to whether you want transparency of views (ie,
> > does a select from a view return data about the view's nominal columns
> > or about the underlying base table columns?). What about cases
> > involving aggregates or grouping --- there may be simple Vars in the
> > target list, but they can hardly be thought to represent
> updatable values.
>
> These questions can't possibly be unique to PG -- other database
> vendors must have answered these questions for their implementations
> of ODBC/JDBC too, or their databases would give ODBC and JDBC client
> authors the same kinds of trouble.
>
> So ... how have these questions been answered by other database
> vendors?
They return type info (RowDescription ('T') message) for "prepare", like Tom
suggested.
See the more or less standardized ESQL/C data structure sqlda on what is needed
(label, type, length + precision, nullability, pointer to data (for binding host
variables), ...). Note that this struct is used for both directions (so when inserting
from host variables the interface knows what conversions need to be applied).
Andreas
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] Numbering of the next release: 8.0 vs 7.4
Hi, > Would it be cool to decide on the version numbering of our next release > like this: > > + If it looks like we'll have Win32 and/or PITR recovery in time for > the next release, we call it PostgreSQL 8.0 > > + If not, we call it 7.4 Wouldn't a new FE/BE protocol be a better reason to call it 8.0? Raising the major version number together with introducing a new protocol which causes incompatibilities between new clients and older servers seems like a logical combination... Just a thought... :) Sander. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Numbering of the next release: 8.0 vs 7.4
Justin Clift wrote: Hi everyone, Thinking about the numbering further. Would it be cool to decide on the version numbering of our next release like this: + If it looks like we'll have Win32 and/or PITR recovery in time for the next release, we call it PostgreSQL 8.0 + If not, we call it 7.4 Win32 and PITR are great big features that will take us a long way to the goal of Enterprise suitability. They're worth making some specific marketing/branding efforts about and making a big fuss, that why I'd like to see them in an 8.0 release. Sound feasible? Sounds reasonable, but from a "change" perspective, the FE/BE protocol, Win32, and PITR, I would say that this is a "new" PostgreSQL, thus should be 8.0. I thought when WAL was added that warrented a different major version, but hey, that's me. But, if the decision is to go for an 8.0, then it should be reasonable to be a little bit more aggresive about adding features and perhaps a few wish list items. What I mean is, if it is just a minor release, one just expects minor improvements and bug fixes. If it is a major release, then one expects an update of the "PostgreSQL vision." So, if the decision is to go with an 8.0, what would you guys say to having a roll call about stuff that is "possible" and "practical" and really design "PostgreSQL 8.0" as something fundimentally "newer" than 7.x. "8.0" could get the project some hype. It has been 7x for so many years. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Case insensitivity, and option?
I was at a client office reviewing some code. They use MSSQL and I noticed that: select * from table where field = 'blah'; gave the same results as: select * from table where field = 'BLah'; I was shocked. (a) because I know a lot of my code could be easier to write, and (b) that their code would break on every other database I am aware of. Does anyone know about this? Is it practical/desirable for PostgreSQL to have this as a configuration setting? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Numbering of the next release: 8.0 vs 7.4
On Wed, 2003-03-12 at 01:26, Tom Lane wrote: > Justin Clift <[EMAIL PROTECTED]> writes: > > Would it be cool to decide on the version numbering of our next release > > like this: > > + If it looks like we'll have Win32 and/or PITR recovery in time for > > the next release, we call it PostgreSQL 8.0 > > + If not, we call it 7.4 > > Works for me: release schedule is solid, what we call it gets decided > at the last minute ;-) > Personally I think Justin is a little off base with his criteria, since I see the FE/BE protocol changes as the real differentiator between an 8.0 and 7.4. Everyone is effected by a FE/BE protocol change, not nearly so many are effected by either win32 or PITR. And think of this crazy scenario: We release an 8.0 with PITR, then need either a 8.1 or a 9.0 with a FE/BE overhaul, then need a possible 10.0 because we've added win32... yuk. That said, I'll take Tom's position on this that we might as well worry about whether it's going to be 7.4 or 8.0 once we hit feature freeze; by then the whole discussion could be irrelevant. Robert Treat ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] 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:[EMAIL PROTECTED] > > Sent: 12 March 2003 09:50 > > To: Hiroshi Inoue; Tom Lane > > Cc: Bruce Momjian; [EMAIL PROTECTED]; > > [EMAIL PROTECTED] > > 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 <[EMAIL PROTECTED]> -- Dave Cramer <[EMAIL PROTECTED]> Cramer Consulting ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Numbering of the next release: 8.0 vs 7.4
mlw wrote: So, if the decision is to go with an 8.0, what would you guys say to having a roll call about stuff that is "possible" and "practical" and really design "PostgreSQL 8.0" as something fundimentally "newer" than 7.x. "8.0" could get the project some hype. It has been 7x for so many years. Sounds great. I just don't want it to take _ages_ to accomplish. :) Regards and best wishes, Justin Clift -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Case insensitivity, and option?
--le 12/03/2003 09:03 -0500, mlw écrivait :
| I was at a client office reviewing some code. They use MSSQL and I
| noticed that:
|
| select * from table where field = 'blah';
| gave the same results as:
| select * from table where field = 'BLah';
|
| I was shocked. (a) because I know a lot of my code could be easier to
| write, and (b) that their code would break on every other database I am
| aware of. Does anyone know about this?
|
| Is it practical/desirable for PostgreSQL to have this as a configuration
| setting?
Well, I quite don't see any difference with writing :
select * from table where lower(field) = lower('BLah');
--
Mathieu Arnold
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Case insensitivity, and option?
On Wed, 2003-03-12 at 09:03, mlw wrote: > I was at a client office reviewing some code. They use MSSQL and I > noticed that: > > select * from table where field = 'blah'; > gave the same results as: > select * from table where field = 'BLah'; > > I was shocked. (a) because I know a lot of my code could be easier to > write, and (b) that their code would break on every other database I am > aware of. Does anyone know about this? Same thing with MySQL. It's a royal pain in the ass. It makes using non-ascii (unicode for example) text near to impossible because of this. > Is it practical/desirable for PostgreSQL to have this as a configuration > setting? I think we already support this. Create a new character set with upper / lower case specified as being equal and PostgreSQL should behave as expected. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Numbering of the next release: 8.0 vs 7.4
On Wednesday 12 March 2003 09:55, Robert Treat wrote: > Personally I think Justin is a little off base with his criteria, since > I see the FE/BE protocol changes as the real differentiator between an > 8.0 and 7.4. Everyone is effected by a FE/BE protocol change, not nearly > so many are effected by either win32 or PITR. And think of this crazy > scenario: We release an 8.0 with PITR, then need either a 8.1 or a 9.0 > with a FE/BE overhaul, then need a possible 10.0 because we've added > win32... yuk. FWIW, the 6.4 protocol change didn't force a move from 6.3.2 to 7.0. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Numbering of the next release: 8.0 vs 7.4
Lamar Owen <[EMAIL PROTECTED]> writes: > FWIW, the 6.4 protocol change didn't force a move from 6.3.2 to 7.0. True, but that was a much smaller change than what we're contemplating here. AFAIR, those changes did not affect the majority of applications --- they only needed to relink with a newer client library, and voila they spoke the new protocol perfectly well. The planned changes for error handling (error codes, etc) will be something that will affect almost every app. They won't *need* to change, maybe, but they'll probably *want* to change. But let's wait till feature freeze to have this discussion; we'll know better by then exactly what we're talking about. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Numbering of the next release: 8.0 vs 7.4
Tom Lane wrote: Lamar Owen <[EMAIL PROTECTED]> writes: FWIW, the 6.4 protocol change didn't force a move from 6.3.2 to 7.0. True, but that was a much smaller change than what we're contemplating here. AFAIR, those changes did not affect the majority of applications --- they only needed to relink with a newer client library, and voila they spoke the new protocol perfectly well. The planned changes for error handling (error codes, etc) will be something that will affect almost every app. They won't *need* to change, maybe, but they'll probably *want* to change. But let's wait till feature freeze to have this discussion; we'll know better by then exactly what we're talking about. Yep, that sounds like the best idea. :-) Regards and best wishes, Justin Clift regards, tom lane -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Numbering of the next release: 8.0 vs 7.4
Justin Clift writes: > + If it looks like we'll have Win32 and/or PITR recovery in time for > the next release, we call it PostgreSQL 8.0 To me, those sound fairly unspectacular as reasons for 8.0. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Roadmap for FE/BE protocol redesign
Barry Lind <[EMAIL PROTECTED]> writes: > One addition I would personally like to see (it comes up in my apps > code) is the ability to detect wheather the server is big endian or > little endian. When using binary cursors this is necessary in order to > read int data. Actually, my hope is to eliminate that business entirely by standardizing the on-the-wire representation for binary data; note the reference to send/receive routines in the original message. For integer data this is simple enough: network byte order will be it. I'm not sure yet what to do about float data. > 2) Better support for domains. Currently the jdbc driver is broken with > regards to domains (although no one has reported this yet). The driver > will treat a datatype that is a domain as an unknown/unsupported > datatype. It would be great if the T response included the 'base' > datatype for a domain attribute so that the driver would know what > parsing routines to call to convert to/from the text representation the > backend expects. I'm unconvinced that we need do this in the protocol, as opposed to letting the client figure it out with metadata inquiries. If we should, I'd be inclined to just replace the typeid field with the base typeid, and not mention the domain to the frontend at all. Comments? > So I would request the ability of the client to set a max rows parameter >for query results. If a query were to return more than the max > number of rows, the client would be given a handle (essentially a cursor > name) that it could use to fetch additional sets of rows. How about simply erroring out if the query returns more than X rows? > 4) Protocol level support of PREPARE. In jdbc and most other > interfaces, there is support for parameterized SQL. If you want to take > advantage of the performance benefits of reusing parsed plans you have > to use the PREPARE SQL statement. This argument seems self-contradictory to me. There is no such benefit unless you're going to re-use the statement many times. Nor do I see how pushing PREPARE down to the protocol level will create any improvement in its performance. > So what I would like to see is the ability for the client to set a MAX > VALUE size parameter. The server would send up to this amount of data > for any column. If the value was longer than MAX VALUE, the server > would respond with a handle that the client could use to get the rest of > the value (in chunks of MAX VALUE) if it wanted to. I don't think I want to embed this in the protocol, either; especially not when we don't have even the beginnings of backend support for it. I think such a feature should be implemented and proven as callable functions first, and then we could think about pushing it down into the protocol. > 6) Better over the wire support for bytea. The current encoding of > binary data \000 results in a significant expansion in the size of data > transmitted. It would be nice if bytea data didn't result in 2 or 3 > times data expansion. AFAICS the only context where this could make sense is binary transmission of parameters for a previously-prepared statement. We do have all the pieces for that on the roadmap. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Numbering of the next release: 8.0 vs 7.4
I think the first thing we should do about that is to define what are the reasons for a major version change. The way this discussion is being taken will not take us anywhere... is just too much about personal opinions. Anyway, for most users a win32 port is not a big deal (after all, practically all of us are using pgsql in an unix-like system)... but a lot of Windows users that doesn`t try pgsql because mysql is just so easy to install on windows machines... and they`re much better in marketing too. Well, they have a company behind them. Felipe Schnack Analista de Sistemas [EMAIL PROTECTED] Cel.: (51)91287530 Linux Counter #281893 Faculdade Ritter dos Reis www.ritterdosreis.br Fone/Fax.: (51)32303328 -- Original Message --- From: Peter Eisentraut <[EMAIL PROTECTED]> To: Justin Clift <[EMAIL PROTECTED]> Sent: Wed, 12 Mar 2003 16:43:31 +0100 (CET) Subject: Re: [HACKERS] Numbering of the next release: 8.0 vs 7.4 > Justin Clift writes: > > > + If it looks like we'll have Win32 and/or PITR recovery in time for > > the next release, we call it PostgreSQL 8.0 > > To me, those sound fairly unspectacular as reasons for 8.0. > > -- > Peter Eisentraut [EMAIL PROTECTED] > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org --- End of Original Message --- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Case insensitivity, and option?
>
>
> --le 12/03/2003 09:03 -0500, mlw écrivait :
> | I was at a client office reviewing some code. They use MSSQL and I |
> noticed that:
> |
> | select * from table where field = 'blah';
> | gave the same results as:
> | select * from table where field = 'BLah';
> |
> | I was shocked. (a) because I know a lot of my code could be easier to
> | write, and (b) that their code would break on every other database I
> am | aware of. Does anyone know about this?
> |
> | Is it practical/desirable for PostgreSQL to have this as a
> configuration | setting?
>
> Well, I quite don't see any difference with writing :
>select * from table where lower(field) = lower('BLah');
That would probably require an extra index, especially if 'field' is a
primary key.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Case insensitivity, and option?
On Wed, 2003-03-12 at 12:57, [EMAIL PROTECTED] wrote:
> >
> >
> > --le 12/03/2003 09:03 -0500, mlw écrivait :
> > | I was at a client office reviewing some code. They use MSSQL and I |
> > noticed that:
> > |
> > | select * from table where field = 'blah';
> > | gave the same results as:
> > | select * from table where field = 'BLah';
> > |
> > | I was shocked. (a) because I know a lot of my code could be easier to
> > | write, and (b) that their code would break on every other database I
> > am | aware of. Does anyone know about this?
> > |
> > | Is it practical/desirable for PostgreSQL to have this as a
> > configuration | setting?
> >
> > Well, I quite don't see any difference with writing :
> >select * from table where lower(field) = lower('BLah');
>
> That would probably require an extra index, especially if 'field' is a
> primary key.
I don't know about MSSql, but on MySQL you also require a different
index for a case sensitive comparison. Problem is, they don't (didn't)
support functional indexes -- so you simply couldn't make one.
End up with: WHERE field = 'var' and strcasecmp(field, 'var') everywhere
--
Rod Taylor <[EMAIL PROTECTED]>
PGP Key: http://www.rbt.ca/rbtpub.asc
signature.asc
Description: This is a digitally signed message part
Re: [HACKERS] Case insensitivity, and option?
I know that the MSSQL code works because the default collation sequence
for character fields is case-insensitive. You can change it for each
field independantly to be case sensitive, local specific, etc. I'm not
sure if PG supports a collation sequence attribute on column
definitions/indexes.
Rod Taylor wrote:
On Wed, 2003-03-12 at 12:57, [EMAIL PROTECTED] wrote:
--le 12/03/2003 09:03 -0500, mlw écrivait :
| I was at a client office reviewing some code. They use MSSQL and I |
noticed that:
|
| select * from table where field = 'blah';
| gave the same results as:
| select * from table where field = 'BLah';
|
| I was shocked. (a) because I know a lot of my code could be easier to
| write, and (b) that their code would break on every other database I
am | aware of. Does anyone know about this?
|
| Is it practical/desirable for PostgreSQL to have this as a
configuration | setting?
Well, I quite don't see any difference with writing :
select * from table where lower(field) = lower('BLah');
That would probably require an extra index, especially if 'field' is a
primary key.
I don't know about MSSql, but on MySQL you also require a different
index for a case sensitive comparison. Problem is, they don't (didn't)
support functional indexes -- so you simply couldn't make one.
End up with: WHERE field = 'var' and strcasecmp(field, 'var') everywhere
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Roadmap for FE/BE protocol redesign
Peter Eisentraut writes: > Dave Page writes: > > > Well what I *really* need has been made quite clear in other posts, but, > > when I say resultset in the same sentence as pgAdmin, I'm referring to > > the ability to enter an arbitrary SQL query, have the results displayed > > in a grid, which can then be editted. To do this pgAdmin needs to be > > able to figure out enough info about the source of the data to generate > > the required insert/update/delete statements. > > Right. But since you can't really write a literal SQL statement that does > an update that refers to a previous query, you are already doing a fair > amount of internal magic anyway, so if the meta-data is determined by > magic as well, that seems consistent. While this may be true, it is possible to build a client side system that can do this for you. Views and cursors are great, but they are not always the best tool for the job. > > What you need is an updateable cursor on the server side. It has all the > facilities you need, including standardized ways to find out the > updatability metadata. Please concentrate on that and do not attempt to > clutter the wire protocol with data that will not withstand a throrough > investigation of semantics. It's not foolproof and may even be foolhardy, but there are certain advantages to client-side decision making. A couple of integers or so for each attribute is not a terribly high price to pay. If a compelling case can be made that it can be put to good use, why not do it? Merlin ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Case insensitivity, and option?
On Wed, 2003-03-12 at 13:35, Dwayne Miller wrote:
> I know that the MSSQL code works because the default collation sequence
> for character fields is case-insensitive. You can change it for each
> field independantly to be case sensitive, local specific, etc. I'm not
> sure if PG supports a collation sequence attribute on column
> definitions/indexes.
Seems to, but it's on a database level -- not per column / index.
In other-words, you could potentially make the entire database case
insensitive.
I've not tried this myself, but there are people on the list who could
answer this definitively.
> Rod Taylor wrote:
>
> >On Wed, 2003-03-12 at 12:57, [EMAIL PROTECTED] wrote:
> >
> >
> >>>--le 12/03/2003 09:03 -0500, mlw écrivait :
> >>>| I was at a client office reviewing some code. They use MSSQL and I |
> >>>noticed that:
> >>>|
> >>>| select * from table where field = 'blah';
> >>>| gave the same results as:
> >>>| select * from table where field = 'BLah';
> >>>|
> >>>| I was shocked. (a) because I know a lot of my code could be easier to
> >>>| write, and (b) that their code would break on every other database I
> >>>am | aware of. Does anyone know about this?
> >>>|
> >>>| Is it practical/desirable for PostgreSQL to have this as a
> >>>configuration | setting?
> >>>
> >>>Well, I quite don't see any difference with writing :
> >>>select * from table where lower(field) = lower('BLah');
> >>>
> >>>
> >>That would probably require an extra index, especially if 'field' is a
> >>primary key.
> >>
> >>
> >
> >I don't know about MSSql, but on MySQL you also require a different
> >index for a case sensitive comparison. Problem is, they don't (didn't)
> >support functional indexes -- so you simply couldn't make one.
> >
> >End up with: WHERE field = 'var' and strcasecmp(field, 'var') everywhere
> >
> >
> >
>
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Rod Taylor <[EMAIL PROTECTED]>
PGP Key: http://www.rbt.ca/rbtpub.asc
signature.asc
Description: This is a digitally signed message part
Re: [HACKERS] SQL99 ARRAY support proposal
Tom Lane wrote:
But I think I like better the notion of extending my bound-together-
ANYARRAY-and-ANYELEMENT proposal,
http://archives.postgresql.org/pgsql-hackers/2003-03/msg00319.php
Suppose that we do that, and then further say that ANYARRAY or
ANYELEMENT appearing as the return type implies that the return type
is actually the common element or array type. Then we have such
useful behaviors as:
array_push(anyarray, anyelement) returns anyarray
array_pop(anyarray) returns anyelement
array_subscript(anyarray, int) yields anyelement
singleton_array(anyelement) yields anyarray
Before I get too far along, I'd like to get some feedback. The attached
patch implements Tom's bound-together-ANYARRAY-and-ANYELEMENT proposal
(and includes ANY as well, per earlier discussion). With it, the
following works:
CREATE OR REPLACE FUNCTION array_push (anyarray, anyelement)
RETURNS anyarray
AS '$libdir/plr','array_push'
LANGUAGE 'C';
regression=# select f1[2] from (select array_push('{1,2}'::integer[],3)
as f1) as t;
f1
2
(1 row)
Does it make sense to commit this now, or should it wait for the other
changes described below?
The following are my proposed next phases in array support changes.
Please let me know now if you think any of these won't fly (conceptually):
1) Implement SQL99/200x ARRAY[] changes proposed here:
http://archives.postgresql.org/pgsql-hackers/2003-03/msg00297.php
as modified by
http://archives.postgresql.org/pgsql-hackers/2003-03/msg00315.php
2) Implement the following new builtin functions
array_push(anyarray, anyelement) returns anyarray
array_pop(anyarray) returns anyelement
array_subscript(anyarray, int) yields anyelement
singleton_array(anyelement) returns anyarray
- any reason not to call this one simply "array"?
split(text, text) returns text[]
- split string into array on delimiter
implode(text[], text) returns text
- join array elements into a string using given string delimiter
3) Modify contrib/array functions as needed and move to the backend. Or
possibly write equivalent functions from scratch -- I just noticed
this in contrib/array:
* This software is distributed under the GNU General Public License
* either version 2, or (at your option) any later version.
Is anyone still in contact with Massimo Dal Zotto? Any chance he
would change the license to BSD?
4) Update "User's Guide"->"Data Types"->"Arrays" documentation and
create a new section: "User's Guide"->
"Functions and Operators"->
"Array Functions and Operators"
Thoughts, comments, objections all welcomed.
Thanks,
Joe
Index: src/backend/parser/parse_coerce.c
===
RCS file: /opt/src/cvs/pgsql-server/src/backend/parser/parse_coerce.c,v
retrieving revision 2.93
diff -c -r2.93 parse_coerce.c
*** src/backend/parser/parse_coerce.c 9 Feb 2003 06:56:28 - 2.93
--- src/backend/parser/parse_coerce.c 12 Mar 2003 16:17:39 -
***
*** 188,194
ReleaseSysCache(targetType);
}
! else if (targetTypeId == ANYOID ||
targetTypeId == ANYARRAYOID)
{
/* assume can_coerce_type verified that implicit coercion is okay */
--- 188,194
ReleaseSysCache(targetType);
}
! else if (targetTypeId == ANYOID || targetTypeId == ANYELEMENTOID ||
targetTypeId == ANYARRAYOID)
{
/* assume can_coerce_type verified that implicit coercion is okay */
***
*** 325,332
continue;
}
! /* accept if target is ANY */
! if (targetTypeId == ANYOID)
continue;
/*
--- 325,332
continue;
}
! /* accept if target is ANY or ANYELEMENT */
! if (targetTypeId == ANYOID || targetTypeId == ANYELEMENTOID)
continue;
/*
Index: src/backend/parser/parse_func.c
===
RCS file: /opt/src/cvs/pgsql-server/src/backend/parser/parse_func.c,v
retrieving revision 1.144
diff -c -r1.144 parse_func.c
*** src/backend/parser/parse_func.c 9 Feb 2003 06:56:28 - 1.144
--- src/backend/parser/parse_func.c 12 Mar 2003 17:46:44 -
***
*** 41,46
--- 41,50
List *fargs,
Oid *input_typeids,
Oid *function_typeids);
+ static Oid enforce_generic_type_consistency(Oid *oid_array,
+
Oid *true_oid_array,
+
Re: [HACKERS] SQL99 ARRAY support proposal
Joe Conway <[EMAIL PROTECTED]> writes: > + * If ANY, ANYARRAY, or ANYELEMENT is used for a function's arguments or > + * return type, make sure the runtime types are consistent with > + * each other. The argument consistency rules are like so: > + * > + * 1) All arguments declared ANY should have matching datatypes. > + * 2) All arguments declared ANYARRAY should have matching datatypes. > + * 3) All arguments declared ANYELEMENT should have matching datatypes. > + * 4) If there are arguments of both ANYELEMENT and ANYARRAY, make sure > + *the runtime scalar argument type is in fact the element type for > + *the runtime array argument type. Hmm. I don't see why we should drag ANY into this --- it should just be a no-constraints placeholder, same as before. What's the gain from constraining it that you don't get from ANYELEMENT? > + * 5) If return type is ANYARRAY, no argument is ANYARRAY or ANYELEMENT, > + *leave the return type as is. > + *XXX should this case be rejected at the point of function creation? Probably. This case could be handled just as well by declaring the output to be ANY, I'd think. > + * 8) If return type is ANYELEMENT, no argument is ANYARRAY or ANYELEMENT > + *leave the return type as is. > + *XXX should this case be rejected at the point of function creation? Likewise. The point of (this reinterpretation of) ANYARRAY and ANYELEMENT is to let the parser deduce the actual output type. If it's not going to be able to deduce anything, use ANY instead. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] SQL99 ARRAY support proposal
Tom Lane wrote:
Joe Conway <[EMAIL PROTECTED]> writes:
+ * If ANY, ANYARRAY, or ANYELEMENT is used for a function's arguments or
+ * return type, make sure the runtime types are consistent with
+ * each other. The argument consistency rules are like so:
Hmm. I don't see why we should drag ANY into this --- it should just be
a no-constraints placeholder, same as before. What's the gain from
constraining it that you don't get from ANYELEMENT?
I was thinking of the case
create function foo("any") returns "any"
but I guess you're right, it can just as easily be
create function foo(anyelement) returns anyelement
I'll pull the ANY stuff out.
+ * 5) If return type is ANYARRAY, no argument is ANYARRAY or ANYELEMENT,
+ *leave the return type as is.
+ *XXX should this case be rejected at the point of function creation?
Probably. This case could be handled just as well by declaring the
output to be ANY, I'd think.
+ * 8) If return type is ANYELEMENT, no argument is ANYARRAY or ANYELEMENT
+ *leave the return type as is.
+ *XXX should this case be rejected at the point of function creation?
Likewise. The point of (this reinterpretation of) ANYARRAY and
ANYELEMENT is to let the parser deduce the actual output type.
If it's not going to be able to deduce anything, use ANY instead.
OK -- I'll take care of that too.
Thanks,
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] regproc's lack of certainty is dangerous
Deepak Bhole of Red Hat just pointed out to me a failure that he got
after some 7.3 stress testing:
> [ "[EMAIL PROTECTED]&*()''| \final_db\n,.;:'" ]=# SELECT n.nspname, p.proname,
> o.oprcode::oid FROM pg_operator o, pg_proc p, pg_namespace n WHERE
> o.oid=270447::oid AND p.oid=o.oprcode::oid AND p.pronamespace=n.oid;
> ERROR: There is more than one procedure named "[ ""[EMAIL PROTECTED]&*()''|
> \final_schema\n,.;:'"" ]"."[ ""[EMAIL PROTECTED]&*''| \ {func_for_op}\n,.;:'"" ]"
This error comes out of regprocin() when it finds multiple candidate
functions with the same name (and, presumably, different argument lists
or different schemas). No big surprise, since that's what he had.
But it's a bit odd that regprocin() is being invoked, when there's no
regproc literal in the given query.
After some digging, it turns out that the error is appearing because
that function name is present in the pg_statistic entry for
pg_operator.oprcode. *Any* query that causes the optimizer to become
interested in pg_operator.oprcode will fail under these circumstances
:-(. And the user can't readily avoid this, since there's no way to
be sure which function names will happen to end up in the histogram.
"Never ANALYZE the table" isn't going to fly as a workaround.
I am not real sure what we should do about it. Clearly there is more
risk than I'd realized in datatypes whose input routines may reject
strings that their output routines had produced in good faith.
One possible route is to try to eliminate the ambiguity, but I doubt
that that will work very effectively for regproc and friends --- the
whole point of those types is to resolve ambiguous input, and so the
possibility of failures in the input routine can't easily be removed.
regproc in particular needs its special behavior to be useful for
bootstrapping.
Another approach is to try to fix pg_statistic to avoid the problem by
not doing I/O conversions. For scalar datatypes (those that have
associated array types) it'd be probably be feasible to store the
histogram and most-common-value arrays as arrays of the datatype itself,
not arrays of text; that should be a win for performance as well as
avoiding risky conversions. I am not sure what to do about columns that
have datatypes without matching array types, though (that would include
array columns and domains, IIRC). Maybe use array of bytea to hold the
internal representation of the type?
Any comments or better ideas out there?
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] regproc's lack of certainty is dangerous
Tom Lane wrote: Another approach is to try to fix pg_statistic to avoid the problem by not doing I/O conversions. For scalar datatypes (those that have associated array types) it'd be probably be feasible to store the histogram and most-common-value arrays as arrays of the datatype itself, not arrays of text; that should be a win for performance as well as avoiding risky conversions. I am not sure what to do about columns that have datatypes without matching array types, though (that would include array columns and domains, IIRC). Maybe use array of bytea to hold the internal representation of the type? ISTM that the best (if not the only feasible) approach is using array of bytea to hold the internal representation of the type. Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] regproc's lack of certainty is dangerous
Joe Conway <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> I am not sure what to do about columns that >> have datatypes without matching array types, though (that would include >> array columns and domains, IIRC). Maybe use array of bytea to hold the >> internal representation of the type? > ISTM that the best (if not the only feasible) approach is using array of > bytea to hold the internal representation of the type. I'd like "select * from pg_statistic" to still produce readable output whenever possible, though. The bytea approach falls down badly on that score, so I don't want to resort to it except where I absolutely must. I think that we can actually get away (from an implementation point of view) with a column containing arrays of different base types; array_out will still work AFAIR. It's an interesting question though how such a column could reasonably be declared. This ties into your recent investigations into polymorphic array functions, perhaps. Maybe "anyarray" shouldn't be quite so pseudo a pseudotype? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [INTERFACES] Roadmap for FE/BE protocol redesign
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > Personally ... as long as a v8.x client can talk to a v7.x backend, you > have my vote ... I'm more apt to upgrade my clients before my servers > anyway ... Surely that's not true for a production environment. You have one database but potentially dozens of various programs around that access it. The main application, some backend scripts for batch jobs, your backup process, your monitoring systems... Not all of these are necessarily on the same machine. It's upgrading the database that's likely to be the driving motivation for new sql or storage features. People usually don't get excited about upgrading the client libraries :) -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] SQL99 ARRAY support proposal
Joe Conway writes: > 2) Implement the following new builtin functions > array_push(anyarray, anyelement) returns anyarray > array_pop(anyarray) returns anyelement > array_subscript(anyarray, int) yields anyelement > singleton_array(anyelement) returns anyarray > - any reason not to call this one simply "array"? > split(text, text) returns text[] > - split string into array on delimiter > implode(text[], text) returns text > - join array elements into a string using given string delimiter I think this goes too far. It is just an invitation to people to create bad database designs by using arrays as lists. Create an array support package on gborg if you like, but I feel this should not be in the mainline. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] regproc's lack of certainty is dangerous
Tom Lane wrote: I think that we can actually get away (from an implementation point of view) with a column containing arrays of different base types; array_out will still work AFAIR. It's an interesting question though how such a column could reasonably be declared. This ties into your recent investigations into polymorphic array functions, perhaps. Maybe "anyarray" shouldn't be quite so pseudo a pseudotype? I was having similar thoughts when you first posted this, but I wasn't sure you'd want to go there. I wonder what changes are required other than promoting the typtype from a 'p' to a 'b' and the I/O functions to array_out/array_in? Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Roadmap for FE/BE protocol redesign
> > One addition I would personally like to see (it comes up in my apps > > code) is the ability to detect wheather the server is big endian or > > little endian. When using binary cursors this is necessary in order to > > read int data. > > Actually, my hope is to eliminate that business entirely by > standardizing the on-the-wire representation for binary data; note the > reference to send/receive routines in the original message. For integer > data this is simple enough: network byte order will be it. I'm not sure > yet what to do about float data. Is that something that the 'XDR' spec deals with? (Thinking back to 3rd year networking unit)... Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] regproc's lack of certainty is dangerous
Tom Lane wrote:
I think that we can actually get away (from an implementation point of
view) with a column containing arrays of different base types; array_out
will still work AFAIR. It's an interesting question though how such a
column could reasonably be declared. This ties into your recent
investigations into polymorphic array functions, perhaps.
Maybe "anyarray" shouldn't be quite so pseudo a pseudotype?
More on this idea; here is a simple experiment:
regression=# update pg_type set typtype = 'b', typinput = 'array_in',
typoutput = 'array_out' where oid = 2277;
UPDATE 1
regression=# create table bar(f1 int, f2 anyarray);
CREATE TABLE
regression=# insert into bar values (1,'{1,2}'::integer[]);
INSERT 744428 1
regression=# insert into bar values (2,'{a,b}'::text[]);
INSERT 744429 1
regression=# select * from bar;
f1 | f2
+---
1 | {1,2}
2 | {a,b}
(2 rows)
Interesting ... but then there is:
regression=# select f1, f2[2] from bar;
ERROR: transformArraySubscripts: type anyarray is not an array
A bit more to do I guess.
Joe
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Case insensitivity, and option?
mlw wrote: > ... >select * from table where field = 'blah'; >gave the same results as: >select * from table where field = 'BLah'; > >I was shocked. (a) because I know a lot of my code could be easier to >write > ... select * from table where field ILIKE 'blAH'; -- ;-) is almost as easy :-) PS: no, don't do this if you want portability. I think the charset idea's a better one. Ron ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] some more docbook help
Hi Gang I can sense that I'm _really_ close to getting docbook working, but not quite there yet. I'm hoping somebody can tell me where I'm screwing up ! I'm on a Red Hat Linux 7.3 system. Although I do have openjade installed I was finding it difficult to figure out where to point stylesheets to postgres, so instead I decided to build openjade (and so OpenSP) from source and configure it _exactly_ as described in doc-toolsets.html and doc-build.html in the pgsql-7.3 docs. Although a motley mix of sgml stuff was initially installed in /usr/share/sgml, I created a /usr/local/share/sgml directory and pointed it to openjade while building openjade as follows: $ ./configure --enable-default-catalog=/usr/local/share/sgml/catalog Subsequently I followed all instructions in doc-toolsets.html for the other packages. I set DOCBOOKSTYLE to /usr/local/share/sgml before running configure for postgres .. I saw an old usenet message (exchange with Bruce Momjian) which suggests that the SGML_CATALOG_FILES env. variable must be set to: > export SGML_CATALOG_FILES=/somewhere/docbook31/docbook.cat I set DOCBOOKINDEX to be /usr/bin/collateindex.pl Now finally I go to the doc/src/sgml directory and try: gmake admin.html > new.out 2> new.err This however results in a make error. The output file is: /usr/bin/perl /usr/bin/collateindex.pl -f -g -t 'Index' -i 'bookindex' -o bookindex.sgml HTML.index /usr/bin/perl /usr/bin/collateindex.pl -f -g -t 'Index' -i 'setindex' -x -o setindex.sgml HTML.index openjade -D . -D ./ref -c /usr/local/share/sgml/catalog -d stylesheet.dsl -i output-html -t sgml book-decl.sgml admin.sgml The error file however starts off with: Processing HTML.index... 0 entries loaded... 0 entries ignored... Done. Processing HTML.index... 0 entries loaded... 0 entries ignored... Done. openjade:/usr/local/share/sgml/docbook31/docbook.cat:63:79:E: end of entity in comment openjade:/usr/local/share/sgml/docbook31/docbook.cat:62:20:E: cannot find "data"; tried "/usr/local/share/sgml/docbook31/data", "./data", "./ref/data" openjade:/usr/local/share/sgml/docbook31/docbook.cat:63:79:E: end of entity in comment openjade:/usr/local/share/sgml/docbook31/docbook.cat:62:20:E: cannot find "data"; tried "/usr/local/share/sgml/docbook31/data", "./data", "./ref/data" openjade:book-decl.sgml:1:55:W: cannot generate system identifier for public text "-//OASIS//DTD DocBook V3.1//EN" openjade:book-decl.sgml:11:0:E: reference to entity "BOOK" for which no system identifier could be generated openjade:book-decl.sgml:1:0: entity was defined here openjade:book-decl.sgml:11:0:E: DTD did not contain element declaration for document type name openjade:admin.sgml:5:9:E: there is no attribute "ID" openjade:admin.sgml:5:16:E: element "BOOK" undefined openjade:admin.sgml:9:7:E: element "TITLE" undefined openjade:/usr/local/share/sgml/docbook31/docbook.cat:62:20:E: cannot find "data"; tried "/usr/local/share/sgml/docbook31/data", "./data", "./ref/data" openjade:/usr/local/share/sgml/docbook-dsssl-1.77/html/dblink.dsl:203:1:E: XRef LinkEnd to missing ID 'STON86' openjade:/usr/local/share/sgml/docbook-dsssl-1.77/html/dblink.dsl:203:1:E: XRef LinkEnd to missing ID 'ROWE87' openjade:/usr/local/share/sgml/docbook-dsssl-1.77/html/dblink.dsl:203:1:E: XRef LinkEnd to missing ID 'STON87a' openjade:/usr/local/share/sgml/docbook-dsssl-1.77/html/dblink.dsl:203:1:E: XRef LinkEnd to missing ID 'STON87b' openjade:/usr/local/share/sgml/docbook-dsssl-1.77/html/dblink.dsl:203:1:E: XRef LinkEnd to missing ID 'STON90a' openjade:/usr/local/share/sgml/docbook-dsssl-1.77/html/dblink.dsl:203:1:E: XRef LinkEnd to missing ID 'STON89' openjade:/usr/local/share/sgml/docbook-dsssl-1.77/html/dblink.dsl:203:1:E: XRef LinkEnd to missing ID 'STON90b' gmake: *** [admin.html] Error 1 Does anybody have any suggestions ? Am I doing something horribly wrong ? Many thanks ! -- Sailesh http://www.cs.berkeley.edu/~sailesh ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] bug in setval?
When I create a new table with a serial column, the first row defaults to inserting '1'. If I delete all the rows from the table and want to reset the sequence, I can't: ERROR: users_health_types_type_id_seq.setval: value 0 is out of bounds (1,9223372036854775807) How do I set the sequence to have next value = 1? Surely the bounds should begin at zero? Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] bug in setval?
Christopher Kings-Lynne wrote:
How do I set the sequence to have next value = 1? Surely the bounds should
begin at zero?
No; see:
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/functions-sequence.html
Notice the is_called flag. I think this does what you want:
SELECT setval('foo', 1, false);
Joe
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] SQL99 ARRAY support proposal
Peter Eisentraut wrote: > Joe Conway writes: > >>2) Implement the following new builtin functions >> array_push(anyarray, anyelement) returns anyarray >> array_pop(anyarray) returns anyelement >> array_subscript(anyarray, int) yields anyelement >> singleton_array(anyelement) returns anyarray >> - any reason not to call this one simply "array"? >> split(text, text) returns text[] >> - split string into array on delimiter >> implode(text[], text) returns text >> - join array elements into a string using given string delimiter I think this goes too far. It is just an invitation to people to create bad database designs by using arrays as lists. Create an array support package on gborg if you like, but I feel this should not be in the mainline. Sorry, I don't agree with that assessment. There are lots of ways people can create bad database designs using the CREATE TABLE statement too ;-) Arrays *do* have a place, and they are supported in SQL99+. We get complaints almost daily regarding the poor array support -- this is just a step toward improving that. If you want to debate the merit of individual functions on that list, let's do that, but to dismiss them all with a wave-of-the-hand is too far. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] some more docbook help
Phew .. after some more struggling with docbook I think I finally managed to get it working. Posting here to help other folks googling through usenet archives. My problem was that I had DOCBOOKSTYLE set to /usr/local/share/sgml - the directory which contained the "catalog" file. However, inspite of this environment variable, configure insisted on setting DOCBOOKSTYLE to /usr/local/share/sgml/docbook31 I finally fixed it by creating a file /usr/local/share/sgml/docbook31/catalog as: CATALOG "../dsssl/catalog" CATALOG "docbook.cat" CATALOG "../docbook-dsssl-1.77/catalog" This works fine thankfully. Now I have another question - and this is probably incredibly stupid. I want to generate the PostgreSQL documentation with some extra add-ons (DDL and DML we have added for streaming data and continuous long-running queries). I would like the add-ons to show in some changed fashion - say perhaps in bright red to show that this is new, not-yet-very-well-tested (:-) functionality specific to our system (TelegraphCQ) and not part of the base PostgreSQL features. How would I go about doing something like this in SGML ? I understand the whole process of SGML documentation as separating content from presentation. So I imagine I should surround our new stuff with some kind of tag, that when used with an HTML (or other) stylesheet generates appropriately changed documentation. I hope I am on the right track ! Could somebody help me please ? Many thanks ! -- Sailesh http://www.cs.berkeley.edu/~sailesh ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [INTERFACES] Roadmap for FE/BE protocol redesign
Greg Stark <[EMAIL PROTECTED]> writes: > It's upgrading the database that's likely to be the driving motivation > for new sql or storage features. People usually don't get excited > about upgrading the client libraries :) Usually not. This cycle might be different though, if we are able to finish the proposed improvements in error reporting and other issues that are handicapping clients. None of that work will help un-upgraded clients... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SQL99 ARRAY support proposal
Joe Conway <[EMAIL PROTECTED]> writes: > Peter Eisentraut wrote: >> Create an array support package on gborg if you like, but I feel this >> should not be in the mainline. > Arrays *do* have a place, and they are supported in SQL99+. FWIW, I'm with Joe on this one. Arrays have their uses; and it's not like there are no misusable constructs in SQL ... regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [INTERFACES] Roadmap for FE/BE protocol redesign
Greg Stark kirjutas K, 12.03.2003 kell 07:10: > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > > > Personally ... as long as a v8.x client can talk to a v7.x backend, you > > have my vote ... I'm more apt to upgrade my clients before my servers > > anyway ... > > Surely that's not true for a production environment. You have one database but > potentially dozens of various programs around that access it. The main > application, some backend scripts for batch jobs, your backup process, your > monitoring systems... Not all of these are necessarily on the same machine. For more radical protocol changes a viable approach could be "protocol proxies", i.e. set up a _separate_ daemon which listens on a separate port and translates v7.x wire protocol to v8.x of the database proper. Then those needing it can keep it around and those who need it not don't get the overhead. It could also be maintained by inerested parties long after being dropped by core developers. > It's upgrading the database that's likely to be the driving motivation for new > sql or storage features. People usually don't get excited about upgrading the > client libraries :) But our SQL itself is slowly drifting towards ANSI/ISO compliance and that has often brought subtle changes that break _applications_. It is not a big issue to changes libraries if you have to change the application anyway. - Hannu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Roadmap for FE/BE protocol redesign
Tom Lane kirjutas K, 12.03.2003 kell 18:19: > Barry Lind <[EMAIL PROTECTED]> writes: > > One addition I would personally like to see (it comes up in my apps > > code) is the ability to detect wheather the server is big endian or > > little endian. When using binary cursors this is necessary in order to > > read int data. > > Actually, my hope is to eliminate that business entirely by > standardizing the on-the-wire representation for binary data; note the > reference to send/receive routines in the original message. For integer > data this is simple enough: network byte order will be it. I'm not sure > yet what to do about float data. Use IEEE floats or just report the representation in startup packet. the X11 protocol does this for all data, even integers - the client expresses a wish what it wants and the server tells it what it gets (so two intel boxes need not to convert to "network byte order" at both ends). > > 2) Better support for domains. Currently the jdbc driver is broken with > > regards to domains (although no one has reported this yet). The driver > > will treat a datatype that is a domain as an unknown/unsupported > > datatype. It would be great if the T response included the 'base' > > datatype for a domain attribute so that the driver would know what > > parsing routines to call to convert to/from the text representation the > > backend expects. > > I'm unconvinced that we need do this in the protocol, as opposed to > letting the client figure it out with metadata inquiries. If we should, > I'd be inclined to just replace the typeid field with the base typeid, > and not mention the domain to the frontend at all. Comments? > > > So I would request the ability of the client to set a max rows parameter > >for query results. If a query were to return more than the max > > number of rows, the client would be given a handle (essentially a cursor > > name) that it could use to fetch additional sets of rows. > > How about simply erroring out if the query returns more than X rows? Or just using prepare/execute - fetch - fetch - fetch ... > > 4) Protocol level support of PREPARE. In jdbc and most other > > interfaces, there is support for parameterized SQL. If you want to take > > advantage of the performance benefits of reusing parsed plans you have > > to use the PREPARE SQL statement. > > This argument seems self-contradictory to me. There is no such benefit > unless you're going to re-use the statement many times. Nor do I see > how pushing PREPARE down to the protocol level will create any > improvement in its performance. I suspect that he actually means support for binary transmission of parameters for a previously-prepared statement here. > > So what I would like to see is the ability for the client to set a MAX > > VALUE size parameter. The server would send up to this amount of data > > for any column. If the value was longer than MAX VALUE, the server > > would respond with a handle that the client could use to get the rest of > > the value (in chunks of MAX VALUE) if it wanted to. > > I don't think I want to embed this in the protocol, either; especially > not when we don't have even the beginnings of backend support for it. > I think such a feature should be implemented and proven as callable > functions first, and then we could think about pushing it down into the > protocol. IIRC, Oracle has such a feature in its support for Large Objects (LONG datatype). If the object data is longer than xxx bytes you will need special ized access to it. also when stepping with single fetches, you will always get handles for LONG objects, if fetching more than one row you'll get raw data. BTW, I'm not advocating such behaviour . Hannu ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Roadmap for FE/BE protocol redesign
> > One addition I would personally like to see (it comes up in my > > apps code) is the ability to detect wheather the server is big > > endian or little endian. When using binary cursors this is > > necessary in order to read int data. > > Actually, my hope is to eliminate that business entirely by > standardizing the on-the-wire representation for binary data; note > the reference to send/receive routines in the original message. For > integer data this is simple enough: network byte order will be it. > I'm not sure yet what to do about float data. When were talking sending data across the wire, are we talking about a format that would let the server use sendfile() for sending the data to the client? Having a database that can send data to the client efficiently would be a nice change of pace given most databases since RDBMSs are notoriously slow (slower than NFS) at sending files to clients. -sc -- Sean Chittenden pgp0.pgp Description: PGP signature
Re: [HACKERS] Roadmap for FE/BE protocol redesign
Hannu Krosing wrote: Tom Lane kirjutas K, 12.03.2003 kell 18:19: Actually, my hope is to eliminate that business entirely by standardizing the on-the-wire representation for binary data; note the reference to send/receive routines in the original message. For integer data this is simple enough: network byte order will be it. I'm not sure yet what to do about float data. Use IEEE floats or just report the representation in startup packet. the X11 protocol does this for all data, even integers - the client expresses a wish what it wants and the server tells it what it gets (so two intel boxes need not to convert to "network byte order" at both ends). IIOP/CDR behaves similarly for performance reasons- "receiver makes it right". It also defines a representation for all of the CORBA idl basic types, wide characters, fixed-point types, structures, etc. A far-reaching, wild suggestion would be to replace the postmaster with a CORBA-based server process with a well defined interface. At a minimum, if a binary protocol is the ultimate destination, perhaps some of the mapping of various types could be borrowed from the specs. Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Roadmap for FE/BE protocol redesign
Peter Eisentraut wrote: (B> (B> Dave Page writes: (B> (B> > Well what I *really* need has been made quite clear in other (B> > posts, but, when I say resultset in the same sentence as (B> > pgAdmin, I'm referring to the ability to enter an arbitrary (B> > SQL query, have the results displayed in a grid, which can (B> > then be editted. To do this pgAdmin needs to be able to (B> > figure out enough info about the source of the data to generate (B> > the required insert/update/delete statements. (B> (B> Right. But since you can't really write a literal SQL statement (B> that does an update that refers to a previous query, you are (B> already doing a fair amount of internal magic anyway, so if the (B> meta-data is determined by magic as well, that seems consistent. (B (BPsqlodbc driver has to parse the queries in order to (Bimplement driver side updatable cursors unwillingly. (BI'm very suspicios if it should be the driver's job (Bbecause it's very hard and ineffective to parse and (Banalyze the queries in the same way as the backend does. (B (B> What you need is an updateable cursor on the server side. (B> It has all the facilities you need, (B (BReally ? How did you confirm it ? (B (B> including standardized ways to find out the (B> updatability metadata. Please concentrate on that and do not attempt to (B> clutter the wire protocol with data that will not withstand a throrough (B> investigation of semantics. (B (Bregards, (BHiroshi Inoue (Bhttp://www.geocities.jp/inocchichichi/psqlodbc/ (B (B---(end of broadcast)--- (BTIP 5: Have you checked our extensive FAQ? (B (Bhttp://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Roadmap for FE/BE protocol redesign
> > I suggested using names to Tom for this reason, but he preferred to use > > attrelid/attnum. > > Oh, and what happenned to the attlognum idea? If something that needs > it is going to be implemented the column should probably be added now > and used instead of attnum. Wll, it'd be nice, but I can see ways of doing ALTER COLUMN/SET TYPE without it, so... (My work on that has been stalled for a while BTW, due to sheer difficulty!) Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Roadmap for FE/BE protocol redesign
On Wed, 2003-03-12 at 20:45, Hiroshi Inoue wrote: > Peter Eisentraut wrote: > > > > Dave Page writes: > > > > > Well what I *really* need has been made quite clear in other > > > posts, but, when I say resultset in the same sentence as > > > pgAdmin, I'm referring to the ability to enter an arbitrary > > > SQL query, have the results displayed in a grid, which can > > > then be editted. To do this pgAdmin needs to be able to > > > figure out enough info about the source of the data to generate > > > the required insert/update/delete statements. > > > > Right. But since you can't really write a literal SQL statement > > that does an update that refers to a previous query, you are > > already doing a fair amount of internal magic anyway, so if the > > meta-data is determined by magic as well, that seems consistent. > > Psqlodbc driver has to parse the queries in order to > implement driver side updatable cursors unwillingly. > I'm very suspicios if it should be the driver's job > because it's very hard and ineffective to parse and > analyze the queries in the same way as the backend does. jdbc has to do this too, and the backend is in a much better position to do the parsing IMO as well. > > > What you need is an updateable cursor on the server side. > > It has all the facilities you need, > > Really ? How did you confirm it ? > > > including standardized ways to find out the > > updatability metadata. Please concentrate on that and do not attempt to > > clutter the wire protocol with data that will not withstand a throrough > > investigation of semantics. > > regards, > Hiroshi Inoue > http://www.geocities.jp/inocchichichi/psqlodbc/ > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- Dave Cramer <[EMAIL PROTECTED]> Cramer Consulting ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
