Re: [HACKERS] Changing the concept of a DATABASE

2012-05-24 Thread Susanne Ebrecht
Am 22.05.2012 15:27, schrieb Albe Laurenz: If you need different applications to routinely access each other's tables, why not assign them to different schemas in one database? I just saw another use case here. There are lots of offices / departments creating maps. Topography maps, pipeline

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

2012-05-24 Thread Kohei KaiGai
2012/5/23 Robert Haas robertmh...@gmail.com: On Wed, May 23, 2012 at 3:45 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: I wanted to have discussion to handle this problem. Unlike leaky-view problem, we don't need to worry about unexpected qualifier distribution on either side of join, because a

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

2012-05-24 Thread Florian Pflug
On May23, 2012, at 22:12 , Robert Haas wrote: Also, suppose that Bob applies an RLS policy to a table, and, later, Alice selects from the table. How do we keep Bob from usurping Alice's privileges? That problem isn't restricted to RLW, though. Bob could just as well have booby-trapped any

Re: [HACKERS] Changing the concept of a DATABASE

2012-05-24 Thread Florian Pflug
On May24, 2012, at 11:39 , Susanne Ebrecht wrote: There are lots of offices / departments creating maps. Topography maps, pipeline maps, nature conservancy (e.g. where are the nests from endangered birds?), mineral resources, wire maps, street maps, bicycle / jogging maps, tourists maps, tree

[HACKERS] pg_stat_statements temporary file

2012-05-24 Thread Magnus Hagander
If pg_stat_statements is set to store it's data across restarts, it stores it in global/pg_stat_statements.stat. This causes some interesting things to happen in combination with a base backup - namely, if you take a base backup *after* you have restarted th emaster, the slave will get a snapshot

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

2012-05-24 Thread Kohei KaiGai
2012/5/24 Florian Pflug f...@phlo.org: If we also apply the security policy to newer version of tuples on update and insert, one idea is to inject a before-row-(update|insert) trigger to check whether it satisfies the security policy. For same reason, the trigger should be executed at the end

[HACKERS] pg_stat_statments queryid

2012-05-24 Thread Magnus Hagander
Was there any actual reason why we didn't end up exposing queryid in the pg_stat_statements view? It would be highly useful when tracking changes over time. Right now I see people doing md5(query) to do that, which is a lot more ugly (and obviously uses more space and is slow, too). --  Magnus

Re: [HACKERS] LISTEN/NOTIFY Security and the docs

2012-05-24 Thread Magnus Hagander
On Fri, May 18, 2012 at 5:08 PM, Chander Ganesan chan...@otg-nc.com wrote: Hi All, I just realized that anyone can listen for notifications (using listen) so long as they know the channel name.  This means that a user could receive and view the payload for another user. Perhaps it would be

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

2012-05-24 Thread Florian Pflug
On May24, 2012, at 12:43 , Kohei KaiGai wrote: The case of INSERT / DELETE are simple; All we need to apply is checks on either new or old tuples. In case of UPDATE, we need to check on the old tuple whether use can see, and on the new tuple whether use can store them. Indeed, these are

Re: [HACKERS] pg_stat_statements temporary file

2012-05-24 Thread Peter Geoghegan
On 24 May 2012 11:43, Magnus Hagander mag...@hagander.net wrote: In general, should a contrib module really store data in the global/ directory? Seems pretty ugly to me... I think the case could be made for moving pg_stat_statements into core, as an optionally enabled view, like

Re: [HACKERS] pg_stat_statements temporary file

2012-05-24 Thread Magnus Hagander
On Thu, May 24, 2012 at 1:36 PM, Peter Geoghegan pe...@2ndquadrant.com wrote: On 24 May 2012 11:43, Magnus Hagander mag...@hagander.net wrote: In general, should a contrib module really store data in the global/ directory? Seems pretty ugly to me... I think the case could be made for moving

Re: [HACKERS] pg_receivexlog stops upon server restart

2012-05-24 Thread Magnus Hagander
On Thu, Apr 19, 2012 at 1:00 PM, Thom Brown t...@linux.com wrote: On 10 April 2012 21:07, Magnus Hagander mag...@hagander.net wrote: On Friday, April 6, 2012, Thom Brown wrote: Hi, I've tried out pg_receivexlog and have noticed that when restarting the cluster, pg_receivexlog gets cut

[HACKERS] shared_preload_libraries path

2012-05-24 Thread Magnus Hagander
Would i make sense to have a postgresql.conf parameter that would add to LD_LIBRARY_PATH when loading libraries from shared_preload_libraries (and other library loads). To make it possible to configure it without having to mess around with the operating system configuration? Or is that too much

