Neues vom PostgreSQL Planet

I released Orafce 3.14.0. It is mostly bugfix release. Only implementation of unistr function is new. The most important fixed issue was missing dependency between functions (n)varchar2 and (n)varchar2_transform. An result of this missing dependency was bad order of functions in dump used by binary upgrade - and at end the binary upgrade was broken. After fixing the binary upgrade between major Postgres releases (by pg_upgrade) is possible.

On 20th of December 2020, Alexander Korotkov committed patch: Multirange datatypes   Multiranges are basically sorted arrays of non-overlapping ranges with set-theoretic operations defined over them.   Since v14, each range type automatically gets a corresponding multirange datatype. There are both manual and automatic mechanisms for naming multirange types. Once can specify multirange type name … Continue reading "Waiting for PostgreSQL 14 – Multirange datatypes"

I recently wrote about building a Django app that stores uploaded image files in bytea format in PostgreSQL. For the second post in this series, we're now going to take a look at applying a blur filter to the uploaded image using PL/Python. 

On 18th of December 2020, Fujii Masao committed patch: pg_stat_statements: Track time at which all statistics were last reset.   This commit adds "stats_reset" column into the pg_stat_statements_info view. This column indicates the time at which all statistics in the pg_stat_statements view were last reset.

On 11st of December 2020, Tom Lane committed patch: Allow subscripting of hstore values.   This is basically a finger exercise to prove that it's possible for an extension module to add subscripting ability. Subscripted fetch from an hstore is not different from the existing "hstore -> text" operator. Subscripted update does seem to be … Continue reading "Waiting for PostgreSQL 14 – Allow subscripting of hstore values."

What if I told you that you can create an out-of-the-box active-active, federated PostgreSQL cluster on Kubernetes?

Now that OverlayNG has landed on JTS master, it is getting attention from downstream projects interested in porting it or using it.  One of the JTS ports is the Net Topology Suite (NTS) project, and it is very proactive about tracking the JTS codebase.  Soon after the OverlayNG commit an NTS developer noticed an issue while running the performance tests in JTS: the Int

Repository, Tuning Guide and API for your postgresql.conf

postgresqlco.nf (aka postgresqlCO.NF, or simply “CONF”) was born a little bit more than two years ago. CONF’s main goal was to help Postgres users find more and easier help to understand and tune their postgresql.confs. Fast-forward to today, CONF has helped more than 150K users with their Postgres configuration settings, and is on the way to serving 1M page views every year, growing at double digits month-over-month.

The PostGIS raster extension has a steep learning curve, but it opens up some unique possibilities for data analysis and accessing non-standard data from within PostgreSQL. Here's an example that shows how to access raster data from PostGIS running on Crunchy Bridge.

Just to remind you, we already discussed issues related to isolation, made a digression regarding low-level data structure, and then explored row versions and observed how data snapshots are obtained from row versions.

Free 1GB Postgres Database on AWS CloudShell TL;DR

AWS CloudShell is a CLI embedded in the AWS Web Console. It is meant to make it easier to run the AWS CLI, SDK and other scripts from your web browser, without having to install anything locally or having to deal with local credential and profiles management. It is a free service.

I wrote about the new pg_timetable 3 major release not so long ago. Two essential features were highlighted:
– new session locking implementation
– new jackc/pgx Golang library used

Today I want to reveal one more advanced feature! Fasten your seat belts!
First, we need to distinguish exclusive client session mode from exclusive chain execution mode.

This post originally appeared on the Crunchy Data blog.

While we talk about “PostGIS” like it’s one thing, it’s actually the collection of a number of specialized geospatial libraries, along with a bunch of code of its own.

This post originally appeared on the Crunchy Data blog.

Summarizing data against a fixed grid is a common way of preparing data for analysis. Fixed grids have some advantages over natural and administrative boundaries:

This post originally appeared on the Crunchy Data blog.

Open source developers sometimes have a hard time figuring out what feature to focus on, in order to generate the maximum value for end users. As a result, they will often default to performance.

Performance is the one feature that every user approves of. The software will keep on doing all the same cool stuff, only faster.

I was browsing through the logical replication restrictions when I noticed that a primary key was not mandatory to be able to perform logical replication. (see Postgres documentation) Actually, I was even surprised that it’s never been mandatory. I guess my brain was confused with other logical replication systems like slony. So, the next logical question here is : How does it work ? How does it picks a row to update in case there is no primary key ?

There's a new article out in Computer Weekly talking about CVE-2019-9193.

© Laurenz Albe 2020

Introduction

We know that PostgreSQL does not update a table row in place. Rather, it writes a new version of the row (the PostgreSQL term for a row version is “tuple”) and leaves the old row version in place to serve concurrent read requests. VACUUM later removes these “dead tuples”.

1. Overview

PostgreSQL is a great open source database developed and maintained by many great software engineers around the world. In each release, there are many features added to this open source database. For example, one feature is very helpful for developer is backtrace_functions introduced in PostgreSQL 13 , which allows a developer to dump out the backtrace when certain errors happened on the server. In this blog, I am going to explain it in a little bit more detail.

The community has spent a lot of time optimizing features over the years. Excellent examples include parallel query and partitioning which have been multi-year efforts to increase the quality, performance, and extend features of the original commit. We should consider the documentation in a similar manner. Just like code, documentation can sometimes use a bug fix, optimization, and/or new features added to the original implementation.