Re: Postgres for a "data warehouse", 5-10 TB

On 13.09.2011 20:11, Marti Raudsepp wrote:
> On Tue, Sep 13, 2011 at 19:34, Robert Klemme<shortcutter(at)googlemail(dot)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