During dump: function not found

Started by Andrew Gouldover 24 years ago4 messagesgeneral

andrewgould@yahoo.com

over 24 years ago

During a pg_dump, I got the following messages:

Notice: function "pgadmin_get_rows" is not dumped.
Reason: return type name (oid 87589805) not found.
Notice: function "pgadmin_get_sequence" is not dumped.
Reason: return type name (oid 87589772) not found.

Does this simply mean that these functions will not be
available if I restore from the dump file? Will these
messages haunt me down the road in other ways?

I'm assuming (dangerous, I know) that these functions
were created by PgAdmin rather than being a part of
PostgreSQL. I deleted all tables and views named
pgadmin* and am trying to weed out everything created
by PgAdmin.

Thanks,

Andrew Gould

__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/

From pgsql-general-owner@postgresql.org Wed Aug 22 11:18:41 2001

Received: from hotmail.com (f104.law4.hotmail.com [216.33.149.104])
by postgresql.org (8.11.3/8.11.4) with ESMTP id f7MF8xP17715
for <pgsql-general@postgresql.org>; Wed, 22 Aug 2001 11:08:59 -0400 (EDT)
(envelope-from ulive1x@hotmail.com)
Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC;
Wed, 22 Aug 2001 08:08:55 -0700
Received: from 24.190.144.118 by lw4fd.law4.hotmail.msn.com with HTTP; Wed, 22 Aug 2001 15:08:55 GMT
X-Originating-IP: [24.190.144.118]
From: "Paul C." <ulive1x@hotmail.com>
To: pgsql-general@postgresql.org
Subject: FTI is really really slow; what am I doing wrong?
Date: Wed, 22 Aug 2001 11:08:55 -0400
Mime-Version: 1.0
Content-Type: text/plain; format=flowed
Message-ID: <F104VWDKpzxDPHprp5y00014a94@hotmail.com>
X-OriginalArrivalTime: 22 Aug 2001 15:08:55.0517 (UTC) FILETIME=[5C9100D0:01C12B1C]
X-Archive-Number: 200108/671
X-Sequence-Number: 14055

