Re: Schema version management

On Tue, May 22, 2012 at 11:31 PM, Joel Jacobson <joel(at)trustly(dot)com> wrote:
> 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?

Sure. If you quote the function name, you can put anything you want
in there. Note that Windows disallows a whole bunch of special
characters in filenames, while UNIX-like systems tend to disallow only
slash.

> 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?

There are cases where we permit features that only work on some
platforms, but it's rare. Usually, we do this only when the platform
lacks some API that exists elsewhere. For example, collations and
prefetching are not supported on Windows because the UNIX APIs we use
don't exist there.

In this case, it seems like you could work around the problem by, say,
URL-escaping any characters that can't be used in an unquoted
identifier. Of course that might make the file name long enough to
hit the platform-specific file name limit. Not sure what to do about
that. The basic idea you're proposing here has been proposed a number
of times before, but it's always fallen down over questions of (1)
what do do with very long object names or those containing special
characters and (2) objects (like functions) for which schema+name is
not a unique identifier.

I don't think either of these problems ought to be a complete
show-stopper. It seems to me that the trade-off is that when object
names are long, contain special characters, or are overloaded, we'll
have to munge the names in some way to prevent collisions. That could
mean that the names are not 100% stable, which would possibly produce
some annoyance if you're using a VCS to track changes, but maybe
that's an acceptable trade-off, because it shouldn't happen very
often. If we could guararantee that identifiers less than 64
characters which are not overloaded and contain no special characters
requiring quoting end up in an eponymous file, I think that would be
good enough to make most of our users pretty happy. In other cases, I
think the point would just be to make it work (with a funny name)
rather than fail.

> \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

It would be better to use \ir here rather than hard-code path names, I
think. Then you'd only need to require that all the files be in the
same directory, rather than requiring them to be at a certain
hard-coded place in the filesystem.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company