Dimitri Fontaine: How to Learn SQL?

planet postgresql - 4時間 44分

Here we are, another SQL query to write. You wish you knew how to write that mechanically, like you would a loop in your favorite programming language. Or at least have a pretty clear idea of a skeleton to tweak until it gives the result set you expect. So instead of working on your SQL query, you google How to write a SQL query? or maybe even How to learn SQL? Right. I feel you, I’ve been there too, even if quite some time ago…

So here my article where I teach you how to learn SQL.

I want to share with you how I did it, and how I continue to do it. There’s no magic secret sauce to it though, it’s all basic work. Again, we have to learn the main concepts and how they play together, then practice simple steps, and then build from there.

カテゴリー: postgresql

Luca Ferrari: New Release of PL/Proxy

planet postgresql - 2019-09-16(月) 09:00:00

There is a new release of PL/Proxy out there!

New Release of PL/Proxy

There is a new exciting release of PL/Proxy: version 2.9 has been released a few hours ago!

This is an important release because it adds support for upcoming PostgreSQL 12. The main problem with PostgreSQL 12 has been that Oid is now a regular column, meaning that HeapTupleGetOid`` is no longer a valid macro. I first proposed a patch that was based on the C preprocessor to get rid of older PostgreSQL version.

The solution implemented by Marko Kreen is of course much more elegant and is based on defining helper functions that are pre-processed depending on the PostgreSQL version.

Enjoy proxying!

カテゴリー: postgresql

Fabien Coelho: Data Loading Performance of Postgres and TimescaleDB

planet postgresql - 2019-09-13(金) 19:46:08

Postgres is the leading feature-full independent open-source relational database, steadily increasing its popularity for the past 5 years. TimescaleDB is a clever extension to Postgres which implements time-series related features, including under the hood automatic partioning, and more.

Because he knows how I like investigate Postgres (among other things) performance, Simon Riggs (2ndQuadrant) prompted me to look at the performance of loading a lot of data into Postgres and TimescaleDB, so as to understand somehow the degraded performance reported in their TimescaleDB vs Postgres comparison. Simon provided support, including provisioning 2 AWS VMs for a few days each.


The short summary for the result-oriented enthousiast is that for the virtual hardware (AWS r5.2xl and c5.xl) and software (Pg 11.[23] and 12dev, TsDB 1.2.2 and 1.3.0) investigated, the performance of loading up to 4 billion rows in standard and partioned tables is great, with Postgres leading as it does not have the overhead of managing dynamic partitions and has a smaller storage footprint to manage. A typical loading speed figure on the c5.xl VM with 5 data per row is over 320 Krows/s for Postgres and 225 Krows/s for TimescaleDB. We are talking about bites of 100 GB ingested per hour.

The longer summary for the performance testing enthousiast is that such investigation is always much more tricky than it looks. Although you are always measuring something, what it is really is never that obvious because it depends on what actually limits the performance: the CPU spent on Postgres processes, the disk IO bandwidth or latency… or even the process of generating fake data. Moreover, performance on a VM with the underlying hardware systems shared between users tend to vary, so that it is hard to get definite and stable measures, with significant variation (about 16%) from one run to the next the norm.

Test Scenario

I basically reused the TimescaleDB scenario where many devices frequently send timespamped data points wh

カテゴリー: postgresql

Pavel Stehule: pspg 2.0.2 is out

planet postgresql - 2019-09-12(木) 13:58:00
there is just one new feature - sort is supported on all columns, not only on numeric columns.
カテゴリー: postgresql

Laurenz Albe: Tracking view dependencies in PostgreSQL

planet postgresql - 2019-09-11(水) 16:00:13


We all know that in PostgreSQL we cannot drop an object if there are view dependencies on it:

CREATE TABLE t (id integer PRIMARY KEY); CREATE VIEW v AS SELECT * FROM t; DROP TABLE t; ERROR: cannot drop table t because other objects depend on it DETAIL: view v depends on table t HINT: Use DROP ... CASCADE to drop the dependent objects too. ALTER TABLE t DROP id; ERROR: cannot drop column id of table t because other objects depend on it DETAIL: view v depends on column id of table t HINT: Use DROP ... CASCADE to drop the dependent objects too.

Some people like it because it keeps the database consistent; some people hate it because it makes schema modifications more difficult. But that’s the way it is.

In this article I want to explore the mechanics behind view dependencies and show you how to track what views depend on a certain PostgreSQL object.

Why would I need that?

Imagine you want to modify a table, e.g. change a column’s data type from integer to bigint because you realize you will need to store bigger numbers.
However, you cannot do that if there are views that use the column. You first have to drop those views, then change the column and then run all the CREATE VIEW statements to create the views again.

As the example shows, editing tables can be quite a challenge if there is a deep hierarchy of views, because you have to create the views in the correct order. You cannot create a view unless all the objects it requires are present.

Best practices with views

Before I show you how to untangle the mess, I’d like to tell you what mistakes to avoid when you are using views in your database design (excuse my switching to teacher mode; I guess holding courses has that effect on you).

Views are good for two things:

  • They allow you to have a recurring SQL query or expression in one place for easy reuse.
  • They can be used as an interface to abstract from the actual table definitions, so that you can reorganize the tables without having to modify the
カテゴリー: postgresql

Interview with Katie McLaughlin

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

Paul Ramsey: Waiting for PostGIS 3: ST_Transform() and Proj6

planet postgresql - 2019-09-11(水) 01:03:37

Where are you? Go ahead and figure out your answer, I'll wait.

No matter what your answer, whether you said "sitting in my office chair" or "500 meters south-west of city hall" or "48.43° north by 123.36° west", you expressed your location relative to something else, whether that thing was your office layout, your city, or Greenwich.

A geospatial database like PostGIS has to have able to convert between these different reference frames, known as "coordinate reference systems". The math for these conversions and the definition of the standards needed to align them all is called "geodesy", a field with sufficient depth and detail that you can make a career out of it.

Fortunately for users of PostGIS, most of the complexity of geodesy is hidden under the covers, and all you need to know is that different common coordinate reference systems are described in the spatial_ref_sys table, so making conversions between reference systems involves knowing just two numbers: the srid (spatial reference id) of your source reference system, and the srid of your target system.

カテゴリー: postgresql

511 Network Authentication Required

planet PHP - 2019-09-11(水) 00:00:00

511 Network Authentication Required is a status that can be used by for example captive portals to signal to computers that they need to go through some kind of sign-in after connecting to a WiFi network.

You might see these kind of sign-in screens when for example connecting to the WiFi at a coffee shop.

Most operating systems and browsers detect this log in screen by making a HTTP request to a standard url. These are some real examples:

  • http://www.msftconnecttest.com/connecttest.txt
  • http://connectivitycheck.gstatic.com/generate_204
  • http://captive.apple.com/hotspot-detect.html
  • http://detectportal.firefox.com/success.txt

Browsers and operating systems will do an HTTP request to one of those urls, and expect a string like success to appear. If it doesn’t appear, it means a router might be blocking it and a pop-up will appear to log into the network.

One of the issues with this approach is that it might not be possible to for a client to distingish a ‘correct’ response, vs. a HTTP response that was intercepted by the network and a captive portal being served instead.

It is a type of man-in-the-middle attack, so returning a captive portal interface instead of the real response might cause systems to malfunction and caches to be primed with bad data.

The 511 Network Authentication Required status code was invented as a default status code for captive portals to return when intercepting a HTTP request. This status signals that it was returned by an intermediate.

The full HTTP response should contain a link to where the user may log in.

The example given from the RFC is as follows:

HTTP/1.1 511 Network Authentication Required Content-Type: text/html <html> <head> <title>Network Authentication Required</title> <meta http-equiv="refresh" content="0; url=https://login.example.net/"> </head> <body> <p>You need to <a href="https://login.example.net/"> authenticate with the local network</a> in order to gain access.</p> </body> </html> References
カテゴリー: php

Interview with David Bisset

planet PHP - 2019-09-10(火) 20:30:00
カテゴリー: php

Xdebug Update: August 2019

planet PHP - 2019-09-10(火) 17:25:00
Xdebug Update: August 2019
London, UK Tuesday, September 10th 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 August, I worked on the following things:

2.8.0beta2 Release

This second beta release addresses a lot of issues that were still outstanding for the 2.8 release. This included simple issues like Wrong name displayed for Recoverable fatal errors and Code Coverage misses fluent interface function call. The trickiest bug was related to the DBGp debugging protocol.

I test Xdebug's implementation of the DBGp protocol by having a file with the PHP script to debug and then a phpt test that has a set of commands to run against that file. As an example, for one of the fixed bugs, the script looks like:

<?php function breakpoint1() { echo base64_encode("testing"), "\n"; strlen(); } breakpoint1(); ??>

And the phpt test looks like:

<?php require 'dbgp/dbgpclient.php'; $filename = realpath( dirname(__FILE__) . '/bug01388-01.inc' ); $commands = array( 'feature_get -n resolved_breakpoints', "breakpoint_set -t line -f file://{$filename} -n 4", 'breakpoint_list', 'feature_set -n resolved_breakpoints -v 1', 'feature_get -n resolved_breakpoints', 'breakpoint_list', "breakpoint_set -t line -f file://{$filename} -n 4", 'breakpoint_list', 'detach', ); dbgpRunFile( $filename, $commands ); ??>

The third command sets a breakpoint on line 4 (the echo statement) and then does various things related to breakpoint resolving. The "remote log" that is generated by the test is then compared (after some regexp replacements) with the expected output.

Because I run the script in a new process, I have some methods in place to also collect the output of the script (both stdout and stderr). I don't usually have stderr on, as there are a few false positives, but when I tested with this in the past, I noticed that one test caused a segmentation fault.

After a few hours of trying to find out the problem, I noticed that this would only happen in the init state (when the debugger first connects to the IDE, and when the IDE can enable features and send breakpoints). If in this init state the IDE would send the detach command, Xdebug would crash. This detach command can be used by an IDE to disengage the debugger, with the script continuing to run afterwards.

Due to an implementation bug related to whether the debugger connection was active or not, a detach in the init state would mark the connection active, while the connection was already cleaned up before hand. This caused Xdebug to access memory, that was already freed (which is a bad thing to do). The fix was luckily quite simple.

IANA Port Assignment

As a result of a twitter conversation that I had, I tried applying for an "assigned port" for the DBGp protocol. As some of you might be aware, Xdebug shares the same default port (9000) as PHP-FPM. This has caused confusion in the past, so I was hoping to avoid future problems with Xdebug 3 by asking IANA to assign me one. However, IANA declined by application by somewhat vague comments about "security" and not needing one because DBGp runs on a LAN.

