Morning

@ypercubeᵀᴹ I've seen the comment and I deleted the comment

Hi BTW

Morning

twitter.com/tarynpivots/status/1454064513318207495?s=21

Wow, someone screwed up

all the sites are down

Yip - just noticed that! Well, it's a lazy Sat. morning in the good ol' Yew-Ess-of-Eh - why not open a maintenance window?

@EvanCarroll - I C that you are asking C questions! :-) Still trying to beat 21ms? I had a thought though - I used an NVMe drive - what sort of drive did you use for your 68.741 ms - if it was a SATA SSD, that might account for the difference? Maybe I could try your solution on my home machine to ensure a level pitch?

Seriously though, I don't know if your C question will get a lot of traction here - there are what, maybe ~ 5 PostgreSQL contributors who post here - maybe 2/3 regularly? You might be better off asking on the PostgreSQL mailing lists - even the (hushed deferential tone) Tom Lane regularly answers emails there!

@Vérace no, I'm beating it already.

@Vérace I'm 100% sure the only difference you're seeing is because I'm on a laptop on battery power ;)

Good for you! On what sort of drive?

@Vérace because I'm literally as fast as generate_series on my computer.

Oh, so you type in the answer?

But there are two ways you can write an srf in PostgreSQL, so, I'm now trying to write it the other way too. ;)

You can write it in PL/pgSQL!

I'm going to answer that question now. I've now wasted enough time to called a slice of my life "Verace's stupid benchmark" on my life's pie chart.

Great - I look forward to implementing your solution! Sorry about your wasted life! :-)

nah it's a ton of fun for me because this is pushing the boundaries of what I know. So that's cool. That doesn't happen all that often with Pg.

Actually it's probably worth disclaiming that I'm going to self-answer a lot of questions on PostgreSQL today, so please support them or don't down vote them.

I have a lot of information that I've collected from different people on this that isn't written down but that needs to be given back.

3 years after writing my first C extension in PostgreSQL I'm returning sets. In another 3 years maybe I'll be writing indexes.

This is the slow progression and dive that open source permits. You never know where you'll go or how far the path takes you.

You can assert though that there are no ceilings with open source, and you can't do any of this with Oracle or SQL Server.

Actually, I think I can do this with SQL Server too, but that would get super-downvoted. They do have native-code you can tie in with deprecated interface I found.

I've never downvoted you - I actually like a lot of your stuff, or even if I don't agree with all of it, I'd defend to the death your right to... blah, blah...

=) cool cool.

You don't happen to know offhand what Oracle setting you have to turn on (or maybe had to turn on in the past - it's been a while) to enable timings of stuff - wait interface tables... stuff like that. I knoow there's a setting that some people didn't like turning on because of a performance hit, but then they lost out on being able to trace/monitor/measure their SQL?

no idea, Oracle tech has never even once put a dollar in pocket so I don't learn their stuff.

the most big-enterprise corporate "we hate small consultants" stuff ever.

Weird - I just answered a question and it's not appearing on the home page?

Can you see it?

@Vérace yep

@Vérace I really wish you would move the setup data to the top.

Ah, it's just come up! Strange though - normally they appear immediately!

I mean just delete what he wrote, and put your setup data up there and help him out.

Hmmm... I was a bit too adventurous in my early edits when I first came onto the forum and got my botty slapped - le chat échaudé craint l'eau froide - once bitten, twice shy!

:-)

The problem with writing the setup data yourself and not editing it into the question is that it means every answer must mirror the setup instructions, it's pain.

@EvanCarroll - fair enough - it's not something I considered doing before... I see what you mean about duplication of effort - but if your answer's already there, won't anyone answering see it anyway?

What is the canonical method of dealing with this?

Where's our master of all matters canonical when you need him?

Maybe, maybe not. Let's assume I come along and answer this question too. Do I tell people in my answer that the setup instructions are in your answer? Because while I can do that, it's a miserable experience for new people that are coming and find my answer. Moreover, if you split it out, it's also a miserable experience for people viewing your answer: now they have to read the setup presented with SQL (in your answer) and without SQL (in the question). That's more work for them too.

My canonical method is to always edit and put the setup instructions in the question, because I view it as a failing of the question: which is fine, they're usually in need of help (that's why they're asking). You should liberally remove content that doesn't help describe the problem, and you should communicate things in a way that makes the more easily searched and understood by people viewing them.

And it's also explicit stackoverflow policy, like what previously used to be the policy of "respect the wishes of the questioner" is absolutely not policy anymore. Now the question serves the community after it's asked, so you have a mandate (within reason -- I personally wouldn't argue about if it bother the person asking the question, but you can!)

In fact, the mods at Ask Ubuntu have so pissed me off with this policy so much I stopped contributing to their community entirely and now I ask on Unix & Linux where they're nicer. ;)

Mostly because frankly I am more skilled than 100% of the mods on Ask Ubuntu and Super User. That's really the bottom of the barrel.

To expand on that, your only recourse to this often-annoying policy is to write to StackExchange and invoke your rights on the CC-BY-SA to have the attribution removed, in which case they delete it from your account. I'm 100% cool with that. I think they should provide an interface to that request. I've only done it once or twice ever. I don't abuse it, and they're good about it.

@EvanCarroll - see your point - it's more compact, cleaner really! I'll do that in future and if some curmudgeonly w$%*ers object, I'll tell them to f*(&off and write their own eff£$*!ing answers if they're so smart! Ah well, gotta toodle... I'm still waiting for a sub-20ms answer to our conundrum! :-) À demain!

Check out my new profile!

BTW, my wife works in C. elegans lab.

She is in her post-doc

Love that wee fella!

search is broken =(

damn website

@Vérace took a crack at dba.stackexchange.com/a/301924/2639

This tree business plus the brief discussion on worms has got me thinking about trees in a general sense - even though C. elegans individuals rarely have more than 1 parent, they sometimes do (between 01% - 1% approx.) - how would you model that in a tree in SQL? A reverse BOM if you will - all ancestors of a human - take a look at Cleopatra's (VIIth) pedigree.

When I first saw it, it was on a box of pedigree drawing software and I thought to myself "What on earth are they doing with those flies?" - the concept that it was a human pedigree just didn't enter my poor innocent head! I know that SQL is Turing complete but how would you mode this? What about generalising further - multiple parents of multiple children - not children in the biological sense - entities - networks?

I'll check out your answer tomorrow - off to beddibyes...

Ah, you want multiple parents. That can only be modeled with a M2M linkage table. (unless one parent can only ever have one child)

@Vérace-getVACCINATEDNOW Any edit that makes a post better is welcome. It is important to respect the original author as a starting point, but quite dramatic edits are sometimes possible to maximize the long term value of a Q & A to the site. This is especially true after the original asker has received everything they need from the interaction and some time has passed. Never fight about edits though.

It is also possible to create a separate Q & A if that makes the exercise cleaner and results in a potential duplicate target with the general solutions clearly presented.

If you are unsure, you can always ask in advance, either here or on Facebook Database Administrators Meta

Most of the time authors appreciate the extra work.