Greetings,
I am trying to test out the performance of the contrib/fulltextindex
package and I am getting horrid performance results.
The Setup:
I created a simple table, ST (id SERIAL, body varchar(1024), which is to be
searched. I created the ST_FTI table, trigger and indices as per
instructions in the FTI readme and C file. To populate the table, I took a
flat text version of 'War and Peace' I found on the net, broke it up into
sentences and inserted each sentence into ST as a row. So I have about
38,000 sentences and my ST_FTI table is about 2 million rows.
The Test:
There is exactly one sentence (row) that has the strings 'Newton' and
'Kepler' in it. That is my target. For a straight select on ST:
select * from st where body ~* 'newton' and body ~* 'kepler';
the cost is 1100.41
BUT for an query using the FTI indices:
select s.* from st s, st_fti f1, st_fti f2 where f1.string
~ '^kepler' and f2.string ~ '^newton' and s.oid = f1.id
and s.oid = f2.id;
the cost becomes a staggering 80628.92!!! The plans are pasted at the end
of this message.
Now, I have all the indices created (on id of st_fti, on string of st_fti
and on oid of st). I cannot figure out why this is so much worse than the
straight query. Indeed, the cost to look up a single string in the st_fti
table is way high:
select * from st_fti where string ~ '^kepler';
costs 36703.40, AND its doing a Seq Scan on st_fti, even though an index
exists.
What am I doing wrong? Is it the sheer size of the st_fti table that is
causing problems? Any help would be greatly appreciated.
Thanks,
Paul C.

FTI search
NOTICE: QUERY PLAN:
Merge Join (cost=80046.91..80628.92 rows=110 width=28)
-> Sort (cost=41827.54..41827.54 rows=19400 width=24)
-> Hash Join (cost=1992.80..40216.39 rows=19400 width=24)
-> Seq Scan on st_fti f2 (cost=0.00..36703.40 rows=19400
width=4)
-> Hash (cost=929.94..929.94 rows=34094 width=20)
-> Seq Scan on st s (cost=0.00..929.94 rows=34094
width=20)
-> Sort (cost=38219.37..38219.37 rows=19400 width=4)
-> Seq Scan on st_fti f1 (cost=0.00..36703.40 rows=19400 width=4)
EXPLAIN

Plain search:
NOTICE: QUERY PLAN:
Seq Scan on st (cost=0.00..1100.41 rows=1 width=16)
EXPLAIN

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp

Justin Clift

justin@postgresql.org

over 24 years ago

In reply to: Andrew Gould (#1)

Re: During dump: function not found

Hi Andrew,

These functions were indeed created by PgAdmin. It seems to create
tables, functions, triggers and views (maybe rules too, not sure) going
by the name pgadmin_xxx and pga_xxx

I'm pretty sure from looking at PgAdmin 7.1.0 recently it has a menu
option to remove it's tables and other constructs from a target database
by itself. You might just want to run that instead of doing it
yourself/

:-)

Regards and best wishes,

Justin Clift

Andrew Gould wrote:

During a pg_dump, I got the following messages:

Notice: function "pgadmin_get_rows" is not dumped.
Reason: return type name (oid 87589805) not found.
Notice: function "pgadmin_get_sequence" is not dumped.
Reason: return type name (oid 87589772) not found.

Does this simply mean that these functions will not be
available if I restore from the dump file? Will these
messages haunt me down the road in other ways?

I'm assuming (dangerous, I know) that these functions
were created by PgAdmin rather than being a part of
PostgreSQL. I deleted all tables and views named
pgadmin* and am trying to weed out everything created
by PgAdmin.

Thanks,

Andrew Gould

__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

Andrew Gould

andrewgould@yahoo.com

over 24 years ago

In reply to: Justin Clift (#2)

Re: During dump: function not found

oops. Too late to use that option; but thanks!

Andrew Gould

--- Justin Clift <justin@postgresql.org> wrote:

Hi Andrew,

These functions were indeed created by PgAdmin. It
seems to create
tables, functions, triggers and views (maybe rules
too, not sure) going
by the name pgadmin_xxx and pga_xxx

I'm pretty sure from looking at PgAdmin 7.1.0
recently it has a menu
option to remove it's tables and other constructs
from a target database
by itself. You might just want to run that instead
of doing it
yourself/

:-)

Regards and best wishes,

Justin Clift

Andrew Gould wrote:

During a pg_dump, I got the following messages:

Notice: function "pgadmin_get_rows" is not dumped.
Reason: return type name (oid 87589805) not found.
Notice: function "pgadmin_get_sequence" is not

dumped.

Reason: return type name (oid 87589772) not found.

Does this simply mean that these functions will

not be

available if I restore from the dump file? Will

these

messages haunt me down the road in other ways?

I'm assuming (dangerous, I know) that these

functions

were created by PgAdmin rather than being a part

of

PostgreSQL. I deleted all tables and views named
pgadmin* and am trying to weed out everything

created

by PgAdmin.

Thanks,

Andrew Gould

__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute

with Yahoo! Messenger

http://phonecard.yahoo.com/
---------------------------(end of

broadcast)---------------------------

TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

--
"My grandfather once told me that there are two
kinds of people: those
who work and those who take the credit. He told me
to try to be in the
first group; there was less competition there."
- Indira Gandhi

__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/

From pgsql-general-owner@postgresql.org Wed Aug 22 13:46:55 2001

Received: from server (client-sp03-85.speedex.net [208.33.90.85])
by postgresql.org (8.11.3/8.11.4) with ESMTP id f7MHJZP72238
for <pgsql-general@postgresql.org>; Wed, 22 Aug 2001 13:19:35 -0400 (EDT)
(envelope-from will@serensoft.com)
Received: from will by server with local (Exim 3.12 #1 (Debian))
id 15ZbfB-0005Nf-00
for <pgsql-general@postgresql.org>; Wed, 22 Aug 2001 12:19:33 -0500
Date: Wed, 22 Aug 2001 12:19:33 -0500
From: will trillich <will@serensoft.com>
To: pgsql-general@postgresql.org
Subject: Re: Re: Syntax for wildcard selection
Message-ID: <20010822121933.B15403@serensoft.com>
Mail-Followup-To: pgsql-general@postgresql.org
References: <jasont@indigoindustrial.co.nz> <200108160011.RAA01695@scotts.mynetwork.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.2.5i
In-Reply-To: <200108160011.RAA01695@scotts.mynetwork.net>; from scott@pacificnet.net on Wed, Aug 15, 2001 at 05:11:28PM -0700
X-Archive-Number: 200108/679
X-Sequence-Number: 14069

On Wed, Aug 15, 2001 at 05:11:28PM -0700, Scott Holmes wrote:

It would seem that my database has unseen garbage in the field being queried.
On further testing I find that

select *
from people
where peopcode LIKE 'AB%AH%'
order by peopcode;

works, however

select *
from people
where peopcode LIKE 'AB%AH'
order by peopcode;

does not. I do have nine records that meet the above criteria and are found
if the pattern ends with '%' but not without it.

here's my guess --

you probably used

create table people (
...
peopcode CHAR(...)
...
);

instead of

peopcode VARCHAR(...)

the "char" type pads with blanks (ascii 32 in latin1 encoding)
to fill the size of the field. "varchar" truncates the data
only if the data exceeds the field size.

so
like "%pat"
will match fields ENDING with "pat" -- for varchar that's
usually at the end of the data; for char, that's at the last few
characters as speficied by the length of the field.

thus, for a field containing
"stuff like this "
a LIKE "%this" would not match, but a LIKE "%this%" would, and so
would LIKE "%this ".

--
Khan said that revenge is a dish best served cold. I think
sometimes it's best served hot, chunky, and foaming.
- P.J.Lee ('79-'80)

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Peter Eisentraut

peter_e@gmx.net

over 24 years ago

In reply to: Andrew Gould (#1)

Re: During dump: function not found

Andrew Gould writes:

Notice: function "pgadmin_get_rows" is not dumped.
Reason: return type name (oid 87589805) not found.
Notice: function "pgadmin_get_sequence" is not dumped.
Reason: return type name (oid 87589772) not found.

Does this simply mean that these functions will not be
available if I restore from the dump file?

Yes.

Will these messages haunt me down the road in other ways?

If something depended on the named functions...

What you did was drop the return type of the function (which used to have
the shown oid). Therefore the function is already useless anyway.

I'm assuming (dangerous, I know) that these functions
were created by PgAdmin rather than being a part of
PostgreSQL. I deleted all tables and views named
pgadmin* and am trying to weed out everything created
by PgAdmin.

Maybe PgAdmin could provide a drop script for that?

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter