Re: [HACKERS] No, pg_size_pretty(numeric) was not such a hot idea

On Sat, May 26, 2012 at 9:30 AM, Tom Lane wrote: > In 9.1: > > regression=# select pg_size_pretty(8*1024*1024); >  pg_size_pretty > >  8192 kB > (1 row) > > In HEAD: > > regression=# select pg_size_pretty(8*1024*1024); > ERROR:  function pg_size_pretty(integer) is not unique > LIN

Re: [HACKERS] Per-Database Roles

Stephen Frost writes: > * Robert Haas (robertmh...@gmail.com) wrote: >> Eh? Why would the presence of usernames in pg_hba.conf mean that they >> have to be global objects? > I havn't had a chance (yet) to look, but perhaps the current code > attempts to validate the role before figuring out what

Re: [HACKERS] Backends stalled in 'startup' state: index corruption

On May 25, 2012, at 7:12 PM, Tom Lane wrote: > Jeff Frost writes: >> In our customer's case, the size of pg_attribute was a little less than 1/4 >> of shared_buffers, so might not be the syncscan? > > Could you go back and double check that? If the shared_buffers setting > were 7GB not 8GB,

Re: [HACKERS] Per-Database Roles

On Fri, May 25, 2012 at 10:34:54PM -0400, Stephen Frost wrote: > * Robert Haas (robertmh...@gmail.com) wrote: > > On Thu, May 24, 2012 at 6:21 PM, Bruce Momjian wrote: > > > Yes, pre-1996.  I think the fact that authentication/user names appear > > > in pg_hba.conf really locked the user name idea

Re: [HACKERS] pg_upgrade libraries check

On Fri, May 25, 2012 at 10:20:29AM -0400, Andrew Dunstan wrote: > pg_upgrade is a little over-keen about checking for shared libraries > that back functions. In particular, it checks for libraries that > support functions created in pg_catalog, even if pg_dump doesn't > export the function. > > The

Re: [HACKERS] Per-Database Roles

* Robert Haas (robertmh...@gmail.com) wrote: > On Thu, May 24, 2012 at 6:21 PM, Bruce Momjian wrote: > > Yes, pre-1996.  I think the fact that authentication/user names appear > > in pg_hba.conf really locked the user name idea into global objects, and > > we have never really been able to make a

Re: [HACKERS] Per-Database Roles

On Thu, May 24, 2012 at 6:21 PM, Bruce Momjian wrote: > On Tue, May 22, 2012 at 10:19:12AM -0400, Robert Haas wrote: >> In retrospect, I think the idea of shared catalogs was probably a bad >> idea.  I think we should have made roles and tablespaces database >> objects rather than shared objects,

Re: [HACKERS] Backends stalled in 'startup' state: index corruption

Jeff Frost writes: > In our customer's case, the size of pg_attribute was a little less than 1/4 > of shared_buffers, so might not be the syncscan? Could you go back and double check that? If the shared_buffers setting were 7GB not 8GB, that would fall right between the pg_attribute sizes you p

Re: [HACKERS] Backends stalled in 'startup' state: index corruption

On May 25, 2012, at 4:02 PM, Tom Lane wrote: > Greg Sabino Mullane writes: >>> Yeah, this is proof that what it was doing is the same as what we saw in >>> Jeff's backtrace, ie loading up the system catalog relcache entries the >>> hard way via seqscans on the core catalogs. So the question to

[HACKERS] No, pg_size_pretty(numeric) was not such a hot idea

In 9.1: regression=# select pg_size_pretty(8*1024*1024); pg_size_pretty 8192 kB (1 row) In HEAD: regression=# select pg_size_pretty(8*1024*1024); ERROR: function pg_size_pretty(integer) is not unique LINE 1: select pg_size_pretty(8*1024*1024); ^ HINT: Could n

Re: [HACKERS] heap metapages

On Fri, May 25, 2012 at 5:57 PM, Jim Nasby wrote: > It occurred to me that having a metapage with information useful to recovery > operations in *every segment* would be useful; it certainly seems worth the > extra block. It then occurred to me that we've basically been stuck with 2 > places to st

Re: [HACKERS] Backends stalled in 'startup' state: index corruption

Greg Sabino Mullane writes: >> Yeah, this is proof that what it was doing is the same as what we saw in >> Jeff's backtrace, ie loading up the system catalog relcache entries the >> hard way via seqscans on the core catalogs. So the question to be >> answered is why that's suddenly a big performa

Re: [HACKERS] 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile

On Fri, 25 May 2012, Merlin Moncure wrote: can you hack this in heapam.c and see if it helps? line 131-ish: if (!RelationUsesLocalBuffers(scan->rs_rd) && scan->rs_nblocks > NBuffers / 4) becomes if (!RelationUsesLocalBuffers(scan->rs_rd)) (also you can set the pa

Re: [HACKERS] Backends stalled in 'startup' state: index corruption

> Yeah, this is proof that what it was doing is the same as what we saw in > Jeff's backtrace, ie loading up the system catalog relcache entries the > hard way via seqscans on the core catalogs. So the question to be > answered is why that's suddenly a big performance bottleneck. It's not > a che

Re: [HACKERS] heap metapages

On 5/22/12 12:09 PM, Simon Riggs wrote: On 22 May 2012 13:52, Robert Haas wrote: It seems pretty clear to me that making pg_upgrade responsible for emptying block zero is a non-starter. But I don't think that's a reason to throw out the design; I think it's a problem we can work around. I l

Re: [HACKERS] [RFC] Interface of Row Level Security

2012/5/25 Florian Pflug : > On May24, 2012, at 19:25 , Robert Haas wrote: >> FWIW, I'm inclined to think that you should NOT be able to create a >> row that references an invisible row.  You might end up with that >> situation anyway, because we don't know what the semantics of the >> security poli

Re: [HACKERS] [RFC] Interface of Row Level Security

2012/5/24 Robert Haas : > On Thu, May 24, 2012 at 12:00 PM, Kohei KaiGai wrote: >>> Another issue, BTW, are FOREIGN KEY constraints. Should you be allowed to >>> created references to rows which are invisible to you, or should FOREIGN KEY >>> constraints be exempt from security policies? I'd say t

Re: [HACKERS] [RFC] Interface of Row Level Security

2012/5/24 Robert Haas : > On Thu, May 24, 2012 at 6:11 AM, Kohei KaiGai wrote: >>> Perhaps when we see that RLS >>> applies, we should replace the reference to the original table with a >>> subquery RTE that has the security_barrier flag set - essentially >>> treating a table with RLS as if it wer

Re: [HACKERS] patch: Use pg_mbcliplen for truncation in text-to-name conversion

Karl Schnaitter writes: > The text_name function was truncating its input string to the first > NAMEDATALEN-1 bytes, which is wrong if the string has multi-byte > characters. This patch changes it to use pg_mbcliplen, following > the namein function. Good catch, but poking around I note that bpch

Re: [HACKERS] Bug in new buffering GiST build code

On 22.05.2012 01:09, Alexander Korotkov wrote: Hi! On Tue, May 22, 2012 at 12:56 AM, Heikki Linnakangas< heikki.linnakan...@enterprisedb.com> wrote: The management of the path stacks is a bit complicated, anyway. I'll think about this some more tomorrow, maybe we can make it simpler, knowing

[HACKERS] Foreground vacuum and buffer access strategy

If I invoke vacuum manually and do so with VacuumCostDelay == 0, I have basically declared my intentions to get this pain over with as fast as possible even if it might interfere with other processes. Under that condition, shouldn't it use BAS_BULKWRITE rather than BAS_VACUUM? The smaller ring si

Re: [HACKERS] Archiver not exiting upon crash

Jeff Janes writes: > So my test harness is an inexplicably effective show-case for the > vulnerability, but it is not the reason the vulnerability should be > fixed. I spent a bit of time looking into this. In principle the postmaster could be fixed to repeat the SIGQUIT signal every second or s

Re: [HACKERS] Re: [BUGS] 9.2beta1 regression: pg_restore --data-only does not set sequence values any more

On 05/21/2012 02:59 PM, Andrew Dunstan wrote: On 05/16/2012 10:23 AM, Andrew Dunstan wrote: On Wed, May 16, 2012 at 9:08 AM, Tom Lane > wrote: Martin Pitt mailto:mp...@debian.org>> writes: > while packaging 9.2 beta 1 for Debian/Ubuntu the postgresql-common

Re: [HACKERS] 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile

On Fri, May 25, 2012 at 11:38 AM, Tom Lane wrote: > Merlin Moncure writes: >> On Fri, May 25, 2012 at 11:17 AM, Stephen Frost wrote: >>> Didn't we implement a system whereby this is exactly what we intend to >>> happen on the read side- that is, everyone doing a SeqScan gangs up on >>> one ring

Re: [HACKERS] incorrect handling of the timeout in pg_receivexlog

On Thu, May 24, 2012 at 4:52 AM, Magnus Hagander wrote: > On Wed, May 23, 2012 at 8:11 PM, Fujii Masao wrote: >> On Tue, May 22, 2012 at 11:04 PM, Robert Haas wrote: >>> On Mon, May 14, 2012 at 2:24 PM, Fujii Masao wrote: On Fri, May 11, 2012 at 11:43 PM, Magnus Hagander wrote:

Re: [HACKERS] 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile

On Fri, 25 May 2012, Robert Haas wrote: On Thu, May 24, 2012 at 7:26 PM, Sergey Koposov wrote: Here is the output from a multi-threaded run (8thtreads, 22 seconds) sorted by blk. Not sure whether that's of much use or not: What are the top dozen or so entries if you sort by shacq? Here it

Re: [HACKERS] Draft release notes complete

On 5/24/12 2:34 PM, Peter Geoghegan wrote: > On 21 May 2012 19:10, Josh Berkus wrote: >> >>> For these reasons, it may be timely and appropriate, from a purely >>> advocacy point-of-view, to call our new group commit "group commit" in >>> release notes and documentation, and announce it as a new f

Re: [HACKERS] 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile

On Thu, May 24, 2012 at 7:26 PM, Sergey Koposov wrote: > Here is the output from a multi-threaded run (8thtreads, 22 seconds) sorted > by blk. Not sure whether that's of much use or not: What are the top dozen or so entries if you sort by shacq? -- Robert Haas EnterpriseDB: http://www.enterpris

Re: [HACKERS] pg_stat_statements temporary file

> Why would they want that? PSS only writes the tempfile on shutdown and > reads it on startup. Unlike pgstats which reads and writes it all the > time. Ah, ok! Didn't know that. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hacker

Re: [HACKERS] pg_stat_statements temporary file

On Fri, May 25, 2012 at 6:49 PM, Josh Berkus wrote: > On 5/25/12 8:19 AM, Tom Lane wrote: >> Andres Freund writes: >>> On Friday, May 25, 2012 04:03:49 PM Peter Geoghegan wrote: Where do you suggest the file be written to? >> >>> One could argue stats_temp_directory would be the correct plac

Re: [HACKERS] pg_stat_statements temporary file

On 5/25/12 8:19 AM, Tom Lane wrote: > Andres Freund writes: >> On Friday, May 25, 2012 04:03:49 PM Peter Geoghegan wrote: >>> Where do you suggest the file be written to? > >> One could argue stats_temp_directory would be the correct place. > > No, that would be exactly the *wrong* place, becaus

Re: [HACKERS] 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile

On Fri, 25 May 2012, Merlin Moncure wrote: how likely is it to see multiple simultaneous scans in the real world? Interleaving scans like that is not a very effective optimization -- if it was me, it'd be trying to organize something around a partitioned tid scan for parallel sequential access.

Re: [HACKERS] 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile

Merlin Moncure writes: > On Fri, May 25, 2012 at 11:17 AM, Stephen Frost wrote: >> Didn't we implement a system whereby this is exactly what we intend to >> happen on the read side- that is, everyone doing a SeqScan gangs up on >> one ring buffer and follows it, which we felt was going to dramati

Re: [HACKERS] Interrupting long external library calls

Sandro Santilli writes: > I ended up providing an explicit mechanism to request interruption of > whatever the library is doing, and experimented (successfully so far) > requesting the interruption from a SIGINT handler. > Do you see any major drawback in doing so ? This seems a bit fragile. It

Re: [HACKERS] 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile

On Fri, May 25, 2012 at 11:17 AM, Stephen Frost wrote: > * Merlin Moncure (mmonc...@gmail.com) wrote: >> Right -- duh.  Well, hm.  Is this worth fixing?  ISTM there's a bit of >> 'optimizing for pgbench-itis' in the buffer partitions -- they seem >> optimized to lever the mostly random access beha

Re: [HACKERS] 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile

On Thu, May 24, 2012 at 4:26 PM, Sergey Koposov wrote: > On Thu, 24 May 2012, Jeff Janes wrote: >> >> Add >> #define LWLOCK_STATS >> near the top of: >> src/backend/storage/lmgr/lwlock.c >> >> and recompile and run a reduced-size workload.  When the processes >> exits, they will dump a lot of data

Re: [HACKERS] 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile

* Merlin Moncure (mmonc...@gmail.com) wrote: > Right -- duh. Well, hm. Is this worth fixing? ISTM there's a bit of > 'optimizing for pgbench-itis' in the buffer partitions -- they seem > optimized to lever the mostly random access behavior of pgbench. But > how likely is it to see multiple simu

Re: [HACKERS] 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile

On Fri, May 25, 2012 at 10:22 AM, Tom Lane wrote: > Merlin Moncure writes: >> Hm, what if BufTableHashPartition() was pseudo randomized so that >> different backends would not get the same buffer partition for a >> particular tag? > > Huh?  You have to make sure that different backends will find

Re: [HACKERS] Interrupting long external library calls

On Thu, May 24, 2012 at 04:37:04PM +0200, Florian Pflug wrote: > On May24, 2012, at 15:04 , Sandro Santilli wrote: > > On Wed, May 16, 2012 at 07:30:03PM +0300, Heikki Linnakangas wrote: > >> On 16.05.2012 15:42, Sandro Santilli wrote: > >>> But CHECK_FOR_INTERRUPTS doesn't return, right ? > >>> Is

Re: [HACKERS] 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile

Merlin Moncure writes: > Hm, what if BufTableHashPartition() was pseudo randomized so that > different backends would not get the same buffer partition for a > particular tag? Huh? You have to make sure that different backends will find the same buffer for the same page, so I don't see how that

Re: [HACKERS] pg_stat_statements temporary file

Andres Freund writes: > On Friday, May 25, 2012 04:03:49 PM Peter Geoghegan wrote: >> Where do you suggest the file be written to? > One could argue stats_temp_directory would be the correct place. No, that would be exactly the *wrong* place, because that directory can be on a RAM disk. We need

Re: [HACKERS] pg_stat_statements temporary file

Magnus Hagander writes: > On Fri, May 25, 2012 at 4:09 PM, Tom Lane wrote: >> Given that pgstats keeps its permanent file in global/, I think the >> argument that pg_stat_statements should not do likewise is pretty thin. > Fair enough. As long as the file is unlinked after reading (per my > patc

Re: [HACKERS] pg_stat_statements temporary file

On Fri, May 25, 2012 at 4:09 PM, Tom Lane wrote: > Peter Geoghegan writes: >> On 25 May 2012 14:13, Magnus Hagander wrote: >>> I still think we should consider the placement of this file to not be >>> in the global/ directory, but this is a quick (back-patchable) fix... > >> Where do you suggest

Re: [HACKERS] [PATCH] Make skipped sort explicit in EXPLAIN ANALYZE

On 25 May 2012 15:19, Marti Raudsepp wrote: > I understand where you're coming from, but personally I think the > current output is more confusing: "Gee Postgres is stupid, it's > sorting when there's nothing to sort!" > > But let's wait for a third opinion. I agree with Tom. The idea that you "s

Re: [HACKERS] 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile

On Fri, May 25, 2012 at 8:06 AM, Merlin Moncure wrote: > On Thu, May 24, 2012 at 6:26 PM, Sergey Koposov wrote: >> On Thu, 24 May 2012, Jeff Janes wrote: >>> >>> Add >>> #define LWLOCK_STATS >>> near the top of: >>> src/backend/storage/lmgr/lwlock.c >>> >>> and recompile and run a reduced-size wo

[HACKERS] pg_upgrade libraries check

pg_upgrade is a little over-keen about checking for shared libraries that back functions. In particular, it checks for libraries that support functions created in pg_catalog, even if pg_dump doesn't export the function. The attached patch mimics the filter that pg_dump uses for functions so t

Re: [HACKERS] [PATCH] Make skipped sort explicit in EXPLAIN ANALYZE

On Fri, May 25, 2012 at 4:49 PM, Tom Lane wrote: > I'm not convinced this is an improvement; it's just one more value > to confuse people with. I understand where you're coming from, but personally I think the current output is more confusing: "Gee Postgres is stupid, it's sorting when there's no

Re: [HACKERS] pg_stat_statements temporary file

Peter Geoghegan writes: > On 25 May 2012 14:13, Magnus Hagander wrote: >> I still think we should consider the placement of this file to not be >> in the global/ directory, but this is a quick (back-patchable) fix... > Where do you suggest the file be written to? Given that pgstats keeps its pe

Re: [HACKERS] pg_stat_statements temporary file

On Friday, May 25, 2012 04:03:49 PM Peter Geoghegan wrote: > > I still think we should consider the placement of this file to not be > > in the global/ directory, but this is a quick (back-patchable) fix... > > Where do you suggest the file be written to? One could argue stats_temp_directory would

Re: [HACKERS] pg_stat_statements temporary file

On 25 May 2012 14:13, Magnus Hagander wrote: > Here's a patch that does the two easy fixes: > 1) writes the file to a temp file and rename()s it over the main file > as it writes down. This removes the (small) risk of corruption because > of a crash during write > > 2) unlinks the file after readi

Re: [HACKERS] [PATCH] Make skipped sort explicit in EXPLAIN ANALYZE

Marti Raudsepp writes: > This is just a small clarity improvement. tuplesort_performsort() > skips sorting entirely when the result set has 0 or 1 tuples, but > EXPLAIN still says it's using "quicksort". The patch changes that to > "skipped" I'm not convinced this is an improvement; it's just one

Re: [HACKERS] proclock table corrupted

Harshitha S writes: > We are encoutering the following error during normal operation of postgres. > postgres[10982]: [2-1] PANIC: proclock table corrupted Ugh. Can you provide a reproducible test case? > Version of Postgres : 9.0.3 > Architecture : mips > OS: RedHat Linux [ raised eyebrow...

[HACKERS] [PATCH] Make skipped sort explicit in EXPLAIN ANALYZE

Hi, This is just a small clarity improvement. tuplesort_performsort() skips sorting entirely when the result set has 0 or 1 tuples, but EXPLAIN still says it's using "quicksort". The patch changes that to "skipped" For example: db=# explain analyze select * from now() order by 1; Sort (cost=0.

Re: [HACKERS] 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile

On Fri, 25 May 2012, Merlin Moncure wrote: These are all on the buffer partition locks. That makes sense...I was wrong earlier: this case was in fact 'create table as', not 'insert select' which rules out both the freelist lock and the wal insert lock because create table as gets to use both a

Re: [HACKERS] pg_stat_statements temporary file

On Thu, May 24, 2012 at 2:19 PM, Magnus Hagander wrote: > On Thu, May 24, 2012 at 2:16 PM, Peter Geoghegan > wrote: >> On 24 May 2012 12:42, Magnus Hagander wrote: >>> What actually happens if it tries to repalloc() something huge? palloc >>> will throw an elog(ERROR), and since this happens du

Re: [HACKERS] 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile

On Thu, May 24, 2012 at 6:26 PM, Sergey Koposov wrote: > On Thu, 24 May 2012, Jeff Janes wrote: >> >> Add >> #define LWLOCK_STATS >> near the top of: >> src/backend/storage/lmgr/lwlock.c >> >> and recompile and run a reduced-size workload.  When the processes >> exits, they will dump a lot of data

Re: [HACKERS] heap metapages

On 24 May 2012 23:02, Bruce Momjian wrote: > On Tue, May 22, 2012 at 09:52:30AM +0100, Simon Riggs wrote: >> Having pg_upgrade touch data files is both dangerous and difficult to >> back out in case of mistake, so I am wary of putting the metapage at >> block 0. Doing it the way I suggest means th

[HACKERS] proclock table corrupted

Hi, We are encoutering the following error during normal operation of postgres. postgres[10982]: [2-1] PANIC: proclock table corrupted Version of Postgres : 9.0.3 Architecture : mips OS: RedHat Linux Can you please let me know if 'fix-strong-lock-cleanup.patch' and this error are related? Reg

Re: [HACKERS] pg_basebackup -x stream from the standby gets stuck

On Thu, May 24, 2012 at 7:02 PM, Fujii Masao wrote: > On Wed, May 23, 2012 at 9:25 PM, Magnus Hagander wrote: >> While reviewing and cleaning this patch up a bit I noticed it actually >> broke pg_receivexlog in the renaming. >> >> Here is a new version of the patch, reworked based on the above so

Re: [HACKERS] libpq URL syntax vs SQLAlchemy

Alex Shulgin writes: > > Upon closer inspection of the issue I came to believe that the proper > fix is to drop support for special treatment of "host part" starting > with slash altogether. > > Attached is a patch to do that. Well, I understand I might be asking for too much, but did anyone had

Re: [HACKERS] pg_stat_statments queryid

On Thu, May 24, 2012 at 5:20 PM, Peter Geoghegan wrote: > On 24 May 2012 16:06, Tom Lane wrote: >> I do not want to promise that it's stable over any timeframe longer than >> a server reboot. > > You already have though, since pg_stat_statements persistently stores > statistics to disk by default

[HACKERS] proposal: features for simpler integration psql to bash

Hello we don't have a macro language in psql. I did some work few years ago - see enhanced psql - http://postgres.cz/wiki/Enhanced-psql but important part of this experimental patch was not accepted (implementation was not in production quality code and was not finalized). Implementation of own