Vasilis Ventirozos: Psql directly from Atom text editor.

planet postgresql - 2017-12-09(土) 00:39:00
Im not a big fun of postgres clients like pgadmin, not sure why, but i always favoured psql over everything.
A friend of mine wrote an atom package that brings psql into atom. Which is awesome. It's tested in linux and (briefly) in osx. In osx i only had to change the path for psql because for some reason it didn't get it from the environment. Feel free to check it out and spam him with any issues you might have.

Link for the package can be found here.

Thanks for reading
-- Vasilis Ventirozos
カテゴリー: postgresql

Umair Shahid: PostgreSQL 11 – Server-side Procedures (Part 1)

planet postgresql - 2017-12-07(木) 21:05:27

Last week marked a rather big step in the PostgreSQL world that went largely unnoticed. Thanks to the work done by 2ndQuadrant contributors, we now have the ability to write Stored Procedures in PostgreSQL!

A procedure is essentially a set of commands to be executed in a particular order. As opposed to functions, procedures are not required to return a value. With this addition, you can now invoke a procedure by simply using the new CALL statement rather than using SELECT. The implementation is fully compliant with the SQL standard and will allow users to write procedures that are somewhat compatible with DB2, MySQL, and to a lesser extent, Oracle.

The commit from last week adds new commands CALL, CREATE/ALTER/DROP PROCEDURE, as well as ALTER/DROP ROUTINE that can refer to either a function or a procedure (or an aggregate function, as an extension to SQL). It also includes support for procedures in various utility commands such as COMMENT and GRANT, as well as support in pg_dump and psql. Support for defining procedures is available in all the languages supplied by the core distribution.

While this commit simply adds structure that is built upon existing functionality, it lays down the foundation on which we will be building the real meat to be made available in PostgreSQL 11. Next steps include the implementation of:

  • Transaction control – allowing us to COMMIT and ROLLBACK inside procedures
  • Returning multiple result sets

Stay tuned for more information!

カテゴリー: postgresql

damien clochard: Major French companies officially asking sofware vendors to support PostgreSQL

planet postgresql - 2017-12-07(木) 16:15:09

The French PostgreSQL Cross-Enterprise Work Group, a non-profit organization advocating for the adoption and use of PostgreSQL, just published an open letter to all software vendors that don’t already do so to take steps to support PostgreSQL as a database management system (DBMS). The ambition of this initiative is to incite them to adapt quickly to the irresistible IT transformation in progress in public and private companies.

Created in 2016, the PostgreSQL Cross-Enterprise Work Group ( called “Groupe de Travail Inter-Entreprise” or “GT Entreprise” in French) is a dedidacted task force inside the PostgreSQLFr non-profit association. Over the last months, it has grown remarkably.

In a new press release (link below) published yesterday, the workgroup sent an open letter to software vendors, asking them officially to support PostgreSQL.

  • Original version :


  • English translation :


This is a major step for PostgreSQL in France and in the french-speaking countries. For the first time, more than 20 national and international companies take an explicit stance in favor of PostgreSQL by recognizing the technical value of the software but also highlighting the benefits of the open source model itself.

Among these companies and public agencies, you can find : Air France, Carrefour (retail), CASDEN (bank), CNES (national space study center), EDF, MSA / GIE AGORA (mutual insurance), E.Leclerc (retail), MAIF (mutual insurance), Météo France (national weather forecast agency), The National Education Ministry, PeopleDoc, SNCF (national railway company), PeopleDoc, SNCF, Société Générale (bank) and Tokheim Services Group.

These institutions gathered in the PostgreSQLFr association to share their experience, promote PostgreSQL and contribute to its development.  Beyond these 3 big missions, the remarkable point is that the group organized itself by adopting the great principles of the PostgreSQ

カテゴリー: postgresql

Paul Ramsey: PostGIS "Fund Me" Milestone

planet postgresql - 2017-12-07(木) 01:00:00

On the twitter this morning, there was a good question:

@postgis what is the Fund Me milestone mean? There is a feature request there that we are interested in using: https://t.co/jqGKjOgueQ

— Jonas Stawski (@jstawski) December 6, 2017

TL;DR: If you find a feature in “Fund Me” and want to fund it, join the postgis-devel mailing list and make yourself known.

If you go to the PostGIS ticket report and scroll through the pages you’ll first see some milestones tied to released versions. These are usually bug reports, both big and small, valid and invalid, and will eventually be closed.

We unfortunately carry a lot of tickets in the current development milestone (2.5 right now) which are, at best, speculative. They should probably be closed (we really will never do them and don’t much care) or moved to the “Fund Me” category (they are valid, but we have no personal/professional impetus to address them).

The “Fund Me” category used to be called “Future”. This was a bad name, as it implied that sometime in the “Future” the ticket might actually be addressed, and all you needed was sufficient patience to wait. The reality is that they way a ticket got into the “Future” category was that it was ignored for long enough that we couldn’t stand to see it in the current milestone anymore.

The PostGIS development community includes all kinds of developers, who make livings in all kinds of ways, and there are folks who will work on tasks for money. The “Fund Me” milestone is a way of pointing up that there are tasks that can be done, if only someone is willing to pay a developer to do them.

That’s the good news!

The bad news is that the tickets all look the same, but they are wildly variable in terms of level of effort and even feasibility.

  • #220 “Implement ST_Numcurves and ST_CurveN” would probably take a couple hours at the outside, and almost any C developer could do it, even oen with zero experience in the PostGIS/PostgreSQL/GEOS ecosystem.
  • #2597 “[raster] St_Grayscale” would require some knowledge of the PostGIS raster im
カテゴリー: postgresql

Aleksander Alekseev: PGConf.ASIA 2017: Impressions, Photos and Slides

planet postgresql - 2017-12-06(水) 22:00:04

Oleg Bartunov and I have been invited to the PGConf.ASIA 2017 conference that took place 4-6 December in Tokyo, Japan. Both of us attended the developer unconference. Also Oleg had an honor to be a keynote speaker and I gave a talk "PostgreSQL Sharding and HA: Theory and Practice …

カテゴリー: postgresql

Markus Winand: Modern SQL: IS DISTINCT FROM — A comparison operator that treats two NULL values as the same

planet postgresql - 2017-12-06(水) 09:00:00
NULL-Aware Comparison: is [not] distinct from

In SQL null is not equal (=) to anything—not even to another null. According to the three-valued logic of SQL, the result of null = null is not true but unknown. SQL has the is [not] null predicate to test if a particular value is null.

With is [not] distinct from SQL also provides a comparison operator that treats two null values as the same.

<expression> IS NOT DISTINCT FROM <expression>

Note that you have to use the negated form with not to arrive at similar logic to the equals (=) operator.

The following truth table highlights the differences between the equals sign (=) and is not distinct from.

A B A = B A IS NOT DISTINCT FROM B 0 0 true true 0 1 false false 0 null unknown false null null unknown true

The result with equals (=) is unknown if one operator is null. The is not distinct from comparison is true if both values are null or false if only one is null.

Conforming Alternatives Note

Although there are standard alternatives to is not distinct from, using a proprietary alternative is often the better choice.

Due to SQL’s three-valued logic, a fully equivalent substitute for A is not distinct from B that works in all SQL databases is surprisingly complex:

CASE WHEN (a = b) or (a IS NULL AND b IS NULL) THEN 0 ELSE 1 END = 0

The result of the expression in the when clause is true if both arguments are equal or both are null. If only one argument is null the result is unknown, not false. This is often not a problem because SQL generally treats unknown like false when making binary decisions such as accepting or rejecting a row for a where clause.

To get the fully equivalent functionality of is not distinct from—i.e. either true or false but never unknown—the case expression reduces the three-valued result into a two a two-valued one. In some databases is not false can be used instead of the case expression.

Another option is to use set operators, which use distinct comparisons internally. The following snippet uses intersect to dete

カテゴリー: postgresql

Dimitri Fontaine: Scaling Python released

planet postgresql - 2017-12-06(水) 02:10:00

Today I am very pleased to announce the release of the book Scaling Python from my good friend Julien Danjou!

As Julien says, Python applications can handle millions of requests. Well, we know here that it’s easier on them when they are using PostgreSQL of course!

カテゴリー: postgresql

Michael Paquier: Postgres 11 highlight - Removal of secondary checkpoint

planet postgresql - 2017-12-03(日) 16:05:22

It has been a long time since the last post. Today here is a post about the following feature that will land in Postgres 11:

commit: 4b0d28de06b28e57c540fca458e4853854fbeaf8 author: Simon Riggs <simon@2ndQuadrant.com> date: Tue, 7 Nov 2017 12:56:30 -0500 Remove secondary checkpoint Previously server reserved WAL for last two checkpoints, which used too much disk space for small servers. Bumps PG_CONTROL_VERSION Author: Simon Riggs Reviewed-by: Michael Paquier

Up to Postgres 10, PostgreSQL has been designed to maintain WAL segments (Write-Ahead Log, an internal journal in the shape of binary data which is used for recovering the instance up to a consistent point) worth two checkpoints. This has as direct effect that past WAL segments are not needed once two checkpoints have been completed, those getting either removed or recycled (renamed). The interest behind keeping two checkpoints worth of data is to get a fallback, so as if the last checkpoint record cannot be found then the recovery falls back to the checkpoint record prior that.

Note that on standbys, two checkpoints are not maintained, as only one checkpoint worth of WAL segments is kept in the shape of restart points created. The code path created both checkpoints and restart points is very similar (look at xlog.c and checkpoint.c).

Falling back to the prior checkpoint can be actually a dangerous thing, see for example this thread about the matter. And I have personally never faced a case where the last checkpoint record was not readable and that it was necessary to fallback to the prior checkpoint because the last checkpoint was not readable after an instance crash (PostgreSQL being legendary stable as well, it is not like one face crashes in production much anyway…).

So the commit above removes this prior checkpoint, which has a couple of consequences:

  • Setting value of max_wal_size will reduce by roughly 33% the frequency of checkpoints happening, assuming that checkpoint_target_completion gets close to 1. The maximum amount of time to finish re
カテゴリー: postgresql

Ozgun Erdogan: Citus 7.1: Window functions, distinct, distributed transactions, more

planet postgresql - 2017-12-02(土) 06:48:00

So about two weeks ago we had a stealth release of Citus 7.1. And while we have already blogged a bit about the recent (and exciting) update to our fully-managed database as a service–Citus Cloud—and about our newly-added support for distributed transactions, it’s time to share all the things about our latest Citus 7.1 release.

If you’re into bulleted lists, here’s the quick overview of what’s in Citus 7.1:

  • Distributed transaction support
  • Zero-downtime shard rebalancer
  • Window function enhancements
  • Distinct ON/count(distinct) enhancements
  • Additional SQL enhancements
  • Checking for new software updates

For all these new features in Citus 7.1, you can try them today on Citus Cloud, get started locally with our Citus open source downloads, or keep right on reading to learn more about all the new Citus things.

Distributed transactions in the Citus database

The new Citus 7.1 distributed transactions feature is so important to our customers that we dedicated an entire blog post to it. As well as a blog on distributed deadlocks, an important pre-requisite feature we had to deliver in Citus 7.0, before tackling the distributed transaction challenge in Citus 7.1.

Zero-downtime Shard Rebalancer

We already covered the new zero-downtime shard rebalancer in Craig’s previous Citus Cloud 2 post, so we won’t dig in too much here. Just in case you missed the Citus Cloud 2 launch, bottom line, the Citus Cloud shard rebalancer now offers a fully online operation, with zero-downtime. To implement the zero-downtime shard rebalancer, we extended Postgres 10 logical replication support. Oh, and to make it easy to observe progress during the rebalancer operation, we created a new UI, which we hope you find useful:

So today we’re excited to announce Citus Cloud 2 w/ sweet new features for our cloud database | Worry-free Postgres https://t.co/vawtGRkmi9 pic.twitter.com/meFjnDT3kt

— Citus Data (@citusdata) November 16, 2017 Cross-tenant support for Windows Functions in Citus

In SQL, a window function performs a computation across a se

カテゴリー: postgresql

Pavel Stehule: New release of pspg pager

planet postgresql - 2017-12-02(土) 04:25:00
* fix some crashes
* less pager like status bar

download from github.
カテゴリー: postgresql

Pavel Stehule: bugfix of precompiled dll of Orafce 3.6.2

planet postgresql - 2017-12-02(土) 04:06:00
Precompiled libraries of Orafce are available on url: https://postgres.cz/files/orafce-3.6.2.zip.
カテゴリー: postgresql

Joshua Drake: Speaking and training at PGConf Austin

planet postgresql - 2017-12-02(土) 02:01:47

I leave this Sunday for beautiful and ecclectic Austin, Tx. I will be providing the training Postgres Performance and Maintenance as well as speaking on The Power of Postgres Replication. The training is one that I give several times a year but the Replication talk is new. Although I have spoke on replication before, this new presentation is all about the power of Logical Replication. If you would like to learn more, pick up a ticket and let's have some fun. If you can't make it to Austin, perhaps you can make it to the PGConf Mini: NYC on December 14th. I will be presenting the same Replication presentation at that event. Let's bring about the new year with a strong showing of @amplifypostgres!

カテゴリー: postgresql

PHP 7.2.0 Released

php.net - 2017-11-30(木) 19:04:21
カテゴリー: php

Kaarel Moppel: Using “lightweight” functional indexes for better plan estimations

planet postgresql - 2017-11-30(木) 18:30:52

A quick performance tip for all those wrestling with occasional un-performant queries in PostgreSQL. There’s one quite simple trick available that many don’t know about, that can be applied at “low cost” when slow queries are caused by poor planner estimates for your filter conditions. Read on for details.

The problem

So the thing with Postgres statistics is that you basically lose them as soon as you transform the data column in some way in your query – most prominent such example would most probably be something like “SELECT … WHERE date_trunc(‘day’, some_indexed_timestamp_column) = ‘2017-01-01″…and gone is the (imaginary) index and column statistics. That query could of course be easily rewritten so that the performance doesn’t explode, but here also a functional index would make sense when rewrite is not possible or the query is executed often and is selective enough. But for queries/filters that are used very rarely a full functional index might not be ideal due to performance side-effects (index maintenance slows down updates, deletes, inserts a bit)…so welcome our performance helper of the day – BRIN functional indexes!

Example use case

So now let’s paint it red with a dummy example: let’s say we want to select all ID’s that are dividable by 10 out of 1 million sequentially growing IDs. Let’s create the test data and look at the resulting query plan.

krl@postgres=# CREATE TABLE t_event AS SELECT generate_series(1, 1e6) id; SELECT 1000000 krl@postgres=# CREATE INDEX ON t_event (id); CREATE INDEX krl@postgres=# ANALYZE t_event ; ANALYZE krl@postgres=# EXPLAIN SELECT * FROM t_event WHERE id % 10 = 0; QUERY PLAN ────────────────────────────────────────────────────────────── Seq Scan on t_event (cost=0.00..19425.00 <strong>rows=5000</strong> width=6) Filter: ((id % '10'::numeric) = '0'::numeric) (2 rows)

What do we see? Well, we see that Postgres expects to get only 5000 rows but actually we know that there would be 100k rows matched! Such a misestim

カテゴリー: postgresql

Jobin Augustine: PostgreSQL 10’s Quorum Commit for Synchronous Replication

planet postgresql - 2017-11-30(木) 16:06:09

PostgreSQL has been lauded for its robustness as an open-source object-relational database. With the introduction of each new version, the potency of the PostgreSQL database to store and wield highly proliferating data has also augmented. This also calls for protection of data should any unlikely event befall. PostgreSQL 10, along with many other flagship features, matures the high availability and protection against data loss with the introduction of “quorum commit” for synchronous replication.

In this article, we will discuss about what synchronous replication and quorum commit are and then move on to see how we can use use the keywords “FIRST” and “ANY” under the parameter synchronous_standby_names.

Synchronous vs asynchronous replication:

The primary difference between synchronous replication and asynchronous replication is the way in which data is written to the replica. Most synchronous replication products write data to the primary storage and standby DB simultaneously. Transactions waiting for commit on the primary will be allowed to proceed only after these standby servers confirm receipt of their data. Thus, the primary copy and the replica always remain synchronized. In case of any fail-over event, the replica can be promoted as the master.

In contrast, asynchronous replication products copy the data to the replica after it is already written to the primary storage. Although the replication process may occur in near-real-time, it is more common for replication to occur on a periodic or scheduled basis. In case of a fail-over event, one can expect some data loss.

Quorum Commit for Synchronous Replication:

PostgreSQL 9.6 introduced the feasibility of specifying multiple synchronous standbys with the aide of the parameter synchronous_standby_names. For example, a value of

synchronous_standby_names = '3 (standby_1, standby_2, standby_3, standby_4, standby_5)'

means that the primary server has to wait for commit confirmations from first 3 available standby nodes among the set of 5 standby

カテゴリー: postgresql

Abdul Yadi: Cash Spell in Indonesian

planet postgresql - 2017-11-30(木) 11:05:17

For those who interested in cash spelling in Indonesian, here I share PLPGSQL code:

SELECT cash_spell(1200000000.45, true);
'Satu Milyar Dua Ratus Juta Rupiah Empat Puluh Lima Sen'

CREATE OR REPLACE FUNCTION int_spell(i_val integer)
   _t text:=''::text;
   _SYMBOL text[]:=ARRAY['satu', 'dua', 'tiga', 'empat', 'lima', 'enam', 'tujuh', 'delapan', 'sembilan', 'sepuluh'
, 'sebelas', 'dua belas', 'tiga belas', 'empat belas', 'lima belas', 'enam belas', 'tujuh belas'
, 'delapan belas', 'sembilan belas'];
   _val integer:=i_val;
   _i integer;
   IF _val<1 OR _val>999 THEN
      RAISE EXCEPTION 'outside boundaries';
   END IF;

   _i:=(_val - (_val % 100)) / 100;
   IF _i>0 THEN
      IF _i>9 THEN
         RAISE EXCEPTION 'hundreds failed';
      END IF;
      IF _i = 1 THEN
         _t:=_SYMBOL[_i] || ' ratus';
      END IF;
      _val:=_val - (_i*100);
   END IF;

   IF _val>0 AND _val<20 THEN
      IF LENGTH(_t) > 0 THEN
         _t:= _t || ' ';
      END IF;
      _t:=_t || _SYMBOL[_val];
   END IF;

   _i:=(_val - (_val % 10)) / 10;
   IF _i>0 THEN
      IF _i>9 THEN
         RAISE EXCEPTION 'tenth failed';
      END IF;
      IF LENGTH(_t) > 0 THEN
         _t:= _t || ' ';
      END IF;
      IF _i = 1 THEN
         _t:= _t || 'sepuluh';
         _t:= _t || _SYMBOL[_i] || ' puluh';
      END IF;
      _val:=_val - (_i*10);
   END IF;

   IF _val>0 THEN
      IF LENGTH(_t) > 0 THEN
         _t:= _t || ' ';
      END IF;
      _t:=_t || _SYMBOL[_val];
   END IF;

   RETURN _t;
GRANT EXECUTE ON FUNCTION int_spell(integer) TO public;

CREATE OR REPLACE FUNCTION cash_spell(n numeric, b_initcap boolean)
   _t text:=''::text;
   _n numeric:=n;
   _frac numeric;
   _cent integer;
   _val integer;
   _suffix text[];
   _s text;
   _i integer;
   _unit numeric;
   IF _n > 999999999999999999999999999

カテゴリー: postgresql

Hubert 'depesz' Lubaczewski: Changes on explain.depesz.com

planet postgresql - 2017-11-30(木) 01:49:49
Bricklen Anderson reported a problem with explains that were using parallel processing. TL;DR: in case of parallel plans make “inclusive" and “exclusive" be wall-clock times, and not “how much time CPU did spend on it". The problem can be seen in here: and in here: You can see, in first one, that time in partial […]
カテゴリー: postgresql

Pierre-Emmanuel André: OpenBSD / PostgreSQL / Authentication

planet postgresql - 2017-11-29(水) 20:31:53
PostgreSQL and the BSD authentication

If you are an OpenBSD user and you are using PostgreSQL, you can use the BSD authentication to login into PostgreSQL. We will see how to do this.

カテゴリー: postgresql

Pavel Golub: Code Quality Comparison of Firebird, MySQL, and PostgreSQL

planet postgresql - 2017-11-29(水) 17:14:39

I have read very interesting post “Code Quality Comparison of Firebird, MySQL, and PostgreSQL” today about static analysis of three open-source RDBMS. And I wonder, should we use static code analyzers on an ongoing basis, e.g. PVS Studio?


So, the code-quality rankings are as follows:

  • 1 place – Firebird and PostgreSQL.
  • 2 place – MySQL.



Please remember that any review or comparison, including this one, is subjective. Different approaches may produce different results (though it is mostly true for Firebird and PostgreSQL, but not for MySQL).

Filed under: Coding, MySQL, PostgreSQL Tagged: Coding, MySQL, PostgreSQL
カテゴリー: postgresql

Markus Winand: Modern SQL: NULL — purpose, comparisons, NULL in expressions, mapping to/from NULL

planet postgresql - 2017-11-28(火) 09:00:00
NULL in SQL: Indicating the Absence of Data

“Every [SQL] data type includes a special value, called the null value,”0 “that is used to indicate the absence of any data value”.1

The null value does not indicate why a value is absent—it simply marks the places that do not have a data value. For example, the SQL language itself uses the null value for the absent values resulting from an outer join.2 Although exceptions exist3, it is not generally possible to tell why a value is null.

Users can use the null value for any reason they like. A very common use case is to allow optional attributes without introducing an extra table. Another important use case is error handling: Contrary to other programming languages, it is not an error to process null values in SQL. Null values just propagate through expressions without aborting execution.

Comparisons Involving null

Comparisons (<, >, =, …) to null are neither true nor false but instead return the third logical value of SQL: unknown. The following example removes all rows—even those where col is actually null—because the where clause treats unknown like false.

WHERE col = null Warning

The SQL Server setting SET ANSI_NULLS OFF changes this behavior.

Testing for null: is [not] null

The SQL predicate is [not] null tests whether the result of an expression is null or not.

<expression> is null

This returns true if the value of expression is null, and false otherwise. The optional not negates the result.

Null-Aware Equals Comparison: is not distinct from

SQL uses two different concepts to tell if two values are the “same”: equals (=) and distinct. The difference is that equals (=) treats comparisons to null as indeterminate—that’s why col = null returns unknown. Distinct, on the other hand, treats null like any other value—i.e. two null values are not distinct from each other (they are indistinguishable) whereas a null value and a not null value are distinct values (not the same).

Originally, distinct was just a concept used internally in the SQL standard to define th

カテゴリー: postgresql