Monday 2 April 2012

Deprecated features in PostgreSQL - Past to present


If you have been using PostgreSQL for a long time, or you’re relatively new but have been following old instructions about how to use it, it’s possible that you’re using features that have been deprecated. The reason features disappear tend to be because they have been superseded by better features which cover the same functionality. It’s important to try to avoid using features which are destined to disappear if there’s a newer alternative. Also when planning an upgrade, it’s useful to know if a feature you’re using will suddenly break in the new version. Some of these features still continue to work, but have since been removed from documentation because they’re to be removed in a future release. Others have just been removed completely.

We’ll start off with the absurdly old and work our way to the present:

Version 6.2

timetravel contrib module

Does it still work?: No. This is ooooold and was last supported back in PostgreSQL 6.1.

What’s wrong with it?: This really dragged performance down and took up a huge amount of storage space. But the concept was pretty cool... being able to query data as it was at another time.

What to use instead: You can use triggers to implement a similar mechanism.

Version 6.4

char2/char4/char8/char16 data types

Does it still work?: No. These were removed way back in PostgreSQL 6.4. In fact I shouldn’t bother mentioning these, but you never know... someone *could* still be using them somewhere.

What’s wrong with it?: Not in the SQL standard and they’re no faster than using the ubiquitous char(n).

What to use instead: char(n)

Version 7.0

abstime data type

Does it still work?: This will still work, but it’s no longer documented as of PostgreSQL 7.0 and only intended to be used internally. Despite its name, it supports both date and time.

What’s wrong with it?: The range this data type provides is limited: ‘1901-12-14’ to ‘2038-01-19’. It also only has a resolution down to the second. Its behaviour is unfortunately like that of MySQL’s, in that if you insert an invalid value, it won’t fire an error. Instead you’ll just see ‘invalid’ as the value when you go to query it.

What to use instead: Since abstime supports timezone, the better alternative is using timestamp with time zone (timestamptz). It takes up more space (8 bytes instead of 4), but it has a far wider range: ‘4713 BC’ to ‘294276 AD’ and supports microsecond resolution.

reltime data type

Does it still work?: Yes, still works, but again, no longer documented as of PostgreSQL 7.0 and for internal use only.

What’s wrong with it?: This stores a date/time offset but only +/- 68 years. Again, this doesn’t error with values higher than this limit. Does it put ‘invalid’ in the column like abstime? No. Instead the value wraps around, so entering +70 years would result in a value of around -66 years. Not what you want. It also has a resolution down to the second.

What to use instead: The SQL standard equivalent of this kind of data type is interval, which PostgreSQL has. This does take up more space (12 bytes), but it’s range is absolutely huge: ‘-178000000 years’ to ‘+178000000 years’. This also has microsecond resolution. Interval can also handle relative time units; for example, adding a month to 15th February using interval will give you 15th March, but with abstime it has a fixed notion of a month being 30 days, so will give you 17th March (on a non-leap year). A year is also considered to be 360 days.

timespan data type

Does it still work?: No. This was deprecated back in PostgreSQL 7.0, and totally removed in PostgreSQL 7.3. If for any reason you’re using this, you’re *definitely* overdue an upgrade, and have been for many years.

What’s wrong with it?: It’s not in the SQL standard and was really just an alias for interval.

What to use instead: Just use interval.

psql/pg_dump’s -u option

Does it still work?: No, this was deprecated as far back as PostgreSQL 7.0 and removed in 8.3. You should definitely not be using this.

What’s wrong with it?: This option forced psql and pg_dump to prompt for the username and password before connecting to the database. Since prompting for a username is always optional, but prompting for a password may or may not be required (depending on authentication method), it didn’t make sense to glue both of these together.

What to use instead: It has been replaced by the -U option to specify the username, and the -W option to prompt for the password.

Version 7.1

getpgusername() function

Does it still work?: Yes, but it’s effectively deprecated as of PostgreSQL 7.1!

What’s wrong with it?: It’s no longer documented, and could be removed in a future release since it’s obsolete.

What to use instead: Call current_user instead, since getpgusername() is now just an alias for that.

Version 8.1

autovacuum contrib module

Does it still work?: No, as it was moved into core since PostgreSQL 8.1.

What’s wrong with it?: Nothing. Quite the opposite. It was considered so essential that it became part of the main codebase.

What to use instead: Nothing to worry about. Since it’s now in core, you get it out of the box without having to explicitly include it.

Version 8.2

mSQL-interface and tips contrib modules

Does it still work?: No, these were completely removed in PostgreSQL 8.2.

What’s wrong with it?: These were considered abandoned and unmaintained.

What to use instead: Nothing.

adddepend, dbase, dbmirror, fulltextindex, mac, ora2pg and userlock
contrib modules

Does it still work?: No, again, these were completely removed in PostgreSQL 8.2.

What’s wrong with it?: Most of these were moved to pgFoundry to be maintained separately.

What to use instead: These still exist on pgFoundry if you really want them (except for fulltextindex which has disappeared, and ora2pg which is on its own website), although they’re all now unmaintained (apart from ora2pg).

Version 8.3

automatic casting to text

Does it still work?: As of PostgreSQL 8.3, non-text data types are no longer implicitly cast to text. This is considered to be one of the major hurdles for some people migrating from earlier versions and the biggest cause of incompatibility.

What’s wrong with it?: Anyone who knows PostgreSQL well will know that it doesn’t like to throw any weirdness or odd behaviour your way. There are cases where implicit casting to text causes undesired results. For example: current_date < 2012-04-02 would result in both sides being automatically cast to text types, even though the date on the right-hand side would first be considered an integer (2012 minus 4 minus 2).

What to use instead: It’s always good practise to be explicit about data types when specifying literals. This will avoid any usual behaviour.

tsearch2 contrib module

Does it still work?: Yes, but it is deprecated as of PostgreSQL 8.3.

What’s wrong with it?: It has been superseded by changes in core with a few functional changes. It’s still kept around for backwards-compatibility.

What to use instead: Use the newer core functionality. There’s information on the tsearch2 contrib module page in the documentation on how to convert to the new functionality.

xml2 contrib module

Does it still work?: Yes, but it is deprecated as of PostgreSQL 8.3.

What’s wrong with it?: Nothing really, and it’s still around for backwards-compatibility, but there is newer XML functionality in core based on the SQL/XML standard.

What to use instead: Use the built-in XML features (xml data type, xml functions, xml parameters).

Version 8.4

pg_dump/pg_dumpall’s -d and -D options

Does it still work?: No, these were removed in PostgreSQL 8.4.

What’s wrong with it?: Such options were often mistaken for a database name parameter, but in fact it caused database dumps to output using insert statements rather than copy statements. This is significantly slower to restore, and cannot be adjusted after the fact.

What to use instead: If someone really did want to use these options intentionally, then the long name options of --inserts and --column-inserts are to be used instead.

Version 9.1

createlang/droplang client applications

Does it still work?: Only up until PostgreSQL 9.1.

What’s wrong with it?: Languages are now treated like extensions as of PostgreSQL 9.1.

What to use instead: Execute CREATE EXTENSION <language name> instead.

CREATE/DROP LANGUAGE

Does it still work?: Yes, but it’s no longer intended to be used by users, only extensions.

What’s wrong with it?: Languages are now considered to be extensions as of PostgreSQL 9.1.

What to use instead: You can install new languages by installing it as an extension with CREATE EXTENSION <language name>. And to remove it, use the DROP equivalent. If you’ve upgraded your cluster to 9.1 or above from a previous version, you will still have the language installed but not as an extension. You can, however, convert it to an extension by using: CREATE EXTENSION <language name> FROM unpackaged. You can then remove it later with DROP EXTENSION.

Version 9.2

=> operator

Does it still work?: Only up to PostgreSQL 9.1, but as of PostgreSQL 9.2, no, at least as far as hstore is concerned. This was actually deprecated in 9.0 and has emitted warnings about using it since then. This is most notably used in the hstore extension. You can still create this operator, but it will return a warning when you do so. Be prepared for this to be completely disallowed in a future release.

What’s wrong with it?: “=>” is reserved in the SQL standard for named function parameters, so it needs to be available for such functionality.

What to use instead: If you’ve been using this in hstore, then it will require changing text=>text to hstore(text,text). If you’ve been using it as a custom operator, you should change it to something else as at some point it will be prohibited.

Literal language name case-sensitivity

Does it still work?: If you're on 9.1 or below, yes, but as of 9.2 you won't get away with this anymore.

What’s wrong with it?: Language names should be treated like an identifier rather than a literal, and in general, literals are case-sensitive. There was special code that case-folded the language name so that the letter casing didn't matter, but this change is the first step in removing string literals as language names altogether.

What to use instead: Just don't use single quotes around language names when writing your functions at all, rather than just lower-casing them. Either use no quotes (as they're not needed for any core language) or use double-quotes as you would with any other identifier.

There are also lots of configuration parameters that have been removed, and here they are:

ParameterRemoved inWhy?
australian_timezones8.2Better generalised timezone configuration
preload_libraries8.2Renamed to shared_preload_libraries
bgwriter_all_percent8.3No longer necessary
bgwriter_all_maxpages8.3No longer necessary
bgwriter_lru_percent8.3No longer necessary
redirect_stderr8.3Renamed to logging_collector
stats_block_level8.3Now covered by track_counts
stats_command_string8.3Renamed to track_activities
stats_reset_on_server_start8.3pg_stat_reset() can be used instead
stats_row_level8.3Now covered by track_counts
stats_start_collector8.3Now always enabled
explain_pretty_print8.4No longer needed
max_fsm_pages8.4No longer needed as per-relation free space maps deal with this.
max_fsm_relations8.4No longer needed as per-relation free space maps deal with this.
add_missing_from9.0Always defaulted to ‘off’ so now permanently off.
regex_flavor9.0Always defaulted to ‘advanced’ so now permanently set to this.
custom_variable_classes9.2Considered better to remove it as it only causes more maintenance with minimal benefit.
silent_mode9.2Not necessary as can be achieved with pg_ctl -l or NOHUP.
wal_sender_delay9.2New latch infrastructure has now made this setting redundant.

Monday 23 January 2012

Password restrictions

I've been changing a lot of my passwords lately so that none are alike. In fact they're so ridiculously random and lengthy that I wouldn't stand a chance remembering them. I've a means of obtaining these passwords though in a method known to me using encryption which requires 2 very different types of key, so remembering them isn't necessary.

However, after going around trying to change my passwords, I've noticed that many places impose restrictions upon how long a password may be, and what characters you're allowed to use.

For example, with PayPal, they absolutely require you to have at least 8 characters, but for some reason, it must not be any more than 20 characters. Why limit it at 20? Surely they hash the password anyway?... don't they? eBay have an identical restriction.

