Обсуждение: Re: [SQL] remove line type?
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 >
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
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 >
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
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