Tag: changes

Who logged to system from multiple countries in 2 hours?

Yesterday someone posted a set of queries for interviews, all centered on answering business-like questions from database.

Today this post is hidden behind some “subscribe to read more" thing, so I will not even link it, but one question there caught my eye.

Since I can't copy paste the text, I'll try to write what I remember:

Given table sessions, with columns: user_id, login_time, and country_id, list all cases where single account logged to the system from more than one country within 2 hour time frame.

The idea behind is that it would be a tool to find hacked account, based on idea that you generally can't change country within 2 hours. Which is somewhat true.

Solution in the blogpost suggested joining sessions table with itself, using some inequality condition. I think we can do better…

Continue reading Who logged to system from multiple countries in 2 hours?

Configuration changes across Pg versions

Back in 2018 I wrote why-upgrade.depesz.com – aggregator of changelogs between various versions of Pg.

Want to know what you will get when upgrading from 12.1 to 12.9? Here you go. Longer time changes? Like, from 9.5.20 to 14.1? I've got your back.

It even has a way to list every change that relates to anything related to indexes.

Today I updated the code, again to show which GUCs (configuration parameters) have changed between given versions. For example, getting diff from 13.5 to 14.1 shows you that:

  • two parameters were removed, and are no longer there
  • there are 17 new parameters
  • three parameters had their default values changed

And each GUC that is listed is (well, is supposed to but in some cases it can't) linked to relevant part of docs that describe what it is.

To make it work I compiled every version of Pg, since 7.2 (there have been 410 of them!), and extracted list of config params, and their default values.

Then, I fetched docs for all major versions of Pg, and extracted list of documentation fragments that relate to each config parameter.

This will require more work on each subsequent release, but I think I can manage it.

Any way – hope you'll find it helpful.

Waiting for PostgreSQL 10 – Implement syntax for transition tables in AFTER triggers.

Another one missed, quite a long time ago, too..:

On 4th of November 2016, Kevin Grittner committed patch:

Implement syntax for transition tables in AFTER triggers.
 
 
This is infrastructure for the complete SQL standard feature.  No
support is included at this point for execution nodes or PLs.  The
intent is to add that soon.
 
As this patch leaves things, standard syntax can create tuplestores
to contain old and/or new versions of rows affected by a statement.
References to these tuplestores are in the TriggerData structure.
C triggers can access the tuplestores directly, so they are usable,
but they cannot yet be referenced within a SQL statement.

Continue reading Waiting for PostgreSQL 10 – Implement syntax for transition tables in AFTER triggers.