フィードアグリゲーター

Alex Korban: Generating land-constrained geographical point grids with PostGIS

planet postgresql - 2019-10-17(木) 09:00:00
When I was in the market for an EV, one of the things I wondered about was how far I would be able to go outside the city before I had to charge it. Having a number for the range isn't enough to know offhand whether I'd be able to reach a particular destination. So I wanted to make the range more obvious in my EV guide by visualising vehicle range on a map. The trivial solution is to use range as a radius and show a circle: Distance circle But of course, that's going to be massively inaccur...
カテゴリー: postgresql

Regina Obe: PostGIS 3.0.0 coming soon - Try 3.0.0rc2 at a package repo near you

planet postgresql - 2019-10-16(水) 08:15:00

PostGIS 3.0.0 is planned for release early next week. In the meantime you will find PostGIS 3.0.0rc1 or rc2 available via yum.postgresql.org, apt.postgresql.org, and EDB Windows 64-bit stackbuilder for PostgreSQL 12.

Continue reading "PostGIS 3.0.0 coming soon - Try 3.0.0rc2 at a package repo near you"
カテゴリー: postgresql

Vasilis Ventirozos: Tuning checkpoints

planet postgresql - 2019-10-16(水) 06:52:00
We recently had the chance to help a customer with some IO related issues that ended up being unconfigured checkpoints. Something that may not always be obvious but can actually be somewhat common.

Let's start with how things roughly work.
Postgres smallest IO unit is a disk block that is 8kb (by default). Each time postgres needs a new block it will fetch it from the disks and load it to an area in RAM called shared_buffers.
When postgres needs to write, it does it in the same manner:
  • Fetches the block(s) from the disks if the block is not in shared_buffers
  • Changes the page in shared buffers.
  • Marks the page as changed (dirty) in shared buffers.
  • It writes the change in  a "sequential ledger of changes" called WAL to ensure durability.

This basically means that the writes are not yet "on disk". This operation is taken care of by a postgres process called checkpointer. Checkpoints are how postgres guarantees that data files and index files will be updated with all the changes that happened before that checkpoint. In case of a crash, postgres will go back to the latest checkpoint record and it will start a REDO operation from WAL. Checkpoints are triggered every checkpoint_timeout (default at 5min) or when changes reach max_wal_size (default at 1GB). This is an IO intensive operation and postgres tries to spread this IO
with checkpoint_completion_target (default at 0.5).

checkpoint_timeout* is the maximum time between checkpoints in seconds.
min_wal_size minimum size of wals that will be recycled rather than removed
max_wal_size** maximum size allowed for wals between checkpoints
checkpoint_completion_target allows data changes to spread over a longer period of time, making the  final fsync() much cheaper.
* Affects recovery time, change only after reviewing the documentation
** This is a softmax, it can exceed this value in special cases.

Best way to start is to set checkpoint_timeout value to something reasonable and set max_wal_size high enough so you won't reach the timeout. To make sens[...]
カテゴリー: postgresql

Hans-Juergen Schoenig: Prewarming PostgreSQL I/O caches

planet postgresql - 2019-10-15(火) 17:00:04

PostgreSQL uses shared_buffers to cache blocks in memory. The idea is to reduce
disk I/O and to speed up the database in the most efficient way
possible. During normal operations your database cache will be pretty useful and
ensure good response times. However, what happens if your database instance is
restarted – for whatever reason? Your PostgreSQL database performance will suffer
until your I/O caches have filled up again. This takes some time and it can
be pretty damaging to your query response times.

pg_prewarm: Filling up your database cache

Fortunately, there are ways in PostgreSQL to fix the problem. pg_prewarm is a
module which allows you to automatically prewarm your caches after a database
failure or a simple restart. The pg_prewarm module is part of the PostgreSQL
contrib package and is usually available on your server by default.
There is no need to install additional third party software. PostgreSQL has all
you need by default.

Warming caches manually or automatically.

