Re: [PATCHES] [HACKERS] Per-table freeze limit proposal
On Tue, 2005-11-15 at 21:58 -0300, Alvaro Herrera wrote: In fact there's no problem because in D, just like in template1, all tuples are frozen. How should we mark this on the catalogs? I don't see any way. All tuples might be frozen or might not be, the point is you don't know. That's why you can't use FrozenTransactionId. Perhaps we should reinstate VACUUM FULL FREEZE to do just a FREEZE with a table lock and skip all that moving data around. Doesn't work either because of the argument above. What about assuming that if somebody executes a database-wide FREEZE, he knows what he is doing and thus we can mark datminxid as FrozenTransactionId? If you lock the table before FREEZE then you will guarantee that all rows will be frozen and you really can then set FrozenTransactionId. Making VACUUM FREEZE take full table locks seems like a very useful thing to me, and it would solve your problems also. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] Per-table freeze limit proposal
On Wed, 2005-11-16 at 08:31 +, Simon Riggs wrote: On Tue, 2005-11-15 at 21:58 -0300, Alvaro Herrera wrote: In fact there's no problem because in D, just like in template1, all tuples are frozen. How should we mark this on the catalogs? I don't see any way. All tuples might be frozen or might not be, the point is you don't know. That's why you can't use FrozenTransactionId. Perhaps we should reinstate VACUUM FULL FREEZE to do just a FREEZE with a table lock and skip all that moving data around. Doesn't work either because of the argument above. What about assuming that if somebody executes a database-wide FREEZE, he knows what he is doing and thus we can mark datminxid as FrozenTransactionId? If you lock the table before FREEZE then you will guarantee that all rows will be frozen and you really can then set FrozenTransactionId. Making VACUUM FREEZE take full table locks seems like a very useful thing to me, and it would solve your problems also. Thinking some more, when initdb issues VACUUM FREEZE we know for certain that nobody else is issuing commands against the database at that point, which is equivalent to a table lock. So we should be able to have a VACUUM FREEZE detect that and if so, set FrozenTransactionId. In normal concurrent running, I would like VACUUM FREEZE to issue a full table SHARE lock to ensure that we can set FrozenTransactionId for that also. Otherwise we will not be able to move frozen tables to read only media. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Per-table freeze limit proposal
On Wed, 2005-11-16 at 07:52 -0300, Alvaro Herrera wrote: Simon Riggs wrote: On Wed, 2005-11-16 at 08:31 +, Simon Riggs wrote: All tuples might be frozen or might not be, the point is you don't know. That's why you can't use FrozenTransactionId. Thinking some more, when initdb issues VACUUM FREEZE we know for certain that nobody else is issuing commands against the database at that point, which is equivalent to a table lock. So we should be able to have a VACUUM FREEZE detect that and if so, set FrozenTransactionId. In normal concurrent running, I would like VACUUM FREEZE to issue a full table SHARE lock to ensure that we can set FrozenTransactionId for that also. Otherwise we will not be able to move frozen tables to read only media. You missed one point however. Even if VACUUM FREEZE freezes all tuples, any transaction following that one is able to insert non-frozen tuples into the table. At that instant, having marked the table with Frozen is bogus, no matter what amount of locks you took on it. (OK I think we are getting there now, after my usual comms errors.) We need something stronger than VACUUM FREEZE then. Perhaps an ALTER TABLE READONLY. That would do a FREEZE and place a permanent table share lock, so we wouldn't need to set/unset the Frozen state. We'd do that as a permissions thing, rather than an actual lock. That way copies of the data could still be taken with ease and the copies would not themselves be READONLY. Not sure what you'd call it to make a whole database readonly all at once...but whatever we call it we know initdb wants to run it on template1. Then your original thought becomes fully viable. This is particularly important because I see the need to be able to freeze older data partitions and migrate them to readonly media as part of very high volume data applications. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] Per-table freeze limit proposal
On Mon, 2005-11-14 at 23:40 -0300, Alvaro Herrera wrote: The whole thing is pretty fragile is somebody manually updates a catalog. But we tell people not to do that, so it should be their fault, right? H...sounds scary. Cool ideas in the patch though. I discovered one problem with the whole approach. Per this patch, we only store normal Xids in relminxid/relvacuumxid. So if a table is completely frozen, we will store RecentXmin. We do this because it would actually be unsafe to store, say, FrozenXid: if another transaction stores/changes a tuple while we are vacuuming it, the Frozen mark wouldn't be correct and thus the table could be corrupted if a Xid wraparound happens (which is why we use RecentXmin in the first place: to cope with the possibility of someone else using the table during the vacuum.) Yep. And because VACUUM FULL FREEZE is no longer possible. The problem comes when this is done to template1, and it is copied to another database after some millions of transactions have come and go -- it will seem like the database has suffered wraparound. We would need to vacuum it completely after copied for the stats to be accurate. I don't understand the issue, can you explain more? I see no problem. If an identical copy gives a problem then surely template1 should also. I'm not sure what to do about that. I think storing FrozenXid may not actually be a totally bad idea. Comments? Its not a totally bad idea, but it has some risk, which where transactions are concerned is not really acceptable. Perhaps we should reinstate VACUUM FULL FREEZE to do just a FREEZE with a table lock and skip all that moving data around. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] Per-table freeze limit proposal
Simon Riggs wrote: On Mon, 2005-11-14 at 23:40 -0300, Alvaro Herrera wrote: The whole thing is pretty fragile is somebody manually updates a catalog. But we tell people not to do that, so it should be their fault, right? H...sounds scary. Cool ideas in the patch though. Yeah, well, actually the problem is solved very easily by setting the pg_database tuple manually, either to InvalidTransactionId or to the minimum computed from pg_class. The problem comes when this is done to template1, and it is copied to another database after some millions of transactions have come and go -- it will seem like the database has suffered wraparound. We would need to vacuum it completely after copied for the stats to be accurate. I don't understand the issue, can you explain more? I see no problem. If an identical copy gives a problem then surely template1 should also. Actually, template1 has the problem too. The scenario is this: - template1 is freezed. datminxid - X - a long time passes, say INT_MAX * 0.75 transactions - a new database D is created, which coming from template1 has datminxid=X - the Xid counter is past the vacuum horizon for D.datminxid, so the system determines that the Xid counter could be wrapped already. - The system automatically decides to stop accepting new transactions. In fact there's no problem because in D, just like in template1, all tuples are frozen. How should we mark this on the catalogs? I don't see any way. Note that setting relminxid = FrozenTransactionId is bogus in any case, because even if we correctly lock and freeze the table, the next transaction after the vacuum could insert a new tuple into the table. But we don't want INSERT to be checking pg_class.relminxid! (Or do we?) Now, restating the problem, certainly template1 has the problem too. In fact we have a bigger problem: we are forcing all tables to be vacuumed every so often, even if they have been completely frozen before! This is because setting relminxid = Frozen is really bogus. I'm not sure what to do about that. I think storing FrozenXid may not actually be a totally bad idea. Comments? Its not a totally bad idea, but it has some risk, which where transactions are concerned is not really acceptable. Perhaps we should reinstate VACUUM FULL FREEZE to do just a FREEZE with a table lock and skip all that moving data around. Doesn't work either because of the argument above. What about assuming that if somebody executes a database-wide FREEZE, he knows what he is doing and thus we can mark datminxid as FrozenTransactionId? Sadly, I see all this as proof that the whole idea doesn't work. It seems better than the current state of the system, where we rely on the user to do certain things, or on pgstat which is inherently inexact. But there is a big hole in the whole reasoning which hasn't been filled yet. Any ideas welcome. The idea of any insert/delete/update operation checking a bit in the Relation and resetting relminxid to TopTransactionId if it's marked Frozen is the only one I have right now. What do people think about it? -- Alvaro Herrerahttp://www.PlanetPostgreSQL.org No hay cielo posible sin hundir nuestras raíces en la profundidad de la tierra(Malucha Pinto) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
