Обсуждение: 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@deriva.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@sss.pgh.pa.us> wrote: > cs@deriva.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@postgresql.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
> -----Original Message----- > From: Michael Calabrese [mailto:m2calabr@yahoo.com] > Sent: 29 January 2003 19:06 > To: pgsql-odbc@postgresql.org > Subject: Re: [ODBC] Error when accessing tables with deleted columns > > > 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. A recent build can be found at http://postgresql.social-housing.org/psqlodbc.zip - this just contains updated dlls. I do not recommend you use this unless you specifically need the updated build (as Michael does). Regards, Dave.
Thank you for the fast responce WOW. I could not ask
for better. Sorry David I accidently sent the reponce
just to your, hopefully this will goto the list.
Here is what I am doing:
Running MS Access 97
Using ODBC dlls that I just got.
Linking tables into Access 97
Now I am getting the error:
Runtime error 3409
Invalid Field definition 'UNKNOWN' in definition of
index or relationship.
There is no error in the PSQL log, then end of it look
like:
PGAPI_Columns:
table='contacts',field_name='bsendemail',type=16,name='bool'
PGAPI_Columns:
table='contacts',field_name='vendcustid',type=1043,name='varchar'
PGAPI_Columns:
table='contacts',field_name='defaulttermid',type=23,name='int4'
PGAPI_Columns:
table='contacts',field_name='statreferral',type=700,name='float4'
PGAPI_Columns:
table='contacts',field_name='statpurchased',type=700,name='float4'
PGAPI_Columns:
table='contacts',field_name='statallbikepur',type=700,name='float4'
PGAPI_Columns:
table='contacts',field_name='statreferralpur',type=700,name='float4'
PGAPI_Columns:
table='contacts',field_name='statage',type=700,name='float4'
PGAPI_Columns:
table='contacts',field_name='statcurbikes',type=700,name='float4'
PGAPI_Columns:
table='contacts',field_name='statoverall',type=700,name='float4'
PGAPI_Columns:
table='contacts',field_name='brecfoldflyer',type=16,name='bool'
PGAPI_Columns:
table='contacts',field_name='donotuse',type=23,name='int4'
PGAPI_Columns:
table='contacts',field_name='associd',type=23,name='int4'
PGAPI_Columns:
table='contacts',field_name='uniqueid',type=1043,name='varchar'
PGAPI_Columns:
table='contacts',field_name='emailid',type=1043,name='varchar'
conn=87554308, query='select c.relname, i.indkey,
i.indisunique, i.indisclustered, a.amnam
e, c.relhasrules, n.nspname from pg_index i, pg_class
c, pg_class d, pg_am a, pg_namespace
n where d.relname = 'contacts' and n.nspname =
'public' and n.oid = d.relnamespace and d.
oid = i.indrelid and i.indexrelid = c.oid and c.relam
= a.oid order by i.indisprimary desc
, i.indisunique, n.nspname, c.relname'
[ fetched 13 rows ]
conn=87554308, PGAPI_Disconnect
conn=87535000, PGAPI_Disconnect
conn=87490680, PGAPI_Disconnect
conn=87509988, PGAPI_Disconnect
So the new dll did take out the delete column. I
assume that if you do a drop column that it removes
the associated index. If not could I have an index
that still is trying to point to the deleted column?
Any suggestions?
--- Dave Page <dpage@vale-housing.co.uk> wrote:
>
>
> > -----Original Message-----
> > From: Michael Calabrese
> [mailto:m2calabr@yahoo.com]
> > Sent: 29 January 2003 19:06
> > To: pgsql-odbc@postgresql.org
> > Subject: Re: [ODBC] Error when accessing tables
> with deleted columns
> >
> >
> > 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.
>
> A recent build can be found at
> http://postgresql.social-housing.org/psqlodbc.zip -
> this just contains
> updated dlls.
>
> I do not recommend you use this unless you
> specifically need the updated
> build (as Michael does).
>
> Regards, Dave.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
> -----Original Message----- > From: Michael Calabrese [mailto:m2calabr@yahoo.com] > Sent: 29 January 2003 22:20 > To: pgsql-odbc@postgresql.org > Subject: Re: [ODBC] Error when accessing tables with deleted columns > > > Thank you for the fast responce WOW. I could not ask > for better. You're welcome. > Now I am getting the error: > Runtime error 3409 > Invalid Field definition 'UNKNOWN' in definition of > index or relationship. I don't really know Access, but don't you have to re-link the table so Access re-examines it's structure? Regards, Dave.
Yes, that is where the error is comming from (linking the tables). I still believe that it has something to do with the deleted column. 1) It is still stopping on the same table. 2) I have a close backup that does not have the delete column and it works fine. I can most likely make it work by dumping the DB and reloading it. It is just that the one with the dropped column is my production database. (sigh) Thanks again for all of your help. Michael --- Dave Page <dpage@vale-housing.co.uk> wrote: > > > > -----Original Message----- > > From: Michael Calabrese > [mailto:m2calabr@yahoo.com] > > Sent: 29 January 2003 22:20 > > To: pgsql-odbc@postgresql.org > > Subject: Re: [ODBC] Error when accessing tables > with deleted columns > > > > > > Thank you for the fast responce WOW. I could not > ask > > for better. > > You're welcome. > > > Now I am getting the error: > > Runtime error 3409 > > Invalid Field definition 'UNKNOWN' in definition > of > > index or relationship. > > I don't really know Access, but don't you have to > re-link the table so > Access re-examines it's structure? > > Regards, Dave. __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com
I just wanted people to know if you have deleted a column in postgres and can not relink the tables in MS Access, the current solution is to dump the database and reload it. I was using 7.2.5 version of the odbc driver and tested the CVS that Dave kindly arranged for me. If someone would like me to test a later CVS please just email me with the location of a compiled version and I will attempt to recreate my problem. Thanks for everyones help, Michael --- Dave Page <dpage@vale-housing.co.uk> wrote: > > > > -----Original Message----- > > From: Michael Calabrese > [mailto:m2calabr@yahoo.com] > > Sent: 29 January 2003 22:20 > > To: pgsql-odbc@postgresql.org > > Subject: Re: [ODBC] Error when accessing tables > with deleted columns > > > > > > Thank you for the fast responce WOW. I could not > ask > > for better. > > You're welcome. > > > Now I am getting the error: > > Runtime error 3409 > > Invalid Field definition 'UNKNOWN' in definition > of > > index or relationship. > > I don't really know Access, but don't you have to > re-link the table so > Access re-examines it's structure? > > Regards, Dave. __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com