Re: [HACKERS] deparsing utility commands

2015-02-24 Thread Stephen Frost
* Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: Stephen Frost wrote: Regardless, as I've tried to point out above, the changes which I'm actually suggesting for this initial body of work are just to avoid the parsetree and go based off of what the catalog has. I'm hopeful that's a

Re: [HACKERS] PostgreSQL on z/OS UNIX?

2015-02-24 Thread Gord Tomlin
Hi Tom, No, I haven't tried at this point. I thought it would be a good first step to find out whether anyone else had already tried, in order to avoid reinventing any wheels. z/OS UNIX does have certification as a UNIX system, but there are some quirks. The most common sources of problems

Re: [HACKERS] PostgreSQL on z/OS UNIX?

2015-02-24 Thread David Fetter
On Tue, Feb 24, 2015 at 10:31:15AM -0500, Gord Tomlin wrote: Hello, I see that PostgreSQL will run on S/390 and S/390x processors, but I can find no mention of the z/OS operating system on the web site, in the documentation or in the mailing list archives. Has there been a known attempt

[HACKERS] Partitioning WIP patch (was: Partitioning: issues/ideas)

2015-02-24 Thread Amit Langote
On 21-01-2015 PM 07:26, Amit Langote wrote: Ok, I will limit myself to focusing on following things at the moment: * Provide syntax in CREATE TABLE to declare partition key * Provide syntax in CREATE TABLE to declare a table as partition of a partitioned table and values it contains *

[HACKERS] A typo in backend/replication/README

2015-02-24 Thread Kyotaro HORIGUCHI
Hi, I found a trivial typo in backend/replication/README | Walreceiver IPC | --- | | When the WAL replay in startup process has reached the end of archived WAL, | recoverable using recovery_command, it starts up the walreceiver process I think the recovery_command should be

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

2015-02-24 Thread Kyotaro HORIGUCHI
At Tue, 24 Feb 2015 14:11:28 +0900, Michael Paquier michael.paqu...@gmail.com wrote in cab7npqt6ox3mv++hgmbd3ydu_5-1y5hcddmstk+qdya_mjp...@mail.gmail.com https://commitfest.postgresql.org/4/ Did you fix that manually for me? Looking at the log entry: 2015-02-19 21:11:08 Michael

Re: [HACKERS] Partitioning WIP patch (was: Partitioning: issues/ideas)