Re: [HACKERS] pg_stat_statements temporary file

2012-05-24 Thread Peter Geoghegan
On 24 May 2012 12:42, Magnus Hagander mag...@hagander.net wrote: What actually happens if it tries to repalloc() something huge? palloc will throw an elog(ERROR), and since this happens during postmaster startup, are you sure it won't prevent the server from starting? Oh, yes, missed that.

Re: [HACKERS] pg_stat_statements temporary file

2012-05-24 Thread Magnus Hagander
On Thu, May 24, 2012 at 2:16 PM, Peter Geoghegan pe...@2ndquadrant.com wrote: On 24 May 2012 12:42, Magnus Hagander mag...@hagander.net wrote: What actually happens if it tries to repalloc() something huge? palloc will throw an elog(ERROR), and since this happens during postmaster startup, are

Re: [HACKERS] shared_preload_libraries path

2012-05-24 Thread Peter Geoghegan
On 24 May 2012 13:09, Magnus Hagander mag...@hagander.net wrote: Would i make sense to have a postgresql.conf parameter that would add to LD_LIBRARY_PATH when loading libraries from shared_preload_libraries (and other library loads). To make it possible to configure it without having to  mess

Re: [HACKERS] shared_preload_libraries path

2012-05-24 Thread Peter Geoghegan
On 24 May 2012 13:22, Peter Geoghegan pe...@2ndquadrant.com wrote: I found this blog post to be insightful: https://blogs.oracle.com/rie/entry/tt_ld_library_path_tt This one might be more useful, and itself refers to the aforementioned, earlier post:

Re: [HACKERS] pg_receivexlog stops upon server restart

2012-05-24 Thread Thom Brown
On 24 May 2012 13:05, Magnus Hagander mag...@hagander.net wrote: On Thu, Apr 19, 2012 at 1:00 PM, Thom Brown t...@linux.com wrote: On 10 April 2012 21:07, Magnus Hagander mag...@hagander.net wrote: On Friday, April 6, 2012, Thom Brown wrote: Hi, I've tried out pg_receivexlog and have

Re: [HACKERS] pg_receivexlog stops upon server restart

2012-05-24 Thread Magnus Hagander
On Thu, May 24, 2012 at 2:34 PM, Thom Brown t...@linux.com wrote: On 24 May 2012 13:05, Magnus Hagander mag...@hagander.net wrote: On Thu, Apr 19, 2012 at 1:00 PM, Thom Brown t...@linux.com wrote: On 10 April 2012 21:07, Magnus Hagander mag...@hagander.net wrote: On Friday, April 6, 2012, Thom

Re: [HACKERS] pg_receivexlog stops upon server restart

2012-05-24 Thread Thom Brown
On 24 May 2012 13:37, Magnus Hagander mag...@hagander.net wrote: On Thu, May 24, 2012 at 2:34 PM, Thom Brown t...@linux.com wrote: On 24 May 2012 13:05, Magnus Hagander mag...@hagander.net wrote: On Thu, Apr 19, 2012 at 1:00 PM, Thom Brown t...@linux.com wrote: On 10 April 2012 21:07, Magnus

Re: [HACKERS] Interrupting long external library calls

2012-05-24 Thread Sandro Santilli
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 there another macro for just checking w/out yet acting upon it ? Hmm, no. CHECK_FOR_INTERRUPTS() checks the InterruptPending

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

2012-05-24 Thread Sergey Koposov
Hi, I've been running some tests on pg 9.2beta1 and in particular a set of queries like create table _tmp0 as select * from ( select *, (select healpixid from idt_match as m where m.transitid=o.transitid) as x from

Re: [HACKERS] Interrupting long external library calls

2012-05-24 Thread Heikki Linnakangas
On 24.05.2012 16: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 there another macro for just checking w/out yet acting upon it ? Hmm, no.

Re: [HACKERS] could not open relation with OID errors after promoting the standby to master

2012-05-24 Thread Joachim Wieland
On Tue, May 22, 2012 at 9:50 AM, Robert Haas robertmh...@gmail.com wrote: Hmm.  I think that if you do it this way, the minimum recovery point won't be respected, which could leave you with a corrupted database. Now, if all the WAL files that you need are present in pg_xlog anyway, then they

Re: [HACKERS] Interrupting long external library calls

2012-05-24 Thread Sandro Santilli
On Thu, May 24, 2012 at 04:55:34PM +0300, Heikki Linnakangas wrote: On 24.05.2012 16: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 there another

