David Fetter | 5 Oct 02:31 2015

== PostgreSQL Weekly News - October 04 2015 ==

== PostgreSQL Weekly News - October 04 2015 ==

PostgreSQL <at> SCaLE is a two day, two track event which takes place on Jan.
21-22, 2016, at Pasadena Convention Center, as part of SCaLE 14X.
The CfP is open until Oct 30, 2015.

== PostgreSQL Product News ==

barman 1.5.0, a backup and recovery manager for PostgreSQL, released.

== PostgreSQL Jobs for October ==


== PostgreSQL Local ==

PGDay.IT 2015 will take place in Prato on October 23, 2015.

PostgreSQL Conference Europe 2015 will be held on October 27-30 in the
Vienna Marriott Hotel, in Vienna, Austria.  The schedule is posted.

PGConf Silicon Valley 2015 is November 17-18 at the South San
Francisco Convention Center.

PgCUBA will take place October 19-23, 2015 in Havana.  Spanish
(Continue reading)

Joe Conway | 1 Oct 20:42 2015

PostgreSQL <at> SCaLE: Call for Presentations

PostgreSQL <at> SCaLE: Call for Presentations

PostgreSQL Users –
PostgreSQL <at> SCaLE is a two day, two track event which takes place on Jan.
21-22, 2016, at Pasadena Convention Center, as part of SCaLE 14X.

SCaLE is a completely volunteer-driven event, and in the last decade it
has grown into the largest volunteer-run Linux/FOSS event in North
America. SCaLE 14X takes place on Jan. 21-24, 2016.

We’re looking for presentations surrounding experiences, tools and
technical details of how you use PostgreSQL.

Upcoming Deadline for Submissions is: 30 Oct, 2015

Who Should Submit?
Anyone and everyone in the community is encouraged to submit! Presenters
should be executives, team leaders, engineers or architects (not sales
or marketing staff). If you’re working with PostgreSQL, we want to hear
from you.

Interested? Here’s what to do next:
Simply go to the SCaLE website, create or login to your account, and
walk through the steps of the CFP interface:


(Continue reading)

Giulio Calacoci | 28 Sep 09:40 2015

Barman 1.5.0 released

28 September 2015: 2ndQuadrant is proud to announce the release of
version 1.5.0 of Barman, Backup and Recovery Manager for PostgreSQL.

This major release features the get-wal command, which turns Barman into
a WAL mine from which you can extract any transaction log file you need.
Barman can now be easily integrated with any PostgreSQL standby server
as a fallback method for WAL synchronisation.

Another important new feature is retry hook scripts that can be executed
before or after a backup command or the archive of a WAL file.
Differently from a standard hook script, already available in Barman, a
retry hook script is executed indefinitely until it succeeds.

With version 1.5.0, Barman enhances its robustness, by adding checks on
wal_level settings, by forbidding the deletion of a running backup, by
requesting a successful check before initiating a backup, etc.

Barman now checks for conflicting paths, making sure that the same
directory cannot be shared by more than a target (i.e. base backups, WAL
archive, incoming directory, etc.), even across different servers within
the same installation.

In terms of user experience, the active option grants users to
temporarily disable a server in Barman, for operational purposes, while
still accessing its catalogue.

Minor bugs have also been fixed.

For a complete list of changes, see the "Release Notes" section below.

Features in detail

Barman get-wal
    No more standby servers that fall out of synchronisation with the
    master server, thanks to Barman's get-wal command. Design complex HA
    and DR architectures of PostgreSQL databases, knowing that you can
    now rely on an 'infinite' source of WAL files for any of your
    servers. Look at the barman-wal-restore script for an idea on how
    you can integrate get-wal with your PostgreSQL standby servers.

Retry hook scripts
    You can now extend Barman by plugging your custom scripts before and
    after taking a backup operation or archiving a WAL file, knowing
    that Barman will repeat them until they succeed. You can now build
    your own pipeline around Barman, for example by storing a WAL in the
    cloud once it reaches Barman.


-   Man page, section 1: http://docs.pgbarman.org/barman.1.html
-   Man page, section 5: http://docs.pgbarman.org/barman.5.html

Release notes

-   Add support for the get-wal command which allows users to fetch any
    WAL file from the archive of a specific server
-   Add support for retry hook scripts, a special kind of hook scripts
    that Barman tries to run until they succeed
-   Add active configuration option for a server to temporarily disable
    the server by setting it to False
-   Add barman_lock_directory global option to change the location of
    lock files (by default: 'barman_home')
-   Execute the full suite of checks before starting a backup, and skip
    it in case one or more checks fail
-   Forbid to delete a running backup
-   Analyse include directives of a PostgreSQL server during backup and
    recover operations
-   Add check for conflicting paths in the configuration of Barman, both
    intra (by temporarily disabling a server) and inter-server (by
    refusing any command, to any server).
-   Add check for wal_level
-   Add barman-wal-restore script to be used as restore_command on a
    standby server, in conjunction with barman get-wal
-   Implement a standard and consistent policy for error management
-   Improved cache management of backups
-   Improved management of configuration in unit tests
-   Tutorial and man page sources have been converted to Markdown format
-   Add code documentation through Sphinx
-   Complete refactor of the code responsible for managing the backup
    and the recover commands
-   Changed internal directory structure of a backup
-   Introduce copy_method option (currently fixed to rsync)
-   Bug fixes:
    -   Manage options without '=' in PostgreSQL configuration files
    -   Preserve Timeline history files (Fixes: #70)
    -   Workaround for rsync on SUSE Linux (Closes: #13 and #26)
    -   Disables dangerous settings in postgresql.auto.conf (Closes:
    -   Fixed error in WAL rate calculation


-   Release Notes:
-   Sources:
-   RPMs for RHEL/CentOS 5:
-   Barman:
-   Dependencies:
-   RPMs for RHEL/CentOS 6:
-   Barman:
-   Dependencies:
-   pgespresso on PostgreSQL Extension framework (PGXN):
-   pgespresso RPM/Debian packages:
-   Online documentation: http://www.pgbarman.org/documentation
-   PDF documentation:


Barman (Backup and Recovery Manager) is an open source administration
tool for disaster recovery of PostgreSQL servers written in Python. It
allows your organisation to perform remote backups of multiple servers
in business critical environments and help DBAs during the recovery
phase. Barman's most requested features include backup catalogues,
incremental backup, retention policies, remote backup and recovery,
archiving and compression of WAL files and backups. 
Barman is distributed under GNU GPL 3.

 Giulio Calacoci - 2ndQuadrant Italia
 PostgreSQL Training, Services and Support
 giulio.calacoci <at> 2ndQuadrant.it | www.2ndQuadrant.it 
David Fetter | 28 Sep 00:29 2015

== PostgreSQL Weekly News - September 27 2015 ==

== PostgreSQL Weekly News - September 27 2015 ==

== PostgreSQL Product News ==

pg_back 1.2, shell script to back up everything for PostgreSQL,

PostgreDAC 3.0.3 a Delphi/C++ builder for PostgreSQL, released.

sql_firewall 0.8.1, an extension intended to protect database from SQL
injection or unexpected queries, released.

== PostgreSQL Jobs for September ==


== PostgreSQL Local ==

PGDay.IT 2015 will take place in Prato on October 23, 2015.

PostgreSQL Conference Europe 2015 will be held on October 27-30 in the
Vienna Marriott Hotel, in Vienna, Austria.  The schedule is posted.

PGConf Silicon Valley 2015 is November 17-18 at the South San
Francisco Convention Center.

PgCUBA will take place October 19-23, 2015 in Havana.  Spanish
language information below:

PGBR2015 (The Brazilian PostgreSQL Conference) will take place in Porto
Alegre, Rio Grande do Sul, on November 18, 19 and 20.  Registration is

PGConf.DE will be held on November 26-27, 2015 in Hamburg, Germany, at
the Lindner Hotel am Michel.

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

== Applied Patches ==

Noah Misch pushed:

- Remove the SECURITY_ROW_LEVEL_DISABLED security context bit.  This
  commit's parent made superfluous the bit's sole usage.  Referential
  integrity checks have long run as the subject table's owner, and
  that now implies RLS bypass.  Safe use of the bit was tricky,
  requiring strict control over the SQL expressions evaluating
  therein.  Back-patch to 9.5, where the bit was introduced.  Based on
  a patch by Stephen Frost.

- Restrict file mode creation mask during tmpfile().  Per Coverity.
  Back-patch to 9.0 (all supported versions).  Michael Paquier,
  reviewed (in earlier versions) by Heikki Linnakangas.

- Remove the row_security=force GUC value.  Every query of a single
  ENABLE ROW SECURITY table has two meanings, with the row_security
  GUC selecting between them.  With row_security=force available,
  every function author would have been advised to either set the GUC
  locally or test both meanings.  Non-compliance would have threatened
  reliability and, for SECURITY DEFINER functions, security.  Authors
  already face an obligation to account for search_path, and we should
  not mimic that example.  With this change, only BYPASSRLS roles need
  exercise the aforementioned care.  Back-patch to 9.5, where the
  row_security GUC was introduced.  Since this narrows the domain of
  pg_db_role_setting.setconfig and pg_proc.proconfig, one might bump
  catversion.  A row_security=force setting in one of those columns
  will elicit a clear message, so don't.

Tom Lane pushed:

- Fix possible internal overflow in numeric multiplication.  mul_var()
  postpones propagating carries until it risks overflow in its
  internal digit array.  However, the logic failed to account for the
  possibility of overflow in the carry propagation step, allowing
  wrong results to be generated in corner cases.  We must slightly
  reduce the when-to-propagate-carries threshold to avoid that.
  Discovered and fixed by Dean Rasheed, with small adjustments by me.
  This has been wrong since commit
  d72f6c75038d8d37e64a29a04b911f728044d83b, so back-patch to all
  supported branches.

- Docs: fix typo in to_char() example.  Per bug #13631 from KOIZUMI

- Make ANALYZE compute basic statistics even for types with no "="
  operator.  Previously, ANALYZE simply ignored columns of datatypes
  that have neither a btree nor hash opclass (which means they have no
  recognized equality operator).  Without a notion of equality, we
  can't identify most-common values nor estimate the number of
  distinct values.  But we can still count nulls and compute the
  average physical column width, and those stats might be of value.
  Moreover there are some tools out there that don't work so well if
  rows are missing from pg_statistic.  So let's add suitable logic for
  this case.  While this is arguably a bug fix, it also has the
  potential to change query plans, and the gain seems not worth taking
  a risk of that in stable branches.  So back-patch into 9.5 but not
  further.  Oleksandr Shulgin, rewritten a bit by me.

- Improve handling of collations in contrib/postgres_fdw.  If we have
  a local Var of say varchar type with default collation, and we apply
  a RelabelType to convert that to text with default collation, we
  don't want to consider that as creating an FDW_COLLATE_UNSAFE
  situation.  It should be okay to compare that to a remote Var, so
  long as the remote Var determines the comparison collation.  (When
  we actually ship such an expression to the remote side, the local
  Var would become a Param with default collation, meaning the remote
  Var would in fact control the comparison collation, because
  non-default implicit collation overrides default implicit collation
  in parse_collate.c.)  To fix, be more precise about what
  FDW_COLLATE_NONE means: it applies either to a noncollatable data
  type or to a collatable type with default collation, if that
  collation can't be traced to a remote Var.  (When it can,
  FDW_COLLATE_SAFE is appropriate.)  We were essentially using that
  interpretation already at the Var/Const/Param level, but we weren't
  bubbling it up properly.  An alternative fix would be to introduce a
  separate FDW_COLLATE_DEFAULT value to describe the second situation,
  but that would add more code without changing the actual behavior,
  so it didn't seem worthwhile.  Also, since we're clarifying the rule
  to be that we care about whether operator/function input collations
  match, there seems no need to fail immediately upon seeing a
  Const/Param/non-foreign-Var with nondefault collation.  We only have
  to reject if it appears in a collation-sensitive context (for
  example, "var IS NOT NULL" is perfectly safe from a collation
  standpoint, whatever collation the var has).  So just set the state
  to UNSAFE rather than failing immediately.  Per report from Jeevan
  Chalke.  This essentially corrects some sloppy thinking in commit
  ed3ddf918b59545583a4b374566bc1148e75f593, so back-patch to 9.3 where
  that logic appeared.

- Allow planner to use expression-index stats for function calls in
  WHERE.  Previously, a function call appearing at the top level of
  WHERE had a hard-wired selectivity estimate of 0.3333333, a kludge
  conveniently dated in the source code itself to July 1992.  The
  expectation at the time was that somebody would soon implement
  estimator support functions analogous to those for operators; but no
  such code has appeared, nor does it seem likely to in the near
  future.  We do have an alternative solution though, at least for
  immutable functions on single relations: creating an expression
  index on the function call will allow ANALYZE to gather stats about
  the function's selectivity.  But the code in clause_selectivity()
  failed to make use of such data even if it exists.  Refactor so that
  that will happen.  I chose to make it try this technique for any
  clause type for which clause_selectivity() doesn't have a special
  case, not just functions.  To avoid adding unnecessary overhead in
  the common case where we don't learn anything new, make selfuncs.c
  provide an API that hooks directly to examine_variable() and then
  var_eq_const(), rather than the previous coding which laboriously
  constructed an OpExpr only so that it could be expensively
  deconstructed again.  I preserved the behavior that the default
  estimate for a function call is 0.3333333.  (For any other
  expression node type, it's 0.5, as before.) I had originally thought
  to make the default be 0.5 across the board, but changing a default
  estimate that's survived for twenty-three years seems like something
  not to do without a lot more testing than I care to put into it
  right now.  Per a complaint from Jehan-Guillaume de Rorthais.
  Back-patch into 9.5, but not further, at least for the moment.

- Fix psql's code for locale-aware formatting of numeric output.  This
  code did the wrong thing entirely for numbers with an exponent but
  no decimal point (e.g., '1e6'), as reported by Jeff Janes in bug
  #13636.  More generally, it made lots of unverified assumptions
  about what the input string could possibly look like.  Rearrange so
  that it only fools with leading digits that it's directly verified
  are there, and an immediately adjacent decimal point.  While at it,
  get rid of some useless inefficiencies, like converting the grouping
  count string to integer over and over (and over).  This has been
  broken for a long time, so back-patch to all supported branches.

- Further fix for psql's code for locale-aware formatting of numeric
  output.  On closer inspection, those seemingly redundant atoi()
  calls were not so much inefficient as just plain wrong: the author
  of this code either had not read, or had not understood, the POSIX
  specification for localeconv().  The grouping field is *not* a
  textual digit string but separate integers encoded as chars.  We'll
  follow the existing code as well as the backend's cash.c in only
  honoring the first group width, but let's at least honor it
  correctly.  This doesn't actually result in any behavioral change in
  any of the locales I have installed on my Linux box, which may
  explain why nobody's complained; grouping width 3 is close enough to
  universal that it's barely worth considering other cases.  Still,
  wrong is wrong, so back-patch.

- Further fix for psql's code for locale-aware formatting of numeric
  output.  (Third time's the charm, I hope.) Additional testing
  disclosed that this code could mangle already-localized output from
  the "money" datatype.  We can't very easily skip applying it to
  "money" values, because the logic is tied to column
  right-justification and people expect "money" output to be
  right-justified.  Short of decoupling that, we can fix it in what
  should be a safe enough way by testing to make sure the string
  doesn't contain any characters that would not be expected in plain
  numeric output.

- Second try at fixing O(N^2) problem in foreign key references.  This
  replaces ill-fated commit 5ddc72887a012f6a8b85707ef27d85c274faf53d,
  which was reverted because it broke active uses of FK cache entries.
  In this patch, we still do nothing more to invalidatable cache
  entries than mark them as needing revalidation, so we won't break
  active uses.  To keep down the overhead of
  InvalidateConstraintCacheCallBack(), keep a list of just the
  currently-valid cache entries.  (The entries are large enough that
  some added space for list links doesn't seem like a big problem.)
  This would still be O(N^2) when there are many valid entries,
  though, so when the list gets too long, just force the "sinval
  reset" behavior to remove everything from the list.  I set the
  threshold at 1000 entries, somewhat arbitrarily.  Possibly that
  could be fine-tuned later.  Another item for future study is whether
  it's worth adding reference counting so that we could safely remove
  invalidated entries.  As-is, problem cases are likely to end up with
  large and mostly invalid FK caches.  Like the previous attempt,
  backpatch to 9.3.  Jan Wieck and Tom Lane

Peter Eisentraut pushed:

- Fix whitespace

- doc: Tweak synopsis indentation for consistency

- Use gender-neutral language in documentation.  Based on patch by
  Thomas Munro <thomas.munro <at> enterprisedb.com>, although I rephrased
  most of the initial work.

- doc: Tweak "cube" index entry.  With the arrival of the CUBE key
  word/feature, the index entries for the cube extension and the CUBE
  feature were collapsed into one.  Tweak the entry for the cube
  extension so they are separate entries.

- pg_ctl: Improve help formatting and order

- reindexdb: Fix mistake in help output

Andres Freund pushed:

- Correct value of LW_SHARED_MASK.  The previous wrong value lead to
  wrong LOCK_DEBUG output, never showing any shared lock holders.
  Reported-By: Alexander Korotkov Discussion:
  CAPpHfdsPmWqz9FB0AnxJrwp1=KLF0n=-iB+QvR0Q8GSmpFVdUQ <at> mail.gmail.com
  Backpatch: 9.5, where the bug was introduced.

- Add some notes about coding conventions do the docs.  This deserves
  to be greatly expanded and improved, but it's a start.  Discussion:
  20150827145219.GI2435 <at> awork2.anarazel.de

- test_decoding: Protect against rare spurious test failures.  A bunch
  of tests missed specifying that empty transactions shouldn't be
  displayed. That causes problems when e.g. autovacuum runs in an
  unfortunate moment. The tests in question only run for a very short
  time, making this quite unlikely.  Reported-By: Buildfarm member
  axolotl Backpatch: 9.4, where logical decoding was introduced

- Lower *_freeze_max_age minimum values.  The old minimum values are
  rather large, making it time consuming to test related behaviour.
  Additionally the current limits, especially for multixacts, can be
  problematic in space-constrained systems. 10000000 multixacts can
  contain a lot of members.  Since there's no good reason for the
  current limits, lower them a good bit. Setting them to 0 would be a
  bad idea, triggering endless vacuums, so still retain a limit.
  While at it fix autovacuum_multixact_freeze_max_age to refer to
  multixact.c instead of varsup.c.  Reviewed-By: Robert Haas
  CA+TgmoYmQPHcrc3GSs7vwvrbTkbcGD9Gik=OztbDGGrovkkEzQ <at> mail.gmail.com
  Backpatch: back to 9.0 (in parts)

- Remove legacy multixact truncation support.  In 9.5 and master there
  is no need to support legacy truncation. This is just committed
  separately to make it easier to backpatch the WAL logged multixact
  truncation to 9.3 and 9.4 if we later decide to do so.  I bumped
  master's magic from 0xD086 to 0xD088 and 9.5's from 0xD085 to 0xD087
  to avoid 9.5 reusing a value that has been in use on master while
  keeping the numbers increasing between major versions.  Discussion:
  20150621192409.GA4797 <at> alap3.anarazel.de Backpatch: 9.5

- Rework the way multixact truncations work.  The fact that multixact
  truncations are not WAL logged has caused a fair share of problems.
  Amongst others it requires to do computations during recovery while
  the database is not in a consistent state, delaying truncations till
  checkpoints, and handling members being truncated, but offset not.
  We tried to put bandaids on lots of these issues over the last
  years, but it seems time to change course. Thus this patch
  introduces WAL logging for multixact truncations.  This allows: 1)
  to perform the truncation directly during VACUUM, instead of
  delaying it to the checkpoint.  2) to avoid looking at the offsets
  SLRU for truncation during recovery, we can just use the master's
  values.  3) simplify a fair amount of logic to keep in memory limits
  straight, this has gotten much easier During the course of fixing
  this a bunch of additional bugs had to be fixed: 1) Data was not
  purged from memory the member's SLRU before deleting segments. This
  happened to be hard or impossible to hit due to the interlock
  between checkpoints and truncation.  2) find_multixact_start()
  relied on SimpleLruDoesPhysicalPageExist - but that doesn't work for
  offsets that haven't yet been flushed to disk. Add code to flush the
  SLRUs to fix. Not pretty, but it feels slightly safer to only make
  decisions based on actual on-disk state.  3) find_multixact_start()
  could be called concurrently with a truncation and thus fail. Via
  SetOffsetVacuumLimit() that could lead to a round of emergency
  vacuuming. The problem remains in pg_get_multixact_members(), but
  that's quite harmless.  For now this is going to only get applied to
  9.5+, leaving the issues in the older branches in place. It is quite
  possible that we need to backpatch at a later point though.  For the
  case this gets backpatched we need to handle that an updated standby
  may be replaying WAL from a not-yet upgraded primary. We have to
  recognize that situation and use "old style" truncation (i.e.
  looking at the SLRUs) during WAL replay. In contrast to before, this
  now happens in the startup process, when replaying a checkpoint
  record, instead of the checkpointer. Doing truncation in the
  restartpoint is incorrect, they can happen much later than the
  original checkpoint, thereby leading to wraparound.  To avoid
  "multixact_redo: unknown op code 48" errors standbys would have to
  be upgraded before primaries.  A later patch will bump the WAL page
  magic, and remove the legacy truncation codepaths. Legacy truncation
  support is just included to make a possible future backpatch easier.
  Discussion: 20150621192409.GA4797 <at> alap3.anarazel.de Reviewed-By:
  Robert Haas, Alvaro Herrera, Thomas Munro Backpatch: 9.5 for now

