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

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> 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.)

Well I gave one example of a use case where you want to span sessions. To be
more explicit, if you have some batch job loading data that you then want to
have a human review via some other path such as a web page before continuing
with the process.

There's nothing saying all the data is coming from the same source at all. You
could have some of the data being loaded by a psql COPY command and then other
data being loaded by a perl script. Being tied to having the two executed from
the same session is an arbitrary limit on the architecture.

> 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. 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 don't think 'alter table set logged off' complicates users' lives too much.

But I like the other user's suggestion too. If there's a standards blessed
feature to have temporary tables that are visible in other sessions then
perhaps that's what we've arrived at from another direction.

This means the final load into production data would still have to be logged,
so this wouldn't get everything the original posters were looking for. But
getting around that does mean risking someone's production data at some point.

--
greg