Re: [HACKERS] Unlogged tables cleanup

On Fri, Dec 9, 2016 at 4:54 AM, Robert Haas wrote: > On Wed, Dec 7, 2016 at 11:20 PM, Michael Paquier > wrote: >> OK, I rewrote a bit the patch as attached. What do you think? > > Committed and back-patched all the way back to 9.2. Thanks! >>>

Re: [HACKERS] Unlogged tables cleanup

On Wed, Dec 7, 2016 at 11:20 PM, Michael Paquier wrote: > OK, I rewrote a bit the patch as attached. What do you think? Committed and back-patched all the way back to 9.2. >> Right (I think). If we set and clear delayChkpt around this work, we >> don't need the

Re: [HACKERS] Unlogged tables cleanup

On Thu, Dec 8, 2016 at 6:03 AM, Robert Haas wrote: > Michael, your greetings were passed on to me with a request that I > look at this thread. Thanks for showing up! > On Fri, Nov 18, 2016 at 12:33 PM, Michael Paquier > wrote: More

Re: [HACKERS] Unlogged tables cleanup

Michael, your greetings were passed on to me with a request that I look at this thread. On Fri, Nov 18, 2016 at 12:33 PM, Michael Paquier wrote: >>> More seriously, if there could be more details regarding that, I would >>> think that we could say something like

Re: [HACKERS] Unlogged tables cleanup

On Thu, Nov 17, 2016 at 7:06 AM, Robert Haas wrote: > Yeah, but surely it's obvious that if you don't WAL log it, it's not > going to work for archiving or streaming. It's a lot less obvious why > you have to WAL log it when you're not doing either of those things if >

Re: [HACKERS] Unlogged tables cleanup

On Wed, Nov 16, 2016 at 11:55 PM, Michael Paquier wrote: > On Wed, Nov 16, 2016 at 7:09 PM, Robert Haas wrote: >> On Wed, Nov 16, 2016 at 3:54 PM, Michael Paquier >> wrote: >>> Indeed I missed this comment block.

Re: [HACKERS] Unlogged tables cleanup

On Wed, Nov 16, 2016 at 7:09 PM, Robert Haas wrote: > On Wed, Nov 16, 2016 at 3:54 PM, Michael Paquier > wrote: >> Indeed I missed this comment block. Please let me suggest the following >> instead: >> /* >> * Set up an init fork for an

Re: [HACKERS] Unlogged tables cleanup

On Wed, Nov 16, 2016 at 3:54 PM, Michael Paquier wrote: > On Wed, Nov 16, 2016 at 11:45 AM, Robert Haas wrote: >> The header comment for heap_create_init_fork() says this: >> >> /* >> * Set up an init fork for an unlogged table so that it can be

Re: [HACKERS] Unlogged tables cleanup

On Wed, Nov 16, 2016 at 11:45 AM, Robert Haas wrote: > The header comment for heap_create_init_fork() says this: > > /* > * Set up an init fork for an unlogged table so that it can be correctly > * reinitialized on restart. Since we're going to do an immediate sync, we >

Re: [HACKERS] Unlogged tables cleanup

On Thu, Nov 10, 2016 at 9:25 PM, Michael Paquier wrote: > On Thu, Nov 10, 2016 at 10:52 PM, Kuntal Ghosh > wrote: >> On Thu, Nov 10, 2016 at 3:42 PM, Michael Paquier >> wrote: >>> Nah. Looking at the code the fix

Re: [HACKERS] Unlogged tables cleanup

On Thu, Nov 10, 2016 at 10:52 PM, Kuntal Ghosh wrote: > On Thu, Nov 10, 2016 at 3:42 PM, Michael Paquier > wrote: >> Nah. Looking at the code the fix is quite obvious. >> heap_create_init_fork() is checking for XLogIsNeeded() to decide if >>

Re: [HACKERS] Unlogged tables cleanup

On Thu, Nov 10, 2016 at 3:42 PM, Michael Paquier wrote: > On Thu, Nov 10, 2016 at 5:15 PM, Michael Paquier > wrote: >> Okay, so what happens is that the CREATE TABLESPACE record removes the >> tablespace directory and recreates a fresh one,

Re: [HACKERS] Unlogged tables cleanup

On Thu, Nov 10, 2016 at 5:15 PM, Michael Paquier wrote: > Okay, so what happens is that the CREATE TABLESPACE record removes the > tablespace directory and recreates a fresh one, but as no CREATE > records are created for unlogged tables the init fork is not >

Re: [HACKERS] Unlogged tables cleanup

On Thu, Nov 10, 2016 at 4:33 PM, Michael Paquier wrote: > On Thu, Nov 10, 2016 at 4:23 PM, konstantin knizhnik > wrote: >> No, it is latest sources from Postgres repository. >> Please notice that you should create new database and tablespace

Re: [HACKERS] Unlogged tables cleanup

On Thu, Nov 10, 2016 at 4:23 PM, konstantin knizhnik wrote: > No, it is latest sources from Postgres repository. > Please notice that you should create new database and tablespace to reproduce > this issue. > So actually the whole sequence is > > mkdir fs > initdb -D

Re: [HACKERS] Unlogged tables cleanup

On Nov 10, 2016, at 10:17 AM, Michael Paquier wrote: > > Hm.. I cannot reproduce what you see on Linux or macos. Perhaps you > have locally a standby pointing as well to this tablespace? No, it is latest sources from Postgres repository. Please notice that you should create new database and

Re: [HACKERS] Unlogged tables cleanup

On Thu, Nov 10, 2016 at 3:29 AM, Robert Haas wrote: > On Wed, Nov 9, 2016 at 11:56 AM, Konstantin Knizhnik > wrote: >> Now simulate server crash using using "pkill -9 postgres". >> >> knizhnik@knizhnik:~/dtm-data$ rm -f logfile ; pg_ctl -D

Re: [HACKERS] Unlogged tables cleanup

On Wed, Nov 9, 2016 at 11:56 AM, Konstantin Knizhnik wrote: > Now simulate server crash using using "pkill -9 postgres". > > knizhnik@knizhnik:~/dtm-data$ rm -f logfile ; pg_ctl -D pgsql.master -l > logfile start > pg_ctl: another server might be running; trying to

[HACKERS] Unlogged tables cleanup

Hi, hackers I wonder if such behavior can be considered as a bug: knizhnik@knizhnik:~/dtm-data$ psql postgres psql (10devel) Type "help" for help. postgres=# create tablespace fs location '/home/knizhnik/dtm-data/fs'; CREATE TABLESPACE postgres=# set default_tablespace=fs; SET postgres=#

[HACKERS] Unlogged tables can vanish after a crash

I observed an interesting (and I think buggy) behaviour today after one of our clusters crashed due to an out of space condition in the data directory. Five databases in that cluster have each one unlogged table. The log reads as follows: PANIC could not write to file pg_xlog/xlogtemp.1820: No

Re: [HACKERS] Unlogged tables can vanish after a crash

Hi, On 2014-11-19 11:26:56 +, Albe Laurenz wrote: I observed an interesting (and I think buggy) behaviour today after one of our clusters crashed due to an out of space condition in the data directory. Hah, just a couple days I pushed a fix for that ;)

Re: [HACKERS] Unlogged tables can vanish after a crash

Andres Freund wrote: On 2014-11-19 11:26:56 +, Albe Laurenz wrote: I observed an interesting (and I think buggy) behaviour today after one of our clusters crashed due to an out of space condition in the data directory. Hah, just a couple days I pushed a fix for that ;)

Re: [HACKERS] unlogged tables vs. GIST

Hi Heikki, On Mon, Feb 11, 2013 at 7:28 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 11.02.2013 08:44, Jeevan Chalke wrote: Hi, Any review comments on this ? Sorry for the delay. I did some minor cleanup on this. I added code to pg_resetxlog and pg_controldata to reset /

Re: [HACKERS] unlogged tables vs. GIST

On 11.02.2013 08:44, Jeevan Chalke wrote: Hi, Any review comments on this ? Sorry for the delay. I did some minor cleanup on this. I added code to pg_resetxlog and pg_controldata to reset / display the current unlogged LSN value. I moved the static counter, for temporary relations, back to

Re: [HACKERS] unlogged tables vs. GIST

Hi, Any review comments on this ? On Tue, Jan 29, 2013 at 6:03 PM, Jeevan Chalke jeevan.cha...@enterprisedb.com wrote: Hi Heikki, On Mon, Jan 28, 2013 at 2:34 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 23.01.2013 17:30, Robert Haas wrote: On Wed, Jan 23, 2013 at 4:04

Re: [HACKERS] unlogged tables vs. GIST

Hi Heikki, On Mon, Jan 28, 2013 at 2:34 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 23.01.2013 17:30, Robert Haas wrote: On Wed, Jan 23, 2013 at 4:04 AM, Jeevan Chalke jeevan.chalke@enterprisedb.**com jeevan.cha...@enterprisedb.com wrote: I guess my earlier patch, which

Re: [HACKERS] unlogged tables vs. GIST

On 23.01.2013 17:30, Robert Haas wrote: On Wed, Jan 23, 2013 at 4:04 AM, Jeevan Chalke jeevan.cha...@enterprisedb.com wrote: I guess my earlier patch, which was directly incrementing ControlFile-unloggedLSN counter was the concern as it will take ControlFileLock several times. In this version

Re: [HACKERS] unlogged tables vs. GIST

On Mon, Jan 28, 2013 at 4:04 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Do we need to do anything to unloggedLSN in pg_resetxlog? Does the server go into recovery after pg_resetxlog? If so, no. If not, probably, but I have no idea what. There's no safe value in that case; what we

Re: [HACKERS] unlogged tables vs. GIST

On Wed, Jan 16, 2013 at 3:24 AM, Robert Haas robertmh...@gmail.com wrote: On Tue, Jan 15, 2013 at 4:26 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I think that might be acceptable from a performance point of view - after all, if the index is unlogged,

Re: [HACKERS] unlogged tables vs. GIST

On Wed, Jan 23, 2013 at 4:04 AM, Jeevan Chalke jeevan.cha...@enterprisedb.com wrote: Yes. I guess my earlier patch, which was directly incrementing ControlFile-unloggedLSN counter was the concern as it will take ControlFileLock several times. In this version of patch I did what Robert has

Re: [HACKERS] unlogged tables vs. GIST

On 15.01.2013 08:54, Jeevan Chalke wrote: For (2), I have added a new function called, GetXLogRecPtrForUnloogedRel() which returns a fake LSN for GiST indexes. However, I have removed GetXLogRecPtrForTemp() function and added its functionality inside this new function itself to avoid complexity.

Re: [HACKERS] unlogged tables vs. GIST

On Tue, Jan 15, 2013 at 1:10 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 15.01.2013 08:54, Jeevan Chalke wrote: For (2), I have added a new function called, GetXLogRecPtrForUnloogedRel() which returns a fake LSN for GiST indexes. However, I have removed GetXLogRecPtrForTemp()

Re: [HACKERS] unlogged tables vs. GIST

On 15.01.2013 20:33, Robert Haas wrote: On Tue, Jan 15, 2013 at 1:10 PM, Heikki Linnakangas Could we stash the counter e.g. in the root page of the index? That would require maintaining a counter per table rather than a single global counter, which would be bad because then we'd need to store

Re: [HACKERS] unlogged tables vs. GIST

Robert Haas robertmh...@gmail.com writes: On Tue, Jan 15, 2013 at 1:10 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Could we stash the counter e.g. in the root page of the index? That would require maintaining a counter per table rather than a single global counter, which would be

Re: [HACKERS] unlogged tables vs. GIST

On Tue, Jan 15, 2013 at 1:48 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Jan 15, 2013 at 1:10 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Could we stash the counter e.g. in the root page of the index? That would require maintaining a

Re: [HACKERS] unlogged tables vs. GIST

On 15.01.2013 20:48, Tom Lane wrote: Robert Haasrobertmh...@gmail.com writes: On Tue, Jan 15, 2013 at 1:10 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Could we stash the counter e.g. in the root page of the index? That would require maintaining a counter per table rather than a

Re: [HACKERS] unlogged tables vs. GIST

On 2013-01-15 20:58:00 +0200, Heikki Linnakangas wrote: On 15.01.2013 20:48, Tom Lane wrote: Robert Haasrobertmh...@gmail.com writes: On Tue, Jan 15, 2013 at 1:10 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Could we stash the counter e.g. in the root page of the index? That

Re: [HACKERS] unlogged tables vs. GIST

On Tue, Jan 15, 2013 at 1:58 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: I think what Heikki had in mind was that the copy in the index would be the authoritative one, not some image in shared memory. This'd imply dirtying the root page on every insert, as well as increased

Re: [HACKERS] unlogged tables vs. GIST

Robert Haas robertmh...@gmail.com writes: I think that might be acceptable from a performance point of view - after all, if the index is unlogged, you're saving the cost of WAL - but I guess I still prefer a generic solution to this problem (a generalization of GetXLogRecPtrForTemp) rather

Re: [HACKERS] unlogged tables vs. GIST

On Tue, Jan 15, 2013 at 4:26 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I think that might be acceptable from a performance point of view - after all, if the index is unlogged, you're saving the cost of WAL - but I guess I still prefer a generic solution

Re: [HACKERS] unlogged tables vs. GIST

Hi Robert / Tom I think to support GiST with unlogged table we need to do three things: 1. Teach the buffer manager that the LSN of a page not marked BM_PERMANENT can be ignored 2. Teach GetXLogRecPtrForTemp() to allocate fake LSNs for GiST buffers using a 64-bit, counter that is persisted

[HACKERS] Unlogged tables and BufferSync()

In BufferSync(), we have this: /* * Unless this is a shutdown checkpoint, we write only permanent, dirty * buffers. But at shutdown time, we write all dirty buffers. */ if (!(flags CHECKPOINT_IS_SHUTDOWN)) flags |= BM_PERMANENT;

Re: [HACKERS] Unlogged tables and BufferSync()

On Fri, Jan 6, 2012 at 7:52 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: In BufferSync(), we have this:        /*         * Unless this is a shutdown checkpoint, we write only permanent, dirty         * buffers.  But at shutdown time, we write all dirty buffers.        

Re: [HACKERS] Unlogged tables, persistent kind

On Tue, Apr 26, 2011 at 8:49 AM, Leonardo Francalanci m_li...@yahoo.it wrote: If that 1%  is random (not time/transaction related), usually you'd rather have an empty  table. Why do you think it would be random? Heap blocks would be zeroed if they were found to be damaged, following a

Re: [HACKERS] Unlogged tables, persistent kind

On Tue, May 3, 2011 at 8:21 PM, Simon Riggs si...@2ndquadrant.com wrote: The current assessment is that UNLOGGED tables are useful only for running a data cache. If the database crashes, then the table is truncated and you must refill the cache. If that is the case, then it must surely be

Re: [HACKERS] Unlogged tables, persistent kind

Simon Riggs si...@2ndquadrant.com writes: You're assuming that there are referential links *from* other tables to the table with damage. In which case you would be correct. But of course, if you needed that data for integrity you would never do that, so the problem is a nonexistent use case.

Re: [HACKERS] Unlogged tables, persistent kind

On Tue, May 3, 2011 at 4:54 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: You're assuming that there are referential links *from* other tables to the table with damage. In which case you would be correct. But of course, if you needed that data for integrity

Re: [HACKERS] Unlogged tables, persistent kind

If that 1% is random (not time/transaction related), usually you'd rather have an empty table. Why do you think it would be random? Heap blocks would be zeroed if they were found to be damaged, following a crash. If you erase full blocks, you have no idea what data you erased; it could

Re: [HACKERS] Unlogged tables, persistent kind

2011/4/25 Christopher Browne cbbro...@gmail.com: On Mon, Apr 25, 2011 at 2:03 PM, Jesper Krogh jes...@krogh.cc wrote: On 2011-04-25 20:00, Leonardo Francalanci wrote: The amount of data loss on a big table will be 1% of the data loss caused by truncating the whole table. If that 1% is random

Re: [HACKERS] Unlogged tables, persistent kind

On Apr 24, 2011, at 1:22 PM, Simon Riggs si...@2ndquadrant.com wrote: Unlogged tables are a good new feature. Thanks. I noticed Bruce had mentioned they were the equivalent of NoSQL, which I don't really accept. Me neither. I thought that was poorly said. Heap blocks would be zeroed if

Re: [HACKERS] Unlogged tables, persistent kind

The only data we can't rebuild it's the heap. So what about an option for UNlogged indexes on a LOGged table? It would always preserve data, and it would 'only' cost a rebuilding of the indexes in case of an unclean shutdown. I think it would give a boost in performance for all those cases

Re: [HACKERS] Unlogged tables, persistent kind

On Mon, Apr 25, 2011 at 8:36 AM, Leonardo Francalanci m_li...@yahoo.it wrote: The only data we can't rebuild it's the heap. So what about an option for UNlogged indexes on a LOGged table? It would always preserve data, and it would 'only' cost a rebuilding of the indexes in case of an unclean

Re: [HACKERS] Unlogged tables, persistent kind

On Mon, Apr 25, 2011 at 8:14 AM, Robert Haas robertmh...@gmail.com wrote: On Apr 24, 2011, at 1:22 PM, Simon Riggs si...@2ndquadrant.com wrote: Unlogged tables are a good new feature. Thanks. I noticed Bruce had mentioned they were the equivalent of NoSQL, which I don't really accept. Me

Re: [HACKERS] Unlogged tables, persistent kind

On Mon, Apr 25, 2011 at 5:04 AM, Simon Riggs si...@2ndquadrant.com wrote: On Mon, Apr 25, 2011 at 8:14 AM, Robert Haas robertmh...@gmail.com wrote: On Apr 24, 2011, at 1:22 PM, Simon Riggs si...@2ndquadrant.com wrote: Unlogged tables are a good new feature. Thanks. I noticed Bruce had

Re: [HACKERS] Unlogged tables, persistent kind

On Mon, Apr 25, 2011 at 3:36 AM, Leonardo Francalanci m_li...@yahoo.it wrote: The only data we can't rebuild it's the heap. So what about an option for UNlogged indexes on a LOGged table? It would always preserve data, and it would 'only' cost a rebuilding of the indexes in case of an unclean

Re: [HACKERS] Unlogged tables, persistent kind

The amount of data loss on a big table will be 1% of the data loss caused by truncating the whole table. If that 1% is random (not time/transaction related), usually you'd rather have an empty table. In other words: is a table that is not consistant with anything else in the db useful? --

Re: [HACKERS] Unlogged tables, persistent kind

On 2011-04-25 20:00, Leonardo Francalanci wrote: The amount of data loss on a big table will be 1% of the data loss caused by truncating the whole table. If that 1% is random (not time/transaction related), usually you'd rather have an empty table. In other words: is a table that is not

Re: [HACKERS] Unlogged tables, persistent kind

Jesper Krogh jes...@krogh.cc wrote: On 2011-04-25 20:00, Leonardo Francalanci wrote: The amount of data loss on a big table will be 1% of the data loss caused by truncating the whole table. If that 1% is random (not time/transaction related), usually you'd rather have an empty table. In

Re: [HACKERS] Unlogged tables, persistent kind

On Mon, Apr 25, 2011 at 2:03 PM, Jesper Krogh jes...@krogh.cc wrote: On 2011-04-25 20:00, Leonardo Francalanci wrote: The amount of data loss on a big table will be 1% of the data loss caused by truncating the whole table. If that 1% is random (not time/transaction related), usually you'd

Re: [HACKERS] Unlogged tables, persistent kind

On Mon, Apr 25, 2011 at 7:00 PM, Leonardo Francalanci m_li...@yahoo.it wrote: The amount of data loss on a big table will be 1% of the data loss caused by truncating the whole table. If that 1% is random (not time/transaction related), usually you'd rather have an empty table. Why do you

Re: [HACKERS] Unlogged tables, persistent kind

On Mon, Apr 25, 2011 at 1:42 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Apr 25, 2011 at 5:04 AM, Simon Riggs si...@2ndquadrant.com wrote: On Mon, Apr 25, 2011 at 8:14 AM, Robert Haas robertmh...@gmail.com wrote: On Apr 24, 2011, at 1:22 PM, Simon Riggs si...@2ndquadrant.com wrote:

Re: [HACKERS] Unlogged tables, persistent kind

On Mon, Apr 25, 2011 at 2:21 PM, Simon Riggs si...@2ndquadrant.com wrote: Right, but the trick is how you identify which blocks you need to zero.  You used the word damaged, which to me implied that the block had been modified in some way but ended up with other than the expected contents, so

Re: [HACKERS] Unlogged tables, persistent kind

Robert Haas robertmh...@gmail.com writes: However, I think that the logged table, unlogged index idea is probably the most promising thing to think about doing first. +1 for that --- it's clean, has a clear use-case, and would allow us to manage the current mess around hash indexes more

[HACKERS] Unlogged tables, persistent kind

Unlogged tables are a good new feature. I noticed Bruce had mentioned they were the equivalent of NoSQL, which I don't really accept. I guess it depends upon whether you mean NoSQL for caches (e.g. memcached) or NoSQL for useful datastores (e.g. Mongo). It seems worth discussin now before we get

Re: [HACKERS] Unlogged tables, persistent kind

On Sun, Apr 24, 2011 at 6:22 PM, Simon Riggs si...@2ndquadrant.com wrote: My implementation path for that would be to add a crash_number onto pg_control and pg_index. Any index marked as unlogged, persistent would only be usable if it's crash number is the same as current system crash number.

Re: [HACKERS] Unlogged tables, persistent kind

Simon Riggs si...@2ndquadrant.com writes: If you don't log changes to tables you have two choices if we crash 1) truncate the table and any indexes 2) rebuild any indexes damaged by the crash No, you have only one choice, and that's (1), because there's no guarantee that what's in the table

Re: [HACKERS] Unlogged tables, persistent kind

On Sun, Apr 24, 2011 at 10:27 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: If you don't log changes to tables you have two choices if we crash 1) truncate the table and any indexes 2) rebuild any indexes damaged by the crash No, you have only one choice,

Re: [HACKERS] Unlogged tables, persistent kind

On Sun, Apr 24, 2011 at 6:15 PM, Simon Riggs si...@2ndquadrant.com wrote: For large sets of low value data, it makes sense. Deleting all data, just simply because some of it might be damaged, is not the only option. IMHO deleting all the data is a surprising option that will cause many people

Re: [HACKERS] Unlogged tables, persistent kind

On Sun, Apr 24, 2011 at 7:41 PM, Greg Stark gsst...@mit.edu wrote: On Sun, Apr 24, 2011 at 6:22 PM, Simon Riggs si...@2ndquadrant.com wrote: My implementation path for that would be to add a crash_number onto pg_control and pg_index. Any index marked as unlogged, persistent would only be

Re: [HACKERS] Unlogged tables, persistent kind

On Sun, Apr 24, 2011 at 11:15 PM, Simon Riggs si...@2ndquadrant.com wrote: IMHO deleting all the data is a surprising option that will cause many people to curse us. I don't see preserving some of the data as being worse. What possible damage to you want to recover from? Without WAL logging

Re: [HACKERS] UNLOGGED tables in psql \d

2011/2/22 Itagaki Takahiro itagaki.takah...@gmail.com: psql \d(+) doesn't show any information about UNLOGGED and TEMP attributes for the table. So, we cannot know the table is unlogged or not unless we directly select from pg_class.relpersistence.  Is this a TODO item? The same issue is in

Re: [HACKERS] UNLOGGED tables in psql \d

On Tue, Feb 22, 2011 at 18:11, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: 2011/2/22 Itagaki Takahiro itagaki.takah...@gmail.com: psql \d(+) doesn't show any information about UNLOGGED and TEMP attributes for the table. So, we cannot know the table is unlogged or not unless we

Re: [HACKERS] UNLOGGED tables in psql \d

2011/2/22 Itagaki Takahiro itagaki.takah...@gmail.com: On Tue, Feb 22, 2011 at 18:11, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: 2011/2/22 Itagaki Takahiro itagaki.takah...@gmail.com: psql \d(+) doesn't show any information about UNLOGGED and TEMP attributes for the table. So,

Re: [HACKERS] UNLOGGED tables in psql \d

On Tue, Feb 22, 2011 at 8:03 AM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: The fact that you didn''t saw it might be enough to reconsider the way we display the unlogged state (and temp state) of a relation. Maybe some a Durability: normal, temp, unlogged  line at bottom of

[HACKERS] UNLOGGED tables in psql \d

psql \d(+) doesn't show any information about UNLOGGED and TEMP attributes for the table. So, we cannot know the table is unlogged or not unless we directly select from pg_class.relpersistence. Is this a TODO item? The same issue is in TEMP tables, but we can determine them by their schema; they

Re: [HACKERS] unlogged tables v5

On Fri, 2010-12-24 at 23:36 -0500, Robert Haas wrote: Here's an updated patch for unlogged tables, incorporating the following changes since v4: Looks good 5. Support unlogged GIN indexes. Not sure from reading the docs whether unlogged indexes are supported on logged tables? Could you

Re: [HACKERS] unlogged tables v5

On Mon, Dec 27, 2010 at 3:22 AM, Simon Riggs si...@2ndquadrant.com wrote: Not sure from reading the docs whether unlogged indexes are supported on logged tables? Could you clarify (or clarify more often)? Does this solve the hash index situation? They are not. The only place you'll see that

Re: [HACKERS] unlogged tables

Excerpts from Robert Haas's message of sáb dic 18 02:21:41 -0300 2010: Here's an attempt to summarize the remaining issues with this patch that I know about. I may have forgotten something, so please mention it if you notice something missing. 1. pg_dump needs an option to control whether

Re: [HACKERS] unlogged tables

On Mon, Dec 20, 2010 at 9:05 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Robert Haas's message of sáb dic 18 02:21:41 -0300 2010: Here's an attempt to summarize the remaining issues with this patch that I know about.  I may have forgotten something, so please mention it

Re: [HACKERS] unlogged tables

Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Robert Haas's message of sáb dic 18 02:21:41 -0300 2010: 1. pg_dump needs an option to control whether unlogged tables are dumped. --no-unlogged-tables seems like the obvious choice, assuming we want the default to be to dump

Re: [HACKERS] unlogged tables

Robert Haas wrote: If there's any third-party code out there that is checking rd_istemp, it likely also needs to be revised to check whether WAL-logging is needed, not whether the relation is temp. The way I've coded it, such code will fail to compile, and can be very easily fixed by

Re: [HACKERS] unlogged tables

On Sat, Dec 18, 2010 at 12:27 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Robert Haas  wrote: If there's any third-party code out there that is checking rd_istemp, it likely also needs to be revised to check whether WAL-logging is needed, not whether the relation is temp. The way

Re: [HACKERS] unlogged tables vs. GIST

On Tue, Dec 14, 2010 at 5:14 PM, Robert Haas robertmh...@gmail.com wrote: On Tue, Dec 14, 2010 at 4:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Dec 14, 2010 at 4:24 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Hmm, the first

Re: [HACKERS] unlogged tables vs. GIST

Given the foregoing discussion, I see only two possible paths forward here. 1. Just decide that that unlogged tables can't have GIST indexes, at least until someone figures out a way to make it work. That's sort of an annoying limitation, but I think we could live with it. +1 In the small

Re: [HACKERS] unlogged tables vs. GIST

Robert Haas robertmh...@gmail.com writes: Given the foregoing discussion, I see only two possible paths forward here. 1. Just decide that that unlogged tables can't have GIST indexes, at least until someone figures out a way to make it work. That's sort of an annoying limitation, but I think

Re: [HACKERS] unlogged tables vs. GIST

On 17.12.2010 21:07, Tom Lane wrote: IIUC, the problem is that the bufmgr might think that a GIST NSN is an LSN that should affect when to force out a dirty buffer? What if we taught it the difference? We could for example dedicate a pd_flags bit to marking pages whose pd_lsn isn't actually an

Re: [HACKERS] unlogged tables vs. GIST

Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 17.12.2010 21:07, Tom Lane wrote: IIUC, the problem is that the bufmgr might think that a GIST NSN is an LSN that should affect when to force out a dirty buffer? What if we taught it the difference? We could for example

Re: [HACKERS] unlogged tables vs. GIST

On Fri, Dec 17, 2010 at 2:07 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: IIUC, the problem is that the bufmgr might think that a GIST NSN is an LSN that should affect when to force out a dirty buffer?  What if we taught it the difference?  We could for

Re: [HACKERS] unlogged tables vs. GIST

Robert Haas robertmh...@gmail.com writes: Another possibly-useful thing about mandating a full page header for every page is that it might give us a way of avoiding unnecessary full page writes. As I wrote previously: Could we do that via a bufmgr status bit, instead? Heikki's idea has the

Re: [HACKERS] unlogged tables vs. GIST

On Fri, Dec 17, 2010 at 2:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 17.12.2010 21:07, Tom Lane wrote: IIUC, the problem is that the bufmgr might think that a GIST NSN is an LSN that should affect when to force out a dirty

Re: [HACKERS] unlogged tables vs. GIST

On Fri, Dec 17, 2010 at 2:31 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Another possibly-useful thing about mandating a full page header for every page is that it might give us a way of avoiding unnecessary full page writes.  As I wrote previously: Could

Re: [HACKERS] unlogged tables vs. GIST

On 17.12.2010 21:32, Robert Haas wrote: I guess the question is whether it's right to conflate table is unlogged with LSN is fake. It's not immediately obvious to me that those concepts are isomorphic, although though the reverse isn't obvious to me either. The buffer manager only needs to

Re: [HACKERS] unlogged tables vs. GIST

Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 17.12.2010 21:32, Robert Haas wrote: I guess the question is whether it's right to conflate table is unlogged with LSN is fake. It's not immediately obvious to me that those concepts are isomorphic, although though the reverse

Re: [HACKERS] unlogged tables vs. GIST

On Fri, Dec 17, 2010 at 3:03 PM, Tom Lane t...@sss.pgh.pa.us wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 17.12.2010 21:32, Robert Haas wrote: I guess the question is whether it's right to conflate table is unlogged with LSN is fake.  It's not immediately obvious to

Re: [HACKERS] unlogged tables vs. GIST

Robert Haas robertmh...@gmail.com writes: On Fri, Dec 17, 2010 at 3:03 PM, Tom Lane t...@sss.pgh.pa.us wrote: Yeah.  I think that BM_UNLOGGED might be a poor choice for the flag name, just because it overstates what the bufmgr needs to assume. I was actually thinking of adding BM_UNLOGGED

Re: [HACKERS] unlogged tables vs. GIST

On Fri, Dec 17, 2010 at 3:15 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Dec 17, 2010 at 3:03 PM, Tom Lane t...@sss.pgh.pa.us wrote: Yeah.  I think that BM_UNLOGGED might be a poor choice for the flag name, just because it overstates what the bufmgr

Re: [HACKERS] unlogged tables vs. GIST

Robert Haas robertmh...@gmail.com writes: Allright, what do you want to call the other bit, then? BM_SKIP_XLOG_FLUSH? Like I said, I'd be tempted to invert the sense, so that the flag is set for normal relations. Then it becomes something like BM_FLUSH_XLOG. regards,

Re: [HACKERS] unlogged tables vs. GIST

[ hit send too soon ... ] Robert Haas robertmh...@gmail.com writes: Since these bits will only be set/cleared when the buffer mapping is changed, can we examine this bit without taking the spinlock? Only if you're willing for the result to be unreliable. In the case of the xlog flush bit, I

Re: [HACKERS] unlogged tables vs. GIST

On Fri, Dec 17, 2010 at 4:17 PM, Tom Lane t...@sss.pgh.pa.us wrote: Since these bits will only be set/cleared when the buffer mapping is changed, can we examine this bit without taking the spinlock? Only if you're willing for the result to be unreliable. If we read the bits while someone else

Re: [HACKERS] unlogged tables

Here's an attempt to summarize the remaining issues with this patch that I know about. I may have forgotten something, so please mention it if you notice something missing. 1. pg_dump needs an option to control whether unlogged tables are dumped. --no-unlogged-tables seems like the obvious

  1   2   3   >