The Heap™ – Consultancy ©®

Zikato

06:20

Morning

McNets

07:01

Morning

John K. N.

07:57

Morning

77

Q: Upcoming cleanup of duplicate votes

It was reported on MSO that it is possible for a user to upvote or downvote more than once on a single post (resulting in reputation changes for the author for each vote). After some data examination, we were able to confirm that this vulnerability exists (it is happening due to race conditions s...

2

Paul White

10:32

Seems weird. A race condition that allows for duplicate voting? I guess this is what happens when you try to enforce data integrity in the application instead of the database?

4

ConcernedOfTunbridgeWells

@PaulWhite Eventual consistency in action.

Paul White

I get the joke, but it's not even that. Pure garbage-in garbage-out scenario.

Zikato

but think about the performance!

Instead of fixing the error or preventing duplicate data, we will use APPROX_COUNT_DISTINCT to check the number of votes and hope it evens out

Paul White

...and run a huge daily job to scan the entire table looking for, and removing, dupes

ConcernedOfTunbridgeWells

Or, you know, a unique index.

Paul White

10:47

a plethora of fix-up bodge jobs is the hallmark of a well-designed system

@ConcernedOfTunbridgeWells a filtered one because some vote types can be repeated

ConcernedOfTunbridgeWells

Last time I heard the core workflow engine I wrote on DQPro hadn't been touched since 2015 when I wrote it.

Paul White

and/or migrated to the community user when a user is deleted but votes preserved

ConcernedOfTunbridgeWells

The devs swear it's not because they can't understand my code.

Paul White

regardless, the whole SO schema is not a great example of db design

ConcernedOfTunbridgeWells

@PaulWhite Could one not just have aliased the users on deletion?

Rather than physically deleting the user?

Paul White

10:49

@ConcernedOfTunbridgeWells better things are not possible in this world

ConcernedOfTunbridgeWells

6-8 weeks, right?

Paul White

foundational principle

Zikato

My current focus is automated testing and deployment of DB code. Because I can't rewrite the queries and add indexes until the end of eternity.

Paul White

anyway it seems technical debt is a real thing after all

Zikato

Sometimes you have to make the schema changes which are inherently risky.

On second thought, I should just make Erik do it. Save myself some time

Paul White

10:52

right

ConcernedOfTunbridgeWells

@PaulWhite Technical debt is also an accumulation of risk as well as baggage. It's not debt in the sense of an overdraft or mortgage. It's more like owing money to the mob - if you don't pay it down they eventually come round and break your legs.

Paul White

yep

Zikato

wow, that's a really good metaphor. Consultants are doing racketeering :)

Paul White

It's fun sometimes to pretend that real databases were designed at all, rather than just coming into existence as raw data storage in a rush to get some sort of product to market.

As evidence, vaguely points at Database Administrators

Zikato

Is there any other way?

Paul White

10:57

dba.stackexchange.com/users/63644/mdccl?tab=answers

Zikato

If you're a startup, you don't have the money to pay experts. When you are successful, you focus on expansion and not tech debt. It's gotta take a near-death experience to make it a priority

anecdotally of course. I haven't done any research

Paul White

Yes I know why the world is fucked up

I'm just stating that it is

No one really cares so long as they get paid

Including the mobsters

I bet the job that will do duplicate vote cleanup will run with NOLOCK

For performance

ConcernedOfTunbridgeWells

@PaulWhite Don't talk to me about NOLOCK - finally got one of the teams to clean up 1,400 NOLOCK hints in their ETL codebase, and it took the enterprise architect threatening to withhold signoff to get them to do it. Note that this happened on an IFRS17 project feeding their general ledger and external reporting.

Muppets.

Oh well, where there's muck, there's brass. Keeps us in a job, I suppose.

Paul White

11:15

Still makes me want to go farm chickens instead

ConcernedOfTunbridgeWells

11:26

@PaulWhite I guess 'Middle Manager Nuggets' just doesn't have that ring to it.

John K. N.

12:06

@PaulWhite @billinkc would approve

Paul White

yeah I guess so!

ConcernedOfTunbridgeWells

Probably a bit rank and gamey anyway.

Erik Darling

@PaulWhite you rang?

Paul White

a mobster appears

Zikato

Ha, another hidden chat functionality I've learned. Hovering over the mention shows the referencing message.

In a year or two, I'll be a chat pro.

Paul White

12:11

I'm told it's not hidden it's intuitive

That's what my invisible Apple manual claims anyway

Erik Darling

saves a tree or two

Paul White

whole forests saved by the lakh of a headphone port and charging adapter

ConcernedOfTunbridgeWells

Blackadder interrogates Darling

►

Zikato

@PaulWhite I do not possess an engineering degree, nor am I technically inclined.

Paul White

that may be the least surprising message ever posted in this room

Erik Darling

12:16

saying things like that might incite a competition

Paul White

655,040 messages in here since 3 January 2011

no way I'm reading all that

ConcernedOfTunbridgeWells

Captain Darling

►

John K. N.

@PaulWhite +10 points

ConcernedOfTunbridgeWells

Still looking for the compilation of Stephen Fry calling Capt. Darling but it's escaped my google-fu.

John K. N.

GDPR maybe? The right to forget....

ConcernedOfTunbridgeWells

12:22

@ErikDarling - depending on which side of the Atlantic you come from, these may or may not make a lot of sense.

John K. N.

Actually that should possibly have read: "The right to have been forgotten."

Paul White

Stephen Fry was far and away the best character in Blackadder. Maybe Lord Flashheart came close.

General Sir Anthony Cecil Hogmanay Melchett, VC KCB

Even the name is funny

Erik Darling

@ConcernedOfTunbridgeWells rob farley used to do this bit on me when i'd see him

i sort of get it

Paul White

Hard to believe Farley does stand up these days

Still, Australians I suppose

ConcernedOfTunbridgeWells

@ErikDarling If you haven't seen Blackadder it was pretty good, particularly season 2 and 4.

Zikato

12:27

All but season 1 is great. Season 1 is just good

I should probably start my 5th rewatch

Paul White

Blackadder - General Melchett (Stephen Fry), the Exclamations - 'Baaah!'

►

Quite a lot of subtle humour that might not translate to American

ConcernedOfTunbridgeWells

Could be worse. Could be Are you being served?

Or maybe Bottom

Bottom Ultraviolence

►

Paul White

12:44

OMG yeah

Erik Darling

12:55

I just usually don’t have time to sit and watch stuff

Occasionally a movie

Paul White

Nobody does. That's why everyone's quoting stuff from years ago

Zikato

You've missed your window

ConcernedOfTunbridgeWells

Plus, I can't think of many really noteworthy sitcoms that have come out recently.

Some folks rave about The Big Bang Theory but it left me cold.

Paul White

Jokes are illegal now anyway

Zikato

13:35

Have all good questions already been asked?

ypercubeᵀᴹ

13:49

@PaulWhite yey to that!

Hannah Vernon

14:11

so I thought to myself, perhaps it would be helpful if I had a visual model of one of our main databases.

apparently that isn't helpful really.

each pixel at the end those lines is a table - I mean wow.

Paul White

yeah that's not super-helpful is it

Hannah Vernon

not very

I mean it's only 589 tables, so not overly huge. I wonder how it looks if it was printed on a large format plotter. Still not readable, I imagine.

@PaulWhite that's some next level sarcasm right there. The transcript for this place is golden.

Peter Vandivier

14:32

Hannah Vernon

@PaulWhite a veritable mine there

@PeterVandivier lol!

Peter Vandivier

man, leave chat for like 6 months and completely forget all the weird markup inconsistencies

Hannah Vernon

@PeterVandivier I love that every single chat app ever built is slightly different like that

there you go!

Peter Vandivier

lol thx

Paul White

yw

Hannah Vernon

14:50

this is the user table, which only has 431 foreign key relationships. Oh and all tables (nearly all) have the last 5 columns listed, which is great fun.

and the reason for the table having 431 foreign key relationships directed at it.

I'm not sure, but I think deleting a user might be a fairly expensive process.

to print it out at 100% zoom level would take 336 48x36 inch plotter sheets.

and they say size doesn't matter

I think it's likely a good thing they didn't mention any of this in the job interview

ConcernedOfTunbridgeWells

Data flow model from something I just reverse enginerred.

ypercubeᵀᴹ

what's worse, 431 foreign key relationships or none at all?

3

ConcernedOfTunbridgeWells

Partial. There are more tabs.

mustaccio

This place is turning into a whine cellar

Hannah Vernon

@ConcernedOfTunbridgeWells yours is definitely bigger than mine

@mustaccio passiveaggressivenotes.com

ConcernedOfTunbridgeWells

15:01

@HannahVernon Needlessly so, I think. It's the preliminary ETL for a general ledger feed on an IFRS17 programme. It's the worst case of enterprise fizzbuzz I've ever seen.

And really badly implemented. £12 or 15m depending on who you ask, and none of the decision makers have any background in large technology projects.

'We're non-technical.'

Hannah Vernon

wow

ConcernedOfTunbridgeWells

'We'll make a business decision.'

It's a candidate for the most incompetently managed project I've ever seen. And I mean incompetent in true sense of the word. Even the consultants from a nameless big-5 firm have no background in large technology projects.

Dunning-Kruger effect in action.

They don't know what they're doing and lack the cognitive faculties to realise they don't know what they're doing.

Hannah Vernon

mmm tasty. The greatest thing about the Dunning-Kruger Club is that you don't know you're a member.

ConcernedOfTunbridgeWells

Indeed.

It's terribad.

Paul White

ironically, people usually think they understand D-K better than they do

Hannah Vernon

15:06

what are you saying

ConcernedOfTunbridgeWells

I'm in the top 5% of people understanding the Dunning Kruger effect.

Paul White

@ypercubeᵀᴹ what are your thoughts on a design that adds 'created_by_user_id` to almost every table and FKs each one to the users table

I suppose it allows them to say they've used foreign keys in db design on their CV

4

and worked with 'complex designs'

ypercubeᵀᴹ

@PaulWhite I don't know, my guess would be some kind of multitenant database or auditing requirements. all paths leading to lunacy ;

Paul White

@mustaccio ^

Erik Darling

15:55

i don't wanna.

Paul White

Have some moist mayonnaise

Erik Darling

*moist

Moist Mayonnaise Baked Cod Recipe Will Make a Fish Lover Out of Anyone

Paul White

I had never heard of the thing before I saw your tweet

Oh I see

Sounds awful

I wonder who thought that'd be a good idea

Josh Darnell

AMERICANS

Paul White

Seems possible yeah

Josh Darnell

16:03

I enjoy a good chocolate mayonnaise cake.

Paul White

@JoshDarnell How have you lot not been invaded yet

Baked fish, mayonnaise, butter, and onion surprise

Servings: Infinite because no one in their right mind

Also what sort of measurement is a "cup"

What's next lamb baked in ketchup?

Peter Vandivier

@PaulWhite we were, by the Canadians. but they were so horrified that they retreated and closed the border

Paul White

I see

Peter Vandivier

shame really. they just didn't realize that butter:America::Maple Syrup:Canada

Josh Darnell

My wife and I made grits yesterday with breakfast. Butter, cream cheese, and heavy cream were all added during the cooking process.

Paul White