フィードアグリゲーター

Craig Ringer: Developing on many versions of PostgreSQL at once

planet postgresql - 2019-08-19(月) 20:30:05
In a developer’s ideal world there’s only one stable and one development version of the product. Everyone updates immediately to the next stable version and nobody cares about anything older. As far as I can tell this has never actually happened, ever, in the history of anything. # Working on many versions PostgreSQL is a […]
カテゴリー: postgresql

WebDB Forum 2019 (2019/9/8-9/9)

www.postgresql.jp news - 2019-08-19(月) 09:13:01
WebDB Forum 2019 (2019/9/8-9/9) harukat 2019/08/19 (月) - 09:13
カテゴリー: postgresql

Johann Oskarsson: Porting a PostgreSQL Extension from Unix to Windows 10

planet postgresql - 2019-08-18(日) 23:24:31

2ndQuadrant has written before about how to build your own extension for Windows. This time we take a look at what it takes to port one written by someone else. I am building this for PostgreSQL 11 on Windows using Visual Studio 2019 and clang.

Here we take are working with the hashtypes extension. Unfortunately, at the time of writing, version 0.1.5 has not been released with the support for PostgreSQL 11. So we build one from git.

git clone https://github.com/adjust/hashtypes.git

The first thing we do is to take a look at the C source files. There are only four of them, common.c, crc32.c, md5.c, and sha.c so we can be reasonably sure the porting effort will be quick.

Then we use the directions from 2ndQuadrant to build our own makefile, called windows.mak. We start by setting the compiler and PostgreSQL directory in variables. Here we are using clang to compile so the flags will be familiar to Unix programmers but alien to those used to cl.

## You will need to change these two settings for your own system. CC="C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\VC\Tools\Llvm\8.0.0\bin\clang.exe" POSTGRES="C:\Program Files\PostgreSQL\11"

Then we set the compilation flags in CFLAGS.

CFLAGS=-m64 -Wall -Wextra -O3 \ -I$(POSTGRES)\include\server\port\win32_msvc \ -I$(POSTGRES)\include\server\port\win32 \ -I$(POSTGRES)\include\server \ -I$(POSTGRES)\include

The CFLAGS are set to build 64bit binaries with -m64 then we add the usual warnings and some extra warnings for extra credit with -Wall and -Wextra. Then since we’re compiling production code we add optimization with -O3. The various -I flags are for include directories and we take their names directly from the tutorial by 2ndQuadrant.

The link flags are easy, we just add the PostgreSQL lib directory with -L and link with postgres with -l.

LDFLAGS=-L$(POSTGRES)\lib -lpostgres

Now we can start our make rules. We make a generic rule for the object files.

