Обсуждение: pg_dump, problem with user defined types?

Hi All,

I can't confirm this against the current latest CVS as my
local tree has undergone some changes, but we seem to have
a problem with "pg_dump" and user defined types.

If I create a type like so:-

CREATE FUNCTION sotime_in (opaque ) RETURNS opaque AS
'/usr/local/pgsql/shlibs/obj/sotimes.so' LANGUAGE 'C';

CREATE FUNCTION sotime_out (opaque ) RETURNS opaque AS
'/usr/local/pgsql/shlibs/obj/sotimes.so' LANGUAGE 'C';

CREATE TYPE sotime ( internallength = 2, externallength = 5,
input = sotime_in, output = sotime_out, send = sotime_out,
receive = sotime_in, default = '-',passedbyvalue);

pg_dump gives me back :-

CREATE TYPE sotime ( internallength = 2, externallength = 5,
input = sotime_in_18272, output = sotime_out_18304, send = sotime_out_18304,
receive = sotime_in_18272, default = '-',passedbyvalue);

CREATE TYPE _sotime ( internallength = -1, externallength = -1,
input = array_in_750, output = array_out_751, send = array_out_751,
receive = array_in_750, default = '-');
.
<table creates>
.
CREATE FUNCTION sotime_in (opaque ) RETURNS opaque AS
'/usr/local/pgsql/shlibs/obj/sotimes.so' LANGUAGE 'C';
CREATE FUNCTION sotime_out (opaque ) RETURNS opaque AS
'/usr/local/pgsql/shlibs/obj/sotimes.so' LANGUAGE 'C';


Looks like the FUNCTION creates are OK but the TYPE creation
is a little confused.

I remember some discussion about name mangling for the functions
but it looks like pg_dump can't cope with this.

Another thing I can't understand is pg_dump dumping the _sotime
type, which seems to be created automatically when I create
the sotime type.

Needless to say, I can't dump/restore a database with user defined
types at all.

Keith.

BTW: I was looking as pg_dump'ing VIEWS.

> Hi All,
>
> I can't confirm this against the current latest CVS as my
> local tree has undergone some changes, but we seem to have
> a problem with "pg_dump" and user defined types.
>
> If I create a type like so:-
>
> CREATE FUNCTION sotime_in (opaque ) RETURNS opaque AS
> '/usr/local/pgsql/shlibs/obj/sotimes.so' LANGUAGE 'C';
>
> CREATE FUNCTION sotime_out (opaque ) RETURNS opaque AS
> '/usr/local/pgsql/shlibs/obj/sotimes.so' LANGUAGE 'C';
>
> CREATE TYPE sotime ( internallength = 2, externallength = 5,
> input = sotime_in, output = sotime_out, send = sotime_out,
> receive = sotime_in, default = '-',passedbyvalue);
>
> pg_dump gives me back :-
>
> CREATE TYPE sotime ( internallength = 2, externallength = 5,
> input = sotime_in_18272, output = sotime_out_18304, send = sotime_out_18304,
> receive = sotime_in_18272, default = '-',passedbyvalue);
>
> CREATE TYPE _sotime ( internallength = -1, externallength = -1,
> input = array_in_750, output = array_out_751, send = array_out_751,
> receive = array_in_750, default = '-');
> .
> <table creates>
> .
> CREATE FUNCTION sotime_in (opaque ) RETURNS opaque AS
> '/usr/local/pgsql/shlibs/obj/sotimes.so' LANGUAGE 'C';
> CREATE FUNCTION sotime_out (opaque ) RETURNS opaque AS
> '/usr/local/pgsql/shlibs/obj/sotimes.so' LANGUAGE 'C';
>
>
> Looks like the FUNCTION creates are OK but the TYPE creation
> is a little confused.
>
> I remember some discussion about name mangling for the functions
> but it looks like pg_dump can't cope with this.
>
> Another thing I can't understand is pg_dump dumping the _sotime
> type, which seems to be created automatically when I create
> the sotime type.
>
> Needless to say, I can't dump/restore a database with user defined
> types at all.

Yes, we have a problem with user-defined types because of the changes I
made to make the function name unique by adding the pg_proc oid to the
name.

I can make the fix, but no one seems to have an opinion on how yet.  I
can e-mail the pertinent postings if you want to discuss solutions.

--
  Bruce Momjian                        |  maillist@candle.pha.pa.us
  830 Blythe Avenue                    |  http://www.op.net/~candle
  Drexel Hill, Pennsylvania 19026      |  (610) 353-9879(w)
  +  If your life is a hard drive,     |  (610) 853-3000(h)
  +  Christ can be your backup.        |

> > pg_dump gives me back :-
> > CREATE TYPE sotime ( internallength = 2, externallength = 5,
> > input = sotime_in_18272, output = sotime_out_18304, send = sotime_out_18304,
> > receive = sotime_in_18272, default = '-',passedbyvalue);
> > CREATE TYPE _sotime ( internallength = -1, externallength = -1,
> > input = array_in_750, output = array_out_751, send = array_out_751,
> > receive = array_in_750, default = '-');
> Yes, we have a problem with user-defined types because of the changes
> I made to make the function name unique by adding the pg_proc oid to
> the name.
> I can make the fix, but no one seems to have an opinion on how yet.

It would seem that for Keith's cases things worked OK originally? There
was trouble with other overloaded functions?

At worst, we should revert to the non-mangled names. On a related note,
has anyone had time to try testing pg_upgrade? That would be a nice
feature for v6.4 if it worked on a wide range of databases (haven't
tried it myself yet).

                    - Tom