Re: [PERFORM] Re: Postgres insert performance and storage requirement compared to Oracle

[moving to -hackers, from -performance]

On Wed, Oct 27, 2010 at 11:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> Gee, I wonder if it would possible for PG to automatically do an
>> asynchronous commit of any transaction which touches only temp tables.
>
> Hmm ... do we need a commit at all in such a case?  If our XID has only
> gone into temp tables, I think we need to write to clog, but we don't
> really need a WAL entry, synced or otherwise.

I think we might need a commit record anyway to keep Hot Standby's
KnownAssignedXids tracking from getting confused. It might be
possible to suppress it when wal_level is less than hot_standby, but
I'm not sure it's worth it.

You definitely need to write to CLOG, because otherwise a subsequent
transaction from within the same backend might fail to see those
tuples.

Also, I think that the right test is probably "Have we done anything
that needs to be WAL-logged?". We can get that conveniently by
checking whether XactLastRecEnd.xrecoff. One option looks to be to
just change this:

if (XactSyncCommit || forceSyncCommit || nrels > 0)

...to say ((XactSyncCommit && XactLastRecEnd.recoff != 0) ||
forceSyncCommit || nrels > 0).

But I'm wondering if we can instead rejigger things so that this test
moves out of the !markXidCommitted branch of the if statement and
drops down below the whole if statement.

/*
* If we didn't create XLOG entries, we're done here;
otherwise we
* should flush those entries the same as a commit
record. (An
* example of a possible record that wouldn't cause an XID to be
* assigned is a sequence advance record due to
nextval() --- we want
* to flush that to disk before reporting commit.)
*/
if (XactLastRecEnd.xrecoff == 0)
goto cleanup;

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Compan