« first day (2194 days earlier)      last day (1722 days later) » 
02:00 - 21:0021:00 - 00:00

9:22 PM
Noun: wonder ‎(countable and uncountable, plural wonders)
  1. Something that causes amazement or awe; a marvel.
  2. Wonders of the World seem to come in sevens.‎
  3. Something astonishing and seemingly inexplicable.
  4. The idea was so crazy that it is a wonder that anyone went along with it.‎
  5. 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)
  1. (intransitive) To be affected with surprise or admiration; to be struck with astonishment; to be amazed; to marvel; often followed by at.
  2. Jonathan Swift, Gulliver's Travels
  3. I could not sufficiently wonder at the intrepidity of these diminutive mortals.
  4. Johnson
  5. We cease to wonder at what we understand.
(4 more not shown…)
Noun: dog ‎(plural dogs)
  1. A mammal, Canis lupus familiaris, that has been domesticated for thousands of years, of highly variable appearance due to human breeding.
  2. The dog barked all night long.‎
  3. A male dog, wolf or fox, as opposed to a bitch (often attributive).
  4. 1928, Siegfried Sassoon, Memoirs of a Fox-Hunting Man, Penguin 2013, page 149:
  5. 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)
  1. (transitive) To pursue with the intent to catch.
  2. (transitive) To follow in an annoying way.
  3. The woman cursed him so that trouble would dog his every step.
  4. (transitive, nautical) To fasten a hatch securely.
  5. It is very important to dog down these hatches...
(8 more not shown…)
Conjunction: dog
  1. though
 
The only dog I really really want to have
is
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...
 
162 messages. I left for an hour and hells breaks loose
 
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.
 
@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.
 
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.
 
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.
 
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*
 
@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.
 
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.
 
9:54 PM
@EvanCarroll Well it avoids a bunch of work if the allocation can be assumed to fail upfront cheaply.
 
Nah, i don't think it avoids any work.
I think it creates more work.
 
Anyway, as I say, it needs more thought and analysis. Probably from someone that knows PG better than I.
 
You can write the same check for availability in the UPDATE itself.
(you'll see that in my answer)
 
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)
 
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.
 
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.
 
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.
 
@EvanCarroll Not quite the same, the first time is lock free, second time is FOR UPDATE.
 
Only if something exists.
Otherwise nothing is locked.
 
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.
 
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.
 
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 :)
 
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.
 
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>
 
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.
 
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?
 
11:36 PM
@TypoCubeᵀᴹ You around?
 
11:56 PM
@PaulWhite yes
 
@TypoCubeᵀᴹ Thanks. It's sort of redundant now, but I was going to ask what you thought of this question
 
02:00 - 21:0021:00 - 00:00

« first day (2194 days earlier)      last day (1722 days later) »