Why I Don't Develop for Postgres | calguy1000.com

From time to time I get sporadic, but very oppinionated complaints about this addon module to CMSMS or that addon module to CMSMS and problems with Postgres.  The people making these 'bug reports' are typically very adamant that this is a "serious bug" and that it should be fixed "ASAP".  I usually end up getting numerous responses from only a few people about these "bug reports", and I usually appear to be quite arrogant because I am firm in my stand against Postgres.  Here I'll try to put this issue to rest.

The first reason I don't support Postgres has to be because the vast majority of CMSMS users use mysql.  Wether this be by choice, performance, historical experience, or because it's what their hosts provide, the reason is irrelevant.   I would hazard a wild guess that at least 90% of the CMSMS users use MySQL, and would continue to do so even if ALL of the functionality from ALL of the addons operated equally on both databases.   Therefore, it would be inefficient of me to spend considerable amount of time installing Postgres, creating another CMSMS install (for each version of CMSMS, installing and testing my work on both environments just to satisfy maybe 10% of the user base.

Now, some people will try to start the argument that 'MySQL is used because CMSMS doesn't support postgres that well'.  It's a chicken and egg argument.   Even in 1995 when I started developing for CMSMS we could count the people that even wanted to use postgres on 1 hand.   Now it may take two hands.

Secondly, even though CMSMS uses the adodb database abstraction library to provide a reasonable level of database independance from the start, it is still possible, and sometimes necessary to write database specific queries to implement certain functionality.   Examples are the 'SHOW TABLES' command that mysql supports, and the 'REGEXP' query operator.   Postgres doesn't support these, or at least not in the same way, and there is no 'common' command to perform the same functions.  Thirdly, Postgres is much more strict about some advanced queries that use limits, or subqueries or group parameters.

The 'SHOW TABLES" command can be used to implement an on-demand backup system for mysql, or to test if a particular table exists.  The SitePusher and NMS modules use this functionality.

The 'REGEXP' query operator allows me to query if certain fields in a table match a regular expression.  This is handy for user controlled filtering. A number of my addon modules use this functionality to provide filtering that myself and numerous other users find handy.   Sure I could use the 'LIKE' operator just as easily, or full text searching, but many customers have explicitly asked for regular expression type searches.  Therefore that functionality goes in, and Postgres support doesn't work.

The stricter syntax requirements of Postgres are not in themselves a problem, usually, except possibly in the case of LIMIT statements, or subqueries having LIMIT statements.  In the past Mysql supported the "LIMIT <start>,<count>" syntax, where Postgres required "LIMIT <count> OFFSET <start>".  Later versions of mysql now support that syntax... also the adodb library we use handles that abstraction in most cases.... except for in the case of subqueries.

Additionally, Postgres is more strict on grouped queries, requiring a more formal query than that required by mysql.   It's yet another step to go through.

Thirdly, occasionally there have been problems with 'case sensitivity' in table names.  Mysql seems to be case insensitive, and Postgres seems to be case sensitive.  Now clearly not being consistent with table names and how I access them is definately something I could change.   But it works fine on mysql.

I will say that the third reason I don't support postgres has to be due to the legacy of my programming experience in PHP, of CMSMS and of postgres.

In the early days of CMSMS (back in the 0.7.9 days), I was just venturing in to database programming, the stuff I had done before was all in C/C++ using custom database libraries, and/or berkely.  I had very little experience programming in any RDBMS (except for some experience with PL/SQL which left a bad taste in my mouth).  Therefore when I started devloping for CMSMS I had a bit of a learning curve to get over.  And a bunch of the queries and commands I wrote wouldn't work on postgres... there are still quite a few of these queries around.   It would take some effort to go through and test everything, fix, test again...

Lastly, as a sole developer, writing and maintaining tens of thousands of lines of code per year, I can't hope to support every permutation and combination of environment, and ensure equal functionality everywhere... The time just isn't there to setup duplicate environments, test each peace of functionality on each environment, fix any issues and test again.

Now somebody is gonna add a comment here saying something like "If Postgres is much more strict, why don't you do your primary development for postgres, then port back to MySQL"... Lets nip that argument in the bud before it starts.   I mentioned above that I think at least 90% of the user base uses mysql, therefore the proper thing to do would be to mimic as much as possilbe the environment are users are running under.   Also, If using postgres for my primary database, I'd still have to go back and test in mysql to handle the 90% user case... that doesn't make sense.

Therefore, I hope you can now see why Postgres support is not a big priority for me with regards to CMSMS, and will probably continue to be neglected.  Unless that is, somebody gives me a significant reason to change my way of working.