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.

12 comments:

Peter Geoghegan said...

Hi Thom. Good post, but I would have liked to have seen "time travel" mentioned, if only as a historic curiosity.

Thom said...

Yes, there will be things I haven't mentioned. Time travel is ancient, in that it was dropped way back in 6.2 when we used punch cards and had to replace valves. ;)

Thom said...

There you go, updated it with time travel. :D

Greg Jaskiewicz said...

There's a 'new one' coming in 9.2, language parameter you pass on to function definition is now not going to work as it did 9.1 and previous.

Thom said...

@Greg

I take it you're referring to processing literal language names case-sensitively? Yes, good point. I had forgot about that. I'll add it in. Thanks.

Josh Berkus said...

Nice, this is a terrific resource. Thanks for compiling it! Do you think you could copy it to the PostgreSQL wiki?

You might want to check your RSS feed, though; it's full of HTML garbage which is making your blog unreadable on Planet Postgres.

Thom said...

Thanks Josh. The garbage you're referring to is a custom CSS block I put in place to lay out the page into an easier to read format. But I neglected to use commented CDATA markup, which I've now put in place. I'll have to remember that next time. :)

But as per your request, I've added it to the wiki: http://wiki.postgresql.org/wiki/Deprecated_Features

Feel free to tidy it up or add anything.

Bruce Momjian said...

Wow, what a great trip back through time --- thanks. That had to take a lot of work.

Thom said...

Thanks Bruce. I could have been much more extensive and covered
- pginterface
- zap_ltv
- datetime (as a module that provides functions)
- apache_logging
- int8 (yes, first time 64-bit integers were allowed, but relegated to a contrib module)
- sequence contrib module (sequence functions)
- "misc utils" contrib module
- retep (Peter Mount's own utilities... whatever happened to Peter?)
- erServer module (replication tools)

... and loads more. :)

The history of Postgres is quite interesting, especially to see how it evolved technically over time. For example, I hadn't noticed that Postgres95 was ported to Windows NT (or at least attempted). And thankfully the git repo logs contain *everything*...

Postgres95 Beta 0.01 (Mon May 1 19:03:10 PDT 1995)

:D

And Bruce, I see your first patch submission back in July 1996. And a presentation you write back in 2006 about its history: http://momjian.us/main/writings/pgsql/decade.pdf

Speaking of which, were there thoughts for Postgres's 20th anniversary in 3 years?

Rowan Collins said...

I was reading through this, thinking "hm" and "heh" and "OK, not even sure what that means" and then I got to this at the very end:

custom_variable_classes
Considered better to remove it as it only causes more maintenance with minimal benefit.

NOOOOOOOOOOO!

Those are essential to my pg_env functions (as in http://rwec.co.uk/pg-atom/env.html but the non-performance-sucking versions that I haven't posted online yet but are in use on production sites 8/)

Please tell me there's some other way to make a custom session-persistent variable? And no, I won't count pl/perl and its weird global hash, whose name I can't remember this minute :P

Or am I completely missing the point, and there's a permanently available namespace I can dump my variables in now?

If not, I guess I'll have to get down and write a proper extension to reimplement them as "environment variables", if no-one's done so yet...


Rowan

Thom said...

It's not what you think. Variables prefixed with a class name will no longer be validated against such a list, so you no longer have to worry about defining your class beforehand.

So from 9.2, feel free to use:

SET random_undefined_class.gibberish_setting = 'meow';

Rowan Collins said...

Aha! Perfect! I shall sleep soundly tonight. ;)

And, um, get round to uploading the fixes to those env and i18n functions at some point.

Oh, and figuring out what is actually up with overloading operators on custom domain types, and when it broke, and why. Currently have to use ||| rather than ||, which is really fiddly and annoying.

Interesting to see the => operator being moved towards reserved status. I actually went for ==> in the end, to avoid conflicts with hstore.