Dave Page | 18 Dec 16:31 2014

PostgreSQL 9.4 Increases Flexibility, Scalability and Performance

18 DECEMBER 2014: The PostgreSQL Global Development Group announces the release of PostgreSQL 9.4, the latest version of the world's leading open source database system.  This release adds many new features which enhance PostgreSQL's flexibility, scalability and performance for many different types of database users, including improvements to JSON support, replication and index performance.


With the new JSONB data type for PostgreSQL, users no longer have to choose between relational and non-relational data stores: they can have both at the same time.  JSONB supports fast lookups and simple expression search queries using Generalized Inverted Indexes (GIN).  Multiple new support functions enable users to extract and manipulate JSON data, with a performance which matches or surpasses the most popular document databases.  With JSONB, table data can be easily integrated with document data for a fully integrated database environment.

"JSONB brings PostgreSQL to the JavaScript development community by allowing JSON data to be stored and queried natively. Node.js and other server-side JavaScript frameworks can benefit from the safety and robustness of PostgreSQL, while still storing data in the schema-less format that they prefer," said Matt Soldo, Product Manager, Heroku Postgres.


In 9.4, Logical Decoding supplies a new API for reading, filtering and manipulating the PostgreSQL replication stream.  This interface is the foundation for new replication tools, such as Bi-Directional Replication, which supports the creation of multi-master PostgreSQL clusters.  Other improvements in the replication system, such as replication slots and time-delayed replicas, improve management and utility of replica servers.

"The main reason behind our immediate adoption of PostgreSQL 9.4 in production is the new Logical Decoding feature," said Marco Favale, Cartographic Production Manager of Navionics. "The ability to write custom and flexible output plugins will allow us to transparently collect changes on selected tables and replicate changes where we like, by removing heavier - and more complex to manage - trigger based
replication solutions."

"Zalando relies on the stability and performance of hundreds of PostgreSQL database servers to continuously serve millions of customers all around Europe," said Valentine Gogichashvili, Team Lead Database Operations at Zalando Technologies.  "We are excited to run time-delayed standby servers which work out of the box, and will evaluate the new bi-directional replication tools as soon as they are released."


Version 9.4 also introduces multiple performance improvements which will allow users to get even more out of each PostgreSQL server.  These include:

* Improvements to GIN indexes, making them up to 50% smaller and up to 3X faster.
* Concurrently updatable Materialized Views for faster, more up-to-date reporting.
* Rapidly reload the database cache on restart using pg_prewarm.
* Faster parallel writing to the PostgreSQL transaction log.
* Support for Linux Huge Pages for servers with large memory.

"We will definitely benefit from concurrent refresh of materialised views, delayed standby servers (which will make our disaster recovery even more robust) as well as the usual performance improvements every new release carries on," added Marco Favale.


About PostgreSQL

PostgreSQL is the leading open source database system, with a global community of thousands of users and contributors and dozens of companies and organizations.  The PostgreSQL Project builds on over 25 years of engineering, starting at the University of California, Berkeley, and has an unmatched pace of development today. PostgreSQL's mature feature set not only matches top proprietary database systems, but exceeds them in advanced database features, extensibility, security and stability.  Learn more about PostgreSQL and participate in our community at: http://www.postgresql.org.

Dave Page
PostgreSQL Core Team
David Fetter | 15 Dec 00:54 2014

== PostgreSQL Weekly News - December 14 2014 ==

== PostgreSQL Weekly News - December 14 2014 ==

The Melbourne PostgreSQL meetup on February 18, 2015 will be hosting
Gabriele Bartolini on PostgreSQL 9.4 for devops.  Details below, and

Prague PostgreSQL Developer Day (P2D2) 2015 will be in Prague, Czech
Republic February 11-12, 2015.

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

== PostgreSQL Product News ==

== PostgreSQL Jobs for December ==


== PostgreSQL Local ==

FOSDEM PGDay (January 30, 2015) and the FOSDEM PostgreSQL
Dev Room (January 31-February 1, 2015)

Prague PostgreSQL Developer Day (P2D2) 2015 will be in Prague, Czech
Republic February 11-12, 2015.

pgDaySF 2015 will be held March 10, 2015 in Burlingame, California.

The CfP is open for Nordic PostgreSQL Day 2015, which will be held
March 11, 2015 in  Copenhagen, Denmark.

The CfP for PGConf US 2015 is open through December 17th, 2014
Notifications will go out on January 10, 2014.  The event takes place
March 25-27, 2015 in NYC.

== 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 ==

Simon Riggs pushed:

- Remove duplicate code in heap_prune_chain().  No need to set tuple
  tableOid twice.  Jim Nasby

- Correct recovery_target_action docs.  From Michael Paquier

- Use GetSystemTimeAsFileTime directly in win32.  PostgreSQL was
  calling GetSystemTime followed by SystemTimeToFileTime in the win32
  port gettimeofday function. This is not necessary and limits the
  reported precision to the 1ms granularity that the SYSTEMTIME struct
  can represent. By using GetSystemTimeAsFileTime we avoid unnecessary
  conversions and capture timestamps at 100ns granularity, which is
  then rounded to 1µs granularity for storage in a PostgreSQL
  timestamp.  On most Windows systems this change will actually have
  no significant effect on timestamp resolution as the system timer
  tick is typically between 1ms and 15ms depending on what timer
  resolution currently running applications have requested. You can
  check this with clockres.exe from sysinternals.  Despite the
  platform limiation this change still permits capture of finer
  timestamps where the system is capable of producing them and it gets
  rid of an unnecessary syscall.  The higher resolution
  GetSystemTimePreciseAsFileTime call available on Windows 8 and
  Windows Server 2012 has the same interface as
  GetSystemTimeAsFileTime, so switching to GetSystemTimeAsFileTime
  makes it easier to use the Precise variant later.  Craig Ringer,
  reviewed by David Rowley

- Windows: use GetSystemTimePreciseAsFileTime if available.
  PostgreSQL on Windows 8 or Windows Server 2012 will now get
  high-resolution timestamps by dynamically loading the
  GetSystemTimePreciseAsFileTime function. It'll fall back to to
  GetSystemTimeAsFileTime if the higher precision variant isn't found,
  so the same binaries without problems on older Windows releases.  No
  attempt is made to detect the Windows version.  Only the presence or
  absence of the desired function is considered.  Craig Ringer

- Execute 18 tests for src/bin/scripts/t/090..  Some requests count as
  two tests.

  produced a stream of NOTICE messages. Removing that since it is
  inconsistent for such a command to produce output without a VERBOSE

- REINDEX SCHEMA.  Add new SCHEMA option to REINDEX and reindexdb.
  Sawada Masahiko Reviewed by Michael Paquier and Fabrízio de Royes

- Further changes to REINDEX SCHEMA.  Ensure we reindex indexes built
  on Mat Views.  Based on patch from Micheal Paquier Add thorough
  tests to check that indexes on tables, toast tables and mat views
  are reindexed.  Simon Riggs

Peter Eisentraut pushed:

- doc: Fix DocBook table column count declaration.  This was broken in

- Fix typo.  Author: Fabrízio de Royes Mello <fabriziomello <at> gmail.com>

- SSL tests: Silence pg_ctl output.  Otherwise the pg_ctl start and
  stop messages get mixed up with the TAP output, which isn't
  technically valid.

- SSL tests: Remove trailing blank lines

- doc: Move website-stylesheet setting to a more appropriate location

- doc: Fix markup

Álvaro Herrera pushed:

- pg_xlogdump/.gitignore: add committsdesc.c.  Author: Michael Paquier

Heikki Linnakangas pushed:

- Add a regression test suite for SSL support.  It's not run by the
  global "check" or "installcheck" targets, because the temporary
  installation it creates accepts TCP connections from any user the
  same host, which is insecure.

- Use correct macro for reltablespace.  It's an OID. WRITE_UINT_FIELD
  is identical to WRITE_OID_FIELD, but let's be tidy.  Mark Dilger

- Put the logic to decide which synchronous standby is active into a
  function.  This avoids duplicating the code.  Michael Paquier,
  reviewed by Simon Riggs and me

- Remove duplicate #define.  Mark Dilger

Tom Lane pushed:

- Fix minor thinko in convertToJsonb().  The amount of space to
  reserve for the value's varlena header is VARHDRSZ, not
  sizeof(VARHDRSZ).  The latter coding accidentally failed to fail
  because of the way the VARHDRSZ macro is currently defined; but if
  we ever change it to return size_t (as one might reasonably expect
  it to do), convertToJsonb() would have failed.  Spotted by Mark

- Fix assorted confusion between Oid and int32.  In passing, also make
  some debugging elog's in pgstat.c a bit more consistently worded.
  Back-patch as far as applicable (9.3 or 9.4; none of these mistakes
  are really old).  Mark Dilger identified and patched the type
  violations; the message rewordings are mine.

- Fix completely broken REINDEX SCHEMA testcase.  Aside from not
  testing the case it claimed to test (namely a permissions failure),
  it left a login-capable role lying around, which quite aside from
  possibly being a security hole would cause subsequent regression
  runs to fail since the role would already exist.

- Make rowsecurity test clean up after itself, too.  Leaving global
  objects like roles hanging around is bad practice.

- Fix corner case where SELECT FOR UPDATE could return a row twice.
  In READ COMMITTED mode, if a SELECT FOR UPDATE discovers it has to
  redo WHERE-clause checking on rows that have been updated since the
  SELECT's snapshot, it invokes EvalPlanQual processing to do that.
  If this first occurs within a non-first child table of an
  inheritance tree, the previous coding could accidentally re-return a
  matching row from an earlier, already-scanned child table.  (And, to
  add insult to injury, I think this could make it miss returning a
  row that should have been returned, if the updated row that this
  happens on should still have passed the WHERE qual.) Per report from
  Kyotaro Horiguchi; the added isolation test is based on his test
  case.  This has been broken for quite awhile, so back-patch to all
  supported branches.

- Fix planning of SELECT FOR UPDATE on child table with partial index.
  Ordinarily we can omit checking of a WHERE condition that matches a
  partial index's condition, when we are using an indexscan on that
  partial index.  However, in SELECT FOR UPDATE we must include the
  "redundant" filter condition in the plan so that it gets checked
  properly in an EvalPlanQual recheck.  The planner got this mostly
  right, but improperly omitted the filter condition if the index in
  question was on an inheritance child table.  In READ COMMITTED mode,
  this could result in incorrectly returning just-updated rows that no
  longer satisfy the filter condition.  The cause of the error is
  using get_parse_rowmark() when get_plan_rowmark() is what should be
  used during planning.  In 9.3 and up, also fix the same mistake in
  contrib/postgres_fdw.  It's currently harmless there (for lack of
  inheritance support) but wrong is wrong, and the incorrect code
  might get copied to someplace where it's more significant.  Report
  and fix by Kyotaro Horiguchi.  Back-patch to all supported branches.

- Revert misguided change to postgres_fdw FOR UPDATE/SHARE code.  In
  commit 462bd95705a0c23ba0b0ba60a78d32566a0384c1, I changed
  postgres_fdw to rely on get_plan_rowmark() instead of
  get_parse_rowmark().  I still think that's a good idea in the long
  run, but as Etsuro Fujita pointed out, it doesn't work today because
  planner.c forces PlanRowMarks to have markType = ROW_MARK_COPY for
  all foreign tables.  There's no urgent reason to change this in the
  back branches, so let's just revert that part of yesterday's commit
  rather than trying to design a better solution under time pressure.
  Also, add a regression test case showing what postgres_fdw does with
  FOR UPDATE/SHARE.  I'd blithely assumed there was one already, else
  I'd have realized yesterday that this code didn't work.

- Avoid instability in output of new REINDEX SCHEMA test.  The planner
  seems to like to do this join query as a hash join, making the
  output ordering machine-dependent; worse, it's a hash on OIDs, so
  that it's a bit astonishing that the result doesn't change from run
  to run even on one machine.  Add an ORDER BY to get consistent
  results.  Per buildfarm.  I also suppressed output from the final
  DROP SCHEMA CASCADE, to avoid occasional failures similar to those
  fixed in commit 81d815dc3ed74a7d.  That hasn't been observed in the
  buildfarm yet, but it seems likely to happen in future if we leave
  it as-is.

- Repair corner-case bug in array version of percentile_cont().  The
  code for advancing through the input rows overlooked the case that
  we might already be past the first row of the row pair now being
  considered, in case the previous percentile also fell between the
  same two input rows.  Report and patch by Andrew Gierth; logic
  rewritten a bit for clarity by me.

- Improve recovery target settings documentation.  Commit 815d71dee
  hadn't bothered to update the documentation to match the behavioral
  change, and a lot of other text in this section was badly in need of

- Update 9.4 release notes.  Set release date, do a final pass of
  wordsmithing, improve some other new-in-9.4 documentation.

Andrew Dunstan pushed:

- Add json_strip_nulls and jsonb_strip_nulls functions.  The functions
  remove object fields, including in nested objects, that have null as
  a value. In certain cases this can lead to considerably smaller
  datums, with no loss of semantic information.  Andrew Dunstan,
  reviewed by Pavel Stehule.

- Add several generator functions for jsonb that exist for json.  The
  functions are: to_jsonb(), jsonb_object(), jsonb_build_object(),
  jsonb_build_array(), jsonb_agg(), and jsonb_object_agg().  Also
  along the way some better logic is implemented in
  json_categorize_type() to match that in the newly implemented
  jsonb_categorize_type().  Andrew Dunstan, reviewed by Pavel Stehule
  and Alvaro Herrera.

- Add CINE option for CREATE TABLE Alexander Shulgin and CREATE
  MATERIALIZED VIEW.  Fabrízio de Royes Mello reviewed by Rushabh

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Petr (PJMODOS) Jelinek sent in two more revisions of a patch to add a
shutdown_at_recovery_target option to recovery.conf.

Euler Taveira de Oliveira sent in a patch to bring the documentation
of pg_receivexlog into line with its new functionality.

Peter Geoghegan sent in another revision of a patch to implement

Petr (PJMODOS) Jelinek sent in another revision of a patch to add a
sequence access method.

Alexander Shulgin sent in two revisions of a patch to track TRUNCATEs
in pgstat transaction stats.

Robert Haas and Heikki Linnakangas traded patches to advance local
xmin more aggressively.

Etsuro Fujita sent in another revision of a patch to allow foreign
tables to be part of table inheritance hierarchies.

Álvaro Herrera sent in a patch to implement logical column ordering.

David Rowley sent in another revision of a patch to allow removing
INNER JOIN under some circumstances.

Matt Newell sent in another revision of a patch to enable libpq

Heikki Linnakangas improved the performance of k-Nearest-Neighbor GiST
index searches using a Pairing Heap http://en.wikipedia.org/wiki/Pairing_heap.

Petr (PJMODOS) Jelinek sent in a patch to implement TABLESAMPLE.

Jeff Davis sent in another revision of a patch to implement a
memory-bounded HashAgg.

Mark Dilger sent in a WIP patch to allow printf formatting for Oids.

Kyotaro HORIGUCHI and Tom Lane traded patches to fix an issue where
some queries return duplicate rows after FOR UPDATE was blocked, in
other words, after getting HeapTupleUpdated in ExecLockRows.

Simon Riggs sent in another revision of a patch to turn off heap-only
tuple cleanup under some circumstances.

Heikki Linnakangas and Michael Paquier traded patches to refactor the
code for sync node detection.

Peter Eisentraut sent in a patch to ensure that VPATH builds not write
to the source tree.

Kyotaro HORIGUCHI sent in a patch to fix an issue where SELECT FROM
<inheritance parent> WHERE <cond> FOR UPDATE may return results which
does not match the <cond>.

Alexander Shulgin sent in another revision of a patch to turn
recovery.conf into GUCs.

Robert Haas sent in a patch to add two new concepts: parallel mode,
and parallel contexts.

Heikki Linnakangas sent in a patch to place pg_rewind, a tool that
allows repurposing an old master server as a new standby server, after
promotion, even if the old master was not shut down cleanly, in

Andreas Karlsson sent in a patch to reduce the required lock strength
of trigger and foreign key DDL.

Tatsuo Ishii sent in a patch to pgbench from exiting when the
pg_bench_* tables do not exist.

Michael Paquier sent in another revision of a patch to allow
compressing full-page writes.

Ali Akbar sent in a test for the patch to fix xpath() to return
namespace definitions.

Emre Hasegeli sent in another revision of a patch to implement a BRIN
range operator class.


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

Nicolas Thauvin | 8 Dec 10:50 2014

pg_back 1.1 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.

* Backup everything or just the databases you want with pg_dump
* Save global objects with pg_dumpall
* Optional configuration file
* Purge old backups according to the retention policy
* Works with all formats (including the new directory format)
* Simple

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:

Magnus Hagander | 9 Dec 14:38 2014

FOSDEM PGDay 2013 and Devroom - Schedule posted and registration open

We have finished the schedule for FOSDEM PGDay and the PostgreSQL
Devroom at FOSDEM 2015 - our apologies for the delay in processing!

You can find the schedule at:

You will find a number of great talks both on PostgreSQL usage and
development from great speakers. And of course, it will as usual
feature the Hallway Track, where you can network with fellow
PostgreSQL users!

We have also opened registration. Number of seats are limited, and as
we sold out last year, we recommend that you make your reservation as
soon as possible. Registration is €50 for all attendees for the PGDay
on Friday. No registration is necessary if you only want to attend the
Devroom at FOSDEM on Saturday.

Registration information can be found at:

Finally, we'd like to remind you that our rate with the conference
hotel (Marriott Brussels) will expire on Dec 15th. Therefore, we
recommend that you book your accommodations as soon as possible! See
http://fosdem2015.pgconf.eu/venue/ for details.

We hope to see you in Brussels in January!


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

David Fetter | 8 Dec 00:53 2014

== PostgreSQL Weekly News - December 07 2014 ==

== PostgreSQL Weekly News - December 07 2014 ==

Prague PostgreSQL Developer Day (P2D2) 2015 will be in Prague, Czech
Republic February 11-12, 2015.

== PostgreSQL Product News ==

oracle_fdw 1.1.0 released.

== PostgreSQL Jobs for December ==


== PostgreSQL Local ==

FOSDEM PGDay (January 30, 2015) and the FOSDEM PostgreSQL
Dev Room (January 31-February 1, 2015)

pgDaySF 2015 will be held March 10, 2015 in Burlingame, California.

The CfP is open for Nordic PostgreSQL Day 2015, which will be held
March 11, 2015 in  Copenhagen, Denmark.

The CfP for PGConf US 2015 is open through December 17th, 2014
Notifications will go out on January 10, 2014.  The event takes place
March 25-27, 2015 in NYC.

== 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 ==

Tom Lane pushed:

- Add a #define for the inet overlaps operator.  Extracted from
  pending inet selectivity patch.  The rest of it isn't quite ready to
  commit, but we might as well push this part so the patch doesn't
  have to track the moving target of pg_operator.h.

- Guard against bad "dscale" values in numeric_recv().  We were not
  checking to see if the supplied dscale was valid for the given digit
  array when receiving binary-format numeric values.  While dscale can
  validly be more than the number of nonzero fractional digits, it
  shouldn't be less; that case causes fractional digits to be hidden
  on display even though they're there and participate in arithmetic.
  Bug #12053 from Tommaso Sala indicates that there's at least one
  broken client library out there that sometimes supplies an incorrect
  dscale value, leading to strange behavior.  This suggests that
  simply throwing an error might not be the best response; it would
  lead to failures in applications that might seem to be working fine
  today.  What seems the least risky fix is to truncate away any
  digits that would be hidden by dscale.  This preserves the existing
  behavior in terms of what will be printed for the transmitted value,
  while preventing subsequent arithmetic from producing results
  inconsistent with that.  In passing, throw a specific error for the
  case of dscale being outside the range that will fit into a
  numeric's header.  Before you got "value overflows numeric format",
  which is a bit misleading.  Back-patch to all supported branches.

- Minor cleanup of function declarations for BRIN.  Get rid of
  PG_FUNCTION_INFO_V1() macros, which are quite inappropriate for
  built-in functions (possibly leftovers from testing as a loadable
  module?).  Also, fix gratuitous inconsistency between SQL-level and
  C-level names of the minmax support functions.

- Fix JSON aggregates to work properly when final function is
  re-executed.  Davide S. reported that json_agg() sometimes produced
  multiple trailing right brackets.  This turns out to be because
  json_agg_finalfn() attaches the final right bracket, and was doing
  so by modifying the aggregate state in-place.  That's verboten,
  though unfortunately it seems there's no way for nodeAgg.c to check
  for such mistakes.  Fix that back to 9.3 where the broken code was
  introduced.  In 9.4 and HEAD, likewise fix json_object_agg(), which
  had copied the erroneous logic.  Make some cosmetic cleanups as

- Improve error messages for malformed array input strings.  Make the
  error messages issued by array_in() uniformly follow the style
  ERROR: malformed array literal: "actual input string" DETAIL:
  specific complaint here and rewrite many of the specific complaints
  to be clearer.  The immediate motivation for doing this is a
  complaint from Josh Berkus that json_to_record() produced an
  unintelligible error message when dealing with an array item,
  because it tries to feed the JSON-format array value to array_in().
  Really it ought to be smart enough to perform JSON-to-Postgres array
  conversion, but that's a future feature not a bug fix.  In the
  meantime, this change is something we agreed we could back-patch
  into 9.4, and it should help de-confuse things a bit.

Magnus Hagander pushed:

- Fix missing space in documentation.  Ian Barwick

Heikki Linnakangas pushed:

- Put SSL_pending() call behind the new internal SSL API.  It seems
  likely that any SSL implementation will need a similar call, not
  just OpenSSL.

- Fix pg_xlogdump's calculation of full-page image data.  The old
  formula was completely bogus with the new WAL record format.

- Print wal_log_hints in the rm_desc routing of a parameter-change
  record.  It was an oversight in the original commit.  Also note in
  the sample config file that changing wal_log_hints requires a
  restart.  Michael Paquier. Backpatch to 9.4, where wal_log_hints was

- Print new track_commit_timestamp in rm_desc of a parameter-change
  record.  Michael Paquier

- Remove erroneous EXTRA_CLEAN line from Makefile.  After commit
  da34731, these files are not generated files anymore.  Adam

- Don't include file type bits in tar archive's mode field.  The "file
  mode" bits in the tar file header is not supposed to include the
  file type bits, e.g. S_IFREG or S_IFDIR. The file type is stored in
  a separate field. This isn't a problem in practice, all tar programs
  ignore the extra bits, but let's be tidy.  This came up in a
  discussion around bug #11949, reported by Hendrik Grewe, although
  this doesn't fix the issue with tar --append. That turned out to be
  a bug in GNU tar. Schilly's tartest program revealed this defect in
  the tar created by pg_basebackup.  This problem goes as far as we
  we've had pg_basebackup, but since this hasn't caused any problems
  in practice, let's be conservative and fix in master only.

- Give a proper error message if initdb password file is empty.  Used
  to say just "could not read password from file "...": Success",
  which isn't very informative.  Mats Erik Andersson. Backpatch to all
  supported versions.

Andrew Dunstan pushed:

- Fix hstore_to_json_loose's detection of valid JSON number values.
  We expose a function IsValidJsonNumber that internally calls the
  lexer for json numbers. That allows us to use the same test
  everywhere, instead of inventing a broken test for hstore
  conversions. The new function is also used in datum_to_json,
  replacing the code that is now moved to the new function.  Backpatch
  to 9.3 where hstore_to_json_loose was introduced.

Álvaro Herrera pushed:

- dummy_seclabel: add sql/, expected/, and .gitignores.  Michael

- pageinspect/BRIN: minor tweaks.  Michael Paquier.  Double-dash
  additions suggested by Peter Geoghegan

- Install kludges to fix check-world for src/test/modules.
  check-world failed in a completely clean tree, because
  src/test/modules fail to build unless errcodes.h is generated first.
  To fix this, install a dependency in src/test/modules' Makefile so
  that the necessary file is generated.  Even with this, running "make
  check" within individual module subdirs will still fail because the
  dependency is not considered there, but this case is less
  interesting and would be messier to fix.  check-world still failed
  with the above fix in place, this time because dummy_seclabel used
  LOAD to load the dynamic library, which doesn't work because the
   <at> libdir <at>  (expanded by the makefile) is expanded to the final install
  path, not the temporary installation directory used by make check.
  To fix, tweak things so that CREATE EXTENSION can be used instead,
  which solves the problem because the library path is expanded by the
  backend, which is aware of the true libdir.

- Fix typos

- doc: Fix markup.  In the spirit of
  d34b48a021b181e30c53280d336820740f67570b Per buildfarm member
  guaibasaurus, via Stefan Kaltenbrunner.

- Keep track of transaction commit timestamps.  Transactions can now
  set their commit timestamp directly as they commit, or an external
  transaction commit timestamp can be fed from an outside system using
  the new function TransactionTreeSetCommitTsData().  This data is
  crash-safe, and truncated at Xid freeze point, same as pg_clog.
  This module is disabled by default because it causes a performance
  hit, but can be enabled in postgresql.conf requiring only a server
  restart.  A new test in src/test/modules is included.  Catalog
  version bumped due to the new subdirectory within PGDATA and a
  couple of new SQL functions.  Authors: Álvaro Herrera and Petr
  Jelínek Reviewed to varying degrees by Michael Paquier, Andres
  Freund, Robert Haas, Amit Kapila, Fujii Masao, Jaime Casanova, Simon
  Riggs, Steven Singer, Peter Eisentraut

- Move security_label test.  Rather than have the core security_label
  regression test depend on the dummy_seclabel module, have that part
  of the test be executed by dummy_seclabel itself directly.  This
  simplifies the testing rig a bit; in particular it should silence
  the problems from the MSVC buildfarm phylum, which haven't yet
  gotten taught how to install src/test/modules.

Andres Freund pushed:

- Don't skip SQL backends in logical decoding for visibility
  computation.  The logical decoding patchset introduced
  PROC_IN_LOGICAL_DECODING flag PGXACT flag, that allows such backends
  to be skipped when computing the xmin horizon/snapshots. That's fine
  and sensible for walsenders streaming out logical changes, but not
  at all fine for SQL backends doing logical decoding. If the latter
  set that flag any change they have performed outside of logical
  decoding will not be regarded as visible - which e.g. can lead to
  that change being vacuumed away.  Note that not setting the flag for
  SQL backends isn't particularly bothersome - the SQL backend doesn't
  do streaming, so it only runs for a limited amount of time.  Per
  buildfarm member 'tick' and Alvaro.  Backpatch to 9.4, where logical
  decoding was introduced.

- Document that pg_stat_*_tables.n_tup_upd includes n_tup_hot_upd.
  Author: Peter Geoghegan

Peter Eisentraut pushed:

- Fix whitespace

- Move PG_AUTOCONF_FILENAME definition.  Since this is not something
  that a user should change, pg_config_manual.h was an inappropriate
  place for it.  In initdb.c, remove the use of the macro, because
  utils/guc.h can't be included by non-backend code.  But we hardcode
  all the other configuration file names there, so this isn't a

- Remove USE_VPATH make variable from PGXS.  The user can just set
  VPATH directly.  There is no need to invent another variable.

- Revert haphazard pgxs makefile changes.  These changes were
  originally submitted as "adds support for VPATH with USE_PGXS", but
  they are not necessary for VPATH support, so they just add more
  lines of code for no reason.

- Fix SHLIB_PREREQS use in contrib, allowing PGXS builds.  dblink and
  postgres_fdw use SHLIB_PREREQS = submake-libpq to build libpq first.
  This doesn't work in a PGXS build, because there is no libpq to
  build.  So just omit setting SHLIB_PREREQS in this case.  Note that
  PGXS users can still use SHLIB_PREREQS (although it is not
  documented).  The problem here is only that contrib modules can be
  built in-tree or using PGXS, and the prerequisite is only applicable
  in the former case.  Commit 6697aa2bc25c83b88d6165340348a31328c35de6
  previously attempted to address this by creating a somewhat fake
  submake-libpq target in Makefile.global.  That was not the right
  fix, and it was also done in a nonportable way, so revert that.

- Fix PGXS vpath build when PostgreSQL is built with vpath.  PGXS
  computes srcdir from VPATH, PostgreSQL proper computes VPATH from
  srcdir, and doing both results in an error from make.
  Conditionalize so only one of these takes effect.

Robert Haas pushed:

- Don't dump core if pq_comm_reset() is called before pq_init().  This
  can happen if an error occurs in a standalone backend.  This bug was
  introduced by commit 2bd9e412f92bc6a68f3e8bcb18e04955cc35001d.
  Reported by Álvaro Herrera.

Simon Riggs pushed:

- Tweaks for recovery_target_action.  Rename parameter
  action_at_recovery_target to recovery_target_action suggested by
  Christoph Berg.  Place into recovery.conf suggested by Fujii Masao,
  replacing (deprecating) earlier parameters, per Michael Paquier.

- Event Trigger for table_rewrite.  Generate a table_rewrite event
  when ALTER TABLE attempts to rewrite a table. Provide helper
  functions to identify table and reason.  Intended use case is to
  help assess or to react to schema changes that might hold exclusive
  locks for long periods.  Dimitri Fontaine, triggering an edit by
  Simon Riggs Reviewed in detail by Michael Paquier

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Jim Nasby sent in a patch to log cleanup lock acquisition failures in

Tom Lane and Emre Hasegeli traded patches to add selectivity
estimation for inet operators.

Dilip Kumar sent in another revision of a patch to allow vacuumdb to
work in parallel.

Jeff Janes sent in a patch to attempt to fix startup/recovery problems
with unlogged tables by adding an unconditional and unchecked unlink
before the copy.

Jeff Davis sent in another revision of a patch to do better memory

SAWADA Masahiko and Michael Paquier traded patches to implement

Alexander Shulgin sent in another revision of a patch to turn
recovery.conf into GUCs.

Craig Ringer and David Rowley traded patches to use the faster, higher
precision timer API on Windows when available.

Teodor Sigaev sent in another revision of a patch to implement a
compression method for SP-GiST.

Kaigai Kouhei sent in another revision of a patch to implement some
interfaces in the custom scan API.

Peter Geoghegan sent in patches to: 1. add new abbreviation
infrastructure to sortsupport, and add a single client of this new
infrastructure, the text sortsupport routine. 2. Estimate total number
of rows to be sorted, and 3. Alter comments to reflect current naming.

Matt Newell sent in two revisions of a patch to support pipelining in

Peter Geoghegan sent in another revision of a patch to create a
Levenshtein distance column HINT for nonexistent columns.

Amit Kapila and David Rowley traded patches to do sequential scans in

Peter Geoghegan sent in a patch to clarify the documentation of
pg_stat_all_tables to mention that pg_stat_*_tables.n_tup_upd includes
HOT updates.

Heikki Linnakangas sent in another revision of a patch to implement an
SSL regression test suite.

Michael Paquier sent in a patch to add missing descriptions for two

Petr (PJMODOS) Jelinek sent in a patch to rename
action_at_recovery_target to recovery_target_action everywhere.

Michael Paquier sent in two more revisions of a patch to implement
compression of full-pages writes.

Stephen Frost sent in a patch to change usage of GetUserId() to

Stephen Frost sent in a patch to change the default for include_realm
to zero.

Adam Brightwell sent in two more revisions of a patch to add a role
attribute bitmask catalog representation.

Tomas Vondra sent in another revision of a patch to implement an
adaptive ndistinct estimator.

Tomas Vondra sent in a patch to gracefully increasing NTUP_PER_BUCKET
instead of batching.


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

Albe Laurenz | 3 Dec 11:58 2014

oracle_fdw 1.1.0 released

I am happy to announce release 1.1.0 of the Foreign Data Wrapper for Oracle.

Apart from a couple of bugfixes, the most notable change is the addition of
efficient translation between PostGIS "geometry" and Oracle SDO_GEOMETRY.

This would not have been possible without Oslandia's code contribution.
Thank you, Vincent Mora and Vincent Picavet!

Project page: http://laurenz.github.io/oracle_fdw/

Source repository: https://github.com/laurenz/oracle_fdw


Laurenz Albe


Sent via pgsql-announce mailing list (pgsql-announce <at> postgresql.org)
To make changes to your subscription:
Tomas Vondra | 1 Dec 01:42 2014

Prague PostgreSQL Developer Day 2015 / announcement & call for papers

Prague PostgreSQL Developer Day 2015 (P2D2 2015) is a two-day conference
that will be held on February 11-12 2015 in Prague, Czech Republic. The
location will be announced in a few days.

The conference will have two parts:

trainings on Feb 11 - sessions on practical topics, for small number of
                      attendees (either half or whole day)

conference on Feb 12 - a regular conference (with ~45 minute talks for
                       large number of attendees).

The conference website is http://www.p2d2.cz/ (czech language only), but
feel free to contact us at info <at> p2d2.cz in case of any questions.

The event is organized for local users, therefore most of the talks will
be presented in czech. However as in previous years, we're looking for a
few english-speaking guests, We can offer a subsidy covering travel and
hotel expenses up to ~500 EUR (should be enough for 2 nights in a hotel
and air ticket from around Europe).

If you'd like to present something interesing and related to
PostgreSQL at the  conference, send us some basic info at info <at> p2d2.cz.
We especially need this:

 * contact info (name, phone, company)
 * topic of the session (abstract, what is the goal, etc.)
 * expected length (usually 40 minutes)
 * whether you need help with covering travel/hotel expenses

***** Schedule *****

The call for papers ends on January 5, 2015 (end of day). The talks
schedule will be announced till January 10 (and the registration will
open shortly after that).

***** Contacts *****

The conference is organized by Czech and Slovak PostgreSQL User Group.
In case of any questions, contact us at info <at> p2d2.cz.

kind regards
Tomas Vondra / CSPUG


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

David Fetter | 1 Dec 01:40 2014

== PostgreSQL Weekly News - November 30 2014 ==

== PostgreSQL Weekly News - November 30 2014 ==

The PGCon 2015 call for papers will go out on Dec 1 2014.

== PostgreSQL Jobs for November ==


== PostgreSQL Local ==

PostgreSQL Conference 2014 Tokyo will be held December 5 in Shinagawa.

FOSDEM PGDay (January 30, 2015) and the FOSDEM PostgreSQL
Dev Room (January 31-February 1, 2015)

pgDaySF 2015 will be held March 10, 2015 in Burlingame, California.

The CfP is open for Nordic PostgreSQL Day 2015, which will be held
March 11, 2015 in  Copenhagen, Denmark.

The CfP for PGConf US 2015 is open through December 17th, 2014
Notifications will go out on January 10, 2014.  The event takes place
March 25-27, 2015 in NYC.

== 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 ==

Heikki Linnakangas pushed:

- Distinguish XLOG_FPI records generated for hint-bit updates.  Add a
  new XLOG_FPI_FOR_HINT record type, and use that for full-page images
  generated for hint bit updates, when checksums are enabled. The new
  record type is replayed exactly the same as XLOG_FPI, but allows
  them to be tallied separately e.g. in pg_xlogdump.

- Add a few paragraphs to B-tree README explaining L&Y algorithm.
  This gives an overview of what Lehman & Yao's paper is all about, so
  that you can understand the rest of the README without having to
  read the paper.  Per discussion with Peter Geoghegan and others.

- Make Port->ssl_in_use available, even when built with !USE_SSL.
  Code that check the flag no longer need #ifdef's, which is more
  convenient.  In particular, makes it easier to write extensions that
  depend on it.  In the passing, modify sslinfo's ssl_is_used function
  to check ssl_in_use instead of the OpenSSL specific 'ssl' pointer.
  It doesn't make any difference currently, as sslinfo is only
  compiled when built with OpenSSL, but seems cleaner anyway.

- Check return value of strdup() in libpq connection option parsing.
  An out-of-memory in most of these would lead to strange behavior,
  like connecting to a different database than intended, but some
  would lead to an outright segfault.  Alex Shulgin and me. Backpatch
  to all supported versions.

- Allow "dbname" from connection string to be overridden in
  PQconnectDBParams If the "dbname" attribute in PQconnectDBParams
  contained a connection string or URI (and expand_dbname = TRUE), the
  database name from the connection string could not be overridden by
  a subsequent "dbname" keyword in the array. That was not
  intentional; all other options can be overridden.  Furthermore, any
  subsequent "dbname" caused the connection string from the first
  dbname value to be processed again, overriding any values for the
  same options that were given between the connection string and the
  second dbname option.  In the passing, clarify in the docs that only
  the first dbname option in the array is parsed as a connection
  string.  Alex Shulgin. Backpatch to all supported versions.

- Allow using connection URI in primary_conninfo.  The old method of
  appending options to the connection string didn't work if the
  primary_conninfo was a postgres:// style URI, instead of a
  traditional connection string. Use PQconnectdbParams instead.  Alex

- Remove dead function prototype.  It was added in commit efc16ea5,
  but never defined.

- Fix assertion failure at end of PITR.  InitXLogInsert() cannot be
  called in a critical section, because it allocates memory. But
  CreateCheckPoint() did that, when called for the end-of-recovery
  checkpoint by the startup process.  In the passing, fix the scratch
  space allocation in InitXLogInsert to go to the right memory
  context. Also update the comment at InitXLOGAccess, which hasn't
  been totally accurate since hot standby was introduced (in a hot
  standby backend, InitXLOGAccess isn't called at backend startup).
  Reported by Michael Paquier

Tom Lane pushed:

- Allow simplification of EXISTS() subqueries containing LIMIT.  The
  locution "EXISTS(SELECT ... LIMIT 1)" seems to be rather common
  among people who don't realize that the database already performs
  optimizations equivalent to putting LIMIT 1 in the sub-select.
  Unfortunately, this was actually making things worse, because it
  prevented us from optimizing such EXISTS clauses into semi or anti
  joins.  Teach simplify_EXISTS_query() to suppress constant-positive
  LIMIT clauses.  That fixes the semi/anti-join case, and may help
  marginally even for cases that have to be left as sub-SELECTs.
  Marti Raudsepp, reviewed by David Rowley

- Support arrays as input to array_agg() and ARRAY(SELECT ...).  These
  cases formerly failed with errors about "could not find array type
  for data type".  Now they yield arrays of the same element type and
  one higher dimension.  The implementation involves creating
  functions with API similar to the existing accumArrayResult()
  family.  I (tgl) also extended the base family by adding an
  initArrayResult() function, which allows callers to avoid
  special-casing the zero-inputs case if they just want an empty array
  as result.  (Not all do, so the previous calling convention remains
  valid.) This allowed simplifying some existing code in xml.c and
  plperl.c.  Ali Akbar, reviewed by Pavel Stehule, significantly
  modified by me

- De-reserve most statement-introducing keywords in plpgsql.  Add a
  bit of context sensitivity to plpgsql_yylex() so that it can
  recognize when the word it is looking at is the first word of a new
  statement, and if so whether it is the target of an assignment
  statement.  When we are at start of statement and it's not an
  assignment, we can prefer recognizing unreserved keywords over
  recognizing variable names, thereby allowing most statements'
  initial keywords to be demoted from reserved to unreserved status.
  This is rather useful already (there are 15 such words that get
  demoted here), and what's more to the point is that future patches
  proposing to add new plpgsql statements can avoid objections about
  having to add new reserved words.  The keywords BEGIN, DECLARE, FOR,
  FOREACH, LOOP, WHILE need to remain reserved because they can be
  preceded by block labels, and the logic added here doesn't
  understand about block labels.  In principle we could probably fix
  that, but it would take more than one token of lookback and the
  benefit doesn't seem worth extra complexity.  Also note I didn't
  de-reserve EXECUTE, because it is used in more places than just
  statement start.  It's possible it could be de-reserved with more
  work, but that would be an independent fix.  In passing, also
  de-reserve COLLATE and DEFAULT, which shouldn't have been reserved
  in the first place since they only need to be recognized within
  DECLARE sections.

- Fix uninitialized-variable warning.  In passing, add an Assert
  defending the presumption that bytes_left is positive to start with.
  (I'm not exactly convinced that using an unsigned type was such a
  bright thing here, but let's at least do this much.)

- Free libxml2/libxslt resources in a safer order.  Mark Simonetti
  reported that libxslt sometimes crashes for him, and that swapping
  xslt_process's object-freeing calls around to do them in reverse
  order of creation seemed to fix it.  I've not reproduced the crash,
  but valgrind clearly shows a reference to already-freed memory,
  which is consistent with the idea that shutdown of the
  xsltTransformContext is trying to reference the already-freed
  stylesheet or input document.  With this patch, valgrind is no
  longer unhappy.  I have an inquiry in to see if this is a libxslt
  bug or if we're just abusing the library; but even if it's a library
  bug, we'd want to adjust our code so it doesn't fail with unpatched
  libraries.  Back-patch to all supported branches, because we've been
  doing this in the wrong(?) order for a long time.

- Improve typcache: cache negative lookup results, add invalidation
  logic.  Previously, if the typcache had for example tried and failed
  to find a hash opclass for a given data type, it would nonetheless
  repeat the unsuccessful catalog lookup each time it was asked again.
  This can lead to a significant amount of useless bufmgr traffic, as
  in a recent report from Scott Marlowe.  Like the catalog caches,
  typcache should be able to cache negative results.  This patch
  arranges that by making use of separate flag bits to remember
  whether a particular item has been looked up, rather than treating a
  zero OID as an indicator that no lookup has been done.  Also,
  install a credible invalidation mechanism, namely watching for inval
  events in pg_opclass.  The sole advantage of the lack of negative
  caching was that the code would cope if operators or opclasses got
  added for a type mid-session; to preserve that behavior we have to
  be able to invalidate stale lookup results.  Updates in pg_opclass
  should be pretty rare in production systems, so it seems sufficient
  to just invalidate all the dependent data whenever one happens.
  Adding proper invalidation also means that this code will now react
  sanely if an opclass is dropped mid-session.  Arguably, that's a
  back-patchable bug fix, but in view of the lack of complaints from
  the field I'll refrain from back-patching.  (Probably, in most cases
  where an opclass is dropped, the data type itself is dropped soon
  after, so that this misfeasance has no bad consequences.)

- Improve performance of OverrideSearchPathMatchesCurrent().  This
  function was initially coded on the assumption that it would not be
  performance-critical, but that turns out to be wrong in workloads
  that are heavily dependent on the speed of plpgsql functions.  Speed
  it up by hard-coding the comparison rules, thereby avoiding
  palloc/pfree traffic from creating and immediately freeing an
  OverrideSearchPath object.  Per report from Scott Marlowe.

- Add bms_next_member(), and use it where appropriate.  This patch
  adds a way of iterating through the members of a bitmapset
  nondestructively, unlike the old way with bms_first_member().  While
  bms_next_member() is very slightly slower than bms_first_member()
  (at least for typical-size bitmapsets), eliminating the need to
  palloc and pfree a temporary copy of the target bitmapset is a
  significant win.  So this method should be preferred in all cases
  where a temporary copy would be necessary.  Tom Lane, with
  suggestions from Dean Rasheed and David Rowley

- Add bms_get_singleton_member(), and use it where appropriate.  This
  patch adds a function that replaces a bms_membership() test followed
  by a bms_singleton_member() call, performing both the test and the
  extraction of a singleton set's member in one scan of the bitmapset.
  The performance advantage over the old way is probably minimal in
  current usage, but it seems worthwhile on notational grounds anyway.
  David Rowley

- Fix minor bugs in commit 30bf4689a96cd283af33edcdd6b7210df3f20cd8 et
  al.  Coverity complained that the "else" added to fillPGconn() was
  unreachable, which it was.  Remove the dead code.  In passing,
  rearrange the tests so as not to bother trying to fetch values for
  options that can't be assigned.  Pre-9.3 did not have that issue,
  but it did have a "return" that should be "goto oom_error" to ensure
  that a suitable error message gets filled in.

Stephen Frost pushed:

- Suppress DROP CASCADE notices in regression tests.  In the
  regression tests, when doing cascaded drops, we need to suppress the
  notices from DROP CASCADE or there can be transient regression
  failures as the order of drops can depend on the physical row order
  in pg_depend.  Report and fix suggestion from Tom.

- Add int64 -> int8 mapping to genbki.  Per discussion with Tom and
  Andrew, 64bit integers are no longer a problem for the catalogs, so
  go ahead and add the mapping from the C int64 type to the int8 SQL
  identification to allow using them.  Patch by Adam Brightwell

- Rename pg_rowsecurity -> pg_policy and other fixes As pointed out by
  Robert, we should really have named pg_rowsecurity pg_policy, as the
  objects stored in that catalog are policies.  This patch fixes that
  and updates the column names to start with 'pol' to match the new
  catalog name.  The security consideration for COPY with row level
  security, also pointed out by Robert, has also been addressed by
  remembering and re-checking the OID of the relation initially
  referenced during COPY processing, to make sure it hasn't changed
  under us by the time we finish planning out the query which has been
  built.  Robert and Alvaro also commented on missing OCLASS and
  OBJECT entries for POLICY (formerly ROWSECURITY or POLICY,
  depending) in various places.  This patch fixes that too, which also
  happens to add the ability to COMMENT on policies.  In passing,
  attempt to improve the consistency of messages, comments, and
  documentation as well.  This removes various incarnations of
  'row-security', 'row-level security', 'Row-security', etc, in favor
  of 'policy', 'row level security' or 'row_security' as appropriate.
  Happy Thanksgiving!

Simon Riggs pushed:

- Remove extraneous SGML tag

- action_at_recovery_target recovery config option.
  action_at_recovery_target = pause | promote | shutdown.  Petr
  Jelinek.  Reviewed by Muhammad Asif Naeem, Fujji Masao and Simon

Robert Haas pushed:

- Attempt to suppress uninitialized variable warning.  Report by
  Heikki Linnakangas.

- Add infrastructure to save and restore GUC values.  This is further
  infrastructure for parallelism.  Amit Khandekar, Noah Misch, Robert

Peter Eisentraut pushed:

- doc: Fix markup

Fujii Masao pushed:

- Mark response messages for translation in pg_isready.  Back-patch to
  9.3 where pg_isready was added.  Mats Erik Andersson

- Make \watch respect the user's \pset null setting.  Previously
  \watch always ignored the user's \pset null setting.  \pset null
  setting should be ignored for \d and similar queries.  For those,
  the code can reasonably have an opinion about what the presentation
  should be like, since it knows what SQL query it's issuing. This
  argument surely doesn't apply to \watch, so this commit makes \watch
  use the user's \pset null setting.  Back-patch to 9.3 where \watch
  was added.

- Add tab-completion for ALTER TABLE ALTER CONSTRAINT in psql.
  Back-patch to 9.4 where ALTER TABLE ALTER CONSTRAINT was added.
  Michael Paquier, bug reported by Andrey Lizenko.

Álvaro Herrera pushed:

- Update transaction README for persistent multixacts.  Multixacts are
  now maintained during recovery, but the README didn't get the memo.
  Backpatch to 9.3, where the divergence was introduced.

- Fix BRIN operator family definitions.  The original definitions were
  leaving no room for cross-type operators, so queries that compared a
  column of one type against something of a different type were not
  taking advantage of the index.  Fix by making the opfamilies more
  like the ones for Btree, and include a few cross-type operator
  classes.  Catalog version bumped.  Per complaints from Hubert
  Lubaczewski, Mark Wong, Heikki Linnakangas.

Noah Misch pushed:

- Reimplement 9f80f4835a55a1cbffcda5d23a617917f3286c14 with
  PQconninfo().  Apart from ignoring "hostaddr" set to the empty
  string, this behaves identically to its predecessor.  Back-patch to
  9.4, where the original commit first appeared.  Reviewed by Fujii

- Revert "Add libpq function PQhostaddr()." This reverts commit
  9f80f4835a55a1cbffcda5d23a617917f3286c14.  The function returned the
  raw value of a connection parameter, a task served by PQconninfo().
  The next commit will reimplement the psql \conninfo change that way.
  Back-patch to 9.4, where that commit first appeared.

- Remove PQhostaddr() from 9.4 release notes.  Back-patch to 9.4, like
  the feature's removal.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Dilip Kumar sent in another revision of a patch to allow doing
vacuumdb in parallel.

Amit Langote sent in a patch to fix a typo in a comment in

Álvaro Herrera sent in another revision of a patch to track commit

Michael Paquier and Rahila Syed traded patches to improve compression
of full-page writes.

Adam Brightwell sent in two more revisions of a patch to implement role
attribute bitmask catalog representation.

Ali Akbar sent in another revision of a patch to fix xpath() to return
namespace definitions.

Marius Timmer sent in a patch to add an option to EXPLAIN to include
sort order information in VERBOSE mode.

Michael Paquier sent in two revisions of a patch to allow compiling
C++ extensions on MSVC using scripts in src/tools.

Ants Aasma sent in a patch to use RTLD_DEEPBIND to handle symbol
conflicts in loaded libraries.

Pavel Stehule sent in another revision of a patch to add an ASSERT
statement to PL/pgsql.

Peter Geoghegan sent in another revision of a patch to implement

Alexander Shulgin sent in another revision of a patch to add an
'ssl_protocols' configuration option.

Álvaro Herrera sent in a flock of patches to move test modules from
contrib to src/test/modules.

Mats Erik Andersson sent in a patch to improve error recovery in
initdb when the password file is empty.

Mats Erik Andersson sent in a patch to localize the text response of

Ian Lawrence Barwick sent in a patch to implement DDL deparsing.

Michael Paquier sent in another revision of a patch to implement
fillfactor for GIN indexes.

Alexander Shulgin sent in a patch to allow TOAST tables to be moved to
a different tablespace.

Peter Eisentraut sent in a patch to allow using Core Foundation locale
functions on OSX.

Tom Lane sent in a patch to fix some misleading error messages in

Michael Paquier sent in a patch to add a new error code to track
unsupported contexts.

Tomas Vondra sent in another revision of a patch to lower array_agg's
memory requirements.

Stephen Frost sent in another revision of a patch to clean up
GetUserId() for pg_stat and pg_signal.

Noah Misch sent in another revision of a patch to help secure "make

David Rowley sent in a patch to allow removing inner JOINs in some

Andrew Dunstan sent in a patch to fix the check for whether something
is considered a number in JSON.

Ian Lawrence Barwick sent in a patch to fix a typo/spacing problem in


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

David Fetter | 24 Nov 15:34 2014

== PostgreSQL Weekly News - November 23 2014 ==

== PostgreSQL Weekly News - November 23 2014 ==

PostgreSQL 9.4RC1 released.  Test!

The CfP for FOSDEM PGDay and Devroom 2015 is open.

PostgreSQL Conference 2014 Tokyo will be held December 5 in Shinagawa.

pgDaySF 2015 will be held March 10, 2015 in Burlingame, California.

== PostgreSQL Product News ==

Ora2PG 14.1, a migration system for moving from Oracle to PostgreSQL, released.

== PostgreSQL Jobs for November ==


== PostgreSQL Local ==

PG-Cuba will be November 25 and 26, 2014

The CfP for FOSDEM PGDay (January 30, 2015) and the FOSDEM PostgreSQL
Dev Room (January 31-February 1, 2015) are open until November 24th,

The CfP is open for Nordic PostgreSQL Day 2015, which will be held
March 11, 2015 in  Copenhagen, Denmark.

The CfP for PGConf US 2015 is open through December 17th, 2014
Notifications will go out on January 10, 2014.  The event takes place
March 25-27, 2015 in NYC.

== 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 ==

Álvaro Herrera pushed:

- Fix relpersistence setting in reindex_index.  Buildfarm members with
  CLOBBER_CACHE_ALWAYS advised us that commit 85b506bbfc2937 was
  mistaken in setting the relpersistence value of the index directly
  in the relcache entry, within reindex_index.  The reason for the
  failure is that an invalidation message that comes after mucking
  with the relcache entry directly, but before writing it to the
  catalogs, would cause the entry to become rebuilt in place from
  catalogs with the old contents, losing the update.  Fix by passing
  the correct persistence value to RelationSetNewRelfilenode instead;
  this routine also writes the updated tuple to pg_class, avoiding the
  problem.  Suggested by Tom Lane.

Heikki Linnakangas pushed:

- Fix WAL-logging of B-tree "unlink halfdead page" operation.  There
  was some confusion on how to record the case that the operation
  unlinks the last non-leaf page in the branch being deleted.
  _bt_unlink_halfdead_page set the "topdead" field in the WAL record
  to the leaf page, but the redo routine assumed that it would be an
  invalid block number in that case. This commit fixes
  _bt_unlink_halfdead_page to do what the redo routine expected.  This
  code is new in 9.4, so backpatch there.

- Remove obsolete debugging option, RTDEBUG.  The r-tree AM that used
  it was removed back in 2005.  Peter Geoghegan

- Add test cases for indexam operations not currently covered.  That
  includes VACUUM on GIN, GiST and SP-GiST indexes, and B-tree indexes
  large enough to cause page deletions in B-tree. Plus some other
  special cases.  After this patch, the regression tests generate all
  different WAL record types. Not all branches within the redo
  functions are covered, but it's a step forward.

- Silence compiler warning about variable being used uninitialized.
  It's a false positive - the variable is only used when 'onleft' is
  true, and it is initialized in that case. But the compiler doesn't
  necessarily see that.

- Revamp the WAL record format.  Each WAL record now carries
  information about the modified relation and block(s) in a
  standardized format. That makes it easier to write tools that need
  that information, like pg_rewind, prefetching the blocks to speed up
  recovery, etc.  There's a whole new API for building WAL records,
  replacing the XLogRecData chains used previously. The new API
  consists of XLogRegister* functions, which are called for each
  buffer and chunk of data that is added to the record. The new API
  also gives more control over when a full-page image is written, by
  passing flags to the XLogRegisterBuffer function.  This also
  simplifies the XLogReadBufferForRedo() calls. The function can dig
  the relation and block number from the WAL record, so they no longer
  need to be passed as arguments.  For the convenience of redo
  routines, XLogReader now disects each WAL record after reading it,
  copying the main data part and the per-block data into MAXALIGNed
  buffers. The data chunks are not aligned within the WAL record, but
  the redo routines can assume that the pointers returned by
  XLogRecGet* functions are. Redo routines are now passed the
  XLogReaderState, which contains the record in the already-disected
  format, instead of the plain XLogRecord.  The new record format also
  makes the fixed size XLogRecord header smaller, by removing the
  xl_len field. The length of the "main data" portion is now stored at
  the end of the WAL record, and there's a separate header after
  XLogRecord for it. The alignment padding at the end of XLogRecord is
  also removed. This compansates for the fact that the new format
  would otherwise be more bulky than the old format.  Reviewed by
  Andres Freund, Amit Kapila, Michael Paquier, Alvaro Herrera, Fujii

- Add pageinspect functions for inspecting GIN indexes.  Patch by me,
  Peter Geoghegan and Michael Paquier, reviewed by Amit Kapila.

- Fix bogus comments in XLogRecordAssemble.  Pointed out by Michael

- Add a comment to regress.c explaining what it contains.  Ian Barwick

- No need to call XLogEnsureRecordSpace when the relation is unlogged.
  Amit Kapila

Tom Lane pushed:

- Update time zone data files to tzdata release 2014j.  DST law
  changes in the Turks & Caicos Islands (America/Grand_Turk) and in
  Fiji.  New zone Pacific/Bougainville for portions of Papua New
  Guinea.  Historical changes for Korea and Vietnam.

- Update 9.4 release notes for commits through today.

- Fix some bogus direct uses of realloc().  pg_dump/parallel.c was
  using realloc() directly with no error check.  While the odds of an
  actual failure here seem pretty low, Coverity complains about it, so
  fix by using pg_realloc() instead.  While looking for other
  instances, I noticed a couple of places in psql that hadn't gotten
  the memo about the availability of pg_realloc.  These aren't bugs,
  since they did have error checks, but verbosely inconsistent code is
  not a good thing.  Back-patch as far as 9.3.  9.2 did not have
  pg_dump/parallel.c, nor did it have pg_realloc available in all
  frontend code.

- Update comments in find_typedef.  These comments don't seem to have
  been touched in a long time.  Make them describe the current
  implementation rather than what was here last century, and be a bit
  more explicit about the unreferenced-typedefs issue.

- Don't require bleeding-edge timezone data in timestamptz regression
  test.  The regression test cases added in commits b2cbced9e et al
  depended in part on the Russian timezone offset changes of Oct 2014.
  While this is of no particular concern for a default Postgres build,
  it was possible for a build using --with-system-tzdata to fail the
  tests if the system tzdata database wasn't au courant.  Bjorn Munch
  and Christoph Berg both complained about this while packaging
  9.4rc1, so we probably shouldn't insist on the system tzdata being
  up-to-date.  Instead, make an equivalent test using a zone change
  that occurred in Venezuela in 2007.  With this patch, the regression
  tests should pass using any tzdata set from 2012 or later.  (I can't
  muster much sympathy for somebody using --with-system-tzdata on a
  machine whose system tzdata is more than three years out-of-date.)

- Improve documentation's description of JOIN clauses.  In bug #12000,
  Andreas Kunert complained that the documentation was misleading in
  saying "FROM T1 CROSS JOIN T2 is equivalent to FROM T1, T2".  That's
  correct as far as it goes, but the equivalence doesn't hold when you
  consider three or more tables, since JOIN binds more tightly than
  comma.  I added a <note> to explain this, and ended up rearranging
  some of the existing text so that the note would make sense in
  context.  In passing, rewrite the description of JOIN USING, which
  was unnecessarily vague, and hadn't been helped any by somebody's
  reliance on markup as a substitute for clear writing.  (Mostly this
  involved reintroducing a concrete example that was unaccountably
  removed by commit 032f3b7e166cfa28.) Back-patch to all supported

- Add missing case for CustomScan.  Per KaiGai Kohei.  In passing
  improve formatting of some code added in commit 30d7ae3c, because
  otherwise pgindent will make a mess of it.

- Remove no-longer-needed phony typedefs in genbki.h.  Now that we
  have a policy of hiding varlena catalog fields behind "#ifdef
  CATALOG_VARLEN", there is no need for their type names to be
  acceptable to the C compiler.  And experimentation shows that it
  does not matter to pgindent either.  (If it did, we'd have problems
  anyway, since these typedefs are unreferenced so far as the C
  compiler is concerned, and find_typedef fails to identify such
  typedefs.) Hence, remove the phony typedefs that genbki.h provided
  to make some varlena field definitions compilable.  In passing,
  rearrange #define's into what seemed a more logical order.

- Fix another oversight in CustomScan patch.  disuse_physical_tlist()
  must work for all plan types handled by create_scan_plan().

- Fix another oversight in CustomScan patch.  execCurrent.c's
  search_plan_tree() must recognize a CustomScan on the target
  relation.  This would only be helpful for custom providers that
  support CurrentOfExpr quals, which is probably a bit far-fetched,
  but it's not impossible I think.  But even without assuming that, we
  need to recognize a scanned-relation match so that we will properly
  throw error if the desired relation is being scanned with both a
  CustomScan and a regular scan (ie, self-join).  Also recognize
  ForeignScanState for similar reasons.  Supporting WHERE CURRENT OF
  on a foreign table is probably even more far-fetched than it is for
  custom scans, but I think in principle you could do it with
  postgres_fdw (or another FDW that supports the ctid column).  This
  would be a back-patchable bug fix if existing FDWs handled
  CurrentOfExpr, but I doubt any do so I won't bother back-patching.

- Initial code review for CustomScan patch.  Get rid of the pernicious
  entanglement between planner and executor headers introduced by
  commit 0b03e5951bf0a1a8868db13f02049cf686a82165.  Also, rearrange
  the CustomFoo struct/typedef definitions so that all the typedef
  names are seen as used by the compiler.  Without this pgindent will
  mess things up a bit, which is not so important perhaps, but it also
  removes a bizarre discrepancy between the declaration arrangement
  used for CustomExecMethods and that used for CustomScanMethods and
  CustomPathMethods.  Clean up the commentary around
  ExecSupportsMarkRestore to reflect the rather large change in its
  API.  Const-ify register_custom_path_provider's argument.  This
  necessitates casting away const in the function, but that seems
  better than forcing callers of the function to do so (or else not
  const-ify their method pointer structs, which was sort of the whole
  point).  De-export fix_expr_common.  I don't like the exporting of
  fix_scan_expr or replace_nestloop_params either, but this one surely
  has got little excuse.

- Remove dead code supporting mark/restore in SeqScan, TidScan,
  ValuesScan.  There seems no prospect that any of this will ever be
  useful, and indeed it's questionable whether some of it would work
  if it ever got called; it's certainly not been exercised in a very
  long time, if ever. So let's get rid of it, and make the comments
  about mark/restore in execAmi.c less wishy-washy.  The mark/restore
  support for Result nodes is also currently dead code, but that's due
  to planner limitations not because it's impossible that it could be
  useful.  So I left it in.

- Simplify API for initially hooking custom-path providers into the
  planner.  Instead of register_custom_path_provider and a
  CreateCustomScanPath callback, let's just provide a standard
  function hook in set_rel_pathlist.  This is more flexible than what
  was previously committed, is more like the usual conventions for
  planner hooks, and requires less support code in the core.  We had
  discussed this design (including centralizing the set_cheapest()
  calls) back in March or so, so I'm not sure why it wasn't done like
  this already.

- Rearrange CustomScan API.  Make it work more like FDW plans do:
  instead of assuming that there are expressions in a CustomScan plan
  node that the core code doesn't know about, insist that all
  subexpressions that need planner attention be in a "custom_exprs"
  list in the Plan representation.  (Of course, the custom plugin can
  break the list apart again at executor initialization.) This lets us
  revert the parts of the patch that exposed setrefs.c and subselect.c
  processing to the outside world.  Also revert the
  GetSpecialCustomVar stuff in ruleutils.c; that concept may work in
  future, but it's far from fully baked right now.

- Fix mishandling of system columns in FDW queries.  postgres_fdw
  would send query conditions involving system columns to the remote
  server, even though it makes no effort to ensure that system columns
  other than CTID match what the remote side thinks.  tableoid, in
  particular, probably won't match and might have some use in queries.
  Hence, prevent sending conditions that include non-CTID system
  columns.  Also, create_foreignscan_plan neglected to check local
  restriction conditions while determining whether to set fsSystemCol
  for a foreign scan plan node.  This again would bollix the results
  for queries that test a foreign table's tableoid.  Back-patch the
  first fix to 9.3 where postgres_fdw was introduced.  Back-patch the
  second to 9.2.  The code is probably broken in 9.1 as well, but the
  patch doesn't apply cleanly there; given the weak state of support
  for FDWs in 9.1, it doesn't seem worth fixing.  Etsuro Fujita,
  reviewed by Ashutosh Bapat, and somewhat modified by me

- Get rid of redundant production in plpgsql grammar.  There may once
  have been a reason for the intermediate proc_stmts production in the
  plpgsql grammar, but it isn't doing anything useful anymore, so
  let's collapse it into proc_sect.  Saves some code and probably a
  small number of nanoseconds per statement list.  In passing,
  correctly alphabetize keyword lists to match pl_scanner.c; note that
  for "rowtype" vs "row_count", pl_scanner.c must sort on the basis of
  the lower-case spelling.  Noted while fooling with a patch to
  de-reserve more plpgsql keywords.

Fujii Masao pushed:

- Add --synchronous option to pg_receivexlog, for more reliable WAL
  writing.  Previously pg_receivexlog flushed WAL data only when WAL
  file was switched.  Then 3dad73e added -F option to pg_receivexlog
  so that users could control how frequently sync commands were issued
  to WAL files. It also allowed users to make pg_receivexlog flush WAL
  data immediately after writing by specifying 0 in -F option. However
  feedback messages were not sent back immediately even after a flush
  location was updated. So even if WAL data was flushed in real time,
  the server could not see that for a while.  This commit removes -F
  option from and adds --synchronous to pg_receivexlog.  If
  --synchronous is specified, like the standby's wal receiver,
  pg_receivexlog flushes WAL data as soon as there is WAL data which
  has not been flushed yet.  Then it sends back the feedback message
  identifying the latest flush location to the server. This option is
  useful to make pg_receivexlog behave as sync standby by using
  replication slot, for example.  Original patch by Furuya Osamu,
  heavily rewritten by me.  Reviewed by Heikki Linnakangas, Alvaro
  Herrera and Sawada Masahiko.

- Fix pg_receivexlog --slot so that it doesn't prevent the server
  shutdown.  When pg_receivexlog --slot is connecting to the server,
  at the shutdown of the server, walsender keeps waiting for the last
  WAL record to be replicated and flushed in pg_receivexlog. But
  previously pg_receivexlog issued sync command only when WAL file was
  switched. So there was the case where the last WAL was never flushed
  and walsender had to keep waiting infinitely. This caused the server
  shutdown to get stuck.  pg_recvlogical handles this problem by
  calling fsync() when it receives the request of immediate reply from
  the server. That is, at shutdown, walsender sends the request,
  pg_recvlogical receives it, flushes the last WAL record, and sends
  the flush location back to the server. Since walsender can see that
  the last WAL record is successfully flushed, it can exit cleanly.
  This commit introduces the same logic as pg_recvlogical has, to
  pg_receivexlog.  Back-patch to 9.4 where pg_receivexlog was changed
  so that it can use the replication slot.  Original patch by Michael
  Paquier, rewritten by me.  Bug report by Furuya Osamu.

- Fix bug in the test of file descriptor of current WAL file in
  pg_receivexlog.  In pg_receivexlog, in order to check whether the
  current WAL file is being opened or not, its file descriptor has to
  be checked against -1 as an invalid value. But, oops, 7900e94 added
  the incorrect test checking the descriptor against 1. This commit
  fixes that bug.  Back-patch to 9.4 where the bug was added.  Spotted
  by Magnus Hagander

Simon Riggs pushed:

- Add pg_dump --snapshot option.  Allows pg_dump to use a snapshot
  previously defined by a concurrent session that has either used
  pg_export_snapshot() or obtained a snapshot when creating a logical
  slot. When this option is used with parallel pg_dump, the snapshot
  defined by this option is used and no new snapshot is taken.  Simon
  Riggs and Michael Paquier

- Reduce btree scan overhead for < and > strategies.  For <, <=, > and >=
  strategies, mark the first scan key as already matched if scanning
  in an appropriate direction.  If index tuple contains no nulls we
  can skip the first Author: Rajeev Rastogi.  Reviewer: Haribabu
  Kommi.  Rework of the code and comments by Simon Riggs

Robert Haas pushed:

- Avoid file descriptor leak in pg_test_fsync.  This can cause
  problems on Windows, where files that are still open can't be
  unlinked.  Jeff Janes

Peter Eisentraut pushed:

- Fix suggested layout for PGXS makefile.  Custom rules must come
  after pgxs inclusion, not before, because any rule added before pgxs
  will break the default 'all' target.  Author: Cédric Villemain
  <cedric <at> 2ndquadrant.fr>

Andrew Dunstan pushed:

- Make psql's \sf and \ef honor ECHO_HIDDEN.  These commands were
  calling the database direct rather than  calling PSQLexec like other
  slash commands that needed database data.  The code is also changed
  not to pass the connection as a parameter to the helper functions.
  It's available in a global variable, and that's what PSQLexec uses.

- Fix an error in psql that overcounted output lines.  This error
  counted the first line of a cell as "extra". The effect was to cause
  far too frequent invocation of the pager. In most cases this can be
  worked around (for example, by using the "less" pager with the -F
  flag), so don't backpatch.

- Rework echo_hidden for \sf and \ef from commit e4d2817.  PSQLexec's
  error reporting turns out to be too verbose for this case, so revert
  to using PQexec instead with minimal error reporting. Prior to
  calling PQexec, we call a function that mimics just the echo_hidden
  piece of PSQLexec.

- Fix memory leaks introduced by commit eca2b9b

Noah Misch pushed:

- Detect PG_PRINTF_ATTRIBUTE automatically.  This eliminates gobs of
  "unrecognized format function type" warnings under MinGW compilers
  predating GCC 4.4.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Etsuro Fujita sent in another revision of a patch to allow foreign
tables to participate in table inheritance hierarchies.

Jeff Davis and Tomas Vondra traded patches to do better memory
accounting, in part as infrastructure for a memory-bounded HashAgg.

Michael Paquier sent in a patch to fix CLOBBER_CACHE_ALWAYS.

Michael Paquier sent in another revision of a patch to track number of
files marked as ready for archiving in pg_stat_archiver.

SAWADA Masahiko sent in another revision of a patch to implement
REINDEX SCHEMA, in this case as an option to reindexdb.

Heikki Linnakangas sent in two PoC patches to deal with a performance
problem in PageRepairFragmentation.

Petr (PJMODOS) Jelinek and Simon Riggs traded patches to add a
shutdown_at_recovery_target option to recovery.conf.

Dimitri Fontaine sent in three more revisions of a patch to add "table
rewrite" as an event which can fire an event trigger.

Adam Brightwell sent in a patch to add int64-to-int8 mapping in

Magnus Hagander sent in another revision of a patch to add a
pg_stat_ssl view.

Aaron W. Swenson sent in a patch to replace configure.in with
configure dot ac.

Heikki Linnakangas sent in a patch to add some more tests for WALs.

Peter Geoghegan sent in two revisions of a patch to create an amcheck
utility, a generalization of his previous btreecheck.

Michael Paquier sent in a patch to uppercase some SQL keywords in the
BRIN documents.

Amit Kapila sent in another revision of a patch to fix some
infelicities between pg_basebackup and Windows.

Peter Eisentraut sent in a series of patches which: Fix SHLIB_PREREQS
use in contrib, allowing PGXS builds, allow the user to set VPATH
directly, and clean up some makefile changes that weren't needed.

Abhijit Menon-Sen sent in a patch to use slice-by-8 for CRC, which
increased the performance of that operation by a factor of 4+.

Adam Brightwell sent in another revision of a patch to fix Catalog.pm
for role attributes.

Laurenz Albe sent in a patch to disallow changing functions which are
part of an index definition.

Michael Paquier sent in a patch to enable GIN code managing entry
insertion not to distinguish fresh from old indexes.

Robert Haas sent in another revision of a patch to add infrastructure
to save and restore GUC values.

Michael Paquier sent in a patch to add fillfactor as storage parameter
for GIN indexes.

Petr (PJMODOS) Jelinek sent in two more revisions of a patch to track
commit timestamps.

Adam Brightwell sent in another revision of a patch to clean up
superuser shortcuts.

Alexander Shulgin sent in another revision of a patch to turn
recovery.conf into GUCs.

Álvaro Herrera added some opfamilies for BRIN indexes which should
help make them more useful.

Marko (johto) Tiikkaja sent in two revisions of a patch to make the
HINTs more useful for certain types common mistakes in UPDATE.

Amit Kapila sent in another revision of a patch to parallelize

David Rowley sent in another revision of a patch to support SEMI and
ANTI join removal in certain cases.

Tom Lane sent in a patch to remove 17 of PL/pgsql's reserved words.


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

Dave Page | 20 Nov 19:24 2014

PostgreSQL 9.4 RC1 Released

The PostgreSQL Global Development Group has released 9.4 RC 1, the
first release candiate for the next version of PostgreSQL.  This
release should be identical to the final version of PostgreSQL 9.4,
excepting any fixes for bugs found in the next two weeks. Please
download, test, and report what you find.

For a full listing of the features in version 9.4, please see the
release notes (http://www.postgresql.org/docs/devel/static/release-9-4.html).
Additional descriptions and notes on the new features are available on
the 9.4 Features Wiki Page

We depend on our community to help test the next version in order to
guarantee that it is high-performance and bug-free.  Please download
PostgreSQL 9.4 RC 1 and try it with your workloads and applications as
soon as you can, and give feedback to the PostgreSQL developers.
Features and APIs in this release candidate should be identical to
9.4.0, allowing you to build and test your applications against it.
More information on how to test and report issues

Get the PostgreSQL 9.4 RC 1, including binaries and installers for
Windows, Linux and Mac from our download page

Full documentation of the new version is available online
(http://www.postgresql.org/docs/devel/static), and also installs with

Dave Page
PostgreSQL Core Team


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

Gilles Darold | 19 Nov 10:24 2014

Ora2Pg 14.1 released

Ora2Pg 14 released

Dalibo is proud to announce that version 14.1 of Ora2Pg, a free and reliable
tool used to migrate an Oracle database to PostgreSQL, has been officially
released and is publicly available for download.

This major release adds full export of Oracle Locator or Spatial
geometries into
PostGis, SDO_GEOM functions and SDO_OPERATOR are also translated. This
adds the following features:

  1. Basic and complex geometry types support
  2. Geometry data conversion from Oracle to PostGIS
  3. Spatial Index conversion
  4. Geometry metadata / constraints support
  5. Spatial functions conversion

For spatial data export, you have three choice, WKT to export data using
SDO_UTIL.TO_WKTGEOMETRY(), WKB to export data using
and INTERNAL to export geometry using a Pure Perl library. Unlike the first
two methods, INTERNAL is fast and do not raise Out Of Memory. The export is
done in WKT format so that you can verify your geometry before importing to

Other additional major features are:

  - Parallel table processing.
  - Auto generation of migration template with a complete project tree.
  - Allow user defined queries to extract data from Oracle.

Parallel table processing is controlled by the -P or --parallel command line
options or the PARALLEL_TABLE configuration directive to set the number of
tables that will be processed in parallel for data extraction. The limit is
the number of cores on your machine. Ora2Pg will the open one connection to
Oracle database for each parallel table extraction. This directive, when
than 1, will invalidate ORACLE_COPIES but not JOBS, so the real number of
process that will be used is (PARALLEL_TABLES * JOBS).

The two options --project_base and --init_project when used indicate to
to create a project template with a work tree, a generic configuration file
and a shell script to export all objects from the Oracle database. So
that you
just have to define the Oracle database connection into the
configuration file
and then execute the shell script called export_schema.sh to export your
Oracle database into files. Here a sample of the command and the
project's tree.

    ora2pg --project_base /tmp --init_project test_project

        fdws/  functions/  grants/  kettles/
        mviews/  packages/  partitions/
        procedures/  sequences/  tables/
        tablespaces/  triggers/  types/  views/
        functions/  mviews/  packages/
        partitions/  procedures/  triggers/
        types/  views/

It create a generic config file where you just have to define the Oracle
database connection and a shell script called export_schema.sh. The
sources/ directory will contains the Oracle code, the schema/ will
contains the code ported to PostgreSQL. The reports/ directory will
contains the html reports with the migration cost assessment.

Sometime you may want to extract data from an Oracle table but you need a
custom query for that. Not just a "SELECT * FROM table" like Ora2Pg do but
a more complex query. The new directive REPLACE_QUERY allow you to overwrite
the query used by Ora2Pg to extract data. The format is
If you have multiple table to extract by replacing the Ora2Pg query, you can
define multiple REPLACE_QUERY lines. For example:

    REPLACE_QUERY   EMPLOYEES[SELECT e.id,e.fisrtname,lastname FROM
          JOIN EMP_UPDT u ON (e.id=u.id AND u.cdate>'2014-08-01 00:00:00')]

Other new features are:

    - Export of declaration of language C function. Previous version was
      not exporting function with no code body like external C function.
    - Export of COMMENT from views.
    - Function to replace some call to SYS_CONTECT(USERENV, ...) by the
      PostgreSQL equivalent.
    - Add POSTGIS_SCHEMA configuration directive to add the dedicated
      PostGis schema into the search_path.
    - Add PG_SUPPORTS_IFEXISTS configuration directive to be able to
      IF EXISTS call in DDL statement generated by Ora2Pg.
    - Triggers are now all excluded/allowed following the table names
      in the ALLOW and EXCLUDED directives
    - Allow automatic export of nested tables (TYPE+TABLE+COPY).

As usual these also some bug fixes and Oracle to PostgreSQl conversion
adjustments, see https://github.com/darold/ora2pg/blob/master/changelog
for a complete list.

Thanks to all contributors, they are all cited in the changelog file.
With very
specials thanks to Dominique Legendre and the French Geological Survey -
(http://www.brgm.eu/) for their great help.

Useful Links:

    - Website: http://ora2pg.darold.net/
    - Download: http://sourceforge.net/projects/ora2pg/
    - Development: https://github.com/darold/ora2pg
    - Changelog: https://github.com/darold/ora2pg/blob/master/changelog
    - Documentation: https://github.com/darold/ora2pg/blob/master/README

About Ora2Pg :

Ora2Pg is an easy and reliable tool to migrate from Oracle to PostgreSQL.
It is developed since 2001 and can export most of the Oracle objects
(table, view, materialized view, tablespace, sequence, indexes, trigger,
grant, function, procedure, package, partition, data, blob and external

Ora2Pg works on any platform and is available under the GPL v3 licence.

Docs, Download & Support at http://ora2pg.darold.net/

Gilles Darold
Consultant PostgreSQL
http://dalibo.com - http://dalibo.org


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