pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: User defined functions... - Found to be tsearch
3+ messages / 3 participants
[nested] [flat]
* Re: User defined functions... - Found to be tsearch
@ 2009-02-11 15:11 Carol Walter <walterc@indiana.edu>
2009-02-12 18:48 ` Re: User defined functions... - Found to be tsearch Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Carol Walter @ 2009-02-11 15:11 UTC (permalink / raw)
To: ; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: User defined functions... - Found to be tsearch
2009-02-11 15:11 Re: User defined functions... - Found to be tsearch Carol Walter <walterc@indiana.edu>
@ 2009-02-12 18:48 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-02-12 18:58 ` Re: User defined functions... - Found to be tsearch Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Scott Marlowe @ 2009-02-12 18:48 UTC (permalink / raw)
To: Carol Walter <walterc@indiana.edu>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: User defined functions... - Found to be tsearch
2009-02-11 15:11 Re: User defined functions... - Found to be tsearch Carol Walter <walterc@indiana.edu>
2009-02-12 18:48 ` Re: User defined functions... - Found to be tsearch Scott Marlowe <scott.marlowe@gmail.com>
@ 2009-02-12 18:58 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2009-02-12 18:58 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Carol Walter <walterc@indiana.edu>; pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2009-02-12 18:58 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-02-11 15:11 Re: User defined functions... - Found to be tsearch Carol Walter <walterc@indiana.edu>
2009-02-12 18:48 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-02-12 18:58 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox