The Heap™ – Consultancy ©® - 2017-01-06 (page 2 of 2)

9:22 PM

Noun: wonder ‎(countable and uncountable, plural wonders)

Something that causes amazement or awe; a marvel.
Wonders of the World seem to come in sevens.‎
Something astonishing and seemingly inexplicable.
The idea was so crazy that it is a wonder that anyone went along with it.‎
Someone very talented at something, a genius.

(13 more not shown…)

Verb: wonder ‎(third-person singular simple present wonders, present participle wondering, simple past and past participle wondered)

(intransitive) To be affected with surprise or admiration; to be struck with astonishment; to be amazed; to marvel; often followed by at.
Jonathan Swift, Gulliver's Travels
I could not sufficiently wonder at the intrepidity of these diminutive mortals.
Johnson
We cease to wonder at what we understand.

(4 more not shown…)

dog

Noun: dog ‎(plural dogs)

A mammal, Canis lupus familiaris, that has been domesticated for thousands of years, of highly variable appearance due to human breeding.
The dog barked all night long.‎
A male dog, wolf or fox, as opposed to a bitch (often attributive).
1928, Siegfried Sassoon, Memoirs of a Fox-Hunting Man, Penguin 2013, page 149:
Firstly, he was there to encourage and assist the hounds (a scratch pack – mostly dog-hounds drafted from fox-hound kennels because they were over-sized) […].

(33 more not shown…)

Verb: dog ‎(third-person singular simple present dogs, present participle dogging, simple past and past participle dogged)

(transitive) To pursue with the intent to catch.
(transitive) To follow in an annoying way.
The woman cursed him so that trouble would dog his every step.
(transitive, nautical) To fasten a hatch securely.
It is very important to dog down these hatches...

(8 more not shown…)

Conjunction: dog

though

Dodi

The only dog I really really want to have

is

Courage the Cowardly Dog

Courage the Cowardly Dog is an American animated horror comedy television series created by John R. Dilworth for Cartoon Network as part of the network's Cartoon Cartoons block. It follows a purple anthropomorphic beagle who lives with a married elderly couple in the middle of Nowhere. The trio are frequently thrown into bizarre misadventures, often involving the paranormal/supernatural. The series is known for its dark, surreal humor and atmosphere. Dilworth pitched the series to Hanna-Barbera's animated shorts showcase What a Cartoon!, and a pilot (entitled "The Chicken from Outer Space") aired...

TypoCubeᵀᴹ

162 messages. I left for an hour and hells breaks loose

swasheck

hells bells

AC/DC - Hells Bells (Official Video)

►

Evan Carroll

Anyone want to take a look at this... dba.stackexchange.com/a/160222/2639

I do not believe that the count(*) is safe in his example.

I'm not even sure count(*) can be made safe without an explicit table-lock.

Wait, I take it back. I have a long history of not asking that question and it's working SWELL for me thus far.

Paul White

@a_hose_with_no_name But it's in contradiction with "So the select statements in your two insert statements will not see new rows."? Thank you for your patience. — Beni Mio 8 hours ago

a hose with no name!

That amused me for reasons I can't really explain.

Evan Carroll

9:37 PM

bwahahha. Someone take the nick.

I like it more.

I think I'm going to make my news years resolution to ask all questions I have going on in my head, publicly. First, even if I know them, they're better than all the questions here. ;) And, second I'll work towards answering them and get a head start.

Paul White

I can't believe he typed that handle out by hand either.

:34604820 It's not often super-helpful. A chat room to discuss competing answers would probably work better.

@EvanCarroll I only read the answer very quickly, but the count(*) is just a starting point. It's a snapshot of committed data so it should be accurate at that moment. All the clever stuff happens after that check. Constraints are generally a good way to solve this sort of problem reliably, if implemented correctly.

I haven't read your answer yet at all. I will.

Evan Carroll

No rush , no questions on my answer.

I'm just wondering if after the count(*) another txn takes the rows & commits, he'll miss the check that insufficient seats are available.

I don't think there is a way around that, shy of table locking. But, I would never write that code. So I'm asking on style that I'm uncomfortable with and 90%+ sure isn't safe.

From my perspective, passing an assertion that you can fail before the txn is a no-go. I would never code that. If you can pass assertion you should pass the txn. Or, just don't assert early.

