Monday, March 18, 2024

Postgres trick: Referring to columns dynamically within static SQL

The goal in this demo is to refer to columns dynamically, without the help of some other language dynamically generating SQL. This is so we can avoid repeated, copy-pasted expressions across multiple, similar columns. We want to write the expression only once, ideally.

The solution is a little complicated, and is definitely Postgres-specific. but perhaps a similar technique can be used in any SQL engine that supports similar JSON functionality.

So starting with this table to work with:

select * from t; id | url_path1 | url_path2 | url_path3 | url_path4 | url_path5 ----+-----------+-----------+-----------+-----------+----------- 1 | /a/a/a | /b/b/b | /c/c/c | /d/d/d | /e/e/e 2 | /f/f/f | /g/g/g | /h/h/h | /i/i/i | /j/j/j (2 rows)

The idea is to convert to JSON so that you can perform a function (in this demo, I’ll be simply concatenating a ‘/’ character) only once for several columns. I want to leave id as-is, but perform the concatenation on url_path*

Then we’ll convert back to relational records in the end.

I’ll be using the demonstrative technique of running one query at a time, putting each into a CTE for the subsequent step and building on that until the end result…

Step 1: 

Convert all columns we want to modify, i.e. all except for id, to JSON, then to records consisting of a key and a value...

We could alternatively do a positive match for something like 'url_path%', but - 'id' works as well for this use case.

postgres=# select id, jsonb_each_text(to_jsonb(t) - 'id') as url_path_kv from t; id | url_path_kv ----+-------------------- 1 | (url_path1,/a/a/a) 1 | (url_path2,/b/b/b) 1 | (url_path3,/c/c/c) 1 | (url_path4,/d/d/d) 1 | (url_path5,/e/e/e) 2 | (url_path1,/f/f/f) 2 | (url_path2,/g/g/g) 2 | (url_path3,/h/h/h) 2 | (url_path4,/i/i/i) 2 | (url_path5,/j/j/j) (10 rows)

Step 2:

Now let’s separate the keys and values into varchar columns, then concatenate a ‘/’ to the values.

Concatenating ‘/’ is the change to the values of url_path* that we are making. This could be replaced with something complicated that we don’t want to repeat 5 times.

with step1_kv as ( select id, jsonb_each_text(to_jsonb(t) - 'id') as url_path_kv from t ) select id, (url_path_kv).key as key, (url_path_kv).value || '/' as val_fixed from step1_kv; id | key | val_fixed ----+-----------+----------- 1 | url_path1 | /a/a/a/ 1 | url_path2 | /b/b/b/ 1 | url_path3 | /c/c/c/ 1 | url_path4 | /d/d/d/ 1 | url_path5 | /e/e/e/ 2 | url_path1 | /f/f/f/ 2 | url_path2 | /g/g/g/ 2 | url_path3 | /h/h/h/ 2 | url_path4 | /i/i/i/ 2 | url_path5 | /j/j/j/ (10 rows)

Step 3:

Convert back to JSON using jsonb_object_agg() which is an aggregate function that makes JSON, essentially

with step1_kv as (
select id, jsonb_each_text(to_jsonb(t) - 'id') as url_path_kv from t
),
step2_kv_separate as (
select id, (url_path_kv).key as key, (url_path_kv).value || '/' as val_fixed
from step1_kv
)
select id, jsonb_object_agg(key, val_fixed) as url_path_jsonb
from step2_kv_separate
group by id;

id | url_path_jsonb
----+--------------------------------------------------------------------------------------------------------------------------
2 | {"url_path1": "/f/f/f/", "url_path2": "/g/g/g/", "url_path3": "/h/h/h/", "url_path4": "/i/i/i/", "url_path5": "/j/j/j/"}
1 | {"url_path1": "/a/a/a/", "url_path2": "/b/b/b/", "url_path3": "/c/c/c/", "url_path4": "/d/d/d/", "url_path5": "/e/e/e/"}

(2 rows)


Step 4:

Convert back to relational records.


