Re: Schema version management

On Wed, May 23, 2012 at 9:09 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Thus, not implausibly, causing the dump to fail entirely on some
> filesystems.  Case sensitivity, encoding issues, and special characters
> in names (eg slashes or backslashes, depending on platform) are
> additional pain points.  This does not sound like a good plan from here.

This is true, which means some users won't be able to use the feature,
because they are using an ancient OS or have function names with slashes,
hm, is it even possible to have function names with slashes?

The maximum length of tables, functions etc in postgres is 63 characters.
A function in postgres can have at most 100 arguments.
The absolute majority of users run operating systems allowing
at least 255 characters, http://en.wikipedia.org/wiki/Comparison_of_file_systems

I suppose you have a lot more experience of what postgres installations exists
in the world. Do you think it's common databases have non-ascii problematic
characters in object names?

Is it a project policy all features of all standard tools must be
useful for all users
on all platforms on all databases? Or is it acceptable if some features are only
useable for, say, 90% of the users?

> Taking a step or two back, it seems to me that the thrust of your
> proposal is essentially to throw away all dump ordering information,
> which does not seem like a particularly good idea either.  It certainly
> will not lead to a dump that can be restored reliably.  If the use-case
> for this is database comparisons, I think we'd be a lot better off to
> write a postprocessing tool for regular dumps to perform such
> comparisons, rather than whacking pg_dump around to the point where it's
> unable to perform its primary function.

Not at all, the ordering information is not thrown away, it is preserved
in the dump file specified by the -f option, from which each split file
is included using \i

Example, this is an extract of the -f dump file in my database:

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- Name: pgx_diag; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA pgx_diag;

ALTER SCHEMA pgx_diag OWNER TO postgres;

-- ... some more schemas, languages etc ...
-- ... and then all the included files:

\i /home/postgres/database/gluepay-split/public/TYPE/dblink_pkey_results.sql
\i /home/postgres/database/gluepay-split/public/TYPE/r_matchedwithdrawal.sql
\i /home/postgres/database/gluepay-split/public/TYPE/r_unapprovedwithdrawal.sql
\i /home/postgres/database/gluepay-split/public/TYPE/ukaccountvalidationchecktype.sql
\i /home/postgres/database/gluepay-split/aml/FUNCTION/check_name.sql
\i /home/postgres/database/gluepay-split/aml/FUNCTION/describe_entityid.sql
\i /home/postgres/database/gluepay-split/aml/FUNCTION/get_linkid.sql
\i /home/postgres/database/gluepay-split/aml/FUNCTION/set_address.sql
-- ... all the objects ..
\i /home/postgres/database/gluepay-split/public/FK_CONSTRAINT/workershistory_workerid_fkey.sql
\i /home/postgres/database/gluepay-split/public/FK_CONSTRAINT/workershistory_workerstatusid_fkey.sql
\i /home/postgres/database/gluepay-split/public/FK_CONSTRAINT/workershistory_workertypeid_fkey.sql

-- .. and after all the included files comes permissions and stuff:

--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;