Re: xlogdump fixups and WAL log question.

Поиск
Список
Период
Сортировка
От Theo Schlossnagle
Тема Re: xlogdump fixups and WAL log question.
Дата
Msg-id D04777A3-CD8C-4702-BA3C-87B83A451A6A@omniti.com
обсуждение исходный текст
Ответ на Re: xlogdump fixups and WAL log question.  ("Simon Riggs" <simon@2ndquadrant.com>)
Список pgsql-hackers
On Oct 21, 2006, at 4:40 PM, Simon Riggs wrote:

> On Sat, 2006-10-21 at 15:17 -0400, Theo Schlossnagle wrote:
>> On Oct 21, 2006, at 3:12 PM, Simon Riggs wrote:
>>
>>> On Sat, 2006-10-21 at 09:00 -0400, Theo Schlossnagle wrote:
>>>> On Oct 21, 2006, at 6:08 AM, Martijn van Oosterhout wrote:
>>>>
>>>>> On Sat, Oct 21, 2006 at 10:37:51AM +0100, Simon Riggs wrote:
>>>>>> Turning off WAL is a difficult topic. Without it you have no  
>>>>>> crash
>>>>>> recovery, which IMHO everybody says they don't care about until
>>>>>> they
>>>>>> crash, then they realise. It's hard to be selective about
>>>>>> writing WAL
>>>>>> for specific operations also.
>>>>>
>>>>> It's been discussed before. One idea is to declare tables without
>>>>> logging. The idea being that during recovery those tables and
>>>>> related
>>>>> indexes are simply truncated. No foreign keys allowed. Obviously
>>>>> they
>>>>> will not be saved via PITR either.
>>>>>
>>>>> Put another way, the table structure is saved in WAL, but the data
>>>>> isn't.
>>>>
>>>> This is exactly what I'd like.  Simon suggested turning off WAL
>>>> during the loads as a possible hack solution.  The reason this  
>>>> won't
>>>> work is that we snap all the time, lots of tables.  We have between
>>>> 2000 and 4000 snapshot operations per day (throughout).  At the  
>>>> same
>>>> time we have reporting queries running (that create and/or populate
>>>> other tables) that last from 5 minutes to 18 hours.  It is  
>>>> important
>>>> that we run everything but the snapshots with WAL on (as we must  
>>>> have
>>>> PITR -- sans snapshots)
>>>
>>> These tables are loaded once then read-only, yes?
>>
>> No, they are loaded, and then reloaded, and then reloaded. Queries
>> that use them will get the most recently loaded version of them.  It
>> meets a business rule like: table foo on the warehouse should be
>> representative of version of table foo on OLTP no older than 30  
>> minutes.
>
> But they can be re-created anew with the same name each time? Or I  
> guess
> not, but you redefine a view every 30 minutes to point to the latest
> one?

closest to the latter.  A view is redefined when the new snapshot is  
complete.

> If so, then I have a patch that will speed up COPY when in the same
> transaction as the table that created it. I've finally fixed a bug  
> in my
> earlier prototypes that seems to make that work now, in all cases.
>
> I was being slightly slow before; I thought this was a new requirement
> but its just the old one slightly restated.

We don't use COPY.  We directly INSERT INTO target_snap SELECT * from  
remote_select(...) t(cast);

remote_select is part of dbi-link.

// Theo Schlossnagle
// CTO -- http://www.omniti.com/~jesus/
// OmniTI Computer Consulting, Inc. -- http://www.omniti.com/




В списке pgsql-hackers по дате отправления:

Предыдущее
От: Volkan YAZICI
Дата:
Сообщение: estimated_count() implementation
Следующее
От: Peter Eisentraut
Дата:
Сообщение: Call for translations