フィードアグリゲーター

Tatsuo Ishii: Shared Relation Cache

planet postgresql - 2019-03-24(日) 17:58:00
 System catalogs? Pgpool-II needs to access PostgreSQL's system catalogs whenever it recognizes tables in user's query.  For example, Pgpool-II has to know whether the table in question is a temporary table or not. If it's a temporary table, then the query using the temporary table must be routed to the primary PostgreSQL, rather than one of standby PostgreSQL servers because  PostgreSQL does not allow to create temporary tables on standby servers. Another use case is converting the table name to OID (Object Identifier). OIDs are unique keys for objects managed in PostgreSQL's system catalogs.

Same thing can be said to functions. Details of functions, for instance whether they are "immutable" or not, is important information since it affects the decision on which the query result using the function should be cached or not when query cache feature is enabled.
Local query cache for system catalogs Sometimes Pgpool-II needs to issue up to as many as 10 queries to the system catalog when it sees a table or function for the first time. Fortunately Pgpool-II does not wast the query results. They are stored in local cache (wee call it "Relation Cache" or "Relcache"), and next time it sees the object in the same or different queries, it extracts info from the local cache . So far so good.

Problem is, the local cache is stored in private memory in Pgpool-II' s child process. For each new session from Pgpool-II clients, different child process is assigned for the session. So even if single table is used in queries, Pgpool-II continues to access system catalogs until the table's info gets filled  in all the local caches.
Shared relation cache How to mitigate the problem? One of the solutions would be sharing the relation cache info among Pgpool-II processes. This way,  once one of the processes accesses the system catalogs and obtains the info, other processes do not need to access the system catalogs any more. The cache shared by the processes is called "shared relation cache".
How to implement it? But how to implement i[...]
カテゴリー: postgresql

Mid-Atlantic Developer Conference

php.net - 2019-03-24(日) 08:55:18
カテゴリー: php

Peter Geoghegan: Visualizing Postgres page images within GDB

planet postgresql - 2019-03-23(土) 09:07:00
It's straightforward to set up GDB to quickly invoke pg_hexedit on a page image, without going through the filesystem. The page image can even come from a local temp buffer.
A user-defined GDB command can be created which shows an arbitrary page image in pg_hexedit from an interactive GDB session.

This is a good way to understand what's really going on when debugging access method code. It also works well with core dumps. I found this valuable during a recent project to improve the Postgres B-Tree code.

An example of how to make this work is available from a newly added section of the pg_hexedit README file:

https://github.com/petergeoghegan/pg_hexedit/#using-pg_hexedit-while-debugging-postgres-with-gdb
カテゴリー: postgresql

Viorel Tabara: Benchmarking Managed PostgreSQL Cloud Solutions - Part Two: Amazon RDS

planet postgresql - 2019-03-22(金) 19:47:19

This is the second part of the multi-series Benchmarking Managed PostgreSQL Cloud Solutions. In Part 1 I presented an overview of the available tools, I discussed the reason for using the AWS Benchmark Procedure for Aurora, as well as PostgreSQL versions to be used, and I reviewed Amazon Aurora PostgreSQL 10.6.

In this part, pgbench and sysbench will be running against Amazon RDS for PostgreSQL 11.1. At the time of this writing the latest PostgreSQL version is 11.2 released about a month ago.

Related resources  ClusterControl for PostgreSQL  Benchmarking Managed PostgreSQL Cloud Solutions - Part One: Amazon Aurora  Cloud Backup Options for PostgreSQL

It’s worth pausing for a second to quickly review the PostgreSQL versions currently available in the cloud:

Amazon is again a winner, with its RDS offering, by providing the most recent version of PostgreSQL. As announced in the RDS forum AWS made PostgreSQL 11.1 available on March 13th, which is four months after the community release.

Setting Up the Environment

A few notes about the constraints related to setting up the environment and running the benchmark, points that were discussed in more detail during Part 1 of this series:

  • No changes to the cloud provider default GUC settings.
  • The connections are limited to a maximum of 1,000 as the AWS patch for pgbench did not apply cleanly. On a related note, I had to download the AWS timing patch from this pgsql-hackers submission since it was no longer available at the link mentioned in the guide.
  • The Enhanced Networking must be enabled for the client instance.
  • The database does not include a replica.
  • The database storage is not encrypted.
  • Both the client and the target instances are in the same availability zone.