Basically, pg_prewarm can be used in two ways:

  • Manual caching
  • Automatic caching on startup

Let us take a look at both options and see how the module works in detail. In general automatic pre-warming is, in my judgement, the better way to preload caches – but in some cases, it can also make sense to just warm caches manually (usually for testing purposes).

pg_prewarm: Putting data into shared_buffers manually

Prewarming the cache manually is pretty simple. The following section explains how the process works in general.

The first thing to do is to enable the pg_prewarm extension in your database:

test=# CREATE EXTENSION pg_prewarm; CREATE EXTENSION

To show how a table can be preloaded, I will first create a table and put it into
the cache:

test=# CREATE TABLE t_test AS SELECT * FROM generate_series(1, 1000000) AS id; SELECT 1000000 test=# SELECT * FROM pg_prewarm('public.t_test'); pg_prewarm ------------ 4425 (1 row)

All you have to do is to call the

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

Federico Campoli: Regenerated

planet postgresql - 2019-10-15(火) 09:00:00

With PostgreSQL 12 the generated columns are now supported natively. Until the version Postgresql 11 it were possible to have generated columns using a trigger.

In this post we’ll see how to configure a generated column via trigger and natively then we’ll compare the performances of both strategies.

カテゴリー: postgresql

Euler Taveira de Oliveira: Postgres Object ownership

planet postgresql - 2019-10-15(火) 07:26:00
Sometimes I have to fix some object ownership such as tables and views. Let's figure out if there is such object in your database:

--
-- list tables, views, foreign tables and sequences not owned by role postgres
--
SELECT n.nspname AS SCHEMA,
c.relname AS relation,
pg_get_userbyid(c.relowner) AS ROLE,
'ALTER TABLE ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' OWNER TO postgres;' AS command
FROM pg_class c
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname !~ '^pg_'
AND nspname <> 'information_schema'
AND relkind = 'r'
AND pg_get_userbyid(c.relowner) <> 'postgres'
UNION ALL
SELECT n.nspname AS SCHEMA,
c.relname AS relation,
pg_get_userbyid(c.relowner) AS ROLE,
'ALTER VIEW ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' OWNER TO postgres;' AS command
FROM pg_class c
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname !~ '^pg_'
AND nspname <> 'information_schema'
AND relkind = 'v'
AND pg_get_userbyid(c.relowner) <> 'postgres'
UNION ALL
SELECT n.nspname AS SCHEMA,
c.relname AS relation,
pg_get_userbyid(c.relowner) AS ROLE,
'ALTER FOREIGN TABLE ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' OWNER TO postgres;' AS command
FROM pg_class c
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname !~ '^pg_'
AND nspname <> 'information_schema'
AND relkind = 'f'
AND pg_get_userbyid(c.relowner) <> 'postgres'
UNION ALL
SELECT n.nspname AS SCHEMA,
c.relname AS relation,
pg_get_userbyid(c.relowner) AS ROLE,
'ALTER SEQUENCE ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' OWNER TO postgres;' AS command
FROM pg_class c
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname !~ '^pg_'
AND nspname <> 'information_schema'
AND relkind = 'S'
AND pg_get_userbyid(c.relowner) <> 'postgres';
This UNION ALL query list tables, views, foreign tables and sequences whose owner is not role postgres. They should be candidates for a new owner (mainly because you are adjus[...]
カテゴリー: postgresql

symfony Project Blog: New in Symfony 4.4: Improved Type Constraint

phpdeveloper.org - 2019-10-15(火) 04:00:02
Contributed by Jan Schädlich in #31351.

The Type constraint included in the Validator component validates that a given value is of a specific data type. This type can be any of the valid PHP t...

カテゴリー: php

Robert Haas: Braces Are Too Expensive

planet postgresql - 2019-10-15(火) 03:39:00
PostgreSQL has what's sometimes called a Volcano-style executor, after a system called Volcano, about which Goetz Greafe published several very interesting papers in the early to mid 1990s. PostgreSQL was in its infancy in those days, but many of the concepts in the Volcano papers have made their way into PostgreSQL over the years. It may also be that Volcano took inspiration from PostgreSQL or its predecessors; I'm not entirely sure of the history or who took inspiration from whom. In any case, the Volcano execution model has been thoroughly embedded in PostgreSQL for the entire history of the database system; the first chinks in the armor only started to appear in 2017. Read more »
カテゴリー: postgresql

Laravel News: Send Notifications for Exceptions with the Notifiable Exception Package

phpdeveloper.org - 2019-10-15(火) 01:00:02
Notifiable Exception is a Laravel package by Andrea Marco Sartori to send notifications for certain exceptions. The post Send Notifications for Exceptions with the Notifiable Exception Package appeared first on Laravel News. Join ...
カテゴリー: php

murze.be: ★ The mixin PHP DocBlock

phpdeveloper.org - 2019-10-14(月) 23:30:02

When using PHP, you've probably used DocBlocks. They can be used to add additional information that can't be inferred by looking at the source code alone. DocBlocks can be used by IDEs, like PhpStorm, to improve autocomplete suggestions. In this blogpost, I'd like to highlight a not so well known D...

カテゴリー: php

Community News: Latest PEAR Releases (10.14.2019)

phpdeveloper.org - 2019-10-14(月) 22:05:02

Latest PEAR Releases:

カテゴリー: php

Álvaro Herrera: PostgreSQL 12: Foreign Keys and Partitioned Tables

planet postgresql - 2019-10-14(月) 16:55:02
Now that PostgreSQL 12 is out, we consider foreign keys to be fully compatible with partitioned tables. You can have a partitioned table on either side of a foreign key constraint, and everything will work correctly. Why do I point this out? Two reasons: first, when partitioned tables were first introduced in PostgreSQL 10, they […]
カテゴリー: postgresql

Dimitri Fontaine: Compute database size

planet postgresql - 2019-10-14(月) 07:05:00
Photo by unsplash-logoCharles 🇵🇭 It is well known that database design should be as simple as possible, and follow the normalization process. Except in some cases, sometimes, for scalability purposes. Partitioning might be used to help deal with large amount of data for instance. But what is a large amount of data? Do you need to pay attention to those scalability trade-offs now, or can you wait until later?
カテゴリー: postgresql

Daniel Vérité: Nondeterministic collations

planet postgresql - 2019-10-14(月) 07:00:00

Since version 12, PostgreSQL collations are created with a parameter named deterministic, that can be true or false, so that collations are now either deterministic (which they are by default), or nondeterministic.

What does that mean? This term refers to what Unicode calls deterministic comparisons between strings:

This is a comparison where strings that do not have identical binary contents (optionally, after some process of normalization) will compare as unequal

So before version 12, comparisons for collatable types in Postgres are always deterministic according to the above definition. Specifically, when the underlying collation provider (libc or ICU) reports that two strings are equal, a tie-breaker bytewise comparison is performed, so that it’s only when the strings consist of identical binary contents that they are truly equal for Postgres.

Starting with version 12, the new “deterministic” property can be set to false at CREATE COLLATION time to request that string comparisons skip the tie-breaker, so that the memory representations being different is not an obstacle to recognize strings as equal when the underlying locale says they are. This does not only affect direct comparisons or lookups through WHERE clauses, but also the results of GROUP BY, ORDER BY, DISTINCT, PARTITION BY, unique constraints, and everything implying the equality operator.

So what can be achieved with nondeterministic collations?

The most obvious features are case-insensitive and accent-insensitive matching implemented with COLLATE clauses, as opposed to calling explicit functions to do case-mapping (upper, lower) and removal of accents (unaccent). Now that these are accessible through the collation service, the traditional recommendation to use the citext datatype for case-insensitive lookups may start to be reconsidered.

Beyond that, nondeterministic collations allow to match strings that are canonically equivalent (differing only by which Unicode normal form they use), or differ only by com

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

Regina Obe: PostGIS 3.0.0rc2

planet postgresql - 2019-10-13(日) 09:00:00

The PostGIS development team is pleased to release PostGIS 3.0.0rc2. This will be the final RC before release.

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

Best served with PostgreSQL 12 , GEOS 3.8.0 and pgRouting 3.0.0-alpha.

Continue Reading by clicking title hyperlink ..
カテゴリー: postgresql

NETTUTS: Automatically Create a Multilingual WordPress Site With a Translator Plugin

phpdeveloper.org - 2019-10-12(土) 04:30:02

A multilingual WordPress site is one that offers content in more than one language. For example, a site can provide content in English, French, German, and Spanish. Since most countries have more than one official language, it makes sense to have content in multiple languages. Having a multilingual ...

カテゴリー: php

Delicious Brains: How Laravel Valet Works Exactly

phpdeveloper.org - 2019-10-12(土) 01:00:02

Developers tend to love messing about with their local development environments to see what fits their project requirements and workflow best. Since its launch in 2016, Laravel Valet has quickly become a popular choice and it has become my go-to environment when developing locally on my Mac because ...

カテゴリー: php

symfony Project Blog: New in Symfony 4.4: New DomCrawler Methods

phpdeveloper.org - 2019-10-12(土) 00:00:03
Contributed by Grégoire Pineau in #33144.

The DomCrawler component is mostly used in Symfony applications via functional tests, to filter the DOM nodes of HTML/XML documents. The methods provi...

カテゴリー: php

Avinash Kumar: How to Set Up Streaming Replication in PostgreSQL 12

planet postgresql - 2019-10-11(金) 23:21:15

PostgreSQL 12 can be considered revolutionary considering the performance boost we observe with partitioning enhancements, planner improvements, several SQL features, Indexing improvements, etc. You may see some of such features discussed in future blog posts. But, let me start this blog with something interesting. You might have already seen some news that there is no

recovery.filein standby anymore and that the replication setup (streaming replication) has slightly changed in PostgreSQL 12. We have earlier blogged about the steps involved in setting up a simple Streaming Replication until PostgreSQL 11 and also about using replication slots for the same. Let’s see how different is it to set up the same Streaming Replication in PostgreSQL 12. Installing PostgreSQL 12 on Master and Standby

On CentOS/RedHat, you may use the rpms available in the PGDG repo (the following link may change depending on your OS release).

# as root: yum install -y https://yum.postgresql.org/12/redhat/rhel-7.4-x86_64/pgdg-redhat-repo-latest.noarch.rpm -y yum install -y postgresql12-server Steps to set up Streaming Replication in PostgreSQL 12

In the following steps, the Master server is: 192.168.0.108 and the Standby server is: 192.168.0.107

Step 1 :
Initialize and start PostgreSQL, if not done already on the Master.

## Preparing the environment $ sudo su - postgres $ echo "export PATH=/usr/pgsql-12/bin:$PATH PAGER=less" >> ~/.pgsql_profile $ source ~/.pgsql_profile ## As root, initialize and start PostgreSQL 12 on the Master $ /usr/pgsql-12/bin/postgresql-12-setup initdb $ systemctl start postgresql-12

 

Step 2 :
Modify the parameter

listen_addressesto allow a specific IP interface or all (using *). Modifying this parameter requires a restart of the PostgreSQL instance to get the change into effect. # as postgres $ psql -c "ALTER SYSTEM SET listen_addresses TO '*'"; ALTER SYSTEM # as root, restart the service $ systemctl restart postgresql-12

You may not have to set any other parameters on the Master

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

murze.be: ★ The value of the void typehint in PHP

phpdeveloper.org - 2019-10-11(金) 23:00:02

When the void typehint was introduced in PHP 7.1. There was some debate about it. Some people wondered if it is beneficial to type nothing? I was one of them. Meanwhile, I changed my opinion on it. In this short post, I'd like to give you a small example where I think void shines.

This is the exa...

カテゴリー: php

ページ