フィードアグリゲーター

9.6.15

postgresql.org - 2019-08-08(木) 09:00:00
9.6.15 is the latest release in the 9.6 series.
カテゴリー: postgresql

9.5.19

postgresql.org - 2019-08-08(木) 09:00:00
9.5.19 is the latest release in the 9.5 series.
カテゴリー: postgresql

PHP 7.4.0beta2 released!

planet PHP - 2019-08-08(木) 09:00:00
The PHP team is glad to announce the second beta release of PHP 7.4: PHP 7.4.0beta2. This continues the PHP 7.4 release cycle, the rough outline of which is specified in the PHP Wiki. For source downloads of PHP 7.4.0beta2 please visit the download page. Please carefully test this version and report any issues found in the bug reporting system. Please DO NOT use this version in production, it is an early test version. For more information on the new features and other changes, you can read the NEWS file, or the UPGRADING file for a complete list of upgrading notes. These files can also be found in the release archive. The next release would be Beta 3, planned for August 22nd. The signatures for the release can be found in the manifest or on the QA site. Thank you for helping us make PHP better.
カテゴリー: php

Brandur Leach: Doubling the Sorting Speed of Postgres Network Types with Abbreviated Keys

planet postgresql - 2019-08-08(木) 01:50:44

A few months ago, I wrote about how SortSupport works in Postgres to vastly speed up sorting on large data types 1 like numeric or text, and varchar. It works by generating abbreviated keys for values that are representative of them for purposes of sorting, but which fit nicely into the pointer-sized value (called a “datum”) in memory that Postgres uses for sorting. Most values can be sorted just based on their abbreviated key, saving trips to the heap and increasing sorting throughput. Faster sorting leads to speedup on common operations like DISTINCT, ORDER BY, and CREATE INDEX.

A patch of mine was recently committed to add SortSupport for the inet and cidr types, which by my measurement, a little more than doubles sorting speed on them. inet and cidr are the types used to store network addresses or individual hosts and in either IPv4 or IPv6 (they generally look something like 1.2.3.0/24 or 1.2.3.4).

inet and cidr have some important subtleties in how they’re sorted which made designing an abbreviated key that would be faithful to those subtleties but still efficient, a non-trivial problem. Because their size is limited, abbreviated keys are allowed to show equality even for values that aren’t equal (Postgres will fall back to authoritative comparison to confirm equality or tiebreak), but they should never falsely indicate inequality.

Network type anatomy, and inet vs. cidr

A property that’s not necessarily obvious to anyone unfamiliar with them is that network types (inet or cidr) can either address a single host (what most people are used to seeing) or an entire subnetwork of arbitrary size. For example:

  • 1.2.3.4/32 specifies a 32-bit netmask on an IPv4 value, which is 32 bits wide, which means that it defines exactly one address: 1.2.3.4. /128 would work similarly for IPv6.

  • 1.2.3.0/24 specifies a 24-bit netmask. It identifies the network at 1.2.3.*. The last byte may be anywhere in the range of 0 to 255.

  • Similarly, 1.0.0.0/8 speci

[...]
カテゴリー: postgresql

Images and WordPress

planet PHP - 2019-08-07(水) 18:02:00

My new WordPress project has multiple photographs per post and as I wanted them to work in an efficient manner for multiple screen resolutions. The secret to this is the srcset and sizes attributes on the img tag.

It turns out that WordPress will create multiple sized thumbnails when you upload an image. It will also add the srcset and sizes attributes into your img tags for you if your image tag has a class of wp‑image‑{id} where {id} is the id of the image in the database.

Image sizes

The set of sizes of images that WordPress creates by default is rather small (you get widths of 150px, 300px, 768px, & 1024px). As I'm uploading 6000px wide photos, having some intermediate sizes greater than 1024 is useful to reduce the amount of data for desktop screens.

You can add new ones using the add_image_size() method which is best done after the theme is set up:

add_action('after_setup_theme', static function () { add_image_size('xl', 1500); add_image_size('2xl', 2000); add_image_size('3xl', 2500); add_image_size('4xl', 3000); add_image_size('5xl', 4000); add_image_size('6xl', 5000); });

The third parameter to add_image_size() is the height constraint, which I don't set as I don't want it to be taken into account for portrait orientation pictures.

Note that even though you've added additional sizes, WordPress will ignore any bigger than 1600 pixels unless you tell it otherwise by adding a filter to max_srcset_image_width:

add_filter('max_srcset_image_width', static function($max_width){ return $max_width < 6000 ? 6000 : $max_srcset_image_width; });

If you change the set of image sizes after you've uploaded some images then you can create the new images using the wp‑cli media regenerate command or install the Regenerate Thumbnails plugin and run it from the Tools menu in your admin.

Adding wp‑image‑{id} class to posts

If you use the WordPress admin site to create your posts, then it automatically set the wp‑image‑{id} class for you on our img tags. I use MarsEdit, a desktop client that uses the XML-RPC API, for creating my articles and it doesn't set this class yet, so I needed to write a plug in for this.

To do this, we put a filter on the xmlrpc_wp_insert_post_data hook to inspect the content of the post and update if needed.

add_filter('xmlrpc_wp_insert_post_data', function ($data) { if ($data['post_type'] ?? '' === 'post') { $content = wp_unslash($data['post_content'] ?? ''); // Find all <img> tags to add the "wp-image-{id}" class if (preg_match_all('/<img.*>/im', $content, $images)) { foreach ($images[0] as $img) { // Find the url in the src attribute if (preg_match('/src="([^"]+)/i', $img, $srcList)) { // Retrieve the id for this image. $postId = attachment_url_to_postid($srcList[1]); if (!$postId) { // This image isn't in the database, so don't touch it continue; } // Add the wp-image-{id} class if it doesn't exist for this image $list = []; if (stripos($img, 'class') === false || preg_match('/class="([^"]+)/i', $img, $list)) { $classes = $list[1] ?? ''; $hasClassAttribute = (bool)count($list); if (!preg_match('/wp-image-([0-9]{1,10})/i', $classes)) { // wp-image-{id} class does not exist on this img $classes .= ' wp-image-' . $postId; if ($hasClassAttribute) { // Update the img tag with the new class attribute $newImg = preg_replace('/class="[^"]*/', 'class="'.$classes, $img); } else { // Insert class attribute into the img tag $newImg = str_replace('<img ', '<img class="'.$classes.'" ', $img); } // Replace the original <img> with our updated one $content = str_replace($match, $newImg, $content); } } } } } $data['post_content'] = wp_slash($content); } return $data; });

Note that the post's content

Truncated by Planet PHP, read more at the original (another 3636 bytes)

カテゴリー: php

Tatsuo Ishii: Automatically updating materialized views

planet postgresql - 2019-08-07(水) 15:29:00
Materialized views are convenient and efficient way to retrieve information from database. Unlike ordinary views, materialized views save the query result and provide faster access to the data. For example, user can create a simple materialized view containing the number of rows in a table:

 CREATE MATERIALIZED VIEW mv1 AS SELECT count(*) FROM pgbench_accounts;

Obtaining the number of rows using the materialized view mv1 is much faster than  directly accessing number of rows in pgbench_accounts.

test=# SELECT * from v1;
 count 
--------
 100000
(1 row)

Time: 0.810 ms
test=# SELECT count(*) FROM pgbench_accounts;
 count 
--------
 100000
(1 row)

Time: 13.945 ms

However if you delete a row from pgbench_accounts, the number of rows in mv1 is still unchanged. To reflect the change of the base table (in this case pgbench_accounts) , you need to recreate or refresh (this actually recreate the contents of materialize views from scratch), which may take long time.

To overcome the problem, SRA OSS is proposing to add a new feature to existing materialized view  "incremental materialized view maintenance". This works like this.

  • add new syntax to allow incrementally update the materialized view when it is created.
  • install triggers automatically to be attached to the base table(s).
  • when UPDATE/DELETE/INSERT applies on the base tables, the trigger calculate the difference to the rows in the materialized view and update the rows.
These should update the materialized view in a real time manner. The price is the over head of trigger invocation. So frequently updating tables are not best suite for the incremental materialized view maintenance. Here are demonstrations how to use the incrementally updating materialized view.

First create incrementally updating materialized view.

CREATE INCREMENTAL MATERIALIZED view mv2 AS SELECT count(*) FROM pgbench_accounts;

The view initially says there are 100,000 rows.

SELECT * FROM mv2;
 count 
--------
 100000
(1 row)

Delete a row from pgbench_accounts. Now t[...]
カテゴリー: postgresql

Interview with James Titcumb

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

Interview with Jeff Geerling

planet PHP - 2019-08-07(水) 01:03:00
カテゴリー: php

505 HTTP Version Not Supported

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

505 HTTP Version Not Supported is a status that a server can emit if it doesn’t support the major HTTP version the client used to make the request.

To test this, I opened a telnet connection to a couple of major websites, and wrote the following:

GET / HTTP/4.0

A few sites returned a 400 Bad Request, but at least Cloudflair returned 505:

HTTP/1.1 505 HTTP Version Not Supported Server: cloudflare Date: Tue, 30 Jul 2019 15:23:35 GMT Content-Type: text/html Content-Length: 201 Connection: close CF-RAY: - <html> <head><title>505 HTTP Version Not Supported</title></head> <body bgcolor="white"> <center><h1>505 HTTP Version Not Supported</h1></center> <hr><center>cloudflare</center> </body> </html>

Currently you’re unlikely to run into this error though, unless you have a buggy client.

We did get a new major HTTP version (2) and a new one is on the way (3), but the mechanism for switching to HTTP/2 or 3 is different, and will just be ignored if a HTTP/1.1 server didn’t support the new version.

References
カテゴリー: php

Paul Ramsey: Waiting for PostGIS 3: Separate Raster Extension

planet postgresql - 2019-08-06(火) 23:28:02

The raster functionality in PostGIS has been part of the main extension since it was introduced. When PostGIS 3 is released, if you want raster functionality you will need to install both the core postgis extension, and also the postgis_raster extension.

カテゴリー: postgresql

Interview with Justin Carmony

planet PHP - 2019-08-06(火) 21:57:00
カテゴリー: php

Umair Shahid: Postgres is the coolest database – Reason #5: It can not be bought out

planet postgresql - 2019-08-06(火) 20:18:58
When I wrote about Reason #3: No Vendor Lock-in, I leaned on the liberal PostgreSQL License and the fact that there are many vendors that can Support PostgreSQL in production. Another aspect that deserves its own mention is the fact that PostgreSQL can not be bought.  Let me explain the significance of that statement.  MySQL.  […]
カテゴリー: postgresql

Xdebug Update: July 2019

planet PHP - 2019-08-06(火) 17:25:00
Xdebug Update: July 2019
London, UK Tuesday, August 6th 2019, 09:25 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 July, I worked on Xdebug for about 20 hours, still a little bit less than normal due to holidays. I worked on the following things:

2.8.0beta1 Release

On July 25th, the same day that PHP 7.4.0beta1 was released, I made Xdebug's 2.8.0beta1 release. PHP 7.4 had changed some internal structures that required changes in Xdebug. You have to use the Xdebug 2.8.0beta1 release with PHP 7.4.0beta1.

This first beta release mainly makes Xdebug compatible again with PHP, but also addresses a bunch of issues that were reported, including a fix for Code Coverage collections, and a few issues related to obtaining the correct PID (Process Identifier) number for debugging. It also addresses an issue with NULL bytes and the DBGp protocol.

NULL bytes in the DBGp protocol

In PHP, it is possible to have NULL bytes in variable, key, and property names by using \0, such as in:

<?php $name = "with_\0_null_char"; $$name = 42; ??>

The DBGp protocol represents variable names through the name attribute of the property element in its XML format:

However, if a name has a NULL character, Xdebug would generate the following XML:

The problem is however, that XML does not allow for NULL bytes in attribute names. The above XML is therefore not well-formed. When the DBGp protocol was designed many many years ago, this issue was not thought off, and hence, the DBGp protocol could not represent these NULL bytes in variable, key, and property names. If you look at the first XML snippet (with name="$name"), then you can see that the DBGp protocol does handle this for the values. In this case, it added the encoding="base64" attribute, and encoded the string "with_\0_null_char" as d2l0aF8AX251bGxfY2hhcg==.

A while ago, for Xdebug 2.6, I addressed this problem by an update to the DBGp protocol to include extended properties. When the IDE enables this feature, Xdebug will then communicate names and values through a base64 encoded value in a sub-element, such as:

What I had failed to see is that it also possible that class names can have NULL characters in their name—when you use anonymous classes:

<?php $a = new class { }; var_dump( $a ); ??>

This would still be communicated as:

The fix for issue #1682 fixes this, by checking whether either of name, value, or class name has an XML incompatible character. If that is the case, and the `extended_properties`` feature has been enabled by the IDE through the protocol, then Xdebug will communicate this value in the new format:

Truncated by Planet PHP, read more at the original (another 2298 bytes)

カテゴリー: php

Mark Wong: PDXPUG August Meetup: PostGIS

planet postgresql - 2019-08-06(火) 13:41:44

When: 6-8pm Thursday August 22, 2019
Where: PSU Business Accelerator (Parking is open after 5pm.)
Who: Jackson Voelkel

Jackson will discuss the basics of GIS, and the current environment in which enterprise spatial analytics are performed. Considering the many pitfalls of this current system, he will discuss how FOSS tools – especially PostgreSQL/PostGIS – are vitally important for modern spatial analytics. In addition to the PostGIS extension, Jackson will talk about network routing using the PgRouting extension and the interface between R and PostgreSQL. This talk will act more as a showcase of GIS and “spatial SQL” within PostgreSQL than it will nitty-gritty database development.

Jackson Voelkel is a Health Data Analyst on Kaiser Permanente’s GIS Team as well as an Adjunct Professor of Geography at Portland State University. He focuses on developing infrastructure for and performing advanced spatial analytics across healthcare, environment, urban planning, utilities, and econometrics. He teaches courses on advanced spatial analytics in R as well as spatial database design with PostgreSQL/PostGIS.

カテゴリー: postgresql

Hugo Dias: Comparing Temporary Tables for PostgreSQL & Oracle GTT

planet postgresql - 2019-08-05(月) 18:36:21

The temporary tables are a useful concept present in most SGBDs, even though they often work differently.

This blog describes the technical features for this kind of tables either in PostgreSQL (version 11) or Oracle (version 12c) databases with some specific examples. Although the purpose of these tables could be the same for all SGBD’s, their specifics, or the way of implementation and manipulation, are completely different.

This feature could be used both by developers or database administrators to store intermediate results that will be needed for further processing in order to provide good performance metrics.

Temporary Tables in PostgreSQL

In PostgreSQL these objects are valid only for the current session: they are created, used and dropped along the same session: the structure of the table and managed data only are visible for the current session, thus the other sessions don’t have access to the temporary tables created on the other sessions.

Below it’s showed a simple example to create a temporary table:

CREATE TEMPORARY TABLE tt_customer ( customer_id INTEGER ) ON COMMIT DELETE ROWS;

The temporary tables are created in a temporary schema: pg_temp_nn and it’s possible to create indexes on these tables:

creation index tt_cusomer_idx_1 on tt_customer(customer_id)

As the data rows on these tables could be also deleted, it’s possible to release the occupied storage through the execution of vaccum command:

VACUUM VERBOSE tt_customer

The analyze command can be executed as well on the temporary tables in order to collect the statistics:

ANALYZE VERBOSE tt_customer;

Both commands can be executed for this kind of table as SQL command, however, the autovaccum daemon that execute them does not act on the temporary tables.

Another important point to consider it’s related to the permanent and temporary tables with the same name: once it happens the permanent table only is taken into account when ca

[...]
カテゴリー: postgresql

Ibrar Ahmed: Out-Of-Memory Killer… or Savior

planet postgresql - 2019-08-02(金) 22:29:44

In a Linux world, whenever your database server crashes or gets terminated, you need to find its cause. There can be several reasons for this. It can be SIGSEGV, which is a crash due to some bug in the backend server, but this is the least likely reason. The most common reason is running out of disk space or running out of memory. If you are getting the “running out of space” error, the only solution is to clear some space and restart your database.

Out-Of-Memory Killer

Whenever your server/process is out of memory, Linux has two ways to handle that, the first one is an OS(Linux) crash and your whole system is down, and the second one is to kill the process (application) making the system run out of memory. The best bet for the second option is to kill the process and save the OS from crashing. In short, the Out-Of-Memory Killer is the process which is responsible for terminating the application to save the kernel from crashing, as it only kills the application and saves the entire OS from crashing. Let’s first discuss how OOM and works and how to control that, and later we will discuss how OOM Killer decides which application to kill.

One of the primary jobs of a Linux operating system is to allocate memory to a process when it is requesting memory allocation. In most cases, the process/application will request the OS for memory, but it will not use all of the memory that was requested. If the OS allocates memory to all the processes that are requesting memory but doesn’t plan to use it, it will soon run out of memory – and the system will crash. In order to handle this scenario, the operating system has a feature that enables the OS to commit memory to a process without actually allocating it. The allocation is done only when the process actually plans to use that memory. At times the OS may not have available memory but it will commit the memory to the process, and when the process plans to use the memory, the OS will allocate the committed memory if it is available. The downside of this featur

[...]
カテゴリー: postgresql

PHP 7.1.31 Released

php.net - 2019-08-02(金) 04:19:53
カテゴリー: php

Developing WordPress sites with Docker

planet PHP - 2019-08-01(木) 18:02:00

I recently set up a new WordPress based website and local Docker-based development environment. This post documents what I did, so that I can do it again next time! As I'm not in the WordPress world, many things are strange to me and I'm indebted to Jenny Wong for pointing me in the right direction on numerous occasions and being very patient with my questions! Thanks Jenny!

Project organisation

There's always ancillary files and directories in a project that aren't part of the actual website, so I have put the WordPress site in a subdirectory called app and then I have room for other stuff. My project's root directory looks like this:

$ tree . --dirsfirst -FL 1 . ├── app/ ├── bin/ ├── data/ ├── docker/ ├── README.md └── docker-compose.yml

This is what each item is for:

  • app/ – The WordPress application files are in this directory.
  • bin/ – Useful command-line scripts
  • data/ – MySQL dump files go here.
  • docker/ – Files required by the Docker setup are in this directory.
  • README.md – Every project needs a README!
  • docker-compose.yml – Development orchestration config file.

I put everything into git, with a .gitignore file to ignore everything in data along with various other WordPress files/directories that shouldn't in version control.

Docker

A pair of Docker containers is used to run the site locally for development. I'm slowly getting my feet wet with Docker, so I'm not sure if this is the best way to do things. The docker-compose command allows you to spin up multiple containers in one go and join them together. This is done via the docker-compose.yml file.

Mine looks like this:

docker-compose.yml:

version: '3' services: db: image: mysql:5.7 ports: - 127.0.0.1:3306:3306 command: [ '--default_authentication_plugin=mysql_native_password', '--character-set-server=utf8mb4', '--collation-server=utf8mb4_unicode_ci' ] volumes: - db_data:/var/lib/mysql environment: MYSQL_DATABASE: wordpress MYSQL_ROOT_PASSWORD: 123456 wp: build: context: ./docker dockerfile: Dockerfile-wp ports: - 127.0.0.1:80:80 volumes: - ./docker/php.conf.ini:/usr/local/etc/php/conf.d/conf.ini - ./app:/var/www/html depends_on: - db links: - db environment: DB_NAME: wordpress DB_USER: root DB_PASSWORD: 123456 DB_HOST: db WP_DEBUG: 1 LIVE_URL: https://project1.com DEV_URL: http://dev.project1.com volumes: db_data: # store database into a volume so that we can pause the containers

There are two containers: wp for the Apache/PHP and db for the MySQL.

The db container

The db container uses the default Docker MySQL container. I picked version 5.7 as that's what's running in my live environment. As this is a single-purpose development container, I just use the MySQL root user and set its password.

I want to persist the MySQL database between invocations of the container, so to do this, I create a volume called db_data and then map the /var/lib/mysql directory to that volume. I also expose MySQL on 3306 so that I can connect to it from my desktop.

The wp container

For the wp container, I start with the default Docker WordPress container and add XDebug and the WP-CLI to it. This is done in the ./docker/Dockerfile-wp file:

./docker/Dockerfile-wp:

FROM wordpress:php7.3-apache # Install xdebug RUN pecl install xdebug && docker-php-ext-enable xdebug # Install Less for WP-CLI RUN apt-get update && apt-get -y install less # Install WP-CLI RUN curl -s -o /usr/local/bin/wp \ https://raw.githubusercontent.com/wp-cli/builds/gh-pages/phar/wp-cli.phar \ && chmod +x /usr/local/bin/wp

docker-compose will now create a container from our Dockerfile-wp and use that.

I map the docker/php.conf.ini into the container so that the PHP picks up our additional php.ini configuration settings:

docker/php.conf.ini:

upload_max_filesize = 10M post_max_size = 10M xdebug.overload_var_dump = 1 xdebug.remote_enable = 1 xdebug.remote_autostart = 0 xdebug.remote_connect_back =

Truncated by Planet PHP, read more at the original (another 5805 bytes)

カテゴリー: php

PHP 7.2.21 Released

php.net - 2019-08-01(木) 17:43:45
カテゴリー: php

ページ