Re: AW: Coping with huge deferred-trigger lists

Zeugswetter Andreas SB <ZeugswetterA(at)wien(dot)spardat(dot)at> writes:
>> Perhaps instead
>> of storing each and every trigger-related tuple in memory, we only need
>> to store one value per affected table: the lowest CTID of any tuple
>> that we need to revisit for deferred-trigger purposes. At the end of
>> the transaction, scan forward from that point to the end of the table,
>> looking for tuples that were inserted by the current xact.

> I thought that this current placing of new rows at end of file is subject to
> change soon (overwrite smgr) ?

Well, the scheme would still *work* if rows were not always placed at
the end of file, though it might get inefficient. But you're right, the
merits of this trigger idea depend a lot on whether we decide to go to
an overwriting smgr, and so we should probably wait till that's decided
before we think about doing this. I just wanted to get the idea
recorded before I forgot about it.

BTW, I don't think the overwriting-smgr idea is a done deal. We haven't
seen any design yet for exactly how it should work. Moreover, I'm
really hesitant to throw away one of the fundamental design choices of
Postgres: overwriting smgr is one of the things that got us to where we
are today. Before we commit to that, we ought to do some serious study
of the alternatives. ISTM the problem with VACUUM is not that you need
to do a regular maintenance procedure, it's that it grabs an exclusive
lock on the table for so long. We could live with VACUUM if it could be
made either incremental (do a few pages and release the lock) or capable
of running in parallel with reader & writer transactions. Vadim's
still-not-integrated LAZY VACUUM code is an indicator that progress
might be made in that direction. (Actually, I suppose if you look at it
in the right way, you might think that a backgroundable VACUUM *is* an
overwriting smgr, just an asynchronous implementation of it...)

> I thus think it would be better to remember all ctids per table.

If we do that then we still have a problem with overrunning memory
after a sufficiently large number of tuples. However, that'd improve
the constant factor by at least an order of magnitude, so it might be
worth doing as an intermediate step. Still have to figure out whether
the triggered-data-change business is significant or not.

regards, tom lane