pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
remove line type?
7+ messages / 3 participants
[nested] [flat]

* remove line type?
@ 2000-05-17 18:43 mikeo <mikeo@spectrumtelecorp.com>
  2000-05-17 18:51 ` Re: remove line type? Bruce Momjian <pgman@candle.pha.pa.us>
  2000-05-17 19:09 ` Re: remove line type? 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:43 remove line type? mikeo <mikeo@spectrumtelecorp.com>
@ 2000-05-17 18:51 ` Bruce Momjian <pgman@candle.pha.pa.us>
  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 18:43 remove line type? mikeo <mikeo@spectrumtelecorp.com>
@ 2000-05-17 19:09 ` mikeo <mikeo@spectrumtelecorp.com>
  2000-05-17 19:04   ` Re: remove line type? Bruce Momjian <pgman@candle.pha.pa.us>
  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: remove line type?
  2000-05-17 18:43 remove line type? mikeo <mikeo@spectrumtelecorp.com>
  2000-05-17 19:09 ` Re: remove line type? mikeo <mikeo@spectrumtelecorp.com>
@ 2000-05-17 19:04   ` Bruce Momjian <pgman@candle.pha.pa.us>
  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: [SQL] remove line type?
@ 2000-05-30 15:35 mikeo <mikeo@spectrumtelecorp.com>
  2000-05-30 17:41 ` Re: Re: [SQL] remove line type? Tom Lane <tgl@sss.pgh.pa.us>
  2000-05-30 19:07 ` Re: Re: [SQL] remove line type? 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 15:35 Re: [SQL] remove line type? mikeo <mikeo@spectrumtelecorp.com>
@ 2000-05-30 17:41 ` Tom Lane <tgl@sss.pgh.pa.us>
  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 15:35 Re: [SQL] remove line type? mikeo <mikeo@spectrumtelecorp.com>
@ 2000-05-30 19:07 ` 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