Dave Page | 2 Jul 17:16 2015

PostgreSQL 9.5 Alpha 1 Released

The PostgreSQL Global Development Group announces that the alpha
release of PostgreSQL 9.5, the latest version of the world's leading
open source database, is available today.  This release contains
previews of all of the features which will be available in the final
release of version 9.5, although some details will change before then.
Please download, test, and report what you find.

Major Features
--------------

Among new major features available for testing in this alpha are:

* Block-Range Indexes (BRIN) which enable compact indexing of
  very large tables
* Large speed improvements in in-memory sorting and hashing
* Automated management of transaction log size
* INSERT ON CONFLICT UPDATE, otherwise known as "UPSERT"
* Grouping Sets, CUBE and ROLLUP analytic operations
* Row-Level Security (RLS) support
* More JSONB data manipulation functions and operators
* Added the pg_rewind tool and other high availability
  improvements to replication
* Multiple enhancements to Foreign Data Wrappers, including
  IMPORT FOREIGN SCHEMA
* Large increases in multi-core and large memory scalability

These features expand the capabilities of PostgreSQL, and introduce
new syntax, APIs, and management interfaces.  Of course, there are
many more features detailed in the release notes, some of which may be
more exciting to you than the above.  Please test every one you can.
(Continue reading)

Magnus Hagander | 30 Jun 16:43 2015
Picon

Reminder: Call for Papers - PostgreSQL Conference Europe 2015

PostgreSQL Conference Europe 2015 will be held on October 27-30 in the Vienna Marriott Hotel, in Vienna, Austria. It will cover topics for PostgreSQL users, developers and contributors, as well as decision and policy makers. For more information about the conference, please see the website at http://2015.pgconf.eu/.

We are now accepting proposals for talks, in English or German. If you are submitting a talk in german, please write a short summary of the abstract in English in the "submission notes" field.

Each session will last 45 minutes, and may be on any topic related to PostgreSQL. Suggested topic areas include:

  • Developing applications for PostgreSQL
  • Administering large scale PostgreSQL installations
  • Case studies and/or success stories of PostgreSQL deployments
  • PostgreSQL tools and utilities
  • PostgreSQL hacking
  • Community & user groups
  • Tuning the server
  • Migrating from other systems
  • Scaling/replication
  • Benchmarking & hardware
  • PostgreSQL related products

Of course, we're happy to receive proposals for talks on other PostgreSQL related topics as well.

We may also have a limited number of longer, 90-minute, slots available. Please indicate clearly in your submission if you wish to make a 90-minute talk.

Finally, there will be a session of five minute lightning talks. A separate call for proposals will be made for them further on.

The submission deadline is August 7th. Selected speakers will be notified before August 24th, 2015.

Please submit your proposals by going to http://2015.pgconf.eu/callforpapers/ and following the instructions there.

The proposals will be considered by committee who will produce a schedule to be published nearer the conference date.

All selected speakers will get free entry to the conference (excluding training sessions). We do not in general cover travel and accommodations for speakers, but may be able to do that in limited cases. If you require assistance with funding to be able to attend, please make a note of this in the submission notes field.

This call for papers is also available on the web at http://2015.pgconf.eu/callforpapers/

We look forward to hearing from you, and seeing you in Vienna in October!


Sasha | 30 Jun 11:21 2015
Picon

SQL Tabs - yet another client tool for Postgresql


I’m pleased to announce a brand-new client tool for Postgresql “SQL Tabs” http://www.sqltabs.com

It’s still in early beta version (v0.3) but already has quite a lot of useful features:
- charts from queries result-sets
- ad-hoc connection switching
- entirely asynchronous, so never blocks you from editing your code


At the moment only builds for Mac and Linux (x64) are available.

If you want to send a feedback, request a missing feature or fill a bug report just create an issue in the following github repository

Best Regards
Aliaksandr Aliashkevich
David Fetter | 28 Jun 23:53 2015

== PostgreSQL Weekly News - June 28 2015 ==

== PostgreSQL Weekly News - June 28 2015 ==

PGConf.DE will be held November 26-27 in Hamburg, Germany, at the
Lindner Hotel am Michel.  The CfP is open until September 13, 2015.
http://2015.pgconf.de/

== PostgreSQL Product News ==

HypoPG, hypothetical indexes for PostgreSQL, released.
https://github.com/dalibo/hypopg

par_psql v0.1: 'Parallel psql', which adds special markings for
queries to be run in parallel, released.
http://github.com/gbb/par_psql/

== PostgreSQL Jobs for June ==

http://archives.postgresql.org/pgsql-jobs/2015-06/threads.php

== PostgreSQL Local ==

PGDay UK, Conference will be taking place on 7th July 2015 – it is aimed at
the UK PostgreSQL Community.  The CfP is open until 13 April 2015.
http://www.postgresqlusergroup.org.uk

PGDay Campinas 2015 will take place in Campinas on August 7.
http://pgdaycampinas.com.br/english/

The Call For Papers for PostgresOpen 2015, being held in Dallas, Texas
from September 16th to 18th, is now open.
http://2015.postgresopen.org/callforpapers/

PostgreSQL Session #7, will be held September 24th, 2015 in Paris,
France,
http://www.postgresql-sessions.org/7/about

PostgreSQL Conference Europe 2015 will be held on October 27-30 in the
Vienna Marriott Hotel, in Vienna, Austria.  The CfP is open until
August 7.
http://2015.pgconf.eu/

PGConf Silicon Valley 2015 is November 17-18 at the South San
Francisco Convention Center.  Discount registration is available
through June 30.
http://www.pgconfsv.com

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 through July 15.
http://pgbr.postgresql.org.br/2015/en/#call-for-papers

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

- Truncate strings in tarCreateHeader() with strlcpy(), not sprintf().
  This supplements the GNU libc bug #6530 workarounds introduced in
  commit 54cd4f04576833abc394e131288bf3dd7dcf4806.  On affected
  systems, a tar-format pg_basebackup failed when some filename
  beneath the data directory was not valid character data in the
  postmaster/walsender locale.  Back-patch to 9.1, where pg_basebackup
  was introduced.  Extant, bug-prone conversion specifications receive
  only ASCII bytes or involve low-importance messages.
  http://git.postgresql.org/pg/commitdiff/4318118edd5582696027f357771e0a8b091fe2bf

Robert Haas pushed:

- psql: Add some tab completion for TABLESAMPLE.  Petr Jelinek,
  reviewed by Brendan Jurd
  http://git.postgresql.org/pg/commitdiff/da9ee026a0ddd100785b00defd1201b317c0797b

- Update get_relation_info comment.  Thomas Munro
  http://git.postgresql.org/pg/commitdiff/51d0fe5d5682a65e3bce7aa62d8666509fd08aa2

- Allow background workers to connect to no particular database.  The
  documentation claims that this is supported, but it didn't actually
  work.  Fix that.  Reported by Pavel Stehule; patch by me.
  http://git.postgresql.org/pg/commitdiff/f7bb7f0625771bc71869cdadafcf54450b2db08f

- Improve handling of CustomPath/CustomPlan(State) children.  Allow
  CustomPath to have a list of paths, CustomPlan a list of plans, and
  CustomPlanState a list of planstates known to the core system, so
  that custom path/plan providers can more reasonably use this
  infrastructure for nodes with multiple children.  KaiGai Kohei, per
  a design suggestion from Tom Lane, with some further kibitzing by
  me.
  http://git.postgresql.org/pg/commitdiff/5ca611841bcd37c7ee8448c46c8398ef8d8edcc4

- Reduce log level for background worker events from LOG to DEBUG1.
  Per discussion, LOG is just too chatty for something that will
  happen as routinely as this.  Pavel Stehule
  http://git.postgresql.org/pg/commitdiff/91118f1a59f2038f072552fdbb98e01363e30b59

- Don't warn about creating temporary or unlogged hash indexes.
  Warning people that no WAL-logging will be done doesn't make sense
  in this case.  Michael Paquier
  http://git.postgresql.org/pg/commitdiff/9043ef390f4f0b4586cfe59cbd22314b9c3e2957

- release notes: Combine items for pg_upgrade and pg_upgrade_support
  moves.  Per suggestions from Amit Langote and Álvaro Herrera.
  http://git.postgresql.org/pg/commitdiff/31c018ecda9f40fe80055d8ba95248c023593fb4

- Remove unnecessary NULL test.  Spotted by Coverity and reported by
  Michael Paquier.  Per discussion, we don't necessarily care about
  making Coverity happy in all such instances, but we can go ahead and
  change them where it otherwise seems to improve the code.
  http://git.postgresql.org/pg/commitdiff/8a8c581a8c99b9beecbdc517957da866f427f297

- release notes: Add entry for commit 5ea86e6e6.  Peter Geoghegan and
  Robert Haas
  http://git.postgresql.org/pg/commitdiff/c66bc72e8a1318e43ea657ffa3798fa95f491650

- Be more conservative about removing tablespace "symlinks".  Don't
  apply rmtree(), which will gleefully remove an entire subtree, and
  don't even apply unlink() unless it's symlink or a directory, the
  only things that we expect to find.  Amit Kapila, with minor tweaks
  by me, per extensive discussions involving Andrew Dunstan, Fujii
  Masao, and Heikki Linnakangas, at least some of whom also reviewed
  the code.
  http://git.postgresql.org/pg/commitdiff/8f15f74a44f68f9cb3a644786d3c732a5eeb237a

- Fix grammar.  Reported by Peter Geoghegan.
  http://git.postgresql.org/pg/commitdiff/7c02d48e698ad38bec1399a9dcc543c80b8f5b8f

Tom Lane pushed:

- Improve inheritance_planner()'s performance for large inheritance
  sets.  Commit c03ad5602f529787968fa3201b35c119bbc6d782 introduced a
  planner performance regression for UPDATE/DELETE on large
  inheritance sets.  It required copying the append_rel_list (which is
  of size proportional to the number of inherited tables) once for
  each inherited table, thus resulting in O(N^2) time and memory
  consumption.  While it's difficult to avoid that in general, the
  extra work only has to be done for append_rel_list entries that
  actually reference subquery RTEs, which inheritance-set entries will
  not.  So we can buy back essentially all of the loss in cases
  without subqueries in FROM; and even for those, the added work is
  mainly proportional to the number of UNION ALL subqueries.
  Back-patch to 9.2, like the previous commit.  Tom Lane and Dean
  Rasheed, per a complaint from Thomas Munro.
  http://git.postgresql.org/pg/commitdiff/2cb9ec1bcb35dd6b4cf7a4a325aaa9791444e69d

- Docs: fix claim that to_char('FM') removes trailing zeroes.  Of
  course, what it removes is leading zeroes.  Seems to have been a
  thinko in commit ffe92d15d53625d5ae0c23f4e1984ed43614a33d.  Noted by
  Hubert Depesz Lubaczewski.
  http://git.postgresql.org/pg/commitdiff/d759b7eb6aee12bd52516905d790072845b4356f

- Fix the logic for putting relations into the relcache init file.
  Commit f3b5565dd4e59576be4c772da364704863e6a835 was a couple of
  bricks shy of a load; specifically, it missed putting
  pg_trigger_tgrelid_tgname_index into the relcache init file, because
  that index is not used by any syscache.  However, we have
  historically nailed that index into cache for performance reasons.
  The upshot was that load_relcache_init_file always decided that the
  init file was busted and silently ignored it, resulting in a
  significant hit to backend startup speed.  To fix, reinstantiate
  RelationIdIsInInitFile() as a wrapper around
  RelationSupportsSysCache(), which can know about additional
  relations that should be in the init file despite being unknown to
  syscache.c.  Also install some guards against future mistakes of
  this type: make write_relcache_init_file Assert that all nailed
  relations get written to the init file, and make
  load_relcache_init_file emit a WARNING if it takes the "wrong number
  of nailed relations" exit path.  Now that we remove the init files
  during postmaster startup, that case should never occur in the
  field, even if we are starting a minor-version update that added or
  removed rels from the nailed set.  So the warning shouldn't ever be
  seen by end users, but it will show up in the regression tests if
  somebody breaks this logic.  Back-patch to all supported branches,
  like the previous commit.
  http://git.postgresql.org/pg/commitdiff/5d1ff6bd559ea8df1b7302e245e690b01b9a4fa4

- Avoid passing NULL to memcmp() in lookups of zero-argument
  functions.  A few places assumed they could pass NULL for the
  argtypes array when looking up functions known to have zero
  arguments.  At first glance it seems that this should be safe
  enough, since memcmp() is surely not allowed to fetch any bytes if
  its count argument is zero.  However, close reading of the C
  standard says that such calls have undefined behavior, so we'd
  probably best avoid it.  Since the number of places doing this is
  quite small, and some other places looking up zero-argument
  functions were already passing dummy arrays, let's standardize on
  the latter solution rather than hacking the function lookup code to
  avoid calling memcmp() in these cases.  I also added Asserts to
  catch any future violations of the new rule.  Given the utter lack
  of any evidence that this actually causes any problems in the field,
  I don't feel a need to back-patch this change.  Per report from
  Piotr Stefaniak, though this is not his patch.
  http://git.postgresql.org/pg/commitdiff/0a52d378b03b7d5ab1d64627a87edaf5ed311c6c

Peter Eisentraut pushed:

- pg_rewind: Improve message wording
  http://git.postgresql.org/pg/commitdiff/e98d635d5dbf25e5cde282af111af9fdffafa557

- pg_basebackup: Remove redundant newline in error message
  http://git.postgresql.org/pg/commitdiff/747781f25e7eaa2e5cb5ed69bdae3e5f61795d2e

Heikki Linnakangas pushed:

- Add missing newline to debug-message.  Michael Paquier
  http://git.postgresql.org/pg/commitdiff/9cb36981fbbf2f298db2476101f4475c52d00fbb

- Fix a couple of bugs with wal_log_hints.  1. Replay of the WAL
  record for setting a bit in the visibility map contained an
  assertion that a full-page image of that record type can only occur
  with checksums enabled. But it can also happen with wal_log_hints,
  so remove the assertion. Unlike checksums, wal_log_hints can be
  changed on the fly, so it would be complicated to figure out if it
  was enabled at the time that the WAL record was generated.  2.
  wal_log_hints has the same effect on the locking needed to read the
  LSN of a page as data checksums. BufferGetLSNAtomic() didn't get the
  memo.  Backpatch to 9.4, where wal_log_hints was added.
  http://git.postgresql.org/pg/commitdiff/4b8e24b9ad308c30dbe2184e06848e638e018114

- Fix typo in comment.  Etsuro Fujita
  http://git.postgresql.org/pg/commitdiff/7845db2aa778aa751b41cff72c41c94993e975e3

- Add missing_ok option to the SQL functions for reading files.  This
  makes it possible to use the functions without getting errors, if
  there is a chance that the file might be removed or renamed
  concurrently.  pg_rewind needs to do just that, although this could
  be useful for other purposes too. (The changes to pg_rewind to use
  these functions will come in a separate commit.) The
  read_binary_file() function isn't very well-suited for
  extensions.c's purposes anymore, if it ever was. So bite the bullet
  and make a copy of it in extension.c, tailored for that use case.
  This seems better than the accidental code reuse, even if it's a
  some more lines of code.  Michael Paquier, with plenty of kibitzing
  by me.
  http://git.postgresql.org/pg/commitdiff/cb2acb1081e13b4b27a76c6b5311115528e49c59

- Don't choke on files that are removed while pg_rewind runs.  If a
  file is removed from the source server, while pg_rewind is running,
  the invocation of pg_read_binary_file() will fail. Use the
  just-added missing_ok option to that function, to have it return
  NULL instead, and handle that gracefully. And similarly for
  pg_ls_dir and pg_stat_file.  Reported by Fujii Masao, fix by Michael
  Paquier.
  http://git.postgresql.org/pg/commitdiff/b36805f3c54fe0e50e58bb9e6dad66daca46fbf6

- Fix double-XLogBeginInsert call in GIN page splits.  If data
  checksums or wal_log_hints is on, and a GIN page is split, the code
  to find a new, empty, block was called after having already called
  XLogBeginInsert(). That causes an assertion failure or PANIC, if
  finding the new block involves updating a FSM page that had not been
  modified since last checkpoint, because that update is WAL-logged,
  which calls XLogBeginInsert again. Nested XLogBeginInsert calls are
  not supported.  To fix, rearrange GIN code so that XLogBeginInsert
  is called later, after finding the victim buffers.  Reported by Jeff
  Janes.
  http://git.postgresql.org/pg/commitdiff/a45c70acf35e43257d86313dcbb7bb0e5201fab1

- Promote the assertion that XLogBeginInsert() is not called twice
  into ERROR.  Seems like cheap insurance for WAL bugs. A spurious
  call to XLogBeginInsert() in itself would be fairly harmless, but if
  there is any data registered and the insertion is not
  completed/cancelled properly, there is a risk that the data ends up
  in a wrong WAL record.  Per Jeff Janes's suggestion.
  http://git.postgresql.org/pg/commitdiff/a32c3ec893cafbd3a4b42c34270a80198f28f123

- Fix markup in docs.  Oops. I could swear I built the docs before
  pushing, but I guess not..
  http://git.postgresql.org/pg/commitdiff/6ab4d38ab085b0177d7ce63f7e1f2fb3f3a8e4a5

Fujii Masao pushed:

- Add index terms for functions jsonb_set and jsonb_pretty.
  http://git.postgresql.org/pg/commitdiff/0b157a0dad4f88f6f4420faa4cddab1e5112988f

Andres Freund pushed:

- Fix the fallback memory barrier implementation to be reentrant.
  This was essentially "broken" since 0c8eda62; but until more
  recently (14e8803f) barriers usage in signal handlers was
  infrequent.  The failure to be reentrant was noticed because the
  test_shm_mq, which uses memory barriers at a high frequency,
  occasionally got stuck on some solaris buildfarm animals. Turns out,
  those machines use sun studio 12.1, which doesn't yet have efficient
  memory barrier support. A machine with a newer sun studio did not
  fail.  Forcing the barrier fallback to be used on x86 allows to
  reproduce the problem.  The new fallback is to use
  kill(PostmasterPid, 0) based on the theory that that'll always imply
  a barrier due to checking the liveliness of PostmasterPid on systems
  old enough to need fallback support. It's hard to come up with a
  good and performant fallback.  I'm not backpatching this for now -
  the problem isn't active in the back branches, and we haven't
  backpatched barrier changes for now. Additionally master looks
  entirely different than the back branches due to the new atomics
  abstraction. It seems better to let this rest in master, where the
  non-reentrancy actively causes a problem, and then consider
  backpatching.  Found-By: Robert Haas Discussion:
  55626265.3060800 <at> dunslane.net
  http://git.postgresql.org/pg/commitdiff/1b468a131bd260c9041484f78b8580c7f232d580

- Fix test_decoding's handling of nonexistant columns in old tuple
  versions.  test_decoding used fastgetattr() to extract column
  values. That's wrong when decoding updates and deletes if a table's
  replica identity is set to FULL and new columns have been added
  since the old version of the tuple was created. Due to the lack of a
  crosscheck with the datum's natts values an invalid value will be
  output, leading to errors or worse.  Bug: #13470 Reported-By:
  Krzysztof Kotlarski Discussion:
  20150626100333.3874.90852 <at> wrigleys.postgresql.org Backpatch to 9.4,
  where the feature, including the bug, was added.
  http://git.postgresql.org/pg/commitdiff/d47a1136e441cebe7ae7fe72d70eb8ce278d5cd6

Álvaro Herrera pushed:

- Fix BRIN xlog replay.  There was a confusion about which block
  number to use when storing an item's pointer in the revmap -- the
  revmap page's blkno was being used, not the data page's blkno.
  Spotted-by: Jeff Janes
  http://git.postgresql.org/pg/commitdiff/402822246866e1094d35a617775a65b4be93d322

- Fix DDL command collection for TRANSFORM.  Commit b488c580ae, which
  added the DDL command collection feature, neglected to update the
  code that commit cac76582053e had previously added two weeks earlier
  for the TRANSFORM feature.  Reported by Michael Paquier.
  http://git.postgresql.org/pg/commitdiff/7d60b2af34842ae89b1abdd31fb5d303bd43c514

Simon Riggs pushed:

- Avoid hot standby cancels from VAC FREEZE VACUUM FREEZE generated
  false cancelations of standby queries on an otherwise idle master.
  Caused by an off-by-one error on cutoff_xid which goes back to
  original commit.  Backpatch to all versions 9.0+ Analysis and report
  by Marco Nenciarini Bug fix by Simon Riggs
  http://git.postgresql.org/pg/commitdiff/66fbcb0d2e1b201477dd2977b6eb93b1cfd9dd6c

Kevin Grittner pushed:

- Add opaque declaration of HTAB to tqual.h.  Commit
  b89e151054a05f0f6d356ca52e3b725dd0505e53 added the
  ResolveCminCmaxDuringDecoding declaration to tqual.h, which uses an
  HTAB parameter, without declaring HTAB.  It accidentally fails to
  fail to build with current sources because a declaration happens to
  be included, directly or indirectly, in all source files that
  currently use tqual.h before tqual.h is first included, but we
  shouldn't count on that.  Since an opaque declaration is enough
  here, just use that, as was done in snapmgr.h.  Backpatch to 9.4,
  where the HTAB reference was added to tqual.h.
  http://git.postgresql.org/pg/commitdiff/604e99396de02f6f23950ee373c13335d2ccdf05

- Fix comment for GetCurrentIntegerTimestamp().  The unit of measure
  is microseconds, not milliseconds.  Backpatch to 9.3 where the
  function and its comment were added.
  http://git.postgresql.org/pg/commitdiff/cca8ba9529f8815acd23fe88c32763765d0e1b68

Tatsuo Ishii pushed:

- Fix function declaration style to respect the coding standard.
  http://git.postgresql.org/pg/commitdiff/527e6d3f099df22783465ca7046fc0c8a534c921

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Peter Geoghegan sent in a patch to allow JSON[B] arrays to take
negative subscripts.

Tomas Vondra sent in a patch to add density correction to sampling for
statistics collection.

Michael Paquier sent in a patch to fix some white space in pg_rewind
error messages.

Michael Paquier and Robert Haas traded patches to fix an issue where a
dangerous rm -rf was being issued in the global makefile.

Michael Paquier sent in two more revisions of a patch to update the
hash index creation warning.

Abhijit Menon-Sen sent in a patch to introduce
XLogLockBlockRangeForCleanup().

Fabien COELHO sent in another revision of a patch to add checkpointer
continuous flushing.

Michael Paquier sent in another revision of a patch to add support for
TAP tests on Windows.

Michael Paquier sent in a patch to improve log capture of TAP tests
and fix race conditions.

Fabrízio de Royes Mello sent in two more revisions of a patch to add
CINE for ALTER TABLE ... ADD COLUMN.

Uriy Zhuravlev sent in another revision of a patch to implement ALTER
OPERATOR.

Craig Ringer sent in a patch to implement ALTER TABLE ... ALTER
CONSTRAINT ... SET DEFERRABLE on UNIQUE or PK.

Oskari Saarenmaa sent in a patch to add -lrt to configure for
sched_yield on Solaris.

Jim Nasby sent in a patch to ensure that object_classes is properly
sized.

Marco Nenciarini sent in a patch to fix an off-by-one bug which caused
VACUUM FREEZE to mistakenly cancel standby sessions.

Peter Geoghegan sent in a patch to add some compatibility notes for
UPSERT with foreign data wrappers.

Amit Kapila sent in a patch to improve the performance of DROP TABLE
when the shared_buffers setting is high.

Jeff Janes sent in a patch to make pg_trgm perform better by
supporting the triconsistent function, introduced in version 9.4 of
the server, to make it faster to implement indexed queries where some
keys are common and some are rare.

Tom Lane sent in a patch to refactor the way the pg_file_settings view
works.

--

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

Graeme B. Bell | 26 Jun 04:09 2015
Picon

'Parallel psql’, for queries and workflows in PostgreSQL/PostGIS.

par_psql v0.1: 'Parallel psql’, for queries and workflows in PostgreSQL/PostGIS.
================================================

Hi everyone

http://github.com/gbb/par_psql/

I’ve written a tool (par_psql) which makes parallelisation easier for PostgreSQL/PostGIS users, by
providing a new piece of syntax.

With —-& inline, it runs queries or groups of queries in parallel.
Without —-&, it synchronises parallel work then runs subsequent code normally.
This allows easy control of parallelism and synchronisation inline within your SQL script.

The tool is backwards compatible with existing psql scripts, and par_psql scripts are backwards
compatible with psql. It should work with any version of PostgreSQL. The only dependencies are bash and psql.

Benchmark and example code is provided at http://github.com/gbb/par_psql.

Quick example
=============

create table a as ...
create table a1 as ...  —-&
create table a2 as ...  —-&
create table c ...

Some cool uses
==============

1. GIS and any other discipline where you prepare diverse source datasets in a multi-stage workflow before
integrating them together.
2. Where you have CPU-intensive queries, split the work via one field (e.g. ID) and create parallel temp
tables. UNION the results.
3. Add “Preview runs”, that complete progressively using subsets of the data without delaying the
main task.
4. Create scripts where several tasks run at fixed times after the script begins (use pg_sleep() and run
them in parallel).

It’s available under the postgresql open source license. It's a 'quick hack' and version 0.1, so please
be kind with any criticism/bug reports. That said, it works well for me. Enjoy! :-)

Graeme Bell

http://github.com/gbb/par_psql/

ps. I am grateful to the Norwegian Forest and Landscape Institute (soon to be integrated into the Norwegian
NIBIO Institute) for supporting and open sourcing this and other scripts as a contribution to this
year’s FOSS4G Europe Open Source Mapping conference.

--

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

Julien Rouhaud | 24 Jun 18:56 2015

Introducing HypoPG, hypothetical indexes for PostgreSQL

# Introducing HypoPG

Paris, June 24 2015

DALIBO is proud to present the first release of HypoPG, an extension
that adds hypothetical indexes in PostgreSQL.

An hypothetical index is an index which doesn't exists on disk. It's
thefore almost instant to create and doesn't add any IO cost, wether
at creation time or at maintenance time. The goal is obviously to
check if an index is useful before spending many time, I/O and disk
space to create it.

With this extension, you can create hypothetical indexes, and then
with EXPLAIN check if PostgreSQL would use them or not.

## " What if I had an Index on this ? "

Did you ever wonder how an index would increase the performances of
your server, but you couldn't afford the time to create it on disk
just for the sake of trying ?

Here's how HypoPG can help:

First let's create a simple use case:

    # CREATE TABLE testable AS SELECT id, 'line ' || id val FROM
generate_series(1,1000000) id;
    # ANALYZE testable ;

Now let's install HypoPG and create an hypothetical index on this new
table

    # CREATE EXTENSION hypopg;
    # SELECT hypopg_create_index('CREATE INDEX ON testable (id)');

You can now use EXPLAIN (without ANALYZE) to check if PostgreSQL would
use that index !

    # EXPLAIN SELECT * FROM testable WHERE id = 1000 ;
                                              QUERY PLAN

-----------------------------------------------------------------------------------------------
     Index Scan using <41079>btree_testable_id on testable
(cost=0.05..8.07 rows=1 width=15)
       Index Cond: (id = 1000)
    (2 rows)

Yay ! If there were an index on the 'id' column, PostgreSQL would take
advantage of it !

## Links

  * Repository : https://github.com/dalibo/hypopg
  * Install with PGXN : http://pgxn.org/dist/hypopg

## About DALIBO :

DALIBO is the leading PostgreSQL company in France, providing support,
trainings and consulting to its customers since 2005. The company
contributes to the PostgreSQL community in various ways, including :
code, articles, translations, free conferences and workshops

Check out DALIBO's open source projects at http://dalibo.github.io.

http://www.dalibo.com

-- 
Julien Rouhaud
http://dalibo.com - http://dalibo.org

--

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

Björn Häuser | 22 Jun 08:37 2015
Picon

PGConf.DE 2015 - Call for Papers

This year's PGConf.DE will be held on November 26-27 in Hamburg,
Germany, at the Lindner Hotel am Michel. Topics for PostgreSQL Users,
Developers,
Contributors and Decision Makers will be covered. For more information
about the conference, please visit the conference website:

http://2015.pgconf.de/

We are now accepting proposals for talks. Please note that we are
looking for talks in English and German.

Each session will last 45 minutes, and may be on any topic related to
PostgreSQL. Suggested topic areas include:

* Developing applications for PostgreSQL
* Administering large scale PostgreSQL installations
* Case studies and/or success stories of PostgreSQL deployments
* PostgreSQL tools and utilities
* PostgreSQL hacking
* Community & user groups
* Tuning the server
* Migrating from other systems
* Scaling/replication
* Benchmarking & hardware
* PostgreSQL related products

Of course, we're happy to receive proposals for talks on other
PostgreSQL related topics as well.

The submission deadline is the September 13, 2015. Selected speakers
will be notified before September XX, 2015.

Please submit your proposal via the Event System of PostgreSQL.EU:

https://www.postgresql.eu/events/speakerprofile/pgconfde2015/

Please first set up your speaker profile. If you have already spoken at
a previous PGConf.EU event, you already have a profile - please take a
minute to review it. Once you have a profile, this page will provide a
link to a form where you can submit one or more talks for PGConf.DE
2015. When the site prompts for a login, use your regular postgresql.org
community account.

The proposals will be considered by committee that will publish a
schedule in advance of the conference.

The Call for Papers is also available through the website:

http://2015.pgconf.de/cfp

We are looking forward to hear from you and we will see you in
Hamburg in November.

====

We are also looking for sponsors. Are you interested? Check your
opportunities here: http://2015.pgconf.de/de/sponsoring.html

--

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

David Fetter | 22 Jun 00:10 2015

== PostgreSQL Weekly News - June 21 2015 ==

== PostgreSQL Weekly News - June 21 2015 ==

PostgreSQL Conference Europe 2015 in Vienna, Austria, on October 27-30 is
now accepting registrations for conference attendance at
http://2015.pgconf.eu/registration/.

== PostgreSQL Product News ==

The Amazon Linux AMI now has pgTap support.
https://lambda-linux.io/blog/2015/06/18/announcing-pgtap-support-for-amazon-linux/

MJSQLView Version 7.03, a Java-based UI which supports PostgreSQL, released.
http://myjsqlview.com/

PG Partition Manager 2.0.0 an extension to manage table
partitioning, released.
https://github.com/keithf4/pg_partman

psycopg2 2.6.1, a Python connector for PostgreSQL, released.
http://initd.org/psycopg/articles/2015/06/16/psycopg-261-released/

== PostgreSQL Jobs for June ==

http://archives.postgresql.org/pgsql-jobs/2015-06/threads.php

== PostgreSQL Local ==

The second Swiss Postgres Conference will be held June 25-26, 2015 at
HSR Rapperswil.
http://www.postgres-conference.ch/

PGDay UK, Conference will be taking place on 7th July 2015 – it is aimed at
the UK PostgreSQL Community.  The CfP is open until 13 April 2015.
http://www.postgresqlusergroup.org.uk

PGDay Campinas 2015 will take place in Campinas on August 7.
The CfP is open through May 31.
http://pgdaycampinas.com.br/english/

The Call For Papers for PostgresOpen 2015, being held in Dallas, Texas
from September 16th to 18th, is now open.
http://2015.postgresopen.org/callforpapers/

The CfP for PostgreSQL Session #7, September 24th, 2015 in Paris,
France, is open until June 15, 2015.  call-for-paper <AT>
postgresql-sessions <DOT> org.
http://www.postgresql-sessions.org/7/about

PostgreSQL Conference Europe 2015 will be held on October 27-30 in the
Vienna Marriott Hotel, in Vienna, Austria.  The CfP is open until
August 7.
http://2015.pgconf.eu/

PGConf Silicon Valley 2015 is November 17-18 at the South San
Francisco Convention Center.  The CfP is open through June 15.
http://www.pgconfsv.com

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

Michael Meskes pushed:

- Check for out of memory when allocating sqlca.  Patch by Michael
  Paquier
  http://git.postgresql.org/pg/commitdiff/94a484222caece19e381a6941b8d826027ac2e75

- Fix memory leak in ecpglib's connect function.  Patch by Michael
  Paquier
  http://git.postgresql.org/pg/commitdiff/af0b49fc98cb3494d1e444a4f5c3364627a3ed5f

Andrew Dunstan pushed:

- Fix comment in fmgr.h to refer to actual function used.
  FunctionLookup() is long gone if it ever existed, and fmgr_info() is
  what's now used, so the comments now reflect that.
  http://git.postgresql.org/pg/commitdiff/41d798a139b5c94ad8ce10b192141b5bcc03dda3

Noah Misch pushed:

- Detect setlocale(LC_CTYPE, NULL) clobbering previous return values.
  POSIX permits setlocale() calls to invalidate any previous
  setlocale() return values.  Commit
  5f538ad004aa00cf0881f179f0cde789aad4f47e neglected to account for
  that.  In advance of fixing that bug, switch to failing hard on
  affected configurations.  This is a planned temporary commit to
  assay buildfarm-represented configurations.
  http://git.postgresql.org/pg/commitdiff/b76e76be460a240e99c33f6fb470dd1d5fe01a2a

- Revert "Detect setlocale(LC_CTYPE, NULL) clobbering previous return
  values." This reverts commit
  b76e76be460a240e99c33f6fb470dd1d5fe01a2a.  The buildfarm yielded no
  related failures.
  http://git.postgresql.org/pg/commitdiff/1f2a378de41bf3e516b8d2c4d65790aeefbfb89d

- Fix failure to copy setlocale() return value.  POSIX permits
  setlocale() calls to invalidate any previous setlocale() return
  values, but commit 5f538ad004aa00cf0881f179f0cde789aad4f47e
  neglected to account for setlocale(LC_CTYPE, NULL) doing so.  The
  effect was to set the LC_CTYPE environment variable to an unintended
  value.  pg_perm_setlocale() sets this variable to assist PL/Perl;
  without it, Perl would undo PostgreSQL's locale settings.  The
  known-affected configurations are 32-bit, release builds using
  Visual Studio 2012 or Visual Studio 2013.  Visual Studio 2010 is
  unaffected, as were all buildfarm-attested configurations.  In
  principle, this bug could leave the wrong LC_CTYPE in effect after
  PL/Perl use, which could in turn facilitate problems like corrupt
  tsvector datums.  No known platform experiences that consequence,
  because PL/Perl on Windows does not use this environment variable.
  The bug has been user-visible, as early postmaster failure, on
  systems with Windows ANSI code page set to CP936 for "Chinese
  (Simplified, PRC)" and probably on systems using other multibyte
  code pages.  (SetEnvironmentVariable() rejects values containing
  character data not valid under the Windows ANSI code page.)
  Back-patch to 9.4, where the faulty commit first appeared.  Reported
  by Didi Hu and 林鹏程.  Reviewed by Tom Lane, though this fix
  strategy was not his first choice.
  http://git.postgresql.org/pg/commitdiff/f0a264a362343287051c4737b01aa3ebe36f21a6

Robert Haas pushed:

- Change TAP test framework to not rely on having a chmod executable.
  This might not work at all on Windows, and is not ever efficient.
  Michael Paquier
  http://git.postgresql.org/pg/commitdiff/ca3f43aa48a83013ea50aeee7cd193a5859c4587

- Add PASSWORD to tab completions for CREATE/ALTER ROLE/USER/GROUP.
  Jeevan Chalke
  http://git.postgresql.org/pg/commitdiff/86e4751786bb0dcb29528ef49b067d0e393e4934

- Fix corner case in autovacuum-forcing logic for multixact
  wraparound.  Since find_multixact_start() relies on
  SimpleLruDoesPhysicalPageExist(), and that function looks only at
  the on-disk state, it's possible for it to fail to find a page that
  exists in the in-memory SLRU that has not been written yet.  If that
  happens, SetOffsetVacuumLimit() will erroneously decide to force
  emergency autovacuuming immediately.  We should probably fix
  find_multixact_start() to consider the data cached in memory as well
  as on the on-disk state, but that's no excuse for
  SetOffsetVacuumLimit() to be stupid about the case where it can no
  longer read the value after having previously succeeded in doing so.
  Report by Andres Freund.
  http://git.postgresql.org/pg/commitdiff/ed16f73c574660aa0902caa1c0adeba07f8c70a5

Tom Lane pushed:

- Fix bogus range_table_mutator() logic for RangeTblEntry.tablesample.
  Must make a copy of the TableSampleClause node; the previous coding
  modified the input data structure in-place.  Petr Jelinek
  http://git.postgresql.org/pg/commitdiff/be87143fe90adf8862791aeddd76151e88ce5603

- In immediate shutdown, postmaster should not exit till children are
  gone.  This adjusts commit 82233ce7ea42d6ba519aaec63008aff49da6c7af
  so that the postmaster does not exit until all its child processes
  have exited, even if the 5-second timeout elapses and we have to
  send SIGKILL.  There is no great value in having the postmaster
  process quit sooner, and doing so can mislead onlookers into
  thinking that the cluster is fully terminated when actually some
  child processes still survive.  This effect might explain recent
  test failures on buildfarm member hamster, wherein we failed to
  restart a cluster just after shutting it down with "pg_ctl stop -m
  immediate".  I also did a bit of code review/beautification,
  including fixing a faulty use of the Max() macro on a volatile
  expression.  Back-patch to 9.4.  In older branches, the postmaster
  never waited for children to exit during immediate shutdowns, and
  changing that would be too much of a behavioral change.
  http://git.postgresql.org/pg/commitdiff/48913db887e6a41fa3f1b6cdf80ee89e38f21d9d

Álvaro Herrera pushed:

- Clamp autovacuum launcher sleep time to 5 minutes.  This avoids the
  problem that it might go to sleep for an unreasonable amount of time
  in unusual conditions like the server clock moving backwards an
  unreasonable amount of time.  (Simply moving the server clock
  forward again doesn't solve the problem unless you wake up the
  autovacuum launcher manually, say by sending it SIGHUP).  Per
  trouble report from Prakash Itnal in
  https://www.postgresql.org/message-id/CAHC5u79-UqbapAABH2t4Rh2eYdyge0Zid-X=Xz-ZWZCBK42S0Q <at> mail.gmail.com
  Analyzed independently by Haribabu Kommi and Tom Lane.
  http://git.postgresql.org/pg/commitdiff/da1a9d0f5bed1f93908be9233a4fef39b988e505

- Fix thinko in comment (launcher -> worker)
  http://git.postgresql.org/pg/commitdiff/3c400a3f2bf4bb93a60cefc09416d37fc3dab8ed

- Fix BRIN supported operators table.  Some of the entries in the
  inclusion opclasses where missing operators, and we had an entry for
  inet_inclusion_ops instead of network_inclusion_ops.  Sort the
  operators within each opclass by strategy number, just to make it
  easier to spot mistakes.  Also sort the rows by data type name,
  rather than OID.
  http://git.postgresql.org/pg/commitdiff/1443a165db007462c5044ad8d03d919ac4323e6d

- Add transforms to pg_get_object_address and friends.  This was
  missed when transforms were added by commit cac76582053ef8e.
  Extracted from a larger patch.  Author: Michael Paquier
  http://git.postgresql.org/pg/commitdiff/ad89a5d115b3b4025f3c135f95f722e7e4becf13

Peter Eisentraut pushed:

- PL/Perl: Add alternative expected file for Perl 5.22
  http://git.postgresql.org/pg/commitdiff/103382abf87453d6555755da8f9fbef0b9965f81

Andres Freund pushed:

- Add missing check for wal_debug GUC.  9a20a9b2 added a new elog(),
  enabled when WAL_DEBUG is defined. The other WAL_DEBUG dependant
  messages check for the wal_debug GUC, but this one did not. While at
  it replace 'upto' with 'up to'.  Discussion:
  20150610110253.GF3832 <at> alap3.anarazel.de Backpatch to 9.4, the first
  release containing 9a20a9b2.
  http://git.postgresql.org/pg/commitdiff/90231cd5188c43da94f58f7a839eee9286d0f864

- Improve multixact emergency autovacuum logic.  Previously autovacuum
  was not necessarily triggered if space in the members slru got
  tight. The first problem was that the signalling was tied to values
  in the offsets slru, but members can advance much faster. Thats
  especially a problem if old sessions had been around that previously
  prevented the multixact horizon to increase. Secondly the skipping
  logic doesn't work if the database was restarted after autovacuum
  was triggered - that knowledge is not preserved across restart. This
  is especially a problem because it's a common panic-reaction to
  restart the database if it gets slow to anti-wraparound vacuums.
  Fix the first problem by separating the logic for members from
  offsets. Trigger autovacuum whenever a multixact crosses a segment
  boundary, as the current member offset increases in irregular
  values, so we can't use a simple modulo logic as for offsets.  Add a
  stopgap for the second problem, by signalling autovacuum whenver
  ERRORing out because of boundaries.  Discussion:
  20150608163707.GD20772 <at> alap3.anarazel.de Backpatch into 9.3, where
  it became more likely that multixacts wrap around.
  http://git.postgresql.org/pg/commitdiff/667912aee649c3608e003568e4b47d95251b1c8c

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

David Rowley sent in a patch to let the executor notice when
multiple aggregates in a query share a transition function, executing
the common ones only once per row.

David Rowley sent in another revision of a patch to improve some
appendStringInfo* calls.

Petr Jelinek sent in a patch to extend CREATE EXTENSION to include its
dependencies via the optional RECURSIVE keyword.

Fabien COELHO sent in another revision of a patch to fix pgbench
--progress report under (very) low rate.

Tomas Vondra sent in a patch to teach the expression walker about
RestrictInfo.

Amit Kapila sent in a patch to rename mapfile if backupfile not
present.

Vik Fearing sent in a patch to add tab completion to psql for CREATE
SEQUENCE.

Michael Paquier sent in a patch to reproduce a problem with pg_rewind.

Michael Paquier sent in a patch to make process_remote_file ignore
files named as pg_xlog/xlogtemp.*.

SAWADA Masahiko sent in a patch to give the GIN function of
pageinspect a consistent data type.

Fabien COELHO sent in another revision of a patch to smooth checkpoint
flushing.

Tomas Vondra sent in another revision of a patch to make a better
ndistinct estimator.

Brendan Jurd sent in two revisions of a patch to add a new built-in
function pg_notify_queue_saturation().

Michael Paquier sent in three more revisions of a patch to improve the
way TAP tests log their output using IPC::Run::run.

Michael Paquier sent in a patch to add a missing -w switch to the
pg_ctl stop call in pg_regress.

Robert Haas, Tom Lane, and Dean Rasheed traded patches to fix some
infelicities recently introduced in the table inheritance part of the
planner.

Marti Raudsepp sent in a patch to fix pg_upgrade when postgres
template1 aren't in the default database.

Petr Jelinek sent in another revision of a patch to implement tab
completion in psql for TABLESAMPLE.

Álvaro Herrera sent in a patch to add pg_get_multixact_members() and
pg_get_multixact_range().

Thomas Munro sent in a patch to fix an issue where the
get_relation_info comment was out of sync with the code nearby.

Andres Freund sent in a patch to rework the way multixact truncations
work with the goal of making them WAL logged.

Fabien COELHO sent in a patch to allow backslash-continuations in
custom scripts.

--

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

Rajiv M Ranganath | 18 Jun 21:15 2015
Picon

Announcing pgTAP Support for Amazon Linux AMI

Hi,

We just announced pgTAP Support for Amazon Linux AMI in Lambda Linux Project.

For more details please see the following blog post,

https://lambda-linux.io/blog/2015/06/18/announcing-pgtap-support-for-amazon-linux/

Thank you!

Best,
Rajiv

--

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

Guillaume Lelarge | 15 Jun 22:32 2015

PostgreSQL Conference Europe Registration Open

Hi,

PostgreSQL Conference Europe 2015 in Vienna, Austria, on October 27-30 is now accepting registrations for conference attendance at http://2015.pgconf.eu/registration/.

The Early Bird special price will be available until September 14th, and is limited to 75 tickets which is a great reason to get your registration in early!

And don't forget - the call for papers is still open! If you have already submitted a talk, or are planning to submit one, we suggest
you wait to register until you have received a confirmation if the talk was accepted or not. The early bird rate will be available long enough for you to register after you have received this notification - and if your talk is accepted, attendance is of course free!

As usual, if you have any questions, don't hesitate to contact us at contact <at> pgconf.eu.

See you in Vienna!

Daniele Varrazzo | 15 Jun 19:30 2015
Picon

Psycopg 2.6.1 released

Psycopg 2.6.1 has been released. You can get it from:

http://initd.org/psycopg/tarballs/PSYCOPG-2-6/psycopg2-2.6.1.tar.gz

The most important bug fixed in this release is the libcrypto
callbacks conflict between libpq and Python (#290), fixed by Jan
Urbański: thank you very much. Other bugs fixed:

- Lists consisting of only None are escaped correctly (ticket #285).
- Correctly unlock the connection after error in flush (ticket #294).
- Fixed MinTimeLoggingCursor.callproc() (ticket #309).

----

Psycopg is the most popular PostgreSQL adapter for the Python
programming language. At its core it fully implements the Python DB
API 2.0 specifications. Several extensions allow access to many of the
features offered by PostgreSQL.

--

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


Gmane