As to the security aspect, they require authentication and encryption for new protocols, and of course DBGp isn't new. I would like to add an encryption layer to DBGp, but I can't require this right away, as that would mean no IDE can talk DBGp any more. Requiring authentication (i.

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

カテゴリー: php

Devrim GÜNDÜZ: Installing PostgreSQL 12 beta/RC on RHEL/CentOS/Fedora

planet postgresql - 2019-09-09(月) 20:36:00
PostgreSQL 12 is in beta right now, and we need everyone to test it!

We have been building repos even from daily builds, Below are the steps to install PostgreSQL 12 on RHEL 8: Continue reading "Installing PostgreSQL 12 beta/RC on RHEL/CentOS/Fedora"
カテゴリー: postgresql

Pavel Stehule: pspg 2.0.1 released

planet postgresql - 2019-09-09(月) 15:11:00
I released new version of pspg 2.0.1. I started work on pspg three years ago, and now there is almost all features what is possible with this design - the data are stored in original form (created by some sql client). The alternative of pspg can be sophisticated CSV viewers, because psql can produce a content in CSV format. Maybe (in far future) pspg 3.0 will be based on internal CSV storage with formatting on pspg side. But it is not a plan for few next years (or somebody can do it). So last month I wrote vertical cursor support (necessary for next step), and last week I finished "sort by column" feature. The sort command has sense only on numeric columns (works with numbers only - I really would not supply ORDER BY clause in SQL). With this limit is has maybe interesting feature - some size units (kB, MB, GB, TB) are supported.

Please, use it freely. And if you like this software, send me a postcard from your country - pspg is postcardware.
カテゴリー: postgresql

Jignesh Shah: Tuning DB Parameters for PostgreSQL 12 in Amazon RDS

planet postgresql - 2019-09-09(月) 13:00:00
In my last entry, we saw how to setup PostgreSQL 12 beta 3 in Amazon RDS. In that entry I purposely left out how to change database parameters as I realized that it deserves an entry (or more) by itself.

Using the AWS CLI you can create a new database parameter as follows:

$ aws rds create-db-parameter-group --db-parameter-group-name jkpg12pg \
--db-parameter-group-family postgres12 --description "My PostgreSQL 12 Parameter Group" \
--region us-east-2 --endpoint https://rds-preview.us-east-2.amazonaws.com

We have just created a group and not applied the parameters to any database. Before we apply, we do to see what are the default values created you can run a command as follows to see values being set by default

$ aws rds describe-db-parameters --db-parameter-group-name jkpg12pg \
--region us-east-2 --endpoint https://rds-preview.us-east-2.amazonaws.com \
--query 'Parameters[].[ParameterName,ParameterValue]' --output text

You see an output containing a list of parameters with values. Lets look at some of the values to see how to interpret them.

application_name None
autovacuum_max_workers GREATEST({DBInstanceClassMemory/64371566592},3)
autovacuum_vacuum_cost_limit GREATEST({log(DBInstanceClassMemory/21474836480)*600},200)
effective_cache_size {DBInstanceClassMemory/16384}
jit None
maintenance_work_mem GREATEST({DBInstanceClassMemory*1024/63963136},65536)
max_connections LEAST({DBInstanceClassMemory/9531392},5000)
shared_buffers {DBInstanceClassMemory/32768}
shared_preload_libraries pg_stat_statements
work_mem None
xmlbinary None
xmloption None

When you see None it basically is equivalent to null which means in such cases it is not set in postgresql.conf and the default value of the PostgreSQL version engine is set by PostgreSQL. In the above example, you will notice that jit is set to None which means it will take the default  ON value of PostgreSQL 12 and enable jit in the instance.

If you change a parameter set to a specific value based [...]
カテゴリー: postgresql

Regina Obe: PostgreSQL 11 64-bit Windows FDWs

planet postgresql - 2019-09-08(日) 12:28:00

We are pleased to provide binaries for file_textarray_fdw and odbc_fdw for PostgreSQL 11 Windows 64-bit.

To use these, copy the files into your PostgreSQL 11 Windows 64-bit install folders in same named folders and then run CREATE EXTENSION as usual in the databases of your choice. More details in the packaged README.txt

Continue reading "PostgreSQL 11 64-bit Windows FDWs"
カテゴリー: postgresql

Dimitri Fontaine: The R in ORM

planet postgresql - 2019-09-08(日) 05:55:00
Ok, let’s face it, I like SQL. A lot. I think it’s a fine DSL given how powerful it is, and I respect its initial goal to attract non developers and try to build English sentences rather than code. Also, I understand that manually hydrating your collection of objects in your backend developement language is not the best use of your time. And that building SQL as strings makes your code ugly.
カテゴリー: postgresql

Dimitri Fontaine: What is an SQL Aggregate?

planet postgresql - 2019-09-08(日) 05:00:00
In our previous articles we had a look at What is an SQL relation? and What is a SQL JOIN?. Today, I want to show you what is an aggregate in SQL. You might have heard about Map/Reduce when Google made it famous, or maybe some years before, or maybe some years later. The general processing functions map and reduce where invented a very long time ago. The novelty from the advertising giant was in using them in a heavily distributed programming context.
カテゴリー: postgresql

Dimitri Fontaine: What is an SQL JOIN?

planet postgresql - 2019-09-08(日) 04:30:00
It took me quite some time before I could reason efficiently about SQL JOINs. And I must admit, the set theory diagrams never helped me really understand JOINs in SQL. So today, I want to help you understand JOINs in a different way, hoping to make the concept click at once for you too! As we saw in the previous article What is an SQL relation?, in SQL a relation is a collection of objects, all sharing the same definition.
カテゴリー: postgresql

Dimitri Fontaine: What is an SQL relation?

planet postgresql - 2019-09-08(日) 04:00:00
If you’re like me, understanding SQL is taking you a long time. And some efforts. It took me years to get a good mental model of how SQL queries are implemented, and then up from the lower levels, to build a mental model of how to reason in SQL. Nowadays, in most case, I can think in terms of SQL and write queries that will give me the result set I need.
カテゴリー: postgresql

Kaarel Moppel: A Primer on PostgreSQL Upgrade Methods

planet postgresql - 2019-09-07(土) 18:00:46

Soon it’s that time of the year again – basically a 2nd Christmas for followers of the “blue elephant cult” if you will :). I’m, of course, referring to the upcoming release of the next PostgreSQL major version, v12. So I thought it’s about time to go over some basics on upgrading to newer major versions! Database upgrades (not only Postgres) are quite a rare event for most people (at least for those running only a couple of DB-s). Since upgrades are so rare, it’s quite easy to forget how easy upgrading actually is. Yes, it is easy – so easy that I can barely wrap my head around why a lot of people still run some very old versions. Hopefully, this piece will help to convince you to upgrade faster in the future :). For the TLDR; (in table version) please scroll to the bottom of the article.

Why should I upgrade in the first place?

Some main points that come to my mind:

  • More security
    Needless to say, I think security is massively important nowadays! Due to various government regulations and the threat of being sued over data leaks, mistakes often carry a hefty price tag. Both new and modern authentication methods and SSL/TLS version support are regularly added to new PostgreSQL versions.
  • More performance without changing anything on the application level!
    Based on my gut feeling and on some previous testing, typically in total around ~5-15% of runtime improvements can be observed following an upgrade (if not IO-bound). So not too much…but on some releases (9.6 parallel query, 10.0 JIT for example) a possibly game changing 30-40% could be observed. As a rule, the upgrades only resulted in upsides – over the last 8 years, I’ve only seen a single case where the new optimizer chose a different plan so that things got slower for a particular query, and it needed to be re-written.
  • New features for developers
  • Every year, new SQL standard implementations, non-standard feature additions, functions and constructs for developers are introduced, in order to reduce the amounts of code writt
カテゴリー: postgresql

Leaders Wanted

planet PHP - 2019-09-07(土) 02:55:00

In the best companies, everyone is a leader. Decisions are made by those best suited to make them, everyone feels trusted and respected, and a shared purpose provides unity and motivation.

I’ve been trying to be a better leader my entire career, and I still have much to learn. Nevertheless, some lessons I’ve learned along the way have stood the test of time, and I’d like to share them with you.

Mind your morale

Morale is a currency; spend it wisely.

One of the most important responsibilities of any leader is to manage the morale and energy of the team. If you’re a freelancer, self-awareness is key, because you must manage yourself, and tending to your own morale is critical to your success.

Managing morale doesn’t mean avoiding hard work. In my experience, providing an environment that lets people do their best work is what keeps morale high.

Decide who decides

Clearly defined roles and responsibilities help everyone. A leader’s job is to trust the right people to make the right decisions, and to provide the necessary context to do so.

When someone is a designated decision-maker, they are more likely to listen carefully to other views, because they don’t have to spend energy presenting or defending their own. Others will feel heard, because the decision-maker is actually listening. Everyone wins.

Choose words carefully

Language is important. The words we use shape our perspective and the perspectives of everyone we work with.

Make we a habit. They didn’t make a mistake; we made a mistake. The client doesn’t have a big opportunity; we have a big opportunity.

When you’re certain about something, say so, but also make it clear when you’re not. Expressing uncertainty doesn’t erode trust, but expressing certainty and being wrong does.

Set healthy boundaries

Communication is good, but be deliberate about it. The cost of real-time, always-on communication tools shouldn’t be overlooked, especially when used with clients. When possible, stick to email and scheduled meetings.

Avoid working outside of normal working hours, too. It’s more sustainable, and often more productive, to use a strict work schedule to help you and your team stay focused and driven. Don’t let the possibility of after-hours work excuse lacklustre performance during the day.

Make meetings count

Meetings are often unfairly maligned. It’s true that a poorly-organized meeting can be disruptive and wasteful, but a good meeting can be invaluable.

Every meeting needs a goal. Agendas are good, but goals are better. With a clear goal, it’s easy to intuit when the meeting is the least bit off-track, so you can correct course as you go. It’s also worth making clear if a meeting is meant to be divergent (new ideas welcome) or convergent (time to reach a consensus).

Make a habit of designating someone to take notes, and email the notes to everyone after the meeting. This will help you move more quickly, because no one will be worried about missing something. It also gives you a good excuse to exclude optional attendees; they can just read the notes. Small meetings without bystanders are more efficient.

Create your own rules

Some of the best lessons will come from your own experience.

A rule we adhere to on my team, for example, is to never estimate someone else’s work. This gives each person a sense of responsibility to the schedule and helps prevent unreasonable expectations.

Try to avoid treating any rule as dogma, however. ‘It depends’ is the only rule that is always dependable, so be willing to make an exception for a really good reason.

Stay humble

Leadership is a skill that requires study and practice, just like any other, and learning from one another is a great way to continue to improve. As peers, we share common threads, so the lessons we learn do as well.

I’m convinced that the most meaningful work is teamwork. Let’s learn together and work together to deliver on the web’s promise. Leaders wanted.

The original version of this article was commissioned for New Adventures magazine, January 2019.

カテゴリー: php