Re: Error when accessing tables with deleted columns

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

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@nwlink.com

"Glen Parker" <glenebob@nwlink.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

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?

I see the connection you're trying to make there, but I don't think it
quite follows. When you drop a table, all its indexes logically become
orphaned and so can be quietly dropped; who would expect the indexes to
stay? When you drop a column that belongs to a multi-column index on
the other hand, the index does not become logically orphaned. It
becomes... Something else... I think it could be an intuative
expectation that the server should re-structure the index minus the
dropped field. In other words, the index *can* exist without the
dropped field, just not in its current form. Because of that
uncertainty, it makes sense to me to refuse to drop the column. The
reason I suggested the same behavior for *single* column indexes is
purely for constistancy.

The post that got me looking into this showed that exact uncertainty;
there was a question whether the index was dropped or not.

And no, requiring CASCADE on table drops to get rid of indexes makes
exactly zero sence to me :-)

Glen

The issue here is whether dropping a column should automatically drop a
multi-column index of which that column is a member.

The example shown below is particularly good because the dropped field
is second in the index, meaning that the index is useful for lookups on
field1 alone, so dropping field2 removes a useful index on field1. I
don't think it is defensible to allow DROP COLUMN to remove the index.
Instead, I think we have to refuse the DROP COLUMN and require the user
to drop the index and recreate it just on field1 if desired. I don't
think CASCASE enters into this because of the effect on field1.

Comments?

---------------------------------------------------------------------------

Example case was:

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.

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?

I see the connection you're trying to make there, but I don't think it
quite follows. When you drop a table, all its indexes logically become
orphaned and so can be quietly dropped; who would expect the indexes to
stay? When you drop a column that belongs to a multi-column index on
the other hand, the index does not become logically orphaned. It
becomes... Something else... I think it could be an intuative
expectation that the server should re-structure the index minus the
dropped field. In other words, the index *can* exist without the
dropped field, just not in its current form. Because of that
uncertainty, it makes sense to me to refuse to drop the column. The
reason I suggested the same behavior for *single* column indexes is
purely for constistancy.

The post that got me looking into this showed that exact uncertainty;
there was a question whether the index was dropped or not.

And no, requiring CASCADE on table drops to get rid of indexes makes
exactly zero sence to me :-)

Glen

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

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:

The issue here is whether dropping a column should automatically drop a
multi-column index of which that column is a member.

The example shown below is particularly good because the dropped field
is second in the index, meaning that the index is useful for lookups on
field1 alone, so dropping field2 removes a useful index on field1. I
don't think it is defensible to allow DROP COLUMN to remove the index.
Instead, I think we have to refuse the DROP COLUMN and require the user
to drop the index and recreate it just on field1 if desired. I don't
think CASCASE enters into this because of the effect on field1.

Comments?

Would it be possible/practical to have PostgreSQL recreate the
multi-column index, but without the dropped column?

Regards and best wishes,

Justin Clift

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

At 11:36 AM 2/15/03 +1100, Justin Clift wrote:

Bruce Momjian wrote:

think CASCASE enters into this because of the effect on field1.
Comments?

Would it be possible/practical to have PostgreSQL recreate the
multi-column index, but without the dropped column?

Wouldn't that take a long time in some cases?

I think it's a good idea to throw an error and refuse to drop the column
and index and let the DB admin decide what to do next.

If someone designs a system that regularly drops columns from tables AND
wants indexes on those columns, I'd figure requiring them to drop relevant
indexes first would be a good idea. Of course if they can optionally
configure things (triggers etc) to drop the index when dropping/altering a
column, that would be ok too.

When the admins don't know what they are doing or make a mistake - it'll
fail safe. When the admins know, as long as they are still able to set
things up accordingly, I don't think it's a big problem.

Regards,
Link.

Justin Clift wrote:

Bruce Momjian wrote:

The issue here is whether dropping a column should automatically drop a
multi-column index of which that column is a member.

The example shown below is particularly good because the dropped field
is second in the index, meaning that the index is useful for lookups on
field1 alone, so dropping field2 removes a useful index on field1. I
don't think it is defensible to allow DROP COLUMN to remove the index.
Instead, I think we have to refuse the DROP COLUMN and require the user
to drop the index and recreate it just on field1 if desired. I don't
think CASCASE enters into this because of the effect on field1.

Comments?

Would it be possible/practical to have PostgreSQL recreate the
multi-column index, but without the dropped column?

Yes, we could, but creating an index takes time, so is probably
something we would want the admin to do manually.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Added to TODO:

* Disallow DROP COLUMN on a column that is part of a multi-column index

---------------------------------------------------------------------------

Lincoln Yeoh wrote:

At 11:36 AM 2/15/03 +1100, Justin Clift wrote:

Bruce Momjian wrote:

think CASCASE enters into this because of the effect on field1.
Comments?

Would it be possible/practical to have PostgreSQL recreate the
multi-column index, but without the dropped column?

Wouldn't that take a long time in some cases?

I think it's a good idea to throw an error and refuse to drop the column
and index and let the DB admin decide what to do next.

If someone designs a system that regularly drops columns from tables AND
wants indexes on those columns, I'd figure requiring them to drop relevant
indexes first would be a good idea. Of course if they can optionally
configure things (triggers etc) to drop the index when dropping/altering a
column, that would be ok too.

When the admins don't know what they are doing or make a mistake - it'll
fail safe. When the admins know, as long as they are still able to set
things up accordingly, I don't think it's a big problem.

Regards,
Link.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Added to TODO:
* Disallow DROP COLUMN on a column that is part of a multi-column index

That's poorly phrased. What you meant to say is "require CASCADE if ..."

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Added to TODO:
* Disallow DROP COLUMN on a column that is part of a multi-column index

That's poorly phrased. What you meant to say is "require CASCADE if ..."

Actually, no. Does CASCADE make sense in this case? In the multi-key
index, the index is _shared_ by several columns? Do we have precedent
for CASCADE to remove something that is shared by others? I don't
think so.

However, I now see in the ALTER TABLE manual page:

CASCADE
Automatically drop objects that depend on the
dropped column or constraint (for example, views
referencing the column).

which does indicate that CASCADE delete things, like views, that are
shared.

TODO wording updated:

* Require DROP COLUMN CASCADE for a column that is part of a
multi-column index

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Added to TODO:
* Disallow DROP COLUMN on a column that is part of a multi-column index

That's poorly phrased. What you meant to say is "require CASCADE if ..."

Actually, no. Does CASCADE make sense in this case?

Yes, it does. The multi-key index is a dependent object, and dropping
dependent objects is exactly what CASCADE is for.

Do we have precedent
for CASCADE to remove something that is shared by others?

Certainly. Try dropping a function that is used in several default
expressions, for example.

regards, tom lane