[HACKERS] [9.2] crash on regex

2012-05-24 Thread Marko Kreen
Following query crashes backend on 9.2: select substring('asd TO foo' from ' TO (([a-z0-9._]+|([^]+|)+)+)'); It is supposed to load potentially quoted table name from CREATE RULE definition. Works fine on 8.3 .. 9.1 Backtrace: Program terminated with signal 11, Segmentation fault. #0

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

2012-05-24 Thread Kohei KaiGai
2012/5/24 Florian Pflug f...@phlo.org: On May24, 2012, at 12:43 , Kohei KaiGai wrote: The case of INSERT / DELETE are simple; All we need to apply is checks on either new or old tuples. In case of UPDATE, we need to check on the old tuple whether use can see, and on the new tuple whether use

Re: [HACKERS] pg_stat_statments queryid

2012-05-24 Thread Peter Geoghegan
On 24 May 2012 11:50, Magnus Hagander mag...@hagander.net wrote: Was there any actual reason why we didn't end up exposing queryid in the pg_stat_statements view? It would be highly useful when tracking changes over time. Right now I see people doing md5(query) to do that, which is a lot more

Re: [HACKERS] shared_preload_libraries path

2012-05-24 Thread Tom Lane
Peter Geoghegan pe...@2ndquadrant.com writes: On 24 May 2012 13:09, Magnus Hagander mag...@hagander.net wrote: Would i make sense to have a postgresql.conf parameter that would add to LD_LIBRARY_PATH when loading libraries from shared_preload_libraries (and other library loads). To make it

Re: [HACKERS] Interrupting long external library calls

2012-05-24 Thread Florian Pflug
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 there another macro for just checking w/out yet acting upon it ? Hmm, no.

Re: [HACKERS] pg_stat_statments queryid

2012-05-24 Thread Magnus Hagander
On Thu, May 24, 2012 at 4:26 PM, Peter Geoghegan pe...@2ndquadrant.com wrote: On 24 May 2012 11:50, Magnus Hagander mag...@hagander.net wrote: Was there any actual reason why we didn't end up exposing queryid in the pg_stat_statements view? It would be highly useful when tracking changes over

Re: [HACKERS] pg_stat_statments queryid

2012-05-24 Thread Robert Haas
On Thu, May 24, 2012 at 10:26 AM, Peter Geoghegan pe...@2ndquadrant.com wrote: On 24 May 2012 11:50, Magnus Hagander mag...@hagander.net wrote: Was there any actual reason why we didn't end up exposing queryid in the pg_stat_statements view? It would be highly useful when tracking changes

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

2012-05-24 Thread Florian Pflug
On May24, 2012, at 16:19 , Kohei KaiGai wrote: So, the proposed interface might be revised as follows: ALTER TABLE tblname ADD SECURITY POLICY func_name(args, ...) [FOR SELECT | INSERT |

Re: [HACKERS] pg_stat_statments queryid

2012-05-24 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Thu, May 24, 2012 at 10:26 AM, Peter Geoghegan pe...@2ndquadrant.com wrote: But I think this explanation is enough to convince me that it might be worthwhile: What I'd like to be able to do is aggregate this information over time and/or across

Re: [HACKERS] [9.2] crash on regex

2012-05-24 Thread Robert Haas
On Thu, May 24, 2012 at 10:16 AM, Marko Kreen mark...@gmail.com wrote: Following query crashes backend on 9.2:  select substring('asd TO foo' from ' TO (([a-z0-9._]+|([^]+|)+)+)'); I spent some time trying to reduce this to the simplest case that still causes a crash, and came up with this:

Re: [HACKERS] could not open relation with OID errors after promoting the standby to master

2012-05-24 Thread Robert Haas
On Thu, May 24, 2012 at 10:13 AM, Joachim Wieland j...@mcknight.de wrote: On Tue, May 22, 2012 at 9:50 AM, Robert Haas robertmh...@gmail.com wrote: Hmm.  I think that if you do it this way, the minimum recovery point won't be respected, which could leave you with a corrupted database. Now, if

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

2012-05-24 Thread Merlin Moncure
On Thu, May 24, 2012 at 8:24 AM, Sergey Koposov kopo...@ast.cam.ac.uk wrote: Hi, I've been running some tests on pg 9.2beta1 and in particular a set of queries like create table _tmp0 as select * from (        select *, (select healpixid from idt_match as m where                            

Re: [HACKERS] pg_stat_statments queryid

2012-05-24 Thread Peter Geoghegan
On 24 May 2012 16:06, Tom Lane t...@sss.pgh.pa.us 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, and can only ever recognise statement

Re: [HACKERS] Creating multiple indexes in one table scan.

2012-05-24 Thread Stephen Frost
* Gurjeet Singh (singh.gurj...@gmail.com) wrote: Bruce points out the even simpler case is to build several indexes in parallel over the same scan. I thought I had posted a patch to that effect long back, but upon searching my emails apparently I forgot about the patch. Attached is

Re: [HACKERS] [9.2] crash on regex

2012-05-24 Thread Thom Brown
On 24 May 2012 16:08, Robert Haas robertmh...@gmail.com wrote: On Thu, May 24, 2012 at 10:16 AM, Marko Kreen mark...@gmail.com wrote: Following query crashes backend on 9.2:  select substring('asd TO foo' from ' TO (([a-z0-9._]+|([^]+|)+)+)'); I spent some time trying to reduce this to the

Re: [HACKERS] Creating multiple indexes in one table scan.

2012-05-24 Thread Gurjeet Singh
On Thu, May 24, 2012 at 11:22 AM, Stephen Frost sfr...@snowman.net wrote: * Gurjeet Singh (singh.gurj...@gmail.com) wrote: Bruce points out the even simpler case is to build several indexes in parallel over the same scan. I thought I had posted a patch to that effect long back, but

Re: [HACKERS] [9.2] crash on regex

2012-05-24 Thread Thom Brown
On 24 May 2012 16:24, Thom Brown t...@linux.com wrote: On 24 May 2012 16:08, Robert Haas robertmh...@gmail.com wrote: On Thu, May 24, 2012 at 10:16 AM, Marko Kreen mark...@gmail.com wrote: Following query crashes backend on 9.2:  select substring('asd TO foo' from ' TO

Re: [HACKERS] pg_stat_statments queryid

2012-05-24 Thread Peter Geoghegan
On 24 May 2012 16:06, Tom Lane t...@sss.pgh.pa.us wrote: What I'd like to be able to do is aggregate this information over time and/or across standbys in a cluster, as queries are evicted and subsequently re-entered into pg_stat_statement's shared hash table. It appears to me that the above

Re: [HACKERS] Creating multiple indexes in one table scan.

2012-05-24 Thread Robert Haas
On Thu, May 24, 2012 at 11:25 AM, Gurjeet Singh singh.gurj...@gmail.com wrote: It'd be great if one of standard utilities like pg_restore supported this, by spawning every concurrent index build in separate backends. Just a thought. If parallel restore doesn't already take this into account

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

2012-05-24 Thread Kohei KaiGai
2012/5/24 Florian Pflug f...@phlo.org: On May24, 2012, at 16:19 , Kohei KaiGai wrote: So, the proposed interface might be revised as follows:  ALTER TABLE tblname ADD SECURITY POLICY      func_name(args, ...) [FOR SELECT |                                                         INSERT |      

Re: [HACKERS] [9.2] crash on regex

2012-05-24 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Thu, May 24, 2012 at 10:16 AM, Marko Kreen mark...@gmail.com wrote: Following query crashes backend on 9.2:  select substring('asd TO foo' from ' TO (([a-z0-9._]+|([^]+|)+)+)'); I spent some time trying to reduce this to the simplest case that

Re: [HACKERS] could not open relation with OID errors after promoting the standby to master

2012-05-24 Thread Heikki Linnakangas
On 24.05.2012 18:16, Robert Haas wrote: On Thu, May 24, 2012 at 10:13 AM, Joachim Wielandj...@mcknight.de wrote: I wouldn't have assumed any corruption was possible given that I did clean shutdowns on both sides... The thing that's worrying me is that there's not really any such thing as a

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

2012-05-24 Thread Kohei KaiGai
I'd like to summarize the current design being discussed. syntax: ALTER TABLE tblname WITH ROW LEVEL SECURITY ( condition clause ) [FOR (SELECT | UPDATE | DELETE)]; ALTER TABLE tblname WITHOUT ROW LEVEL SECURITY; I tried to patch the parser/gram.y, but here was syntax conflicts on ADD

Re: [HACKERS] could not open relation with OID errors after promoting the standby to master

2012-05-24 Thread Andres Freund
On Thursday, May 24, 2012 06:35:06 PM Heikki Linnakangas wrote: On 24.05.2012 18:16, Robert Haas wrote: On Thu, May 24, 2012 at 10:13 AM, Joachim Wielandj...@mcknight.de wrote: I wouldn't have assumed any corruption was possible given that I did clean shutdowns on both sides... The

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

2012-05-24 Thread Fujii Masao
On Wed, May 23, 2012 at 9:25 PM, Magnus Hagander mag...@hagander.net 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 we're down to a single callback. I moved

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

2012-05-24 Thread Robert Haas
On Thu, May 24, 2012 at 6:11 AM, Kohei KaiGai kai...@kaigai.gr.jp 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 were a security view.

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

2012-05-24 Thread Robert Haas
On Thu, May 24, 2012 at 6:20 AM, Florian Pflug f...@phlo.org wrote: But the security policy should still apply to the old rows, i.e. you shouldn't be after to UPDATE or DELETE rows you cannot see, no? Agreed. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL

[HACKERS] plperl_helpers.h fix for clang

2012-05-24 Thread Peter Eisentraut
clang warns about that newish SvREFCNT_inc(sv) call in plperl_helpers.h about an unused return value, because the macro expansion of SvREFCNT_inc(sv) returns sv. The merit of that warning might be debatable, but it seems easy to fix by using SvREFCNT_inc_void(sv) instead. And we could use

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

2012-05-24 Thread Robert Haas
On Thu, May 24, 2012 at 12:00 PM, Kohei KaiGai kai...@kaigai.gr.jp 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 they shouldn't

Re: [HACKERS] plperl_helpers.h fix for clang

2012-05-24 Thread Alex Hunsaker
On Thu, May 24, 2012 at 11:19 AM, Peter Eisentraut pete...@gmx.net wrote: clang warns about that newish SvREFCNT_inc(sv) call in plperl_helpers.h about an unused return value, because the macro expansion of SvREFCNT_inc(sv) returns sv.  The merit of that warning might be debatable, but it

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

2012-05-24 Thread Florian Pflug
On May24, 2012, at 18:42 , Kohei KaiGai wrote: I'd like to summarize the current design being discussed. syntax: ALTER TABLE tblname WITH ROW LEVEL SECURITY ( condition clause ) [FOR (SELECT | UPDATE | DELETE)]; ALTER TABLE tblname WITHOUT ROW LEVEL SECURITY; I tried to patch the

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

2012-05-24 Thread Greg Sabino Mullane
Yesterday I had a client that experienced a sudden high load on one of their servers (8.3.5 - yes, I know. Those of you with clients will understand). When I checked, almost all connections were in a startup state, very similar to this thread:

Re: [HACKERS] plperl_helpers.h fix for clang

2012-05-24 Thread Alex Hunsaker
On Thu, May 24, 2012 at 11:31 AM, Alex Hunsaker bada...@gmail.com wrote: On Thu, May 24, 2012 at 11:19 AM, Peter Eisentraut pete...@gmx.net wrote: And we could use SvREFCNT_inc_simple_void(sv), since sv doesn't have any side effects, but that's optional. Hrm I can't seem to find either of

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

2012-05-24 Thread Sergey Koposov
On Thu, 24 May 2012, Merlin Moncure wrote: Are you sure? I looked at all the ReleasePredicateLocks calls and they appear to be guarded by: /* Nothing to do if this is not a serializable transaction */ if (MySerializableXact == InvalidSerializableXact) return

Re: [HACKERS] [9.2] crash on regex

2012-05-24 Thread Tom Lane
Thom Brown t...@linux.com writes: Hmmm... curiously, lazy (non-greedy) quantifiers are stable, such as: ((a))*? I've found it. The triggering conditions are (1) more than one set of capturing parens in a substring() pattern, and (2) at least one trial midpoint failing in ccondissect() or one of

Re: [HACKERS] shared_preload_libraries path

2012-05-24 Thread Peter Eisentraut
On tor, 2012-05-24 at 14:09 +0200, Magnus Hagander wrote: Would i make sense to have a postgresql.conf parameter that would add to LD_LIBRARY_PATH when loading libraries from shared_preload_libraries (and other library loads). Well, you could write a library that sets it in its init function,

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

2012-05-24 Thread Jeff Janes
On Thu, May 24, 2012 at 6:24 AM, Sergey Koposov kopo...@ast.cam.ac.uk wrote: Hi, I've been running some tests on pg 9.2beta1 and in particular a set of queries like ... And I noticed than when I run the query like the one shown above in parallel (in multiple connections for ZZZ=0...8) the

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

2012-05-24 Thread Robert Haas
On Thu, May 24, 2012 at 1:42 PM, Sergey Koposov kopo...@ast.cam.ac.uk wrote: I guess there is nothing catastrophically wrong with that, but still I'm very suprised that you get severe locking problems (factor of two slow-down) when running parallel read-only transactions. Me, too. How many

Re: [HACKERS] shared_preload_libraries path

2012-05-24 Thread Magnus Hagander
On Thu, May 24, 2012 at 7:58 PM, Peter Eisentraut pete...@gmx.net wrote: On tor, 2012-05-24 at 14:09 +0200, Magnus Hagander wrote: Would i make sense to have a postgresql.conf parameter that would add to LD_LIBRARY_PATH when loading libraries from shared_preload_libraries (and other library

Re: [HACKERS] plperl_helpers.h fix for clang

2012-05-24 Thread Peter Eisentraut
On tor, 2012-05-24 at 11:36 -0600, Alex Hunsaker wrote: On Thu, May 24, 2012 at 11:31 AM, Alex Hunsaker bada...@gmail.com wrote: On Thu, May 24, 2012 at 11:19 AM, Peter Eisentraut pete...@gmx.net wrote: And we could use SvREFCNT_inc_simple_void(sv), since sv doesn't have any side effects,

Re: [HACKERS] Creating multiple indexes in one table scan.

2012-05-24 Thread Andrew Dunstan
On 05/24/2012 11:44 AM, Robert Haas wrote: On Thu, May 24, 2012 at 11:25 AM, Gurjeet Singhsingh.gurj...@gmail.com wrote: It'd be great if one of standard utilities like pg_restore supported this, by spawning every concurrent index build in separate backends. Just a thought. If parallel

Re: [HACKERS] Changing the concept of a DATABASE

2012-05-24 Thread Josh Berkus
Yes, we do. It would be best to conclude that things I do on hackers relate in some way to those goals, even if it isn't immediately clear how. See, now you've got me all curious. How does inter-DB queries relate to the New Replication? -- Josh Berkus PostgreSQL Experts Inc.

[HACKERS] Stateful pointers in set-returning functions

2012-05-24 Thread Ian Pye
Hi, I'm writing a set-returning function which places a file handle into PG's FuncCallContext's user_fctx space. My problem is that when the function is ran with a limit clause (SELECT * FROM foo() LIMIT 10) the server will stop calling the function automatically, not giving me a chance to close

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

2012-05-24 Thread Sergey Koposov
On Thu, 24 May 2012, Robert Haas wrote: On Thu, May 24, 2012 at 1:42 PM, Sergey Koposov kopo...@ast.cam.ac.uk wrote: I guess there is nothing catastrophically wrong with that, but still I'm very suprised that you get severe locking problems (factor of two slow-down) when running parallel

Re: [HACKERS] Stateful pointers in set-returning functions

2012-05-24 Thread Tom Lane
Ian Pye ian...@gmail.com writes: I'm writing a set-returning function which places a file handle into PG's FuncCallContext's user_fctx space. My problem is that when the function is ran with a limit clause (SELECT * FROM foo() LIMIT 10) the server will stop calling the function automatically,

Re: [HACKERS] Changing the concept of a DATABASE

2012-05-24 Thread Andres Freund
On Thursday, May 24, 2012 08:12:56 PM Josh Berkus wrote: Yes, we do. It would be best to conclude that things I do on hackers relate in some way to those goals, even if it isn't immediately clear how. See, now you've got me all curious. How does inter-DB queries relate to the New

Re: [HACKERS] Stateful pointers in set-returning functions

2012-05-24 Thread Ian Pye
Fair enough -- thanks for the tip. On Thu, May 24, 2012 at 11:21 AM, Tom Lane t...@sss.pgh.pa.us wrote: Ian Pye ian...@gmail.com writes: I'm writing a set-returning function which places a file handle into PG's FuncCallContext's user_fctx space. My problem is that when the function is ran

Re: [HACKERS] could not open relation with OID errors after promoting the standby to master

2012-05-24 Thread Tom Lane
Andres Freund and...@anarazel.de writes: The control file currently is not a very good match because of the current requirement of staying below 512 bytes. If we would include the list of running xacts that wouldn't be enough. I wondered before if there is more to do to fix that then to do

Re: [HACKERS] could not open relation with OID errors after promoting the standby to master

2012-05-24 Thread Andres Freund
On Thursday, May 24, 2012 08:32:47 PM Tom Lane wrote: Andres Freund and...@anarazel.de writes: The control file currently is not a very good match because of the current requirement of staying below 512 bytes. If we would include the list of running xacts that wouldn't be enough. I

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

2012-05-24 Thread Robert Haas
On Thu, May 24, 2012 at 2:19 PM, Sergey Koposov kopo...@ast.cam.ac.uk wrote: On Thu, 24 May 2012, Robert Haas wrote: On Thu, May 24, 2012 at 1:42 PM, Sergey Koposov kopo...@ast.cam.ac.uk wrote: I guess there is nothing catastrophically wrong with that, but still I'm very suprised that you

Re: [HACKERS] Re: [COMMITTERS] pgsql: Send new protocol keepalive messages to standby servers.

2012-05-24 Thread Robert Haas
On Wed, May 23, 2012 at 2:28 PM, Fujii Masao masao.fu...@gmail.com wrote: On Sat, Dec 31, 2011 at 10:34 PM, Simon Riggs si...@2ndquadrant.com wrote: Send new protocol keepalive messages to standby servers. Allows streaming replication users to calculate transfer latency and apply delay via

Re: [HACKERS] could not open relation with OID errors after promoting the standby to master

2012-05-24 Thread Tom Lane
Andres Freund and...@anarazel.de writes: On Thursday, May 24, 2012 08:32:47 PM Tom Lane wrote: I'm not sure I believe that we can make a recovery resume from an arbitrary point in WAL anyway, or that it would be worth the trouble. Can't we just resume from the last restartpoint? Well, with a

Re: [HACKERS] Re: [COMMITTERS] pgsql: Send new protocol keepalive messages to standby servers.

2012-05-24 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Wed, May 23, 2012 at 2:28 PM, Fujii Masao masao.fu...@gmail.com wrote: Is there plan to implement such external functions before 9.2 release? If not, keepalive protocol seems to be almost useless because there is no use of it for a user and the

Re: [HACKERS] could not open relation with OID errors after promoting the standby to master

2012-05-24 Thread Andres Freund
On Thursday, May 24, 2012 08:46:21 PM Tom Lane wrote: Andres Freund and...@anarazel.de writes: On Thursday, May 24, 2012 08:32:47 PM Tom Lane wrote: I'm not sure I believe that we can make a recovery resume from an arbitrary point in WAL anyway, or that it would be worth the trouble.

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

2012-05-24 Thread Merlin Moncure
On Thu, May 24, 2012 at 1:43 PM, Robert Haas robertmh...@gmail.com wrote: On Thu, May 24, 2012 at 2:19 PM, Sergey Koposov kopo...@ast.cam.ac.uk wrote: On Thu, 24 May 2012, Robert Haas wrote: On Thu, May 24, 2012 at 1:42 PM, Sergey Koposov kopo...@ast.cam.ac.uk wrote: I guess there is nothing

Re: [HACKERS] Archiver not exiting upon crash

2012-05-24 Thread Jeff Janes
On Wed, May 23, 2012 at 2:21 PM, Tom Lane t...@sss.pgh.pa.us wrote: I wrote: Jeff Janes jeff.ja...@gmail.com writes: But what happens if the SIGQUIT is blocked before the system(3) is invoked?  Does the ignore take precedence over the block, or does the block take precedence over the ignore,

Re: [HACKERS] Archiver not exiting upon crash

2012-05-24 Thread Tom Lane
Jeff Janes jeff.ja...@gmail.com writes: On Wed, May 23, 2012 at 2:21 PM, Tom Lane t...@sss.pgh.pa.us wrote: However, I remain unsatisfied with this idea as an explanation for the behavior you're seeing. In the first place, that race condition window ought not be wide enough to allow failure

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

2012-05-24 Thread Merlin Moncure
On Thu, May 24, 2012 at 2:24 PM, Merlin Moncure mmonc...@gmail.com wrote: As you can see, raw performance isn't much worse with the larger data sets, but scalability at high connection counts is severely degraded once the working set no longer fits in shared_buffers. Hm, wouldn't the

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

2012-05-24 Thread Sergey Koposov
On Thu, 24 May 2012, Robert Haas wrote: As you can see, raw performance isn't much worse with the larger data sets, but scalability at high connection counts is severely degraded once the working set no longer fits in shared_buffers. Actually the problem persits even when I trim the dataset

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

2012-05-24 Thread Tom Lane
Greg Sabino Mullane g...@endpoint.com writes: Yesterday I had a client that experienced a sudden high load on one of their servers (8.3.5 - yes, I know. Those of you with clients will understand). When I checked, almost all connections were in a startup state, very similar to this thread:

Re: [HACKERS] Missing optimization when filters are applied after window functions

2012-05-24 Thread Bruce Momjian
On Wed, May 16, 2012 at 09:25:13AM -0400, Tom Lane wrote: Volker Grabsch v...@notjusthosting.com writes: I propose the following general optimization: If all window functions are partitioned by the same first field (here: id), then any filter on that field should be executed before

Re: [HACKERS] Re: [COMMITTERS] pgsql: Send new protocol keepalive messages to standby servers.

2012-05-24 Thread Robert Haas
On Thu, May 24, 2012 at 2:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Wed, May 23, 2012 at 2:28 PM, Fujii Masao masao.fu...@gmail.com wrote: Is there plan to implement such external functions before 9.2 release? If not, keepalive protocol seems to be

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

2012-05-24 Thread Jeff Janes
On Thu, May 24, 2012 at 12:46 PM, Merlin Moncure mmonc...@gmail.com wrote: On Thu, May 24, 2012 at 2:24 PM, Merlin Moncure mmonc...@gmail.com wrote: As you can see, raw performance isn't much worse with the larger data sets, but scalability at high connection counts is severely degraded once

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

2012-05-24 Thread Robert Haas
On Thu, May 24, 2012 at 3:46 PM, Merlin Moncure mmonc...@gmail.com wrote: On Thu, May 24, 2012 at 2:24 PM, Merlin Moncure mmonc...@gmail.com wrote: As you can see, raw performance isn't much worse with the larger data sets, but scalability at high connection counts is severely degraded once

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

2012-05-24 Thread Merlin Moncure
On Thu, May 24, 2012 at 3:35 PM, Robert Haas robertmh...@gmail.com wrote: On Thu, May 24, 2012 at 3:46 PM, Merlin Moncure mmonc...@gmail.com wrote: hm, looking at the code some more, it looks like the whole point of the strategy system is to do that.  ISTM bulk insert type queries would be

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

2012-05-24 Thread Greg Sabino Mullane
On Thu, May 24, 2012 at 03:54:54PM -0400, Tom Lane wrote: Did you check I/O activity? I looked again at Jeff Frost's report and now think that what he saw was probably a lot of seqscans on bloated system catalogs, cf http://archives.postgresql.org/message-id/28484.1337887...@sss.pgh.pa.us

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

2012-05-24 Thread Greg Sabino Mullane
I think there are probably two independent issues here. The missing index entries are clearly bad but it's not clear that they had anything to do with the startup stall. On further log digging, I think you are correct, as those index warnings go back many days before the startup problems

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

2012-05-24 Thread Robert Haas
On Thu, May 24, 2012 at 4:46 PM, Merlin Moncure mmonc...@gmail.com wrote: Wait -- OP's gripe this isn't regarding standard pgbench, but multiple large concurrent 'insert into foo select...'.  I looked in the code and it appears that the only bulk insert strategy using operations are copy,

Re: [HACKERS] Draft release notes complete

2012-05-24 Thread Peter Geoghegan
On 21 May 2012 19:10, Josh Berkus j...@agliodbs.com 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 feature. First, shouldn't we be having

Re: [HACKERS] release note item

2012-05-24 Thread Bruce Momjian
On Mon, May 21, 2012 at 12:19:27PM -0400, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: Regarding the item: * Properly handle empty arrays returned by PL/Perl functions (Andrew Dunstan) DETAILS? This was a bug fix, not a feature, and in any case is due to Alex

Re: [HACKERS] Draft release notes complete

2012-05-24 Thread Bruce Momjian
On Thu, May 24, 2012 at 10:34:22PM +0100, Peter Geoghegan wrote: In passing, I noticed this: E.1.3.12.2. pg_stat_statements Improve pg_stat_statements to aggregate similar queries (Peter Geoghegan, Tom Lane) Improve pg_stat_statements' handling of PREPARE/EXECUTE statements (Tom Lane)

Re: [HACKERS] heap metapages

2012-05-24 Thread Bruce Momjian
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 the .meta files would be wholly new and can be

Re: [HACKERS] Draft release notes complete

2012-05-24 Thread Peter Geoghegan
On 24 May 2012 22:57, Bruce Momjian br...@momjian.us wrote: OK, item moved down.  We have not have bug fix designation.  You have a suggestion? I assumed you were going to put it beside the other compatibility note relating to pg_stat_statements, Change pg_stat_statements' total_time column to

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

2012-05-24 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Thu, May 24, 2012 at 4:46 PM, Merlin Moncure mmonc...@gmail.com wrote: We don't get to skip wal of course, but we should be able to use a bulk insert strategy, especially if there was some way of predicting that a large number of tuples were going

Re: [HACKERS] Per-Database Roles

2012-05-24 Thread Bruce Momjian
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, and come up with some ad-hoc method of representing the set

  1   2   >