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

On 13.09.2011 20:57, Stefan Keller wrote:
> Interesting debate.

Indeed.

> 2011/9/13 Marti Raudsepp<marti@juffo.org>:
>> Yes, every BEGIN/EXCEPTION block creates a subtransaction -- like a
>> SAVEPOINT it can roll back to in case of an error.
>
> Are you sure? In theory I always understood that there are no
> "subtransactions".

What theory are you referring to?

> In fact when looking at the docs there is chapter 39.6.6. saying "By
> default, any error occurring in a PL/pgSQL function aborts execution
> of the function, and indeed of the surrounding transaction as well.
> You can trap errors and recover from them by using a BEGIN block with
> an EXCEPTION clause."
> (http://www.postgresql.org/docs/current/interactive/plpgsql-control-structures.html
> )
>
> So the doc isn't totally explicit about this. But whatever: What would
> be the the function of a subtransaction? To give the possibility to
> recover and continue within the surrounding transaction?

I find this pretty explicit:

It is important not to confuse the use of BEGIN/END for grouping
statements in PL/pgSQL with the similarly-named SQL commands for
transaction control. PL/pgSQL's BEGIN/END are only for grouping; they do
not start or end a transaction. Functions and trigger procedures are
always executed within a transaction established by an outer query —
they cannot start or commit that transaction, since there would be no
context for them to execute in. However, a block containing an EXCEPTION
clause effectively forms a subtransaction that can be rolled back
without affecting the outer transaction. For more about that see Section
38.6.5.

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

Cheers

    robert