Re: pg_restore cannot restore function

OK, I have researched your problem, and we had several bugs in this
area. First, we quoted the function name in the storage tag, which
wasn't necessary and was inconsistent with other objects. Second, we
quoted all storage tags for GRANT/REVOKE entries, so restoring a table
without quotes would restore the table without the GRANT/REVOKE part.
And third, the documentation wasn't clear.

The following patch fixes all of these, and it will be in 7.3. The
patch probably will not work in 7.2.1 because we have added schemas to
the code, but you can probably manually apply it if needed.

Thanks for the report.

---------------------------------------------------------------------------

Jie Liang wrote:

OK, we figured it out.
The problem is the documentation confused me!!!
In man page of pg_restore:
-P function-name
--function=function name
Specify a procedure or function to be restored.

User will assume that syntax of restoring a function is same as
restoring a table, but it's not true, it's slightly different.
To restore a table:
pg_restore -Rxt mytable -d mydb2 dbf
works, but to restore a function:
pg_restore -P myfunction -d mydb2 dbf
won't work, and you need to use:
pg_restore -P "\"myfunction\" (args and type)" -d mydb2 dbf
to make it work!!!!!

I believe that the man page of pg_restore should be improved.

Thanks.

Jie Liang

-----Original Message-----
From: Jan Wieck [mailto:JanWieck@Yahoo.com]
Sent: Monday, July 01, 2002 11:14 AM
To: Jie Liang
Cc: 'Bruce Momjian'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.org'
Subject: Re: [SQL] pg_restore cannot restore function

Jie Liang wrote:

Oops,my OS is FreeBSD4.3 PostgreSQL7.2

I cannot see such an error message in the pg_restore sources at all. Are
you sure to use the right versions together?

Jan

Thanks

Jie Liang

-----Original Message-----
From: Jie Liang
Sent: Friday, June 28, 2002 1:46 PM
To: 'Jan Wieck'
Cc: 'Bruce Momjian'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.org'
Subject: RE: [SQL] pg_restore cannot restore function

No any error msg in the logfile, I didn't see any create function

statement

in my logfile which I enabled the query log.
This function is written in PL/pgSQL which is enabled in target db,
If I pg_dump the schema into a plain text file, I can see its defination
there, I can easily copy & paste (restore) it into mydb2.
however, I failed to restore it by using flag -P with compressed file.
I also tried to use
su postgres -c "/usr/local/pgsql/bin/pg_restore --function=myfunction
--dbname=mydb2 dbf"
error msg
pg_restore: [archiver] could not open input file: No such file or

directory

weird???

I use
pg_restore -Rxt mytable -d mydb2 dbf
have no such a problem, it works.

Is any syntax error??
I am confused by documentation now!
Is it a bug????

Thanks

Jie Liang

-----Original Message-----
From: Jan Wieck [mailto:JanWieck@Yahoo.com]
Sent: Friday, June 28, 2002 12:39 PM
To: Jie Liang
Cc: 'Bruce Momjian'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.org'
Subject: Re: [SQL] pg_restore cannot restore function

Jie Liang wrote:

I use
pg_dump -Fc mydb > dbf
then I create another db by:
createdb mydb2
I use
pg_restore -P myfunction -d mydb2 dbf

cannot restore myfunction into mydb2

why??????

Good question. Is there any error message in the postmaster log?

If the function is written in a procedural language, is that language
enabled in the target database? If the function is written in the SQL
language, do all underlying objects like tables and views exist? If it's
a C language function, does the shared object containing the function
exist at the expected location?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  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

Bruce Momjian <pgman@candle.pha.pa.us> writes:

The following patch fixes all of these, and it will be in 7.3.

It will? Kindly explain what the heck this is doing. It looks to
me like it will probably break more cases than it fixes. AFAICS
it's just completely muddying the waters about what is quoted and
what isn't ...

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

The following patch fixes all of these, and it will be in 7.3.

It will? Kindly explain what the heck this is doing. It looks to
me like it will probably break more cases than it fixes. AFAICS
it's just completely muddying the waters about what is quoted and
what isn't ...

Well, the waters were already muddy. Doing a pg_dump -Ft, you will see
with the old code how the tags for functions had quotes, and how the
GRANT/REVOKE has quotes for table names, while the CREATE TABLE tags for
these do not. This fixes that, and tries to get the -P string they
enter for pg_restore to match the tag by removing spaces.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  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