Dia dhaoibh go léir a chairde - Morning all!

Morning

Oh God - he's still here... which clone is this?

:-)

red-gate.com/simple-talk/sql/t-sql-programming/… for an alternate perspective

also the related red-gate.com/simple-talk/sql/t-sql-programming/…

nice ones

He was active on the site: A-K

(Alex Kuznetsov, not Celko)

Has anyone tried/considered using/looked into free StackOverflow for Teams? Might be a nice idea - a kinda virtual water-cooler for those familiar with SO. However, before plunging in,

I have one major question - which went unanswered on the link "Where is the data/questions/answers hosted?"! If it's SO hosting it, then it's a strict no-no... I wouldn't mind firing up a SQL Server Express instance and using it internally, but outside hosting is not on!

@ypercubeᵀᴹ - I seem to remember an Alex Kuznetsov who was involved with MySQL corrupted database recovery - same guy or no? Searched but Alex and Kuznetsov appear to be common enough names...

MySQL recovery? I don't think so.

He was answering mainly SQL Server and db design questions, if I remember well

Maybe it's the name I have wrong...

author of Defensive Database Programming with SQL Server

Looks like the pdf is free to download from red gate: assets.red-gate.com/community/books/…

@ypercubeᵀᴹ - yep, my bad, it was Aleksandr Kuzminsky - (profile on dba.se - company).

@ypercubeᵀᴹ - an 11 year old book about SQL Server - not exactly manna from heaven - if there was an updated edition, that would be another thing...

it's about defensive programming mainly. You could use some of the points in other dbms as well.

prefixing columns with table name/alias for example. or checking how nulls act in constraints

See one of his answers here, where he uses a very clever trick with unique constraints and null (this one wouldn't translate to other dbms as it is) but is indeed cunning:

Morning

@ypercubeᵀᴹ - you refer to @Alex Kusnetsot's answer - but it's not present in the thread, nor is (AFAICS) any link to it...

@Vérace ? I don't follow

this is his answer: dba.stackexchange.com/questions/43823/…

Ah, not on form this morning - I was looking for the full name... `<Doh - slaps forehead à la Homer Simpson>...

yeah, he changed it to A-K some years ago

I wonder what he's doing these days. He was one of the people I was most excited to blog with on the SQLBlog.com site.

All I'll add is I've never encountered a start/end setup in the wild that did not violate a temporal constraint. A-K's approach is an interesting, easier way to keep the intervals in check, but IMO not necessary with the right approach (views/teaching people the right way to query version tables)

SQL Server has gotten really good with selecting the MAX row with conditions - used to be two index seeks per row, now just a single seek with a TOP operator if the CI is setup correctly.

@bbaird in Postgres it's easy to constraint intervals to be non-overlapping with an EXCLUDE constraint (a generalization of UNIQUE)

A-K's designs are interesting, yes. They allow to have them non-overlapping and without gaps if wanted.

@ypercubeᵀᴹ It's cool someone spent the time to do that so people might not screw it up as often, but it's another non-standard thing tacked onto Postgres (which I have reasons for not using when large amounts of data have to be versioned anyway).

My biggest issue I think with the start-end paradigm is it tries to "simplify" the conceptual representation of the thing (forcing everything to an interval), while the actual representation (point in time) is 1. far more elegant 2. plays nice with the RM

I found that question interesting - in particular that SQL Server considers NULL and a different NULL to be the same... didn't know that - real "gotcha"...

@ypercubeᵀᴹ I tried doing it with all sorts of shenanigans using PostgreSQL... but I did find a way with Firebird - SQL in DDL - Kewl!

Maybe a case for having a NOT VALID clause for UNIQUE indexes as there is for FOREIGN KEYs?

Re. temporal stuff - yes, I remember working for an airline which had a truly dreadful scheduling system (the guys in Ops used Excel instead) - we had aircraft taking off before they landed... crew scheduled for 3 flights at one time - we even had crew flying on two aircraft at once... amazing stuff - and they charged approx. €500K for the s/ware and €50K/annum for support - great work if you can get it!

@Vérace Ooof. My biggest headache is the DW putting the starttime > the end time for rows they are "undoing" and I'm like what?

@bbaird - it wasn't just scheduling - we had aircraft landing with more fuel than they took off with - and no, we didn't have an in-flight refuelling capacity! It wasn't Air America :-)

lol

Much of it was the fault of the pilots - highly skilled my arse - most of them couldn't tell the difference between a metric tonne and an lb (imperial pound unit)!

How many headaches could have been avoided with a good UI and some CHECK constraints on the database?

@bbaird - I was (trying) to write a flight-pay system from out of this data swamp - the staff table had 35K (yes, 35,000) fields (arrays of arrays of... - don't ask, I still have nightmares but the therapy helped!) - pilots were robbing the company blind by abusing the time zone reporting - everything was GMT but we had pilots doing Cuba - Cancún and their flights would straddle midnight GMT so they'd file for an extra days pay... (hmmm... maybe they weren't so stupid after all... just lazy...)

@Vérace That's worse than anything I've got... oof.

Airline - strict reporting requirements - each crew (flight or cabin) member had a 500 day window dividing into 2 with 35 fields each... or something like that... I wrote code that generated a 600,000 line Delphi function which normalised this nightmare - great thing was, it worked first time!

@bbaird Is that Jackie Chan?

@PaulWhite Yes

@Vérace only in UNIQUE constraints! But yeah, that's a remainder from the ancient era, possibly as back as Sybase ;)

@ypercubeᵀᴹ Does Sybase also allow SQL in DDL? Or is it NOT VALID? I'm really into the idea of having DDL being much more powerful - i.e. database assertions or database level constraints - it still astounds me that they haven't been implemented - except through triggers - I'd prefer to have much of that in the table definition.

@Vérace sybase treating nulls in UNIQUE constraints same as SQL Server

I don't think there are many DBMS that allow subqueries in CHECK constraints (if that's what you mean with "SQL in DDL")

DDL being a subset of SQL so I couldn't think of what else you meant ;)

@ypercubeᵀᴹ Link from above - somebody (you IIRC - or was it @PaulWhite?) showed how some of these (SQL in CONSTRAINTs or other DDL) can be subverted by subsequent UPDATEs, but not in this case... see here!

@Vérace I think you're referring to:

@PaulWhite Yes, it would appear that cross-table SQL doesn't work - but that SQL can still be pretty useful within a table... err... I tried this - basically the idea is that a manager can't lend over 100 (K, M....). The problem with the implementation is that the TRIGGER which is created in the background is a before TRIGGER, so it allows the SUM go over 100, but not over 200... see here ...

- maybe there should be a BEFORE, ON or AFTER keyword with SQL in CHECK constraints? It's an interesting problem.

@Vérace I don't like it.

CHECK
( NOT EXISTS
  (
    SELECT SUM(loan_amount) AS s
    FROM portfolio
    GROUP BY manager_name
    HAVING SUM(loan_amount) > 100
  )
)

No, no, no. Just no.

Code review: reject ;)

He seems not to like it 😀

@Verace that would likely be more easily and more robustily implemented with a ledger-type schema.

basically storing the total_loan_amount in every row, having a CHECK constraint that it is <= 100 and enforcing correctness with check and foreign keys.

Eg, (A-K strikes again): dba.stackexchange.com/questions/5608/…

Too bad you don't have actual transactions - super easy to enforce through procedures. AFAIK banks do not store running totals of your account, usually just prior statement balance + transactions since last reconcile

ugh, "BIG DATA" vendor meeting and the rah rah rah from the org

@ypercubeᵀᴹ Yes that's another nice example. It really does simplify a lot of things, including meaning one can stop worrying about race conditions.

@bbaird what do you mean with "Too bad you don't have actual transactions"?

I mean, this is trivial if you have try/catch rollback etc

Engine that handles the appropriate locks

I'm assuming you're doing stuff in PL/SQL or a derivative

No, the answer was for sql server. I have done something similar in Postgres. Haven't used Oracle in a professional manner (only simple questions in SO and dba)

So yeah, it is doable via transactions but the DDL adds an extra security element, that no app will ever violate the business rules, by not using proper transactions or using a NOLOCK, whatevr

@ypercubeᵀᴹ Sort of? I mean, assuming you're following the very unsecure/problematic pattern of letting each application determine how it interacts with/modifies the database as opposed to a single data layer

Which should just be... no, not allowed, bad, violates the DBA oath or something of the sort.

@bbaird there is a death eater somewhere lurking, waiting for the chance to hit ;)

oh god, someone is pushing for a data lake

@bbaird lakes and swamps aren't that different...

@ypercubeᵀᴹ - how do you get the previous values for your ledger? Could you point to a sample PG fiddle where what A-K did in the ledger post you pointed to? I'm a huge believer in using DDL to the hilt!

@Vérace with a SELECT TOP 1 ... ORDER BY something DESC ...

the link with the original article in sqlblog.com has examples: web.archive.org/web/20180422151826/http://sqlblog.com/blogs/…

@ypercubeᵀᴹ - OK- got it! But how does that stop the sum for a manager going over 100 in my Firebird example without using a TRIGGER?

@Vérace CHECK (total_loan <= 100)

ugh, this isn't training its a marketing seminar

I gotta write a stern email to some folks because this is NOT the first time I've spent 90 minutes going over a slide deck you can find on the vendor's webpage

Still curious who is going to create this "lake" considering we can't even build a DW correctly

See, the reason we need to build a lake is that the DW is incorrect

I've just been handed this urgent bulletin: The data lake is also incorrect because we really need to use a deltalake. This will clearly resolve the issue

I think the difference between a lake and a warehouse is the data is allowed to settle at the lowest point of the terrain (and thus can be pumped through pipes), while the warehouse keeps the data locked up in boxes.

Oh yes, can't have just a lake. Need the Delta Lake to fix the lake.

JSON-based transaction... log...?

Amusingly enough, my spouse is a civil engineer and has been arguing with the mechanicals about the location of their underground pipes for the warehouse they're working on because their site won't drain...

God help us

@billinkc lol

Deltalake does look pretty slick. delta.io The folks I've talked with that are using it are big fans without being fanbois over it.

@Vérace have fun: dbfiddle.uk/…

@ypercubeᵀᴹ - just in the middle of writing my own! :-) Will post tomorrow!

some minor correction: dbfiddle.uk/…

nice

@billinkc I can imagine maybe in an industry that is not insurance or an organization without 35 years of tech debt

@billinkc - how is this delta lake thingy any different to/better than the system formerly known as Presto, now known as Trino?

It appears to be virtually identical in functionality...? Never used either so am not sure... just an interested bystander!

No idea about presto/trino

The challenge I had conceptually with using files as storage was what to do with the data while we were processing it. Do we copy fileA out to temporary storage, "do processing", and then swap it back in? Do I try to have a compressed historical set of data and then hot data and make a view or something over it (with an eye toward whatever PDW/APS/Azure SQL DW/Synapse? is called now)

Deltalake seems to solve that problem