style*

Paul White

@EvanCarroll Yes indeed, but I think the assessment is that such occurrences would be rare. The answer appears to optimize for concurrency over the possibility of failure. The count(*) isn't an assert; it's an entry condition.

At it simplest, the whole transaction could be entirely serialized, but that wouldn't be great for concurrency. Depending on the locking semantics of for update, I'm guessing your solution is closer to a serial approach.

In practice, both would probably be OK, unless you're Ticketek or a major airline etc.

Certainly more than one way to remove the fur from the feline.

Evan Carroll

No. My solution is sane. I would instead take what is returned and see if it's equal to the amount you need. If not, roll back. As the original update will hold an EXCLUSIVE ROW lock on the row.

Checking in the txn before you attempt just adds complexity. I don't think it actually gains anything.

anyway, I agree, unless you're running sub-ms txns the chance of a collision is unlikely but when people ask for concurrent, I want to pretend hard.

Paul White

9:54 PM

@EvanCarroll Well it avoids a bunch of work if the allocation can be assumed to fail upfront cheaply.

Evan Carroll

Nah, i don't think it avoids any work.

I think it creates more work.

Paul White

Anyway, as I say, it needs more thought and analysis. Probably from someone that knows PG better than I.

Evan Carroll

You can write the same check for availability in the UPDATE itself.

(you'll see that in my answer)

Paul White

if (SELECT count(*)
          FROM bookings.bookings
         WHERE session_id = _session_id AND booker is NULL
       ) < _number_of_seats THEN
        RETURN 0 ;
    end if ;

If (not enough seats) bail. (without taking any locks)

Evan Carroll

Right, but that's more work not less work.

Look at the WITH free_seats CTE where he does the work again. Except that time he has to do it.

Paul White

9:56 PM

Under high concurrency it might be worth it. Presumably it's a tiny amount of work as well, assuming good indexing.

@EvanCarroll Yes I saw that.

Evan Carroll

So the only thing you can be saving is the execution of the same work. If that returns nothing then nothing happens in his update and insert.

So the trick is just to check what the UPDATE returns.

Paul White

@EvanCarroll Not quite the same, the first time is lock free, second time is FOR UPDATE.

Evan Carroll

Only if something exists.

Otherwise nothing is locked.

Paul White

Yes but lots of other stuff might be attempting to get the same locks concurrently.

If there's no hope getting the required seats, there's no point 1000 concurrent transactions fighting over a tiny set of records trying to lock them.

But there's merit in both ideas, as I said before.

Evan Carroll

Yea, so you'd have to put a count(*) OVER () > ticket_required to get past that.

I left that out of my question because I'm not sure how he wants to handle fewer than the desire amount of tickets remaining.

Paul White

10:02 PM

I like to think of high concurrency as extreme multi-threaded programming, and we know how easy that is to get right under all conditions :)

Evan Carroll

Anyway, we can all have different answers. I just don't think that one provides anything beneficial, except the potential to confuse others.

which is always fun.

I'll probably delete my answer when Erwin shows up anyway.

Paul White

Well it uses VOLATILE NOT LEAKPROOF STRICT which sounds cool at least ;)

So. Two good answers already, a decent bounty, and we get:

You would use a timed reservation. Like when you buy tickets for a concert, you have M minutes to confirm the booking, or someone else gets the chance — Neil McGuigan 18 mins ago

Why do people do that.

Can't downvote it, can't debate it, can't mark it accepted or award the bounty. What's the point.

</ rhetorical>

Paul White

10:23 PM

@MaxVernon In case it wasn't obvious before, the optimizer does decide whether to sort or not. For me, 260 VALUES were enough to get a sort without the trace flag.

> ...or even better, make the INSERT statement understand when it would be beneficial to do the sort first.

Dodi

10:42 PM

I have inserted data into my dimension tables

but still have the fact table with no data

How do I insert the data into the fact table?

Paul White

11:36 PM

@TypoCubeᵀᴹ You around?

TypoCubeᵀᴹ

11:56 PM

@PaulWhite yes

Paul White

@TypoCubeᵀᴹ Thanks. It's sort of redundant now, but I was going to ask what you thought of this question

Transcript for

The Heap™ – Consultancy ©®