<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">On 07/09/2013 09:51 PM, Robert Collins
wrote:<br>
</div>
<blockquote
cite="mid:CAJ3HoZ0Q40_+oBMZ6Z=LvRWuiWyQy+UYiUZkowBDvGkbBD=KyQ@mail.gmail.com"
type="cite"><br>
<br>
<div class="gmail_quote">On 10 July 2013 11:00, Jay Pipes <span
dir="ltr"><<a moz-do-not-send="true"
href="mailto:jaypipes@gmail.com" target="_blank">jaypipes@gmail.com</a>></span>
wrote:<br>
<div> </div>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
<div class="im">
> I am not up to speed on"InnoDB's gap locking
behavior" but it is<br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
not something I would expect to be a problem in
Postgresql.<br>
</blockquote>
<br>
</div>
InnoDB and PostgreSQL behave in very different manners
regarding locking and transaction isolation, even though they
both implement a version of MVCC. In InnoDB's case, its
implementation of MVCC is optimized more for storage space (it
allows using a series of log records to reconstruct or undo a
record to a particular "version" of the record) vs.
PostgreSQL, which stores every version of every record in its
data space.<br>
</blockquote>
<div><br>
PostgreSQL only keeps enough versions live to ensure the
oldest transaction can still read all the versions that were
live at it's start: once the transaction is closed any
obsolete version kept alive by it can be gc'd or overwritten.
I think the distinction you are drawing is that InnoDB only
stores deltas between record versions - an interesting tweak.
I guess that TOAST mitigates the temporary storage overhead:
as a particular TOAST value is immutable, there's no need to
copy that data when updating a different value in a row. That
won't help with 'update one int in each of a billion rows'
case though.<br>
</div>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
AFAIK, PostgreSQL won't issue gap locks like InnoDB because it
will simply write a new version of the records -- a version
that marks the record as deleted -- to its data files, instead
of gap locking to ensure that rows affected by a DELETE
statement with an open-ended (or non-existent) WHERE clause
can be properly isolated (and rolled back properly in the case
of a failure).<br>
</blockquote>
<div><br>
PostgreSQL doesn't do gap locks, but instead you have to deal
with <a moz-do-not-send="true"
href="http://wiki.postgresql.org/wiki/SSI">http://wiki.postgresql.org/wiki/SSI</a>
: the transaction that is deleting 1M rows, for instance, will
have a query that may return rows which another transaction is
adding; if so one of the two will be rolled back. This is in
many ways equivalent from the point of view of writing good
SQL that will work well on both systems.<br>
</div>
</div>
</blockquote>
<br>
This is not a problem with token cleanup path, though. Tokens are
cleaned up based on expiry time, a value that is written and never
changed. Tokens should never be removed from the database until
their expiry has been hit, or valid tokens will be denied. <br>
<br>
<blockquote
cite="mid:CAJ3HoZ0Q40_+oBMZ6Z=LvRWuiWyQy+UYiUZkowBDvGkbBD=KyQ@mail.gmail.com"
type="cite">
<div class="gmail_quote">
<div>
</div>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
In any case, MySQL is certainly a production-capable database
like PostgreSQL. It has its quirks and downsides, as does any
system, including PostgreSQL. Biases and false assumptions
should be set aside. ;)<br>
<br>
Best,<br>
-jay
<div class="HOEnZb">
<div class="h5"><br>
<br>
_______________________________________________<br>
OpenStack-dev mailing list<br>
<a moz-do-not-send="true"
href="mailto:OpenStack-dev@lists.openstack.org"
target="_blank">OpenStack-dev@lists.openstack.org</a><br>
<a moz-do-not-send="true"
href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev"
target="_blank">http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev</a><br>
</div>
</div>
</blockquote>
</div>
<br>
<br clear="all">
<br>
-- <br>
Robert Collins <<a moz-do-not-send="true"
href="mailto:rbtcollins@hp.com" target="_blank">rbtcollins@hp.com</a>><br>
Distinguished Technologist<br>
HP Cloud Services
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
OpenStack-dev mailing list
<a class="moz-txt-link-abbreviated" href="mailto:OpenStack-dev@lists.openstack.org">OpenStack-dev@lists.openstack.org</a>
<a class="moz-txt-link-freetext" href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev">http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev</a>
</pre>
</blockquote>
<br>
</body>
</html>