Re: Error when accessing tables with deleted columns

Hi!
I think I have found the following bug:
I have a PostgreSQL server (version 7.3.1) with a test database and a
test table in it. I created the table with two columns and dropped one
of it afterwards (using "alter table ... drop column").
When I try to connect to this table from MS Access 2000 using the
PostgreSQL ODBC driver (version 7.02.00.05) I get the following error
message (in German, hope you can understand it): "Das Datenbankmodul
kann '........pg.dropped.2........' nicht finden. Stellen Sie sicher,
dass es sich um einen gültigen Parameter oder Alias-Namen handelt, der
keine ungültigen Zeichen oder falsche Zeichensetzung enthält und dessen
Name nicht zu lang ist."

The psqlodbc_1044.log contains the following (confidential information
wiped out):
Global Options: Version='07.02.0005', fetch=100, socket=4096,
unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190
disable_optimizer=1, ksqo=1, unique_index=1,
use_declarefetch=0
text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=1 NAMEDATALEN=64
extra_systable_prefixes='dd_;', conn_settings=''
conn_encoding='OTHER'
conn=142161752, query=' '
conn=142161752, query='select version()'
[ fetched 1 rows ]
[ PostgreSQL version string = 'PostgreSQL 7.3.1 on
i686-pc-linux-gnu, compiled by GCC 2.95.3' ]
[ PostgreSQL version number = '7.3' ]
conn=142161752, query='set DateStyle to 'ISO''
conn=142161752, query='set geqo to 'OFF''
conn=142161752, query='select oid from pg_type where typname='lo''
[ fetched 0 rows ]
conn=142161752, query='select pg_client_encoding()'
[ fetched 1 rows ]
[ Client encoding = 'SQL_ASCII' (code = 0) ]
conn=142161752,
PGAPI_DriverConnect(out)='DSN=test;DATABASE=test;SERVER=xxx.xxx.xxx.xxx;PORT=5432;UID=xxx;PWD=xxx;A6=;A7=100;A8=4096;B0=254;B1=8190;BI=0;C2=dd_;;CX=1850fab'
conn=142161752, query='SELECT Config, nValue FROM MSysConf'
ERROR from backend during send_query: 'ERROR: Relation "msysconf" does
not exist'
STATEMENT ERROR: func=SC_execute, desc='', errnum=7, errmsg='Error while
executing the query'

------------------------------------------------------------
hdbc=142161752, stmt=142156088, result=142155928
manual_result=0, prepare=0, internal=0
bindings=0, bindings_allocated=0
parameters=0, parameters_allocated=0
statement_type=0, statement='SELECT Config, nValue FROM
MSysConf'
stmt_with_params='SELECT Config, nValue FROM MSysConf'
data_at_exec=-1, current_exec_param=-1, put_data=0
currTuple=-1, current_col=-1, lobj_fd=-1
maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0,
scroll_concurrency=1
cursor_name='SQL_CUR08792138'
----------------QResult Info
-------------------------------
fields=142185880, manual_tuples=0, backend_tuples=0,
tupleField=0, conn=0
fetch_count=0, num_total_rows=0, num_fields=0,
cursor='(NULL)'
message='ERROR: Relation "msysconf" does not exist',
command='(NULL)', notice='(NULL)'
status=7, inTuples=0
CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR:
Relation "msysconf" does not exist'
------------------------------------------------------------
henv=142155544, conn=142161752, status=1, num_stmts=16
sock=142155600, stmts=142155672, lobj_type=-999
---------------- Socket Info -------------------------------
socket=736, reverse=0, errornumber=0, errormsg='(NULL)'
buffer_in=142172736, buffer_out=142176840
buffer_filled_in=1, buffer_filled_out=0, buffer_read_in=1
conn=142161752, query='select relname, nspname, relkind from pg_class,
pg_namespace where relkind in ('r', 'v') and pg_namespace.oid =
relnamespace order by nspname, relname'
[ fetched 54 rows ]
conn=142161752, query='select c.relhasrules from pg_namespace u,
pg_class c where u.oid = c.relnamespace and c.relname = 'test' and
u.nspname = 'public''
[ fetched 1 rows ]
conn=142161752, query='select u.nspname, c.relname, a.attname,
a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull,
c.relhasrules from pg_namespace u, pg_class c, pg_attribute a, pg_type t
where u.oid = c.relnamespace and c.oid= a.attrelid and a.atttypid =
t.oid and (a.attnum > 0) and c.relname like 'test' and u.nspname like
'public' order by u.nspname, c.relname, attnum'
[ fetched 2 rows ]
PGAPI_Columns: table='test',field_name='id',type=23,name='int4'
PGAPI_Columns:
table='test',field_name='........pg.dropped.2........',type=1042,name='bpchar'

IMHO, the last line clearly shows the offending field specification. I
think the field_name should be checked for dropped columns before
sending the data to MS Access, which doesn't seem to understand this.

By the way (not too important, but since I'm just writing this mail
...): Would it be possible for the driver to filter out the "MSysConf"
queries produced by MS Access? They are somewhat annoying in the server
log files.

I hope that my bug report was helpful for you. Could you inform me if my
guess was right and when you will have fixed this bug?

Thanks and out ;)
Christian Schroeder

cs(at)deriva(dot)de writes:
> table='test',field_name='........pg.dropped.2........',type=1042,name='bpchar'

> IMHO, the last line clearly shows the offending field specification.

IIRC, the ODBC driver sources were fixed to ignore dropped columns in
early December. I'm not sure if an official release has been made since
then, but perhaps you could grab the CVS tip code and build for
yourself.

regards, tom lane

I have discovered today that I have the same problem.
I do not have MS C to compile the driver. Can someone
please compile for me so I can use/test the fix? I
would be very gratefull as I need it to complete a
project that I am working on. Just give me a url (or
ftp), if possible, if you can compile.

Thanks for everyone's help,
Michael Calabrese

--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> cs(at)deriva(dot)de writes:
> >
>
table='test',field_name='........pg.dropped.2........',type=1042,name='bpchar'
>
> > IMHO, the last line clearly shows the offending
> field specification.
>
> IIRC, the ODBC driver sources were fixed to ignore
> dropped columns in
> early December. I'm not sure if an official release
> has been made since
> then, but perhaps you could grab the CVS tip code
> and build for
> yourself.
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org
> so that your
> message can get through to the mailing list cleanly

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com