Every now and then I see something like this:
SELECT u.* FROM users u WHERE 0 = (SELECT COUNT(*) FROM addresses a WHERE a.user_id = u.id);
and it kinda pains me. So figured, I'll write about what is the problem with it, and how to avoid such constructs.
Continue reading SQL best practices – don't compare count(*) with 0
This question happens every now and then in one of PostgreSQL support places.
Whenever it happens, I just suggest to not try, as interacting with outside world from database can be problematic, and instead use LISTEN/NOTIFY.
But it occurred to me, that while I know how to do it, I don't think I actually did it. It being: handle listen/notify in real life code. So let's try. I will not be writing actual email sending or http requesting, but will make sure that my program will get, from database, information when NOTIFY happens. How hard could it be?
Continue reading How can I send mail or HTTP request from database?
Based on checking logs, and my own personal needs I added more categories of keywords to pgdoc.link:
This brought the total number of known keyword to 2410.
As a side note – while I generally like PostgreSQL docs, state of contrib module documentation is …, well, challenging. Every doc has its own approach to listing stuff. That was “fun" to work on, and it's the main reason why I'm sure not all functions/modules are handled. If you will notice something that isn't – please let me know, I'll do my best to add it.
Was asked recently about optimization of interesting case. There was table like:
=$ CREATE TABLE input_data ( category_id INT8, object_id INT8, interaction_ts timestamptz, interaction_type TEXT, interaction_count INT4 );
And there was a code that was grouping it all by sum()ing interaction_count per category, object, interaction_type, and timestamp truncated to hour.
Basically, storing somewhere result of:
=$ SELECT category_id, object_id, date_trunc( 'hour', interaction_ts ) AS ts, SUM(interaction_count) FILTER (WHERE interaction_type = 'a') AS a_count, SUM(interaction_count) FILTER (WHERE interaction_type = 'b') AS b_count FROM input_data GROUP BY 1, 2, 3;
While talking about optimizations, one idea that came was to store whole day of counts in single row, as array. So the resulting count table would be:
=$ CREATE TABLE results ( category_id int8, object_id int8 interaction_day DATE, a_counts int4[], b_counts int4[] );
Where a_counts, and b_counts would always have 24 elements, one for each hour.
Now, how to roll it up like this?
Continue reading Grouping data into array of sums – fun with custom aggregates
Checking logs for pgdoc.link I noticed that some people where searching for system views, like pg_stat_activity, or pg_stat_all_tables in Pg 9.3.
Now, these will work. This increased total number of known keywords from 1840 to 1883. Not much, but it's a progress 🙂
On 1st of November 2024, Michael Paquier committed patch:
Add SQL function array_reverse() This function takes in input an array, and reverses the position of all its elements. This operation only affects the first dimension of the array, like array_shuffle(). The implementation structure is inspired by array_shuffle(), with a subroutine called array_reverse_n() that may come in handy in the future, should more functions able to reverse portions of arrays be introduced. Bump catalog version. Author: Aleksander Alekseev Reviewed-by: Ashutosh Bapat, Tom Lane, Vladlen Popolitov Discussion: https://postgr.es/m/CAJ7c6TMpeO_ke+QGOaAx9xdJuxa7r=49-anMh3G5476e3CX1CA@mail.gmail.com
Continue reading Waiting for PostgreSQL 18 – Add SQL function array_reverse()
PostgreSQL documentation is, generally speaking, great. But it isn't the easiest thing to search in. Over the years I memorized urls to certain docs, but there is a limit to it.
What's more, there are certain inconsistencies. For example – most pages that describe program have name that starts with app-. But not all. Some programs have names that start with pg_, and some don't have this _ thing. Their docs usually get rid of _ in file name, but, again, not always, sometimes it's changed to –.
I've been aware of all these things, and even tried to look for a way to fix it in docs, but never could get around to learn docbook.
So, figured I can provide a tool that will search the way I think it should…
We recently hit an interesting case where planner picked wrong plan. Kinda. And figured it could be used to show how to deal with such cases.
Continue reading Case study: optimization of weirdly picked bad plan
Amazing. Awesome. Well, but what is it? We could store json data in Pg since PostgreSQL 9.2 – so it's been there for over 12 years now. How is the new shiny thing different? What does it allow you to do?
Let's see if I can shed some light on it…
Continue reading SQL/JSON is here! (kinda “Waiting for Pg 17")
On 17th of September 2024, Peter Eisentraut committed patch:
Add temporal FOREIGN KEY contraints
Add PERIOD clause to foreign key constraint definitions. This is
supported for range and multirange types. Temporal foreign keys check
for range containment instead of equality.
This feature matches the behavior of the SQL standard temporal foreign
keys, but it works on PostgreSQL's native ranges instead of SQL's
"periods", which don't exist in PostgreSQL (yet).
Reference actions ON {UPDATE,DELETE} {CASCADE,SET NULL,SET DEFAULT}
are not supported yet.
(previously committed as 34768ee3616, reverted by 8aee330af55; this is
essentially unchanged from those)
Author: Paul A. Jungwirth <pj@illuminatedcomputing.com>
Reviewed-by: Peter Eisentraut <peter@eisentraut.org>
Reviewed-by: jian he <jian.universality@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.comContinue reading Waiting for PostgreSQL 18 – Add temporal FOREIGN KEY contraints