Re: [HACKERS] How about to have relnamespace and relrole?

2015-03-02 Thread Robert Haas
On Tue, Feb 3, 2015 at 10:12 AM, Tom Lane t...@sss.pgh.pa.us wrote: Two reasons this isn't terribly compelling are (1) it's creating a join in a place where the planner can't possibly see it and optimize it, and (2) you risk MVCC anomalies because the reg* output routines would not be using

[HACKERS] Why are json = jsonb casts marked as explicit-only?

2015-03-02 Thread Tom Lane
Surely they should be assignment level, if not implicit. I don't really see why I should have to write a cast to assign a json expression result into a jsonb column, in particular. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)

Re: [HACKERS] Bootstrap DATA is a pita

2015-03-02 Thread Greg Stark
On Sat, Feb 21, 2015 at 11:08 PM, Andres Freund and...@2ndquadrant.com wrote: The changes in pg_proc.h are just to demonstrate that using names instead of oids works. Fwiw I always thought it was strange how much of our bootstrap was done in a large static text file. Very little of it is

Re: [HACKERS] Bug in pg_dump

2015-03-02 Thread Michael Paquier
On Tue, Mar 3, 2015 at 4:57 AM, Stephen Frost sfr...@snowman.net wrote: * Michael Paquier (michael.paqu...@gmail.com) wrote: On Mon, Mar 2, 2015 at 6:27 AM, Stephen Frost sfr...@snowman.net wrote: Please see the latest version of this, attached. I've removed the left join, re-used the

Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE} 2.0

2015-03-02 Thread Peter Geoghegan
On Mon, Mar 2, 2015 at 12:15 PM, Heikki Linnakangas hlinn...@iki.fi wrote: Hmm. I used a b-tree to estimate the effect that the locking would have in the UPSERT case, for UPSERT into a table with a b-tree index. But you're right that for the question of whether this is acceptable for the case

Re: [HACKERS] autogenerated column names + views are a dump hazard

2015-03-02 Thread Andres Freund
On 2015-03-02 16:32:53 -0500, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: The easiest way to solve this would teach ruleutils.c to simply always attach AS clauses for auto-generated columnnames. Won't look too pretty though. Does somebody have a better idea? No, it would

Re: [HACKERS] Providing catalog view to pg_hba.conf file - Patch submission

2015-03-02 Thread Greg Stark
On Mon, Mar 2, 2015 at 7:51 PM, Greg Stark st...@mit.edu wrote: Nobody's allocating anything that big. It's a list of 25,000 pointers to 472-byte structs. That should add up to about 11MB. Instead the memory context is a total of 954606152 bytes which is still under a gigabyte and the database

Re: [HACKERS] autogenerated column names + views are a dump hazard

2015-03-02 Thread Tom Lane
Andres Freund and...@2ndquadrant.com writes: CREATE VIEW v_03 AS SELECT * FROM (SELECT '2' ORDER BY 1) s; View definition: SELECT s.?column? FROM ( SELECT '2'::text ORDER BY '2'::text) s; Note the added cast to determine the type of the expression and the generated column

Re: [HACKERS] How about to have relnamespace and relrole?

2015-03-02 Thread Andres Freund
On 2015-03-02 16:42:35 -0500, Robert Haas wrote: On Tue, Feb 3, 2015 at 10:12 AM, Tom Lane t...@sss.pgh.pa.us wrote: Two reasons this isn't terribly compelling are (1) it's creating a join in a place where the planner can't possibly see it and optimize it, and (2) you risk MVCC anomalies

Re: [HACKERS] POLA violation with \c service=

2015-03-02 Thread David Fetter
On Mon, Mar 02, 2015 at 04:52:37PM -0500, Robert Haas wrote: On Mon, Feb 23, 2015 at 3:56 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: David Fetter wrote: My thinking behind this was that the patch is a bug fix and intended to be back-patched, so I wanted to mess with as little

Re: [HACKERS] Proposal: knowing detail of config files via SQL

2015-03-02 Thread Robert Haas
On Sat, Feb 28, 2015 at 12:27 AM, Stephen Frost sfr...@snowman.net wrote: While this generally works, the usual expectation is that functions that should be superuser-only have a check in the function rather than depending on the execute privilege. I'm certainly happy to debate the merits of

