Обсуждение: User defined functions...
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. Carol
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
Does your user have their own SCHEMA, if so you will have to : set search_path to SCHEMA_NAME -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Joshua D. Drake Sent: Friday, February 06, 2009 1:36 PM To: Carol Walter Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] User defined functions... 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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Thank you, all, for your help. It appears that everything is in schema "public". I found a number of functions that have very similar names to the ones in his tables so I think he probably dropped these after he created the tables. Carol On Feb 6, 2009, at 2:42 PM, Plugge, Joe R. wrote: > Does your user have their own SCHEMA, if so you will have to : > > set search_path to SCHEMA_NAME > > -----Original Message----- > From: pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Joshua D. Drake > Sent: Friday, February 06, 2009 1:36 PM > To: Carol Walter > Cc: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] User defined functions... > > 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 > > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin
Hello,
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 commandcitesrch=# 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 themafter the table was created.
It will show per database. So if you want to see his, connect to his
database.
Joshua D. DrakeCarol--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997
On Wed, Feb 11, 2009 at 8:11 AM, Carol Walter <walterc@indiana.edu> wrote: > 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. tsearch is included from 8.3 onwards. 8.2 does not have it built it. You'd have to go into the contrib directory and build it (if you built pgsql from source) and then install it from there. But upgrading from 8.2 with tsearch added on to 8.3 is a bit of a pain, so I'd strongly suggest just going to 8.3 if you can. If 8.2.4 is missing a contrib dir, and you installed from packages it's likely you didn't install the 8.2.4-contrib package.
Scott Marlowe <scott.marlowe@gmail.com> writes:
> tsearch is included from 8.3 onwards. 8.2 does not have it built it.
> You'd have to go into the contrib directory and build it (if you built
> pgsql from source) and then install it from there. But upgrading from
> 8.2 with tsearch added on to 8.3 is a bit of a pain, so I'd strongly
> suggest just going to 8.3 if you can. If 8.2.4 is missing a contrib
> dir, and you installed from packages it's likely you didn't install
> the 8.2.4-contrib package.
No, Carol's problem is she's trying to load 8.2-vintage tsearch
configuration tables into an 8.3 installation. That won't work
unless she first installs the *8.3* contrib/tsearch2 compatibility
module. See its documentation.
regards, tom lane