From: Tom Lane <tgl@sss.pgh.pa.us>
To: mikeo <mikeo@spectrumtelecorp.com>
Cc: pgsql-sql@postgreSQL.org
Cc: pgsql-general@postgreSQL.org
Subject: Re: Re: [SQL] remove line type?
Date: Tue, 30 May 2000 13:41:55 -0400
Message-ID: <2590.959708515@sss.pgh.pa.us> (raw)
In-Reply-To: <3.0.1.32.20000530113504.009563b0@pop.spectrumtelecorp.com>
References: <3.0.1.32.20000530113504.009563b0@pop.spectrumtelecorp.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, mikeo@spectrumtelecorp.com, pgsql-general@postgreSQL.org
Subject: Re: [SQL] remove line type?
In-Reply-To: <2590.959708515@sss.pgh.pa.us>
References: <3.0.1.32.20000530113504.009563b0@pop.spectrumtelecorp.com> <2590.959708515@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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