Postgres Pro
Downloads
Home   >   mailing lists

Re: User defined functions... - Found to be tsearch - Mailing list pgsql-admin

From Carol Walter
Subject Re: User defined functions... - Found to be tsearch
Date
Msg-id 0B44204C-7F10-4DD5-8180-CF5D6506F671@indiana.edu
Whole thread Raw
In response to Re: User defined functions...  ("Joshua D. Drake" <jd@commandprompt.com>)
Responses Re: User defined functions... - Found to be tsearch
List pgsql-admin
Tree view
Hello,

It seems these functions are not user defined functions, but tsearch functions.  Now, I need to know about tsearch2.  I've searched my system and I don't find the functions that are being called from this database.  I'm still a newby with postgres.  Should I see these tables and these functions with in my users database or are they supposed to be global.  If they are supposed to be available only within the specific users database, how do I make them available to other databases.  I'm trying to make an exact copy of this database.  The original database is failing the sanity check.  I can copy the data out, but not the schema.  I manually created a duplicate schema to copy the data back into, but the copy is failing on these two table because it doesn't find the functions.  I'm not even sure I have the tsearch module for this version of postgres.  It's 8.2.4.  I did an upgrade to 8.2.11 and I see a tsearch directory under contrib, but there doesn't seem to be one anywhere else, where I would expect it to be for the 8.2.4 version.

When I describe the tables in the user's database that are related to tsearch they look as follows:

template1=# \c citesrch
You are now connected to database "citesrch".
citesrch=# \d pg_ts_dict
         Table "public.pg_ts_dict"
     Column      |     Type     | Modifiers 
-----------------+--------------+-----------
 dict_name       | text         | not null
 dict_init       | regprocedure | 
 dict_initoption | text         | 
 dict_lexize     | regprocedure | not null
 dict_comment    | text         | 
Indexes:
    "pg_ts_dict_pkey" PRIMARY KEY, btree (dict_name)

citesrch=# \d pg_ts_parser
       Table "public.pg_ts_parser"
    Column     |     Type     | Modifiers 
---------------+--------------+-----------
 prs_name      | text         | not null
 prs_start     | regprocedure | not null
 prs_nexttoken | regprocedure | not null
 prs_end       | regprocedure | not null
 prs_headline  | regprocedure | not null
 prs_lextype   | regprocedure | not null
 prs_comment   | text         | 
Indexes:
    "pg_ts_parser_pkey" PRIMARY KEY, btree (prs_name)

These tables exist in my user's database and look like any other user table.  If I search for functions that have names similar to the ones I see in the user table, I get records as follows:

citesrch=# select * from pg_proc where proname like 'pr%';
    proname     | pronamespace | proowner | prolang | proisagg | prosecdef | proisstrict | proretset | provolatile | pronargs | prorettype |  proargtypes   | proallargtypes | proargmodes | proargnames |     prosrc     |      probin      | proacl 
----------------+--------------+----------+---------+----------+-----------+-------------+-----------+-------------+----------+------------+----------------+----------------+-------------+-------------+----------------+------------------+--------
 prsd_end       |         2200 |       10 |      13 | f        | f         | f           | f         | v           |        1 |       2278 | 2281           |                |             |             | prsd_end       | $libdir/tsearch2 | 
 prsd_getlexeme |         2200 |       10 |      13 | f        | f         | f           | f         | v           |        3 |         23 | 2281 2281 2281 |                |             |             | prsd_getlexeme | $libdir/tsearch2 | 
 prsd_headline  |         2200 |       10 |      13 | f        | f         | f           | f         | v           |        3 |       2281 | 2281 2281 2281 |                |             |             | prsd_headline  | $libdir/tsearch2 | 
 prsd_lextype   |         2200 |       10 |      13 | f        | f         | f           | f         | v           |        1 |       2281 | 2281           |                |             |             | prsd_lextype   | $libdir/tsearch2 | 
 prsd_start     |         2200 |       10 |      13 | f        | f         | f           | f         | v           |        2 |       2281 | 2281 23        |                |             |             | prsd_start     | $libdir/tsearch2 | 
(5 rows)

citesrch=# select * from pg_proc where proname like '%dict%';
   proname   | pronamespace | proowner | prolang | proisagg | prosecdef | proisstrict | proretset | provolatile | pronargs | prorettype | proargtypes | proallargtypes | proargmodes | proargnames |       prosrc       |      probin      | proacl 
-------------+--------------+----------+---------+----------+-----------+-------------+-----------+-------------+----------+------------+-------------+----------------+-------------+-------------+--------------------+------------------+--------
 set_curdict |         2200 |       10 |      13 | f        | f         | t           | f         | v           |        1 |       2278 | 23          |                |             |             | set_curdict        | $libdir/tsearch2 | 
 set_curdict |         2200 |       10 |      13 | f        | f         | t           | f         | v           |        1 |       2278 | 25          |                |             |             | set_curdict_byname | $libdir/tsearch2 | 
(2 rows)



On Feb 6, 2009, at 2:35 PM, Joshua D. Drake wrote:

On Fri, 2009-02-06 at 14:30 -0500, Carol Walter wrote:
Hello,

Should all functions be visible when I issue the command
citesrch=# select * from pg_proc;
including those that are user defined?
My user has several functions in his database that I don't see there.   
I don't know if I'm looking in the wrong place or he dropped them  
after the table was created.

It will show per database. So if you want to see his, connect to his
database.

Joshua D. Drake


Carol


--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
  Consulting, Development, Support, Training
  503-667-4564 - http://www.commandprompt.com/
  The PostgreSQL Company, serving since 1997


pgsql-admin by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: strange autovacuum behaviour
Next
From: Rajesh Kumar Mallah
Date:
Subject: Re: controlling autovacuum during the day.