フィードアグリゲーター

Interview with Jeremy Mikola

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

506 Variant Also Negotiates

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

In 1998 RFC2295 was published. It’s experimental, and meant to introduce a new way to do content negotiation in HTTP. As far as I personally know, I don’t think it got a lot of traction.

Traditionally, when a HTTP client wants to do content-negotation, they will send one or more accept headers:

GET / HTTP/1.1 Accept: text/html; image/png; text/*; q=0.9 Accept-Language: en-CA; en Accept-Charset: UTF-8 Accept-Encoding: gzip, brotli

RFC2295 intended to introduce a new way to do this, with a lot more flexibility and features. The RFC talks about selecting specific variants not just based on mimetype, but also html features a browser supports, color capabilities, screen resolution, speed preference, paper size for printers and even selecting content for specific devices like VR goggles and PDA’s.

An interesting feature is that it can also return a list of urls for specific variations, changing the HTTP model a bit by giving every representation and variant their own url, and returning all this in with a 300 Multiple Choices response.

An example of such a response (from the RFC):

HTTP/1.1 300 Multiple Choices Date: Tue, 11 Jun 1996 20:02:21 GMT TCN: list Alternates: {"paper.1" 0.9 {type text/html} {language en}}, {"paper.2" 0.7 {type text/html} {language fr}}, {"paper.3" 1.0 {type application/postscript} {language en}} Vary: negotiate, accept, accept-language ETag: "blah;1234" Cache-control: max-age=86400 Content-Type: text/html Content-Length: 227 <h2>Multiple Choices:</h2> <ul> <li><a href=paper.1>html, English version</a> <li><a href=paper.2>html, French version</a> <li><a href=paper.3>Postscript, English version</a> </ul>

The RFC introduces a new error code: 506 Variant Also Negotiates. To the best of my understanding, this error returned when a server is misconfigured and a ‘negotiating resource’ is pointing to another resource that doesn’t serve a representation, but instead also tries to negotiate.

I can imagine that a negotiating resource could for example point to itself, or sets up something like a redirection look. I think 506 is a specific error that a server could return for this case.

Should you use this?

I often include a section that answers whether you should use this status code. In this case, I think it’s better to dive into whether you should support the negotiation feature.

The issue with the feature is that it never left the experimental phase, and as far as I know got very little adoption. It was defined before HTTP/1.1 was finalized, and for all intents and purposes I think it can be considered dead.

However, it solves a couple of really interesting problems that aren’t solved

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

カテゴリー: php

Hans-Juergen Schoenig: PostgreSQL: Trivial timeseries examples

planet postgresql - 2019-08-13(火) 18:22:56

Timeseries are an increasingly important topic – not just in PostgreSQL. Recently I gave a presentation @AGIT in Salzburg about timeseries and I demonstrated some super simple examples. The presentation was well received so I decided to share this stuff in the form of a blog PostgreSQL, so that more people can learn about windowing functions and SQL in general. A link to the video is available at the end of the post so that you can listen to the original material in German.

Loading timeseries data the easy way

To show how data can be loaded, I compiled a simple dataset, which can be found on my website. Here is how it works:

test=# CREATE TABLE t_oil ( region text, country text, year int, production int, consumption int ); CREATE TABLE test=# COPY t_oil FROM PROGRAM 'curl https://www.cybertec-postgresql.com/secret/oil_ext.txt'; COPY 644

The cool thing is that if you happen to be a superuser you can easily load the data from the web directly. COPY FROM PROGRAM allows you to execute code on the server and pipe it directly to PostgreSQL, which is super simple. But keep in mind: This only works if you are a PostgreSQL superuser (for security reasons).

lag: The backbone of timeseries analysis

If you are dealing with timeseries calculating the difference to the previous period is really important and is needed in many cases. Fortunately SQL allows you to do that pretty easily. Here is how it works:

test=# SELECT year, production, lag(production, 1) OVER (ORDER BY year) FROM t_oil WHERE country = 'USA' LIMIT 5; year | production | lag ------+------------+------- 1965 | 9014 | 1966 | 9579 | 9014 1967 | 10219 | 9579 1968 | 10600 | 10219 1969 | 10828 | 10600 (5 rows)

The lag functions takes two parameters: The first column defines the column, which should be used in this case. The second parameter is optional. I

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

11.5, 10.10, 9.6.15, 9.5.19, 9.4.24 リリース (2019-08-08)

www.postgresql.jp news - 2019-08-13(火) 13:15:30
11.5, 10.10, 9.6.15, 9.5.19, 9.4.24 リリース (2019-08-08) harukat 2019/08/13 (火) - 13:15
カテゴリー: postgresql

PHP Conference Brasil 2019

php.net - 2019-08-12(月) 17:21:22
カテゴリー: php

Regina Obe: PostGIS 3.0.0alpha4, 2.5.3, 2.4.8, 2.3.10 Released

planet postgresql - 2019-08-11(日) 09:00:00

The PostGIS development team is pleased to provide enhancements/features 3.0.0alpha4 for 3.0 feature major branch bug fix 2.5.3, 2.4.8, and 2.3.10 for the 2.5, 2.4, and 2.3 stable branches.

3.0.0alpha4 This release works with PostgreSQL 9.5-12beta3 and GEOS >= 3.6

Best served with PostgreSQL 12beta3. Designed to take advantage of features in PostgreSQL 12 and Proj 6

2.5.3 This release supports PostgreSQL 9.3-12 You are encouraged to use the PostGIS 3.0 unreleased branch with PostgreSQL 12 , which has features specifically designed to take advantage of features new in PostgreSQL 12.

2.4.8 This release supports PostgreSQL 9.3-10.

2.3.10

This release supports PostgreSQL 9.2-10.

View all closed tickets for 3.0.0, 2.5.3, 2.4.8, 2.3.10.

After installing the binaries or after running pg_upgrade, make sure to do:

ALTER EXTENSION postgis UPDATE;

— if you use the other extensions packaged with postgis — make sure to upgrade those as well

ALTER EXTENSION postgis_sfcgal UPDATE; ALTER EXTENSION postgis_topology UPDATE; ALTER EXTENSION postgis_tiger_geocoder UPDATE;

If you use legacy.sql or legacy_minimal.sql, make sure to rerun the version packaged with these releases.

カテゴリー: postgresql

Dimitri Fontaine: The Art Of PostgreSQL

planet postgresql - 2019-08-10(土) 18:15:00

I did it again! Today I am releasing the new edition of my book, with a new title: “The Art of PostgreSQL”. I’m very happy (and quite excited) to declare my book as Generally Available!

The Art of PostgreSQL is the new edition of my previous release, Mastering PostgreSQL in Application Development. It contains mostly fixes to the old content, a new title, and a new book design (PDF and paperback). Content wise, The Art of PostgreSQL also comes with a new whole chapter about PostgreSQL Extensions.

The new chapter covers extensions such as hstore, pg_trgm, intarray, earthdistance, ip4r, and hll or HyperLogLog, one of the all times favorite extensions of Craig Kerstiens… who made himself available to answer my questions and share his view of PostgreSQL Extensions in an interview!

カテゴリー: postgresql

Asif Rehman: An Overview of Replication in PostgreSQL Context

planet postgresql - 2019-08-10(土) 00:33:04

Replication is a critical part of any database system that aims to provide high availability (HA) and effective disaster recovery (DR) strategy. This blog is aimed at establishing the role of replication in a database system. The blog will give a general overview of replication and its types as well as an introduction to replication options in PostgreSQL.

The term Replication is used to describe the process of sharing information between one or more software or hardware systems; to ensure reliability, availability, and fault-tolerance. These systems can be located in the same vicinity, could be on a single machine or perhaps connected over a wide network. The replication can be divided broadly into Hardware and Software categories. We’ll explore these categories briefly, however, the main focus of this blog is database replication. So, let’s first understand what constitutes a database replication system.

In a nutshell, the database replication describes the process of coping data from the database instance to one or more database instances. Again, these instances can be in the same location or perhaps connected over a wide network.

Hardware-Based Replication

Let’s start with the hardware replication. Hardware-based replication keeps the multiple connected systems in sync. This syncing of data is done at the storage level as soon as an I/O is performed on the system, it’s propagated to the configured storage modules/devices/systems. This type of replication can be done for the entire storage or the selected partitions. The biggest advantage of this type of solution is that (generally) it’s easier to set up and is independent of software. This makes it perform much better, however, it reduces the flexibility and control over the replication. Here are the few pros of this type of replication.

Real-time – all the changes are applied to subsequent systems immediately.
Easier to Setup – no scripting or software configurations are required.
Independent of Application – replication happens at

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

ahsan hadi: Horizontal scalability with Sharding in PostgreSQL – Where it is going Part 3 of 3.

planet postgresql - 2019-08-08(木) 23:38:22

Built-in Sharding Architecture

The build-in sharding feature in PostgreSQL is using the FDW based approach, the FDW’s are based on sql/med specification that defines how an external data source can be accessed from the PostgreSQL server. PostgreSQL provides number of foreign data wrapper (FDW’s) that are used for accessing external data sources, the postgres_fdw is used for accessing Postgres database running on external server, MySQL_fdw is used for accessing MySQL database from PG, MongoDB_fdw is used for accessing MongoDB and so on.

The diagram below explains the current approach of built-in Sharding in PostgreSQL, the partitions are created on foreign servers and PostgreSQL FDW is used for accessing the foreign servers and using the partition pruning logic the planner decides which partition to access and which partitions to exclude from the search.

Push Down Capabilities

Push down in this context is the ability to push parts of the foreign query to foreign servers in order to decrease the amount of data travelling from the foreign server to parent node. The two basic push-down techniques that have been part of postgres fdw from the start are select target-list pushdown and WHERE clause pushdown.

In the query above the planner will decide which partition to access based on the partition key i.e. logdate in this case. The WHERE clause will be pushed down to the foreign server that contains the respective partition. That’s the basic push down capabilities available in postgres_fdw.

The sharding feature requires more advanced push-down capabilities in order to push the maximum operations down to the foreign servers containing partitions and minimising the data sent over the wire to the parent node.

The above is a decent set of push down capabilities that have been added to PostgreSQL in last few major releases. The good thing about these features is that it already benefits a number of use cases even when the entire sharding feature is not in place.

What’s remain

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

ahsan hadi: Horizontal scalability with Sharding in PostgreSQL – Where it is going Part 2 of 3.

planet postgresql - 2019-08-08(木) 23:29:09

Declarative Partitioning

So far we have discussed scalability, what is scalability, why and when you need and what are the different types of scalability. Now we are starting to get into the meat of this topic and will discuss declarative partitioning and sharding in PostgreSQL. The sharding functionality is being laid on top of declarative partitioning functionality in PostgreSQL.

Declarative partitioning was released in PostgreSQL 10, prior to declarative partitioning PostgreSQL was using table inheritance and plpgsql triggers for providing table partitioning. The example below shows how a table can be partitioned using the declarative partitioning syntax introduced in PG 10 :

Declarative partitioning basically provides the native support for partitioning in PostgresSQL, using the syntax used in the example above, the user can create partitioned tables. This would divide a table into pieces called partitions, the pieces are called partitioned tables. All rows inserted into a partitioned table will be routed to one of the partition based on the partition key.

Lot of performance improvement for declarative partitioning was added in PostgreSQL 11 that improved the code for partition pruning, partition pruning is the ability of eliminating certain partitions from the search based on the quals provided in the WHERE predicate.

Sharding in PostgreSQL

Sharding is the ability to partition a table across one or more foreign servers, with declarative partitioning as show above the table can partitioned into multiple partitioned tables living on the same database server. Sharding allows the table to be partitioned in a way that the partitions live on external foreign servers and the parent table lives on the primary node where the user is creating the sharded table. All the foreign servers that are being used in the sharded tables are PostgreSQL foreign servers, other foreign server i.e. MongoDB, MySQL etc are not supported.  

The example below shows how a sharded table can be creat

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

ahsan hadi: Horizontal scalability with Sharding in PostgreSQL – Where it is going Part 1 of 3

planet postgresql - 2019-08-08(木) 23:18:17

I recently had the opportunity of taking part in couple of interesting talks on the future of sharding in PostgreSQL. The first talk was delivered by Bruce Momjain in PostgreSQL conf Ottawa (May 2019) in which he presented the future of Sharding in PostgreSQL and talked about current state and future of built-in sharding in PostgreSQL. The second talk was presented by myself in PostgreSQL conference in Beijing (China – July 2019) in which i also talked about the current state and future of built-in sharding in PG. I essentially used the slides from Bruce’s presentation (with his permission of-course) and added few more slides discussing the requirements that warrant scalability and also talked about other alternatives for global transaction manager. 

During the PostgreSQL conference in Ottawa, I continued with the tradition of arranging a breakfast meeting during the conference week to discuss the sharding topic. We have been doing this every year for last several years when i was working with EnterpriseDB and continued the meeting now that i am working with HighGo Software Inc. The breakfast meeting this year was attended by folks from EDB, NTT, HighGO and Pivotal, the number of participants this year were more then what we have been getting for last years so we had to opt for a bigger breakfast table. The purpose of this meeting to get folks from companies that are working on this feature in the community and this time also folks from companies that intend to contribute to this feature in the community. The agenda is to discuss the current state of built-in sharding, ongoing work, what’s coming down the pipe. The other topics discussed in this meeting is to discuss the overall goal for the sharding feature, what use-cases we expect to benefit from this features, what are the rough time-scales for getting something in PG that we can called sharding.

About this Blog

I was initially hoping to cover this topic in one blog but since this feature is so huge the blog also turned out to be much longer t

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

Alexander Hagerman: Postgres Advisory Locks with asyncio

planet postgresql - 2019-08-08(木) 22:47:00
Testing Postgres advisory locks with asyncio and asyncpg.

Recently, here on the Cloud team at Elastic we started working on building a new service in Python 3.7. This service fetches data from a Postgres database, transforms it, and then submits that data to another service. Like many cloud-based services, ours runs in an orchestrated container environment where N instances can be running at any time. Often that's a good thing, but our service has a few critical sections where only one instance should be able to process data. Since we are retrieving data from Postgres, we decided to go ahead and make use of advisory locks to control these critical sections. In this article I want to explain what advisory locks are, provide an implementation, and test to verify functionality.

Advisory locks

Postgres provides the ability to create locks that only have meaning within the context of your application. These are advisory locks. You use advisory locks to control an application’s ability to process data. Anytime your application is about to enter a critical path, you attempt to acquire the lock. When you acquire the lock, you can safely continue processing.

async with AdvisoryLock(self.dbconfig, "gold_leader") as connection:

If it fails, then your application may retry, wait, or exit. Since this lock is external to the application, this allows for multiple instances of the application to run while providing safe critical path concurrency.

Building the lock

As part of our work, we wanted to make using advisory locks easy. To do this, we created the PostgresAdvisoryLock context manager. Since this is meant to be used with asyncio and asyncpg, we control the acquisition and release of the lock via __aenter__ and __aexit__.

class AdvisoryLock: async def __aenter__(self) -> asyncpg.connection.Connection: self.locked_connection = await asyncpg.connect(...) await self._set_lock() if self.got_lock: return self.locked_connection else: [...]
カテゴリー: postgresql

PHP Conference Japan 2019 CFP Started

php.net - 2019-08-08(木) 20:33:52
カテゴリー: php

PHP Conference Japan 2019

php.net - 2019-08-08(木) 19:29:31
カテゴリー: php

PHP 7.4.0beta2 released!

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

Shawn Wang: The difference in five modes in the AES encryption algorithm

planet postgresql - 2019-08-08(木) 16:52:49

Recently, I did some work with Sawada-san on the TDE. So I studied on the encryption algorithm. So far, I study five modes in the AES. In this document, I will introduce the difference in the five kinds of mode.

General

The block ciphers are schemes for encryption or decryption where a block of plaintext is treated as a single block and is used to obtain a block of ciphertext with the same size. Today, AES (Advanced Encryption Standard) is one of the most used algorithms for block encryption. It has been standardized by the NIST (National Institute of Standards and Technology) in 2001, in order to replace DES and 3DES which were used for encryption in that period. The size of an AES block is 128 bits, whereas the size of the encryption key can be 128, 192 or 256 bits. Please note this, there is three length in the key, but the size of the encryption block always is 128 bits. Block cipher algorithms should enable encryption of the plaintext with size which is different from the defined size of one block as well. We can use some algorithms for padding block when the plaintext is not enough a block, like PKCS5 or PKCS7, it also can defend against PA attack, if we use ECB or CBC mode. Or we can use the mode of AES which support a stream of plaintext, like CFB, OFB, CTR mode.

Now let’s introduce the five modes of AES.

  • ECB mode: Electronic Code Book mode
  • CBC mode: Cipher Block Chaining mode
  • CFB mode: Cipher FeedBack mode
  • OFB mode: Output FeedBack mode
  • CTR mode: Counter mode

The attack mode:

  • PA: Padding attack
  • CPA: Chosen Plaintext Attack
  • CCA: Chosen Ci
ECB Mode

The ECB (Electronic Code Book) mode is the simplest of all. Due to obvious weaknesses, it is generally not recommended. A block scheme of this mode is presented in Fig. 1.


We can see it in Fig. 1, the plaintext is divided into blocks as the length of the block of AES, 128. So the ECB mode needs to pad data until it is same as the length of the block. Then every block

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

11.5

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

10.10

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

ページ