.c.obj: $(CC) $(CF[...]
カテゴリー: postgresql

Hans-Juergen Schoenig: Timeseries: EXCLUDE TIES, CURRENT ROW and GROUP

planet postgresql - 2019-08-17(土) 16:00:06

Windowing functions and analytics have been around for quite some time and many people already make use of this awesome stuff in the PostgreSQL world. Timeseries  are an especially important area in this context. However, not all features have been widely adopted and thus many developers have to implement functionality at the application level in a painful way instead of just using some of the more advanced SQL techniques.

The idea of this blog is to demonstrate some of the advanced stuff so that more people out there can make use of PostgreSQL’s true power.

Preparing data for analytics

For the purpose of this post I have created a simple data set:

test=# CREATE TABLE t_demo AS SELECT ordinality, day, date_part('week', day) AS week FROM generate_series('2020-01-02', '2020-01-15', '1 day'::interval) WITH ORDINALITY AS day; SELECT 14

In PostgreSQL the generate_series function will return one row each day spanning January 1st, 2020 to January 15th, 2020. The WITH ORDINALITY clause tells PostgreSQL to add an “id” column to the resultset of the function. The date_part function will extract the number of the week out of our date. The purpose of this column is to have a couple of identical values in our timeseries.

In the next list you see the data set we will use:

test=# SELECT * FROM t_demo; ordinality | day | week ------------+------------------------+------ 1 | 2020-01-02 00:00:00+01 | 1 2 | 2020-01-03 00:00:00+01 | 1 3 | 2020-01-04 00:00:00+01 | 1 4 | 2020-01-05 00:00:00+01 | 1 5 | 2020-01-06 00:00:00+01 | 2 6 | 2020-01-07 00:00:00+01 | 2 7 | 2020-01-08 00:00:00+01 | 2 8 | 2020-01-09 00:00:00+01 | 2 9 | 2020-01-10 00:00:00+01 | 2 10 | 2020-01-11 00:00:00+01 | 2 11 | 2020-01-12 00:00:00+01 | 2 12 | 2020-01-13 00:00:00+01 | 3 13 | 2020-01-14 00:00:00+01 | 3 [...]
カテゴリー: postgresql

SunshinePHP 2020 CFP Started

php.net - 2019-08-17(土) 09:00:01
カテゴリー: php

Robert Treat: Charm City Postgres

planet postgresql - 2019-08-16(金) 06:17:00

This post marks the official launch of the Charm City Postgres Meetup group. Our goal is to help bring together the growing community of Developers, DBAs, DBREs, and other technologist in the greater Baltimore area who are working with Postgres. We're currently working to organize folks and would encourage interested people to sign-up on the meetup site, and have set an initial meetup at Smart Office @ BWI. If you have questions or want to speak, sponsor, or host a meeting in the future, you can contact me either through the meetup page or find me on the Postgres Team Slack.

カテゴリー: postgresql

Ibrar Ahmed: A Faster, Lightweight Trigger Function in C for PostgreSQL

planet postgresql - 2019-08-16(金) 00:16:25

We have been writing blog posts about how to write simple extensions in C language and a little more complex one by Ibrar which were well received by PostgreSQL user community. Then we observed that many PostgreSQL users create simple triggers for small auditing requirements, and then feel the pain of trigger on transactions. So we were discussing how simple/lightweight and faster a trigger function is when written in C. Generally, Trigger functions are written in high-level languages like PlpgSQL, but it has a higher overhead during execution and it can impact the transactions – and thereby application performance.

This blog post is an attempt to create a simple trigger function to address one of the common use-cases of triggers, which is to update auditing columns in a table.

In this post, we are going to introduce SPI (Server Programming Interface) functions for novice users. Towards the end of the blog, we share some of the quick benchmark results for understanding the benefits.

Example of Audit timestamp

Let’s proceed with taking up a case and assume that we have a table to hold transaction details. But auditing requirements say that there should be a timestamp on each tuple when the tuple is inserted and when it was last updated.

CREATE TABLE transdtls( transaction_id int, cust_id int, amount int, ... insert_ts timestamp, update_ts timestamp );

For demonstration purpose, let’s remove and trim the other columns and create a table with only 3 essential columns.

CREATE TABLE transdtls( transaction_id int, insert_ts timestamp, update_ts timestamp ); Developing Trigger Function

The trigger function can also be developed and packaged as an extension, which we discussed in s previous blog post here. So we are not going to repeat those steps here. The difference is that file names are named as “trgr” instead of “addme” in the previous blog. Makefile is also modified to refer “trgr” files. This need not be same as the function name “trig_test” in the C source detailed

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

Displaying exif information in WordPress posts

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

After discovering that WordPress modifies img tags when rendering a page, it crossed my mind that I could display exif information underneath each image on my new photography blog. The basic process is applicable to any manipulation of the content that you would want to do before it is displayed.

To do this, we add a new filter to the the_content hook:

add_filter('the_content', function ($content) { // manipulate $content return $content; });

As with all filters, we have to return data in the same format as the first parameter to our function. For the_content, we receive a string which is the post's article text.

The core operation

In my case, all my <img> tags are immediately followed by a <figcaption> tag as I have a caption under each one. I decided to add the exif data within the <figcaption>.

We use regex to find the tags:

if (!preg_match_all( '@img [^>]+><figcaption>[^<]*</figcaption>@', $content, $matches)) { return $content; }

This will store each <img> tag that's followed by a <figcaption> tag into the $matches variable, so we can now iterate over it:

foreach ($matches[0] as $match) { // Find the url in the tag if (preg_match('@src="([0-9a-z:/._-]+)@i', $match, $srcList)) { $url = $srcList[1]; if ($url) { $postId = attachment_url_to_postid($url); if ($postId) { $exif = ffp_create_exifcaption($postId); $new = str_replace('</figcaption>', $exif .'</figcaption>', $match); $content = str_replace($match, $new, $content); } } } }

This code iterates over each match and finds the url in the src attribute of the image tag. We can then then load the post from that url using the rather helpful $postId = attachment_url_to_postid($url);.

Now that we have an ID, we can create the EXIF caption in a separate function and then finally we append it to the end of the current caption. We do this by simply replacing the closing </figcaption> with our text followed by </figcaption> which is a nice fast operation.

Creating EXIF display string

To create the string of EXIF data, I used a separate function to make it easier.

WordPress has already extracted the EXIF data we require when the image was uploaded into the admin, so we simply need to read it and format it. To get it we do this:

$metadata = wp_get_attachment_metadata($postId); if (!isset($metadata['image_meta'])) { return ''; } $imagemeta = $meta['image_meta'] ?? [];

$imagemeta is an array of useful characteristics about the image.

We pull the info we care into a set of variables like this:

$camera = $imagemeta['camera'] ?? null; $lens = $imagemeta['lens'] ?? null; $aperture = $imagemeta['aperture'] ?? null; $shutter = $imagemeta['shutter_speed'] ?? null; $iso = $imagemeta['iso'] ?? null; $focal_length = $imagemeta['focal_length'] ?? null; if (! ($camera || $lens || $aperture || $shutter || $iso || $focal_length)) { return ''; }

(Obviously, if there is no data, there's nothing to do.)

Finally we format it nicely:

$string = '<aside class="exif">'; if ($camera) { $string .= '<div><ion-icon name="camera"></ion-icon><span>'.$camera.'</span></div>'; } if ($lens) { $string .='<div><ion-icon name="radio-button-on"></ion-icon><span>'.$lens.'</span></div>'; } if ($aperture) { $aperture = 'f/' . $aperture; $string .= '<div><ion-icon name="aperture"></ion-icon><span>'.$aperture.'</span></div>'; } if ($shutter) { $shutter = '1/' . (int)(1.0/(float)$shutter); $string .= '<div><ion-icon name="timer"></ion-icon><span>'.$shutter.'</span></div>'; } if ($iso) { $string .= '<div><span class="text-icon">ISO</span><span>'.$iso.'</span></div>'; } if ($focal_length) { $focal_length = (string)(round((float)$focal_length)) . 'mm'; $string .= '<div><ion-icon name="eye"></ion-icon><span>'.$focal_length.'</span></div>'; } $string .= '</aside>'; return $string;

We wrap our entire EXIF string in an <aside> tag so that we can style appropriately. Then for each item of data, we display an icon followed by the text information. The icons are Io

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

カテゴリー: php

Paul Ramsey: Waiting for PostGIS 3: ST_TileEnvelope(z,x,y)

planet postgresql - 2019-08-14(水) 03:21:30

With the availability of MVT tile format in PostGIS via ST_AsMVT(), more and more people are generating tiles directly from the database. Doing so usually involves a couple common steps:

カテゴリー: postgresql

Interview with Jeremy Mikola

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

506 Variant Also Negotiates

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

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

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

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

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

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

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

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

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

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

Should you use this?

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

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

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

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

カテゴリー: php

Hans-Juergen Schoenig: PostgreSQL: Trivial timeseries examples

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

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

Loading timeseries data the easy way

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

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

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

lag: The backbone of timeseries analysis

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

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

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

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

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

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

PHP Conference Brasil 2019

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

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

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

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

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

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

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

2.4.8 This release supports PostgreSQL 9.3-10.

2.3.10

This release supports PostgreSQL 9.2-10.

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

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

ALTER EXTENSION postgis UPDATE;

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

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

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

カテゴリー: postgresql

Alexander Hagerman: Postgres Advisory Locks with asyncio

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

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

Advisory locks

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

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

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

Building the lock

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

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

PHP Conference Japan 2019 CFP Started

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

PHP Conference Japan 2019

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

ページ