pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedremove line type?
7+ messages / 3 participants
[nested] [flat]
* remove line type?
@ 2000-05-17 18:43 mikeo <mikeo@spectrumtelecorp.com>
0 siblings, 2 replies; 7+ messages in thread
From: mikeo @ 2000-05-17 18:43 UTC (permalink / raw)
To: pgsql-sql
hi,
we're looking at migrating from ORACLE to postgres in the
very near future and we've run into a small problem. there's
a data type defined "LINE". we have named one of our tables
as "LINE" also and it would require a great deal of code
changes to rename that table. is it possible to simply
"turn off" the line type? any help is appreciated.
thanks,
mikeo
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: remove line type?
@ 2000-05-17 18:51 Bruce Momjian <pgman@candle.pha.pa.us>
parent: mikeo <mikeo@spectrumtelecorp.com>
1 sibling, 0 replies; 7+ messages in thread
From: Bruce Momjian @ 2000-05-17 18:51 UTC (permalink / raw)
To: mikeo <mikeo@spectrumtelecorp.com>; +Cc: pgsql-sql
I guess you could remove the line type from the pg_type table and see if
that helps.
> hi,
> we're looking at migrating from ORACLE to postgres in the
> very near future and we've run into a small problem. there's
> a data type defined "LINE". we have named one of our tables
> as "LINE" also and it would require a great deal of code
> changes to rename that table. is it possible to simply
> "turn off" the line type? any help is appreciated.
>
> thanks,
> mikeo
>
>
--
Bruce Momjian | http://www.op.net/~candle
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: remove line type?
@ 2000-05-17 19:04 Bruce Momjian <pgman@candle.pha.pa.us>
parent: mikeo <mikeo@spectrumtelecorp.com>
0 siblings, 0 replies; 7+ messages in thread
From: Bruce Momjian @ 2000-05-17 19:04 UTC (permalink / raw)
To: mikeo <mikeo@spectrumtelecorp.com>; +Cc: pgsql-sql
If you do it in template1 database after initdb, all new databases will
not have that type either.
> that worked!!! thanks!
>
> mikeo
>
>
> At 02:51 PM 5/17/00 -0400, Bruce Momjian wrote:
> >I guess you could remove the line type from the pg_type table and see if
> >that helps.
> >
> >> hi,
> >> we're looking at migrating from ORACLE to postgres in the
> >> very near future and we've run into a small problem. there's
> >> a data type defined "LINE". we have named one of our tables
> >> as "LINE" also and it would require a great deal of code
> >> changes to rename that table. is it possible to simply
> >> "turn off" the line type? any help is appreciated.
> >>
> >> thanks,
> >> mikeo
> >>
> >>
> >
> >
> >--
> > Bruce Momjian | http://www.op.net/~candle
> > pgman@candle.pha.pa.us | (610) 853-3000
> > + If your life is a hard drive, | 830 Blythe Avenue
> > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> >
>
--
Bruce Momjian | http://www.op.net/~candle
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: remove line type?
@ 2000-05-17 19:09 mikeo <mikeo@spectrumtelecorp.com>
parent: mikeo <mikeo@spectrumtelecorp.com>
1 sibling, 1 reply; 7+ messages in thread
From: mikeo @ 2000-05-17 19:09 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: pgsql-sql
that worked!!! thanks!
mikeo
At 02:51 PM 5/17/00 -0400, Bruce Momjian wrote:
>I guess you could remove the line type from the pg_type table and see if
>that helps.
>
>> hi,
>> we're looking at migrating from ORACLE to postgres in the
>> very near future and we've run into a small problem. there's
>> a data type defined "LINE". we have named one of our tables
>> as "LINE" also and it would require a great deal of code
>> changes to rename that table. is it possible to simply
>> "turn off" the line type? any help is appreciated.
>>
>> thanks,
>> mikeo
>>
>>
>
>
>--
> Bruce Momjian | http://www.op.net/~candle
> pgman@candle.pha.pa.us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] remove line type?
@ 2000-05-30 15:35 mikeo <mikeo@spectrumtelecorp.com>
0 siblings, 2 replies; 7+ messages in thread
From: mikeo @ 2000-05-30 15:35 UTC (permalink / raw)
To: pgsql-sql; pgsql-general@postgresql.org
for your perusal...
>From: Bruce Momjian <pgman@candle.pha.pa.us>
>Subject: Re: [SQL] remove line type?
>To: mikeo <mikeo@spectrumtelecorp.com>
>Date: Tue, 30 May 2000 10:48:02 -0400 (EDT)
>
>That is very strange. I would send it to the mailing lists.
>
>> hi bruce,
>> we've run into a problem after having deleted the line type.
>> when we attempt to query a table by column which is defined as float8
>> we get this error:
>>
>> \d test1
>> Table "test1"
>> Attribute | Type | Modifier
>> -----------+-------------+----------
>> tfap_id | float8 |
>> tfap_name | varchar(50) |
>> groupid | integer |
>> groupdesc | varchar(50) |
>> switch | varchar(50) |
>>
>> select * from test1 where tfap_id = 49232;
>> ERROR: Unable to locate type oid 628 in catalog
>>
>> if the column is defined as an integer we get the desired results:
>>
>> spectl=# \d topflow_application
>> Table "topflow_application"
>> Attribute | Type | Modifier
>> -----------+-------------+----------
>> tfap_id | integer |
>> tfap_name | varchar(50) |
>> groupid | integer |
>> groupdesc | varchar(50) |
>> switch | varchar(50) |
>>
>> select * from topflow_application where tfap_id = 049232;
>> tfap_id | tfap_name | groupid | groupdesc |
>> switch
>>
---------+---------------------------+---------+-------------------------+--
>> -------------
>> 49232 | xxxxxxxxxxxxxxxxxxxxxxxxx | 31 | Remote Control Services |
>> 111.111.1.111
>>
>> now, the programmer who created test1 table made that column a float by
>> accident
>> but that could mean trouble down the road for us as we do use float for
>> some other columns.
>> you can still select from test1 as long as you don't reference the float
>> column in the where
>> clause.
>>
>> oid 628 is the oid for the line row in the pg_type table. is there
>> something else that we
>> need to do or is deleting this type not a good idea after all?
>>
>> thanks,
>> mikeo
>>
>>
>>
>>
>> At 03:04 PM 5/17/00 -0400, you wrote:
>> >If you do it in template1 database after initdb, all new databases will
>> >not have that type either.
>> >
>> >> that worked!!! thanks!
>> >>
>> >> mikeo
>> >>
>> >>
>> >> At 02:51 PM 5/17/00 -0400, Bruce Momjian wrote:
>> >> >I guess you could remove the line type from the pg_type table and
see if
>> >> >that helps.
>> >> >
>> >> >> hi,
>> >> >> we're looking at migrating from ORACLE to postgres in the
>> >> >> very near future and we've run into a small problem. there's
>> >> >> a data type defined "LINE". we have named one of our tables
>> >> >> as "LINE" also and it would require a great deal of code
>> >> >> changes to rename that table. is it possible to simply
>> >> >> "turn off" the line type? any help is appreciated.
>> >> >>
>> >> >> thanks,
>> >> >> mikeo
>>
>>
>
>
>--
> Bruce Momjian | http://www.op.net/~candle
> pgman@candle.pha.pa.us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Re: [SQL] remove line type?
@ 2000-05-30 17:41 Tom Lane <tgl@sss.pgh.pa.us>
parent: mikeo <mikeo@spectrumtelecorp.com>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2000-05-30 17:41 UTC (permalink / raw)
To: mikeo <mikeo@spectrumtelecorp.com>; +Cc: pgsql-sql; pgsql-general@postgreSQL.org
mikeo <mikeo@spectrumtelecorp.com> writes:
>>> we've run into a problem after having deleted the line type.
>>> when we attempt to query a table by column which is defined as float8
>>> we get this error:
>>>
>>> select * from test1 where tfap_id = 49232;
>>> ERROR: Unable to locate type oid 628 in catalog
Interesting. I get:
bust=# create table foo (f1 int, f2 float8);
CREATE
bust=# insert into foo values(1,2.5);
INSERT 148298 1
bust=# select * from foo;
f1 | f2
----+-----
1 | 2.5
(1 row)
bust=# drop type line;
DROP
bust=# select * from foo;
f1 | f2
----+-----
1 | 2.5
(1 row)
bust=# select * from foo where f2 = 2.5;
f1 | f2
----+-----
1 | 2.5
(1 row)
bust=# select * from foo where f2 < 3;
f1 | f2
----+-----
1 | 2.5
(1 row)
bust=# select * from foo where f2 = 3;
ERROR: Unable to locate type oid 628 in catalog
It looks to me like the problem appears when the parser has to resolve
an ambiguous operator. (Since there isn't a "float8 = int" operator,
this last case requires some smarts to figure out what to do.)
Presumably there is a line = line operator still in the system, and
it doesn't surprise me a whole lot that this error would pop up if the
parser had occasion to scan through the '=' operators looking for a
possible match and came across that one. Let's see:
bust=# select * from pg_operator where oprname = '=' and
bust-# (oprleft = 628 or oprright = 628);
oprname | oprowner | oprprec | oprkind | oprisleft | oprcanhash | oprleft | oprright | oprresult | oprcom | oprnegate | oprlsortop | oprrsortop | oprcode | oprrest | oprjoin
---------+----------+---------+---------+-----------+------------+---------+----------+-----------+--------+-----------+------------+------------+---------+---------+-----------
= | 256 | 0 | b | t | f | 628 | 628 | 16 | 1616 | 0 | 0 | 0 | line_eq | eqsel | eqjoinsel
(1 row)
bust=# delete from pg_operator where oprname = '=' and
bust-# (oprleft = 628 or oprright = 628);
DELETE 1
bust=# select * from foo where f2 = 3;
f1 | f2
----+----
(0 rows)
Yup, looks like that's the problem.
It's probably not good that DROP TYPE only zaps the pg_type entry and
doesn't go hunting for stuff that depends on it. In the meantime you
might want to do
delete from pg_operator where oprleft = 628 or oprright = 628;
and perhaps something similar for pg_proc, although name collisions for
functions are probably less of a problem there.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Re: [SQL] remove line type?
@ 2000-05-30 19:07 mikeo <mikeo@spectrumtelecorp.com>
parent: mikeo <mikeo@spectrumtelecorp.com>
1 sibling, 0 replies; 7+ messages in thread
From: mikeo @ 2000-05-30 19:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; pgsql-general@postgreSQL.org
thanks much. that now works! :)
At 01:41 PM 5/30/00 -0400, Tom Lane wrote:
>mikeo <mikeo@spectrumtelecorp.com> writes:
>>>> we've run into a problem after having deleted the line type.
>>>> when we attempt to query a table by column which is defined as float8
>>>> we get this error:
>>>>
>>>> select * from test1 where tfap_id = 49232;
>>>> ERROR: Unable to locate type oid 628 in catalog
>
>Interesting. I get:
>
>bust=# create table foo (f1 int, f2 float8);
>CREATE
>bust=# insert into foo values(1,2.5);
>INSERT 148298 1
>bust=# select * from foo;
> f1 | f2
>----+-----
> 1 | 2.5
>(1 row)
>
>bust=# drop type line;
>DROP
>bust=# select * from foo;
> f1 | f2
>----+-----
> 1 | 2.5
>(1 row)
>
>bust=# select * from foo where f2 = 2.5;
> f1 | f2
>----+-----
> 1 | 2.5
>(1 row)
>
>bust=# select * from foo where f2 < 3;
> f1 | f2
>----+-----
> 1 | 2.5
>(1 row)
>
>bust=# select * from foo where f2 = 3;
>ERROR: Unable to locate type oid 628 in catalog
>
>It looks to me like the problem appears when the parser has to resolve
>an ambiguous operator. (Since there isn't a "float8 = int" operator,
>this last case requires some smarts to figure out what to do.)
>Presumably there is a line = line operator still in the system, and
>it doesn't surprise me a whole lot that this error would pop up if the
>parser had occasion to scan through the '=' operators looking for a
>possible match and came across that one. Let's see:
>
>bust=# select * from pg_operator where oprname = '=' and
>bust-# (oprleft = 628 or oprright = 628);
> oprname | oprowner | oprprec | oprkind | oprisleft | oprcanhash | oprleft
| oprright | oprresult | oprcom | oprnegate | oprlsortop | oprrsortop |
oprcode | oprrest | oprjoin
>---------+----------+---------+---------+-----------+------------+---------
+----------+-----------+--------+-----------+------------+------------+-----
----+---------+-----------
> = | 256 | 0 | b | t | f | 628
| 628 | 16 | 1616 | 0 | 0 | 0 |
line_eq | eqsel | eqjoinsel
>(1 row)
>
>bust=# delete from pg_operator where oprname = '=' and
>bust-# (oprleft = 628 or oprright = 628);
>DELETE 1
>bust=# select * from foo where f2 = 3;
> f1 | f2
>----+----
>(0 rows)
>
>Yup, looks like that's the problem.
>
>It's probably not good that DROP TYPE only zaps the pg_type entry and
>doesn't go hunting for stuff that depends on it. In the meantime you
>might want to do
>
>delete from pg_operator where oprleft = 628 or oprright = 628;
>
>and perhaps something similar for pg_proc, although name collisions for
>functions are probably less of a problem there.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2000-05-30 19:07 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-05-17 18:43 remove line type? mikeo <mikeo@spectrumtelecorp.com>
2000-05-17 18:51 ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-05-17 19:09 ` mikeo <mikeo@spectrumtelecorp.com>
2000-05-17 19:04 ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-05-30 15:35 Re: [SQL] remove line type? mikeo <mikeo@spectrumtelecorp.com>
2000-05-30 17:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-05-30 19:07 ` mikeo <mikeo@spectrumtelecorp.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox