Re: [Bizgres-general] WAL bypass for INSERT, UPDATE and

Ühel kenal päeval, R, 2005-12-23 kell 23:06, kirjutas Tom Lane:
> Greg Stark <gsstark(at)mit(dot)edu> writes:
> > It seems to me the only rational way to approach this is to have a per-table
> > flag that sets that table to be non-logged. Essentially changing a table's
> > behaviour to that of a temporary table except that other transactions can see
> > it.
>
> But what's the point? Nowhere in your scenario is there a reason why
> we need to have multiple sessions working on the data being loaded.
> So a temp table solves the problem perfectly. (Temp tables do span
> transactions, just not sessions.)

"Human operator looking at the date before loading it into final tables"
seems to be exactly that usecase. After some script loads the data in
temp tables, you may want an operator to look at the data using some
visual app or some other script. It would be quite tricky to make them
use the same session, especially if different steps use different
technologies (shell script, psql, jdbc, python, ...)

> I've got a fundamental philosophical objection to proposals in this
> line, which I suppose I'd better state for the record. I don't like
> inventing nonstandard SQL extensions or peculiar semantics just to gain
> performance.

Out TEMP tables *already* are nonstandard. IIRC the standard temp tables
the word temp applies only for data, not the schema.

> It imposes extra complexity on users that they could do
> without; the first time someone loses critical data because he didn't
> fully understand the tradeoffs involved, he'll have a justifiable gripe.
> I also don't like playing Oracle's or MySQL's game by inventing
> proprietary syntax. We claim to believe in standards compliance, so we
> should have a pretty high bar for inventing nonstandard syntax. When
> the proposed change has a narrow use-case and is only going to yield
> marginal improvements even in that case, I think we should just say no.
>
> Bottom line: if we can't figure out how to do it transparently, I think
> we haven't thought hard enough.

I guess we can never make the database so smart that it can quess users
specific needs of durability on per-table basis. What we can do is
making different durability choices more obvious at syntax level.

I have been long musing about having multiple wal files/filesets,
perhaps per-tablespace, perhaps just specified on per-table basis.
This would give both possibility for added performance by using
more/different storages and also a possibility to select different
classes of durability.

making one of WAL files (strategies) be /dev/null would almost get us
non-logged writes, except for a little overhead in write() calls.
fsync() on /dev/null should be instantaneous .

----------------------
Hannu