Re: foreign key locks, 2nd attempt - Mailing list pgsql-hackers

Excerpts from Simon Riggs's message of lun mar 05 16:34:10 -0300 2012:
> On Mon, Mar 5, 2012 at 6:37 PM, Alvaro Herrera
> <alvherre@commandprompt.com> wrote:

> It does however, illustrate my next review comment which is that the
> comments and README items are sorely lacking here. It's quite hard to
> see how it works, let along comment on major design decisions. It
> would help myself and others immensely if we could improve that.

Hm.  Okay.

> Is there a working copy on a git repo? Easier than waiting for next
> versions of a patch.

No, I don't have an external mirror of my local repo.

> My other comments so far are
>
> * some permutations commented out - no comments as to why
> Something of a fault with the isolation tester that it just shows
> output, there's no way to record expected output in the spec

The reason they are commented out is that they are "invalid", that is,
it requires running a command on a session that's blocked in the
previous command.  Obviously, that cannot happen in real life.

isolationtester now has support for detecting such conditions; if the
spec specifies running a command in a locked session, the permutation is
killed with an error message "invalid permutation" and just continues
with the next permutation.  It used to simply die, aborting the test.
Maybe we could just modify the specs so that all permutations are there
(this can be done by simply removing the permutation lines), and the
"invalid permutation" messages are part of the expected file.  Would
that be better?

> Comments required for these points
>
> * Why do we need multixact to be persistent? Do we need every page of
> multixact to be persistent, or just particular pages in certain
> circumstances?

Any page that contains at least one multi with an update as a member
must persist.  It's possible that some pages contain no update (and this
is even likely in some workloads, if updates are rare), but I'm not sure
it's worth complicating the code to cater for early removal of some
pages.

> * Why do we need to expand multixact with flags? Can we avoid that in
> some cases?

Did you read my blog post?
http://www.commandprompt.com/blogs/alvaro_herrera/2011/08/fixing_foreign_key_deadlocks_part_three/
This explains the reason -- the point is that we need to distinguish the
lock strength acquired by each locker.

> * Why do we need to store just single xids in multixact members?
> Didn't understand comments, no explanation

This is just for SELECT FOR SHARE.  We don't have a hint bit to indicate
"this tuple has a for-share lock", so we need to create a multi for it.
Since FOR SHARE is probably going to be very uncommon, this isn't likely
to be a problem.  We're mainly catering for users of SELECT FOR SHARE so
that it continues to work, i.e. maintain backwards compatibility.

(Maybe I misunderstood your question -- what I think you're asking is,
"why are there some multixacts that have a single member?")

I'll try to come up with a good place to add some paragraphs about all
this.  Please let me know if answers here are unclear and/or you have
further questions.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support