We recently started upgrading some of our databases on windows 64-bit to 9.2, in doing so we ran into a nasty issue we discovered when some of our backups were failing. The first time it happened, I chucked it up to a dirty PostgreSQL 8.4 database being restored to PostgreSQL 9.2.1. The second time it happened restoring a 9.1 database to 9.2.2, I thought, better look into this to see if there is a known issue. Low and behold I found this:
http://archives.postgresql.org/pgsql-bugs/2012-12/msg00091.php (Bug #7741). Apparently something to do with granting rights on Types.
As a workaround for this problem so our backups would work again was to delete the offending permissions from system tables. It's probably not the best way but only way we could think of, we delete the bad record in pg_default_acl and after that backup works without complaint.
-- there is no such thing as T for default priviledges that pg_dump understands based on
-- backup bad records just in case we need them again --SELECT*into zz_bad_pg_default_acl FROM pg_default_acl WHERE defaclobjtype ='T';
--deleteunknownrecords--DELETEfrom pg_default_acl WHERE defaclobjtype ='T';
If anyone else has further input on this, I'd be interested.
As Hitoshi Hirada mentioned already in PL/v8 is now the richest procedural language , PL/V8 1.3 has been recently released and some of the great new features are automatic conversion of json objects, and ability to write window functions. Not only does PL/V8 allow you to make the most of the native JSON support in PostgreSQL 9.2, but in many cases particularly numeric processing, the speed is much better than what you get with plpgsql. Someone asked us a while back about this and we are hoping to do a demonstration of pl/v8 for numeric processing where you can see the marked difference in speed and perhaps compare with something like PL/R that is also often used for array numeric processing.
For those who are on windows we've compiled PLv8 1.3 for windows 32-bit and windows 64-bit that work with PostgreSQL EDB installs. We haven't had a chance to thoroughly test them yet, so let us know if you find issues. We've saved the last versions we compiled under a different name since some things are not backward compatible between the PLv8 1.1 and 1.3 releases.
We are also hoping to making our windows binary download links more visible. For stop gap fix, we are tagging all the articles concerning things like FDW extensions, PostGIS, plv8 we've compiled for windows under winextensions and accessible from http://www.postgresonline.com/winextensions.php