Thread: BUG #1096: pg_restore cannot restore large objects with other oid columns

The following bug has been logged online:

Bug reference:      1096
Logged by:          Janko Richter

Email address:      jankorichter@yahoo.de

PostgreSQL version: 7.4

Operating system:   Linux

Description:        pg_restore cannot restore large objects with other oid
columns

Details:

I'm using a database with tsearch2 and large objects.
When I restore the dumped  database, pg_restore tries to restore the
functions of tsearch2 as a large object. Some
tsearch functions are stored in tseach2 tables using the oids of the
functions.

It may be better, if pg_restore can skip large objects,
which do not exist. Otherwise, I can't restore the database.

"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> When I restore the dumped  database, pg_restore tries to restore the
> functions of tsearch2 as a large object.

It does what?  I don't think you're describing this accurately.  Please
show the actual problem and not your interpretation of it.

            regards, tom lane

I have found the bug.

Unfortunately, I have a table called public.pg_proc within my database
with the same structure of pg_catalog.pg_proc. I did a test with it and
forgot to drop it.

Within postgresql-7.4.1/src/interfaces/libpq/fe-lobj.c near line 555 is
a query call as

        res = PQexec(conn, "select proname, oid from pg_proc \
                        where proname = 'lo_open'       \
                   or proname = 'lo_close'      \
                   or proname = 'lo_creat'      \
                   or proname = 'lo_unlink'     \
                   or proname = 'lo_lseek'      \
                   or proname = 'lo_tell'       \
                   or proname = 'loread'        \
                   or proname = 'lowrite'");

without a given schema. In my opinion, it must be:

res = PQexec(conn, "select proname, oid from pg_catalog.pg_proc \
 ...

I have changed it in my fe-lobj.c . Now pg_restore works well.

BTW, I have droped the my test table public.pg_proc too ;-)

Regards, Janko


Tom Lane wrote:

>"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
>
>
>>When I restore the dumped  database, pg_restore tries to restore the
>>functions of tsearch2 as a large object.
>>
>>
>
>It does what?  I don't think you're describing this accurately.  Please
>show the actual problem and not your interpretation of it.
>
>            regards, tom lane
>
>
>
--
Janko Richter

Janko Richter <jankorichter@yahoo.de> writes:
> I have found the bug.
> Unfortunately, I have a table called public.pg_proc within my database
> with the same structure of pg_catalog.pg_proc. I did a test with it and
> forgot to drop it.

> Within postgresql-7.4.1/src/interfaces/libpq/fe-lobj.c near line 555 is
> a query call as

Now that's a coincidence.  I just fixed that problem about a week ago
--- it's fixed in 7.4.2 ...

            regards, tom lane

7.4.X CVS has:

    if (conn->sversion >= 70300)
        query = "select proname, oid from pg_catalog.pg_proc "
            "where proname in ("
            "'lo_open', "
            "'lo_close', "
            "'lo_creat', "
            "'lo_unlink', "
            "'lo_lseek', "
            "'lo_tell', "
            "'loread', "
            "'lowrite') "
            "and pronamespace = (select oid from pg_catalog.pg_namespace
"
            "where nspname = 'pg_catalog')";

so I think this fix is in 7.4.2.  Please upgrade.

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

Janko Richter wrote:
> I have found the bug.
>
> Unfortunately, I have a table called public.pg_proc within my database
> with the same structure of pg_catalog.pg_proc. I did a test with it and
> forgot to drop it.
>
> Within postgresql-7.4.1/src/interfaces/libpq/fe-lobj.c near line 555 is
> a query call as
>
>         res = PQexec(conn, "select proname, oid from pg_proc \
>                         where proname = 'lo_open'       \
>                    or proname = 'lo_close'      \
>                    or proname = 'lo_creat'      \
>                    or proname = 'lo_unlink'     \
>                    or proname = 'lo_lseek'      \
>                    or proname = 'lo_tell'       \
>                    or proname = 'loread'        \
>                    or proname = 'lowrite'");
>
> without a given schema. In my opinion, it must be:
>
> res = PQexec(conn, "select proname, oid from pg_catalog.pg_proc \
>  ...
>
> I have changed it in my fe-lobj.c . Now pg_restore works well.
>
> BTW, I have droped the my test table public.pg_proc too ;-)
>
> Regards, Janko
>
>
> Tom Lane wrote:
>
> >"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> >
> >
> >>When I restore the dumped  database, pg_restore tries to restore the
> >>functions of tsearch2 as a large object.
> >>
> >>
> >
> >It does what?  I don't think you're describing this accurately.  Please
> >show the actual problem and not your interpretation of it.
> >
> >            regards, tom lane
> >
> >
> >
> --
> Janko Richter
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073