ads' corner

While attending a conference (yay) the discussion turned to system columns, and especially ctid. This was in context of backups, and the physical position of data on disk. The question was if that can be used to determine if an incremental backup is required, and if it is enough to just copy that field where the ctid points to. Not quite, but I’d like to use this question to describe the system columns more in detail.

In a PostgreSQL table one may find - depending on the server version - the following additional system columns:

• tableoid
• xmin
• xmax
• cmin
• cmax
• ctid
• oid

Most of the columns exist in every table, but are excluded when doing a SELECT *. However one can include the column(s) in a query, and then PostgreSQL will return the values for the columns as well.

 

 

Continue reading "ctid and other PostgreSQL table internals"

Together with Lætitia Avrot and Nikolay Samokhvalov I was invited to participate in a Community Panel (YouTube video) about PostgreSQL Upgradability at Postgres Vision 2022. The panel was moderated by Bruce Momjian and initiated and organized by Jimmy Angelakos. Bruce did talk with each of us before, which helped a lot to guide the discussion in the right direction. The recording of the panel discussion is available on the Postgres Vision website.

During this panel each of us provided examples for how easy or complicated PostgreSQL upgrades still are.
 

 

Continue reading "PostgreSQL Upgrades are hard!"

As is a good tradition, the PostgreSQL Project participates in Google Summer of Code (GSoC). Last year we submitted 7 projects for 7 students - and got all 7 projects accepted. This year we got quite a few more good proposals from students, and more mentors are helping. Guess what? Google accepted all 12 proposals!

Google modified the program again. For 2021 they cut the time for every project in half, to accommodate for the at-home work during the Covid-19 pandemic. This turned out to be suboptimal, and many larger projects need more time. This year students can choose between “medium” (175 hours) and “large” (350 hours) projects. This gives everyone a chance to scope the project accordingly.

 

 

Continue reading "PostgreSQL Project @ GSoC 2022"

For one of my Ansible Playbooks I need the group name associated with a specific group id. Turns out there is not much documentation online how to do this, and I had to test it out.

 

Continue reading "Ansible: Lookup group name by gid"

The one thing I like to do is challenge PostgreSQL - and readers - to the extreme. A while ago I posted about the Beer o'clock. Building Zoé was another interesting example. And at some point I blog about Dancing with the Stars as well. Today it's all about nothing. Empty. Where is all the data gone?

A while ago I posted this screenshot on Twitter, and as far as I can tell no one found the correct answer.

The screenshot looks like there's nothing. Empty names, schema name, role name, database name, even no data. And yet this all comes together and somehow works. Let's dig in.

 

Continue reading "My PostgreSQL database is empty!"

For the "PostgreSQL Person of the Week" interviews I'm using Hugo as static blogging engine. Part of every interview is the list of tags, which links this interview to other similar interviews. However until recently no one really knew if a tag is popular or just used in this interview. I wanted to change this, and add the tag count behind every tag.

The way I use Hugo I have the previews also online, and I don't want to count any interviews which are not published (still drafted) when counting entries for a tag.

Tags in Hugo are taxonomies, which is a user-defined grouping of content.

 

Continue reading "Hugo: Count entries in Taxonomy"

My problem: sometimes I forget to review published interviews for a talk.

I publish a weekly interview series, the "PostgreSQL Person of the Week" interviews. The data lives in a big spreadsheet, which has several sub-sheets.

The first one, the main sheet, is holding the overall status of all interviews, and has one column which indicates if the interview is complete.

Another sheet, the fifth one, holds the statistics data for my talk about what I learned interviewing the community.

The first column in the fifth sheet is copying the name from the first sheet. The second column indicates if I already looked over the interview and gathered all the statistics. And this is where today's problem came up: whenever I finished an interview (over 100 of them already) I marked this second column with a question mark ("?") to let myself know that at some point I need to read the interview again. But of course I don't always remember to do that.

My idea was to color the background of this cell, based on the value in the first sheet: make the cell background green when the interview is completed. This gives me an instant indication if I need to work on this interview or not.

Turns out this is a bit complicated, but possible.

 

Continue reading "Google Sheets: Format cell background based on a value in a cell in another sheet"