== PostgreSQL Weekly News - April 13 2014 ==

== PostgreSQL Weekly News - April 13 2014 ==

There will be a Google hangout April 17, 8am GMT titled Postgres
Performance Diagnostics: Busting that slow running SQL.  RSVP:
https://plus.google.com/events/cm0roo9chi2s6p3afp679lhv1bk

Postgres Open 2014 will be in Chicago, IL, USA, September 17-19.  The
CfP is open!
http://postgresopen.org/2014/callforpapers/

== PostgreSQL Product News ==

Benetl 4.5, a free ETL tool for postgreSQL, released.
http://www.benetl.net

oracle_fdw 1.0.0 released.
https://github.com/laurenz/oracle_fdw

== PostgreSQL Jobs for April ==

http://archives.postgresql.org/pgsql-jobs/2014-04/threads.php

== PostgreSQL Local ==

PGCon 2014, the world-wide developer conference for PostgreSQL, will
be in Ottawa, Ontario, Canada May 20-24, 2014.
http://www.pgcon.org/2014/

The sixth PGDay Cubano be held on 13 and 14 October 2014 in Habana.
https://postgresql.uci.cu/?p=380

== 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@fetter.org, German language
to pwn@pgug.de, Italian language to pwn@itpug.org.  Spanish language
to pwn@arpug.com.ar.

== Applied Patches ==

Heikki Linnakangas pushed:

- Fix WAL replay bug in the new GIN incomplete-split code.  Forgot to
  set the incomplete-split flag on the left page half, in redo of a
  page split.  Spotted this by comparing the page contents on master
  and standby, after inserting/applying each WAL record.
  http://git.postgresql.org/pg/commitdiff/594bac42728fed67e05458633f35c2b1d7b38f1e

- Zero padding byte at end of GIN posting list.  This isn't strictly
  necessary, but helps debugging.
  http://git.postgresql.org/pg/commitdiff/38a2b95c342c0d3d261d525a74943a977725483e

- Fix hot standby bug with GiST scans.  Don't reset the rightlink of a
  page when replaying a page update record.  This was a leftover from
  pre-hot standby days, when it was not possible to have scans
  concurrent with WAL replay. Resetting the right-link was not
  necessary back then either, but it was done for the sake of
  tidiness.  But with hot standby, it's wrong, because a concurrent
  scan might still need it.  Backpatch all versions with hot standby,
  9.0 and above.
  http://git.postgresql.org/pg/commitdiff/7ca32e255ba4f7e55bbdbcf0df996ac62798672b

- Fix misc typos in comments.
  http://git.postgresql.org/pg/commitdiff/5b075ae893a2b6eb42188463bbb76c6a141370a8

- Fix a few more misc typos in comments.
  http://git.postgresql.org/pg/commitdiff/150a9df5288d2ba59a26767659a99c44b683fe8f

- Fix typo in comment.  Tomonari Katsumata
  http://git.postgresql.org/pg/commitdiff/787064cd0078a346cd744f832794e23136392efd

- Fix bugs in GIN "fast scan" with partial match.  There were a couple
  of bugs here. First, if the fuzzy limit was exceeded, the loop in
  entryGetItem might drop out too soon if a whole block needs to be
  skipped because it's < advancePast ("continue" in a while-loop
  checks the loop condition too). Secondly, the loop checked when
  stepping to a new page that there is at least one offset on the page
  < advancePast, but we cannot rely on that on subsequent calls of
  entryGetItem, because advancePast might change in between. That
  caused the skipping loop to read bogus items in the
  TbmIterateResult's offset array.  First item and fix by Alexander
  Korotkov, second bug pointed out by Fabrízio de Royes Mello, by a
  small variation of Alexander's test query.
  http://git.postgresql.org/pg/commitdiff/614167c6d7e98d4538c4546754b3c2dba480f71c

Robert Haas pushed:

- Assert that strong-lock count is >0 everywhere it's decremented.
  The one existing assertion of this type has tripped a few times in
  the buildfarm lately, but it's not clear whether the problem is
  really originating there or whether it's leftovers from a trip
  through one of the other two paths that lack a matching assertion.
  So add one.  Since the same bug(s) most likely exist(s) in the
  back-branches also, back-patch to 9.2, where the fast-path lock
  mechanism was added.
  http://git.postgresql.org/pg/commitdiff/315772e4ecf2e91f17a8e375afe8dff2c2174406

- Remove 'make clean' support for ipc_test.  I missed this in the
  previous commit; Tom Lane spotted my error.
  http://git.postgresql.org/pg/commitdiff/f235db03ff28ef3e5d13aa49d496cba5693217de

- Remove ipc_test.  This doesn't seem to be useful any more, and it's
  not really worth the effort to keep updating it every time relevant
  dependencies or calling signatures in the shared memory or semaphore
  code change.
  http://git.postgresql.org/pg/commitdiff/b8a721149b190dc09c00620f65cb85b190b83d45

- Add new to_reg* functions for error-free OID lookups.  These
  functions won't throw an error if the object doesn't exist, or if
  (for functions and operators) there's more than one matching object.
  Yugo Nagata and Nozomi Anzai, reviewed by Amit Khandekar, Marti
  Raudsepp, Amit Kapila, and me.
  http://git.postgresql.org/pg/commitdiff/0886fc6a5c75b294544263ea979b9cf6195407d9

- doc: Fix typo.  Ian Barwick
  http://git.postgresql.org/pg/commitdiff/02f65617eab3deb715428a3ed62ca4e7f56ceda3

- Get rid of the dynamic shared memory state file.  Instead of storing
  the ID of the dynamic shared memory control segment in a file within
  the data directory, store it in the main control segment.  This
  avoids a number of nasty corner cases, most seriously that doing an
  online backup and then using it on the same machine (e.g. to fire up
  a standby) would result in the standby clobbering all of the
  master's dynamic shared memory segments.  Per complaints from Heikki
  Linnakangas, Fujii Masao, and Tom Lane.
  http://git.postgresql.org/pg/commitdiff/11a65eed1637a05b03e174700799b024e104bfb4

- Fix silly oversight in patch to remove dsm state file.  I'm not sure
  if this is what's causing the Windows buildfarm members to get
  unhappy, but I don't think it can be helping anything...
  http://git.postgresql.org/pg/commitdiff/0c4ea7a309249064b7c2a8b9612ee00f570f14af

- Add missing include.  This is more cleanup from commit
  11a65eed1637a05b03e174700799b024e104bfb4.  Amit Kapila
  http://git.postgresql.org/pg/commitdiff/b0827320610c6e4a42b2747b354ed05f7c94a30d

Tom Lane pushed:

- Add an in-core GiST index opclass for inet/cidr types.  This
  operator class can accelerate subnet/supernet tests as well as
  btree-equivalent ordered comparisons.  It also handles a new network
  operator inet && inet (overlaps, a/k/a "is supernet or subnet of"),
  which is expected to be useful in exclusion constraints.  Ideally
  this opclass would be the default for GiST with inet/cidr data, but
  we can't mark it that way until we figure out how to do a more or
  less graceful transition from the current situation, in which the
  really-completely-bogus inet/cidr opclasses in contrib/btree_gist
  are marked as default.  Having the opclass in core and not default
  is better than not having it at all, though.  While at it, add new
  documentation sections to allow us to officially document
  GiST/GIN/SP-GiST opclasses, something there was never a clear place
  to do before.  I filled these in with some simple tables listing the
  existing opclasses and the operators they support, but there's
  certainly scope to put more information there.  Emre Hasegeli,
  reviewed by Andreas Karlsson, further hacking by me
  http://git.postgresql.org/pg/commitdiff/f23a5630ebc797219b62797f566dec9f65090e03

- Create infrastructure for moving-aggregate optimization.  Until now,
  when executing an aggregate function as a window function within a
  window with moving frame start (that is, any frame start mode except
  UNBOUNDED PRECEDING), we had to recalculate the aggregate from
  scratch each time the frame head moved.  This patch allows an
  aggregate definition to include an alternate "moving aggregate"
  implementation that includes an inverse transition function for
  removing rows from the aggregate's running state.  As long as this
  can be done successfully, runtime is proportional to the total
  number of input rows, rather than to the number of input rows times
  the average frame length.  This commit includes the core
  infrastructure, documentation, and regression tests using
  user-defined aggregates.  Follow-on commits will update some of the
  built-in aggregates to use this feature.  David Rowley and Florian
  Pflug, reviewed by Dean Rasheed; additional hacking by me
  http://git.postgresql.org/pg/commitdiff/a9d9acbf219b9e96585779cd5f99d674d4ccba74

- Provide moving-aggregate support for a bunch of numerical
  aggregates.  First installment of the promised moving-aggregate
  support in built-in aggregates: count(), sum(), avg(), stddev() and
  variance() for assorted datatypes, though not for float4/float8.  In
  passing, remove a 2001-vintage kluge in interval_accum(): interval
  array elements have been properly aligned since around 2003, but
  nobody remembered to take out this workaround.  Also, fix a thinko
  in the opr_sanity tests for moving-aggregate catalog entries.  David
  Rowley and Florian Pflug, reviewed by Dean Rasheed
  http://git.postgresql.org/pg/commitdiff/9d229f399e87d2ae7132c2e8feef317ce1479728

- Provide moving-aggregate support for boolean aggregates.  David
  Rowley and Florian Pflug, reviewed by Dean Rasheed
  http://git.postgresql.org/pg/commitdiff/d95425c8b9d3ea1681bd91b76ce73be95ca5ee21

- Suppress compiler warning in new contrib/pg_trgm code.  MSVC doesn't
  seem to like it when a constant initializer loses precision upon
  being assigned.  David Rowley
  http://git.postgresql.org/pg/commitdiff/46a60abfe9fa13087dbbe15953c20df35f006968

- Improve some O(N^2) behavior in window function evaluation.
  Repositioning the tuplestore seek pointer in window_gettupleslot()
  turns out to be a very significant expense when the window frame is
  sizable and the frame end can move.  To fix, introduce a tuplestore
  function for skipping an arbitrary number of tuples in one call,
  parallel to the one we introduced for tuplesort objects in commit
  8d65da1f.  This reduces the cost of window_gettupleslot() to O(1) if
  the tuplestore has not spilled to disk.  As in the previous commit,
  I didn't try to do any real optimization of tuplestore_skiptuples
  for the case where the tuplestore has spilled to disk.  There is
  probably no practical way to get the cost to less than O(N) anyway,
  but perhaps someone can think of something later.  Also fix
  PersistHoldablePortal() to make use of this API now that we have it.
  Based on a suggestion by Dean Rasheed, though this turns out not to
  look much like his patch.
  http://git.postgresql.org/pg/commitdiff/e0c91a7ff015fab0ccbb0f75b6819f29ae00295e

Michael Meskes pushed:

- Several fixes to array handling in ecpg.  Patches by Ashutosh Bapat
  <ashutosh.bapat@enterprisedb.com>
  http://git.postgresql.org/pg/commitdiff/f9179685371b74bf4752bf3f87846e5625cf91fa

- The new pointer_to_struct test needs a special command line
  parameter to ecpg.  Hopefully this will fix the buildfarm failures
  on Windows.
  http://git.postgresql.org/pg/commitdiff/ef29a884575f233e6cd382dcfb42af602fa78cd6

Bruce Momjian pushed:

- docs:  add link to pg_start_backup() from pg_basebackup
  --checkpoint.  This references the meaning of the fast/spread
  checkpoint option.  Per private IM report
  http://git.postgresql.org/pg/commitdiff/85c6bba2ce37761fb5e5d21fb5d53cae3d6861f9

- C comment:  track_activity_query_size doesn't support memory units.
  And explain why.  Per report from Pavel Stehule
  http://git.postgresql.org/pg/commitdiff/8fcccadfea491a1074be38266e2346162faa03e3

- docs: psql '--' comments are not passed to the server.  C-style
  block comments are passed to the server.
  http://git.postgresql.org/pg/commitdiff/3c41b812c5578fd7bd5c2de42941012d7d56dde2

Stephen Frost pushed:

- Make security barrier views automatically updatable.  Views which
  are marked as security_barrier must have their quals applied before
  any user-defined quals are called, to prevent user-defined functions
  from being able to see rows which the security barrier view is
  intended to prevent them from seeing.  Remove the restriction on
  security barrier views being automatically updatable by adding a new
  securityQuals list to the RTE structure which keeps track of the
  quals from security barrier views at each level, independently of
  the user-supplied quals.  When RTEs are later discovered which have
  securityQuals populated, they are turned into subquery RTEs which
  are marked as security_barrier to prevent any user-supplied quals
  being pushed down (modulo LEAKPROOF quals).  Dean Rasheed, reviewed
  by Craig Ringer, Simon Riggs, KaiGai Kohei
  http://git.postgresql.org/pg/commitdiff/842faa714c0454d67e523f5a0b6df6500e9bc1a5

- Make a dedicated AlterTblSpcStmt production.  Given that ALTER
  TABLESPACE has moved on from just existing for general purpose
  rename/owner changes, it deserves its own top-level production in
  the grammar.  This also cleans up the RenameStmt to only ever be
  used for actual RENAMEs again- it really wasn't appropriate to hide
  non-RENAME productions under there.  Noted by Alvaro Herrera.
  http://git.postgresql.org/pg/commitdiff/5f508b6dea19b66961c645bf5e5c427ac3af8359

- Add ANALYZE into regression tests.  Looks like we can end up with
  different plans happening on the buildfarm, which breaks the
  regression tests when we include EXPLAIN output (which is done in
  the regression tests for updatable security views, to ensure that
  the user-defined function isn't pushed down to a level where it
  could view the rows before the security quals are applied).  This
  adds in ANALYZE to hopefully make the plans consistent.  The ANALYZE
  ends up changing the original plan too, so the update looks bigger
  than it really is.  The new plan looks perfectly valid, of course.
  http://git.postgresql.org/pg/commitdiff/b3e6593716efef901fcc847f33256c6b49958898

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Michael Paquier sent in two more revisions of a patch to include
replication slot data in base backups.

Ian Lawrence Barwick sent in three revisions of a patch to add psql
tab completion for event triggers.

Vaishnavi Prabakaran sent in a patch to add a new option in
pg_basebackup to exclude pg_log files during base backup.

Rajeev Rastogi sent in two revisions of a patch to add autonomous
transactions.

Etsuro Fujita sent in two revisions of a patch to improve the ALTER
TABLE documentation.

Bruce Momjian sent in another revision of a patch to fix a socket
issue on Windows.

Simon Riggs sent in a patch to implement ALTER TABLE ... SET
reloptions.

Amit Kapila sent in a patch to fix a dsm invalid errcode issue.

Bruce Momjian sent in another revision of a patch to fix the oid
display in psql's \d+.

Kyotaro HORIGUCHI sent in another revision of a patch to get more from
indexes.

Sergey Muraviov and Gregory Stark traded patches to improve the
display of wide tables in psql.

MauMau sent in another revision of a patch to fix an issue where
pg_ctl always uses the same event source.

Andres Freund sent in a patch to add pinning_backends column to the
pg_buffercache extension.

David Rowley sent in a patch to add a window function optimization
which allows pushdowns of items matching PARTITION BY clauses.

Jan Wieck sent in two revisions of a patch to ensure that a snapshot's
txids are unique.