Re: [SQL] remove line type?

Started by mikeoalmost 26 years ago5 messagesgeneral

mikeo@spectrumtelecorp.com

almost 26 years ago

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

Show quoted text

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

tgl@sss.pgh.pa.us

almost 26 years ago

In reply to: mikeo (#1)

Re: Re: [SQL] remove line type?

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

mikeo@spectrumtelecorp.com

almost 26 years ago

In reply to: Tom Lane (#2)

Re: Re: [SQL] remove line type?

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

Show quoted text

(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

mikeo@spectrumtelecorp.com

almost 26 years ago

In reply to: Tom Lane (#2)

TOP SESSIONS?

hi,

in oracle you would use these two cursors to determine who was
connected and

what they were doing.

select distinct s.sid sid, s.serial# serial, s.status status, osuser,
spid ,

count(o.sid) counter, s.username username, s.program
program, sql_address

from v$session s, v$open_cursor o, v$process p

where s.sid = o.sid(+)

and paddr = addr

group by s.sid,s.serial#, s.status , osuser, spid ,s.username,
s.program ,sql_address

order by 1,3

select distinct name

from sys.audit_actions, v$sqltext

where address = ?

and action = command_type

<bold><color><param>0000,0000,8080</param><bigger>does anyone know what
tables in postgres would give me the same or similar information?

</bigger></color></bold>TIA,

mikeo

mascarm@mascari.com

almost 26 years ago

In reply to: mikeo (#1)

Re: TOP SESSIONS?

mikeo wrote:

hi,
in oracle you would use these two cursors to determine who was connected and
what they were doing.

select distinct s.sid sid, s.serial# serial, s.status status, osuser, spid ,
count(o.sid) counter, s.username username, s.program program, sql_address
from v$session s, v$open_cursor o, v$process p
where s.sid = o.sid(+)
and paddr = addr
group by s.sid,s.serial#, s.status , osuser, spid ,s.username, s.program ,sql_address
order by 1,3

select distinct name
from sys.audit_actions, v$sqltext
where address = ?
and action = command_type

does anyone know what tables in postgres would give me the same or similar information?

TIA,
mikeo

PostgreSQL attempts to communicate what queries are being
performed by setting the process information in the connected
backend when processing a SQL statement, much like sendmail. You
should be able to determine who's connected and what they're
doing with something like:

ps axf

You'll see who's connected to what database from what machine and
the type of query being executed. I don't know of any tables in
PostgreSQL which would provide similar information.

Hope that helps,

Mike Mascari