First, setup the client and the database instances:

  • The client is an on demand r4.8xlarge EC2 instance:
    • vCPU: 32 (16 Cores x 2 Threads/Core)
    • RAM: 244 GiB
    • Storage: EB
[...]
カテゴリー: postgresql

Pavel Stehule: How to split string to array by individual characters?

planet postgresql - 2019-03-22(金) 15:57:00
Postgres has too much features, so sometimes is good to remind some.

Function string_to_array is well known. This function has two or three parameters. If second parameter (delimiter) is null, then input string is separated to array of characters.

postgres=# select string_to_array('Pavel Stěhule',null);
┌───────────────────────────────┐
│ string_to_array │
╞═══════════════════════════════╡
│ {P,a,v,e,l," ",S,t,ě,h,u,l,e} │
└───────────────────────────────┘
(1 row)
カテゴリー: postgresql

Peter Bengtsson: Best way to count distinct indexed things in PostgreSQL

planet postgresql - 2019-03-22(金) 02:13:14
`SELECT COUNT(*) FROM (SELECT DISTINCT my_not_unique_indexed_column FROM my_table) t`
カテゴリー: postgresql

Small history about QA

planet PHP - 2019-03-22(金) 00:10:00

Despite I'm mainly a developer, I now use most of my time on doing QA on PHP projects.

Here is, around release of versions7.2.17RC1 and 7.3.4RC1 a report which should help to understand this activity.

 

1. Presentation

Usually, tests are done by PHP developers, particularly thanks to travis and then by users who will install the RC version available 2 weeks before a GA version.

The PHP project follow a release process (cf README.RELEASE_PROCESS) which gives 2 days between the preparation of a version, the Tuesday on git, and the Thursday its announcement in the mailing lists. These 2 days are especially designed to allow the build of binary packages (mostly by Microsoft and often by me for my repository) and to allow a last QA check which mays allow to discover some late issue.

When the new versions were available (on Tuesday afternoon) I start building the packages for my repostiory, givinf more coverage than the current travis configuration:

  • Fedora 27 to 31
  • RHEL 6, 7 and 8-Beta
  • i386 and x86_64
  • NTS and ZTS
  • various compiler versions  (GCC 4 to 9) and system library versions

I also run the build of the 7.3.4RC1 package in Fedora rawhide to trigger the re-build of all the PHP stack in Koschei, one of the CI tools of the Fedora project.

Notice : time to build all the packages for all the targets is about 3h for each version !  (I really need a faster builder).

 

2. Discoverd issues 2.1. Failed tests with pcre2 version 10.33RC1

Already available in rawhide, this version introduce a change in some error message, making 2 tests to fail.

Minor issue, fixed in PHP 7.3+: commit c421d9a.

2.2. Failed tests on 32-bit

In fix of bug #76117 the output of var_export have changed, make 2 tests to fail on 32-bit.

After confirmation by the autor of the change, tests have been fixed in PHP 7.2+ : commits a467a89 and 5c8d69b.

2.3. Regression

Koschei allow to discover very quickly a important regression in the run of the "make test" command. After digging, this regression was introduced in the fix of bug #77609, read the comments on the commit 3ead672.

After discussion between the Release managers, it have been choosen to:

  • revert this change to get back to a sane situation
  • to re-run the release process (new tag onr git)

The version which wil be announced shortly will not be affected byt this regression.

 

3. Conclusion

To ensure of the quality of PHP, of no regression is a complex, long and serious work. Thanks to all the actors, developers, QA team and users, this works pretty well.

So, if you use PHP in a development environment, it is essential to install the RC versions to detect and report us quickly any problem, so we can react before the finale version.

For users of my repository, the RC versions of PHP and various extensions are nearly always available in the testing repositories.

 

カテゴリー: php

Upgrading to PHP 7.1.27 – how to fix the GPG error: https://packages.sury.org error.

planet PHP - 2019-03-21(木) 18:22:00

If, like me, you’re still using PHP7.1, then you should upgrade to the most current security release which is 7.1.27 and was released on the 7th of March. Prior to that we were on 7.1.24 and missed out on fixed for a number of CVEs that were addressed in 7.1.26  and 7.1.25 – and fixes for a segmentation fault or two as well.

This morning, doing this on Debian Jessie yielded the error:

W: An error occurred during the signature verification. The repository is not updated and the previous index files will be used. GPG error: https://packages.sury.org jessie InRelease: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY B188E2B695BD4743

This is because Ondřej Surý – who maintains the packages there – had to change over to a new DPA signing key as it was present on a server which got compromised.

This means that to install packages from there since the new one was switched to, I needed to download the new key:

# wget -O /etc/apt/trusted.gpg.d/php.gpg href="https://packages.sury.org/php/apt.gpg

After doing that a quick apt-get update set things straight again and apt-get upgrade could then carry on and move things along to 7.1.27.

Realising then that we’d missed out on upgrading to 7.1.27 sooner, I’ve added an additional check to our own product’s status page to determine whether the version of PHP being used is the newest “on-branch” version. It looks something like:


/**
* Get the current/latest version released on branch x for PHP_VERSION.
*
* @param string $version Branch/Version to check for. e.g. 7.1 or 7.1.23...
*
* @return string
*/
function getLatestPHPReleaseOnBranch($version)
{
    $v = explode(".", $version);
    $branch = "{$v[0]}.{$v[1]}";
    $major = $v[0];
    if (extension_loaded('curl')) {
        $url = "https://secure.php.net/releases/active.php";
        $curl = curl_init($url);

        // Use browser's user agent string.
        $agent = $_SERVER['HTTP_USER_AGENT'];

        curl_setopt($curl, CURLOPT_USERAGENT, $agent);
        curl_setopt($curl, CURLOPT_FAILONERROR, true);
        curl_setopt($curl, CURLOPT_FOLLOWLOCATION, true);
        curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
        $json = curl_exec($curl);
        curl_close($curl);
        $decoded = json_decode($json, true);
        $version = $decoded[$major][$branch]['version'];
        return $version;
    } else {
        return null;
    }
}
$latestPHP = getLatestPHPReleaseOnBranch(PHP_VERSION);
if ($latestPHP !== null) {
    $current = PHP_MAJOR_VERSION . "." . PHP_MINOR_VERSION . "." . PHP_RELEASE_VERSION;
    $branch = PHP_MAJOR_VERSION . "." . PHP_MINOR_VERSION;
    echo "Using most recent on-branch PHP release? ", version_compare($current, $latestPHP, "==") ? "Yes" : "No", " (Latest release is $latestPHP Currently on ", PHP_VERSION , ")";
}

Please use this, or something similar, to determine when and if you need to update your PHP install.

カテゴリー: php

Yogesh Sharma: PostgreSQL Zero to Hero: Getting Started with RPMs -Part 1

planet postgresql - 2019-03-20(水) 23:33:38

One of the most important things to using PostgreSQL successfully in your development and production environments is simply getting started! One of the most popular ways to install PostgreSQL is by using RPM packages. The PostgreSQL RPM packages work across many Linux distributions, including, RedHat Enterprise Linux (RHEL), CentOS, Fedora, Scientific Linux, and more, and the PostgreSQL community provides installers for these distributions.

This guide will help you get started with installing and configuring PostgreSQL for a CentOS / RHEL 7 based system, which will also work for Fedora 29. We will be installing PostgreSQL 11, which is the latest major release of PostgreSQL as of this writing.

Installation Installing yum / dnf repository setup rpm
カテゴリー: postgresql

Craig Kerstiens: How to evaluate your database

planet postgresql - 2019-03-20(水) 22:47:00

Choosing a database isn’t something you do every day. You generally choose it once for a project, then don’t look back. If you experience years of success with your application you one day have to migrate to a new database, but that occurs years down the line. In choosing a database there are a few key things to consider. Here is your checklist, and spoiler alert, Postgres checks out strongly in each of these categories.

