Neues vom PostgreSQL Planet

© Laurenz Albe 2022

I have been faced with a request for an unusual unique constraint that puzzled me for a while. Since the solution I came up with is a nice show-case for range data types, I’ll share it with you. Also, it allows me to rant some about NULL, which is a temptation I find hard to resist.

The problem: an unusual unique constraint

We have a table like this:

CREATE TABLE tab ( a integer NOT NULL, b integer );

Now we want to make sure that the combination of a and b is unique in the following way:

Sometimes, we see Customers curious about the features of PostgreSQL and see if the features similar to Oracle exist in PostgreSQL. One of such features is the capabilities of Rebuilding an Index ONLINE.

FZF is quite popular tool for fuzzy string finder. Very helpful for checking history of commands. But, can I use it in psql? Initially, the tests seemed promising. Within psql, I can: =$ \set x `fzf < :HISTFILE` And then I can: $ :x age ────────────────────────────────────────── 14 years 11 mons 28 days 14:16:01.493027 (1 row) … Continue reading "Can you use fzf for psql history?"

PostgreSQL Person of the Week Interview with Jim Chanco Jr: My name is Jim Chanco Jr, I’m 35 years old, and I’m a father of 2 (Malachi 13, Isabelle 3). I live in Clayton, North Carolina, but I’m originally from Charleston, South Carolina.

Welcome to the 5th installment of the #PGSQLPhriday blogging series. I am thrilled to be this month's host! The topic posts should be published by Friday February 3rd.

On January 17, Chicago PUG started the new year by welcoming Ryan Booz. Ryan’s presentation Successful Database DevOps with PostgreSQL. Although I already thanked Ryan and want to thank him again – the presentation was phenomenal! We had an extended discussion after his talk, and even the next day, people in my office kept talking about it! I should mention that there was a poker tournament in the room adjacent to the one where we had our meetup. A couple of people migrated to our room to listen to the presentation!

A simple way to make more attractive the PostgreSQL command line interface!

Authentication

Authorization Verify that the user is who they claim to be. This can be through password, trust, or some other federated login (like Kerberos) As an authenticated user, what am I permitted to do within the system?

Before we get started, lets establish a few terms:

What is a database job scheduler? Essentially, a job scheduler is a process that kicks off in-database functions and procedures at specified times and runs them independently of user sessions. The benefits of having a scheduler built into the database are obvious: no dependencies, no inherent security leaks, fits in your existing high availability plan, and takes part in your data recovery plan, too.

Postgres is one of the most widely used databases and supports a number of operating systems. When you are writing code for PostgreSQL, it’s easy to test your changes locally, but it can be cumbersome to test it on all operating systems. A lot of times, you may encounter failures across platforms and it can get confusing to move forward while debugging. To make the dev/test process easier for you, you can use the Postgres CI.

After looking at the implications of the replication factor for a YugabyteDB cluster, this blogpost looks at how a table "materialises" in a YugabyteDB cluster.

The creation of a table in YugabyteDB YSQL first of all adds the table metadata to the PostgreSQL catalog, exactly like it does in PostgreSQL. This data (the metadata) is stored on the master.

Disclaimer

This article will contain spoilers both on how I solved 2022 Day 13's challenge "Distress Signal" using SQL, as well as general ideas on how to approach the problem. I recommend trying to solve it yourself first, using your favorite language. This article is delayed from the actual puzzle's release. Also note that my solutions are seldom going to be the "best" solutions - they were solved as quickly as possible, and these articles will show my first solutions, with some minor reformatting and cleaning up.

There’s a particular anti-pattern in database design that PostgreSQL handles… not very well.

For example, let’s say you are building something like Twitch. (The real Twitch doesn’t work this way! At least, not as far as I know!) So, you have streams, and you have users, and users watch streams. So, let’s do a schema!

CREATE TABLE stream (stream_id bigint PRIMARY KEY);

CREATE TABLE "user" (user_id bigint PRIMARY KEY);

Hi, friends and colleagues in Chicago, and those who plan to attend the PG Day Chicago on April 20! We are looking for volunteers, and there are many options available. I know that the volunteer sign-up was not working before, but now it is fixed, and you can sign up here: the link to the volunteer form.

One of the most frequently reoccurring questions that we get at YugabyteDB how to interpret the information that YugabyteDB provides when people are testing (high) availability. This is a summary of that.

usql? But why?

usql is a universal command-line interface for many database(repositories). But why are we still using CLI (command line utilities) in the 21st century? And what is wrong with psql?

How to correctly detect an SQL NULL value in PL/Perl.

I know that many people are reluctant to admit they made some stupid mistakes (like accidentally dropping a table in production). Some of these mistakes are very visible to the rest of the world, like the infamous Amazon S3 rm *, but raise the hands those who never ever did anything like this?! That’s why I always try to share my “oops,” in hope that it will make many people feel better.

Now, here is my recent story.

PostgreSQL Person of the Week Interview with Miroslav Šedivý: My name is Miroslav Šedivý, but most people call me Miro, which allows them to avoid typing some letters they do not have on their keyboard. I was born in Czechoslovakia (yes, I am over 30 now), studied computer sciences in France and Germany, and now I am living in Austria.

Previously, I wrote that you should never lock tables. And you usually shouldn’t! But sometimes, there’s a good reason to. Here’s one.

When you are doing a schema-modifying operation, like adding a column to a table, PostgreSQL needs to take an ACCESS EXCLUSIVE lock on the table while it is modifying the system catalogs. Unless it needs to rewrite the table, this lock isn’t held for very long.