Does updating A require dynamic access to B or read once at start?
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?
-
-
-
unsure, I imagine it's read-once (included in an early WITH subquery) but who knows how optimizer plays out
- Show replies
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 …
- Show replies
New conversation -
-
-
fortunately can still read B, so just copying to a temp table and altering that, will rename later good enough
-
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 - Show replies
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 -
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.