Does your database solve your problem?

There are a lot of new databases that rise up every year, each of these looks to solve hard problems within the data space. But, you should start by looking and seeing if they’re looking to solve a problem that you personally have. Most applications at the end of the day have some relational data model and more and more are also working with some level of unstructured data. Relational databases of course solve the relational piece, but they increasingly support the unstructured piece as well. Postgres in particular

Do you need strong gurantees for your data? ACID is still at the core of how durable and safe is your data, knowing how it stacks up here is a good evaluation criteria. But then there is also the CAP theorem which you see especially applied to distributed or clustered databases. Each of the previous links is worth a read to get a better understanding of the theory around databases. If you’re interested in how various databases perform under CAP then check out the Jepsen series of tests. But for the average person like myself it can be boiled down a bit more. Do you need full gurantee around your transactions, or do you optimize for some performance?

While it doesn’t fully speak to all the possible options you can have with databases, Postgres comes with some pretty good flexibility out of the box. It allows both synchronous (guaranteed it makes it) and asynchronous (queued up occurring soon after) replication to standbys. Those standbys could be for read replicas for reporting or for high availability. What’s nice about Postgres is can actually al

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

Christophe Pettus: “Look It Up: Practical PostgreSQL Indexing” at Nordic PGDay 2019

planet postgresql - 2019-03-20(水) 22:34:56

The slides from my presentation at PGDay Nordic 2019 are now available.

カテゴリー: postgresql

Hubert 'depesz' Lubaczewski: Migrating simple table to partitioned. How?

planet postgresql - 2019-03-20(水) 07:57:58
Recently someone asked, on irc, how to make table partitioned. The thing is that it was supposed to be done with new partitioning, and not the old way. The problem is that while we can create table that will be seen as partitioned – we can't alter table to become partitioned. So. Is it possible? … Continue reading "Migrating simple table to partitioned. How?"
カテゴリー: postgresql

Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 12 – Partial implementation of SQL/JSON path language

planet postgresql - 2019-03-20(水) 06:30:44
On 16th of March 2019, Alexander Korotkov committed patch: Partial implementation of SQL/JSON path language   SQL 2016 standards among other things contains set of SQL/JSON features for JSON processing inside of relational database. The core of SQL/JSON is JSON path language, allowing access parts of JSON documents and make computations over them. This commit … Continue reading "Waiting for PostgreSQL 12 – Partial implementation of SQL/JSON path language"
カテゴリー: postgresql

416 Range Not Satisfiable

planet PHP - 2019-03-20(水) 00:00:00

It’s possible for a client to request partial responses from a server. For example, a client might only want the first 5 minutes of a video, or the last 100 lines of a log file.

HTTP clients and servers can do this with range requests.

For example, this request asks for the first 100 bytes:

GET /foo.md HTTP/1.1 Range: bytes=1-100

If a server doesn’t support range requests, it will return 200 OK and returns the entire resource. If it did support range requests, it can use 206 Partial Content and return just what the client asked for.

However, if a client requested a range that didn’t make sense, it can use 416 Range Not Satisfiable to indicate this. For example, maybe a file was 1024 bytes, and the client asked for bytes 2000-3000.

HTTP/1.1 416 Range Not Satisfiable Content-Range: bytes */1000 References
カテゴリー: php

Hans-Juergen Schoenig: Foreign data wrapper for PostgreSQL: Performance Tuning

planet postgresql - 2019-03-19(火) 20:42:05

Foreign data wrappers have been around for quite a while and are one of the most widely used feature in PostgreSQL. People simply like foreign data wrappers and we can expect that the community will add even more features as we speak. As far as the postgres_fdw is concerned there are some hidden tuning options, which are not widely known by users. So let us see how we can speed up the PostgreSQL foreign data wrapper.

Foreign data wrappers: Creating a “database link”

To show how things can be improved we first have to create some sample data in “adb”, which can then be integrated into some other database:

adb=# CREATE TABLE t_local (id int); CREATE TABLE adb=# INSERT INTO t_local SELECT * FROM generate_series(1, 100000); INSERT 0 100000

In this case I have simply loaded 100.000 rows into a very simple table. Let us now create the foreign data wrapper (or “database link” as Oracle people would call it). The first thing to do is to enable the postgres_fdw extension in “bdb”.

bdb=# CREATE EXTENSION postgres_fdw; CREATE EXTENSION

In the next step we have to create the “SERVER”, which points to the database containing our sample table. CREATE SERVER works like this:

bdb=# CREATE SERVER some_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'adb'); CREATE SERVER

Once the foreign server is created the users we need can be mapped:

bdb=# CREATE USER MAPPING FOR current_user SERVER some_server OPTIONS (user 'hs'); CREATE USER MAPPING

In this example the user mapping is really easy. We simply want the current user to connect to the remote database as “hs” (which happens to be my superuser).

Finally we can link the tables. The easiest way to do that is to use “IMPORT FOREIGN SCHEMA”, which simply fetches the remote data structure and turns everything into a foreign table.

bdb=# \h IMPORT Command: IMPORT FOREIGN SCHEMA Description: import table definitions from a foreign server Syntax: IMPORT FOREIGN SCHEM[...]
カテゴリー: postgresql

Interview with Ian Littman

planet PHP - 2019-03-19(火) 20:30:00

@iansltx Show Notes
  • Longhorn PHP
  • Use the discount code elephpant to save $50 off your Longhorn PHP ticket!

Audio

This episode is sponsored by


The post Interview with Ian Littman appeared first on Voices of the ElePHPant.

カテゴリー: php

Style Guide for Object Design: Release of the PHP edition

planet PHP - 2019-03-19(火) 19:30:00
With a foreword by Ross Tuck

Today I've released the revised edition of my latest book "Style Guide for Object Design". Over the past few weeks I've fixed many issues, smaller and larger ones. Ross Tuck and Laura Cody have read the manuscript and provided me with excellent feedback, which has helped me a lot to get the book in a better shape. I added many sections, asides, and even an extra chapter, because some topics deserve more detail and background than just a few paragraphs. Oh, and Ross wrote the kindest of forewords to kick off the book. It's available in the free sample of the book.

The book will be available at the initial "preview release" price of 20 dollars (with a suggested price of 29 dollars) but only until April 15th. Use this link to apply the discount: https://leanpub.com/object-design/c/PREVIEW_RELEASE.

What happens on April 15th?

Over the past few weeks I've been talking with some fine people from Manning, publisher of tech books. This resulted in a contract with them. They will republish the "Style Guide for Object Design". There will be an e-book, but also a printed version, which is great.

The book currently has code samples in PHP and contains some PHP-specific suggestions, but the Manning edition will have Java code samples, and no programming language or ecosystem-specific advice, hopefully making the book useful and appealing to a larger group of object-oriented programmers. This is an exciting thing for me as a book author, because I may be able to reach more people in this way.

I know that some readers prefer to read the PHP version, so that's why the version as it is now will be available until April 15th. From that moment on, you won't be able to buy it anymore. However, if you've bought the e-book from Leanpub, you will be granted access to the Manning Early Access Program (MEAP) for the book, meaning that you will eventually be able to read the Manning/Java edition too. Once this is possible, I'll send out an email to existing readers, and update the Leanpub page with instructions for joining the program.

Changelog

If you already read (part of) the book, here's a summary of the most important changes, so you can decide if it'll be useful to download and read the new version. The full change log is in the back of the book and it includes linkes to the chapters and sections that have been updated.

  • Foreword

    • Added the foreword by Ross Tuck.
  • The lifecycle of an object

    • Rewrote the explanation about the two types of objects. What really defines these types is how they are related to each other. The first type uses the second type (services versus materials).
  • Creating services

    • Added a subsection: "Keeping together configuration values that belong together", which introduces a way to keep together configuration values that belong together.
    • Added an aside: "What if I need the service and the service I retrieve from it?", which answers a common question that was asked by the technical reviewer.
  • Creating other objects

    • Added a new section: "Don't use custom exception classes for invalid argument exceptions", explaining why you don't usually need custom exception classes for invalid argument exceptions.
    • Added an aside: "Adding more object types also leads to more typing, is that really necessary?", explaining some of the benefits of using object types instead of primitive types, just in case people are wondering if all that extra typing is really necessary.
    • Added another example to the section "Don't inject dependencies, optionally pass them as method arguments", explaining how you could rewrite the currency conversion logic using a simple services. Added a comment about the design trade-offs you have to make in this type of situation.
    • Added an aside about PHP's class-based scoping, explaining how it's possible that a named constructor can manipulate private properties directly.
    • Added a subsection "Optionally use the private constructor to enforce constraints" with an example showing how you can use the private constructor when you have multiple named constructors.
    • Finish the chapter with a new section: "The exception to the rule: Data transfer objects" about Data transfer objects, a type of object with less strict rules, which was not yet discussed in detail.
  • Manipulating objects

    • Added a new introductio

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

カテゴリー: php

Tim Colles: PostgreSQL Roles are Cluster-Wide

planet postgresql - 2019-03-19(火) 01:20:58

A role in PostgreSQL is common to all databases in the cluster. This seems to be the result of a design decision made when the former user and group handling was unified under role. Follow these links for reference:

Roles, or rather those roles that are not just representing a specific user, ought instead to be an intrinsic part of the database model. Roles are defined by the kind of access they provide (read, write, etc) and by what relations(table, view, function, etc) they provide that access to. Access control is ideally managed within a database using roles rather than separately within each individual application that uses that database. So it makes sense that the access control rules (the roles and their associated permissions) would be defined alongside the definitions of the relations for which they are controlling access, any changes are then self contained. The access control model should be represented as part and parcel of the rest of the database model. Individual users (which are also represented as roles in PostgreSQL) are assigned one or more of the roles defined within each particular database model (based on the local enterprise definition of what needs they have of any particular database).

There is no sense to representing this kind of role at the cluster level as the definition of the role is associated specifically with the database where it actually controls access. In PostgreSQL, to encapsulate the full functionality of the database requires using not only the system catalog tables specific to that database but also the roles relevant to that database held in the cluster wide system catalog tables. With the exception of some special cases, li

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

Avinash Kumar: PostgreSQL Upgrade Using pg_dumpall

planet postgresql - 2019-03-18(月) 23:59:50

There are several approaches to assess when you need to upgrade PostgreSQL. In this blog post, we look at the option for upgrading a postgres database using pg_dumpall. As this tool can also be used to back up PostgreSQL clusters, then it is a valid option for upgrading a cluster too. We consider the advantages and disadvantages of this approach, and show you the steps needed to achieve the upgrade.

This is the first of our Upgrading or Migrating Your Legacy PostgreSQL to Newer PostgreSQL Versions series where we’ll be exploring different paths to accomplish postgres upgrade or migration. The series will culminate with a practical webinar to be aired April 17th (you can register here).

We begin this journey by providing you the most straightforward way to carry on with a PostgreSQL upgrade or migration: by rebuilding the entire database from a logical backup.

Defining the scope

Let’s define what we mean by upgrading or migrating PostgreSQL using pg_dumpall.

If you need to perform a PostgreSQL upgrade within the same database server, we’d call that an in-place upgrade or just an upgrade. Whereas a procedure that involves migrating your PostgreSQL server from one server to another server, combined with an upgrade from an older version (let’s say 9.3) to a newer version PostgreSQL (say PG 11.2), can be considered a migration.

There are two ways to achieve this requirement using logical backups :

  1. Using pg_dumpall
  2. Using pg_dumpall + pg_dump + pg_restore

We’ll be discussing the first option (pg_dumpall) here, and will leave the discussion of the second option for our next post.

pg_dumpall

pg_dumpall can be used to obtain a text-format dump of the whole database cluster, and which includes all databases in the cluster. This is the only method that can be used to backup globals such as users and roles in PostgreSQL.

There are, of course, advantages and disadvantages in employing this approach to upgrading PostgreSQL by rebuilding the database cluster using pg_dumpall.

Advantages of using pg_dumpall for upgrading a Pos[...]
カテゴリー: postgresql

ページ