I was hoping for a big red fireball under those clouds but I'll take what I can get

what's the use-case @U052852ES ?

At least Postgres is very widely used, so more Postgres details will very often be good for your resumé 🙂 Ignoring that you forgot about the details later on 😄

The system I'm working on has about 3.5K triggers and are interconnected in various ways.

and I have to add/change triggers so that we can take part of the compute problem they are currently doing in recursive SQL queries in an external Rust process.

fun

😱 @ 3.5K triggers - i hope they have been very disciplined about controlling and documenting interactions!

what?

I don't know what that is?

but yes, spaghetti code would be a kind description of this system. Large parts of the business logic is in SQL.

as in that's an awful lot of triggers - and triggers can presumably trip other triggers, which sounds like a recipe for a difficult to understand system

and just updating the triggers takes about 20 minutes on a large system.

yup

why did they choose to use SQL to implement business logic @U052852ES?

No idea... this happened years ago and then it just grew larger and larger.

a grey-goo system!

yup, thousand of lines of SQL. Love it!

declarative and functional

I’m learning that where (foo = ANY($1)) or (bar = ANY($2)) is really slow given large $1 and $2

even with indexes on foo and bar

are $1 and $2 sub-queries @U04V5VAUN?

No, they’re arrays

We’re migrating from mongo to postgres, so we have a ton of clojurespace joins

how big are $1 and $2 ?

can be tens of thousands…

ok, so not massive... i woulda thought that could have been optimised like a hash join... i.e. index scan with hash-tables from $1 and $2

does it get quicker if you put $1 and $2 into temp tables and do an explicit join ?

haven’t tried that.

In my (very limited!) experience, handling arrays just leads to wishing you'd done a relational table instead.

The only advantage that I know of with arrays is that when fetching data, you don't have to do an explicit join to get the equivalent of an array via tables. But you lose out on a bunch of readily available and better understood table-oriented SQL.

Always ready to get schooled, though 🤓 hope you figure out your problem, @U04V5VAUN

Yah, a join would have been a lot better, but it’s not possible atm 😕

@U04V5V0V4 but it isn't composable!

iirc a sql function can be named and can call others, so why not?

yes, functions can call each other. But in my experience you can't (or it is difficult) to just combine code and glue it together. ymmv of course

Can you write tests for SQL functions? :thinking_face:

not that I am aware of? But who know?

i guess the difficulty with composing sql fns is managing side-effects - they are mostly not very pure

kinda depends on whether they are contained within the same transaction ie is the shared mutable state updated consistently?

but yes, in practise it's usually a sht1sh0w cos nobody cares about this stuff 🙂

yup, and the developer experience kinda sucks IMHO.