Re: [HACKERS] Min Xid problem proposal
On Fri, 2005-12-09 at 12:32 -0500, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: My proposal to solve that problem, is to make any transaction that inserts or modifies tuples in a table that is marked as frozen, unfreeze it first. The problem I had last time was finding a good spot in the code for doing so. I'm now proposing to do it in the parser, in setTargetTable(). My god, no. Do you have any idea how many paths for updates you've missed? (Think about prepared plans for starters.) Furthermore, you can't do this in the way you propose (non-WAL-logged update to pg_class). What if the system crashes without ever having written this update to disk? The inserted tuples might have made it --- whether they're committed or not doesn't matter, you've still blown it. I don't see any very good argument for allowing this mechanism to set minxid = FrozenXid in the first place. If there are only frozenXid in the table, set minxid = current XID. That eliminates the entire problem at a stroke. (Yes, I know what you are going to say. The idea of freezing a table and then never having to vacuum it at all is NOT worth the cost of putting in a mechanism that would guarantee its safety.) From what's been said VACUUM FREEZE will not alter the fact that a frozen table will need vacuuming again in the future and so cannot ever be read-only. I can't really see any reason to run VACUUM FREEZE... If you want to make a table read-only forever, we need a separate command to do that, ISTM. ALTER TABLE ... READONLY could set minXid = FrozenTransactionId, indicating no further VACUUMs required, ever. We can then disallow INSERT/UPDATE/DELETE against the table in the permissions layer. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Min Xid problem proposal
Simon Riggs wrote: From what's been said VACUUM FREEZE will not alter the fact that a frozen table will need vacuuming again in the future and so cannot ever be read-only. I can't really see any reason to run VACUUM FREEZE... Yeah. If you want to make a table read-only forever, we need a separate command to do that, ISTM. Let's get this goose cooked and then we can improve it. This patch has been waiting on my queue for too long. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Min Xid problem proposal
Alvaro Herrera [EMAIL PROTECTED] writes: My proposal to solve that problem, is to make any transaction that inserts or modifies tuples in a table that is marked as frozen, unfreeze it first. The problem I had last time was finding a good spot in the code for doing so. I'm now proposing to do it in the parser, in setTargetTable(). My god, no. Do you have any idea how many paths for updates you've missed? (Think about prepared plans for starters.) Furthermore, you can't do this in the way you propose (non-WAL-logged update to pg_class). What if the system crashes without ever having written this update to disk? The inserted tuples might have made it --- whether they're committed or not doesn't matter, you've still blown it. I don't see any very good argument for allowing this mechanism to set minxid = FrozenXid in the first place. If there are only frozenXid in the table, set minxid = current XID. That eliminates the entire problem at a stroke. (Yes, I know what you are going to say. The idea of freezing a table and then never having to vacuum it at all is NOT worth the cost of putting in a mechanism that would guarantee its safety.) regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Min Xid problem proposal
Tom Lane wrote: I don't see any very good argument for allowing this mechanism to set minxid = FrozenXid in the first place. If there are only frozenXid in the table, set minxid = current XID. That eliminates the entire problem at a stroke. Ok, so I shall go back to the original patch, which did exactly this. Is it OK for applying? (I'm using RecentXmin instead of current XID though, because a currently-running transaction could insert tuples in the table I just vacuumed.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Min Xid problem proposal
Alvaro Herrera [EMAIL PROTECTED] writes: Ok, so I shall go back to the original patch, which did exactly this. Is it OK for applying? I haven't looked at it ... when did you post it exactly? (I'm using RecentXmin instead of current XID though, because a currently-running transaction could insert tuples in the table I just vacuumed.) Duh, right. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Min Xid problem proposal
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Ok, so I shall go back to the original patch, which did exactly this. Is it OK for applying? I haven't looked at it ... when did you post it exactly? From: Alvaro Herrera [EMAIL PROTECTED] To: Tom Lane [EMAIL PROTECTED] Cc: Patches pgsql-patches@postgresql.org Date: Mon, 14 Nov 2005 23:40:52 -0300 Subject: Re: [PATCHES] [HACKERS] Per-table freeze limit proposal I have a version that applies cleanly to current CVS tip. Do I post it again? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Min Xid problem proposal
Alvaro Herrera [EMAIL PROTECTED] writes: I have a version that applies cleanly to current CVS tip. Do I post it again? No need unless you think the changes are significant. I'll try to look over the patch soon. regards, tom lane ---(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
