Thread: pg_restore cannot restore index

pg_restore cannot restore index

From

Jie Liang

Date:

09 July 2002, 15:45:12

Another possible bug:
pg_restore -i "\"indexname\"" -d mydb mydumpfile
msg:
pg_restore: connecting to database for restore
pg_restore: creating FUNCTION "plpgsql_call_handler" ()
pg_restore: [archiver (db)] could not execute query: ERROR:  function
plpgsql_call_handler already exists with same argument types
pg_restore: *** aborted because of error

I read the pg_restore.c source code, I found:
#ifdef HAVE_GETOPT_LONG       struct option cmdopts[] = {               {"clean", 0, NULL, 'c'},
{"create",0, NULL, 'C'},               {"data-only", 0, NULL, 'a'},               {"dbname", 1, NULL, 'd'},
 {"file", 1, NULL, 'f'},               {"format", 1, NULL, 'F'},               {"function", 1, NULL, 'P'},
{"host", 1, NULL, 'h'},               {"ignore-version", 0, NULL, 'i'},               {"index", 1, NULL, 'I'},
 
So, -i may be mapped wrong, however, -I is illegal option.

Thanks!


Jie Liang



-----Original Message-----
From: Jie Liang [mailto:jie@stbernard.com]
Sent: Wednesday, July 03, 2002 12:03 PM
To: 'Jan Wieck'; Jie Liang
Cc: 'Bruce Momjian'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.org'
Subject: Re: [SQL] pg_restore cannot restore function



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 #



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: pg_restore cannot restore index

From

Jie Liang

Date:

09 July 2002, 17:01:08



Another possible bug:
pg_restore -i "\"indexname\"" -d mydb mydumpfile
msg:
pg_restore: connecting to database for restore
pg_restore: creating FUNCTION "plpgsql_call_handler" ()
pg_restore: [archiver (db)] could not execute query: ERROR:  function
plpgsql_call_handler already exists with same argument types
pg_restore: *** aborted because of error

I read the pg_restore.c source code, I found:
#ifdef HAVE_GETOPT_LONG       struct option cmdopts[] = {               {"clean", 0, NULL, 'c'},
{"create",0, NULL, 'C'},               {"data-only", 0, NULL, 'a'},               {"dbname", 1, NULL, 'd'},
 {"file", 1, NULL, 'f'},               {"format", 1, NULL, 'F'},               {"function", 1, NULL, 'P'},
{"host", 1, NULL, 'h'},               {"ignore-version", 0, NULL, 'i'},               {"index", 1, NULL, 'I'},
 
So, -i may be mapped wrong, however, -I is illegal option.

Thanks!


Jie Liang

Re: pg_restore cannot restore index

From

Bruce Momjian

Date:

09 July 2002, 22:59:00

Yep, documentation is wrong.  Documentation patch attached and applied.
Also, in 7.3 you will not need the weird quoting for objects.

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

