On Jul 28, 2006, at 5:05 PM, Hannu Krosing wrote:
> Ühel kenal päeval, R, 2006-07-28 kell 12:38, kirjutas Jim C. Nasby:
>> There are other transactions to consider: user transactions that will
>> run a long time, but only hit a limited number of relations. These
>> are
>> as big a problem in an OLTP environment as vacuum is.
>
> These transactions are better kept out of an OLTP database, by their
> nature they belong to OLAP db :)
Sure, but that's not always possible/practical.
>> Rather than coming up with machinery that will special-case vacuum or
>> pg_dump, etc., I'd suggest thinking about a generic framework that
>> would
>> work for any long-runnnig transaction.
>
> So instead of actually *solving* one problem you suggest *thinking*
> about solving the general case ?
>
> We have been *thinking* about dead-space-map for at least three
> years by
> now.
No, I just wanted anyone who was actually going to work on this to
think about a more general fix. If the vacuum-only fix has a chance
of getting into core a version before the general case, I'll happily
take what I can get.
>> One possibility:
>>
>> Transaction flags itself as 'long-running' and provides a list of
>> exactly what relations it will be touching.
>>
>> That list is stored someplace a future vacuum can get at.
>>
>> The transaction runs, with additional checks that ensure it will not
>> touch any relations that aren't in the list it provided.
>
> I have thought abou that too, but checking on each data change seemed
> too expensive to me, at least for the first cut.
>
> There seems to be some ways to avoid actual checking for table-in-
> list,
> but you still have to check weather you have to check .
Well, presumably the check to see if you have to check would be
extremely cheap. As for checking that only approved relations are
touched, you can do that by analyzing the rules/triggers/etc that are
on all the tables involved. Or for a start, just disallow this on
tables with rules or triggers (well, we'd probably have to allow for
RI).
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461