pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Robert Klemme <shortcutter@googlemail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Postgres for a "data warehouse", 5-10 TB
Date: Tue, 13 Sep 2011 22:22:47 +0200
Message-ID: <j4oe2s$1i3$1@dough.gmane.org> (raw)
In-Reply-To: <CABRT9RAKjmoa91sOBySx97=0ZKv64sD7miY0ZgUDu9dK0rQygw@mail.gmail.com>
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>
	<4E6E3FE8.5070609@squeakycode.net>
	<j4ltdo$r65$1@dough.gmane.org>
	<CABRT9RAJvq0bzBnqtEzc=80H7oYPYC4LggKWZOYcDbRRMnskJA@mail.gmail.com>
	<CAM9pMnMFXPnvVt9GR2iUfJoP-c4gpiQCVQFDPc1EFH+wxTGUhg@mail.gmail.com>
	<CABRT9RAKjmoa91sOBySx97=0ZKv64sD7miY0ZgUDu9dK0rQygw@mail.gmail.com>

On 13.09.2011 20:11, Marti Raudsepp wrote:
> On Tue, Sep 13, 2011 at 19:34, Robert Klemme<shortcutter@googlemail.com>  wrote:
>> I don't think so.  You only need to catch the error (see attachment).
>> Or does this create a sub transaction?
>
> Yes, every BEGIN/EXCEPTION block creates a subtransaction -- like a
> SAVEPOINT it can roll back to in case of an error.

Ouch!  Learn something new every day.  Thanks for the update!

http://www.postgresql.org/docs/8.4/interactive/plpgsql-structure.html

Side note: it seems that Oracle handles this differently (i.e. no 
subtransaction but the INSERT would be rolled back) making the pattern 
pretty usable for this particular situation.  Also, I have never heard 
that TX ids are such a scarse resource over there.

Would anybody think it a good idea to optionally have a BEGIN EXCEPTION 
block without the current TX semantics?  In absence of that what would 
be a better pattern to do it (other than UPDATE and INSERT if not found)?

>> Yes, I mentioned the speed issue.  But regardless of the solution for
>> MySQL's "INSERT..ON DUPLICATE KEY UPDATE" which Igor mentioned you
>> will have the locking problem anyhow if you plan to insert
>> concurrently into the same table and be robust.
>
> In a mass-loading application you can often divide the work between
> threads in a manner that doesn't cause conflicts.

Yeah, but concurrency might not the only reason to optionally update. 
If the data is there you might rather want to overwrite it instead of 
failure.

Kind regards

	robert




view thread (48+ messages)  latest in thread

Message-ID: <j4oe2s$1i3$1@dough.gmane.org>
Permalink:  ../j4oe2s$1i3$1@dough.gmane.org/
Also on:    postgresql.org/message-id/j4oe2s$1i3$1@dough.gmane.org

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: shortcutter@googlemail.com
  Subject: Re: Postgres for a "data warehouse", 5-10 TB
  In-Reply-To: <j4oe2s$1i3$1@dough.gmane.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox