On Wed, 2007-11-07 at 08:38 -0500, Bruce Momjian wrote: > Simon Riggs wrote: > > If an application has already made that choice then we should allow them > > the opportunity to work with PostgreSQL. The application may be at > > fault, but PostgreSQL is the loser because of that decision. > > > > The SQL Standard says that the default for this is defined by the > > implementation; that doesn't bar us from changing the implementation if > > we wish. We can do that without changing PostgreSQL's historic default. > > > > Perhaps we can have a parameter? > > > > default_null_sorting = 'last' # may alternatively be set to 'first' > > > > (or another wording/meaning.) > > > > That is what I thought you'd implemented, otherwise I would have > > suggested this myself way back. This new parameter would be a small > > change, but will make a major difference to application portability. > > > > This seems like the key to unlocking your new functionality for most > > people. > > You already have that control at the SQL SELECT level so you are just > avoiding typing to add the GUC parameter. My understanding is that both MySQL and MSSQL support NULLS FIRST by default, so being able to accept much of their SQL without change would be a huge win. Editing an application, you would be required to add the words NULLS FIRST to every single ORDER BY and every single CREATE INDEX in an application. If we know that is what people would do, why not have one parameter to do this for them? Now imagine you are writing an application that has to work on multiple databases. Can you realistically create a workable framework that has the SQL written in multiple different ways? That issue is the big issue preventing many off-the-shelf software vendors from supporting Postgres. Say you did decide to edit the application. As soon as you edit the SQL within an application it typically will violate any support contract in place. That's usually enough to prevent even the brave from doing this. One might argue that SQL generators such as Hibernate can automatically and easily generate the required SQL, so they don't need this. That's very nice to know we'll be able to use the new feature maybe 10-20% of the time, but what about other applications? We already have parameters of this category, for example: default_with_oids == WITH OIDS text on CREATE TABLE default_transaction_isolation... default_read_only... plus many of the other GUCs in statement behaviour section of the Server Configuration chapter. add mising from transform null equals etc http://developer.postgresql.org/pgdocs/postgres/runtime-config-compatible.html#RUNTIME-CONFIG-COMPATIBLE-CLIENTS > I think we need more requests for such a feature before we add it. Almost none of the features we've added have come by request. Features get added because we see the benefit ourselves. Yes, we should discuss this more widely; I'm confident many others will see the benefit in allowing migration from other systems to happen more easily. What we have now implements SQL Standard behaviour. I think that's uninteresting for 99% of applications. I believe in standardisation, but nobody gets excited about it. There are few applications that will specify NULLS FIRST for a few queries only, actually coding that into the SQL. Implement SQLServer and MySQL behaviour? Now we're talking about hundreds of new applications that might decide to migrate/support PostgreSQL because of our flexibility in being able to support both kinds of sorting. It's going to be a short patch. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