with step1_kv as (
select id, jsonb_each_text(to_jsonb(t) - 'id') as url_path_kv from t
),
step2_kv_separate as (
select id, (url_path_kv).key as key, (url_path_kv).value || '/' as val_fixed
from step1_kv
),
step3_back_to_json as (
select id, jsonb_object_agg(key, val_fixed) as url_path_jsonb
from step2_kv_separate
group by id
)
select id, r.* --back to relational records
from step3_back_to_json
cross join lateral
jsonb_to_record(url_path_jsonb) as r(url_path1 varchar, url_path2 varchar, url_path3 varchar, url_path4 varchar, url_path5 varchar);

id | url_path1 | url_path2 | url_path3 | url_path4 | url_path5
----+-----------+-----------+-----------+-----------+-----------
1 | /a/a/a/ | /b/b/b/ | /c/c/c/ | /d/d/d/ | /e/e/e/
2 | /f/f/f/ | /g/g/g/ | /h/h/h/ | /i/i/i/ | /j/j/j/

(2 rows)


Now, you could easily argue the complexity isn’t worth it...but consider if you had url_path1 through url_path99 and several complex expressions to apply to each. :) Could be worth it!

And I want to say it may be possible to do so without so many conversion steps. I’m kind of a noob with these JSON functions. Plus, I wanted to break the problem down into clear and separate steps for demonstrative purposes.

Anyway, that’s what blew my mind when I learned it was even possible to refer to columns dynamically with such a JSON trick!

Saturday, February 03, 2024

A Bonkers SQL Feature!

It’s time for a “Bonkers SQL Feature” post:

A (IMO) bonkers standard SQL behavior is that newlines between string constants cause implicit concatenation. Spaces between them do not. And if you try a similar thing but with column names or variables names (i. e. not constants), there is no implicit concatenation. Use two column names in this manner, and you end up with the second column treated as an alias for the first. :)

A quick demo follows:

/* newlines concatenate */
postgres=# select 'a'
'b'
'c'
;
 ?column?
----------
 abc
(1 row)

/* spaces do not concatenate - causes syntax error */
postgres=# select 'a' 'b' 'c';
ERROR:  syntax error at or near "'b'"
LINE 1: select 'a' 'b' 'c';
                   ^

/* let's test newlines but with column names */
postgres=# create table t (a varchar, b varchar);
CREATE TABLE

postgres=# insert into t values('aaa','bbb');
INSERT 0 1
postgres=#
postgres=# select a
b
from t;
  b
-----
 aaa
(1 row)


Per the Postgres documentation, the behavior defined by standard SQL is, and kudos to them for calling it bizarre:

Two string constants that are only separated by whitespace with at least one newline are concatenated and effectively treated as if the string had been written as one constant.

(This slightly bizarre behavior is specified by SQL; PostgreSQL is following the standard.)

A potential danger in this behavior is if you break up a list using newlines but forget to write a comma, such as:

/* basic IN check */
postgres=# SELECT 'foo' IN ('foo','bar');
 ?column?
----------
 t
(1 row)

/* here 'bar' is on the second line and I forgot to add a comma, so the check becomes `'foo' IN ('foobar')` essentially, resulting in false
postgres=# SELECT 'foo' IN ('foo'
postgres(#  'bar');

 ?column?
----------
 f
(1 row)

MySQL does similarly, BUT horrifyingly to me, they decided to change “Two string constants that are only separated by whitespace with at least one newline” to “Two string constants that are only separated by whitespace.” In other words, it takes this oddball behavior one step further and does it for spaces as well, despite what the standard says. (Although perhaps it makes more intuitive sense not to differentiate between different kinds of whitespace, standard be damned??)

MySQL:

/* newline test is consistent with Standard and Postgres */
mysql> select
    -> 'a'
    -> 'b'
    -> 'c'
    -> ;
+-----+
| a   |
+-----+
| abc |
+-----+
1 row in set (0.00 sec)

/* space test - WAT!! */
mysql> select 'a' 'b' 'c';
+-----+
| a   |
+-----+
| abc |
+-----+
1 row in set (0.00 sec)

/* column name test is the same */
mysql> create table t (col1 varchar(3), col2 varchar(3));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t (col1, col2) values ('aaa', 'bbb');
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> select col1
    -> col2
    -> from t;
+------+
| col2 |
+------+
| aaa  |
+------+
1 row in set (0.00 sec)

Not sure what else to say except...Be careful out there. :) 





 

Sunday, November 19, 2023

My r/sql comment on what do Oracle Materialized Views got that PostgreSQL ain't be...gotten?

Postgres materialized views barely do anything. I was so excited when they were announced [version 9.3 in 2013] until I actually looked into them. They are basically something I could implement myself in like 30 minutes with a few pl/pgsql functions (and maybe a table to store the necessary metadata). They just create a table based on a given query and you can refresh the whole thing on demand (like a truncate/insert). Then you can query the materialized view like any other table. If you want to refresh them automatically, you attach the refresh call to some cron job or perhaps in triggers on all dependencies (creating a mess IMO).

Oracle materialized views can do some pretty great stuff:

  • Automatically refresh: You don't need to manually call any refresh function (although you CAN if you want to).
    • Refresh on a schedule - Oracle has a built-in scheduler and materialized views are integrated with that, so I can say when I create my MV to "refresh every night at 2am" and it just works. Not a massive selling point, but it's convenient not to need some external scheduler such as cron.
    • Refresh when dependencies (base tables) are updated: Say I create a MV based on a query such as - SELECT x, y, z FROM sales JOIN country ... WHERE country.id IN (...) - now whenever an INSERT/UPDATE/DELETE is run on sales or country, the MV is automatically updated. No call to any refresh function anywhere is necessary. But it's smart about it too - in this case since I'm only concerned about a specific list of countries (in my where clause), if I update a row having a country.id NOT in that in-list, no refresh on the MV will be triggered. So basically it only refreshes when it has to. Your mileage may vary in complex use cases.
  • Query rewrite aka use an MV as an automagic index: This lets you create a MV as sort of an index. The query doesn't need to know about the MV's existence, but if the MV can answer the question the query is asking, the query optimizer automatically reroutes the query to the MV. Example: An application is issuing the query SELECT sum(qty*price) FROM sales but it's gotten pretty slow as it's recomputing that total for millions of sales every time it is run. I can create a MV sales_mv on that exact query, and the query rewrite feature will automatically take the query from sales to sales_mv without any modification of the query or application being necessary. Rewrite can even work in many cases where the MV's query isn't an exact match. For example if I modify the above query to GROUP BY state, the MV now has each sum(qty*price) per state. If I then run a query like SELECT sum(qty*price) FROM sales WHERE state = 'CA', the query rewrite feature is smart enough to know that the MV is a superset of the data I requested, and it knows which subset of the MV can be used to fetch my desired results.
    • I can even provide the MV with dimension metadata that can help the query optimizer in understanding when rewrite can be applied. If I have that same sales query but broken down by month - so something like: SELECT sum(qty*price), month_num FROM sales group by month_num - I can create a dimension that declares that months roll up in to quarters and which ones. So now if I ask the database SELECT sum(qty*price) FROM sales where quarter='Q2' - if I've defined my dimensions properly the query optimizer can figure out that Q2 can be determined by adding up the totals from month_nums 4, 5 and 6 in the MV - a minimal amount of work compared to summing from the detail level up - and it finds me my answer that way.
  • "Fast" refresh: (Oracle calls it "fast" although I prefer the term incremental refresh.) Say that sales MV refresh takes 30 seconds because there's a lot of data. If we have a trickle-in of about a sale per minute, we're spending a lot of time and energy refreshing this whole thing repeatedly - we spend 30 seconds of every minute refreshing. But imagine if you as a human being had the job of keeping track of a sales total on paper. Throughout the day, a salesperson yells at you about a new sale, "We just got another sale of $100!" - you wouldn't recompute your entire sales total from the entire history every single time; rather you'd take your running total - let's say it's $10,000 - and simply add $100 to it - so now it's $10,100. So, that's analogous to how "fast refresh" works - just apply the increments. This feature can be pretty huge in speeding up refreshes.
    • One of many, many use cases: Multi-row constraints: If you've ever wished you could apply some validation across multiple rows in your table, you might be able to accomplish that with materialized views. Since you can add check constraints on a MV, you could define an MV to run some aggregation, and place a constraint on the result of that aggregation. For example, you have a table representing retirement fund allocations. Each customer's allocations must add up to 100%. So you could create a MV on the query SELECT SUM(percent_allocated) AS total_allocated, customer_id FROM allocation_table GROUP BY customer_id -- add a check constraint that total_allocated must equal 100 and now no bad data can ever creep into allocation_table. Now this may be too slow and clunky if the full MV query needed to be refreshed every time the base tables are updated, but again...fast refresh!
  • Oracle also lets you choose to defer applying the "deltas" from a fast refresh to read time of the materialized views. So if I would rather not slow down the writes to the base tables, by dealing with MV refresh at that time, I can tell Oracle to automatically queue up those changes and hold off on applying them to the materialized view until the time at which I actually read from it. So it's a tradeoff - if I have a large reporting query backed by MV, the user might not mind if I add to it, let's say, an average of half a second to it at read time. That may be better than adding a few milliseconds to every write to the underlying tables, if I have tight business constraints on the writes. So this feature allows me to do that, and it's easy and declarative - type a few magic words and it just works.
    • Deferring applying deltas analogy: to continue with the total sales on paper analogy: this is like keeping track of that new $100 sale on a separate paper (let's call it the delta log) that lists each new sale. Maybe you're so busy you don't have the mental capacity to perform addition constantly. Another $100 sale comes in, so you jot "$100" on the delta log. A $200 sale comes in, you write $200. A $1042.59 sale comes in...etc. Now your manager asks, "Hey I need the current total now please." So at THAT point, you catch up on the math: you add 10,000 + 100 + 200 + 1042.59 = 11,342.59, the new running total. Manager thanks you, you toss out the delta paper and start with a new one, repeat.
    • Caching joins: Now think about using fast refresh in conjunction with query write - that's a pretty powerful indexing feature. You could use it to precompute joins. (Everybody complains about how JOINS are slow and painful in SQL - Oracle actually has multiple solutions to pre-join tables. MVs are one of them.) If I have a billion WIDGETs, each of which was made in a FACTORY (linked to by WIDGET.FACTORY_ID), maybe queries that join the two are getting slow. So I can precompute the joins and query rewrite will potentially be able to use the MV for my slow queries, without anyone having to modify a query. And whenever a WIDGET or FACTORY is updated/inserted/deleted, fast refresh applies only the deltas so the whole thing isn't refreshed.
    • So many potential use cases here. Sometimes I see the argument that Postgres has partial indexes, and Oracle doesn't, so isn't Oracle lame? For context, a partial index lets you create an index with a where clause to pre-filter out a bunch of data you don't care about for the scope of a query/queries. So for example CREATE INDEX my_index ON order(id, order_date, etc.) WHERE status = 'ACTIVE';Now if you have 99% inactive orders at any given time, the index can potentially be used to search the active ones more quickly. Great PG feature btw, but with Oracle you can accomplish essentially same thing by creating a MV: CREATE MATERIALIZED VIEW ... SELECT id, order_date, etc. WHERE status = 'ACTIVE' just make sure you enable fast refresh and query rewrite and it's pretty much the same thing. (You can even define the MV as "index organized" to make it physically shaped like a B*Tree index, rather than a heap table, to make it even closer to "the same thing.") Sure you could argue in this use case, the Oracle solution is more complex, but the point is this one feature can be applied in tons of use cases - I could come up with some all day. :)
  • Stale tolerance: this goes hand and hand with query rewrite. If I am using query rewrite, I can define whether staleness is tolerated. So imagine I am running SELECT sum(qty*price) FROM sales again, and I've defined it to refresh on a nightly schedule (not automatically when dependencies are updated). This means at any given moment during the day, the MV's contents may be "stale" if updates/inserts/deletes have happened since refresh. So I can decide on a per-session basis (if desired) if I'd like query rewrite to route me to the MV when the MV is stale. If I need real-time data, run the normal sum against the guaranteed-to-be-up-to-date sales table instead even though it may be slower. If last night's data is good enough, I declare that stale tolerated is OK and I get to use the MV that way.
  • Synchronized Materialized View Groups: Basically you can group and refresh many materialized views together to guarantee that they are in sync. Think atomicity.
  • Plays well with partitions/subpartitions.

There's more, but that's the gist.

 

Source (myself): https://old.reddit.com/r/PostgreSQL/comments/kexm13/people_who_used_other_databases_before_does/gg5m6cr/

My most upvoted r/sql comment of all time

I try to provide meaningful insight in my comments on r/sql...

But the funny thing is my upvoted comment of all time on that subreddit is simply: