[BUGS] BUG #8036: how to disable toasting
The following bug has been logged on the website: Bug reference: 8036 Logged by: anonymous Email address: zahid.qua...@cloverinfotech.com PostgreSQL version: 9.0.5 Operating system: windows Description: how to disable toasting in postgresql 9.0 please guide -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8036: how to disable toasting
On Thu, Apr 4, 2013 at 11:12:54AM +, zahid.qua...@cloverinfotech.com wrote: > The following bug has been logged on the website: > > Bug reference: 8036 > Logged by: anonymous > Email address: zahid.qua...@cloverinfotech.com > PostgreSQL version: 9.0.5 > Operating system: windows > Description: > > how to disable toasting in postgresql 9.0 please guide See ALTER TABLE SET STORAGE. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8036: how to disable toasting
This is not a bug report; please use the pgsql-general list for questions like this, or for any follow-up on this question. "zahid.qua...@cloverinfotech.com" wrote: > PostgreSQL version: 9.0.5 You need to update to 9.0.13 as soon as possible. http://www.postgresql.org/support/versioning/ http://www.postgresql.org/support/security/faq/2013-04-04/ > how to disable toasting in postgresql 9.0 please guide Use a statement like this for each column which you want to keep in the main record (where the row still fits on the page): ALTER TABLE tablename ALTER COLUMN colname SET STORAGE MAIN; http://www.postgresql.org/docs/9.0/interactive/sql-altertable.html http://www.postgresql.org/docs/9.0/interactive/storage-toast.html -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] pg_stat_statements doesn't work with --disable-spinlocks
pg_stat_statements (re-)initializes spinlocks as part of normal
operation. With --disable-spinlock, spinlocks are implemented using
semaphores, and semaphores are expected to be created at postmaster
startup. Hence, you get an assertion failure:
postgres=# select * from pg_stat_statements ;
The connection to the server was lost. Attempting reset: Failed.
!>
TRAP: FailedAssertion("!(!IsUnderPostmaster)", File: "pg_sema.c", Line: 326)
Even if that worked, re-initializing a spinlock with SpinLockInit, like
pg_stat_statement does, would always allocate a new semaphore, so you
would run out very quickly.
- Heikki
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_stat_statements doesn't work with --disable-spinlocks
Heikki Linnakangas writes:
> pg_stat_statements (re-)initializes spinlocks as part of normal
> operation. With --disable-spinlock, spinlocks are implemented using
> semaphores, and semaphores are expected to be created at postmaster
> startup. Hence, you get an assertion failure:
> postgres=# select * from pg_stat_statements ;
> The connection to the server was lost. Attempting reset: Failed.
> !>
> TRAP: FailedAssertion("!(!IsUnderPostmaster)", File: "pg_sema.c", Line: 326)
> Even if that worked, re-initializing a spinlock with SpinLockInit, like
> pg_stat_statement does, would always allocate a new semaphore, so you
> would run out very quickly.
You'd run out even without that, since we're only leaving a dozen or two
extra semaphores available, and this code wants a spinlock per hash
entry.
I think that we can probably write this off as an unsupported
combination. If you're running without real spinlocks, any sort of
performance optimization is a complete waste of time until you fix that,
so what's the point of using pg_stat_statements?
Having said that, though, I agree that assuming that spinlocks can be
reinitialized is being overly intimate with the implementation details.
It's not too hard to imagine it failing with some future version of
spinlocks, even disregarding the rather dubious assumption that no two
processes would be touching the new entry at about the same time.
We should probably try to find a way of avoiding that.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8034: pg_buffercache gets invalid memory alloc request size with very large shared memory buffers
On 03/04/13 08:44, dben...@whitepages.com wrote: The following bug has been logged on the website: Bug reference: 8034 Logged by: Devin Ben-Hur Email address: dben...@whitepages.com PostgreSQL version: 9.2.3 Operating system: Ubuntu Precise Description: When a very large shared buffer pool (~480GB) is used with postgresql, pg_buffercache contrib module gets an allocation error trying to Allocate NBuffers worth of BufferCachePagesRec records: https://github.com/postgres/postgres/blob/REL9_2_3/contrib/pg_buffercache/pg_buffercache_pages.c#L101-L102 The requested allocation exceeds the 1GB limitation imposed by AllocSizeIsValid macro: https://github.com/postgres/postgres/blob/REL9_2_3/src/include/utils/memutils.h#L40-L43 Reproduce: 1) acquire server with half terabyte of memory 2) tweak OS settings to allow large shared memory 3) set postgresql.conf: shared_buffers = 400GB 4) CREATE EXTENSION pg_buffercache; 5) SELECT * FROM pg_buffercache LIMIT 1; Yes indeed - however I'm not sure this is likely to be encountered in any serious configuration. The general rule for sizing shared buffers is: shared_buffers = min(0.25 * RAM, 8G) Now there has been some discussion about how settings bigger than 8G make sense in some cases...but I'm not aware of any suggestions that sizes in the hundreds of G make sense. However it would be nice if pg_buffercache *could* work with bigger sizes if they make sense at any time. Someone who understands the memory allocation system better than I do will need to comment about how that might work :-) Cheers Mark -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8034: pg_buffercache gets invalid memory alloc request size with very large shared memory buffers
Mark Kirkwood writes: >> When a very large shared buffer pool (~480GB) is used with postgresql, >> pg_buffercache contrib module gets an allocation error trying to Allocate >> NBuffers worth of BufferCachePagesRec records: > Yes indeed - however I'm not sure this is likely to be encountered in > any serious configuration. I too am a bit skeptical of trying to make this actually work. For one thing, pg_buffercache would be locking down the entire buffer arena for a rather significant amount of time while it transfers gigabytes of data into the local array. What would likely make more sense, if we ever get to the point where this is a practical size of configuration, is to provide a mechanism to read out data for just a portion of the arena at a time. > However it would be nice if pg_buffercache *could* work with bigger > sizes if they make sense at any time. Someone who understands the > memory allocation system better than I do will need to comment about how > that might work :-) There has been some discussion of inventing a "big_palloc" infrastructure to allow allocation of arrays larger than 1GB, for use in places like the sort code. If we ever get around to doing that, it'd be straightforward enough to make pg_buffercache use the facility ... but I really doubt pg_buffercache itself is a sufficient reason to do it. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
