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

Re: Re: [SQL] remove line type?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: mikeo <mikeo(at)spectrumtelecorp(dot)com>
Cc: pgsql-sql(at)postgreSQL(dot)org, pgsql-general(at)postgreSQL(dot)org
Subject: Re: Re: [SQL] remove line type?
Date: 2000-05-30 17:41:55
Message-ID: 2590.959708515@sss.pgh.pa.us (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-generalpgsql-sql
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

Responses

pgsql-sql by date

Next:From: Rostislav OpocenskyDate: 2000-05-30 18:31:39
Subject: Function-based index not used in a simple query
Previous:From: Steve WamplerDate: 2000-05-30 17:36:38
Subject: Insert with replace?

pgsql-general by date

Next:From: Philip HallstromDate: 2000-05-30 18:20:10
Subject: Any way to send email from "within" PostgreSQL?
Previous:From: Patrick WelcheDate: 2000-05-30 16:17:26
Subject: Re: Can not guess your host type

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