Merging a couple of emails:
On 20-Aug.-2020 19:37, David Rowley wrote:
When updates occur in a non-partitioned table we can follow item
pointer chains to find the live row and check if the WHERE clause
still matches to determine if the row should be updated, or in this
case just locked since it's a SELECT FOR UPDATE. However, with
partitioned table, a concurrent UPDATE may have caused the row to have
been moved off to another partition, in which case the tuple's item
pointer cannot point to it since we don't have enough address space,
we only have 6 bytes for a TID. To get around the fact that we can't
follow these update chains, we just throw the serialization error,
which is what you're getting. Ideally, we'd figure out where the live
version of the tuple is and check if it matches the WHERE clause and
lock it if it does, but we've no means to do that with the current
design.
This is the absolute best description of what causes the "tuple to be
locked was already moved to another partition due to concurrent update"
message I have ever seen. It totally makes sense why this happens given
your explanation. Thank you for giving the detail.
I am backing off the query with a time delay and then retrying and that
seems to be the correct approach as well, but I only stumbled upon that
by accident. Hopefully when the google gnomes index the mailing list
this message will come up to save others time and worry about the message.
On 21-Aug.-2020 00:34, Thomas Munro wrote:
Maybe it's just getting algorithmically ugly. To claim some job rows,
you have to skip all dead/non-matching tuples left behind so far at
the start of the table by all the other sessions, and then also all
currently locked tuples, and you have to do update-chain walks on some
of them too. It all gets a bit explosive once you have such high
numbers of workers.
Yes, fundamentally it seems to come down to traffic volume. When I get
over 128 connections all selecting and locking that one table, applying
the locks seems to struggle and the problem grows exponentially. I'm an
extreme edge case, so not really a scenario that locking could ever have
been expected to handle but it's pretty good that it gets up to 128 at all.
I think I'd experiment with splitting the job table up into N tables
and feed jobs into all of them about evenly (by hashing, at random,
whatever), and then I'd assign each consumer a "preferred" table where
it looks for jobs first (perhaps my_worker_id % ntables), before
trying the others in round robin order. Then they won't trample on
each other's toes so much.
I think this is a good idea, but (in my case) I think this is where it
will need v13 which is going to give that via "Allow ROW values to be
used as partitioning expressions" ? (e.g. will v13 permit queueid mod
200 as the partition expression to make 200 partitions to allow 200
[contention free] consumers?).
In the past I've wondered about a hypothetical circular_seqscan
option, which would cause table scans to start where they left off
last time in each backend, so SELECT * FROM t LIMIT 1 repeated would
show you a different row each time until you get all the way around to
the start again (as we're entirely within our rights to do for a query
with no ORDER BY). That'd give the system a chance to vacuum and
start refilling the start of the table before you get around to it
again, instead of repeatedly having to step over the same useless
pages every time you need a new job.
I like the sound of this; if I understand correctly, it would
essentially walk in insertion(-ish) order which would be OK for me. As
long as it was documented clearly; perhaps I should put a page online
about high traffic message queues with PostgreSQL for people to find
when they try the same thing.
Hmm. I guess another way to avoid colliding with others' work would
be to try to use SELECT * FROM t TABLESAMPLE SYSTEM (10) WHERE ... FOR
UPDATE SKIP LOCKED LIMIT .... It's less cache-friendly, and less
order-preserving, but way more contention-friendly. That has another
complication though; how do you pick 10? And if it doesn't return any
or enough rows, it doesn't mean there isn't enough, so you may need to
be ready to fall back to the plain approach if having 250 rows is
really important to you and TABLESAMPLE doesn't give you enough. Or
something.
For me, this is an acceptable compromise. I just need to consume
something on each pass and up to 250 items but getting something less
than 250 would still allow progress and if it removed wait time, could
even be an overall greater throughput. I may try this just to see how
it performs.
If someone has a strictly ordered queue, they will never really have
this issue as they must start at the beginning and go sequentially using
only 1 consumer. Because I only need loose/approximate ordering and
throughput is the objective, all the locking and contention comes into play.
By the way, when working with around 64 consumer processes I was also
annoyed by the thundering herd problem when using NOTIFY. I found
various workaround solutions to that, but ultimately I think we need
more precise wakeups for that sort of thing, which I hope to revisit
one day.
I'm making a note of this because I also happen to have a different
scenario which has NOTIFY with well over 100 LISTEN consumers... That's
not given me problems - yet - but I'm now aware of this should problems
arise.
