fortunately can still read B, so just copying to a temp table and altering that, will rename later good enough
Hmmm. Two PSQL tables, A and B. B small. Updating A takes days, involves reading B. Seem unable to alter B while A updating. Correct?
-
-
-
Got it. Updating A puts AccessShare lock on B ALTER TABLE B requires AccessExclusive lock https://www.postgresql.org/docs/9.1/static/explicit-locking.html … h/t
@legalinspire -
ffffffffuuuuuuuu broke after 80+ hours time to rewrite I guesspic.twitter.com/kmihAkLJbP
-
temp table use >400GB nice

-
Yikes. Break it down into multiple passes?
-
Yeah probably going to do step by step. Later tho gotta have some base results for Monday
-
Someday I will tell you the story of The Button.
-
oh please do!
End of conversation
New conversation -
-
-
Does updating A require dynamic access to B or read once at start?
-
unsure, I imagine it's read-once (included in an early WITH subquery) but who knows how optimizer plays out
-
Maybe dump B into a cursor or something and use it instead if you need B to be writable during? Problems are obvious...
-
...but sometimes brute force isn't bad. :)
-
BTW I am not trying to teach you anything. Your level of knowledge is so far above mine the light from it will not reach me for years.
-
But as the saying goes, the greatest swordsman fears not the second-greatest, but the worst. :)
-
oh god no I'm garbage at sql :)
End of conversation
New conversation -
-
-
> updating A takes days w-what
-
it's a pretty involved query on a ton of rows probably it's deeply inefficient toopic.twitter.com/7K6PAf4k32
End of conversation
New conversation -
-
-
(typo before) how large is A (out of curiosity)?
-
A is 3.4M rows, requires self-join of a basal 30M row table plus some other large crap though
End of conversation
New conversation -
-
-
So long as you don't use NOLOCK, yes. Correct.
-
that's what throws me maybe. psql allegedly doesn't use/require NOLOCK perhaps? https://stackoverflow.com/questions/2394565/postgresql-equivalent-of-sqlservers-nolock-hint …
-
That's bizarre. Seems like a pretty serious flaw.
End of conversation
New conversation -
Loading seems to be taking a while.
Twitter may be over capacity or experiencing a momentary hiccup. Try again or visit Twitter Status for more information.