Re: [HACKERS] making an unlogged table logged

2011-01-08 Thread Robert Haas
On Tue, Jan 4, 2011 at 9:41 PM, Robert Haas robertmh...@gmail.com wrote: Generally, to do this, it would be necessary to do the following things (plus anything I'm forgetting): It occurs to me that almost exactly this same procedure could be used to make a *temporary* table into a permanent

Re: [HACKERS] making an unlogged table logged

2011-01-07 Thread Dimitri Fontaine
Robert Haas robertmh...@gmail.com writes: Adding logical replication would be a lot of work but we'd get a lot of collateral benefits. Imagine that PG had There has been extensive discussions at last pgcon about that (mainly in the hallway track, but also in the devroom we had) to work on

Re: [HACKERS] making an unlogged table logged

2011-01-07 Thread Josh Berkus
Jan Wieck started a discussion back then to offer the basics we need in core as far as queuing goes, here: http://archives.postgresql.org/pgsql-hackers/2010-05/msg01209.php What happened to that effort? Stalled due to lack of manpower, currently. --

Re: [HACKERS] making an unlogged table logged

2011-01-05 Thread Robert Haas
On Tue, Jan 4, 2011 at 10:56 PM, Rob Wultsch wult...@gmail.com wrote: 1. Could the making a table logged be a non-exclusive lock if the ALTER is allowed to take a full checkpoint? No, that doesn't solve either of the two problems I described, unfortunately. 2. Unlogged to logged has giant use

Re: [HACKERS] making an unlogged table logged

2011-01-05 Thread David Fetter
On Wed, Jan 05, 2011 at 09:04:08AM -0500, Robert Haas wrote: On Tue, Jan 4, 2011 at 10:56 PM, Rob Wultsch wult...@gmail.com wrote: 1. Could the making a table logged be a non-exclusive lock if the ALTER is allowed to take a full checkpoint? No, that doesn't solve either of the two problems

Re: [HACKERS] making an unlogged table logged

2011-01-05 Thread Rob Wultsch
On Wed, Jan 5, 2011 at 7:48 AM, David Fetter da...@fetter.org wrote: On Wed, Jan 05, 2011 at 09:04:08AM -0500, Robert Haas wrote: On Tue, Jan 4, 2011 at 10:56 PM, Rob Wultsch wult...@gmail.com wrote: 1. Could the making a table logged be a non-exclusive lock if the ALTER is allowed to take a

Re: [HACKERS] making an unlogged table logged

2011-01-05 Thread Josh Berkus
On 1/4/11 6:41 PM, Robert Haas wrote: Going the other direction ought to be possible too, although it seems somewhat less useful. Actually, it's more useful; many people who *upgrade* to 9.1 will wand to convert one or two of their tables to unlogged. Note that the conversion both ways can be

Re: [HACKERS] making an unlogged table logged

2011-01-05 Thread Robert Haas
On Wed, Jan 5, 2011 at 2:02 PM, Josh Berkus j...@agliodbs.com wrote: Note that the conversion both ways can be worked around with the CREATE/ALTER TABLE dosiedo, so I don't think either of these is critical for 9.1.  Unless you feel like working on them, of course. As I said in my OP, I don't

Re: [HACKERS] making an unlogged table logged

2011-01-05 Thread Simon Riggs
On Tue, 2011-01-04 at 21:41 -0500, Robert Haas wrote: 6. If wal_level != minimal, XLOG every page of every fork except the init fork, for both the table and the associated indexes. (Note that this step also requires an AccessExclusiveLock rather than some weaker lock, because of the arbitrary

Re: [HACKERS] making an unlogged table logged

2011-01-05 Thread Robert Haas
On Wed, Jan 5, 2011 at 2:36 PM, Simon Riggs si...@2ndquadrant.com wrote: The lock strength selected on the master doesn't need to be the same as the lock strength on the standby. You could quite easily generate AEL lock records to send to standby, without actually taking that lock level on the

Re: [HACKERS] making an unlogged table logged

2011-01-05 Thread A.M.
On Jan 5, 2011, at 2:37 PM, Robert Haas wrote: On Wed, Jan 5, 2011 at 2:36 PM, Simon Riggs si...@2ndquadrant.com wrote: The lock strength selected on the master doesn't need to be the same as the lock strength on the standby. You could quite easily generate AEL lock records to send to

Re: [HACKERS] making an unlogged table logged

2011-01-05 Thread Josh Berkus
Hm- if the unlogged tables are being used as HTTP transient state storage, it would be handy to have that (admittedly non-essential) data on the standby when it becomes master, even if there are no guarantees surrounding the data beyond it looked like this at some point. Since the tables

Re: [HACKERS] making an unlogged table logged

2011-01-05 Thread Dimitri Fontaine
Josh Berkus j...@agliodbs.com writes: It would be useful (this would allow per-standby buffer tables, for that matter), but it would also be tremendously difficult. Seems worthy of a TODO, though. Don't we have anything covering xid-less tables in the TODO already? The read-only tables

Re: [HACKERS] making an unlogged table logged

2011-01-05 Thread Robert Haas
On Wed, Jan 5, 2011 at 3:58 PM, Josh Berkus j...@agliodbs.com wrote: Hm- if the unlogged tables are being used as HTTP transient state storage, it would be handy to have that (admittedly non-essential) data on the standby when it becomes master, even if there are no guarantees surrounding

Re: [HACKERS] making an unlogged table logged

2011-01-05 Thread Josh Berkus
On 1/5/11 3:14 PM, Robert Haas wrote: I think that's probably a dead end - just to take one example, if you don't sync often enough, the standby might have transaction ID wraparound problems. Autovacuum on the master will prevent that for permanent tables, but not for an

Re: [HACKERS] making an unlogged table logged

2011-01-05 Thread Robert Haas
On Wed, Jan 5, 2011 at 6:25 PM, Josh Berkus j...@agliodbs.com wrote: On 1/5/11 3:14 PM, Robert Haas wrote: I think that's probably a dead end - just to take one example, if you don't sync often enough, the standby might have transaction ID wraparound problems.  Autovacuum on the master will

[HACKERS] making an unlogged table logged

2011-01-04 Thread Robert Haas
Somebody asked about this on Depesz's blog today, and I think it's come up here before too, so I thought it might be worth my writing up a few comments on this. I don't think I'm going to have time to work on this any time soon, but if someone else wants to work up a patch, I'm game to review. I

Re: [HACKERS] making an unlogged table logged

2011-01-04 Thread Rob Wultsch
On Tue, Jan 4, 2011 at 7:41 PM, Robert Haas robertmh...@gmail.com wrote: Somebody asked about this on Depesz's blog today, and I think it's come up here before too, so I thought it might be worth my writing up a few comments on this.  I don't think I'm going to have time to work on this any

Re: [HACKERS] making an unlogged table logged

2011-01-04 Thread Joshua D. Drake
-- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company A couple thoughts: 1. Could the making a table logged be a non-exclusive lock if the ALTER is allowed to take a full checkpoint? If possible, that would certainly be better. If the bgwriter is