Re: Review: Row-level Locks & SERIALIZABLE transactions, postgres vs. Oracle

Joe Conway wrote:

> Should I be installing Florian's patch in addition to yours when I
> start testing?

There's some manual fix-up needed, primarily because we need to
differentiate between SERIALIZABLE and REPEATABLE READ isolation
levels, and therefore replaced the IsXactIsoLevelSerializable macro
with IsXactIsoLevelXactSnapshotBased and
IsXactIsoLevelFullySerializable. If you can wait until tomorrow,
I'll create a merged patch for you and confirm that it passes the
modified Florian's pgbench test (with the FOR SHARED clause
removed). I'll include a crude hack to pgbench I had to use to test
this, with an explanation of why it was needed. I'm still trying to
put together better testing techniques for the long term.

> Also, where can I get the latest and greatest version of your
> patch?

There's always the git repository, but I'll post a new patch
tomorrow, based on what I've recently found.

-Kevin

On 7/17/10 12:09 PM, Kevin Grittner wrote:
> Joe Conway wrote:
>
>> Should I be installing Florian's patch in addition to yours when I
>> start testing?
>
> There's some manual fix-up needed, primarily because we need to
> differentiate between SERIALIZABLE and REPEATABLE READ isolation
> levels, and therefore replaced the IsXactIsoLevelSerializable macro
> with IsXactIsoLevelXactSnapshotBased and
> IsXactIsoLevelFullySerializable. If you can wait until tomorrow,
> I'll create a merged patch for you and confirm that it passes the
> modified Florian's pgbench test (with the FOR SHARED clause
> removed). I'll include a crude hack to pgbench I had to use to test
> this, with an explanation of why it was needed. I'm still trying to
> put together better testing techniques for the long term.
>
>> Also, where can I get the latest and greatest version of your
>> patch?
>
> There's always the git repository, but I'll post a new patch
> tomorrow, based on what I've recently found.

That all sounds great. I'll concentrate today on understanding the
theory and high level design.

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support

Joseph Conway <mail(at)joeconway(dot)com> wrote:
> On 7/17/10 12:09 PM, Kevin Grittner wrote:

>> I'll post a new patch tomorrow
>
> That all sounds great. I'll concentrate today on understanding the
> theory and high level design.

Well, I started today by doing a make distclean and rebuilding from
scratch, and my patch behaved OK *without* the other patch, so that
issue wasn't real -- I had just gotten into a bad build state
somehow.

I'm attaching a fresh patch, but I think the only differences are:

(1) Some minor changes to line numbers based on recent commits.

(2) Some white space adjustments I made to better comply with style
guidelines.

I've also attached a small patch which hacks pgbench to continue
when a transaction fails with SQLSTATE '40001'. Florian's test
catches these in the test function and ignores them, but with the
SSI technique, some of the failures aren't being detected until the
COMMIT attempt, so the function can't catch and ignore them, so
pgbench has to do so. I'm also attaching a very slightly modified
version of the pgbench test which Florian used for the other patch.
It did show up real problems at first, but those were fixed before
the -2 patch I recently posted. (Yes, I admit that the very first
thing I do these days when I see a test or script which demonstrates
problems with serializability is to test is with the SSI code.)

To run Florian's test, I've been putting these files one level up
from my checkout directory, running the init script through psql,
then running:

pgbench -s 10 -j 10 -c 10 -t 1000 -n -f ../fkbench.pgbench fkbench

To run the tests included in the main patch (if you have python,
twisted, etc., installed), after the make check, run make dcheck.

If you spot anything on the Serializable Wiki page which is unclear,
please feel free to fix it or let me know. I'm hoping to ultimately
draw from that for a README file.

Thanks for looking at this!

-Kevin

On 07/18/2010 11:41 AM, Kevin Grittner wrote:
>
> I'm attaching a fresh patch, but I think the only differences are:

<snip>

Thanks for the detailed info. I managed to make my way through much of
the background info in the papers and wiki yesterday, so I will start
reviewing shortly.

> If you spot anything on the Serializable Wiki page which is unclear,
> please feel free to fix it or let me know. I'm hoping to ultimately
> draw from that for a README file.

Sounds good -- exactly what I was thinking as I reviewed it.

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support

On 07/18/2010 11:41 AM, Kevin Grittner wrote:
> To run the tests included in the main patch (if you have python,
> twisted, etc., installed), after the make check, run make dcheck.

Question about dcheck. After install of twisted, I get:

8<-----------------------------
bash-4.1$ make dcheck
make -C src/test dcheck
make[1]: Entering directory `/opt/src/pgsql/src/test'
make -C regress dcheck
make[2]: Entering directory `/opt/src/pgsql/src/test/regress'
./pg_dtester.py --temp-install --top-builddir=../../.. \
--multibyte=SQL_ASCII
Traceback (most recent call last):
File "./pg_dtester.py", line 18, in <module>
from dtester.events import EventMatcher, EventSource, Event, \
ImportError: No module named dtester.events
8<-----------------------------

Another python package I'm missing?

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support

On 07/18/2010 07:02 PM, Joe Conway wrote:
> On 07/18/2010 11:41 AM, Kevin Grittner wrote:
>> To run the tests included in the main patch (if you have python,
>> twisted, etc., installed), after the make check, run make dcheck.
>
> Question about dcheck. After install of twisted, I get:
>
> 8<-----------------------------
> bash-4.1$ make dcheck
> make -C src/test dcheck
> make[1]: Entering directory `/opt/src/pgsql/src/test'
> make -C regress dcheck
> make[2]: Entering directory `/opt/src/pgsql/src/test/regress'
> ./pg_dtester.py --temp-install --top-builddir=../../.. \
> --multibyte=SQL_ASCII
> Traceback (most recent call last):
> File "./pg_dtester.py", line 18, in <module>
> from dtester.events import EventMatcher, EventSource, Event, \
> ImportError: No module named dtester.events
> 8<-----------------------------
>
> Another python package I'm missing?

Sorry for the noise -- I see the dependency listed on the wiki to Markus
Wanner's dtester. Looks like "make dcheck" is running now (although
seems rather slow).

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support