Thread: something more about my question about performance
I wrote : >Hello, >i have one postgresql server (7.0) where several users access. >Is there any way to improve performance for: > >i) accesses of different users at different dbs >ii) accesses of different users at the same db > >Thanks. My question simple wanted know if it is possible, to improve performance, to make users access to different postgres servers, making access, for example, the user1 to the db1 on the server1, the user2 to db2 on the server2 and so on. If it possible, how can I do that? Thank you. Best. Enrico Mangano Netsoftware Division ______________ NETHOUSE S.R.L. Cso Re Umberto I, 57 - 10128 Torino - Italy Tel. +39-011-581.581 - Fax +39-011-581.591 http://www.nethouse.it - mailto:enrico.mangano@nethouse.it
It seems that when an INSERT causes a duplicate key exception for unique/primary keys, the JDBC library is throwing the SQL exception but there is no code that lets my program detect a duplicate key error, or which key caused the duplicate error. Is there a plan for the JDBC to return the codes for postgres back to the program? I running 7.1beta4 now. David
hi, there! On Thu, 1 Mar 2001, David Wall wrote: > It seems that when an INSERT causes a duplicate key exception for > unique/primary keys, the JDBC library is throwing the SQL exception but > there is no code that lets my program detect a duplicate key error, or which > key caused the duplicate error. > > Is there a plan for the JDBC to return the codes for postgres back to the > program? I running 7.1beta4 now. AFAIK clean implementation of such feature is not possible because backend protocol does not have error codes (only error messages) /fjoe
> Is there a plan for the JDBC to return the codes for postgres back to the program? I running 7.1beta4 now. > >> AFAIK clean implementation of such feature is not possible because backend >> protocol does not have error codes (only error messages) Too bad. Is the definitive list of error messages documented? (Probably yes, but where?) Perhaps we could write an ugly implementation that would parse the error message and return meaningful errors codes so that all users don't suffer the same problem. Thoughts? David
At 00:41 02/03/01 +0600, Max Khon wrote: >hi, there! > >On Thu, 1 Mar 2001, David Wall wrote: > > > It seems that when an INSERT causes a duplicate key exception for > > unique/primary keys, the JDBC library is throwing the SQL exception but > > there is no code that lets my program detect a duplicate key error, or > which > > key caused the duplicate error. > > > > Is there a plan for the JDBC to return the codes for postgres back to the > > program? I running 7.1beta4 now. > >AFAIK clean implementation of such feature is not possible because backend >protocol does not have error codes (only error messages) Thats correct. It's also not practical to get the driver to translate because the format of the messages can change between releases as well. Peter >/fjoe
At 11:06 01/03/01 -0800, David Wall wrote: > > Is there a plan for the JDBC to return the codes for postgres back to >the program? I running 7.1beta4 now. > > > >> AFAIK clean implementation of such feature is not possible because >backend > >> protocol does not have error codes (only error messages) > >Too bad. Is the definitive list of error messages documented? (Probably >yes, but where?) Perhaps we could write an ugly implementation that would >parse the error message and return meaningful errors codes so that all users >don't suffer the same problem. problem is two fold: 1: Enlarges the driver, which affects those who use the driver over a network connection (some people still use JDBC direct in applets) 2: It becomes sensitive to changes during development. An example of #2 is where the Timestamp format changed just slightly, then getTimestamp broke. Peter
> >AFAIK clean implementation of such feature is not possible because backend > >protocol does not have error codes (only error messages) > > Thats correct. It's also not practical to get the driver to translate > because the format of the messages can change between releases as well. Practically speaking, how often do the messages really change between releases? It seems that we could create a pluggable translator that would work based on the release version. If the messages change too frequently, then something's seriously wrong with Postgresql server since the main sql-portion of the system has been in place and there aren't too many things a program really needs to detect about a database (missing row, duplicate key, constraint violation, transaction error, deadlock detected, etc.). David
I have ideas on how to do error codes and multiple languages. I can make a proposal for 7.2. [ Charset ISO-8859-1 unsupported, converting... ] > > >AFAIK clean implementation of such feature is not possible because > backend > > >protocol does not have error codes (only error messages) > > > > Thats correct. It's also not practical to get the driver to translate > > because the format of the messages can change between releases as well. > > Practically speaking, how often do the messages really change between > releases? It seems that we could create a pluggable translator that would > work based on the release version. If the messages change too frequently, > then something's seriously wrong with Postgresql server since the main > sql-portion of the system has been in place and there aren't too many things > a program really needs to detect about a database (missing row, duplicate > key, constraint violation, transaction error, deadlock detected, etc.). > > David > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
As stated, the backend protocol doesn't pass error codes but error messages. My question is, are there enough of these kind of things to create a new protocol version (7.2)? After all, we are still using the same protocol since (?)6.5. I think adding error codes would not only benefit JDBC but ODBC and possibly others also. I know there have been various discussions like this one about parsing error messages in the ODBC driver. As I remember, there were one or two other things that have been discussed that would require a change in the protocol also. Just my two cents. -Cedar On Thu, 1 Mar 2001, Bruce Momjian wrote: > I have ideas on how to do error codes and multiple languages. I can > make a proposal for 7.2. > > [ Charset ISO-8859-1 unsupported, converting... ] > > > >AFAIK clean implementation of such feature is not possible because > > backend > > > >protocol does not have error codes (only error messages) > > > > > > Thats correct. It's also not practical to get the driver to translate > > > because the format of the messages can change between releases as well. > > > > Practically speaking, how often do the messages really change between > > releases? It seems that we could create a pluggable translator that would > > work based on the release version. If the messages change too frequently, > > then something's seriously wrong with Postgresql server since the main > > sql-portion of the system has been in place and there aren't too many things > > a program really needs to detect about a database (missing row, duplicate > > key, constraint violation, transaction error, deadlock detected, etc.). > > > > David > > > > > > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 >
Quoting Cedar Cox <cedarc@visionforisrael.com>: > > As stated, the backend protocol doesn't pass error codes but error > messages. My question is, are there enough of these kind of things to > create a new protocol version (7.2)? After all, we are still using the > same protocol since (?)6.5. I think adding error codes would not only > benefit JDBC but ODBC and possibly others also. I know there have been > various discussions like this one about parsing error messages in the > ODBC > driver. As I remember, there were one or two other things that have > been > discussed that would require a change in the protocol also. Just my > two > cents. I totally agree. Putting the error codes (an extra 2 or 4 bytes) in the protocol shouldn't be too much hardship, and does mean several benefits: 1: No parsing of error messages on the client side 2: Small clients (embeded systems) can ignore the textual side of the message 3: It would be optional not to send the textual message - just the error number. Again good for non-human clients 4: Codes would be language independent (human that is). Currently AFAIK only JDBC supports it's own error messages in non-english languages. Having the codes would mean the backend ones could also be in non-english format and the clients would still work fine. Virtually every interface would benefit from it. Peter > > -Cedar > > On Thu, 1 Mar 2001, Bruce Momjian wrote: > > > I have ideas on how to do error codes and multiple languages. I can > > make a proposal for 7.2. > > > > [ Charset ISO-8859-1 unsupported, converting... ] > > > > >AFAIK clean implementation of such feature is not possible > because > > > backend > > > > >protocol does not have error codes (only error messages) > > > > > > > > Thats correct. It's also not practical to get the driver to > translate > > > > because the format of the messages can change between releases as > well. > > > > > > Practically speaking, how often do the messages really change > between > > > releases? It seems that we could create a pluggable translator that > would > > > work based on the release version. If the messages change too > frequently, > > > then something's seriously wrong with Postgresql server since the > main > > > sql-portion of the system has been in place and there aren't too > many things > > > a program really needs to detect about a database (missing row, > duplicate > > > key, constraint violation, transaction error, deadlock detected, > etc.). > > > > > > David > > > > > > > > > > > > -- > > Bruce Momjian | http://candle.pha.pa.us > > pgman@candle.pha.pa.us | (610) 853-3000 > > + If your life is a hard drive, | 830 Blythe Avenue > > + Christ can be your backup. | Drexel Hill, Pennsylvania > 19026 > > > > -- Peter Mount peter@retep.org.uk PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
hi,<br /> i'm a newbie to PostgresQL, currently trying to use the ODBC driver. i'm connecting from my Win98
machineto a PostgresQL installation on a Linux box. i'm trying out the sample code from the MSDN concerning sending
largeobjects through ODBC but i cannot get the code to work. <br /><br /> What i did is create a test table
with(id int, obj lo) and then just try to insert some data into it. The strange things is that after the
executestatement,it does not even return a SQL_NEED_DATA even though i have binded the parameter to a
SQL_LONGVARBINARY.it just finished with the executino and over at the database side, an empty large object is created,
i'veattached the code and logs from the ODBC driver below. Hope someone can point out what am i doing wrong here.
Thanks!<br/><br /> Code<br /> -----------------------------<br /><font size="2">retcode = SQLPrepare ( hstmt, (unsigned
char*)"INSERTINTO a (id , obj) VALUES ( ? , ? ) ",SQL_NTS ); <br /> SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
SQL_C_ULONG,SQL_INTEGER, 0, 0, &sID, 0, &cbID);<br /> SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_LONGVARBINARY,0, 0, (SQLPOINTER)2, 0, &cbObj);<br /><br /> cbObj = SQL_LEN_DATA_AT_EXEC(0); <br
/>sID = 1;<br /> <br /> retcode = SQLExecute(hstmt); <br /> <br /> if (retcode
==SQL_NEED_DATA) {<br /> printf("Need Data\n");<br /> }<br /></font>-----------------------------<br /><br
/><fontsize="2">Log<br /></font>-----------------------------<br /><font size="2">conn=15272060, SQLDriverConnect(
in)='DSN=POSTGRESQL;',DriverCompletion=0<br /> DSN info:
DSN='POSTGRESQL',onlyread='0',protocol='6.4',showoid='1',fakeoidindex='0',showsystable='0'<br/>
conn_settings=''translation_dll='',translation_option=''<br /> Global Options: Version='06.50.0000', fetch=100,
socket=4096,unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190, disable_optimizer=1, ksqo=1,
unique_index=0,use_declarefetch=0, text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1,
extra_systable_prefixes='dd_;',conn_settings=''<br /> conn=15272060, query=' '<br /> conn=15272060, query='set
DateStyleto 'ISO''<br /> conn=15272060, query='set geqo to 'OFF''<br /> conn=15272060, query='set ksqo to 'ON''<br />
conn=15272060,query='select oid from pg_type where typname='lo''<br /> [ fetched 1 rows ]<br /> [ Large Object
oid= 350752 ]<br /> conn=15272060,
SQLDriverConnect(out)='DSN=POSTGRESQL;DATABASE=test;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=1;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS='<br
/>conn=15272060, query='BEGIN'<br /> conn=15272060, query='COMMIT'<br /> conn=15272060, query='BEGIN'<br />
conn=15272060,query='INSERT INTO a (id , obj) VALUES ( 100 , '4157953' ) '<br /> conn=15272060, query='COMMIT'<br />
conn=15272060,SQLDisconnect<br /></font>-----------------------------
hi, there! On Thu, 1 Mar 2001, Peter Mount wrote: > > > It seems that when an INSERT causes a duplicate key exception for > > > unique/primary keys, the JDBC library is throwing the SQL exception but > > > there is no code that lets my program detect a duplicate key error, or > > which > > > key caused the duplicate error. > > > > > > Is there a plan for the JDBC to return the codes for postgres back to the > > > program? I running 7.1beta4 now. > > > >AFAIK clean implementation of such feature is not possible because backend > >protocol does not have error codes (only error messages) > > Thats correct. It's also not practical to get the driver to translate > because the format of the messages can change between releases as well. can we consider adding error code prepended to error message and separated with e.g. one single space? seems that this will not break anything but will allow JDBC/ODBC drivers to return meaningful error codes. /fjoe
hi, there! On Thu, 1 Mar 2001, Bruce Momjian wrote: > I have ideas on how to do error codes and multiple languages. I can > make a proposal for 7.2. that would be great /fjoe
Quoting Max Khon <fjoe@iclub.nsu.ru>: > hi, there! > > On Thu, 1 Mar 2001, Peter Mount wrote: > > > > > It seems that when an INSERT causes a duplicate key exception for > > > > unique/primary keys, the JDBC library is throwing the SQL > exception but > > > > there is no code that lets my program detect a duplicate key > error, or > > > which > > > > key caused the duplicate error. > > > > > > > > Is there a plan for the JDBC to return the codes for postgres back > to the > > > > program? I running 7.1beta4 now. > > > > > >AFAIK clean implementation of such feature is not possible because > backend > > >protocol does not have error codes (only error messages) > > > > Thats correct. It's also not practical to get the driver to translate > > > because the format of the messages can change between releases as > well. > > can we consider adding error code prepended to error message and > separated with e.g. one single space? seems that this will not break > anything but will allow JDBC/ODBC drivers to return meaningful error > codes. That is the other alternative, and one that could be implemented pretty quickly. Peter -- Peter Mount peter@retep.org.uk PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/