Dropping column silently kills multi-coumn index (was [ODBC] Error when accessing tables with deleted columns)

> -----Original Message-----
> From: Michael Calabrese [mailto:m2calabr(at)yahoo(dot)com]
> Sent: 29 January 2003 19:06
> To: pgsql-odbc(at)postgresql(dot)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(at)vale-housing(dot)co(dot)uk> wrote:
>
>
> > -----Original Message-----
> > From: Michael Calabrese
> [mailto:m2calabr(at)yahoo(dot)com]
> > Sent: 29 January 2003 19:06
> > To: pgsql-odbc(at)postgresql(dot)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(at)postgresql(dot)org

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

The other discussion made me try this out:

> 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.

(Obviously 7.3.* is involved here...)

I did the following:

oms=# create table __temp1(field1 varchar(10), field2 varchar(10));
CREATE TABLE
oms=# create index __idx_temp1 on __temp1 (field1, field2);
CREATE INDEX
oms=# alter table __temp1 drop column field2;
ALTER TABLE

Note that the ALTER TABLE query succeeded *quietly* and did in fact drop
the index.

Is this intended behavior? Rather seems like it should refuse to drop
an indexed column, or at least refuse to drop a culumn that's a member
of a multi-culumm index and give notice when an index is dropped. I
think I'd opt for the never-drop-an-indexed-column approach.

Just an observation :-)

Glen Parker
glenebob(at)nwlink(dot)com

"Glen Parker" <glenebob(at)nwlink(dot)com> writes:
> Note that the ALTER TABLE query succeeded *quietly* and did in fact drop
> the index.

If indexes require a CASCADE to be dropped by DROP COLUMN, then DROP
TABLE on an indexed table would also require CASCADE. Does that seem
like a good idea?

regards, tom lane