Jie Liang wrote:
> Another possible bug:
> pg_restore -i "\"indexname\"" -d mydb mydumpfile
> msg:
> pg_restore: connecting to database for restore
> pg_restore: creating FUNCTION "plpgsql_call_handler" ()
> pg_restore: [archiver (db)] could not execute query: ERROR:  function
> plpgsql_call_handler already exists with same argument types
> pg_restore: *** aborted because of error
>
> I read the pg_restore.c source code, I found:
> #ifdef HAVE_GETOPT_LONG
>         struct option cmdopts[] = {
>                 {"clean", 0, NULL, 'c'},
>                 {"create", 0, NULL, 'C'},
>                 {"data-only", 0, NULL, 'a'},
>                 {"dbname", 1, NULL, 'd'},
>                 {"file", 1, NULL, 'f'},
>                 {"format", 1, NULL, 'F'},
>                 {"function", 1, NULL, 'P'},
>                 {"host", 1, NULL, 'h'},
>                 {"ignore-version", 0, NULL, 'i'},
>                 {"index", 1, NULL, 'I'},
> So, -i may be mapped wrong, however, -I is illegal option.
>
> Thanks!
>
>
> Jie Liang
>
>
>
> -----Original Message-----
> From: Jie Liang [mailto:jie@stbernard.com]
> Sent: Wednesday, July 03, 2002 12:03 PM
> To: 'Jan Wieck'; Jie Liang
> Cc: 'Bruce Momjian'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.org'
> Subject: Re: [SQL] pg_restore cannot restore function
>
>
>
> 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 #
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>

--
  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
Index: pg_restore.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/pg_restore.sgml,v
retrieving revision 1.26
diff -c -r1.26 pg_restore.sgml
*** pg_restore.sgml    4 Jul 2002 03:04:54 -0000    1.26
--- pg_restore.sgml    10 Jul 2002 02:57:12 -0000
***************
*** 28,34 ****
     <arg> -d <replaceable class="parameter">dbname</replaceable> </arg>
     <arg> -f <replaceable class="parameter">output-file</replaceable> </arg>
     <arg> -F <replaceable class="parameter">format</replaceable> </arg>
!    <arg> -i  <replaceable class="parameter">index</replaceable> </arg>
     <arg> -l </arg>
     <arg> -L <replaceable class="parameter">contents-file</replaceable> </arg>
     <group> <arg> -N </arg> <arg> -o </arg> <arg> -r </arg> </group>
--- 28,35 ----
     <arg> -d <replaceable class="parameter">dbname</replaceable> </arg>
     <arg> -f <replaceable class="parameter">output-file</replaceable> </arg>
     <arg> -F <replaceable class="parameter">format</replaceable> </arg>
!    <arg> -i </arg>
!    <arg> -I  <replaceable class="parameter">index</replaceable> </arg>
     <arg> -l </arg>
     <arg> -L <replaceable class="parameter">contents-file</replaceable> </arg>
     <group> <arg> -N </arg> <arg> -o </arg> <arg> -r </arg> </group>
***************
*** 210,220 ****
       </varlistentry>

       <varlistentry>
!       <term><option>-i <replaceable class="parameter">index</replaceable></option></term>
!       <term><option>--index=<replaceable class="parameter">index</replaceable></option></term>
        <listitem>
         <para>
!     Restore definition for named <replaceable class="parameter">index</replaceable> only.
         </para>
        </listitem>
       </varlistentry>
--- 211,221 ----
       </varlistentry>

       <varlistentry>
!       <term><option>-i </term>
!       <term><option>--ignore-version</term>
        <listitem>
         <para>
!     Ignore database version checks.
         </para>
        </listitem>
       </varlistentry>

Re: pg_restore cannot restore index

From

Jie Liang

Date:

10 July 2002, 11:42:39

Thanks!
But I did not make long form works also, is it:
pg_restore --index="\"indexname\"" --dbname=mydb mydumpfile
???????????
msg:
pg_restore:[archiver] could open input file: No such file or directory


could you give out a example of long form????


Thanks again.


Jie Liang

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: Tuesday, July 09, 2002 7:59 PM
To: Jie Liang
Cc: 'Jan Wieck'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.org'
Subject: Re: pg_restore cannot restore index



Yep, documentation is wrong.  Documentation patch attached and applied. 
Also, in 7.3 you will not need the weird quoting for objects.

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

Jie Liang wrote:
> Another possible bug:
> pg_restore -i "\"indexname\"" -d mydb mydumpfile
> msg:
> pg_restore: connecting to database for restore
> pg_restore: creating FUNCTION "plpgsql_call_handler" ()
> pg_restore: [archiver (db)] could not execute query: ERROR:  function
> plpgsql_call_handler already exists with same argument types
> pg_restore: *** aborted because of error
> 
> I read the pg_restore.c source code, I found:
> #ifdef HAVE_GETOPT_LONG
>         struct option cmdopts[] = {
>                 {"clean", 0, NULL, 'c'},
>                 {"create", 0, NULL, 'C'},
>                 {"data-only", 0, NULL, 'a'},
>                 {"dbname", 1, NULL, 'd'},
>                 {"file", 1, NULL, 'f'},
>                 {"format", 1, NULL, 'F'},
>                 {"function", 1, NULL, 'P'},
>                 {"host", 1, NULL, 'h'},
>                 {"ignore-version", 0, NULL, 'i'},
>                 {"index", 1, NULL, 'I'},
> So, -i may be mapped wrong, however, -I is illegal option.
> 
> Thanks!
> 
> 
> Jie Liang
> 
> 
> 
> -----Original Message-----
> From: Jie Liang [mailto:jie@stbernard.com]
> Sent: Wednesday, July 03, 2002 12:03 PM
> To: 'Jan Wieck'; Jie Liang
> Cc: 'Bruce Momjian'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.org'
> Subject: Re: [SQL] pg_restore cannot restore function
> 
> 
> 
> 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 #
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 
> 

--  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,
Pennsylvania19026

Re: pg_restore cannot restore index

From

Bruce Momjian

Date:

10 July 2002, 22:06:06

Jie Liang wrote:
> Thanks!
> But I did not make long form works also, is it:
> pg_restore --index="\"indexname\"" --dbname=mydb mydumpfile
> ???????????
> msg:
> pg_restore:[archiver] could open input file: No such file or directory

Strange.  I found a few more problems with the getopt values in
pg_restore.c not matching the 'case' statement or the documentation.

I got it working here with my patched version using:
pg_restore -I aa -d test /bjm/x

It can't find the file?  I didn't fix anything in that area.  I am
confused how that could be messed up.  I don't see any other meaningful
changes to pg_restore except the quote fixes I did.  Are you sure the
file is correct?

Thanks for pointing these things out.  It is a big help.  Now I am
starting to wonder what else is wrong in the code.  :-)


> 
> 
> could you give out a example of long form????
> 
> 
> Thanks again.
> 
> 
> Jie Liang
> 
> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> Sent: Tuesday, July 09, 2002 7:59 PM
> To: Jie Liang
> Cc: 'Jan Wieck'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.org'
> Subject: Re: pg_restore cannot restore index
> 
> 
> 
> Yep, documentation is wrong.  Documentation patch attached and applied. 
> Also, in 7.3 you will not need the weird quoting for objects.
> 
> ---------------------------------------------------------------------------
> 
> Jie Liang wrote:
> > Another possible bug:
> > pg_restore -i "\"indexname\"" -d mydb mydumpfile
> > msg:
> > pg_restore: connecting to database for restore
> > pg_restore: creating FUNCTION "plpgsql_call_handler" ()
> > pg_restore: [archiver (db)] could not execute query: ERROR:  function
> > plpgsql_call_handler already exists with same argument types
> > pg_restore: *** aborted because of error
> > 
> > I read the pg_restore.c source code, I found:
> > #ifdef HAVE_GETOPT_LONG
> >         struct option cmdopts[] = {
> >                 {"clean", 0, NULL, 'c'},
> >                 {"create", 0, NULL, 'C'},
> >                 {"data-only", 0, NULL, 'a'},
> >                 {"dbname", 1, NULL, 'd'},
> >                 {"file", 1, NULL, 'f'},
> >                 {"format", 1, NULL, 'F'},
> >                 {"function", 1, NULL, 'P'},
> >                 {"host", 1, NULL, 'h'},
> >                 {"ignore-version", 0, NULL, 'i'},
> >                 {"index", 1, NULL, 'I'},
> > So, -i may be mapped wrong, however, -I is illegal option.
> > 
> > Thanks!
> > 
> > 
> > Jie Liang
> > 
> > 
> > 
> > -----Original Message-----
> > From: Jie Liang [mailto:jie@stbernard.com]
> > Sent: Wednesday, July 03, 2002 12:03 PM
> > To: 'Jan Wieck'; Jie Liang
> > Cc: 'Bruce Momjian'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.org'
> > Subject: Re: [SQL] pg_restore cannot restore function
> > 
> > 
> > 
> > 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 #
> > 
> > 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> > 
> > http://archives.postgresql.org
> > 
> > 
> 
> -- 
>   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                        |  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,
Pennsylvania19026

Re: pg_restore cannot restore index

From

Jie Liang

Date:

11 July 2002, 11:40:59

On this point, I'd like to ask:
1. where I can download this new version?
2. does
pg_restore --index=aa --dbname=test /bjm/x
works also???

Because
pg_restore --table=mytable --dbname=mydb mydumpfile
doesn't work!

I got same error msg.

Jie Liang


-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: Wednesday, July 10, 2002 7:06 PM
To: Jie Liang
Cc: 'Jan Wieck'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.org'
Subject: Re: pg_restore cannot restore index


Jie Liang wrote:
> Thanks!
> But I did not make long form works also, is it:
> pg_restore --index="\"indexname\"" --dbname=mydb mydumpfile
> ???????????
> msg:
> pg_restore:[archiver] could open input file: No such file or directory

Strange.  I found a few more problems with the getopt values in
pg_restore.c not matching the 'case' statement or the documentation.

I got it working here with my patched version using:
pg_restore -I aa -d test /bjm/x

It can't find the file?  I didn't fix anything in that area.  I am
confused how that could be messed up.  I don't see any other meaningful
changes to pg_restore except the quote fixes I did.  Are you sure the
file is correct?

Thanks for pointing these things out.  It is a big help.  Now I am
starting to wonder what else is wrong in the code.  :-)


> 
> 
> could you give out a example of long form????
> 
> 
> Thanks again.
> 
> 
> Jie Liang
> 
> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> Sent: Tuesday, July 09, 2002 7:59 PM
> To: Jie Liang
> Cc: 'Jan Wieck'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.org'
> Subject: Re: pg_restore cannot restore index
> 
> 
> 
> Yep, documentation is wrong.  Documentation patch attached and applied. 
> Also, in 7.3 you will not need the weird quoting for objects.
> 
>
---------------------------------------------------------------------------
> 
> Jie Liang wrote:
> > Another possible bug:
> > pg_restore -i "\"indexname\"" -d mydb mydumpfile
> > msg:
> > pg_restore: connecting to database for restore
> > pg_restore: creating FUNCTION "plpgsql_call_handler" ()
> > pg_restore: [archiver (db)] could not execute query: ERROR:  function
> > plpgsql_call_handler already exists with same argument types
> > pg_restore: *** aborted because of error
> > 
> > I read the pg_restore.c source code, I found:
> > #ifdef HAVE_GETOPT_LONG
> >         struct option cmdopts[] = {
> >                 {"clean", 0, NULL, 'c'},
> >                 {"create", 0, NULL, 'C'},
> >                 {"data-only", 0, NULL, 'a'},
> >                 {"dbname", 1, NULL, 'd'},
> >                 {"file", 1, NULL, 'f'},
> >                 {"format", 1, NULL, 'F'},
> >                 {"function", 1, NULL, 'P'},
> >                 {"host", 1, NULL, 'h'},
> >                 {"ignore-version", 0, NULL, 'i'},
> >                 {"index", 1, NULL, 'I'},
> > So, -i may be mapped wrong, however, -I is illegal option.
> > 
> > Thanks!
> > 
> > 
> > Jie Liang
> > 
> > 
> > 
> > -----Original Message-----
> > From: Jie Liang [mailto:jie@stbernard.com]
> > Sent: Wednesday, July 03, 2002 12:03 PM
> > To: 'Jan Wieck'; Jie Liang
> > Cc: 'Bruce Momjian'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.org'
> > Subject: Re: [SQL] pg_restore cannot restore function
> > 
> > 
> > 
> > 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 #
> > 
> > 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> > 
> > http://archives.postgresql.org
> > 
> > 
> 
> -- 
>   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                        |  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,
Pennsylvania19026

Re: pg_restore cannot restore index

From

Jie Liang

Date:

11 July 2002, 15:48:10

What parameter I should change in order to make postmaster taking CPU as
much as
possible?
Maybe I should ask: how can I make big tables equijoin faster?
I have a serveral tables that contain more 2.5 million records, I need to
equijoin
those tables often.

Thanks!




Jie Liang

Re: pg_restore cannot restore index

From

Bruce Momjian

Date:

13 July 2002, 10:51:03

Jie Liang wrote:
> On this point, I'd like to ask:
> 1. where I can download this new version?
> 2. does
> pg_restore --index=aa --dbname=test /bjm/x
> works also???

OK, the attached patch should allow -I to work in 7.2.X.  This will all
be fixed in 7.3.

> Because
> pg_restore --table=mytable --dbname=mydb mydumpfile
> doesn't work!

Is this a different problem?  --table doesn't work either?

--
  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
*** ./pg_restore.c.orig    Sat Jul 13 10:45:43 2002
--- ./pg_restore.c    Sat Jul 13 10:45:55 2002
***************
*** 163,171 ****
      }

  #ifdef HAVE_GETOPT_LONG
!     while ((c = getopt_long(argc, argv, "acCd:f:F:h:i:lL:NoOp:P:rRsS:t:T:uU:vWxX:", cmdopts, NULL)) != -1)
  #else
!     while ((c = getopt(argc, argv, "acCd:f:F:h:i:lL:NoOp:P:rRsS:t:T:uU:vWxX:")) != -1)
  #endif
      {
          switch (c)
--- 163,171 ----
      }

  #ifdef HAVE_GETOPT_LONG
!     while ((c = getopt_long(argc, argv, "acCd:f:F:h:iI:lL:NoOp:P:rRsS:t:T:uU:vWxX:", cmdopts, NULL)) != -1)
  #else
!     while ((c = getopt(argc, argv, "acCd:f:F:h:iI:lL:NoOp:P:rRsS:t:T:uU:vWxX:")) != -1)
  #endif
      {
          switch (c)

Re: pg_restore cannot restore index

From

Jie Liang

Date:

15 July 2002, 10:46:42

Same problem, did you test:
pg_restore --index=aa --dbname=test /bjm/x
??????
I didn't make it work, I may miss someting.

Thanks!


Jie Liang



-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: Saturday, July 13, 2002 7:51 AM
To: Jie Liang
Cc: 'Jan Wieck'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.org'
Subject: Re: pg_restore cannot restore index


Jie Liang wrote:
> On this point, I'd like to ask:
> 1. where I can download this new version?
> 2. does
> pg_restore --index=aa --dbname=test /bjm/x
> works also???

OK, the attached patch should allow -I to work in 7.2.X.  This will all
be fixed in 7.3. 
> Because
> pg_restore --table=mytable --dbname=mydb mydumpfile
> doesn't work!

Is this a different problem?  --table doesn't work either?

--  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,
Pennsylvania19026

Re: pg_restore cannot restore index

From

Bruce Momjian

Date:

15 July 2002, 18:23:52

I just ran some tests in 7.2.1 and 7.3 and both worked fine.  Can I see
the exact error it generates?

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

Jie Liang wrote:
> Same problem, did you test:
> pg_restore --index=aa --dbname=test /bjm/x
> ??????
> I didn't make it work, I may miss someting.
> 
> Thanks!
> 
> 
> Jie Liang
> 
> 
> 
> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> Sent: Saturday, July 13, 2002 7:51 AM
> To: Jie Liang
> Cc: 'Jan Wieck'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.org'
> Subject: Re: pg_restore cannot restore index
> 
> 
> Jie Liang wrote:
> > On this point, I'd like to ask:
> > 1. where I can download this new version?
> > 2. does
> > pg_restore --index=aa --dbname=test /bjm/x
> > works also???
> 
> OK, the attached patch should allow -I to work in 7.2.X.  This will all
> be fixed in 7.3.
>   
> > Because
> > pg_restore --table=mytable --dbname=mydb mydumpfile
> > doesn't work!
> 
> Is this a different problem?  --table doesn't work either?
> 
> -- 
>   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                        |  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,
Pennsylvania19026

Re: pg_restore cannot restore index

From

Jie Liang

Date:

15 July 2002, 19:22:55

su postgres -c "/usr/local/pgsql/bin/pg_restore --table=mytable
--dbname=mydb mydumpfile"
error msg
pg_restore: [archiver] could not open input file: No such file or directory

I run it on 7.2.0



Jie Liang

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: Monday, July 15, 2002 3:24 PM
To: Jie Liang
Cc: 'Jan Wieck'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.org'
Subject: Re: pg_restore cannot restore index



I just ran some tests in 7.2.1 and 7.3 and both worked fine.  Can I see
the exact error it generates?

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

Jie Liang wrote:
> Same problem, did you test:
> pg_restore --index=aa --dbname=test /bjm/x
> ??????
> I didn't make it work, I may miss someting.
> 
> Thanks!
> 
> 
> Jie Liang
> 
> 
> 
> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> Sent: Saturday, July 13, 2002 7:51 AM
> To: Jie Liang
> Cc: 'Jan Wieck'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.org'
> Subject: Re: pg_restore cannot restore index
> 
> 
> Jie Liang wrote:
> > On this point, I'd like to ask:
> > 1. where I can download this new version?
> > 2. does
> > pg_restore --index=aa --dbname=test /bjm/x
> > works also???
> 
> OK, the attached patch should allow -I to work in 7.2.X.  This will all
> be fixed in 7.3.
>   
> > Because
> > pg_restore --table=mytable --dbname=mydb mydumpfile
> > doesn't work!
> 
> Is this a different problem?  --table doesn't work either?
> 
> -- 
>   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                        |  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,
Pennsylvania19026

Re: pg_restore cannot restore index

From

Bruce Momjian

Date:

15 July 2002, 19:24:43

Jie Liang wrote:
> su postgres -c "/usr/local/pgsql/bin/pg_restore --table=mytable
> --dbname=mydb mydumpfile"
> error msg
> pg_restore: [archiver] could not open input file: No such file or directory
> 
> I run it on 7.2.0

OK, my guess is that the 'su' is moving you to another directory.  Try
specifying the full path of the file, e.g. /var/tmp/mydumpfile.

--  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,
Pennsylvania19026