Message-ID: <4E6E3FE8.5070609@squeakycode.net>
Date: Mon, 12 Sep 2011 12:22:48 -0500
From: Andy Colson <andy@squeakycode.net>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:6.0.2) Gecko/20110902 Thunderbird/6.0.2
MIME-Version: 1.0
To: Robert Klemme <shortcutter@googlemail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Postgres for a "data warehouse", 5-10 TB
References: 
 <CAMhtkAbK7saRO_Tayb1nS3YLWN=Wns=Ci47qs-3YbX0T2+hkpA@mail.gmail.com>
	<CAOR=d=0zmw-EGRJyYmwCTxR5g6osbFLS5i-3kzjG05j7S=1JaA@mail.gmail.com>
	<CAMhtkAah2c4XfSec=OtgL1V51wpD=jygbZnBVBRYCV02MscebQ@mail.gmail.com>
	<4E6CC458.4010206@squeakycode.net>
	<CABRT9RB-etP-K-0u0y-Wv+BysSd9ToZm71BqcY7hFQZC2tmjYQ@mail.gmail.com>
	<j4lens$hp0$1@dough.gmane.org>
In-Reply-To: <j4lens$hp0$1@dough.gmane.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 9/12/2011 12:15 PM, Robert Klemme wrote:
> On 11.09.2011 19:02, Marti Raudsepp wrote:
>> On Sun, Sep 11, 2011 at 17:23, Andy Colson<andy@squeakycode.net> wrote:
>>> On 09/11/2011 08:59 AM, Igor Chudov wrote:
>>>> By the way, does that INSERT UPDATE functionality or something like
>>>> this exist in Postgres?
>>> You have two options:
>>> 1) write a function like:
>>> create function doinsert(_id integer, _value text) returns void as
>>> 2) use two sql statements:
>>
>> Unfortunately both of these options have caveats. Depending on your
>> I/O speed, you might need to use multiple loader threads to saturate
>> the write bandwidth.
>>
>> However, neither option is safe from race conditions. If you need to
>> load data from multiple threads at the same time, they won't see each
>> other's inserts (until commit) and thus cause unique violations. If
>> you could somehow partition their operation by some key, so threads
>> are guaranteed not to conflict each other, then that would be perfect.
>> The 2nd option given by Andy is probably faster.
>>
>> You *could* code a race-condition-safe function, but that would be a
>> no-go on a data warehouse, since each call needs a separate
>> subtransaction which involves allocating a transaction ID.
>
> Wouldn't it be sufficient to reverse order for race condition safety?
> Pseudo code:
>
> begin
> insert ...
> catch
> update ...
> if not found error
> end
>
> Speed is another matter though...
>
> Kind regards
>
> robert
>
>
>

No, I dont think so, if you had two loaders, both would start a 
transaction, then neither could see what the other was doing.  There are 
transaction isolation levels, but they are like playing with fire.  (in 
my opinion).

-Andy