Re: Possible bugs in ODBC driver - Mailing list pgsql-interfaces
| From | Dario Fumagalli |
|---|---|
| Subject | Re: Possible bugs in ODBC driver |
| Date | |
| Msg-id | 199804170951.LAA22696@mail.art-media.it Whole thread Raw |
| List | pgsql-interfaces |
OK, I went to my faithful Linux server :) tried your query and now as you
told me I post my results on the mailing list:
First I ran the query on one of our databases:
select relname, usename from pg_class, pg_user where relkind = 'r'
and relname !~ '^xinv[0-9]+' and int4out(usesysid) = int4out(relowner)
order by
relname;
The result I had are consistent both if I log as the postgres user (I
created all this database using this user)
and if I perform the query as a guest user (that cannot create tables and
can only do readonly operations):
$ psql jia
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: jia
jia=> select relname, usename from pg_class, pg_user where relkind = 'r'
jia-> and relname !~ '^xinv[0-9]+' and int4out(usesysid) =
int4out(relowner) ord
er by
jia-> relname;
relname |usename
--------------+--------
argomento |postgres
faq |postgres
pg_aggregate |postgres
pg_am |postgres
pg_amop |postgres
pg_amproc |postgres
pg_attrdef |postgres
pg_attribute |postgres
pg_class |postgres
pg_database |postgres
pg_description|postgres
pg_index |postgres
pg_inheritproc|postgres
pg_inherits |postgres
pg_ipl |postgres
pg_language |postgres
pg_listener |postgres
pg_opclass |postgres
pg_operator |postgres
pg_parg |postgres
pg_proc |postgres
pg_relcheck |postgres
pg_rewrite |postgres
pg_shadow |postgres
pg_statistic |postgres
pg_trigger |postgres
pg_type |postgres
pg_user |postgres
pg_version |postgres
(29 rows)
jia=>
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
$ psql -u jia
Username: jiaguest
Password:
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: jia
jia=> select relname, usename from pg_class, pg_user where relkind = 'r'
jia-> and relname !~ '^xinv[0-9]+' and int4out(usesysid) =
int4out(relowner) ord
er by
jia-> relname;
relname |usename
--------------+--------
argomento |postgres
faq |postgres
pg_aggregate |postgres
pg_am |postgres
pg_amop |postgres
pg_amproc |postgres
pg_attrdef |postgres
pg_attribute |postgres
pg_class |postgres
pg_database |postgres
pg_description|postgres
pg_index |postgres
pg_inheritproc|postgres
pg_inherits |postgres
pg_ipl |postgres
pg_language |postgres
pg_listener |postgres
pg_opclass |postgres
pg_operator |postgres
pg_parg |postgres
pg_proc |postgres
pg_relcheck |postgres
pg_rewrite |postgres
pg_shadow |postgres
pg_statistic |postgres
pg_trigger |postgres
pg_type |postgres
pg_user |postgres
pg_version |postgres
(29 rows)
jia=>
Moreover, if I perform a \d:
jia=> \d
Database = jia
+------------------+----------------------------------+----------+
| Owner | Relation | Type |
+------------------+----------------------------------+----------+
| postgres | argomento | table |
| postgres | argomento_idx_codice | index |
| postgres | argomento_pkey | index |
| postgres | argomento_seq_codice | sequence |
| postgres | faq | table |
| postgres | faq_idx_codice | index |
| postgres | faq_idx_codice_argomento | index |
| postgres | faq_idx_codice_faq_padre | index |
| postgres | faq_pkey | index |
| postgres | faq_seq_codice | sequence |
+------------------+----------------------------------+----------+
jia=>
I don't see duplicates table names.
Here there are the SQL statements I use to create the various database
objects:
create table argomento (
codice integer primary key not null,
argomento varchar(80)
);
------------
create unique index argomento_idx_codice on argomento (codice);
------------
create function inc_argomento_seq_codice()
returns integer as
'
select nextval(\'argomento_seq_codice\');
'
language 'sql';
------------
create table faq (
codice integer primary key not null,
codice_faq_padre integer,
autore varchar(65) not null,
codice_argomento integer default 0 not null,
soggetto varchar(100) not null,
testo text not null,
data char(8)
);
------------
create unique index faq_idx_codice on faq (codice);
------------
create index faq_idx_codice_faq_padre on faq (codice_faq_padre);
------------
create function inc_faq_seq_codice()
returns integer as
'
select nextval(\'faq_seq_codice\');
'
language 'sql';
------------
The Linux system I use is a Debian 1.3 distribution labeled as follows:
$ uname -a
Linux brainworks 2.0.30 #2 Tue Mar 10 19:38:42 CET 1998 i586 unknown
The PostgreSQL database version I'm using:
$ cat PG_VERSION
6.3
The source I used (I never use pre-compiled binaries) is:
$ ls -l
total 3870
-rwxr-xr-x 1 postgres postgres 1128 Mar 19 12:06 postgres
-rw-r--r-- 1 postgres postgres 3944336 Mar 25 10:02
postgresql-6.3.1.tar.gz
The startup script I use is the following (note: despite the header the
script has been modified extensively):
$ cat /etc/init.d/postgres
#!/bin/sh
#
# postgresql init.d script to start the postgesql postmaster
# This file should be used to construct scripts for
/etc/init.d.
#
# Written by Miquel van Smoorenburg <miquels@cistron.nl>.
# Modified for Debian GNU/Linux
# by Ian Murdock <imurdock@gnu.ai.mit.edu>.
#
# Version: @(#)skeleton 1.7 05-May-1997 miquels@cistron.nl
#
PATH=/sbin:/usr/sbin:/usr/local/postgresql/bin:/usr/local/bin:/bin:/usr/bin
POSTMASTER="/usr/local/postgresql/bin/postmaster"
NAME=postmaster
DESC="PostgreSQL postmaster"
USER=postgres
test -f $POSTMASTER || exit 0
set -e
case "$1" in
start)
echo -n "Starting $DESC: "
/bin/su -c "$POSTMASTER -i -S -D /var/postgres/data" $USER
echo "$NAME."
;;
stop)
echo -n "Stopping $DESC: "
kill -TERM `pidof $POSTMASTER`
echo "$NAME."
;;
reload)
# echo "Reloading $DESC configuration files."
# start-stop-daemon --stop --signal 1 --quiet --pidfile
/var/run/$NAME.pid \
# --exec $POSTMASTER
;;
*)
# echo "Usage: /etc/init.d/$NAME {start|stop|reload}"
echo "Usage: /etc/init.d/$NAME {start|stop}"
exit 1
;;
esac
exit 0
The environment variables are as follows:
$ export
declare -x CLASSPATH="/usr/local/jdk1.1/lib/classes.zip:."
declare -x EDITOR="joe"
declare -x HOME="/var/postgres"
declare -x HOSTNAME="brainworks"
declare -x HOSTTYPE="i586"
declare -x HUSHLOGIN="FALSE"
declare -x HZ="100"
declare -x JDK_HOME="/usr/local/jdk1.1"
declare -x LOGNAME="postgres"
declare -x MACHTYPE="i586-debian-linux"
declare -x MAIL="/var/spool/mail/postgres"
declare -x
MANPATH="/usr/local/man:/usr/man:/usr/X11R6/man:/usr/local/postgresql
/man"
declare -x OSTYPE="linux"
declare -x PAGER="less"
declare -x
PATH="/usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games:.:/usr/loc
al/postgresql/bin:/usr/local/jdk1.1/bin"
declare -x PGDATA="/var/postgres/data"
declare -x PGLIB="/usr/local/postgresql/lib"
declare -x PS1="\\\$ "
declare -x SHELL="/bin/sh"
declare -x SHLVL="1"
declare -x TERM="linux"
declare -x USER="postgres"
The following is the content of postodbc.log (generated using the suggested
check box) that is created when I use the program that hangs:
conn=50202748, SQLDriverConnect(
in)='DSN=ODBC_Giovine;UID=postgres;PWD=xxxxxx;DATABASE=giovine;'
conn=50202748, DSN
info(DSN='ODBC_Giovine',server='www.bw4.com',dbase='giovine',user='postgres'
,passwd='xxxxxx',port='5432',readonly='0',protocol='',conn_settings='')
conn=50202748,
SQLDriverConnect(out)='DSN=ODBC_Giovine;DATABASE=giovine;SERVER=www.bw4.com;
PORT=5432;UID=postgres;READONLY=0;PWD=xxxxxx;PROTOCOL=;CONNSETTINGS='
conn=50202748, query=' '
conn=50202748, query='set geqo to 'OFF''
conn=50202748, query='BEGIN'
conn=50202748, query='declare C50215884 cursor for select oid from pg_type
where typname='lo''
conn=50202748, query='fetch 100 in C50215884'
[ fetched 0 rows ]
conn=50202748, query='close C50215884; END'
conn=50202748, query='BEGIN'
conn=50202748, query='declare C50281496 cursor for select relname, usename
from pg_class, pg_user where relkind = 'r' and relname !~ '^pg_|^dd_' and
relname !~ '^xinv[0-9]+' and int4out(usesysid) = int4out(relowner) order by
relname'
conn=50202748, query='fetch 100 in C50281496'
[ fetched 4 rows ]
conn=50202748, query='close C50281496; END'
conn=50202748, query='BEGIN'
conn=50202748, query='declare C50215884 cursor for select * from codifa
where descriz like 'ASPRO%'
'
conn=50202748, query='fetch 100 in C50215884'
[ fetched 7 rows ]
conn=50202748, query='close C50215884; END'
One hint: please provide another checkbox that causes the log generator to
replace the password stored in unencrypted form with asterisks or some
other bogus characters.
The schema of the affected table is simple but long:
giovine=> \d codifa;
Table = codifa
+----------------------------------+----------------------------------+-----
--+
| Field | Type |
Length|
+----------------------------------+----------------------------------+-----
--+
| codi | char() |
7 |
| descriz | char() |
28 |
| tipo | char() |
1 |
| forn | char() |
4 |
| sc_ri | char() |
1 |
| sud_mer | char() |
1 |
| prez | char() |
6 |
| scad_mm | char() |
2 |
| scad_aa | char() |
2 |
| ct | char() |
1 |
| tab_stu | char() |
2 |
| single_ssn | char() |
1 |
| sostanza | char() |
6 |
| p_cip | char() |
8 |
| iva | char() |
2 |
| fu | char() |
1 |
| filler | char() |
1 |
| tab_scad | char() |
1 |
| ssn | char() |
1 |
| tick | char() |
4 |
| frig | char() |
1 |
| gt_gtv | char() |
4 |
| minischede | char() |
1 |
| dpr_stup | char() |
2 |
| sottogrup_stup | char() |
1 |
| nascita_mm | char() |
2 |
| nascita_aa | char() |
2 |
| progressione | char() |
1 |
| distribuzione | char() |
4 |
| refi | char() |
1 |
| caus_cong | char() |
1 |
| minsan_32 | char() |
6 |
| mercato_parallelo | char() |
1 |
| ass_inde | char() |
1 |
| minsan_10 | char() |
9 |
| antibiotici_m | char() |
1 |
| cod_cont | char() |
2 |
| note_ssn | char() |
2 |
| data_ven_gg | char() |
2 |
| data_ven_mm | char() |
2 |
| data_ven_aa | char() |
2 |
| atc | char() |
7 |
| doping | char() |
1 |
| classe_doping | char() |
1 |
| nuovo_gm | char() |
6 |
| ean_8_13 | char() |
13 |
| data_val_gg | char() |
2 |
| data_val_mm | char() |
2 |
| data_val_aa | char() |
2 |
| cod_int_ditta | char() |
13 |
| inventario | char() |
2 |
| prezzo_um | char() |
6 |
| aggancio_conf | char() |
9 |
| data_ics_gg | char() |
2 |
| data_ics_mm | char() |
2 |
| data_ics_aa | char() |
2 |
| data_fcs_gg | char() |
2 |
| data_fcs_mm | char() |
2 |
| data_fcs_aa | char() |
2 |
| um | char() |
1 |
| div_int_ditta | char() |
2 |
| cambio_conc_ssn | char() |
1 |
| cambio_sigla_ssn | char() |
1 |
| decod_prezzo | char() |
8 |
| cod_nomenclatore | char() |
7 |
| aggancio_galen_fn | char() |
6 |
| note_ssn_prec | char() |
2 |
| filler2 | char() |
5 |
| prezzo_old | char() |
8 |
| minsan_10_old | char() |
9 |
| iva_old | char() |
2 |
| ult_agg_gg | int2 |
2 |
| ult_agg_mm | int2 |
2 |
| ult_agg_aa | int2 |
2 |
| tipo_agg | char() |
1 |
+----------------------------------+----------------------------------+-----
--+
giovine=>
The sql statement used to create it:
CREATE TABLE codifa
(
codi char(7),
descriz char(28),
tipo char(1),
forn char(4),
sc_ri char(1),
sud_mer char(1),
prez char(6),
scad_mm char(2),
scad_aa char(2),
ct char(1),
tab_stu char(2),
single_ssn char(1),
sostanza char(6),
p_cip char(8),
iva char(2),
fu char(1),
filler char(1),
tab_scad char(1),
ssn char(1),
tick char(4),
frig char(1),
gt_gtv char(4),
minischede char(1),
dpr_stup char(2),
sottogrup_stup char(1),
nascita_mm char(2),
nascita_aa char(2),
progressione char(1),
distribuzione char(4),
refi char(1),
caus_cong char(1),
minsan_32 char(6),
mercato_parallelo char(1),
ass_inde char(1),
minsan_10 char(9),
antibiotici_m char(1),
cod_cont char(2),
note_ssn char(2),
data_ven_gg char(2),
data_ven_mm char(2),
data_ven_aa char(2),
atc char(7),
doping char(1),
classe_doping char(1),
nuovo_gm char(6),
ean_8_13 char(13),
data_val_gg char(2),
data_val_mm char(2),
data_val_aa char(2),
cod_int_ditta char(13),
inventario char(2),
prezzo_um char(6),
aggancio_conf char(9),
data_ics_gg char(2),
data_ics_mm char(2),
data_ics_aa char(2),
data_fcs_gg char(2),
data_fcs_mm char(2),
data_fcs_aa char(2),
um char(1),
div_int_ditta char(2),
cambio_conc_ssn char(1),
cambio_sigla_ssn char(1),
decod_prezzo char(8),
cod_nomenclatore char(7),
aggancio_galen_fn char(6),
note_ssn_prec char(2),
filler2 char(5),
prezzo_old char(8),
minsan_10_old char(9),
iva_old char(2),
ult_agg_gg int2,
ult_agg_mm int2,
ult_agg_aa int2,
tipo_agg char(1)
);
It is a fairly faithful transposition from an old COBOL managed database.
This table, created and accessed by user postgres is almost never modified
and has always worked well. We never had core dumps or backup problems.
This computer is a fresh installation so there are not "remains" of old
PostgreSQL libraries or other.
The programs that hangs are all those based on Borland Database Engine
4.50. The problem is that with those drivers only Access users can use
PostgreSQL and not the other consistent part that use any Borland Product
(all database related Borland products use a single local engine called
Borland Database Engine, that "sits" on top of local, native and ODBC
drivers). Those programs worked well with the old drivers. If you like I
can send you the executable of a very simple program that simply sends the
contents of an edit box to a server and retrieves the results.
Sample source code of a simple program that hangs:
#include <vcl.h>
#pragma hdrstop
#include "Unit1.h"
//--------------------------------------------------------------------------
-
#pragma package(smart_init)
#pragma resource "*.dfm"
TForm1 *Form1;
//--------------------------------------------------------------------------
-
__fastcall TForm1::TForm1(TComponent* Owner)
: TForm(Owner)
{
}
//--------------------------------------------------------------------------
-
void __fastcall TForm1::Button1Click(TObject *Sender)
{
qry->Active = false;
qry->SQL->Text = edtQuery->Text;
qry->Active = true;
}
This program, almost entirely auto-generated by Borland C++ Builder 3.0
Professional (build 3.70) perform as follows:
1) You install the old ODBC drivers: it works and sends the query digited
in the edtQuery edit box to the PostgreSQL server.
2) You remove the old ODBC drivers using their uninstall procedure and
install the new drivers: the program generates an ugly access violation
(other programs show a more friendly "Record size is too big for table"
error).
3) You remove the new ODBC drivers using their uninstall procedure and
reinstall the old drivers: the program does work again. Perhaps it is the
BDE or other that have problems, but isn't is possible somehow to restore
the old driver behaviour in order to make the new drivers compatible as the
old?
P.S.
By the amount of data I'm sending to the list via this email you can see
how we are concerned with this problem. We are spreading as much as
possible the PostgreSQL database (and in the future we'll put some
PostgreSQL related pages on our web server), but we (and many, many, many
others) need rock solid means to connect this Unix database to client
workstations (typically Windows95 based).
----------
Da: Byron Nikolaidis <byronn@insightdist.com>
A: Dario Fumagalli <dfumagalli@art-media.it>;
pgsql-interfaces@postgreSQL.org
Oggetto: Re: Possible bugs in ODBC driver
Data: giovedì 16 aprile 1998 22.15
Dario Fumagalli wrote:
> Dear Byron,
>
> We are a software house situated in Italy.
> We focus our applications on Linux+PostgreSQL 6.3.1 (server side) +
Borland
> Delphi/C++ Builder or Access 97 (front ends).
> We (and much more people than perhaps you think about) often use Borland
> compilers because they produce with relatively small developement time
fast
> executables and powerful programs.
>
> I'm the person in charge of evaluating software updates/drivers/patches
> before incorporating them in our applications.
>
> So as soon as I saw your post about a new release of the PostgreSQL ODBC
> driver I downloaded and installed it.
>
> Now, I have to say that this driver seems "nicer" (the old driver was
like
> a sort of "poor man's ODBC driver") and somewhat faster.
>
> But... there are some glitches we had not with the previous drivers
> (neither 0.21 nor 0.3x):
>
> 1) There is a minor glitch that (at least under Borland C++ Builder
> Professional 3.0 + BDE 4.1) causes the metadata of the last table to
appear
> duplicated (if you like I can send you a screen shot). I.e. if I have a
> database with the following three tables:
>
> - customers
> - orders
> - order_details
>
> The DatabaseExplorer utility Borland gives you to quickly manage tables
> using SQL (it is like the psql shell in Unix), tells you the database has
> the following structure:
>
> - customers
> - orders
> - order_details
> - order_details
>
> with the last entry (and its fields, indexes and so on...) repeated
twice.
> We can see this problem on all our databases. It seems to be some sort of
> linked list problem. This problem never appeared using the old drivers.
>
We have had this problem inhouse, and maybe it is possible that you have
it.
The SQLTables() function in the driver does a query very similar to a '\d'
in
the psql (you might want to try that and see if you get the same problem).
The
query is:
select relname, usename from pg_class, pg_user where relkind = 'r'
and relname !~ '^xinv[0-9]+' and int4out(usesysid) = int4out(relowner)
order by
relname;
If the relowner does not match the usesysid for all relations, you will get
multiple entries.
Maybe we could change the above query to use "distinct" to prevent this.
> 2) There is a very serious new limit on the maximum number of table
columns
> the driver seems to manage.
> We have a fairly large database of pharmaceutics, with tables having over
> 70 fields. We are using it since Feb/98 without a problem until I
installed
> the new ODBC driver. Now, if I perform a query like:
>
> SELECT * from codifa WHERE descriz LIKE 'ASPRO%'
>
> the program stops telling there is a database problem. It says:
>
> "Record size is too big for table"
>
Is that message "Record size is too big for table" coming from your
products or
from the ODBC driver? The new driver does do a good job of getting the
real
error message back to the user. It tries to concatenate any backend error
message with other messages. If that message is from your products, then
it
would be good if you could find the real error message.
The new driver (as did the old I think) only has one hard limit on number
of
fields and that is 512 for the bitmap.
Another option for finding the real error message is to use the new
'CommLog'
feature of the new driver. If this feature is selected, it will create a
file
called "psqlodbc.log" in your root directory of your PC with all queries
and
error messages to/from the backend. You could then look in there and see
what
happened and send it to me.
Also, are you using Postgres v6.3?
Byron
P.S., please use the "pgsql-interfaces@postgreSQL.org" to post these notes
for
everyone to see, including me.
pgsql-interfaces by date: