Lightning PHP Conference

php.net - 2019-07-10(水) 10:27:54
カテゴリー: php

Paul Ramsey: Serving Dynamic Vector Tiles from PostGIS

planet postgresql - 2019-07-10(水) 04:09:15

One of the most popular features of PostGIS 2.5 was the introduction of the "vector tile" output format, via the ST_AsMVT() function.

カテゴリー: postgresql

501 Not Implemented

planet PHP - 2019-07-10(水) 00:00:00

A server can return 501 Not Implemented when it doesn’t support a certain feature.

The RFC specifically lists ‘not supporting a specific HTTP method on any resource’ as an example of this.

In most practical cases this is similar enough to 405 Method Not Allowed, and 405 is probably the clearer status code for most cases.

But there is a difference. Given that 5xx are server-side errors and 4xx errors are client-side errors, it could be argued that emitting a 405 means that a method should never really have been called by a client, but a 501 could mean that the method should have worked, but the server has incomplete functionality.

I sometimes use 501 during development of new API’s as a ‘stub response’ for features I intend to build, but haven’t built yet.

Example HTTP/1.1 501 Not Implemented Content-Type text/html <img src="http://geocities.example/work-in-progress.gif" /> References
カテゴリー: php

Interview with Adam Kelso

planet PHP - 2019-07-09(火) 20:30:00
カテゴリー: php

Xdebug Update: June 2019

planet PHP - 2019-07-09(火) 17:34:00
Xdebug Update: June 2019
London, UK Tuesday, July 9th 2019, 09:34 BST

This is another of the monthly update reports in which I explain what happened with Xdebug development in this past month. It will be published on the first Tuesday after the 5th of each month. Patreon supporters will get it earlier, on the first of each month. You can become a patron here to support my work on Xdebug. More supporters, means that I can dedicate more of my time to improving Xdebug.

In June, I worked on Xdebug for 20 hours, a little bit less than normal as I spend some time on holidays and conferences. I worked on the following things:

2.8.0alpha1 Release

I made the 2.8.0alpha1 release just when the month was running out.

This alpha release adds preliminary support for PHP 7.4, but also addresses some other issues. It also includes a major overhaul on how I do SKIPIF sections in the phpt tests that I used to test Xdebug.

Instead of having complicated sections such as:

<?php if (!version_compare(phpversion(), "7.0.12", '>')) echo "skip > PHP 7.0.12, < PHP 7.1 needed\n"; ?> <?php if (!version_compare(phpversion(), "7.1", '<')) echo "skip > PHP 7.0.12, < PHP 7.1 needed\n"; ?> <?php if (!extension_loaded('zend opcache')) echo "skip opcache required\n"; ?>

I now have:

check_reqs('PHP > 7.0.12,< 7.1; opcache');

This makes it a lot easier to maintain Xdebug's tests among multiple PHP versions. After all, I still support PHP versions since PHP 7.0—although I am considering dropping support for PHP 7.0 and only support PHP 7.1 and up.

This release also features the Resolving Breakpoints feature, which the folks at Jetbrains have sponsored. After their last review, I made a few modifications to run the resolver for newly set breakpoints as well. I am intending to create a separate blog post on what this new feature solves.

I also found an already existing bug with return breakpoints, where a return breakpoint would not cause a break immediately. A fix for this is also in the alpha1 release.

32-bit Support

I am considering to stop creating Windows binaries for 32-bit installations of PHP. It duplicates the amount of time that it takes to create Windows binaries, slowing down, testing on CI, and the creation of releases as it takes ~2 hours to build them all on AppVeyor.

The reasoning behind this is that very few people still use 32-bit Windows installations, and I am not sure it's still worth spending time on it. I ran a little Twitter poll about this.

Although even if I would drop 32-bit binary creations, users that still need these binaries can get them on the PECL website. PECL builds extensions for all the supported PHP versions, such as here for Xdebug 2.8.0alpha1


I have been continuing with the PHP Internals News podcast. In this weekly podcast, I discuss in 15-30 minutes, proposed new features to the PHP language with fellow PHP internals developers. It is available on Spotify and iTunes, and through an RSS Feed. Let me know if you are a listener!

カテゴリー: php

Laurenz Albe: ICU collations against glibc 2.28 data corruption

planet postgresql - 2019-07-09(火) 17:00:27
© Laurenz Albe 2019


As announced by the glibc mailing list and discussed by Daniel Vérité, version 2.28 of the GNU C library will ship with many changes to the collations.

PostgreSQL uses the operating system’s collations by default. When this version hits your Linux operating system as part of a routine bugfix upgrade, and you don’t use the “C” or “POSIX” collation, you stand a good chance that some indexes on strings will become corrupted.

Since version 10, PostgreSQL does not only support “libc collations” but also “ICU collations” which are not affected by this particular problem. This article describes ICU collations and how you can swicth your database to using them.

Why is it a problem?

Collations determine how strings are compared, so they affect everything in the database that has to do with the order of strings:

  • the result of the ORDER BY clause in SELECT statements
  • the order of the keys in b-tree indexes

Now changes in the behavior of ORDER BY can be annoying, but that doesn’t threaten data integrity.
But if your index is suddenly ordered in the wrong fashion, that is data corruption. It can lead to incorrect query results or to duplicate entries in UNIQUE indexes.

What can you do to avoid the problem

The straightforward remedy is to rebuild all indexes on strings.

The simple way to do this is to run REINDEX INDEX for all affected indexes. However, that will block all attempts to use the index and will prevent data modifications on the table.

In PostgreSQL v12, you will be able to use REINDEX CONCURRENTLY to avoid these disadvantages. In older versions of PostgreSQL you can work around this by using CREATE INDEX CONCURRENTLY to create a copy of the index, then drop the original one. I’ll demonstrate that further down.

But I wanted to tell you about ICU collations and their benefits.

ICU collations

ICU is a set of libraries that provide, among other things, standardized Unicode collations. They are available on many op

カテゴリー: postgresql

Interview with Adam Harvey

planet PHP - 2019-07-09(火) 15:03:00
カテゴリー: php

Luca Ferrari: Generate Primary Keys (almost) Automatically

planet postgresql - 2019-07-09(火) 09:00:00

What if your database design is so poor that you need to refactor tables in order to add primary keys?

Generate Primary Keys (almost) Automatically

While playing on quite large database (in terms of number of tables) with a friend of mine, we discovered that almost all tables did not have a primary key!
This is really baaaad!

Why is that bad? Well, you should not ask, but let’s keep the poor database design alone and focus on some more concrete problems: in particular not having a primary key prevents a lot of smart softwares and middlewares to work on your database. As you probably know, almost every ORM requires each table to have at least one surrogate key in order to properly identify each row and enable persistence (that is, modification of rows).

Luckily, fixing tables for such software is quite simple: just add a surrogate key and everyone will be happy again. But unluckily, while adding a primary key is a matter of issuing an ALTER TABLE, doing so for a long list of tables is boring.

Here comes the power of PostgreSQL again: thanks to its rich catalog, it is possible to automate the process.

In this post you will see how to build from a query to a whole procedure that does the trick.

A query to generate the ALTER TABLE commands

A first example is the following query, that searches for every table in the schema public that does not have a constraint of type p (primary key) and issue an ALTER TABLE for such table:

testdb=# WITH to_be_fixed...
カテゴリー: postgresql

Interview with Adam Harvey

planet PHP - 2019-07-09(火) 01:01:00
カテゴリー: php

Luca Ferrari: PostgreSQL & recovery.conf

planet postgresql - 2019-07-08(月) 09:00:00

The coming version of PostgreSQL, 12, will loose the recovery.conf file. It will get some time to get used to!

PostgreSQL & recovery.conf

According to the documentation for the upcoming version 12, the recovery.conf file has gone! The release note states it clearly: the server will not start if recovery.conf is in place and all the configuration parameters have moved to the classic postgresql.conf (or included files).

The change proposal is quite old, but represents a deep change in the way PostgreSQL handles the server startup and recovery and could take a while to get all the software out there to handle it too.

Please note that since PostgreSQL 12 is still in beta, things could change a little, even if the discussion and the implementation is nearly ended.

Two files can be created to instrument a standby node:

  • standby.signal if present in the PGDATA directory the host will work as a standby, that is it will wait for new incoming WAL segments and replay them for the rest of its life;
  • recovery.signal if present will stop the WAL replaying as soon as all the WALs have been consumed or the recovery_target parameter has been reached.

It is interesting to note that standby.signal takes precedence on recovery.signal, meaning that if both file exists the node will act as a standby. Both files may be empty, they act now as as triggering files rather than configuration files (here the change in the suffix).

So, what is the rationale for this change? There are several reasons, including the not needing for a duplication of configuration files. But...

カテゴリー: postgresql

JsonMapper 1.5 released

planet PHP - 2019-07-07(日) 21:40:00

Now that the kids are with their grandparents for a week, I finally had the time to merge pull requests and tinker on bug reports myself.

Today I released JsonMapper version 1.5.0, one and a half year after 1.4. It got support for PHP 7 scalar type declarations, and improves class map handling. Some common fatal errors are prevented by throwing an exception with a message that is easier to grasp.

I also shut down my PEAR channel, zustellzentrum.cweiske.de. JsonMapper is now only available via packagist.

カテゴリー: php

Daniel Vérité: The CSV output format in psql

planet postgresql - 2019-07-06(土) 21:50:12

Exporting query results in CSV has been possible for a long time (since version 8.0), either with
COPY (SELECT ...) TO STDOUT CSV as an SQL command, or with the \copy meta-command in psql, which invokes COPY under the hood and handles the flow of data on the client side.

But there are still a few cases not covered by this functionality, which is why in PostgreSQL 12, CSV has been added to the output formats. It means that we can now issue \pset format csv, so that all commands producing tabular results can output them in the CSV format. It can also be opted for on the command line with the --csv option.

In practice, we need to use an output format instead of \copy:

  • when data must be fetched through a query that is not supported by COPY, for instance a cursor, since fetch from c is valid but copy (fetch from c) to stdout csv is not.

  • when the result is produced by a meta-command instead of a query: \crosstabview, \l, \d etc…

CSV as a replacement for the unaligned format (-A)

Aside from \copy, simple exports in tabular format are often done with the “unaligned” format with its field separator given by the fieldsep parameter (-F option).

But this format has two weaknesses:

  • when the separator appears inside the data, there’s no way to distinguish it (no escape mechanism).
  • when line feeds are present inside fields (in multi-line text), there’s no way to distinguish a line feed within a field from a record separator.


$ query="select 'ab,cd' as col1, 'ef'||chr(10)||'gh' as col2" $ psql -AtF',' -c "$query" ab,cd,ef gh

In the above example, when trying to interpret the output, we can’t know where the fields start and end, nor how many records there were in the source data.

Although it comes from a single record of two columns, this output could just as well represent a single column with ab,cd,ef as the value of the first record, and gh for the second one.

The CSV format solves this problem:

$ psql --csv -t[...]
カテゴリー: postgresql

elein mustain: Postgresql Interval, Date, Timestamp and Time Data Types

planet postgresql - 2019-07-06(土) 04:42:23
Does anyone really know what time it is? First we have the bare bones of the PostgreSQL Interval, Date and Timestamp Data types. Here are the questions: What types are they? And what options do they have? What postgresql.conf variables affect date and time i/o? Available Date and Time Data types Date day of year […]
カテゴリー: postgresql

Louise Grandjonc: Testing your Django app with Citus

planet postgresql - 2019-07-05(金) 23:02:00

Recently, I started working on the django-multitenant application. The main reason we created it was to to help django developers use citus in their app. While I was working on it, I wrote unit tests. And to be able to reproduce a customer’s production environment, I wanted the tests to use citus and not a single node postgres. If you are using citus as your production database, we encourage you to have it running in your development environment as well as your staging environments to be able to minimise the gap between dev and production. To understand better the importance of dev/prod parity, I recommend reading the Twelve-Factor app that will give you ideas to lower the chances of having last minute surprising when deploying on prod.

The goal of this article is to explain how I set it up as I think that if you are using citus on production, it is better to have tests running with citus.

Setting up docker

I will consider that you already have docker installed, otherwise I recommend taking a look at our article[https://docs.citusdata.com/en/v8.1/installation/singlemachinedocker.html#single-machine-docker]

Once you have docker running, create in your app directory a docker-compose.yml containing:

version: '2.1' services: master: image: 'citusdata/citus:8.2.1' ports: ['5600:5432'] labels: ['com.citusdata.role=Master'] volumes: ['/var/run/postgresql'] manager: container_name: "${COMPOSE_PROJECT_NAME:-citus}_manager" image: 'citusdata/membership-manager:0.1.0' volumes: ['/var/run/docker.sock:/var/run/docker.sock'] depends_on: { master: { condition: service_healthy } } worker1: image: 'citusdata/citus:8.2.1' ports: ['5601:5432'] labels: ['com.citusdata.role=Worker'] depends_on: { manager: { condition: service_healthy } } worker2: image: 'citusdata/citus:8.2.1' ports: ['5602:5432'] labels: ['com.citusdata.role=Worker'] depends_on: { manager: { condition: service_healthy } } healthcheck: image: busybox depends_on: [...]
カテゴリー: postgresql

Kirk Roybal: Generated columns in PostgreSQL 12

planet postgresql - 2019-07-05(金) 22:26:11
Computed columns in PostgreSQL 12 are here, but still have a ways to go.
カテゴリー: postgresql

PHP 7.2.20 Released

php.net - 2019-07-04(木) 18:14:21
カテゴリー: php

PHP 7.3.7 Release Announcement

php.net - 2019-07-04(木) 17:29:00
カテゴリー: php

Interview with Adam Harvey

planet PHP - 2019-07-04(木) 14:03:00
カテゴリー: php

PHP 7.3.7 Release Announcement

planet PHP - 2019-07-04(木) 09:00:00
The PHP development team announces the immediate availability of PHP 7.3.7. This is a bug fix release.All PHP 7.3 users are encouraged to upgrade to this version.For source downloads of PHP 7.3.7 please visit our downloads page, Windows source and binaries can be found on windows.php.net/download/. The list of changes is recorded in the ChangeLog.
カテゴリー: php