2015-02-24 Thread Amit Langote
On 24-02-2015 PM 05:13, Amit Langote wrote: -- partitions CREATE TABLE parent_monthly_x_201401 PARTITION OF parent_monthly_00100_201401 FOR VALUES BETWEEN (2014, 1) AND (2014, 2); CREATE TABLE parent_monthly_x_201402 PARTITION OF parent_monthly_00100_201402 FOR VALUES BETWEEN (2014,

Re: [HACKERS] Partitioning WIP patch (was: Partitioning: issues/ideas)

2015-02-24 Thread Amit Langote
On 24-02-2015 PM 05:13, Amit Langote wrote: Additionally, a partition can itself be further partitioned (though I have not worked on the implementation details of multilevel partitioning yet): CREATE TABLE table_name PARTITION OF parent_name PARTITION BY {RANGE|LIST} ON(key_columns) FOR

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

2015-02-24 Thread Jeevan Chalke
Hi, Personally, I was looking for something like this as I need to see rolename and namespace name many times in my queries rather than it's oid. But making a JOIN expression every-time was a pain. This certainly makes it easier. And I see most DBAs are looking for it. I agree on Tom's concern

Re: [HACKERS] deparsing utility commands

2015-02-24 Thread Stephen Frost
* Andres Freund (and...@2ndquadrant.com) wrote: Hi, On 2015-02-23 19:48:43 -0500, Stephen Frost wrote: Yes, it might be possible to use the same code for a bunch of minor commands, but not for the interesting/complex stuff. We can clearly rebuild at least CREATE commands for all

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

2015-02-24 Thread Jeevan Chalke
Reviewed posted directly on mail thread instead of posting it on commitfest app. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Abbreviated keys for Numeric

2015-02-24 Thread Tomas Vondra
On 24.2.2015 05:09, Andrew Gierth wrote: Tomas == Tomas Vondra tomas.von...@2ndquadrant.com writes: Tomas I believe the small regressions (1-10%) for small data sets, Tomas might be caused by this 'random padding' effect, because that's Tomas probably where L1/L2 cache is most important.

Re: [HACKERS] A typo in backend/replication/README

2015-02-24 Thread Heikki Linnakangas
On 02/24/2015 12:02 PM, Kyotaro HORIGUCHI wrote: Hi, I found a trivial typo in backend/replication/README | Walreceiver IPC | --- | | When the WAL replay in startup process has reached the end of archived WAL, | recoverable using recovery_command, it starts up the walreceiver

[HACKERS] contrib/fuzzystrmatch/dmetaphone.c license

2015-02-24 Thread Heikki Linnakangas
contrib/fuzzystrmatch/dmetaphone.c says this: /* COPYRIGHT NOTICES *** Most of this code is directly from the Text::DoubleMetaphone perl module version 0.05 available from http://www.cpan.org. It bears this copyright notice: Copyright 2000,

Re: [HACKERS] contrib/fuzzystrmatch/dmetaphone.c license

2015-02-24 Thread Dave Page
On Tue, Feb 24, 2015 at 12:24 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: contrib/fuzzystrmatch/dmetaphone.c says this: /* COPYRIGHT NOTICES *** Most of this code is directly from the Text::DoubleMetaphone perl module version 0.05

Re: [HACKERS] pg_dump gets attributes from tables in extensions

2015-02-24 Thread Alvaro Herrera
Michael Paquier wrote: On Tue, Feb 24, 2015 at 3:13 PM, Rushabh Lathia rushabh.lat...@gmail.com wrote: rushabh@rushabh-centos-vm:dump_test$ cat dump_test--1.0.sql /* dump_test/dump_test--1.0.sql */ Hm. I think it would be a good idea to collect these extension files somewhere so that

Re: [HACKERS] deparsing utility commands

2015-02-24 Thread Ryan Pedela
On Tue, Feb 24, 2015 at 6:48 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Stephen Frost wrote: I'm thinking something like SELECT * FROM pg_creation_commands({'pg_class'::regclass, 'sometable'::pg_class}); would return a set of commands in the JSON-blob format that creates the

Re: [HACKERS] Allow snapshot too old error, to prevent bloat

2015-02-24 Thread Kevin Grittner
Amit Kapila amit.kapil...@gmail.com wrote: Could you please explain in slightly more detail why can't it work if we use timestamp instead of snapshot-xmin in your patch in function TestForOldSnapshot()? It works fine for the additional visibility checking in scans, but it doesn't cover the

Re: [HACKERS] deparsing utility commands

2015-02-24 Thread Alvaro Herrera
Stephen Frost wrote: Regardless, as I've tried to point out above, the changes which I'm actually suggesting for this initial body of work are just to avoid the parsetree and go based off of what the catalog has. I'm hopeful that's a small enough and reasonable enough change to happen during

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

2015-02-24 Thread Andres Freund
On 2015-02-24 16:03:41 +0900, Michael Paquier wrote: Looking at this code, I think that it is really confusing to move the data related to the status of the backup block out of XLogRecordBlockImageHeader to the chunk ID itself that may *not* include a backup block at all as it is conditioned

Re: [HACKERS] Fillfactor for GIN indexes

2015-02-24 Thread Tomas Vondra
Hi, I've been wondering whether this might improve behavior with one of my workloads, suffering by GIN bloat - the same one I used to test GIN fastscan, for example. It's a batch process that loads a mailing list archive into a table with a GIN index on message body, by doing something like

Re: [HACKERS] deparsing utility commands

2015-02-24 Thread Andres Freund
On 2015-02-24 10:48:38 -0300, Alvaro Herrera wrote: Stephen Frost wrote: Regardless, as I've tried to point out above, the changes which I'm actually suggesting for this initial body of work are just to avoid the parsetree and go based off of what the catalog has. I'm hopeful that's a

Re: [HACKERS] Proposal : REINDEX xxx VERBOSE

2015-02-24 Thread Sawada Masahiko
On Fri, Feb 20, 2015 at 12:24 PM, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: Hello, I showed an extreme number of examples to include *almost of all* variations of existing syntax of option specification. And showed what if all variations could be used for all commands. It was

Re: [HACKERS] KNN-GiST with recheck

2015-02-24 Thread Tomas Vondra
Hi, On 17.2.2015 14:21, Alexander Korotkov wrote: On Sun, Feb 15, 2015 at 2:08 PM, Alexander Korotkov aekorot...@gmail.com mailto:aekorot...@gmail.com wrote: Revised patch with reordering in GiST is attached (knn-gist-recheck-in-gist.patch) as well as testing script (test.py). I meant to

Re: [HACKERS] Primary not sending to synchronous standby

2015-02-24 Thread Andres Freund
On 2015-02-23 17:53:59 +0100, Andres Freund wrote: On 2015-02-23 15:48:25 +, Thom Brown wrote: On 23 February 2015 at 15:42, Andres Freund and...@2ndquadrant.com wrote: On 2015-02-23 16:38:44 +0100, Andres Freund wrote: I unfortunately don't remember enough of the thread to

Re: [HACKERS] Auditing extension for PostgreSQL (Take 2)

2015-02-24 Thread David Steele
On 2/23/15 10:59 AM, David Steele wrote: On 2/17/15 10:34 AM, Stephen Frost wrote: There seems to be a number of places which are 'pgaudit' and a bunch that are 'pg_audit'. I'm guessing you were thinking 'pg_audit', but it'd be good to clean up and make them all consistent. Fixed, though I

Re: [HACKERS] Partitioning WIP patch (was: Partitioning: issues/ideas)

2015-02-24 Thread Corey Huinker
I think it's confusing to use BETWEEN to mean [low,high) when it already means [low,high] in WHERE clauses. Why not leverage range notation instead? CREATE TABLE parent_monthly_x_201401 PARTITION OF parent_monthly_x FOR VALUES IN RANGE '[2014-04-01,2014-05-01)' IN RANGE could easily be

Re: [HACKERS] PostgreSQL on z/OS UNIX?

2015-02-24 Thread Tom Lane
Gord Tomlin gord.tom...@sympatico.ca writes: I see that PostgreSQL will run on S/390 and S/390x processors, but I can find no mention of the z/OS operating system on the web site, in the documentation or in the mailing list archives. Has there been a known attempt to port PostgreSQL to z/OS

Re: [HACKERS] pg_basebackup fails with long tablespace paths

2015-02-24 Thread Peter Eisentraut
On 2/2/15 8:58 AM, Robert Haas wrote: I think we should commit this, where by this I mean your patch to error-check the length of filenames and symlinks instead of truncating them. done -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your

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

2015-02-24 Thread Heikki Linnakangas
On 02/20/2015 05:21 PM, Andres Freund wrote: In the attached patch I've merged compact/noncompact code, made aborts use similar logic to avoid including useless bytes and used both for the 2pc equivalents. +1 for this approach in general. To avoid using more space in the compact case the

Re: [HACKERS] pg_basebackup fails with long tablespace paths

2015-02-24 Thread Peter Eisentraut
On 1/23/15 3:26 AM, Abhijit Menon-Sen wrote: At 2014-12-24 08:10:46 -0500, pete...@gmx.net wrote: As a demo for how this might look, attached is a wildly incomplete patch to produce GNU long-link headers. Hi Peter. In what way exactly is this patch wildly incomplete? (I ask because it's

Re: [HACKERS] New CF app deployment

2015-02-24 Thread Peter Eisentraut
On 2/22/15 3:12 PM, Magnus Hagander wrote: Would you suggest removing the automated system completely, or keep it around and just make it possible to override it (either by removing the note that something is a patch, or by making something that's not listed as a patch become marked as such)?

Re: [HACKERS] Precedence of NOT LIKE, NOT BETWEEN, etc

2015-02-24 Thread Tom Lane
I wrote: I'm not seeing any terribly pleasing ways to fix this. Aside from the option of doing nothing, it seems like these are the choices: 1. We could hack base_yylex() to reduce NOT LIKE to a single token which could be given the same precedence as LIKE. Ditto for the other four cases.

Re: [HACKERS] OBJECT_ATTRIBUTE is useless (or: ALTER TYPE vs ALTER TABLE for composites)

2015-02-24 Thread Peter Eisentraut
On 2/23/15 2:01 PM, Alvaro Herrera wrote: I found that the OBJECT_ATTRIBUTE symbol is useless. I can just remove it and replace it with OBJECT_COLUMN, and everything continues to work; no test fails that I can find. It appears that it would change the command tag from ALTER TYPE to ALTER

Re: [HACKERS] OBJECT_ATTRIBUTE is useless (or: ALTER TYPE vs ALTER TABLE for composites)

2015-02-24 Thread Alvaro Herrera
Peter Eisentraut wrote: On 2/23/15 2:01 PM, Alvaro Herrera wrote: I found that the OBJECT_ATTRIBUTE symbol is useless. I can just remove it and replace it with OBJECT_COLUMN, and everything continues to work; no test fails that I can find. It appears that it would change the command tag

[HACKERS] PostgreSQL on z/OS UNIX?

2015-02-24 Thread Gord Tomlin
Hello, I see that PostgreSQL will run on S/390 and S/390x processors, but I can find no mention of the z/OS operating system on the web site, in the documentation or in the mailing list archives. Has there been a known attempt to port PostgreSQL to z/OS UNIX? Thanks, Gord Tomlin -- Sent

Re: [HACKERS] a fast bloat measurement tool (was Re: Measuring relation free space)

2015-02-24 Thread Jim Nasby
On 2/22/15 8:32 PM, Tomas Vondra wrote: On 23.2.2015 03:20, Jim Nasby wrote: On 2/22/15 5:41 PM, Tomas Vondra wrote: Otherwise, the code looks OK to me. Now, there are a few features I'd like to have for production use (to minimize the impact): 1) no index support:-( I'd like to see

Re: [HACKERS] Precedence of NOT LIKE, NOT BETWEEN, etc

2015-02-24 Thread Peter Eisentraut
On 2/23/15 3:08 PM, Tom Lane wrote: I thought of another possibility: 3. Leave everything as-is but mark the NOT-operator productions as having the precedence of NOT rather than of LIKE etc. This would change the behavior only for the NOT-LIKE-followed-by- example, and would make the two

Re: [HACKERS] mogrify and indent features for jsonb

2015-02-24 Thread Josh Berkus
Is there a way to take the json: '{a: 1, b: 2, c: {type: json, stuff: test}, d: [aa,bb,cc,dd]}' and add ee to d without replacing it? I can think of ways of currently doing it, but it's very convoluted just for pushing a value to an array. Can you think of a reasonable syntax for

Re: [HACKERS] Combining Aggregates

2015-02-24 Thread Peter Eisentraut
On 2/20/15 3:32 PM, Tomas Vondra wrote: That's just because the count is hidden there in an opaque custom transition function. If, say, we had instead an array of transition functions {inc, plus, plussq} and we knew that plus and plussq are associative operators, all we'd need to special

Re: [HACKERS] Combining Aggregates

2015-02-24 Thread Peter Eisentraut
On 2/20/15 3:09 PM, Tomas Vondra wrote: The 'combine' function gets two such 'state' values, while transition gets 'state' + next value. I think the combine function is not actually a property of the aggregate, but a property of the transition function. If two aggregates have the same

Re: [HACKERS] Enforce creation of destination folders for source files in pg_regress (Was: pg_regress writes into source tree)

2015-02-24 Thread Peter Eisentraut
On 2/23/15 1:27 AM, Michael Paquier wrote: I would like to have an extension in tree that also does this, so we have a regression test of this functionality. Sure. Here is one in the patch attached added as a test module. The name of the module is regress_dynamic. Perhaps the name could be

[HACKERS] Dereferenced pointer checks in data.c of ECPG

2015-02-24 Thread Michael Paquier
Hi all, ecpg_get_data@data.c is using to null-pointer checks for pval but it happens that we have the guarantee that those pointers are never NULL, see for example this piece of code at the code of ecpg_get_data(): /* pval is a pointer to the value */ if (!pval) {

Re: [HACKERS] Precedence of standard comparison operators

2015-02-24 Thread Tom Lane
Here's a completed patch for this. This includes fixing the NOT LIKE problem as discussed in the other thread. I've done more-or-less-exhaustive testing on this to verify that it produces warnings whenever necessary. It generates a few false-positive warnings in corner cases that seem too

Re: [HACKERS] Unable to build pg_rewind

2015-02-24 Thread Mark Kirkwood
On 25/02/15 12:47, Michael Paquier wrote: On Wed, Feb 25, 2015 at 8:03 AM, Mark Kirkwood mark.kirkw...@catalyst.net.nz mailto:mark.kirkw...@catalyst.net.nz wrote: On 25/02/15 11:06, Ratay, Steve wrote: I have checked out the pg_rewind code from

Re: [HACKERS] CATUPDATE confusion?

2015-02-24 Thread Michael Paquier
On Fri, Feb 20, 2015 at 8:08 AM, Adam Brightwell adam.brightw...@crunchydatasolutions.com wrote: Thanks for the review and feedback. One of the interesting behaviors (or perhaps not) is how 'pg_class_aclmask' handles an invalid role id when checking permissions against 'rolsuper' instead

Re: [HACKERS] Partitioning WIP patch (was: Partitioning: issues/ideas)

2015-02-24 Thread Amit Langote
On 25-02-2015 AM 01:13, Corey Huinker wrote: I think it's confusing to use BETWEEN to mean [low,high) when it already means [low,high] in WHERE clauses. Yeah, I'm not really attached to that syntax. Why not leverage range notation instead? CREATE TABLE parent_monthly_x_201401

Re: [HACKERS] PostgreSQL on z/OS UNIX?

2015-02-24 Thread Tom Lane
Gord Tomlin gord.tom...@sympatico.ca writes: z/OS UNIX does have certification as a UNIX system, but there are some quirks. The most common sources of problems when porting packages to z/OS UNIX are its use of EBCDIC, and autoconf problems. I guess it's time for some fail/rinse/repeat. Hmm

Re: [HACKERS] Unable to build pg_rewind

2015-02-24 Thread Mark Kirkwood
On 25/02/15 11:06, Ratay, Steve wrote: I have checked out the pg_rewind code from https://github.com/vmware/pg_rewind.git on the master branch and am using PostgreSQL 9.4.1 source code to build against. When I try to compile pg_rewind, I am getting the following errors. How can I resolve

Re: [HACKERS] Abbreviated keys for text cost model fix

2015-02-24 Thread Jeremy Harris
On 23/02/15 16:40, Tomas Vondra wrote: On 22.2.2015 22:30, Peter Geoghegan wrote: You should try it with the data fully sorted like this, but with one tiny difference: The very last tuple is out of order. How does that look? If this case is actually important, a merge-sort can take

Re: [HACKERS] Abbreviated keys for text cost model fix

2015-02-24 Thread Peter Geoghegan
On Tue, Feb 24, 2015 at 4:32 PM, Jeremy Harris j...@wizmail.org wrote: On 23/02/15 16:40, Tomas Vondra wrote: On 22.2.2015 22:30, Peter Geoghegan wrote: You should try it with the data fully sorted like this, but with one tiny difference: The very last tuple is out of order. How does that

Re: [HACKERS] A typo in backend/replication/README

2015-02-24 Thread Kyotaro HORIGUCHI
Hello, At Tue, 24 Feb 2015 14:44:17 +0200, Heikki Linnakangas hlinnakan...@vmware.com wrote in 54ec7221.5050...@vmware.com On 02/24/2015 12:02 PM, Kyotaro HORIGUCHI wrote: Hi, I found a trivial typo in backend/replication/README .. Thanks, fixed. .. Likewise, the release note for 8.3.2 at

Re: [HACKERS] pg_dump gets attributes from tables in extensions

2015-02-24 Thread Alvaro Herrera
Michael Paquier wrote: On Tue, Feb 24, 2015 at 11:55 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Maybe something in src/test/modules could keep these files so that pg_dump can be tested. Is anybody interested in doing that? For the patch to fix data dump of extensions that

Re: [HACKERS] pg_dump gets attributes from tables in extensions

2015-02-24 Thread Michael Paquier
On Wed, Feb 25, 2015 at 10:30 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Michael Paquier wrote: On Tue, Feb 24, 2015 at 11:55 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Maybe something in src/test/modules could keep these files so that pg_dump can be tested. Is

Re: [HACKERS] Precedence of NOT LIKE, NOT BETWEEN, etc

2015-02-24 Thread Tom Lane
Peter Eisentraut pete...@gmx.net writes: On 2/23/15 3:08 PM, Tom Lane wrote: I thought of another possibility: 3. Leave everything as-is but mark the NOT-operator productions as having the precedence of NOT rather than of LIKE etc. This would change the behavior only for the

Re: [HACKERS] Bug in pg_dump

2015-02-24 Thread Gilles Darold
Le 24/02/2015 05:40, Michael Paquier a écrit : On Tue, Feb 24, 2015 at 2:17 AM, Gilles Darold gilles.dar...@dalibo.com mailto:gilles.dar...@dalibo.com wrote: Looks great to me, I have tested with the postgis_topology extension everything works fine. Actually, after looking more

Re: [HACKERS] Unable to build pg_rewind

2015-02-24 Thread Michael Paquier
On Wed, Feb 25, 2015 at 8:03 AM, Mark Kirkwood mark.kirkw...@catalyst.net.nz wrote: On 25/02/15 11:06, Ratay, Steve wrote: I have checked out the pg_rewind code from https://github.com/vmware/pg_rewind.git on the master branch and am using PostgreSQL 9.4.1 source code to build against.

[HACKERS] Unable to build pg_rewind

2015-02-24 Thread Ratay, Steve
I have checked out the pg_rewind code from https://github.com/vmware/pg_rewind.git on the master branch and am using PostgreSQL 9.4.1 source code to build against. When I try to compile pg_rewind, I am getting the following errors. How can I resolve these problems? gcc -Wall

Re: [HACKERS] pg_dump gets attributes from tables in extensions

2015-02-24 Thread Alvaro Herrera
Michael Paquier wrote: On Wed, Feb 25, 2015 at 10:30 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: That looks interesting -- I don't recall seeing it. Does it support more doing than one extension? If so, we could certainly integrate it. It uses TAP tests to kick pg_dump commands,

Re: [HACKERS] PostgreSQL on z/OS UNIX?

2015-02-24 Thread Gavin Flower
On 25/02/15 11:12, Tom Lane wrote: Gord Tomlin gord.tom...@sympatico.ca writes: z/OS UNIX does have certification as a UNIX system, but there are some quirks. The most common sources of problems when porting packages to z/OS UNIX are its use of EBCDIC, and autoconf problems. I guess it's time

Re: [HACKERS] pg_dump gets attributes from tables in extensions

2015-02-24 Thread Michael Paquier
On Tue, Feb 24, 2015 at 11:55 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Michael Paquier wrote: On Tue, Feb 24, 2015 at 3:13 PM, Rushabh Lathia rushabh.lat...@gmail.com wrote: rushabh@rushabh-centos-vm:dump_test$ cat dump_test--1.0.sql /* dump_test/dump_test--1.0.sql */

Re: [HACKERS] Proposal : REINDEX xxx VERBOSE

2015-02-24 Thread Jim Nasby
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 suggesting '()' style options? (Anyway VACUUM gets the*third additional* option