Обсуждение: [HACKERS] case_preservation_and_insensitivity = on
Hi hackers, "A system that is not case-preserving is necessarily case-insensitive, but it is possible and common for a system to be case-insensitive, yet case-preserving" [1] Imagine if you could turn on a GUC that would turn PostgreSQL into such a system, where the case would be preserved by default for all created objects, without having to use double-quotes around all identifiers, and while still being able to refer to such created objects case-insensitively, without having to use double-quotes around all identifiers. Today, you have to sacrifice the nice case-insensitivity feature if you wish to preserve case information, which is a shame. This would make a huge difference in terms of usability when using PostgreSQL together with external systems where casing is important. Today, you have to maintain silly look-up tables to translate between PostgreSQL's internal lowercase objects, and the outside world's e.g. CamelCase names for the corresponding objects, or you have to sacrifice the nice case-insensitivity feature. Case Preservation + Case Insensitivity = A good combination Thoughts? [1] https://en.wikipedia.org/wiki/Case_preservation
Joel Jacobson <joel@trustly.com> writes: > Case Preservation + Case Insensitivity = A good combination > Thoughts? Have you read any of our innumerable previous discussions about this? The last one was barely a month ago, cf https://www.postgresql.org/message-id/flat/ACF85C502E55A143AB9F4ECFE960660A17282D%40mailserver2.local.mstarlabs.com https://www.postgresql.org/message-id/flat/CA%2BTgmoYcDXCp5E-2ga2%2BbBz%3DcdQN6T_bBDXksiggm6BtR7UA1A%40mail.gmail.com (somehow the thread got broken in two in the archives) The short answer is that nobody can see a way to modify the identifier case-folding rules that isn't going to add more pain than it subtracts. And much of the added pain will be felt by people who aren't getting any benefit, who will therefore be vociferously against the whole thing. regards, tom lane
On Thu, Feb 16, 2017 at 6:53 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Have you read any of our innumerable previous discussions about this? No, sorry, didn't see them, thanks for sharing the links. > The short answer is that nobody can see a way to modify the identifier > case-folding rules that isn't going to add more pain than it subtracts. > And much of the added pain will be felt by people who aren't getting > any benefit, who will therefore be vociferously against the whole thing. I've read the discussion and have an idea: When case preservation by default is on, then simply enforce UNIQUE(LOWER(object_name)), to prevent ambiguity. If all objects lowercase names are unique, but the casing is preserved, then a user who later on suffers from problems with external tools that work poorly with non-lowercase object names, could then simply switch back to lowercase object names by changing the GUC. OTOH, if not enforcing lowercase uniqueness, there would be a risk two objects with different casing would have conflicting lowercase names, and then the user who later runs into problems with some external tool would have a serious problem, since switching to lowercase would not be an option.
On Thu, Feb 16, 2017 at 11:16 PM, Joel Jacobson <joel@trustly.com> wrote: >> The short answer is that nobody can see a way to modify the identifier >> case-folding rules that isn't going to add more pain than it subtracts. >> And much of the added pain will be felt by people who aren't getting >> any benefit, who will therefore be vociferously against the whole thing. > > I've read the discussion and have an idea: > > When case preservation by default is on, then simply enforce > UNIQUE(LOWER(object_name)), to prevent ambiguity. That (1) breaks backward compatibility, because people might have objects with names identical except for case in existing databases and (2) requires an expression index on a system catalog, which is not supported. You could work around problem #2 with enough work, I guess, by storing two copies of the name of "name" column, one with the original casing and a second that has been downcased for indexing purposes. I don't really understand what the GUC does in this scenario. Changing a GUC won't change the data that's already in your system catalogs, so it would have to change the interpretation of newly-arriving queries against that data. But once you've already decided to have a hard-and-fast rule that the names must be unique after lower-casing, there's no obvious benefit to rejecting queries that mention the same name with different case. As compared with any proposal that actually changes the case-folding behavior, a new mode that is case-preserving but case-insensitive would break less stuff. People who never use case to differentiate between different objects probably won't notice the difference, except that sometimes they might accidentally type something in the wrong case and it would work instead of failing. Tools that are designed on the existing fold-to-lowercase behavior would keep working if they don't actually query the system catalogs for information; those that do might need adjustment. It still sounds pretty painful, though. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Sun, Feb 19, 2017 at 5:58 PM, Robert Haas <robertmhaas@gmail.com> wrote: >> When case preservation by default is on, then simply enforce >> UNIQUE(LOWER(object_name)), to prevent ambiguity. > > That (1) breaks backward compatibility, because people might have > objects with names identical except for case in existing databases and Yes, but if since the target for this new feature is probably new projects (who need this feature to even consider PostgreSQL as a database, like the original author of the other thread where they were using SQLAnywhere), then maybe that's an acceptable tradeoff, since all the existing database who try to use the feature will just get an error if they try to switch on the feature e.g. "error: objects exist with identical lowercase names". > (2) requires an expression index on a system catalog, which is not > supported. You could work around problem #2 with enough work, I > guess, by storing two copies of the name of "name" column, one with > the original casing and a second that has been downcased for indexing > purposes. Yes, storing both the unique lowercase name together with the OriginalCase was also my idea on how to implement it. > I don't really understand what the GUC does in this scenario. Changing the GUC (if you run into problems with tools) would simply just change what names are returned from pg for all the object names, i.e. it would then return the lowercase names instead of the OriginalCase names, to make the tools happy. This means any pg user who enabled the CasePreserving feature when creating the database, would not have to redesign their entire database if they later run into problems with tools, but can simply just switch off the GUC. > But once you've already > decided to have a hard-and-fast rule that the names must be unique > after lower-casing, there's no obvious benefit to rejecting queries > that mention the same name with different case. Exactly, that trade-off is necessary, otherwise such queries would be ambiguous. I think a good general philosophy for the PostgreSQL project would be to try to look at how to meed the needs for new users of new projects in a way that don't impair things for existing users, by accepting the new users might have to live with some trade-offs for their new feature to be possible to implement, such as in this case that the trade-off is to not be able to create objects of different casing with the same lowercase names, a tradeoff that I personally think would not be a problem for most projects, since it seems unlikely you would both have a "users" table and a "Users" table in the same database.
Joel Jacobson <joel@trustly.com> writes:
> I think a good general philosophy for the PostgreSQL project would be to
> try to look at how to meed the needs for new users of new projects
> in a way that don't impair things for existing users,
Yeah, exactly, and the problem here is that claiming that something
like this doesn't impact existing users is just ignoring reality.
At the minimum, every author of a driver or other client-side tool
is going to have to try to figure out how to make their code work
with all the possible case-folding rules. They don't have the option
of ignoring server-side changes in the language.
That's why I alluded upthread to the old "server-side-autocommit" fiasco.
That too was sold to us using the argument that it wouldn't impact people
who didn't turn it on. It took about a year for the full scope of the
damage to become apparent, and the end result was that we took the feature
out again. I haven't yet seen an alternate-case-folding proposal that
wouldn't likely end up as the same kind of failure.
The versions of autocommit that have actually stood the test of time were
implemented on the client side (in psql and JDBC, and I think ODBC as
well), where the scope of affected code was lots smaller. I wonder
whether there's any hope of providing something useful for case-folding
in that way. psql's lexer is already smart enough that you could teach it
rules like "smash any unquoted identifier to lower case" (probably it
would fold keywords too, but that seems OK). That's probably not much
help for custom applications, which aren't likely to be going through
psql scripts; but the fact that such behavior is in reach at all on the
client side seems encouraging.
regards, tom lane
On 2/19/17 4:51 PM, Joel Jacobson wrote: >> But once you've already >> decided to have a hard-and-fast rule that the names must be unique >> after lower-casing, there's no obvious benefit to rejecting queries >> that mention the same name with different case. > Exactly, that trade-off is necessary, otherwise such queries would be ambiguous. > > I think a good general philosophy for the PostgreSQL project would be to > try to look at how to meed the needs for new users of new projects > in a way that don't impair things for existing users, > by accepting the new users might have to live with some trade-offs > for their new feature to be possible to implement, > such as in this case that the trade-off is to not be able to create > objects of different casing with the same lowercase names, > a tradeoff that I personally think would not be a problem for most projects, > since it seems unlikely you would both have a "users" table and a > "Users" table in the same database. There's a serious problem with that, though: there certainly *could* be existing users that depend on the difference between "Users" and users, and there's no way we can just leave them out in the cold. Even if the project decided that "Users" and users is stupid and that we should deprecate it, I think the odds of also deciding to tell existing users to re-write their apps are zero. So no matter how this is designed, there has to be some way for existing users to be able to continue relying on "Users" and users being different. AFAICT that rules out any chance of this being a GUC, because you can't take a GUC into consideration when creating a unique index. What would work is an initdb option that controls this: when ignoring case for uniqueness is disabled, your new column would simply be left as NULL. With some extra effort you could probably allow changing that on a running database as well, just not with something as easy to change as a GUC. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532)
On Mon, Feb 20, 2017 at 2:40 AM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote: > Even if the project decided that "Users" and users is stupid and that we > should deprecate it, I think the odds of also deciding to tell existing > users to re-write their apps are zero. But if the feature can't be turned on without also enforcing lowercase uniqueness, then the described problem situation will never happen. Any existing projects who want to use the new feature but can't due to conflicting names, will simply just have to live without it, just like they already do. > So no matter how this is designed, there has to be some way for existing > users to be able to continue relying on "Users" and users being different. There is a way, simply don't switch on the feature, and "Users" and "users" will continue to be different. > What would work is an initdb option that controls this: when ignoring case > for uniqueness is disabled, your new column would simply be left as NULL. > With some extra effort you could probably allow changing that on a running > database as well, just not with something as easy to change as a GUC. initdb option sounds good to me, just like you specify e.g. --encoding. Also, I think the --lowercase-uniqueness feature would be useful by itself even without the --case-preserving feature, since that might be a good way to enforce a good design of new databases, as a mix of "users" and "Users" is probably considered ugly by many system designers.
On Mon, Feb 20, 2017 at 1:45 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > The versions of autocommit that have actually stood the test of time were > implemented on the client side (in psql and JDBC, and I think ODBC as > well), where the scope of affected code was lots smaller. I wonder > whether there's any hope of providing something useful for case-folding > in that way. psql's lexer is already smart enough that you could teach it > rules like "smash any unquoted identifier to lower case" (probably it > would fold keywords too, but that seems OK). That's probably not much > help for custom applications, which aren't likely to be going through > psql scripts; but the fact that such behavior is in reach at all on the > client side seems encouraging. This sounds like a really good solution to me, since there is actually nothing missing on the PostgreSQL server-side, it's merely a matter of inconvenience on the client-side. As long as the definitions of the database objects when stored in the git repo can be written without the double-quotes, i.e. CREATE TABLE Users ( instead of CREATE TABLE "Users" ( where the object would be created as "Users" with capital "U", then I see no problem. Most people probably use psql to initiate a db instance of their project locally, so if psql would have a --preserve-case option, that would solve the problem of creating new objects. Or maybe --no-case-folding is a better name for the option.
On 2/20/17 3:30 AM, Joel Jacobson wrote: > Also, I think the --lowercase-uniqueness feature would be useful by > itself even without the --case-preserving feature, > since that might be a good way to enforce a good design of new databases, > as a mix of "users" and "Users" is probably considered ugly by many > system designers. FWIW, I don't think --lowercase-uniqueness is a good name. --case-insensitive-unique would be better. In addition to that, it'd be interesting to allow for a user-supplied name validation function that can throw an error if it sees something it doesn't like (such as a name that contains spaces, or one that's longer than NAMEDATALEN). I suspect it'd be pretty hard to add that though. BTW, keep in mind that what you're suggesting here means changing *every* catalog that contains a name field. A query against info_schema will show you that that's most of them. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532)
On Mon, Feb 20, 2017 at 3:06 PM, Joel Jacobson <joel@trustly.com> wrote: > On Mon, Feb 20, 2017 at 1:45 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> The versions of autocommit that have actually stood the test of time were >> implemented on the client side (in psql and JDBC, and I think ODBC as >> well), where the scope of affected code was lots smaller. I wonder >> whether there's any hope of providing something useful for case-folding >> in that way. psql's lexer is already smart enough that you could teach it >> rules like "smash any unquoted identifier to lower case" (probably it >> would fold keywords too, but that seems OK). That's probably not much >> help for custom applications, which aren't likely to be going through >> psql scripts; but the fact that such behavior is in reach at all on the >> client side seems encouraging. > > This sounds like a really good solution to me, > since there is actually nothing missing on the PostgreSQL server-side, > it's merely a matter of inconvenience on the client-side. It doesn't sound like a good solution to me, because there can be SQL code inside stored procedures that clients never see. In fact, a function or procedure can assemble an SQL query text using arbitrary Turing-complete logic and then execute it. In fact, you don't even really need a function or procedure; the client could send a DO block that does this directly. We don't run into this problem with autocommit because a function or procedure has to run entirely within a single transaction, so I don't think that is really the same thing. If you only care about rewriting queries that come directly from a client and you don't care about DO blocks, then you could probably make this work, but it still requires that the client parse the query using a lexer and parser that are very similar to the quite complicated ones on the server side. That might be hard to get right, and it's probably also expensive. I think that solving this problem on the server side is likely to be a huge amount of really unrewarding work that might get rejected anyway after tons of effort, but if you did happen to succeed in solving it, it would be a good clean solution. Doing something on the client side is just a kludge. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Feb 20, 2017 at 3:06 PM, Joel Jacobson <joel@trustly.com> wrote:
>> On Mon, Feb 20, 2017 at 1:45 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> The versions of autocommit that have actually stood the test of time were
>>> implemented on the client side (in psql and JDBC, and I think ODBC as
>>> well), where the scope of affected code was lots smaller. I wonder
>>> whether there's any hope of providing something useful for case-folding
>>> in that way.
> It doesn't sound like a good solution to me, because there can be SQL
> code inside stored procedures that clients never see. ...
> If you only care about rewriting queries that come directly from a
> client and you don't care about DO blocks, then you could probably
> make this work,
I think these are straw-man arguments, really. Consider the actual use
case for such a feature: it's for porting some application that was not
written against Postgres to begin with. The odds that such an app uses DO
blocks are exactly zero. It may well make use of server-side functions,
but those are going to require significant porting effort in any case.
So I think a case-folding function that could be interposed in front of
libpq or JDBC or $favorite_driver could well go a long way towards solving
that sort of problem. It wouldn't be 100%, sure, but if you hold your
breath and wait for a 100% solution to appear on the server side, you're
going to be quite blue.
> but it still requires that the client parse the query
> using a lexer and parser that are very similar to the quite
> complicated ones on the server side. That might be hard to get right,
Not if you look into src/fe_utils. There's a reason why we sweated so
hard to break out psql's lexer, and it wasn't that pgbench needed it.
> I think that solving this problem on the server side is likely to be a
> huge amount of really unrewarding work that might get rejected anyway
> after tons of effort, but if you did happen to succeed in solving it,
> it would be a good clean solution.
The problem is not whether hacking the server's case folding could be done
cleanly (although I agree that touching it at all would be a lot of work).
The problem is all the client-side breakage you're going to cause for
every other bit of software that will have no choice but to learn to
cope with it.
Basically, the difficulty with requests like this is that they demand
externalizing someone's case-folding conversion costs onto the rest of
the Postgres-using universe. I don't believe there's a way to make that
problem disappear if you insist on doing it server-side. But maybe a
properly defined client-side library could help some people without
creating costs for other people.
regards, tom lane
On Thu, Feb 23, 2017 at 6:59 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > I think these are straw-man arguments, really. Consider the actual use > case for such a feature: it's for porting some application that was not > written against Postgres to begin with. I'm not sure that's totally true. I think at least some requests for this feature are intended at satisfying somebody's sense of aesthetics. But I'll agree that the biggest use case is probably porting applications from other systems. Even then, though, people end up using PG-specific features as a way of working around stuff that other systems support using different syntax - e.g. T-SQL that works on SQL server might get turned into a DO block for PostgreSQL, and it's surprising if that causes you to suddenly get different case-folding rules. Also, people sometimes start by doing a port, and then later start using PG-specific features, which introduces the same kinds of problems. > The odds that such an app uses DO > blocks are exactly zero. It may well make use of server-side functions, > but those are going to require significant porting effort in any case. > So I think a case-folding function that could be interposed in front of > libpq or JDBC or $favorite_driver could well go a long way towards solving > that sort of problem. It wouldn't be 100%, sure, but if you hold your > breath and wait for a 100% solution to appear on the server side, you're > going to be quite blue. True, but that doesn't make a leaky abstraction not a hack. It just makes having a hack a practical improvement over not having one -- and I'm perfectly happy to concede that hacks are sometimes useful. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Thu, Feb 23, 2017 at 8:04 AM, Robert Haas <robertmhaas@gmail.com> wrote: > > It doesn't sound like a good solution to me, because there can be SQL > code inside stored procedures that clients never see. In our code base, we use CamelCase in all PL/pgSQL functions, both for columns and variables, e.g. SELECT UserID INTO _UserID FROM Users WHERE Username = 'foo'; Here, it's not a problem that the column name is e.g. "userid", since the case-insensitive feature makes it work. What type of case problem do you foresee for stored procedures? I've only experienced the case-folding to be a problem outside of SPs, since the casing *is* preserved in the PL/pgSQL source code (since it's stored as-is, without any modifications). What *would* be a problem though, is if in a future PL/pgSQL 3, a PL/pgSQL query like, SELECT UserID FROM Users WHERE Username = 'foo'; would automatically export the column "UserID" to the current scope as a PL/pgSQL 3 variable named "userid", since then you would actually want the value of the userid column to be exported to a variable named "UserID". Such a feature would be nice, since a very common code-pattern in PL/pgSQL is to just have lots of meaningless identical lists of columns and then an identical list of variables with the same names as the columns. When the list is short, it's not a problem, but when selecting lots of columns, it gets ugly. What I usually end up with is to align the columns and variables on two rows, e.g.: SELECT SomeCol, OtherCol, FooCol, BarCol, MyCol, ExtraCol, LastCol INTO _SomeCol, _OtherCol, _FooCol, _BarCol, _MyCol, _ExtraCol, _LastCol FROM Foo WHERE Bar = 'Baz'; This is to avoid typos that are then visually easy to spot, thanks to all chars being aligned. Imagine if, thanks to case-preservation, if you should simply do: SELECT SomeCol, OtherCol, FooCol, BarCol, MyCol, ExtraCol, LastCol FROM Foo WHERE Bar = 'Baz'; And all the columns would be exported to the variables SomeCol, OtherCol, FooCol, BarCol, MyCol, ExtraCol, LastCol, instead of somecol, othercol, foocol, barcol, mycol, extracol, lastcol; This would be a huge win in avoiding unnecessary code repetition. Then of course, if you want a column Foo to instead be exported to Bar, then you simply do "SELECT Foo AS Bar". Thoughts?
On 2/24/17 11:34 AM, Joel Jacobson wrote: > SELECT SomeCol, OtherCol, FooCol, BarCol, MyCol, ExtraCol, LastCol > INTO _SomeCol, _OtherCol, _FooCol, _BarCol, _MyCol, _ExtraCol, _LastCol > FROM Foo > WHERE Bar = 'Baz'; > > This is to avoid typos that are then visually easy to spot, thanks to > all chars being aligned. Why not just use a record or the table composite? I'll commonly do stuff like: DECLARE r record BEGIN SELECT INTO STRICT r blah, foo, bar, baz FROM pirate ; IF r.blah THEN RAISE 'Yaaarrrrr!' END IF; ... (Well, to be honest I always try to write pirate apps in plR... ;P) -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532)
On 2/24/17 12:28 AM, Robert Haas wrote: > On Thu, Feb 23, 2017 at 6:59 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> I think these are straw-man arguments, really. Consider the actual use >> case for such a feature: it's for porting some application that was not >> written against Postgres to begin with. > I'm not sure that's totally true. I think at least some requests for > this feature are intended at satisfying somebody's sense of > aesthetics. If I had $1 for every time I had to chase someone away from using camelcase I'd be able to sponsor a key at the next conference. And honetly I'd actually like to be able to use camelcase and still get easy to read output from \d & co. IOW, this is definitely NOT driven just by porting efforts. I think the only reason we don't hear more requests about it is people (grudgingly) just muddle on without it. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532)
On 25/02/17 09:02, Jim Nasby wrote: > On 2/24/17 12:28 AM, Robert Haas wrote: >> On Thu, Feb 23, 2017 at 6:59 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> I think these are straw-man arguments, really. Consider the actual use >>> case for such a feature: it's for porting some application that was not >>> written against Postgres to begin with. >> I'm not sure that's totally true. I think at least some requests for >> this feature are intended at satisfying somebody's sense of >> aesthetics. > > If I had $1 for every time I had to chase someone away from using > camelcase I'd be able to sponsor a key at the next conference. And > honetly I'd actually like to be able to use camelcase and still get > easy to read output from \d & co. > > IOW, this is definitely NOT driven just by porting efforts. I think > the only reason we don't hear more requests about it is people > (grudgingly) just muddle on without it. I'd love to be able to successfully use camelcase for things like variable and table names in pg, without having to quote everything - but never felt it worthwhile to ask for it. Cheers, Gavin
On Fri, Feb 24, 2017 at 11:04 PM, Joel Jacobson <joel@trustly.com> wrote: > On Thu, Feb 23, 2017 at 8:04 AM, Robert Haas <robertmhaas@gmail.com> wrote: >> It doesn't sound like a good solution to me, because there can be SQL >> code inside stored procedures that clients never see. > > In our code base, we use CamelCase in all PL/pgSQL functions, both for > columns and variables, > e.g. SELECT UserID INTO _UserID FROM Users WHERE Username = 'foo'; > > Here, it's not a problem that the column name is e.g. "userid", > since the case-insensitive feature makes it work. > > What type of case problem do you foresee for stored procedures? If we did something on the server-side, stored procedures would be handled just like everything else, so stored procedures would suffer or be unaffected to precisely the same extent as anything else. However, Tom proposed doing the case-remapping on the client side, which would cause the behavior to be one thing for queries submitted directly from the client and something else for queries that appear inside PL code. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company