Re: [HACKERS] POLA violation with \c service=

2015-03-02 Thread Robert Haas
On Mon, Feb 23, 2015 at 3:56 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: David Fetter wrote: My thinking behind this was that the patch is a bug fix and intended to be back-patched, so I wanted to mess with as little infrastructure as possible. A new version of libpq seems like a very

Re: [HACKERS] pgaudit - an auditing extension for PostgreSQL

2015-03-02 Thread Bruce Momjian
On Thu, Feb 19, 2015 at 12:29:18PM +0900, Fujii Masao wrote: The pg_audit doesn't log BIND parameter values when prepared statement is used. Seems this is an oversight of the patch. Or is this intentional? It's actually intentional - following the model I talked about in my earlier

Re: [HACKERS] Patch: raise default for max_wal_segments to 1GB

2015-03-02 Thread Andres Freund
Hi, On 2015-03-02 15:40:27 -0800, Josh Berkus wrote: ! #max_wal_size = 1GB # in logfile segments Independent of the rest of the changes, the in logfile segments bit should probably be changed. Greetings, Andres Freund -- Andres Freund

Re: [HACKERS] Patch: raise default for max_wal_segments to 1GB

2015-03-02 Thread Josh Berkus
On 03/02/2015 03:43 PM, Andres Freund wrote: Hi, On 2015-03-02 15:40:27 -0800, Josh Berkus wrote: ! #max_wal_size = 1GB# in logfile segments Independent of the rest of the changes, the in logfile segments bit should probably be changed. Point! Although I think

Re: [HACKERS] How about to have relnamespace and relrole?

2015-03-02 Thread Jim Nasby
On 3/2/15 3:56 PM, Andres Freund wrote: On 2015-03-02 16:42:35 -0500, Robert Haas wrote: On Tue, Feb 3, 2015 at 10:12 AM, Tom Lanet...@sss.pgh.pa.us wrote: Two reasons this isn't terribly compelling are (1) it's creating a join in a place where the planner can't possibly see it and optimize

Re: [HACKERS] [REVIEW] Re: Compression of full-page-writes

2015-03-02 Thread Andres Freund
On 2015-03-03 08:59:30 +0900, Michael Paquier wrote: Already mentioned upthread, but I agree with Fujii-san here: adding information related to the state of a block image in XLogRecordBlockHeader makes little sense because we are not sure to have a block image, perhaps there is only data

Re: [HACKERS] [REVIEW] Re: Compression of full-page-writes

2015-03-02 Thread Michael Paquier
On Tue, Mar 3, 2015 at 9:24 AM, Andres Freund and...@2ndquadrant.com wrote: On 2015-03-03 08:59:30 +0900, Michael Paquier wrote: Already mentioned upthread, but I agree with Fujii-san here: adding information related to the state of a block image in XLogRecordBlockHeader makes little sense

Re: [HACKERS] Proposal: knowing detail of config files via SQL

2015-03-02 Thread Jim Nasby
On 2/27/15 11:27 PM, Stephen Frost wrote: @@ -344,6 +346,21 @@ ProcessConfigFile(GucContext context) PGC_BACKEND, PGC_S_DYNAMIC_DEFAULT); } + guc_file_variables = (ConfigFileVariable *) + guc_malloc(FATAL,

Re: [HACKERS] Patch: raise default for max_wal_segments to 1GB

2015-03-02 Thread Josh Berkus
On 03/02/2015 03:18 PM, Josh Berkus wrote: Attached. Per discussion on the thread Redesigning checkpoint_segments this raises the default for the new parameter max_wal_size to 1GB. Seems too small to add it to the CF, but if you want me to, I will. Ooops, patch didn't include the docs

Re: [HACKERS] [REVIEW] Re: Compression of full-page-writes

2015-03-02 Thread Michael Paquier
On Tue, Mar 3, 2015 at 5:17 AM, Rahila Syed rahilasye...@gmail.com wrote: Hello, When I test the WAL or replication related features, I usually run make installcheck and pgbench against the master at the same time after setting up the replication environment. I will conduct these tests before

Re: [HACKERS] Patch: raise default for max_wal_segments to 1GB

2015-03-02 Thread Heikki Linnakangas
On 03/03/2015 01:43 AM, Andres Freund wrote: On 2015-03-02 15:40:27 -0800, Josh Berkus wrote: ! #max_wal_size = 1GB # in logfile segments Independent of the rest of the changes, the in logfile segments bit should probably be changed. The base unit is still logfile

Re: [HACKERS] Redesigning checkpoint_segments

2015-03-02 Thread Stephen Frost
Heikki, On Monday, March 2, 2015, Heikki Linnakangas hlinn...@iki.fi wrote: On 03/02/2015 08:05 PM, Josh Berkus wrote: On 03/02/2015 05:38 AM, Stephen Frost wrote: * Robert Haas (robertmh...@gmail.com) wrote: On Mon, Mar 2, 2015 at 6:43 AM, Heikki Linnakangas hlinn...@iki.fi wrote: On

Re: [HACKERS] Weirdly pesimistic estimates in optimizer

2015-03-02 Thread Tomas Vondra
Hi David ;-) On 2.3.2015 20:19, David Kubečka wrote: The question is why optimizer, or rather the cost estimator, produced so much different estimates upon very small change in input. Moreover it seems that the main culprit of bad estimates isn't actually directly related to outer table, but

Re: [HACKERS] 32bit OID wrap around concerns

2015-03-02 Thread Tom Lane
Alvaro Herrera alvhe...@2ndquadrant.com writes: Qingqing Zhou wrote: Do we think above scenario is something we shall worry about? Especially for large databases. IMO in theory it sucks that toast values use the shared OID generator, though in practice I have never seen a problem due to

Re: [HACKERS] Idea: closing the loop for pg_ctl reload

2015-03-02 Thread Kevin Grittner
Jan de Visser j...@de-visser.net wrote: On March 2, 2015 09:50:49 AM Tom Lane wrote: However, you could and should use pg_malloc0, which takes care of that for you... I am (using pg_malloc, that is). So, just to be sure: pg_malloc memsets the block to 0, right? I think you may have misread

[HACKERS] autogenerated column names + views are a dump hazard

2015-03-02 Thread Andres Freund
Hi, Kathy (CCed) just found a bug in BDR that turned out to actually be a bug in postgres. CREATE VIEW v_03 AS SELECT * FROM (SELECT '2' ORDER BY 1) s; postgres[l]=# \d+ v_03 View public.v_03 ┌──┬──┬───┬──┬─┐ │ Column │ Type │

Re: [HACKERS] 32bit OID wrap around concerns

2015-03-02 Thread Qingqing Zhou
On Mon, Mar 2, 2015 at 12:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: There is an issue if you do a dump and restore: the toast OIDs used in any one table will be consecutive after that, because we load all the data for each table sequentially. With these consecutive oids, the aggravated oid

Re: [HACKERS] Join push-down support for foreign tables

2015-03-02 Thread Thom Brown
On 2 March 2015 at 12:48, Shigeru Hanada shigeru.han...@gmail.com wrote: Attached is the revised/rebased version of the $SUBJECT. This patch is based on Kaigai-san's custom/foreign join patch, so please apply it before this patch. In this version I changed some points from original

Re: [HACKERS] Idea: closing the loop for pg_ctl reload

2015-03-02 Thread Tom Lane
Jan de Visser j...@de-visser.net writes: 4/ Question: Can I assume pg_malloc allocated memory is set to zero? If not, is it OK to do a memset(..., 0, ...)? I don't have much experience on any of the esoteric platforms pgsql supports... No, you need the memset. You might accidentally get

Re: [HACKERS] Idea: GSoC - Query Rewrite with Materialized Views

2015-03-02 Thread Kevin Grittner
Eric Grinstein e...@aluno.puc-rio.br wrote: I would be very interested in tracking the staleness of the MV. You see, I work in a research group in database tuning, and we have implemented some solutions to take advantage of MV's and speed up queries. Please be sure, right up front, that

Re: [HACKERS] File based Incremental backup v8

2015-03-02 Thread Fujii Masao
On Thu, Feb 12, 2015 at 10:50 PM, Marco Nenciarini marco.nenciar...@2ndquadrant.it wrote: Hi, I've attached an updated version of the patch. basebackup.c:1565: warning: format '%lld' expects type 'long long int', but argument 8 has type '__off_t' basebackup.c:1565: warning: format '%lld'

Re: [HACKERS] Report search_path value back to the client.

2015-03-02 Thread Alexander Kukushkin
Hi, 2015-02-20 16:19 GMT+01:00 Tom Lane t...@sss.pgh.pa.us: 2. Semantics. The existing GUC_REPORT variables are all things that directly relate to client-visible behavior, eg how values of type timestamp will be interpreted and printed. search_path is no such thing, so it's hard to make a

Re: [HACKERS] Join push-down support for foreign tables

2015-03-02 Thread Kouhei Kaigai
I seem to be getting a problem with whole-row references: # SELECT p.name, c.country, e.pet_name, p FROM pets e INNER JOIN people p on e.person_id = p.id inner join countries c on p.country_id = c.id; ERROR: table r has 3 columns available but 4 columns specified CONTEXT: Remote SQL

Re: [HACKERS] Providing catalog view to pg_hba.conf file - Patch submission

2015-03-02 Thread Greg Stark
​So earlier someone commented that using lists list_nth() seemed odd and a tuplestore might be better. In fact using lists this way is O(n^2). I've done some quick tests and it doesn't start being a problem until about 10,000 lines which obviously isn't a terribly common way to use

Re: [HACKERS] Join push-down support for foreign tables

2015-03-02 Thread Thom Brown
On 2 March 2015 at 14:07, Kouhei Kaigai kai...@ak.jp.nec.com wrote: I seem to be getting a problem with whole-row references: # SELECT p.name, c.country, e.pet_name, p FROM pets e INNER JOIN people p on e.person_id = p.id inner join countries c on p.country_id = c.id; ERROR: table r

Re: [HACKERS] Providing catalog view to pg_hba.conf file - Patch submission

2015-03-02 Thread Stephen Frost
Greg, * Greg Stark (st...@mit.edu) wrote: On Mon, Mar 2, 2015 at 6:36 AM, Haribabu Kommi kommi.harib...@gmail.com wrote: Loading pg_hba.conf during SIGHUP in the backends will solve the problem of displaying the data which is not yet loaded. This change may produce a warning if it

Re: [HACKERS] Redesigning checkpoint_segments

2015-03-02 Thread Stephen Frost
* Robert Haas (robertmh...@gmail.com) wrote: On Mon, Mar 2, 2015 at 6:43 AM, Heikki Linnakangas hlinn...@iki.fi wrote: On 02/26/2015 01:32 AM, Josh Berkus wrote: But ... I thought we were going to raise the default for max_wal_size to something much higher, like 1GB? That's what was

Re: [HACKERS] Redesigning checkpoint_segments

2015-03-02 Thread Heikki Linnakangas
On 02/26/2015 01:32 AM, Josh Berkus wrote: But ... I thought we were going to raise the default for max_wal_size to something much higher, like 1GB? That's what was discussed on this thread. No conclusion was reached on that. Me and some others were against raising the default, while others

Re: [HACKERS] remove pg_standby?

2015-03-02 Thread Fujii Masao
On Mon, Mar 2, 2015 at 8:37 PM, Heikki Linnakangas hlinn...@iki.fi wrote: On 03/02/2015 11:53 AM, Fujii Masao wrote: On Sat, Feb 28, 2015 at 5:00 AM, Josh Berkus j...@agliodbs.com wrote: On 11/10/2014 10:54 AM, Magnus Hagander wrote: On Mon, Nov 10, 2014 at 7:48 PM, Heikki Linnakangas

Re: [HACKERS] Providing catalog view to pg_hba.conf file - Patch submission

2015-03-02 Thread Greg Stark
On Mon, Mar 2, 2015 at 6:36 AM, Haribabu Kommi kommi.harib...@gmail.com wrote: Loading pg_hba.conf during SIGHUP in the backends will solve the problem of displaying the data which is not yet loaded. This change may produce a warning if it fails to load pg_hba.conf in the backends. This

Re: [HACKERS] Redesigning checkpoint_segments

2015-03-02 Thread Robert Haas
On Mon, Mar 2, 2015 at 6:43 AM, Heikki Linnakangas hlinn...@iki.fi wrote: On 02/26/2015 01:32 AM, Josh Berkus wrote: But ... I thought we were going to raise the default for max_wal_size to something much higher, like 1GB? That's what was discussed on this thread. No conclusion was reached

Re: [HACKERS] Join push-down support for foreign tables

2015-03-02 Thread Shigeru Hanada
Attached is the revised/rebased version of the $SUBJECT. This patch is based on Kaigai-san's custom/foreign join patch, so please apply it before this patch. In this version I changed some points from original postgres_fdw. 1) Disabled SELECT clause optimization ~9.4 postgres_fdw lists only

Re: [HACKERS] remove pg_standby?

2015-03-02 Thread Heikki Linnakangas
On 03/02/2015 11:53 AM, Fujii Masao wrote: On Sat, Feb 28, 2015 at 5:00 AM, Josh Berkus j...@agliodbs.com wrote: On 11/10/2014 10:54 AM, Magnus Hagander wrote: On Mon, Nov 10, 2014 at 7:48 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: pg_standby is more configurable than the built-in

Re: [HACKERS] remove pg_standby?

2015-03-02 Thread Fujii Masao
On Sat, Feb 28, 2015 at 5:00 AM, Josh Berkus j...@agliodbs.com wrote: On 11/10/2014 10:54 AM, Magnus Hagander wrote: On Mon, Nov 10, 2014 at 7:48 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: pg_standby is more configurable than the built-in standby_mode=on. You can set the sleep

Re: [HACKERS] pg_basebackup may fail to send feedbacks.

2015-03-02 Thread Fujii Masao
On Tue, Feb 24, 2015 at 6:44 PM, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: Hello, the attached is the v4 patch that checks feedback timing every WAL segments boundary. At Fri, 20 Feb 2015 17:29:14 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp

Re: [HACKERS] INSERT ... ON CONFLICT UPDATE and RLS

2015-03-02 Thread Dean Rasheed
On 26 February 2015 at 09:27, Dean Rasheed dean.a.rash...@gmail.com wrote: I think this should probably be committed as 2 separate patches... On closer inspection, the 2 parts are interrelated since the new self-join test that tests the inheritance planner changes also requires the rewriter

Re: [HACKERS] [REVIEW] Re: Compression of full-page-writes

2015-03-02 Thread Fujii Masao
On Fri, Feb 27, 2015 at 12:44 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Fri, Feb 27, 2015 at 8:01 AM, Michael Paquier michael.paqu...@gmail.com wrote: On Fri, Feb 27, 2015 at 6:54 AM, Rahila Syed rahilasye...@gmail.com wrote: Even this patch doesn't work fine. The standby emit the

Re: [HACKERS] Additional role attributes superuser review

2015-03-02 Thread Adam Brightwell
Alvaro, I thought I saw a comment about using underscore to separate words in privilege names, such as EXCLUSIVE_BACKUP rather than running it all together. Was that idea discarded? I'm not sure there was an actual discussion on the topic. Though, at one point I had proposed it as one of

Re: [HACKERS] Additional role attributes superuser review

2015-03-02 Thread Alvaro Herrera
Adam Brightwell wrote: Alvaro, I thought I saw a comment about using underscore to separate words in privilege names, such as EXCLUSIVE_BACKUP rather than running it all together. Was that idea discarded? I'm not sure there was an actual discussion on the topic. Though, at one point

Re: [HACKERS] File based Incremental backup v8

2015-03-02 Thread Marco Nenciarini
Il 02/03/15 14:21, Fujii Masao ha scritto: On Thu, Feb 12, 2015 at 10:50 PM, Marco Nenciarini marco.nenciar...@2ndquadrant.it wrote: Hi, I've attached an updated version of the patch. basebackup.c:1565: warning: format '%lld' expects type 'long long int', but argument 8 has type '__off_t'

Re: [HACKERS] Merge compact/non compact commits, make aborts dynamically sized

2015-03-02 Thread Andres Freund
On 2015-03-02 19:11:15 +0200, Heikki Linnakangas wrote: Come to think of it, it would be cleaner anyway to move the XLogBeginInsert() and XLogInsert() calls inside XactEmitCommitRecord. Then those structs don't need to be static either. That was my first thought as well - but it doesn't easily

Re: [HACKERS] Merge compact/non compact commits, make aborts dynamically sized

2015-03-02 Thread Andres Freund
On 2015-02-25 12:10:42 +0100, Andres Freund wrote: On 2015-02-24 20:51:42 +0200, Heikki Linnakangas wrote: Regarding XactEmitCommitRecord and XactEmitAbortRecord, I wonder if you could pass an xl_xact_parsed/abort_commit struct to them, instead of the individual fields? You could then also

Re: [HACKERS] Proposal : REINDEX xxx VERBOSE

2015-03-02 Thread Sawada Masahiko
On Wed, Feb 25, 2015 at 4:58 PM, Jim Nasby jim.na...@bluetreble.com wrote: On 2/24/15 8:28 AM, Sawada Masahiko wrote: According to the above discussion, VACUUM and REINDEX should have trailing options. Tom seems (to me) suggesting that SQL-style (bare word preceded by WITH) options and Jim

Re: [HACKERS] Additional role attributes superuser review

2015-03-02 Thread Tom Lane
Stephen Frost sfr...@snowman.net writes: * Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: If we were choosing those names nowadays, would we choose CREATEDB at all in the first place? I think we'd go for something more verbose, probably CREATE_DATABASE. (CREATEROLE is not as old as

Re: [HACKERS] Providing catalog view to pg_hba.conf file - Patch submission

2015-03-02 Thread Greg Stark
On Mon, Mar 2, 2015 at 1:42 PM, Greg Stark st...@mit.edu wrote: ​So earlier someone commented that using lists list_nth() seemed odd and a tuplestore might be better. In fact using lists this way is O(n^2). I've done some quick tests and it doesn't start being a problem until about 10,000

Re: [HACKERS] Additional role attributes superuser review

2015-03-02 Thread Stephen Frost
* Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: Adam Brightwell wrote: I'm not sure there was an actual discussion on the topic. Though, at one point I had proposed it as one of the forms of this attribute. Personally, I think it is easier to read with the underscore. But, ultimately,

Re: [HACKERS] Idea: closing the loop for pg_ctl reload

2015-03-02 Thread Jan de Visser
On March 2, 2015 09:50:49 AM Tom Lane wrote: However, you could and should use pg_malloc0, which takes care of that for you... I am (using pg_malloc, that is). So, just to be sure: pg_malloc memsets the block to 0, right? My question was more along the lines if memsetting to 0 to ensure that

Re: [HACKERS] Merge compact/non compact commits, make aborts dynamically sized

2015-03-02 Thread Heikki Linnakangas
On 03/02/2015 06:51 PM, Andres Freund wrote: On 2015-02-25 12:10:42 +0100, Andres Freund wrote: On 2015-02-24 20:51:42 +0200, Heikki Linnakangas wrote: Regarding XactEmitCommitRecord and XactEmitAbortRecord, I wonder if you could pass an xl_xact_parsed/abort_commit struct to them, instead of

Re: [HACKERS] deparsing utility commands

2015-03-02 Thread Andres Freund
Hi, On 2015-02-15 01:48:15 -0300, Alvaro Herrera wrote: This is a repost of the patch to add CREATE command deparsing support to event triggers. It now supports not only CREATE but also ALTER and other commands such as GRANT/REVOKE, COMMENT ON and SECURITY LABEL. This patch series is broken

Re: [HACKERS] Merge compact/non compact commits, make aborts dynamically sized

2015-03-02 Thread Heikki Linnakangas
On 03/02/2015 07:14 PM, Andres Freund wrote: On 2015-03-02 19:11:15 +0200, Heikki Linnakangas wrote: Come to think of it, it would be cleaner anyway to move the XLogBeginInsert() and XLogInsert() calls inside XactEmitCommitRecord. Then those structs don't need to be static either. That was my

Re: [HACKERS] Idea: closing the loop for pg_ctl reload

2015-03-02 Thread Tom Lane
Jan de Visser j...@de-visser.net writes: On March 2, 2015 09:50:49 AM Tom Lane wrote: However, you could and should use pg_malloc0, which takes care of that for you... I am (using pg_malloc, that is). So, just to be sure: pg_malloc memsets the block to 0, right? No, it doesn't, but

Re: [HACKERS] Idea: closing the loop for pg_ctl reload

2015-03-02 Thread Jan de Visser
On March 2, 2015 12:44:40 PM Tom Lane wrote: No, it doesn't, but pg_malloc0 does. Consult the code if you're confused: src/common/fe_memutils.c Doh! I read pg_malloc( ), not pg_malloc0. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your

Re: [HACKERS] Additional role attributes superuser review

2015-03-02 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: Stephen Frost sfr...@snowman.net writes: * Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: If we were choosing those names nowadays, would we choose CREATEDB at all in the first place? I think we'd go for something more verbose, probably

Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE} 2.0

2015-03-02 Thread Heikki Linnakangas
On 02/21/2015 10:41 PM, Peter Geoghegan wrote: On Sat, Feb 21, 2015 at 11:15 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: What I had in mind is that the winning inserter waits on the other inserter's token, without super-deleting. Like all inserts do today. So the above scenario

Re: [HACKERS] pg_upgrade and rsync

2015-03-02 Thread Bruce Momjian
On Tue, Feb 24, 2015 at 12:13:17PM +0300, Vladimir Borodin wrote: 20 февр. 2015 г., в 18:21, Bruce Momjian br...@momjian.us написал(а): On Fri, Feb 20, 2015 at 09:45:08AM -0500, Bruce Momjian wrote: #3 bothered me as well because it was not specific enough. I like

Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE} 2.0

2015-03-02 Thread Heikki Linnakangas
On 02/17/2015 02:11 AM, Peter Geoghegan wrote: Whatever works, really. I can't say that the performance implications of acquiring that hwlock are at the forefront of my mind. I never found that to be a big problem on an 8 core box, relative to vanilla INSERTs, FWIW - lock contention is not

Re: [HACKERS] Additional role attributes superuser review

2015-03-02 Thread Alvaro Herrera
Stephen Frost wrote: Alvaro, * Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: That being so, I would consider the idea that the NO bit is a separate word rather than run together with the actual privilege name. And given that CREATE has all the options default to NO, there is no need

Re: [HACKERS] add modulo (%) operator to pgbench

2015-03-02 Thread Robert Haas
On Sun, Mar 1, 2015 at 1:42 PM, Robert Haas robertmh...@gmail.com wrote: On Sat, Feb 28, 2015 at 12:06 PM, Stephen Frost sfr...@snowman.net wrote: I took a look through the patch and the discussion and it certainly seems ready to me. I agree with Robert- let's go ahead and get this in and

[HACKERS] Weirdly pesimistic estimates in optimizer

2015-03-02 Thread David Kubečka
Hi all, I have encountered a performance problem with relatively simple query, which I think is caused by the overly pesimistic estimates in optimizer. I have originally run into this issue on a table few GBs large, but it can be reproduced with much smaller table as follows: -- Setup main fact

Re: [HACKERS] Providing catalog view to pg_hba.conf file - Patch submission

2015-03-02 Thread Greg Stark
On Mon, Mar 2, 2015 at 4:36 PM, Greg Stark st...@mit.edu wrote: So I didn't get the memo about SFRM_Materialize. Here's a rewrite of this using that interface which seems to test ok up to 100k. At that point I start running into memory errors on reading the HBA file so I guess that's an

Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE} 2.0

2015-03-02 Thread Peter Geoghegan
On Mon, Mar 2, 2015 at 11:20 AM, Heikki Linnakangas hlinn...@iki.fi wrote: Are we OK with a 10% overhead, caused by the locking? That's probably acceptable if that's what it takes to get UPSERT. But it's not OK just to solve the deadlock issue with regular insertions into a table with exclusion

Re: [HACKERS] Providing catalog view to pg_hba.conf file - Patch submission

2015-03-02 Thread Stephen Frost
* Greg Stark (st...@mit.edu) wrote: On Mon, Mar 2, 2015 at 4:36 PM, Greg Stark st...@mit.edu wrote: So I didn't get the memo about SFRM_Materialize. Here's a rewrite of this using that interface which seems to test ok up to 100k. At that point I start running into memory errors on

Re: [HACKERS] Providing catalog view to pg_hba.conf file - Patch submission

2015-03-02 Thread Greg Stark
On Mon, Mar 2, 2015 at 7:42 PM, Stephen Frost sfr...@snowman.net wrote: Uh, maybe because it's trying to allocate over 1GB and palloc() doesn't support that? Nobody's allocating anything that big. It's a list of 25,000 pointers to 472-byte structs. That should add up to about 11MB. Instead the

Re: [HACKERS] alter user/role CURRENT_USER

2015-03-02 Thread Alvaro Herrera
Kyotaro HORIGUCHI wrote: Hello, thank you for the comment. Looking at this a bit, I'm not sure completely replacing RoleId with a node is the best idea; some of the users of that production in the grammar are okay with accepting only normal strings as names, and don't need all the

Re: [HACKERS] Providing catalog view to pg_hba.conf file - Patch submission

2015-03-02 Thread Alvaro Herrera
Greg Stark wrote: Hm. I'm wondering why I'm getting out of memory errors now with just 25k lines in pg_hba.conf. It looks like the HbaLine struct is only 472 bytes so the list should only be occupying about 11MB. In fact it's occupying about a gigabyte: Maybe it's leaking heavily while

Re: [HACKERS] Additional role attributes superuser review

2015-03-02 Thread Stephen Frost
Alvaro, * Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: Let's go with the NO_ prefix then ... that seems better to me than no separator. Works for me. Thanks! Stephen signature.asc Description: Digital signature

Re: [HACKERS] Bug in pg_dump

2015-03-02 Thread Stephen Frost
* Michael Paquier (michael.paqu...@gmail.com) wrote: On Mon, Mar 2, 2015 at 6:27 AM, Stephen Frost sfr...@snowman.net wrote: Please see the latest version of this, attached. I've removed the left join, re-used the 'query' buffer (instead of destroying and recreating it), and added a bit of

[HACKERS] 32bit OID wrap around concerns

2015-03-02 Thread Qingqing Zhou
One scenario is to use an oid to identify a toast value. As the oid generation is mono increased within a database instance, it can gets wrap around after 2^32 generations. After that: 1. GetNewOidWithIndex() could gets unbounded performance as it needs to by pass already in use values of its own.

Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE} 2.0

2015-03-02 Thread Heikki Linnakangas
On 03/02/2015 09:29 PM, Peter Geoghegan wrote: On Mon, Mar 2, 2015 at 11:20 AM, Heikki Linnakangas hlinn...@iki.fi wrote: Are we OK with a 10% overhead, caused by the locking? That's probably acceptable if that's what it takes to get UPSERT. But it's not OK just to solve the deadlock issue with

Re: [HACKERS] 32bit OID wrap around concerns

2015-03-02 Thread Alvaro Herrera
Qingqing Zhou wrote: One scenario is to use an oid to identify a toast value. As the oid generation is mono increased within a database instance, it can gets wrap around after 2^32 generations. After that: 1. GetNewOidWithIndex() could gets unbounded performance as it needs to by pass already

Re: [HACKERS] [REVIEW] Re: Compression of full-page-writes

2015-03-02 Thread Rahila Syed
Hello, When I test the WAL or replication related features, I usually run make installcheck and pgbench against the master at the same time after setting up the replication environment. I will conduct these tests before sending updated version. Seems this increases the header size of WAL record

Re: [HACKERS] Redesigning checkpoint_segments

2015-03-02 Thread Heikki Linnakangas
On 03/02/2015 08:05 PM, Josh Berkus wrote: On 03/02/2015 05:38 AM, Stephen Frost wrote: * Robert Haas (robertmh...@gmail.com) wrote: On Mon, Mar 2, 2015 at 6:43 AM, Heikki Linnakangas hlinn...@iki.fi wrote: On 02/26/2015 01:32 AM, Josh Berkus wrote: But ... I thought we were going to raise