BUG #14701: pg_dump fails to dump pg_catalog schema

neil@postgrescompare.com writes:

Just installed the beta of v10 on my Mac and attempted to dump the contents
of pg_catalog as follows.

./bin/pg_dump -U postgres -n pg_catalog
pg_dump: unrecognized collation provider: d

The reason for that is that the "default" collation has collprovider =
'd', which pg_dump doesn't know what to do with, and I'm not sure it
could do anything sensible with given that CREATE COLLATION doesn't
accept anything corresponding to that. OTOH, I'm not real sure what
is the point of the 'd' value, because I can find no code in the backend
that deals with COLLPROVIDER_DEFAULT; it seems at best rather accidental
that the entry works at all. We could get rid of the 'd' and have
some hack to make initdb insert the appropriate 'i' or 'c' value
based on USE_ICU, perhaps. Alternatively, I think that CREATE COLLATION
ought to grow the ability to accept "provider = default" and pg_dump
should use that. Peter?

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

I wrote:

OTOH, I'm not real sure what
is the point of the 'd' value, because I can find no code in the backend
that deals with COLLPROVIDER_DEFAULT; it seems at best rather accidental
that the entry works at all.

Actually, it works only for finite values of "work". You can break it
by cloning the "default" entry:

regression=# create collation foo from "default";
WARNING: could not determine encoding for locale "": codeset is "ANSI_X3.4-1968"
WARNING: could not determine encoding for locale "": codeset is "ANSI_X3.4-1968"
CREATE COLLATION
regression=# select * from pg_collation where collname = 'foo';
collname | collnamespace | collowner | collprovider | collencoding | collcollate | collctype | collversion
----------+---------------+-----------+--------------+--------------+-------------+-----------+-------------
foo | 2200 | 10 | d | 0 | | |
(1 row)
regression=# select * from text_tbl order by f1 collate foo;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

The crash is from a SIGSEGV that I didn't bother to identify the
exact location of. (Note: this is in a non-ICU-enabled build,
prevailing locale "C".)

It wouldn't be unreasonable to disallow cloning "default",
perhaps, but I'd be inclined to want to plug the crash
problem more directly than that.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On 6/11/17 20:19, Tom Lane wrote:

neil@postgrescompare.com writes:

Just installed the beta of v10 on my Mac and attempted to dump the contents
of pg_catalog as follows.

./bin/pg_dump -U postgres -n pg_catalog
pg_dump: unrecognized collation provider: d

The reason for that is that the "default" collation has collprovider =
'd', which pg_dump doesn't know what to do with, and I'm not sure it
could do anything sensible with given that CREATE COLLATION doesn't
accept anything corresponding to that. OTOH, I'm not real sure what
is the point of the 'd' value, because I can find no code in the backend
that deals with COLLPROVIDER_DEFAULT; it seems at best rather accidental
that the entry works at all. We could get rid of the 'd' and have
some hack to make initdb insert the appropriate 'i' or 'c' value
based on USE_ICU, perhaps.

Conceptually, the locale code works like this:

if provider == d
strcoll()
else if provider == c
strcoll_l()
else if provider == i
ucol_strcoll()
[else
apparently dubious here ...]

But actually the code checks for DEFAULT_COLLATION_OID instead of
provider == 'd', so having another collation with provider 'd' isn't
going to do anything useful.

Maybe it would actually be vaguely useful to change that around, now
that I think about it.

Alternatively, I think that CREATE COLLATION
ought to grow the ability to accept "provider = default" and pg_dump
should use that.

We could probably do that at least in pg_dump. What are the
expectations for pg_catalog schema dumps? Are they supposed to be
restorable?

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:

On 6/11/17 20:19, Tom Lane wrote:

Alternatively, I think that CREATE COLLATION
ought to grow the ability to accept "provider = default" and pg_dump
should use that.

We could probably do that at least in pg_dump. What are the
expectations for pg_catalog schema dumps? Are they supposed to be
restorable?

Hmm, well, actually not --- it certainly wouldn't make any sense to
try to create pg_class again, for instance. You could imagine changing
the schema name and creating a clone of all the objects, but that
only works for objects with schema-qualified names. So mostly this
is only useful for documentation, which I think is Neil's use-case
anyway.

That leads to the idea that it would be okay to let pg_dump print
"provider = default" but *not* let CREATE COLLATION accept that.
If we did that and also disallowed cloning the default collation,
then we'd have the property that only the original default collation
has provider 'd', and the existing code would continue to work.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On 12 June 2017 at 12:18, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:

On 6/11/17 20:19, Tom Lane wrote:

Alternatively, I think that CREATE COLLATION
ought to grow the ability to accept "provider = default" and pg_dump
should use that.

We could probably do that at least in pg_dump. What are the
expectations for pg_catalog schema dumps? Are they supposed to be
restorable?

Hmm, well, actually not --- it certainly wouldn't make any sense to
try to create pg_class again, for instance. You could imagine changing
the schema name and creating a clone of all the objects, but that
only works for objects with schema-qualified names. So mostly this
is only useful for documentation, which I think is Neil's use-case
anyway.

Yes that's what I was using it for. I thought it worth reporting just
in case it was a symptom of something bigger. Hopefully it's just for
my uncommon case :)

That leads to the idea that it would be okay to let pg_dump print
"provider = default" but *not* let CREATE COLLATION accept that.
If we did that and also disallowed cloning the default collation,
then we'd have the property that only the original default collation
has provider 'd', and the existing code would continue to work.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On 6/12/17 12:18, Tom Lane wrote:

That leads to the idea that it would be okay to let pg_dump print
"provider = default" but *not* let CREATE COLLATION accept that.
If we did that and also disallowed cloning the default collation,
then we'd have the property that only the original default collation
has provider 'd', and the existing code would continue to work.

Fixed that way.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs