Re: [HACKERS] Move unused buffers to freelist

2013-08-06 Thread Amit Kapila
On Friday, June 28, 2013 6:20 PM Robert Haas wrote: On Fri, Jun 28, 2013 at 12:52 AM, Amit Kapila amit.kap...@huawei.com wrote: Currently it wakes up based on bgwriterdelay config parameter which is by default 200ms, so you means we should think of waking up bgwriter based on allocations

Re: [HACKERS] System catalog vacuum issues

2013-08-06 Thread Craig Ringer
On 08/06/2013 01:56 PM, Vlad Arkhipov wrote: Hello, We are suffering from a long-standing issue with autovacuuming/vacuuming system catalogs on the production server. We are actively using temporary tables in the legacy application, so system catalogs grows unbounded in time. Autovacuum

Re: [HACKERS] mvcc catalo gsnapshots and TopTransactionContext

2013-08-06 Thread Andres Freund
On 2013-08-05 13:09:31 -0400, Noah Misch wrote: On Fri, Jul 12, 2013 at 11:42:23AM +0200, Andres Freund wrote: On 2013-07-11 15:09:45 -0400, Tom Lane wrote: It never has been, and never will be, allowed to call the catcache code without being in a transaction. What do you think will

Re: [HACKERS] System catalog vacuum issues

2013-08-06 Thread Vlad Arkhipov
On 08/06/2013 04:00 PM, Craig Ringer wrote: On 08/06/2013 01:56 PM, Vlad Arkhipov wrote: Hello, We are suffering from a long-standing issue with autovacuuming/vacuuming system catalogs on the production server. We are actively using temporary tables in the legacy application, so system

Re: [HACKERS] System catalog vacuum issues

2013-08-06 Thread Sergey Konoplev
On Mon, Aug 5, 2013 at 10:56 PM, Vlad Arkhipov arhi...@dc.baikal.ru wrote: dcdb=# select relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum from pg_stat_sys_tables where relname = 'pg_attribute'; relname| n_live_tup | n_dead_tup | last_vacuum | last_autovacuum

Re: [HACKERS] System catalog vacuum issues

2013-08-06 Thread Vlad Arkhipov
On 08/06/2013 04:26 PM, Sergey Konoplev wrote: On Mon, Aug 5, 2013 at 10:56 PM, Vlad Arkhipov arhi...@dc.baikal.ru wrote: dcdb=# select relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum from pg_stat_sys_tables where relname = 'pg_attribute'; relname| n_live_tup | n_dead_tup

Re: [HACKERS] System catalog vacuum issues

2013-08-06 Thread Sergey Konoplev
On Tue, Aug 6, 2013 at 12:37 AM, Vlad Arkhipov arhi...@dc.baikal.ru wrote: What pgstattuple shows on this table? http://www.postgresql.org/docs/9.2/interactive/pgstattuple.html dcdb=# select * from pgstattuple('pg_catalog.pg_attribute'); table_len | tuple_count | tuple_len | tuple_percent

[HACKERS] pgbench progress report improvements

2013-08-06 Thread Fabien
Here is a patch submission for reference to the next commitfest. Improve pgbench measurements progress report - Use progress option both under init bench. Activate progress report by default, every 5 seconds. When initializing, --quiet reverts to the old every 100,000 insertions

Re: [HACKERS] Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])

2013-08-06 Thread Dimitri Fontaine
Greg Stark st...@mit.edu writes: set it too large. Or if I set multiple settings together and you set one of them you'll undo my change and lose just part of my changes but not stop me from setting the others inconsistently. So we need to be able to change more than one setting in a single

Re: [HACKERS] [GENERAL] Bottlenecks with large number of relation segment files

2013-08-06 Thread KONDO Mitsumasa
(2013/08/05 19:28), Andres Freund wrote: On 2013-08-05 18:40:10 +0900, KONDO Mitsumasa wrote: (2013/08/05 17:14), Amit Langote wrote: So, within the limits of max_files_per_process, the routines of file.c should not become a bottleneck? It may not become bottleneck. 1 FD consumes 160 byte in

Re: [HACKERS] [GENERAL] Bottlenecks with large number of relation segment files

2013-08-06 Thread KONDO Mitsumasa
(2013/08/05 21:23), Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: ... Also, there are global limits to the amount of filehandles that can simultaneously opened on a system. Yeah. Raising max_files_per_process puts you at serious risk that everything else on the box will

Re: [HACKERS] [GENERAL] Bottlenecks with large number of relation segment files

2013-08-06 Thread Andres Freund
On 2013-08-06 19:19:41 +0900, KONDO Mitsumasa wrote: (2013/08/05 21:23), Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: ... Also, there are global limits to the amount of filehandles that can simultaneously opened on a system. Yeah. Raising max_files_per_process puts

Re: [HACKERS] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters

2013-08-06 Thread Cédric Villemain
Again, what are we trying to achieve?! no idea - wondering about that myself... It seems we are trying to add grammar for modifying postgresql.conf. Something we can already do easily in a standard extension, but without grammar changes. Maybe better to provide a contrib/ to modify config,

Re: [HACKERS] Should we remove not fast promotion at all?

2013-08-06 Thread Tomonari Katsumata
Hi, 2013/8/6 Tom Lane t...@sss.pgh.pa.us Fujii Masao masao.fu...@gmail.com writes: On Tue, Aug 6, 2013 at 11:40 AM, Andres Freund and...@2ndquadrant.com wrote: FWIW I'd rather keep plain promotion for a release or two. TBH, I have a bit of trust issues regarding the new method, and I'd

Re: [HACKERS] [GENERAL] Bottlenecks with large number of relation segment files

2013-08-06 Thread KONDO Mitsumasa
(2013/08/06 19:33), Andres Freund wrote: On 2013-08-06 19:19:41 +0900, KONDO Mitsumasa wrote: (2013/08/05 21:23), Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: ... Also, there are global limits to the amount of filehandles that can simultaneously opened on a system. Yeah.

Re: [HACKERS] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters

2013-08-06 Thread Tom Lane
=?iso-8859-1?q?C=E9dric_Villemain?= ced...@2ndquadrant.com writes: Again, what are we trying to achieve?! no idea - wondering about that myself... It seems we are trying to add grammar for modifying postgresql.conf. Something we can already do easily in a standard extension, but without

Re: [HACKERS] File-per-GUC WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])

2013-08-06 Thread Bruce Momjian
On Mon, Aug 5, 2013 at 05:29:48PM -0400, Bruce Momjian wrote: That is a killer point. So really the value of the global lock is to ensure serializability when transactions are updating multiple GUCs. Well, I think it is more than that. The global lock will allow multiple GUCs to be

Re: [HACKERS] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters

2013-08-06 Thread Bruce Momjian
On Tue, Aug 6, 2013 at 12:40:12PM +0200, Cédric Villemain wrote: Again, what are we trying to achieve?! no idea - wondering about that myself... It seems we are trying to add grammar for modifying postgresql.conf. Something we can already do easily in a standard extension, but without

Re: [HACKERS] System catalog vacuum issues

2013-08-06 Thread Tom Lane
Vlad Arkhipov arhi...@dc.baikal.ru writes: On 08/06/2013 04:26 PM, Sergey Konoplev wrote: What pgstattuple shows on this table? dcdb=# select * from pgstattuple('pg_catalog.pg_attribute'); table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len |

Re: [HACKERS] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters

2013-08-06 Thread Cédric Villemain
There seemed to be agreement on having a config.d, though. Yes. Also, the validate_conf_parameter() (or some similar name) Amit added in his patch sounds useful if an extension can use it (to check a GUC someone want to change, to check a configuration file, ...) -- Cédric Villemain +33 (0)6

Re: [HACKERS] Add json_typeof() and json_is_*() functions.

2013-08-06 Thread Robert Haas
On Fri, Aug 2, 2013 at 8:22 AM, Andrew Tipton and...@kiwidrew.com wrote: But without json_is_scalar(), the choice is one of these two forms: json_typeof() NOT IN ('object', 'array') json_typeof() IN ('string', 'number', 'boolean', 'null') The first of those is what seemed to make sense to

Re: [HACKERS] how to pass data (tuples) to worker processes?

2013-08-06 Thread Robert Haas
On Sat, Aug 3, 2013 at 6:31 AM, Andrew Tipton and...@kiwidrew.com wrote: Robert: any chance you could share a few more details on the enhancements you're planning for bgworkers? I seem to recall reading that communicating with the dynamic bgworkers after they had been launched was next on

Re: [HACKERS] File-per-GUC WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])

2013-08-06 Thread Amit Kapila
On Tuesday, August 06, 2013 5:39 PM Bruce Momjian wrote: On Mon, Aug 5, 2013 at 05:29:48PM -0400, Bruce Momjian wrote: That is a killer point. So really the value of the global lock is to ensure serializability when transactions are updating multiple GUCs. Well, I think it is more

Re: [HACKERS] [GENERAL] Possible bug with row_to_json

2013-08-06 Thread Tom Lane
Jack Christensen j...@jackchristensen.com writes: jack=# create table player( jack(# player_id serial primary key, jack(# name varchar not null unique jack(# ); CREATE TABLE jack=# insert into player(name) values('Jack'); INSERT 0 1 jack=# select row_to_json(t) jack-# from ( jack(#

Re: [HACKERS] File-per-GUC WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])

2013-08-06 Thread 'Bruce Momjian'
On Tue, Aug 6, 2013 at 06:30:18PM +0530, Amit Kapila wrote: Now, I assume that ALTER SYSTEM SET would automatically issue a pg_reload_conf(), so we would need to make sure that people modifying multiple parameters that are related do it in a single transaction, and that we issue a

Re: [HACKERS] File-per-GUC WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])

2013-08-06 Thread Stephen Frost
* Andres Freund (and...@2ndquadrant.com) wrote: I don't think we're designing a feature that's supposed to be used under heavy concurrency here. If you have users/tools doing conflicting actions as superusers you need to solve that by social means, not by technical ones. If this actually gets

Re: [HACKERS] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters

2013-08-06 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: =?iso-8859-1?q?C=E9dric_Villemain?= ced...@2ndquadrant.com writes: Maybe better to provide a contrib/ to modify config, then design what we can achieve more with an ALTER SYSTEM command. Hmm ... putting the UI into a contrib module would neatly

Re: [HACKERS] File-per-GUC WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])

2013-08-06 Thread Bruce Momjian
On Tue, Aug 6, 2013 at 10:54:22AM -0400, Stephen Frost wrote: * Andres Freund (and...@2ndquadrant.com) wrote: I don't think we're designing a feature that's supposed to be used under heavy concurrency here. If you have users/tools doing conflicting actions as superusers you need to solve

Re: [HACKERS] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters

2013-08-06 Thread Bruce Momjian
On Tue, Aug 6, 2013 at 10:58:52AM -0400, Stephen Frost wrote: * Tom Lane (t...@sss.pgh.pa.us) wrote: =?iso-8859-1?q?C=E9dric_Villemain?= ced...@2ndquadrant.com writes: Maybe better to provide a contrib/ to modify config, then design what we can achieve more with an ALTER SYSTEM

[HACKERS] latest pgbench results

2013-08-06 Thread Robert Haas
Here are the latest pgbench results from the IBM POWER7 machine. These results were gathered about two weeks ago. I ran each test configuration three times; below I report the median of the three results. For all runs, I used scale factor = 300, clients = jobs, and the following non-default

Re: [HACKERS] DATE type output does not follow datestyle parameter

2013-08-06 Thread Bruce Momjian
On Wed, Jul 24, 2013 at 09:06:30PM +0900, MauMau wrote: Hello, The description of datestyle parameter does not seem to match the actual behavior. Is this a bug to be fixed? Which do you think should be corrected, the program or the manual? The manual says: DateStyle (string) Sets

Re: [HACKERS] DATE type output does not follow datestyle parameter

2013-08-06 Thread Robert Haas
On Tue, Aug 6, 2013 at 11:40 AM, Bruce Momjian br...@momjian.us wrote: Yes, you are correct, this is inconsistent. Let me look at writing a patch to fix this. Is this format so old that we can't fix this? I think I would be more inclined to change the documentation than the behavior. --

Re: [HACKERS] latest pgbench results

2013-08-06 Thread Fabien COELHO
Hello, Here are the latest pgbench results from the IBM POWER7 machine. These results were gathered about two weeks ago. I ran each test configuration three times; below I report the median of the three results. For all runs, I used scale factor = 300, This means a database size of about

Re: [HACKERS] DATE type output does not follow datestyle parameter

2013-08-06 Thread Tom Lane
Bruce Momjian br...@momjian.us writes: Is this format so old that we can't fix this? Yes. I don't see any reason to change it, either, as nobody has complained that it's actually bad. If you feel a compulsion to change the docs, do that. regards, tom lane -- Sent

Re: [HACKERS] Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])

2013-08-06 Thread Boszormenyi Zoltan
2013-08-05 16:01 keltezéssel, Stephen Frost írta: * Greg Stark (st...@mit.edu) wrote: On Fri, Aug 2, 2013 at 4:05 PM, Stephen Frost sfr...@snowman.net wrote: I'm not even clear we do want this in /etc since none of our GUC options are repeatable things like Apache virtual servers. It actually

Re: [HACKERS] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters

2013-08-06 Thread Josh Berkus
On 08/06/2013 05:29 AM, Bruce Momjian wrote: Let's look at the problems: * remote users can lock themselves out of the server * interconnected GUC variables are complex to change * need a way to disable this feature Given the above, I am not sure I see a way forward for ALTER SYSTEM

Re: [HACKERS] latest pgbench results

2013-08-06 Thread Josh Berkus
On 08/06/2013 08:26 AM, Robert Haas wrote: Here are the latest pgbench results from the IBM POWER7 machine. These results were gathered about two weeks ago. I ran each test configuration three times; below I report the median of the three results. For all runs, I used scale factor = 300,

Re: [HACKERS] Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])

2013-08-06 Thread Bruce Momjian
On Tue, Aug 6, 2013 at 06:34:35PM +0200, Boszormenyi Zoltan wrote: 2013-08-05 16:01 keltezéssel, Stephen Frost írta: * Greg Stark (st...@mit.edu) wrote: On Fri, Aug 2, 2013 at 4:05 PM, Stephen Frost sfr...@snowman.net wrote: I'm not even clear we do want this in /etc since none of our GUC

Re: [HACKERS] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters

2013-08-06 Thread Bruce Momjian
On Tue, Aug 6, 2013 at 10:33:20AM -0700, Josh Berkus wrote: On 08/06/2013 05:29 AM, Bruce Momjian wrote: Let's look at the problems: * remote users can lock themselves out of the server * interconnected GUC variables are complex to change * need a way to disable this feature

Re: [HACKERS] latest pgbench results

2013-08-06 Thread Robert Haas
On Tue, Aug 6, 2013 at 12:04 PM, Fabien COELHO coe...@cri.ensmp.fr wrote: How long did it run? Each run was 30 minutes. As there has been some changes in pgbench, would it make sense to run the same pgbench version (whatever) against the different servers? I used the master branch's version

Re: [HACKERS] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters

2013-08-06 Thread Alvaro Herrera
Josh Berkus escribió: (2) Enabling DBAAS services to give users limited control over settings. (5) Enabling new ways of writing Puppet/Chef/etc. scripts, which can check a setting before changing it. Surely these two cases are better covered by conf.d. For (2), DBaaS providers could offer

Re: [HACKERS] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters

2013-08-06 Thread Claudio Freire
On Tue, Aug 6, 2013 at 3:31 PM, Bruce Momjian br...@momjian.us wrote: I'd like to look at use cases, and let's see how ALTER SYSTEM SET addresses or doesn't address these use cases. I'd really like it if some other folks also posted use cases they know of. (1) Making is easier for GUIs to

Re: [HACKERS] Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])

2013-08-06 Thread Boszormenyi Zoltan
2013-08-06 19:41 keltezéssel, Bruce Momjian írta: On Tue, Aug 6, 2013 at 06:34:35PM +0200, Boszormenyi Zoltan wrote: 2013-08-05 16:01 keltezéssel, Stephen Frost írta: * Greg Stark (st...@mit.edu) wrote: On Fri, Aug 2, 2013 at 4:05 PM, Stephen Frost sfr...@snowman.net wrote: I'm not even

Re: [HACKERS] DATE type output does not follow datestyle parameter

2013-08-06 Thread Bruce Momjian
On Tue, Aug 6, 2013 at 12:09:53PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Is this format so old that we can't fix this? Yes. I don't see any reason to change it, either, as nobody has complained that it's actually bad. If you feel a compulsion to change the docs,

[HACKERS] Doc Patch: Subquery section to say that subqueries can't modify data

2013-08-06 Thread Karl O. Pinc
Hi, The attached documentation patch, doc-subqueries-v1.patch, applies against head. I wanted to document that subqueries can't modify data. This is mentioned in the documentation for SELECT and implied elsewhere but I was looking for something more than an 'in-passing' mention. (I wrote a

Re: [HACKERS] Review: UNNEST (and other functions) WITH ORDINALITY

2013-08-06 Thread Greg Stark
On Mon, Aug 5, 2013 at 1:31 AM, Robert Haas robertmh...@gmail.com wrote: This looks like really nice work. It does. It's functionally equivalent to my attempt but with much better comments and cleaner code. But it doesn't seem to cover the case I was stumped on, namely nulls first appearing

[HACKERS] Re: Doc Patch: Subquery section to say that subqueries can't modify data

2013-08-06 Thread David Johnston
Instead of simply expanding the section on sub-queries, which may still be worthwhile, it seems that we have effectively introduced a new kind of query - namely one that mixes both query DDL and update DDL into a kind of hybrid query. An entire section describing the means to implement these

[HACKERS] refactor heap_deform_tuple guts

2013-08-06 Thread Alvaro Herrera
Hi, heap_deform_tuple and slot_deform_tuple contain duplicated code. This patch refactors them so that the guts are in a single place. I have checked the resulting assembly code for heap_deform_tuple, and with the inline declaration, the gcc version I have (4.7.2) generates almost identical

Re: [HACKERS] Re: Doc Patch: Subquery section to say that subqueries can't modify data

2013-08-06 Thread Karl O. Pinc
Good points. On 08/06/2013 05:15:28 PM, David Johnston wrote: Instead of simply expanding the section on sub-queries, which may still be worthwhile, it seems that we have effectively introduced a new kind of query - namely one that mixes both query DDL and update DDL into a kind of hybrid

Re: [HACKERS] Review: UNNEST (and other functions) WITH ORDINALITY

2013-08-06 Thread David Fetter
On Tue, Aug 06, 2013 at 11:10:11PM +0100, Greg Stark wrote: On Mon, Aug 5, 2013 at 1:31 AM, Robert Haas robertmh...@gmail.com wrote: This looks like really nice work. It does. It's functionally equivalent to my attempt but with much better comments and cleaner code. But it doesn't seem

Re: [HACKERS] refactor heap_deform_tuple guts

2013-08-06 Thread Robert Haas
On Tue, Aug 6, 2013 at 6:32 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: heap_deform_tuple and slot_deform_tuple contain duplicated code. This patch refactors them so that the guts are in a single place. I have checked the resulting assembly code for heap_deform_tuple, and with the

Re: [HACKERS] Unsafe GUCs and ALTER SYSTEM WAS: Re: ALTER SYSTEM SET

2013-08-06 Thread Greg Smith
On 8/5/13 2:36 PM, Josh Berkus wrote: Most of our users not on Heroku are running with superuser as the app user now. Like, 95% of them based on my personal experience (because our object permissions management sucks). My percentage wouldn't be nearly that high. 95% of database installs

Re: [HACKERS] Unsafe GUCs and ALTER SYSTEM WAS: Re: ALTER SYSTEM SET

2013-08-06 Thread Bruce Momjian
On Tue, Aug 6, 2013 at 09:24:47PM -0400, Greg Smith wrote: # ALTER SYSTEM SET shared_buffers = ‘8GB’ FORCE; NOTICE: Changing shared_buffers only takes effect after a server restart. ALTER SYSTEM Will bad examples pop up in the Internet that just use FORCE all the time? Sure they will,

Re: [HACKERS] Unsafe GUCs and ALTER SYSTEM WAS: Re: ALTER SYSTEM SET

2013-08-06 Thread Amit Kapila
From: Greg Smith [mailto:g...@2ndquadrant.com] Sent: Wednesday, August 07, 2013 6:55 AM To: Josh Berkus Cc: Stephen Frost; Bruce Momjian; Greg Stark; Andres Freund; Alvaro Herrera; Fujii Masao; Robert Haas; Amit Kapila; Dimitri Fontaine; pgsql-hackers@postgresql.org; Tom Lane Subject: Re:

Re: [HACKERS] Unsafe GUCs and ALTER SYSTEM WAS: Re: ALTER SYSTEM SET

2013-08-06 Thread Amit Kapila
On Wednesday, August 07, 2013 8:01 AM Bruce Momjian wrote: On Tue, Aug 6, 2013 at 09:24:47PM -0400, Greg Smith wrote: # ALTER SYSTEM SET shared_buffers = ‘8GB’ FORCE; NOTICE: Changing shared_buffers only takes effect after a server restart. ALTER SYSTEM Will bad examples pop up in

Re: [HACKERS] how to pass data (tuples) to worker processes?

2013-08-06 Thread Amit Kapila
On Tuesday, August 06, 2013 6:29 PM Robert Haas wrote: On Sat, Aug 3, 2013 at 6:31 AM, Andrew Tipton and...@kiwidrew.com wrote: Robert: any chance you could share a few more details on the enhancements you're planning for bgworkers? I seem to recall reading that communicating with the