Received: from candle.pha.pa.us (216-55-132-35.dsl.san-diego.abac.net [216.55.132.35]) by postgresql.org (Postfix) with ESMTP id 35837474E49; Tue, 9 Jul 2002 22:58:58 -0400 (EDT) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.10.1) id g6A2wn618602; Tue, 9 Jul 2002 22:58:49 -0400 (EDT) From: Bruce Momjian Message-Id: <200207100258.g6A2wn618602@candle.pha.pa.us> Subject: Re: pg_restore cannot restore index In-Reply-To: <7C760DAA511DC74B99E7D22189F786F101BF2114@MAIL01.stbernard.com> To: Jie Liang Date: Tue, 9 Jul 2002 22:58:49 -0400 (EDT) Cc: "'Jan Wieck'" , "'admin@postgresql.org'" , "'pgsql-sql@postgresql.org'" X-Mailer: ELM [version 2.4ME+ PL99 (25)] MIME-Version: 1.0 Content-Type: multipart/mixed; boundary=ELM1026269929-10863-0_ Content-Transfer-Encoding: 7bit X-Archive-Number: 200207/125 X-Sequence-Number: 8504 --ELM1026269929-10863-0_ Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII 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 --ELM1026269929-10863-0_ Content-Transfer-Encoding: 7bit Content-Type: text/plain Content-Disposition: inline; filename="/bjm/diff" 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 **** -d dbname -f output-file -F format ! -i index -l -L contents-file -N -o -r --- 28,35 ---- -d dbname -f output-file -F format ! -i ! -I index -l -L contents-file -N -o -r *************** *** 210,220 **** ! ! ! Restore definition for named index only. --- 211,221 ---- ! ! ! Ignore database version checks. --ELM1026269929-10863-0_--