Обсуждение: Question about (lazy) vacuum
With all this code to handle ignoring vacuum transactions in calculating the global xmin it just occurred to me to wonder: Does lazy vacuum need a transaction at all? It doesn't do the tuple moving tricks with xvac that vacuum full does so does lazy vacuum's xid ever make it into tables? Couldn't it just use GetTopTransactionId instead of its own xid? Would this cause any problems? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Ühel kenal päeval, K, 2006-08-23 kell 05:23, kirjutas Gregory Stark: > With all this code to handle ignoring vacuum transactions in calculating the > global xmin it just occurred to me to wonder: Does lazy vacuum need a > transaction at all? It doesn't do the tuple moving tricks with xvac that > vacuum full does so does lazy vacuum's xid ever make it into tables? Couldn't > it just use GetTopTransactionId instead of its own xid? Would this cause any > problems? When I asked the same question, I was told that a lot of core functionality vacuum uses needs to be in transaction. I guess bad things can happen, if some other backend ends a transaction you claim to be in. And it is not so much about what ends up in tables, but about what other backends think. BTW, I think that CONCURRENT CREATE INDEX should be modified to use long transactions which actually build the index and are ignored by vacuum and short ones which write data to system tables and are not ignored. That way we have one less obstacle for keeping high-update tables in shape. > -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com
Hannu Krosing <hannu@skype.net> writes: > When I asked the same question, I was told that a lot of core > functionality vacuum uses needs to be in transaction. I guess bad things > can happen, if some other backend ends a transaction you claim to be in. > > And it is not so much about what ends up in tables, but about what other > backends think. Well the only way other backends would find out about vacuum's xid is via tables or via the PGProc table. But they aren't going to care about vacuum's transaction status in PGProc unless they're vacuum and then we're going out of our way to make sure it doesn't care. Vacuum doesn't take any record locks so the xid isn't necessary for that. Vacuum does WAL log some entries via log_heap_clean but I'm unclear whether that requires a transaction, I don't see it being used anywhere. > BTW, I think that CONCURRENT CREATE INDEX should be modified to use long > transactions which actually build the index and are ignored by vacuum > and short ones which write data to system tables and are not ignored. > That way we have one less obstacle for keeping high-update tables in > shape. Hm, that might be worth thinking about. Note that it locks out vacuum from running on the table it's running on so it would only help in allowing other tables to be vacuumed effectively. If we ever get per-table xmin then it would be entirely unnecessary. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Ühel kenal päeval, K, 2006-08-23 kell 08:11, kirjutas Greg Stark: > Hannu Krosing <hannu@skype.net> writes: > > BTW, I think that CONCURRENT CREATE INDEX should be modified to use long > > transactions which actually build the index and are ignored by vacuum > > and short ones which write data to system tables and are not ignored. > > That way we have one less obstacle for keeping high-update tables in > > shape. > > Hm, that might be worth thinking about. Note that it locks out vacuum from > running on the table it's running on so it would only help in allowing other > tables to be vacuumed effectively. Exactly. The whole point of one vacuum not blocking others is to make sure, that a vacuum on a huge table would not disable vacuuming and thereby reusing rows of much much smaller tables. Locking out vacuum on the table itself is ok, as the runtimes of vacuum and concurrent create index on a same table are in the same order. -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com
Gregory Stark wrote: > > With all this code to handle ignoring vacuum transactions in calculating the > global xmin it just occurred to me to wonder: Does lazy vacuum need a > transaction at all? It doesn't do the tuple moving tricks with xvac that > vacuum full does so does lazy vacuum's xid ever make it into tables? Couldn't > it just use GetTopTransactionId instead of its own xid? Would this cause any > problems? Two points: 1. it needs to get locks 2. GetTopTransactionId returns "its own Xid" anyway ... The point of getting locks is that it needs the lock manager to be able to release the lock if the transaction rolls back. (The lock object itself does not hold anything about the transaction itself AFAIR.) -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Hannu Krosing <hannu@skype.net> writes:
> Ühel kenal päeval, K, 2006-08-23 kell 05:23, kirjutas Gregory Stark:
>> global xmin it just occurred to me to wonder: Does lazy vacuum need a
>> transaction at all?
> When I asked the same question, I was told that a lot of core
> functionality vacuum uses needs to be in transaction.
Locks for instance; you certainly need a lock on the table. In general
a whole lot of the backend functionality is bound up in the transaction
start/stop mechanisms, and quite a bit of rearchitecting would be needed
to do very much outside a transaction. Doesn't really seem worth it.
regards, tom lane