Robert Haas pushed:

- Teach planstate_tree_walker about custom scans.  This logic was
  missing from ExplainPreScanNode, from which I derived
  planstate_tree_walker.  But it shouldn't be missing, especially not
  from a generic walker function, so add it.  KaiGai Kohei

- Print a MergeJoin's mergeNullsFirst array as bool, not int.  It's
  declared as being an array of bool, but it's printed differently
  from the way bool and arrays of bool are handled elsewhere.  Patch
  by Amit Kapila.  Anomaly noted independently by Amit Kapila and
  KaiGai Kohei.

- Add readfuncs.c support for plan nodes.  For parallel query, we need
  to be able to pass a Plan to a worker, so that it knows what it's
  supposed to do.  We could invent our own way of serializing plans
  for that purpose, but piggybacking on the existing node
  infrastructure seems like a much better idea.  Initially, we'll
  probably only support a limited number of nodes within parallel
  workers, but this commit adds support for everything in plannodes.h
  except CustomScan, because doing it all at once seems easier than
  doing it piecemeal, and it makes testing this code easier, too.
  CustomScan is excluded because making that work requires a larger
  rework of that facility.  Amit Kapila, reviewed and slightly revised
  by me.

- Don't zero opfuncid when reading nodes.  The comments here stated
  that this was just in case we ever had an ALTER OPERATOR command
  that could remap an operator to a different function.  But those
  comments have been here for a long time, and no such command has
  come about.  In the absence of such a feature, forcing the pg_proc
  OID to be looked up again each time we reread a stored rule or
  similar is just a waste of cycles.  Moreover, parallel query needs a
  way to reread the exact same node tree that was written out, not one
  that has been slightly stomped on.  So just get rid of this for now.
  Per discussion with Tom Lane.

Teodor Sigaev pushed:

- Allow autoanalyze to add pages deleted from pending list to FSM
  Commit e95680832854cf300e64c10de9cc2f586df558e8 introduces adding
  pages to FSM for ordinary insert, but autoanalyze was able just
  cleanup pending list without adding to FSM.  Also fix double call of
  IndexFreeSpaceMapVacuum() during ginvacuumcleanup() Report from
  Fujii Masao Patch by me Review by Jeff Janes

Fujii Masao pushed:

- Make pg_controldata report newest XID with valid commit timestamp.
  Previously pg_controldata didn't report newestCommitTs and this was
  an oversight in commit 73c986a.  Also this patch changes
  pg_resetxlog so that it uses the same sentences as pg_controldata
  does, regarding oldestCommitTs and newestCommitTs, for the sake of
  consistency.  Back-patch to 9.5 where track_commit_timestamp was
  added.  Euler Taveira

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Gurjeet Singh sent in a patch to redefine GIST_MAX_SPLIT_PAGES so that
in case of a split, gistplacetopage() now throws an error when the
block-ids needed exceed 32.

Corey Huinker sent in another revision of a patch to allow setting
fetch size by foreign server and table.

Thomas Munro sent in another revision of a patch to strip "volatile"
setting from replication commands.

Dmitry Dolgov sent in two more revisions of a patch to fix some issues
with jsonb_set().

Rahila Syed sent in another revision of a patch to implement a vacuum
progress checker.

Jim Nasby sent in two revisions of a patch to fix some less than ideal
error reporting in pg_stat_statements.

Amit Kapila sent in two revisions of a patch to add parallel support
for all the plan and other nodes (like SubPlan which could be required
for worker) except CustomScan node.

David Rowley sent in three more revisions of a patch to use foreign
keys to improve join estimates.

Jeff Janes sent in a patch to invert ResolveRecoveryConflictWithLock
to be called back from the lmgr code so that is it like
ResolveRecoveryConflictWithBufferPin code.

Tom Lane sent in a patch to look for stats for bool functions.

Robert Haas and Amit Kapila traded patches for parallel seq scan.

Taiki Kondo sent in another revision of a patch to enable join
pushdown with table partitions.

Teodor Sigaev sent in another revision of a patch to fix a GIN vacuum

Stephen Frost sent in a patch to rename withCheckOptions to

Thomas Munro sent in a patch to use the lockbit-on macro consistently.

Kaigai Kouhei sent in a patch to add custom scan on readfuncs.

Amir Rohan sent in a patch to fix in-core regression tests.

Rushabh Lathia sent in a patch to fix the HINT for slot name error

Peter Eisentraut sent in a patch to documented the cluster_name and
update_process_title settings near one another.

Fabien COELHO sent in a patch to fix some computations in pgbench.

Tom Lane sent in a patch to fix some issues with pg_ctl/pg_rewind on

Jeff Janes sent in a patch to add tab completion in psql for ALTER

Teodor Sigaev sent in another revision of a patch to rework the access
method interface.

Nikolay Shaplov sent in two more revisions of a patch to allow showing
tuple data in pageinspect.

Takashi Horikawa sent in another revision of a patch to implement
partitioned checkpointing.

Paul Ramsey sent in another revision of a patch to enable FDW
extension support.

Alexander Shulgin sent in another revision of a patch to add on-demand
running query plans using auto_explain and signals.

Tomas Vondra sent in two more revisions of a patch to allow index
scans based on partial indexes.

Jinyu Zhang sent in a patch to optimize memory allocation in function


Sent via pgsql-announce mailing list (pgsql-announce <at> postgresql.org)
To make changes to your subscription:

Satoshi Nagayasu | 23 Sep 07:16 2015

sql_firewall 0.8.1 is out


I'm proud to announce the latest release of sql_firewall.

sql_firewall is a PostgreSQL extension which is intended to
protect your database from SQL injections or unexpected queries.

The sql_firewall module learns the queries allowed to be executed,
and prevents/warns on executing queries which are not found in the
firewall rule.

In the 0.8.1 release, there are several fixes:

  * Fix sql_firewall.c to suppress `unused-const-variable' warning
    on OS X.
  * Fix sql_firewall_import_rule() to check file status before
    importing a rule file.
  * Fix JumbleRangeTable() to jumble query with relation name
    instead of oid.
  * Fix JumbleExpr() to use function name on query jumbling instead
    of the oid.
  * Fix README to add the Compatibility section.
  * Add COPYRIGHT and ChangeLog.

For more information, please visit the github repo.


NAGAYASU Satoshi <snaga <at> uptime.jp>


Sent via pgsql-announce mailing list (pgsql-announce <at> postgresql.org)
To make changes to your subscription:

Pavel Golub | 22 Sep 10:08 2015

PostgresDAC meets RAD Studio 10 Seattle (both Delphi and C++ Builder)

PostgresDAC v3.0.3 with support for Delphi and C++ Builder 10 Seattle
is available immediately!

You're welcome to download the PostgresDAC v3.0.3 right now at:
http://microolap.com/products/connectivity/postgresdac/download/ or
login to your private area on our site at

Please don't hesitate to ask any questions or report bugs with our
Support Ticketing system available at

With best wishes,
 Pavel                          mailto:pavel <at> gf.microolap.com


Sent via pgsql-announce mailing list (pgsql-announce <at> postgresql.org)
To make changes to your subscription:

Björn Häuser | 21 Sep 19:04 2015

Registration now open for PGConf.DE 2015


PGConf.DE 2015 is now open for registrations. To signup, please visit:


The Early Bird special price, limited to 50 tickets, will be available
until October 12th.

The conference will be at 26. and 27. November, at the Lindner Hotel
am Michel, Hamburg. There will be trainings on the 26. and three
tracks with talks on 27.

You can find more information regarding the trainings here:

See you in Hamburg!


Sent via pgsql-announce mailing list (pgsql-announce <at> postgresql.org)
To make changes to your subscription:

Nicolas Thauvin | 18 Sep 10:32 2015

pg_back 1.2 released

pg_back 1.2 released

Because sometimes you just need a simple way to dump all your
databases, here is pg_back. It is a simple shell script to backup

Though it works as is, the main goal of this script is to be a
template script that anyone with some basics shell scripts skills can
customize to fit their own needs.

The 1.2 release now detects if it is running on a hot standby server
and pauses the replication before dumping the databases. This way,
pg_dump won't fail because of replication conflicts.

pg_back is licensed under the classic 2 clauses BSD license.

More at https://github.com/orgrim/pg_back

Nicolas Thauvin
DBA PostgreSQL
http://dalibo.com - http://dalibo.org


Sent via pgsql-announce mailing list (pgsql-announce <at> postgresql.org)
To make changes to your subscription:

David Fetter | 21 Sep 01:55 2015

== PostgreSQL Weekly News - September 20 2015 ==

== PostgreSQL Weekly News - September 20 2015 ==

== PostgreSQL Product News ==

Envelopt 1.0.0, a web platform for creating PostgreSQL applications,

PostgreDAC 4.6, a Delphi/C++ builder for PostgreSQL, and dbExpress
PostgreSQL driver 3.7, released.

tds_fdw 1.0.4, a foreign data wrapper for MS-SQL Server and Sybase, released.

== PostgreSQL Jobs for September ==


== PostgreSQL Local ==

PostgreSQL Session #7, will be held September 24th, 2015 in Paris,

PGDay.IT 2015 will take place in Prato on October 23, 2015.

PostgreSQL Conference Europe 2015 will be held on October 27-30 in the
Vienna Marriott Hotel, in Vienna, Austria.  The schedule is posted.

PGConf Silicon Valley 2015 is November 17-18 at the South San
Francisco Convention Center.

PgCUBA will take place October 19-23, 2015 in Havana.  Spanish
language information below:

PGBR2015 (The Brazilian PostgreSQL Conference) will take place in Porto
Alegre, Rio Grande do Sul, on November 18, 19 and 20.  The CfP is open
until August 31.

PGConf.DE will be held on November 26-27, 2015 in Hamburg, Germany, at
the Lindner Hotel am Michel.

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

== Applied Patches ==

Teodor Sigaev pushed:

- Check existency of table/schema for -t/-n option
  (pg_dump/pg_restore).  Patch provides command line option
  --strict-names which requires that at least one table/schema should
  present for each -t/-n option.  Pavel Stehule
  <pavel.stehule <at> gmail.com>

- Fix wrong comment in commit d02426029b133ee2bbe492a038642359bce3c527
  Per gripe from Robert Haas

- pgbench progress with timestamp.  This patch adds an option to
  replace the "time since pgbench run started" with a Unix epoch
  timestamp in the progress report so that, for instance, it is easier
  to compare timelines with pgsql log Fabien COELHO
  <coelho <at> cri.ensmp.fr>

- Fix bug introduced by microvacuum for GiST Commit
  013ebc0a7b7ea9c1b1ab7a3d4dd75ea121ea8ba7 introduces microvacuum for
  GiST, deletetion of tuple marked LP_DEAD uses IndexPageMultiDelete
  while recovery code uses IndexPageTupleDelete in loop. This causes a
  difference in offset numbers of tuples to delete. Patch introduces
  usage of IndexPageMultiDelete in GiST except gistplacetopage() where
  only one tuple is deleted at once. That also slightly improve
  performance, because IndexPageMultiDelete is more effective.  Patch
  changes WAL format, so bump wal page magic.  Bug report from Jeff
  Janes Diagnostic and patch by Anastasia Lubennikova and me

- Add header forgotten in 213335c14529a8d5e2007ec0c256f4cf64d62d3b.
  Report from Peter Eisentraut

- Fix oversight in tsearch type check.  Use IsBinaryCoercible() method
  instead of custom is_expected_type/is_text_type functions which was
  introduced when tsearch2 was moved into core.  Per report by David
  E. Wheeler.  Analysis by Tom Lane.  Patch by me.

Fujii Masao pushed:

- Improve log messages related to tablespace_map file.  This patch
  changes the log message which is logged when the server successfully
  renames backup_label file to *.old but fails to rename
  tablespace_map file during the shutdown. Previously the WARNING
  message "online backup mode was not canceled" was logged in that
  case.  However this message is confusing because the backup mode is
  treated as canceled whenever backup_label is successfully renamed.
  So this commit makes the server log the message "online backup mode
  canceled" in that case.  Also this commit changes errdetail messages
  so that they follow the error message style guide.  Back-patch to
  9.5 where tablespace_map file is introduced.  Original patch by Amit
  Kapila, heavily modified by me.

- Fix comment regarding the meaning of infinity for timeline history
  entry.  Michael Paquier

Stephen Frost pushed:

- Add POLICY to COMMENT documentation.  COMMENT supports POLICY but
  the documentation hadn't caught up with that fact.  Patch by Charles
  Clavadetscher Back-patch to 9.5 where POLICY was added.

- Enforce ALL/SELECT policies in RETURNING for RLS.  For the
  UPDATE/DELETE RETURNING case, filter the records which are not
  visible to the user through ALL or SELECT policies from those
  considered for the UPDATE or DELETE.  This is similar to how the
  GRANT system works, which prevents RETURNING unless the caller has
  SELECT rights on the relation.  Per discussion with Robert, Dean,
  Tom, and Kevin.  Back-patch to 9.5 where RLS was introduced.

- RLS refactoring.  This refactors rewrite/rowsecurity.c to simplify
  the handling of the default deny case (reducing the number of places
  where we check for and add the default deny policy from three to
  one) by splitting up the retrival of the policies from the
  application of them.  This also allowed us to do away with the
  policy_id field.  A policy_name field was added for WithCheckOption
  policies and is used in error reporting, when available.  Patch by
  Dean Rasheed, with various mostly cosmetic changes by me.
  Back-patch to 9.5 where RLS was introduced to avoid unnecessary
  differences, since we're still in alpha, per discussion with Robert.

Tom Lane pushed:

- Revert "Fix an O(N^2) problem in foreign key references".  Commit
  5ddc72887a012f6a8b85707ef27d85c274faf53d does not actually work
  because it will happily blow away ri_constraint_cache entries that
  are in active use in outer call levels.  In any case, it's a very
  ugly, brute-force solution to the problem of limiting the cache
  size.  Revert until it can be redesigned.

- Remove no-longer-used T_PrivGrantee node tag.  Oversight in commit
  31eae6028eca4365e7165f5f33fee1ed0486aee0, which replaced PrivGrantee
  nodes with RoleSpec nodes.  Spotted by Yugo Nagata.

- Don't use "#" as an abbreviation for "number" in PL/Tcl error
  messages.  Also, rewrite one error message to make it follow our
  message style guidelines better.  Euler Taveira and Tom Lane

- Fix documentation of regular expression character-entry escapes.
  The docs claimed that \uhhhh would be interpreted as a Unicode value
  regardless of the database encoding, but it's never been implemented
  that way: \uhhhh and \xhhhh actually mean exactly the same thing,
  namely the character that pg_mb2wchar translates to 0xhhhh.
  Moreover we were falsely dismissive of the usefulness of Unicode
  code points above FFFF.  Fix that.  It's been like this for ages, so
  back-patch to all supported branches.

- Sync regex code with Tcl 8.6.4.  Sync our regex code with upstream
  changes since last time we did this, which was Tcl 8.5.11 (see
  commit 08fd6ff37f71485e2fc04bc6ce07d2a483c36702).  The only
  functional change here is to disbelieve that an octal escape is
  three digits long if it would exceed \377.  That's a bug fix, but
  it's a minor one and could change the interpretation of working
  regexes, so don't back-patch.  In addition to that,
  s/INFINITY/DUPINF/ to eliminate the risk of collisions with
  <math.h>'s macro, and s/LOCAL/NOPROP/ because that also seems like
  an unnecessarily collision-prone macro name.  There were some other
  cosmetic changes in their copy that I did not adopt, notably a
  rather half-hearted attempt at renaming some of the C functions in a
  more verbose style.  (I'm not necessarily against the concept, but
  renaming just a few functions in the package is not an improvement.)

- Fix low-probability memory leak in regex execution.  After an
  internal failure in shortest() or longest() while pinning down the
  exact location of a match, find() forgot to free the DFA structure
  before returning.  This is pretty unlikely to occur, since we just
  successfully ran the "search" variant of the DFA; but it could
  happen, and it would result in a session-lifespan memory leak since
  this code uses malloc() directly.  Problem seems to have been
  aboriginal in Spencer's library, so back-patch all the way.  In
  passing, correct a thinko in a comment I added awhile back about the
  meaning of the "ntree" field.  I happened across these issues while
  comparing our code to Tcl's version of the library.

- Be more wary about partially-valid LOCALLOCK data in
  RemoveLocalLock().  RemoveLocalLock() must consider the possibility
  that LockAcquireExtended() failed to palloc the initial space for a
  locallock's lockOwners array.  I had evidently meant to cope with
  this hazard when the code was originally written (commit
  1785acebf2ed14fd66955e2d9a55d77a025f418d), but missed that the pfree
  needed to be protected with an if-test.  Just to make sure things
  are left in a clean state, reset numLockOwners as well.  Per
  low-memory testing by Andreas Seltenreich.  Back-patch to all
  supported branches.

Peter Eisentraut pushed:

- Fix whitespace

- Review program help output for wording and formatting

- Order some new options on man pages more sensibly, minor

- Remove trailing slashes from directories in find command.  BSD find
  is not very smart and ends up writing double slashes into the output
  in those cases.  Also, xgettext is not very smart and splits the
  file names incorrectly in those cases, resulting in slightly
  incorrect file names being written into the POT file.

- Simplify GETTEXT_FILES list

- Add missing serial comma

Robert Haas pushed:

- Determine whether it's safe to attempt a parallel plan for a query.
  Commit 924bcf4f16d54c55310b28f77686608684734f42 introduced a
  framework for parallel computation in PostgreSQL that makes most but
  not all built-in functions safe to execute in parallel mode.  In
  order to have parallel query, we'll need to be able to determine
  whether that query contains functions (either built-in or
  user-defined) that cannot be safely executed in parallel mode.  This
  requires those functions to be labeled, so this patch introduces an
  infrastructure for that.  Some functions currently labeled as safe
  may need to be revised depending on how pending issues related to
  heavyweight locking under paralllelism are resolved.  Parallel plans
  can't be used except for the case where the query will run to
  completion.  If portal execution were suspended, the parallel mode
  restrictions would need to remain in effect during that time, but
  that might make other queries fail.  Therefore, this patch
  introduces a framework that enables consideration of parallel plans
  only when it is known that the plan will be run to completion.  This
  probably needs some refinement; for example, at bind time, we do not
  know whether a query run via the extended protocol will be execution
  to completion or run with a limited fetch count.  Having the client
  indicate its intentions at bind time would constitute a wire
  protocol break.  Some contexts in which parallel mode would be safe
  are not adjusted by this patch; the default is not to try parallel
  plans except from call sites that have been updated to say that such
  plans are OK.  This commit doesn't introduce any parallel paths or
  plans; it just provides a way to determine whether they could
  potentially be used.  I'm committing it on the theory that the
  remaining parallel sequential scan patches will also get committed
  to this release, hopefully in the not-too-distant future.  Robert
  Haas and Amit Kapila.  Reviewed (in earlier versions) by Noah Misch.

- Add new function planstate_tree_walker.  ExplainPreScanNode knows
  how to iterate over a generic tree of plan states; factor that logic
  out into a separate walker function so that other code, such as
  upcoming patches for parallel query, can also use it.  Patch by me,
  reviewed by Tom Lane.

- Glue layer to connect the executor to the shm_mq mechanism.  The
  shm_mq mechanism was built to send error (and notice) messages and
  tuples between backends.  However, shm_mq itself only deals in raw
  bytes.  Since commit 2bd9e412f92bc6a68f3e8bcb18e04955cc35001d, we
  have had infrastructure for one message to redirect protocol
  messages to a queue and for another backend to parse them and do
  useful things with them.  This commit introduces a somewhat
  analogous facility for tuples by adding a new type of DestReceiver,
  DestTupleQueue, which writes each tuple generated by a query into a
  shm_mq, and a new TupleQueueFunnel facility which reads raw tuples
  out of the queue and reconstructs the HeapTuple format expected by
  the executor.  The TupleQueueFunnel abstraction supports reading
  from multiple tuple streams at the same time, but only in
  round-robin fashion.  Someone could imaginably want other policies,
  but this should be good enough to meet our short-term needs related
  to parallel query, and we can always extend it later.  This also
  makes one minor addition to the shm_mq API that didn' seem worth
  breaking out as a separate patch.  Extracted from Amit Kapila's
  parallel sequential scan patch.  This code was originally written by
  me, and then it was revised by Amit, and then it was revised some
  more by me.

Michael Meskes pushed:

- Let compiler handle size calculation of bool types.  Back in the day
  this did not work, but modern compilers should handle it themselves.

Andrew Dunstan pushed:

- Honour TEMP_CONFIG when testing pg_upgrade This setting contains
  extra configuration for the temp instance, as used in pg_regress'
  --temp-config flag.  Backpatch to 9.2 where test.sh was introduced.

- Cache argument type information in json(b) aggregate functions.
  These functions have been looking up type info for every row they
  process. Instead of doing that we only look them up the first time
  through and stash the information in the aggregate state object.
  Affects json_agg, json_object_agg, jsonb_agg and jsonb_object_agg.
  There is plenty more work to do in making these more efficient,
  especially the jsonb functions, but this is a virtually cost free
  improvement that can be done right away.  Backpatch to 9.5 where the
  jsonb variants were introduced.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Egon Kocjan sent in a patch to add a command line option for pg_ctl on
Windows to redirect logging of errors (write_stderr).

Teodor Sigaev sent in another revision of a patch to add GiST support
for UUIDs.

Franck Verrot sent in two more revisions of a patch to report column
for which type coercion fails.

Andres Freund sent in a patch to move PinBuffer and UnpinBuffer to

Andrew Dunstan sent in a patch to cache type info in json_agg and

Kyotaro HORIGUCHI sent in a patch to enable index-only scans with
partial indexes.

Alexander Shulgin sent in three more revisions of a patch to enable
getting the query plans of running queries using auto_explain and

Petr Jelinek sent in a patch to remove the on
start check for track_commit_timestamp being same in config and
control file.

Peter Eisentraut sent in a patch mostly of historical interest to fix
some SSL issues.

Fabien COELHO sent in another revision of a patch to mark pgbench
progress with timestamps.

Jesper Pedersen sent in three revisions of a patch to add additional
LWLOCK_STATS statistics.

Fabien COELHO sent in two more revisions of a patch to extend pgbench
expressions with functions.

Petr Jelinek sent in two more revisions of a patch to implement CREATE

Amit Langote sent in a patch to remove obsolete cross-references to
set_append_rel_pathlist in comments.

Kyotaro HORIGUCHI sent in a patch to remove the no-longer-needed
T_PrivGrantee from NodeTag in src/include/nodes/nodes.h.

Thomas Munro sent in a patch to remove obsolete use of volatile in
walsender.c, walreceiver.c, walreceiverfuncs.

Anastasia Lubennikova and Teodor Sigaev traded patches to add a
microvacuum for gist.

Thomas Munro sent in two more revisions of a patch to add a setting
which makes synchronous commit apply.

Alexander Korotkov sent in another revision of a patch to implement
pg_rewind target switch.

Daniel Verité sent in two more revisions of a patch to add a \rotate
command to psql.

Michael Paquier sent in another revision of a patch to improve test
coverage of extensions with pg_dump.

Dean Rasheed sent in two revisions of a patch to fix some numerical
issues with some transcendental functions on NUMERIC.

Alexander Korotkov sent in a patch to fix the LW_SHARED_MASK macro.

Euler Taveira de Oliveira sent in a patch to standardize some
sentences in pg_resetxlog.

Michael Paquier sent in another revision of a patch to fix some
pg_rewind usability issues.

Jan Wieck and Tom Lane traded patches to fix an O(N^2) issue in
foreign key references.

Robert Haas sent in a patch to number plan nodes in anticipation of
parallelization work to follow.

SAWADA Masahiko sent in another revision of a patch to add a "frozen"
bit to the visibility map.

Nikolay Shaplov sent in another revision of a patch to pageinspect
which allows showing tuple data.

Teodor Sigaev sent in another revision of a patch to add pages deleted
from pending list to FSM.

Alexander Korotkov sent in another revision of a patch to rework the
access method interface.

Adrian Vondendriesch sent in a patch to make pg_size_pretty handle
negative values.


Sent via pgsql-announce mailing list (pgsql-announce <at> postgresql.org)
To make changes to your subscription:

Geoff Montee | 20 Sep 23:26 2015

tds_fdw 1.0.4 - Foreign Data Wrapper for MS SQL Server and Sybase

Hi all,

Today, I am releasing version 1.0.4 of tds_fdw, a TDS foreign data
wrapper. It can be used to connect to Microsoft SQL Server and Sybase
databases. This should work on PostgreSQL 9.1+.

New in this version:

* Added support for PostgreSQL 9.5. See issue #33. Thanks to GitHub
user  <at> mc-soi for this patch!


* Fixed several memory leaks. See issue #29.


* Implemented a special conversion for the DATETIME type, so that it
can be converted into PostgreSQL's timestamp type without tinkering
with the system's locale settings. See issues #28, #21, and #13.




* Added some variables that provide a way to write tds_fdw memory
usage to the PostgreSQL error log for diagnostic purposes. These
variables are called tds_fdw.show_before_row_memory_stats,

This version does not support write operations, where push-downs,
column push-downs, or join push-downs.






Geoff Montee


Sent via pgsql-announce mailing list (pgsql-announce <at> postgresql.org)
To make changes to your subscription:

Denis Yanov | 16 Sep 15:08 2015

New Delphi PostgreSQL data access components and dbExpress drivers

Devart company released new PostgreSQL Delphi data access components named PgDAC 4.6.

List of improvements and fixes:

- RAD Studio 10 Seattle is supported
- INSERT, UPDATE and DELETE batch operations are supported
- Support of bit and bit varying data types is improved
- Now Trial for Win64 is a fully functional Professional Edition
- Now at automatic refresh of Detail dataset the OnBeforeOpen event is not called
- Generating conditions for filtration when using similar field names and aliases in TCRDBGrid is fixed
- SQLMonitor behavior on using similar objects is fixed
- Bug with endless reconnection in assigned connection is fixed
- Bug with editing Blob and Memo fields used in local filter is fixed
- Bug with inserting to JSON fields is fixed

More info             Download PgDAC 4.6

Additionally to new released version of PgDAC Devart released updated version of dbExpress driver for PostgreSQL.
What's new:
- RAD Studio 10 Seattle is supported and now you can use the latest IDE to develop cross-platform applications.

- Now the trial limitation by 6 columns is removed from the Trial version for Win64 and it becomes a fully-functional Professional Edition.

More info        Download dbExpress PostgreSQL driver 3.7