Thread: Weird quirk with pg_dump of complex types

All,

This is not so much a bug as a weird inconsistency, which ought to go on
a list somewhere so that it gets cleaned up the next time someone
overhauls pg_dump:

Summary: CREATE TYPE uses explicit schemas
Versions Tested: 8.2.9, 8.3.5
Platform: Linux
Description of Issue:

When doing pg_dump in text mode, complext types will be dumped like this:

CREATE TYPE complex_foo (
    var    INT,
    gar    TEXT,
    natch    public.foo_type
);

That is, a custom type in a complex type declaration is explicitly
schema-qualified, even when the schema in question is in the default
schema_path.  This is inconsistent with all other database objects,
which use "SET search_path" to qualify the correct schemas.

This is only a real problem in that it may interfere with backup and/or
schema comparison automation (like I'm trying to write right now).

--Josh Berkus

On Thu, 2009-02-26 at 15:25 -0800, Josh Berkus wrote:
> That is, a custom type in a complex type declaration is explicitly
> schema-qualified, even when the schema in question is in the default
> schema_path.  This is inconsistent with all other database objects,
> which use "SET search_path" to qualify the correct schemas.
>

Strange. However, I don't think setting the search path will suffice,
because the different types could come from different schemas in a way
that makes it impossible.

Functions are similar, actually. The argument list needs to specify
schema paths as well, if it's not in some expected place (I think it
does so for all schemas other than pg_catalog).

Regards,
    Jeff Davis

Jeff,

> Functions are similar, actually. The argument list needs to specify
> schema paths as well, if it's not in some expected place (I think it
> does so for all schemas other than pg_catalog).

Except that they don't appear to do so.

--Josh

On Thu, 2009-02-26 at 15:52 -0800, Josh Berkus wrote:
> Jeff,
>
> > Functions are similar, actually. The argument list needs to specify
> > schema paths as well, if it's not in some expected place (I think it
> > does so for all schemas other than pg_catalog).
>
> Except that they don't appear to do so.

Here is the case I'm talking about:

postgres=# create schema a;
CREATE SCHEMA
postgres=# create type a.int4 as (i pg_catalog.int4);
CREATE TYPE
postgres=# create function f1(x a.int4, y pg_catalog.int4) returns
pg_catalog.int4 language sql as $$ select 1; $$;
CREATE FUNCTION

-- pg_dump output:

SET search_path = public, pg_catalog;

...

CREATE FUNCTION f1(x a.int4, y integer) RETURNS integer
    LANGUAGE sql
    AS $$ select 1; $$;


So, there are some special cases somewhere so that the pg_dump output
isn't littered with unreadable "pg_catalog.int4" everywhere.

In the general case though, for any object that refers to multiple other
objects, I don't see any way around explicit schema qualification. I
suppose it could be smart and say "foo_type is unique in my search path,
so I don't need to schema-qualify it".

Have you considered working from the "custom" format rather than text?
I'm not sure whether it solves your problem, but I think it provides the
most information.

Regards,
    Jeff Davis

Jeff,

> In the general case though, for any object that refers to multiple other
> objects, I don't see any way around explicit schema qualification. I
> suppose it could be smart and say "foo_type is unique in my search path,
> so I don't need to schema-qualify it".

Yeah, but for most other objects "public" is also excluded as well as
pg_catalog.  For CREATE TYPE, "public" is explicit.

> Have you considered working from the "custom" format rather than text?
> I'm not sure whether it solves your problem, but I think it provides the
> most information.

--Josh Berkus

Josh Berkus <josh@agliodbs.com> writes:
> When doing pg_dump in text mode, complext types will be dumped like this:

> CREATE TYPE complex_foo (
>     var    INT,
>     gar    TEXT,
>     natch    public.foo_type
> );

You didn't say which schema "complex_foo" is in?

> That is, a custom type in a complex type declaration is explicitly
> schema-qualified, even when the schema in question is in the default
> schema_path.

There is no such thing as a default search path for pg_dump's purposes.

            regards, tom lane

On Thu, 2009-02-26 at 21:42 -0800, Josh Berkus wrote:
> Jeff,
>
> > In the general case though, for any object that refers to multiple other
> > objects, I don't see any way around explicit schema qualification. I
> > suppose it could be smart and say "foo_type is unique in my search path,
> > so I don't need to schema-qualify it".
>
> Yeah, but for most other objects "public" is also excluded as well as
> pg_catalog.  For CREATE TYPE, "public" is explicit.
>


Ah, I see what you mean. Here's what I get when the type is named
public.integer (pg_dump output):

CREATE FUNCTION f1(x "integer", y integer) RETURNS integer
    LANGUAGE sql
    AS $$ select 1; $$;

and here's what I get when I try to be creative, and I define a
public.tsvector type (pg_dump output):

CREATE FUNCTION f1(x tsvector, y pg_catalog.tsvector) RETURNS integer
    LANGUAGE sql
    AS $$ select 1; $$;

It seems like pg_dump tries fairly hard to make the output readable in
the typical case. It does seem a little inconsistent that the list of
types that make up another type don't follow the exact same rules; I
don't know the reason for that.

Is using the custom format a possibility?

Regards,
    Jeff Davis

Jeff Davis <pgsql@j-davis.com> writes:
> It seems like pg_dump tries fairly hard to make the output readable in
> the typical case. It does seem a little inconsistent that the list of
> types that make up another type don't follow the exact same rules;

Are you entirely sure that they don't?  Josh didn't provide enough
context to be sure, but I didn't see any clear proof in his example
that the type references were working any differently than they do
elsewhere in pg_dump.

The normal deal in pg_dump is that when dumping any given object,
the search path is set to that object's schema followed by pg_catalog.
So you will get a schema qualification for any reference to an object in
a different schema.  Also, there are certain standard data types that
are spelled in special ways because The SQL Standard Says So.

            regards, tom lane

On Fri, 2009-02-27 at 01:24 -0500, Tom Lane wrote:
> Are you entirely sure that they don't?

Oh, you're right, of course:

postgres=# create type public.mytype as (i int);
CREATE TYPE
postgres=# create type public.mytype2 as (j mytype);
CREATE TYPE

-- pg_dump output:
CREATE TYPE mytype2 AS (
        j mytype
);

Regards,
    Jeff Davis

Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
>> When doing pg_dump in text mode, complext types will be dumped like this:
>
>> CREATE TYPE complex_foo (
>>     var    INT,
>>     gar    TEXT,
>>     natch    public.foo_type
>> );
>
> You didn't say which schema "complex_foo" is in?

Public.

--Josh

Jeff Davis wrote:
> On Fri, 2009-02-27 at 01:24 -0500, Tom Lane wrote:
>> Are you entirely sure that they don't?
>
> Oh, you're right, of course:
>
> postgres=# create type public.mytype as (i int);
> CREATE TYPE
> postgres=# create type public.mytype2 as (j mytype);
> CREATE TYPE
>
> -- pg_dump output:
> CREATE TYPE mytype2 AS (
>         j mytype
> );

Really?

Ok, I'll have to work up a reproduceable case, because I'm definitely
getting the "public" qualification in the create type.

--Josh

Josh Berkus <josh@agliodbs.com> writes:
> Ok, I'll have to work up a reproduceable case, because I'm definitely
> getting the "public" qualification in the create type.

Hmm, that shouldn't happen if the containing type is in "public",
so yeah, let's see the test case.

            regards, tom lane