php|architect: Internal Apparatus: Memoization

phpdeveloper.org - 2019-06-29(土) 06:30:01

By Edward Barnard Memoization is another form of memory use. I see it regularly used for improving application performance, but it’s also used to improve compiler performance. To learn the concept, we’ll use a PHP project with database tables, then look at other ways to use the technique....

カテゴリー: php

PHP: Hypertext Preprocessor: PHP 7.4.0 alpha 2 Released

phpdeveloper.org - 2019-06-29(土) 06:30:01

PHP team is glad to announce the release of the second PHP 7.4.0 version, PHP 7.4.0 Alpha 2. 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.0 Alpha 2 please visit the download page. Please carefully test this version...

カテゴリー: php

Tomáš Votruba Blog: 7 News and Changes in Symplify 6

phpdeveloper.org - 2019-06-29(土) 06:30:01

Do you use Easy Coding Standard, Package Builder or Statie? Do you need to upgrade safely? Do you want to benefit from new features?

This post shows 7 news and changes, that might affect you (in a good way).

カテゴリー: php

Vasilis Ventirozos: Accessing PostgreSQL data from AWS Lambda

planet postgresql - 2019-06-29(土) 04:45:00
All databases need some maintenance that we usually perform using scheduled tasks. For example, if you have an RDS instance and you want to get a bloat report once a month, you’ll probably need a small EC2 instance just to do these kinds of things. In this post, we will talk about accessing RDS, getting a result from a SQL statement and reporting it to a slack channel using a scheduled Lambda function; let’s call this poor man’s crontab :)>
Before we start, psycopg2 isn’t supported by lambda so it needs to be packaged into the lambda deployment package along with any other dependencies, but since psycopg2 requires libpq it needs to be compiled with libpq statically linked. There are many binaries of psycopg2 that you can download, but I would suggest you compile your own, using the latest PostgreSQL source code. We’ll talk about all these steps throughout this post.
We’ll cover the following:
  • Creating a new AWS postgres RDS instance
  • How to compile psycopg (we’ll use docker for that)
  • Code for the function
  • Packaging
  • Lambda function, trigger, schedule
  • Testing

I won’t get into much detail about the steps of building an RDS instance as it is pretty straight forward, assuming you have your aws client setup, you’ll need something similar to the following, if not, go to aws console and clickety-click.
aws rds create-db-instance \
--db-subnet-group-name [your subnet group] \
--db-security-groups [your security group] \
--db-instance-identifier [your instance identifier] \
--db-instance-class db.t2.micro \
--engine postgres \
--allocated-storage 5 \
--no-publicly-accessible \
--db-name [your db name] \
--master-username [your username] \
--master-user-password [your password]\
--backup-retention-period 3
For compiling psycopg2 we need a linux installation, I am on a mac (don’t hate) so I used a docker container (ubuntu bionic) for the compiles. You can find the Dockerfile here

The steps for compiling are the following :
# Download sou[...]
カテゴリー: postgresql

Bruce Momjian: Ibiza: A Different Type of Conference

planet postgresql - 2019-06-28(金) 22:45:01

Having returned from last week's Ibiza conference, I have a new understanding of the event's goals. I know there was some uncertainty about the event, for several reasons:

  • Having a conference at a resort is a new thing for our community. We started years ago with conferences in universities, and steadily grew to hotel-based conferences in minor and then major cities.
  • Ibiza has a reputation in some countries as a party destination. The wildest thing I saw were yelping dogs being walked along the beach.
  • The beach mention often confused people. This was part of an effort to raise the importance of the hallway track, rather than it being just scheduled holes between technical talks. I didn't realize it was possible during a 90-minute lunch break to eat and swim in the ocean, but I did it!
  • There is historical abuse of resort-based conferences as paid or tax-free vacations. This was certainly not the case for Ibiza, but it is an additional hurdle.

I returned from the conference with a warm feeling for the venue, the people I met, and the things I learned, as did my wife and daughter. While resort conferences are not for everybody, they are popular in other industries, and there is certainly a need for this type of event. The next scheduled "beach" conference is in Bali, and I plan to attend.

カテゴリー: postgresql

Bruce Momjian: The Democratization of Databases

planet postgresql - 2019-06-28(金) 02:15:01

Having delivered my new talk, The Democratization of Databases, at PostGres IBiZa and Postgres Vision, I am now posting my slides online. It covers the history of various governing structures and why democracy provides superior results. It has been well received.

カテゴリー: postgresql

Joe Conway: PostgreSQL Deep Dive: PostgreSQL Defaults and Impact on Security - Part 2

planet postgresql - 2019-06-27(木) 22:17:33

In Part 1 of this blog, we covered quite a bit of information with respect to how a PostgreSQL database is initially configured by default from the perspective of discretionary access control. We also saw how to inspect those default behaviors using the crunchy_check_access extension. In Part 2, we will explore the situation identified in CVE-2018-1058 and discuss how to protect yourself.

CVE-2018-1058: Explained and Exploited

Finally we have come to the Pièce De Résistance!

CVE-2018-1058 describes how a user can create objects, named the same as objects in different schemas which can change the behavior of other users' queries, potentially causing unexpected or malicious behavior. This is also known as a trojan-horse attack. In order to fully understand how this situation may arise, we will first review some fundamental PostgreSQL concepts.

カテゴリー: postgresql

第 162 回理事会議事録 (2019-6)

www.postgresql.jp news - 2019-06-27(木) 19:09:23
第 162 回理事会議事録 (2019-6) anzai 2019/06/27 (木) - 19:09
カテゴリー: postgresql

The Next Step

planet PHP - 2019-06-27(木) 19:03:00

Nearly 9 years ago Marjolein and I started Ingewikkeld together. It was mostly a joint freelance business, we weren't planning on having people work for us. My main focus was PHP development, and I wanted to help customers with their PHP-related problems, whether that was architecture, development, training. Anything related to PHP, really.

When we started, I really only focussed on my freelance PHP work, but at some point there was so much work, and I had to say no to so many potential clients, that we hired people. It started out as an interesting idea, but as soon as Jelrik was on the job market, things went quickly. Only 2 months later, Mike joined as well.

Jelrik has since moved on, but Mike (who I've been friends with even before Ingewikkeld was started) stuck around. He's still with Ingewikkeld. And over the years, Mike turned out to really complement my chaotic nature. And this has triggered a change at Ingewikkeld.

Some time ago already we started preparations for several changes to the Ingewikkeld company structure. I'm happy to announce today that we've set the first step, by adding Mike to the Ingewikkeld leadership. The new Ingewikkeld leadership will be:

  • Stefan Koopmanschap: Business director
  • Marjolein van Elteren: Creative director
  • Mike van Riel: Technical Director

I am really happy with this first step. And it's not the last. More things will change in the coming time, to make Ingewikkeld an even more solid business delivering even more quality services.

カテゴリー: php

You may not need a query bus

planet PHP - 2019-06-27(木) 16:00:00

"Can you make a query bus with SimpleBus?" The question has been asked many times. I've always said no. Basically, because I didn't build in the option to return anything from a command handler. So a handler could never become a query handler, since a query will of course have to return something.

I've always thought that the demand for a query bus is just a sign of the need for symmetry. If you have command and query methods, then why not have command and query buses too? A desire for symmetry isn't a bad thing per se. Symmetry is attractive because it feels natural, and that feeling can serve as design feedback. For instance, you can use lack of symmetry to find out what aspect of a design is still missing, or to find alternative solutions.

Nonetheless, I think that we may actually not need a query bus at all.

The return type of a query bus is "mixed"

A command or query bus interface will look something like this:

interface Bus { /** * @return mixed */ public function handle(object $message); }

A sample query and query handler would look like this:

final class GetExchangeRate { // ... } final class GetExchangeRateHandler { public function handle(GetExchangeRate $query): ExchangeRate { // ... } }

When you pass an instance of GetExchangeRate to Bus::handle() it will eventually call GetExchangeRateHandler::handle() and return the value. But Bus::handle() has an unknown return type, which we would call "mixed". Now, you know that the return type is going to be ExchangeRate, but a compiler wouldn't know. Nor does your IDE.

// What type of value is `$result`? $result = $bus->handle(new GetExchangeRate(/* ... */));

This situation reminds me of the problem of a service locator (or container, used as locator) that offers a generic method for retrieving services:

interface Container { public function get(string $id): object; }

You don't know what you're going to get until you get it. Still, you rely on it to return the thing you were expecting to get.

Implicit dependencies

This brings me to the next objection: if you know which service is going to answer your query, and what type the answer is going to be, why would you depend on another service?

If I see a service that needs an exchange rate, I would expect this service to have a dependency called ExchangeRateRepository, or ExchangeRateProvider, or anything else, but not a QueryBus, or even a Bus. I like to see what the actual dependencies of a service are.

final class CreateInvoice { // What does this service need a `Bus` for?! public function __construct(Bus $bus) { // ... } }

In fact, this argument is also valid for the command bus itself; we may not even need it, since there is one command handler for a given command. Why not call the handler directly? For the automatic database transaction wrapping the handler? I actually prefer dealing with the transaction in the repository implementation only. Automatic event dispatching? I do that manually in my application service.

Really, the main thing that I hope the command bus brought us, is a tendency to model use cases as application services, which are independent of an application's infrastructure. And I introduced the void return type for command handlers to prevent write model entities from ending up in the views. However, I've become much less dogmatic over the years: I happily return IDs of new entities from my application services these days.

No need for middleware

Actually, the idea of the command bus having middleware that could do things before or after executing the command handler, was pretty neat. Dealing with database transactions, dispatching events, logging, security checks, etc. However, middlewares also tend to hide important facts from the casual reader. One type of middleware is quite powerful nonetheless: one that serializes an incoming message and adds it to a queue for asynchronous processing. This works particularly well with commands, because they don't return anything anyway.

I'm not sure if any of these middleware solutions will be interesting for a query bus though. Queries shouldn't need to run within a database transaction. They won't dispatch events, they won't nee

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

カテゴリー: php

Luca Ferrari: PostgreSQL Administrator Account WITH NOLOGIN (recover your role)

planet postgresql - 2019-06-27(木) 09:00:00

Today I got an email from a friend of mine that locked out of his own database due to a little mistake.

PostgreSQL Administrator Account WITH NOLOGIN (recover your postgres role)

What if you get locked out your own cluster due to a simple and, to some extent, stupid error? Let’s see it in quick list of steps.
First of all, lock the default postgres account so that the default administrator cannot any more log in the clsuter:

% psql -U postgres -c "ALTER ROLE postgres WITH NOLOGIN" testdb ALTER ROLE % psql -U postgres -c "SELECT version();" testdb psql: FATAL: role "postgres" is not permitted to log in

What a mess!

PostgreSQL has a specific recovery mode, called single user mode, that resemble the operating system single user mode and can be used for such situations. Let’s see how.
First of all, shut down the cluster, avoid more damages of what you have already done!

% sudo service postgresql stop

Now, start the postgres process in single user mode. You need to know the data directory of your cluster in order for it to work:

% sudo -u postgres postgres --single -D /mnt/pg_data/pgdata/11.1 PostgreSQL stand-alone backend 11.3 backend>

What happened? I used the operating system user postgres to launch the operating system process postgres (ok there’s a little name confusion here!) in single (--single) mode for my own data directory (-D). I got a prompt, I’m connected to the backend process directly, so this is not the same as a local or TCP/IP...

カテゴリー: postgresql

PHP 7.4.0 alpha 2 Released

php.net - 2019-06-27(木) 08:34:49
カテゴリー: php

Kaarel Moppel: Version 1.6 of pgwatch2 PostgreSQL monitoring tool released

planet postgresql - 2019-06-26(水) 17:00:20

After almost half a year since last updates I’m glad to announce that another set of useful features and improvements have found their way to pgwatch2 – our Open Source PostgreSQL monitoring tool of choice. New version is incremented to 1.6 but continuing the naming tradition I’m also calling it “Feature Pack 5” as it’s mostly a feature release (with a couple of bugfixes as well). So here a quick overview on changes – for the “full monty” and when updating from a previous version please turn to the Github changelog link below.

Highlights – Prometheus and Patroni support

The choice of data storage “backends” has been widened again (previous version added PostgreSQL) – it’s now possible to leverage the very popular Prometheus TSDB to store metrics gathered by pgwatch2! But a recommendation – I’d use Prometheus only when it’s already running in your organization and would strongly consider the alternatives (Postgres, InfluxDB) if planning to only monitor Postgres and starting a fresh monitoring setup as Prometheus is not able to store string values which is not optimal for Postgres monitoring. Also remember that we’re talking about the “pull” model now – previously metrics were pushed to the datastore. To get quickly started there’s also a suitable “predefined config” provided and an “DB Overview” dashboard similar to Postgres / InfluxDB.

The second big feature add support for our favourite Postgres HA cluster manager – Patroni, and should especially appeal to people running Postgres in a “cloudy” manner. In short the feature means that you can provide connection information to your Distributed Consensus Store (etcd, Zookeeper, Consul) that’s powering Patroni and then pgwatch2 will periodically consult this DCS and fetch the active “to be monitored” instances listing from there. Remember – the standard way is to exactly tell on which IP-s / hostnames your Postgres instances are available. For the time being though to keep it simple access is only possible to DCS-s that are not using passwords (which is the common ca

カテゴリー: postgresql

Michael Paquier: Postgres 12 highlight - More progress reporting

planet postgresql - 2019-06-26(水) 15:16:35

Since Postgres 11, it is possible to monitor progress activity of running manual VACUUM and even autovacuum using a dedicated system catalog called pg_stat_progress_vacuum. This is vital for operators when doing some long-running operations, because it is possible to not blindly wait for an operation to finish. When doing performance workload analysis, this is also proving to be helpful in evaluating VACUUM job progressing for tuning system-level parameters or even relation-level once depending on the load pattern.

Postgres 12 has added more monitoring in this area thanks for a set of commits. Here is the one for CLUSTER and VACUUM FULL:

commit: 6f97457e0ddd8b421ca5e483439ef0318e6fc89a author: Robert Haas <rhaas@postgresql.org> date: Mon, 25 Mar 2019 10:59:04 -0400 Add progress reporting for CLUSTER and VACUUM FULL. This uses the same progress reporting infrastructure added in commit c16dc1aca5e01e6acaadfcf38f5fc964a381dc62 and extends it to these additional cases. We lack the ability to track the internal progress of sorts and index builds so the information reported is coarse-grained for some parts of the operation, but it still seems like a significant improvement over having nothing at all. Tatsuro Yamada, reviewed by Thomas Munro, Masahiko Sawada, Michael Paquier, Jeff Janes, Alvaro Herrera, Rafia Sabih, and by me. A fair amount of polishing also by me. Discussion: http://postgr.es/m/59A77072.3090401@lab.ntt.co.jp

And here is the second one for CREATE INDEX and REINDEX:

commit: ab0dfc961b6a821f23d9c40c723d11380ce195a6 author: Alvaro Herrera <alvherre@alvh.no-ip.org> date: Tue, 2 Apr 2019 15:18:08 -0300 Report progress of CREATE INDEX operations This uses the progress reporting infrastructure added by c16dc1aca5e0, adding support for CREATE INDEX and CREATE INDEX CONCURRENTLY. There are two pieces to this: one is index-AM-agnostic, and the other is AM-specific. The latter is fairly elaborate for btrees, including reportage for parallel index builds and the separate phases that btree index c[...]
カテゴリー: postgresql

PHP 7.4.0 alpha 2 Released

planet PHP - 2019-06-26(水) 09:00:00
PHP team is glad to announce the release of the second PHP 7.4.0 version, PHP 7.4.0 Alpha 2. 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.0 Alpha 2 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 Alpha 3, planned for July 11th. 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

Richard Yen: The Challenges of Setting max_connections and Why You Should Use a Connection Pooler

planet postgresql - 2019-06-26(水) 06:01:09

PostgreSQL is “The World’s Most Advanced Open Source Database,” and I believe it. In my 10+ years of working with it, it’s been solid, serving up data to SaaS loads of over 1000 queries per second, rarely going down, surviving all manner of accusations of corruption (which turned out to be software engineering error) and performance degradation (which turned out to be user error). It’s got so many features and extensions that suit every need, maybe 50-60% of which most users don’t use on a regular basis, or haven’t even heard about. Unfortunately, in my recent experiences in technical support, I’ve learned that Postgres is generally very trusting, but that’s the case with many pieces of technology; it makes no judgments of how you should tune the parameters in postgresql.conf, just like the gas pedal on your sports car doesn’t make any judgments about how fast you should be driving. Just because you can put the pedal to the metal doesn’t mean you should do that in rush hour traffic, and just because you can set a parameter very high doesn’t necessarily mean that you should set it that high to serve a high-traffic OLTP application.

One of the parameters that gets the most misunderstanding is max_connections. It’s understandable that on modern systems with lots of CPUs and lots of RAM, serving modern SaaS loads to a global user base, one can see tens of thousands of user sessions at a time, each trying to query the database to update a user’s status, upload a selfie, or whatever else users might do. Naturally, a DBA would want to set max_connections in postgresql.conf to a value that would match the traffic pattern the application would send to the database, but that comes at a cost. One example of such a cost would be connection/disconnection latency; for every connection that is created, the OS needs to allocate memory to the process that is opening the network socket, and PostgreSQL needs to do its own under-the-hood computations to establish that connection. Scale that up to thousands of user sessi

カテゴリー: postgresql

Joe Conway: PostgreSQL Deep Dive: PostgreSQL Defaults and Impact on Security - Part 1

planet postgresql - 2019-06-26(水) 03:25:05

Recently I gave a "deep dive" talk on the topic of PostgreSQL security, and I wanted to capture one part of that content into a blog since this format is both better for making that content stand on its own and for expanding on it a bit.

Specifically, in this two-part blog, we will cover a PostgreSQL extension that I wrote called crunchy_check_access -- the motivation behind it and what it does -- and then use that extension to probe and understand the consequences of the default, out of the box, security of PostgreSQL. We will also examine the implications of choices we make when setting up "users" and "groups". To cap it off, we will explore the situation identified in CVE-2018-1058 and discuss how to protect yourself.

Warning: this two-part blog is a wall of text (and SQL). It would probably be more suited as a chapter in a long book. I encourage you to stick with it though, as I believe even those with a great deal of PostgreSQL experience will find the content very enlightening. At a minimum it will probably remind you of things you once learned but have since forgotten.

カテゴリー: postgresql

451 Unavailable For Legal Reasons

planet PHP - 2019-06-26(水) 00:00:00

If a server refuses to serve content for legal reasons, it can use the 451 Unavailable For Legal Reasons status code.

Examples of this could include government censorship, or DMCA takedown requests.

In many cases when a country censors certain information, it’s also not allowed to discuss that the content was censored. For those cases the 451 status is not going to be very useful, but for cases where it can be discussed, it’s a good status code to use. It’s a more specific version of 403 Forbidden.

The number 451 is a reference to the book Fahrenheit 451, by Ray Bradbury. In the book censorship of literature is one of the central themes. ‘Fahrenheit 451’ is itself a reference to the temperature at which books ignite (232°C in the developed world).

When a resource is blocked, a server should also respond with a Link header identifiying who blocked the request.

This should refer to the entity that’s responsible for the blocking, not the entity that set the policy (so in the case of Youtube it would refer to Google, not the US Government in case of a DMCA takedown).

Example HTTP/1.1 451 Unavailable For Legal Reasons Link: <https://proxy.example.org/legal>; rel="blocked-by" Content-Type text/html <h1>Government policy prohibits you from reading this information.</h1> References
  • RFC7725 - An HTTP Status Code to Report Legal Obstacles
カテゴリー: php