Thread

1. Re: [SQL] remove line type?

   mikeo <mikeo@spectrumtelecorp.com> — 2000-05-30T15:35:04Z

   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
   >
   
   

2. Re: Re: [SQL] remove line type?

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-05-30T17:41:55Z

   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
   
   

3. Re: Re: [SQL] remove line type?

   mikeo <mikeo@spectrumtelecorp.com> — 2000-05-30T19:07:59Z

   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
   >
   
   

4. TOP SESSIONS?

   mikeo <mikeo@spectrumtelecorp.com> — 2000-05-31T18:41:17Z

   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
   
   
   
   

5. Re: TOP SESSIONS?

   Mike Mascari <mascarm@mascari.com> — 2000-05-31T23:32:40Z

   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