Skip site navigation (1) Skip section navigation (2)

Re: Re: [SQL] remove line type?

From: mikeo <mikeo(at)spectrumtelecorp(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-sql(at)postgreSQL(dot)org, pgsql-general(at)postgreSQL(dot)org
Subject: Re: Re: [SQL] remove line type?
Date: 2000-05-30 19:07:59
Message-ID: 3.0.1.32.20000530150759.00957360@pop.spectrumtelecorp.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-sql
thanks much.  that now works!  :)


At 01:41 PM 5/30/00 -0400, Tom Lane wrote:
>mikeo <mikeo(at)spectrumtelecorp(dot)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
>

In response to

pgsql-sql by date

Next:From: Tom LaneDate: 2000-05-30 21:36:36
Subject: Re: Function-based index not used in a simple query
Previous:From: Rostislav OpocenskyDate: 2000-05-30 18:31:39
Subject: Function-based index not used in a simple query

pgsql-general by date

Next:From: Joseph ShraibmanDate: 2000-05-30 19:15:08
Subject: Re: Is there a way to drop a column?
Previous:From: G. Anthony ReinaDate: 2000-05-30 18:43:58
Subject: Is there a way to drop a column?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group