== PostgreSQL Weekly News - January 09 2011 ==

== PostgreSQL Weekly News - January 09 2011 ==

AustinPUG will be meeting this Wednesday, Jan. 12. The topic will be
setting up streaming replication. Free pizza! RSVP to austinpug AT
postgresql DOT org appreciated but not required.
http://pugs.postgresql.org/austinpug

Selena Deckelmann will be speaking on PostgreSQL Community and
Development February 7, 2010, 4pm at Oregon State University in
Corvallis, OR.

Open Database Camp will be on May 7-9, 2011 in Sardinia, Italy
http://datacharmer.blogspot.com/2011/01/announcing-open-database-camp-sardinia.html

== PostgreSQL Product News ==

PostgreSQL LiveCD based on PostgreSQL 9.0.2 atop CentOS 5.5 is out.
http://www.pglivecd.org/

phpPgAdmin 5.0.2, a web-based administrative tool for PostgreSQL, released.
http://phppgadmin.sourceforge.net/

== PostgreSQL Jobs for January ==

http://archives.postgresql.org/pgsql-jobs/2011-01/threads.php

== PostgreSQL Local ==

The Call for Papers for the fourth annual Prague PostgreSQL
Developers' Day is open. It will be held February 10, 2011 at Charles
University in Prague.
http://archives.postgresql.org/pgsql-announce/2010-12/msg00009.php

The PostgreSQLFr Call for Projects is open. Projects must target
PostgreSQL and the French-speaking community. Mail appel-projets-2010
AT postgresql DOT fr.
http://www.postgresql.fr/appel_a_projets_2010:call_for_projects

PGDay is scheduled at this year's Southern California Linux Exposition
(SCALE) held in the LAX Hilton Hotel in the city of Los Angeles,
California, on Friday February 25th, 2011. Post your talk submission
to pgday-submissions AT googlegroups DOT com.

PostgreSQL Conference East 2011: NYC March 22nd - 25th
http://www.postgresqlconference.org

PGCon will be May 19-20, 2011 at the University of Ottawa, preceded by
two days of tutorials on May 17-18. The Call for Papers is open!
http://www.pgcon.org/2011/

== PostgreSQL in the News ==

Planet PostgreSQL: http://planet.postgresql.org/

PostgreSQL Weekly News is brought to you this week by David Fetter

Submit news and announcements by Sunday at 3:00pm Pacific time.
Please send English language ones to david(at)fetter(dot)org, German language
to pwn(at)pgug(dot)de, Italian language to pwn(at)itpug(dot)org(dot) Spanish language
to pwn(at)arpug(dot)com(dot)ar(dot)

== Reviews ==

Pavel Stehule reviewed the MULTISET patch.

== Applied Patches ==

Magnus Hagander pushed:

- perltidy run on the MSVC build system. Forgot this with previuos
commit, line it up so it's easier to submit (readable) patches
against the MSVC build system.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=c0e96b49e588b2a5ab501a2acc03b96ff76cf288

- Add missing part of replication role docs. Noted by Peter Eisentraut.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=1996b482859c67726e77b80a263d3cce954e022d

- Add views and functions to monitor hot standby query conflicts. Add
the view pg_stat_database_conflicts and a column to
pg_stat_database, and the underlying functions to provide the
information.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=40d9e94bd7537144d3d379c1fd1264dff5cf4bb2

- Bump catversion, forgot in previous commit.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=77745cc7f1617852844dd88ec8ddb48f38e19e9a

- Give superusers REPLIACTION permission by default. This can be
overriden by using NOREPLICATION on the CREATE ROLE statement, but
by default they will have it, making it backwards compatible and
"less surprising" (given that superusers normally override all
checks).
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=66a8a0428db046d725b88c8b956384c2a4b4a11c

- Add pgreadlink() on Windows to read junction points. Add support
for reading back information about the symbolic links we've created
with pgsymlink(), which are actually Junction Points. Just as
pgsymlink() can only create directory symlinks, pgreadlink() can
only read directory symlinks.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=db4d22d0eff091e96d92c82566c9e9d297040b93

- Properly install gram.h on MSVC builds. This file is now needed by
pgAdmin builds, which started failing since it was missing in the
installer builds.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=3457514c2d9bac552d4caeb1d3ac5a8d03d3a439

- Ensure the directory for gram.h is created on win32. Result of bad
testing of my last commit.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=361418be7c23e236d07edf4052de85ab8f32d88d

- Split pg_start_backup() and pg_stop_backup() into two pieces Move
the actual functionality into a separate function that's easier to
call internally, and change the SQL-callable function to be a
wrapper calling this. Also create a pg_abort_backup() function,
only callable internally, that does only the most vital parts of
pg_stop_backup(), making it safe(r) to call from error handlers.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=4448917d51d6f87655ce357cca009634b0a136e4

Robert Haas pushed:

- Update comments in RecordTransactionCommit() to mention unlogged tables.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=dc8a14311a4825b6bf7b787764b9f0c3f8d8df8e

- Fix crash in ALTER OPERATOR CLASS/FAMILY .. SET SCHEMA. In the
previous coding, the parser emitted a List containing a C string,
which is no good, because copyObject() can't handle it. Dimitri
Fontaine.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=7f60be72b0865dd442c4f6164a997ff528a44d85

- Improve recovery.conf.sample comments. Jehan-Guillaume de Rorthais,
with some additional wordsmithing by me.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=a9f72b408325c4abb41b5ab65f8fb79af4c29495

- Document pg_stat_replication, bump catversion since that was
overlooked. Itagaki Takahiro, edited by me.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=9b4271deb97270d336c9d34ac911748faa5a4892

- Remove bogus claims regarding createuser defaults. Josh Kupershmidt.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=af84bee43e356866dc46c95ae5bf77996a8237ff

ITAGAKI Takahiro pushed:

- Improve psql tab completion for CREATE/ALTER ROLE [NO]REPLICATION.
Missing support for VALID UNTIL in CREATE ROLE is also added.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=14158f25cd164d65cf174dab9e22b3b4a22962a7

- New system view pg_stat_replication displays activity of wal sender
processes. Itagaki Takahiro and Simon Riggs.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=a755ea33ae3d772977fa083a8614bb9d4cc36e5a

Andrew Dunstan pushed:

- Work around header misdefines in modern Windows SDK when
_WIN32_WINNT is less than 0x0501. Only required for versions 8.2,
8.3 and 8.4., as we defined _WIN32_WINNT as 0x0501 after that.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=1d1a43422252576e9a7fa07d8dbfea9c724aa75b

- Allow older branches to be built with Visual Studio 2008. This is a
backport of commit df0cdd53 to the 8.2, 8.3 and 8.4 branches.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=5739935188ca27b5b6e63d72748af733b5813aa9

Bruce Momjian pushed:

- Simplify functions and parameters used by pg_upgrade.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=25cc7424e3b4d3bd76fa54bfc6907f294b4b99d1

- In pg_upgrade, fix backward logging display of link operations.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=0857a8449826867711a9c1f99088bd13731ac192

- In pg_upgrade, copy pg_largeobject_metadata and its index for 9.0+
servers because, like pg_largeobject, it is a system table whose
contents are not dumped by pg_dump --schema-only.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=3302334b48e2be6eb2c01dcf500363dbd4f22e59

- Clarify pg_upgrade's creation of the map file structure. Also clean
up pg_dump's calling of pg_upgrade_support functions.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=5cff5b577918cf29e0ba3a35158c27cfe2de280c

- In pg_upgrade, adjust transfer_relfile()'s parameters to be more logical.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=b1f2a94fa2bebbc74d392a18e4ac1d1e558485fc

- Fix for recent pg_upgrade commit to fix large objects relfilenode
handling. (metadata user ids still an open issue).
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=a45beaac642147ebf2e561cce62967d244c343c6

- Update pg_upgrade C comments.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=e66e43988a334ca58c584a0652d29dff61e698b9

- Rename pg_upgrade variables, for clarity.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=56a9adcb96ead8569582aa1f0f77a8fa9e24c189

- Update documentation to say that \lo_import sets :LASTOID, not lo_insert.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=1aad44f988dbebbdde986478f25096037c6dd931

- Improve C comments about backend variables set by pg_upgrade_support
functions.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=46d28820b6d213bf26b24ecd289a1c466ecf1956

- Force pg_upgrade's to preserve pg_class.oid, not
pg_class.relfilenode. Toast tables have identical pg_class.oid and
pg_class.relfilenode, but for clarity it is good to preserve the
pg_class.oid. Update comments regarding what is preserved, and do
some variable/function renaming for clarity.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=2896c87ce4dc789722296f010eaefd5cbb86ece3

- Fix pg_upgrade of large object permissions by preserving
pg_auth.oid, which is stored in pg_largeobject_metadata. No
backpatch to 9.0 because you can't migrate from 9.0 to 9.0 with the
same catversion (because of tablespace conflict), and a pre-9.0
migration to 9.0 has not large object permissions to migrate.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=d8d3d2a4f37f6df5d0118b7f5211978cca22091a

- Fix C indenting in pg_upgrade function.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=519c008a966251e45d683738d89f238e5c801781

- In pg_upgrade, track only one copy of namespace/relname in
FileNameMap because the old and new values are identical.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=f75383e6e8263cd98582edb0b3884132ad9e368a

- Simplify pg_upgrade C comment about what is preserved.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=3095643d374ca79e5aa347cefbaebdcf70169386

- More pg_upgrade relfilenode C comments added.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=2f657e4d720d08b26234f3b9e40e347f3cc42220

- Document why pg_upgrade preserves pg_auth.oid (because they are
stored in pg_largeobject_metadata).
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=133641a48eb9a3406fda7014a60fd42dfecfa089

- In pg_upgrade, remove unnecessary separate handling of toast tables
now that we restore by oid; they can be handled like regular tables
when creating the file mapping structure.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=a60b32b3dcb4d49a60c96558ce405764ac17c799

- In pg_upgrade, clarify use of install_db_support_functions().
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=ebaf648ad2e471b2e2003ecfe4740ce27226c02a

- In pg_upgrade, remove functions that did sequential array scans
looking up relations, but rather order old/new relations and use the
same array index value for both. This should speed up pg_upgrade
for databases with many relations.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=002c105a0706bd1c1e939fe0f47ecdceeae6c52d

Tom Lane pushed:

- Update sequence_1.out for recent changes in sequence regression test.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=6c596c29a3666830d089facf9d6c7f44b4d0e9f2

- Fix GIN to support null keys, empty and null items, and full index
scans. Per my recent proposal(s). Null key datums can now be
returned by extractValue and extractQuery functions, and will be
stored in the index. Also, placeholder entries are made for
indexable items that are NULL or contain no keys according to
extractValue. This means that the index is now always complete,
having at least one entry for every indexed heap TID, and so we can
get rid of the prohibition on full-index scans. A full-index scan
is implemented much the same way as partial-match scans were
already: we build a bitmap representing all the TIDs found in the
index, and then drive the results off that. Also, introduce a
concept of a "search mode" that can be requested by extractQuery
when the operator requires matching to empty items (this is just as
cheap as matching to a single key) or requires a full index scan
(which is not so cheap, but it sure beats failing or giving wrong
answers). The behavior remains backward compatible for opclasses
that don't return any null keys or request a non-default search
mode. Using these features, we can now make the GIN index opclass
for anyarray behave in a way that matches the actual anyarray
operators for &&, <@, @>, and = ... which it failed to do before in
assorted corner cases. This commit fixes the core GIN code and
ginarrayprocs.c, updates the documentation, and adds some simple
regression test cases for the new behaviors using the array
operators. The tsearch and contrib GIN opclass support functions
still need to be looked over and probably fixed. Another thing I
intend to fix separately is that this is pretty inefficient for
cases where more than one scan condition needs a full-index search:
we'll run duplicate GinScanEntrys, each one of which builds a large
bitmap. There is some existing logic to merge duplicate
GinScanEntrys but it needs refactoring to make it work for entries
belonging to different scan keys. Note that most of gin.h has been
split out into a new file gin_private.h, so that gin.h doesn't
export anything that's not supposed to be used by GIN opclasses or
the rest of the backend. I did quite a bit of other code
beautification work as well, mostly fixing comments and choosing
more appropriate names for things.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=73912e7fbd1b52c51d914214abbec1cda64595f2

- Fix the built-in GIN support procedure declarations in pg_proc.h.
Add more "internal" arguments so that these pg_proc entries reflect
the current preferred API. This is purely a cosmetic change, since
GIN doesn't actually consult the pg_proc entry when calling a
support function. Accordingly, no catversion bump.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=a032d50128f30029651806030f71d594ed6b29e1

- Ooops, forgot to fix contrib/tsearch2's references to GIN support funcs.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=541fc3d4df66a5e51d172a78288069bd92dda0c8

- Update GIN support function definitions for contrib/intarray. The
underlying C code still needs work, but this at least gets its
current regression test passing again.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=69c26c57cfb4194b8cdc69b48b8a71dad1f92bac

- Refactor GIN's handling of duplicate search entries. The original
coding could combine duplicate entries only when they originated
from the same qual condition. In particular it could not combine
cases where multiple qual conditions all give rise to full-index
scan requests, which is an expensive case well worth optimizing.
Refactor so that duplicates are recognized across all the quals.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=56a57473a999b0497e63bde3e303beda5a3c0ff3

- Remove pg_am.amindexnulls. The only use we have had for
amindexnulls is in determining whether an index is safe to cluster
on; but since the addition of the amclusterable flag, that usage is
pretty redundant. In passing, clean up assorted sloppiness from the
last patch that touched pg_am.h: Natts_pg_am was wrong, and
ambuildempty was not documented.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=7e2f906201c8bb95f7fb17e56b8740c38bda5441

- Add array_contains_nulls() function in arrayfuncs.c. This will
support fixing contrib/intarray (and probably other places) so that
they don't have to fail on arrays that contain a null bitmap but no
live null entries.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=adf328c0e1bfde90b944d53f7197fc436bc0c707

- Fix up gincostestimate for new extractQuery API. The only reason
this wasn't crashing while testing the core anyarray operators was
that it was disabled for those cases because of passing the wrong
type information to get_opfamily_proc :-(. So fix that too, and
make it insist on finding the support proc --- in hindsight,
silently doing nothing is not as sane a coping mechanism as all
that.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=4d1b76e49eb848b046ddb1beb0f4589816ec8261

- Fix assorted corner-case bugs in contrib/intarray. The array
containment operators now behave per mathematical expectation for
empty arrays (ie, an empty array is contained in anything). Both
these operators and the query_int operators now work as expected in
GiST and GIN index searches, rather than having corner cases where
the index searches gave different answers. Also, fix unexpected
failures where the operators would claim that an array contained
nulls, when in fact there was no longer any null present (similar to
bug #5784). The restriction to not have nulls is still there, as
removing it would take a lot of added code complexity and probably
slow things down significantly. Also, remove the arbitrary
restriction to 1-D arrays; unlike the other restriction, this was
buying us nothing performance-wise. Assorted cosmetic improvements
and marginal performance improvements, too.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=fdf2dbda3f49310b20780ad7b290da935cd2335d

- Use array_contains_nulls instead of ARR_HASNULL on user-supplied
arrays. This applies the fix for bug #5784 to remaining places
where we wish to reject nulls in user-supplied arrays. In all these
places, there's no reason not to allow a null bitmap to be present,
so long as none of the current elements are actually null. I did
not change some other places where we are looking at system catalog
entries or aggregate transition values, as the presence of a null
bitmap in such an array would be suspicious.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=304845075ce0eb9045af50ed32c688a0cb8dd490

- Fix up core tsquery GIN support for new extractQuery API. No need
for the empty-prefix-match kluge to force a full scan anymore.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=52fd2d65a33c9c33b29788e9df89d7716f1ec0bc

- Fix crash in the new GiST insertion code, when an update splits the
root page. This bug was exercised by contrib/intarray/bench, as
noted by Tom Lane.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=ca63029eac379d82f78a985a5d4068b9954deb02

- Improve comment.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=327b25768217f5d4fd82770d0ab449814548f094

- Update contrib/hstore for new GIN extractQuery API. In particular,
make hstore @> '' succeed for all hstores, likewise hstore ?& '{}'.
Previously the results were inconsistent and could depend on whether
you were using a GiST index, GIN index, or seqscan.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=ba398969cd1b32c550345bcf6b713deeb0a7d1de

- Update contrib/pg_trgm for new GIN extractQuery API. No actual
change in functionality ... just get rid of uselessly complex code
to pass the number of keys via extra_data.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=be0c3ea2d30ba225f0249ae88d6b0bdf3b753162

Peter Eisentraut pushed:

- Improve array_upper example. The previous example didn't make it
clear whether array_upper returned the last element or the index of
the last element.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=f9e4961b7d599ff42accf324a3c080c5095785c9

- Improve markup of unnest example.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=7f40e30309b5c8f19b1ac86c463ab0ea99907873

Michael Meskes pushed:

- In ecpg's parser removed a fixed length limit for constants defining
an array dimension.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=833a2b57bcbebda29fc1eb0b98c0d94ae2c31b7b

- There is no need to have to identical functions in ecpg thus
removing one of them.
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=1066dbfb85462116e4ed3d889a686f1e48333467

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Christian Ullrich sent in a patch which allows for SSPI client
authentication on non-Windows builds.

Brar Piening sent in another revision of the patch to add Visual
Studio 2010/Windows SDK 7.1 support.

Andrew Dunstan sent in a patch to allow MSVC 2008 to be used on back
branches.

Jeff Davis sent in a WIP patch to implement RANGE types.

ITAGAKI Takahiro sent in two patches to implement pg_stat_walsender.
The first is a generic formatter for host and port. The second adds
the actual system view.

Dimitri Fontaine sent in another revision of the patch which allows
pg_dump to support EXTENSIONs.

Dimitri Fontaine sent in a patch to allow upgrading extensions.

Simon Riggs sent in a patch which implements
pg_stat_replication_activity in response to ITAGAKI Takahiro's
pg_stat_walsender patch.

Shigeru HANADA sent in a patch which removes the requirement that FDWs
have USAGE privilege in SQL/MED. This is per the standard.

Shigeru HANADA sent in a pair of patches which implement fdw_handler
and foreign_scan, both for SQL/MED.

KaiGai Kohei sent in another revision of the patch to add sepgsql as a
contrib module.

Magnus Hagander sent in a patch to implement streaming base backups.

ITAGAKI Takahiro sent in another revision of the patch to add
MULTISET.

Gurjeet Singh sent in another revision of the patch to add a UNIQUE
constraint using extant indexes.

Tom Lane sent in a patch to fix GIN for empty/null/full-scan cases.

ITAGAKI Takahiro sent in another version of the patch to export some
of the COPY functions for use in, among other things, foreign
data wrappers for files.

Joachim Wieland sent in another revision of the patch to export
snapshots.

Joachim Wieland sent in another revision of the patches to do
directory-format parallel pg_dump.

Heikki Linnakangas sent in a patch to give streaming base backups a
bison grammar.

Magnus Hagander sent in an updated patch to stream base backups.

Alexander Korotkov sent in another revision of the patch to add
wildcards to the trigram contrib module.

Andreas Karlsson sent in a patch to add an obj_unique_identifier()
function.

Noah Misch sent in a flock of patches intended to make the process of
altering a column's type more efficient.