And then came my bank (a Dutch bank which shall remain nameless). They also said that it must be at least 8 characters, but no more than 12. It must also contain at least one lower-case letter, one upper-case letter, one number and one non-alphabetic character. But then it also may not use a speech mark ("), equals (=), tilde (~), less-than (<) or greater-than (>). So this not only suggests they're not hashing the password, but there's also probably some risk posted by those additional characters, meaning they may not be sanitising their data. Normally if that were the case I'd expect characters like a back-tick (`), semi-colon (;) or single-quote (') to be prohibited, but for some reason those are allowed in this case. *shrug*

Google seems to be better at this, but still imposes an arbitrary limit. In this case they require a minimum of 8 characters, and allow up to 100 characters. Why 100? Does it matter? Thankfully they allow any character you like, including spaces and all types of punctuation you can think of. But in addition to this, I have 2-factor authentication enabled, so I require a 6-digit pin generated by my phone, so the risk of someone logging into my account is minimised. Although I guess this is all moot when it comes to Google's application-specific passwords. This is necessary if you use 2-factor authentication when you wish applications to access your account (such as an Email client), because those applications can't ask you for the 6-digit pin. So Google generates a password specifically for that application which can be revoked whenever you like. Here's an example of what its generated passwords look like:

qihi jnmd irjb ytis

They always show up as just lower-case letters, and the spaces don't matter. So really, if you have application-specific passwords enabled, the security on your Google Account is only as strong as those passwords, and obviously the more you have the lower the security. Still, it's extremely unlikely anyone could get into your account using brute-force trying to find a code, but it's a shame Google doesn't allow you to enter an application-specific password of your choice.

I also recently set up an account with Good Old Games (gog.com). The limit there was 32 characters. I tried using special characters in the password but found that it caused the form to show the password field as invalid, but wouldn't say why. I figured out they only allow upper-case and lower-case letters and numbers.

Amazon fare better than the rest so far. Any characters you want, up to 128 characters.

The best I've seen is SpiderOak. I gave it a totally random mix of every type of character and a password 1024 characters long. It was fine with this. I don't know how many more characters it will take, but surely that should be enough.

So SpiderOak is the most secure out of this list. SpiderOak also can't recover your password because they don't store it. Apparently it's only used to decrypt your user data, which they can't access. In fact they boast a zero-knowledge policy, meaning they don't know your password or anything that you're storing, not even including file names.

On the opposite end is the Dutch bank who shall remain nameless. You'd expect a bank to have higher requirements than most, but it seems not. 8-12 characters? And some characters excluded? What are they playing at? They don't even offer 2-factor authentication. It's just username and password (no, not even account details).

So, anyone know why these companies place such restrictions on passwords? Does it present a denial-of-service avenue? Do they not hash it in case they want to provide the password to, say, the NSA? Any good reason?

Saturday 7 January 2012

With a Little Help by Cory Doctorow

Look what arrived today (apologies for the poor quality pictures you're about to see).


Yes, something wrapped in a coffee burlap bag.


Which contains a rectangular hexahedron wrapped in rice paper sealed with stickers.


It's a book! A quality-looking hardback book emblazoned with a shiny faceless figure donning a cape and glasses, and there's a strip of bright orange extending from the spine of the book. There's also a 4GB SDHC memory card stuck to the front.


As you can see it's the special edition of With a Little Help by Cory Doctorow. This book is number 76 of 250. Let's have a look inside...


We appear to have a letter addressed to "Bear" from Melissa Frain from Tor (no, not the Tor project, Tor the sci-fi folk). Bear? Anyway, it's a request for the review of a manuscript. And at the back we have...


A sketch of what appears to be either a woman with a birthday cake stuck to her head, or a fairytale princess or some such variety of character.

And lastly, we have...


A personal inscription from Doctorow himself. Naturally his message alludes to the fact that he would be nothing without my help, and he's eternally grateful. (or something slightly less insane)

If you don't already know, Cory Doctorow (a Torontonian resident in the UK), amongst many other things, is a science fiction writer. This book is available for free in audio book format, on CD (MP3 or Ogg Vorbis) for a nominal fee, in several ebook formats (with a suggested donation) in paperback through on-demand publishing, or this gorgeous, unique limited edition hand-bound book. There's even the option to offer to give a book/books to institutions such as schools, prisons, hospitals etc.

So if you can get it for free, why the hell would you fork out money for it? Well for a start, it's directly re-numerating the author for the hard work they put in to make the publication you've enjoyed. That support means they'll go on to write more in future, and also affords them luxuries such as paying their energy bill and buying food to stave off death. But then the hardback book costs a fair amount. What are you getting for your money? Well, by its very nature, a limited edition item is... limited. And this one is particularly limited. The book I'm looking at is one of only 250. A personal inscription from the author isn't something you'll get with the vast majority of books (and he even spelled my name correctly). And then there's the end-papers. No-one else will have the same book as the one I have here as it has unique pieces of Cory's paper hoard that he didn't want to just throw away, so he has immortalised them forever at both ends of the book. I'm intrigued as to what this sketch is about though.

But not only that, the SD card on the front contains all the audio book files (Ogg Vorbis and MP3) every format of the ebook, and all the covers. Incidentally, the SDHC card is merely stuck into a recess in the book with some Blu-Tack, so can be easily removed and replaced.

As for the actual book itself, it says on Cory's site that these are hand-bound at the Wyvern Bindery in Clerkenwell, London, and the printing of the book is done by a family-run company called Oldacres in Hatton Garden who have been around since 1897.

The book costs the same (including P&P) regardless of where you live, except being in the UK gave me the benefit of getting it next day.

Now that the book is in my hands, I'm very pleased with my purchase and would recommend it to anyone who appreciates both good sci-fi, and beautiful, high-quality, unique well-made books. Plus the money goes directly back to the writer instead of mostly eaten away through a complicated chain of publishers, distributors and third-party sellers.

One last thing: Cory added a "feature" you don't get with most books; the opportunity to submit typo corrections with the advantage being that the typo will be fixed with a credit to yourself in the next printing of the book.

If you would like your very own copy, go get it.