Re: [HACKERS] postgresql latency bgwriter not doing its job

2014-08-28 Thread Fabien COELHO
Hello Aidan, If all you want is to avoid the write storms when fsyncs start happening on slow storage, can you not just adjust the kernel vm.dirty* tunables to start making the kernel write out dirty buffers much sooner instead of letting them accumulate until fsyncs force them out all at

Re: [HACKERS] Support for N synchronous standby servers

2014-08-28 Thread Michael Paquier
On Wed, Aug 27, 2014 at 2:46 PM, Rajeev rastogi rajeev.rast...@huawei.com wrote: I have done some more review, below are my comments: Thanks! 1. There are currently two loops on *num_sync, Can we simplify the function SyncRepGetSynchronousNodes by moving the priority calculation inside the

Re: [HACKERS] Specifying the unit in storage parameter

2014-08-28 Thread Fujii Masao
On Thu, Aug 28, 2014 at 12:55 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Wed, Aug 27, 2014 at 10:59 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Not necessarily, because it's harmless. It's there for purely aesthetical reasons, so it's your choice whether to add it or not.

Re: [HACKERS] Specifying the unit in storage parameter

2014-08-28 Thread Michael Paquier
On Thu, Aug 28, 2014 at 4:20 PM, Fujii Masao masao.fu...@gmail.com wrote: I don't have strong opinion about this. There are many tables which regression test creates but doesn't drop. But if you strongly think that the table must be dropped, I'm OK with that. This remark is just to limit the

Re: [HACKERS] SKIP LOCKED DATA (work in progress)

2014-08-28 Thread Thomas Munro
On 28 August 2014 00:25, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Thomas Munro wrote: Thanks, I hadn't seen this, I should have checked the archives better. I have actually already updated my patch to handle EvalPlanQualFetch with NOWAIT and SKIP LOCKED with isolation specs, see

Re: [HACKERS] Function to know last log write timestamp

2014-08-28 Thread Fujii Masao
On Thu, Aug 28, 2014 at 2:44 AM, Jim Nasby j...@nasby.net wrote: On 8/27/14, 7:33 AM, Fujii Masao wrote: On Tue, Aug 19, 2014 at 1:07 AM, Robert Haas robertmh...@gmail.com wrote: On Fri, Aug 15, 2014 at 7:17 AM, Fujii Masao masao.fu...@gmail.com wrote: On Fri, Aug 15, 2014 at 3:40 AM,

Re: [HACKERS] replication commands and log_statements

2014-08-28 Thread Fujii Masao
On Thu, Jun 19, 2014 at 5:29 PM, Ian Barwick i...@2ndquadrant.com wrote: On 12/06/14 20:37, Fujii Masao wrote: On Wed, Jun 11, 2014 at 11:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: Andres Freund and...@2ndquadrant.com writes: Your wish just seems like a separate feature to me. Including

Re: [HACKERS] delta relations in AFTER triggers

2014-08-28 Thread Marti Raudsepp
On Thu, Aug 28, 2014 at 12:03 AM, Kevin Grittner kgri...@ymail.com wrote: In essence, make the relations work like PL/pgSQL variables do. If you squint a little, the new/old relation is a variable from the function's point of view, and a parameter from the planner/executor's point of view.

[HACKERS] Why data of timestamptz does not store value of timezone passed to it?

2014-08-28 Thread rohtodeveloper
Hi,all I have a question about data type timestamp with time zone. Why data of timestamptz does not store value of timezone passed to it? Considering the following example. postgres=# select '2014-08-28 14:30:30.423602+02'::timestamp with time zone; timestamptz

Re: [HACKERS] inherit support for foreign tables

2014-08-28 Thread Etsuro Fujita
(2014/08/22 11:51), Noah Misch wrote: On Wed, Aug 20, 2014 at 08:11:01PM +0900, Etsuro Fujita wrote: (2014/07/02 11:23), Noah Misch wrote: Your chosen ANALYZE behavior is fair, but the messaging from a database-wide ANALYZE VERBOSE needs work: INFO: analyzing test_foreign_inherit.parent

Re: [HACKERS] Optimization for updating foreign tables in Postgres FDW

2014-08-28 Thread Etsuro Fujita
(2014/08/27 22:56), Robert Haas wrote: On Mon, Aug 25, 2014 at 8:58 AM, Albe Laurenz laurenz.a...@wien.gv.at wrote: Reading the code, I noticed that the pushed down UPDATE or DELETE statement is executed during postgresBeginForeignScan rather than during postgresIterateForeignScan. It probably

[HACKERS] v4 protocol TODO item - Lazy fetch/stream of TOASTed values?

2014-08-28 Thread Craig Ringer
Hi all Per the protocol todo: https://wiki.postgresql.org/wiki/Todo#Wire_Protocol_Changes do you think it's reasonable to allow for delayed sending of big varlena values and arrays in the protocol? The JDBC spec already has support for this in arrays, XML, and binary blobs, but AFAIK the

Re: [HACKERS] Add .NOTPARALLEL to contrib/Makefile

2014-08-28 Thread Andres Freund
On 2014-08-26 23:56:10 -0400, Peter Eisentraut wrote: On Tue, 2014-08-26 at 02:05 +0200, Andres Freund wrote: Currently running make -j16 all check in contrib/ results in a mess because all pg_regress invocations fight over the same port. Adding a simple .NOTPARALLEL: check-%-recurse

Re: [Fwd: Re: [HACKERS] proposal: new long psql parameter --on-error-stop]

2014-08-28 Thread Andres Freund
Hi, On 2014-08-27 22:48:54 +0200, Pavel Stehule wrote: Hi I chose \? xxx, because it is related to psql features. I wrote commands: \? options \? variables comments? Generall I like it. Some stuff I changed: * I rephrased the sgml changes * s/Printing options/Display options/. Or

Re: [HACKERS] Optimization for updating foreign tables in Postgres FDW

2014-08-28 Thread Etsuro Fujita
(2014/08/27 23:05), Tom Lane wrote: I wrote: Robert Haas robertmh...@gmail.com writes: Hmm, I'm worried that may be an API contract violation. Actually, there's another problem there. What of UPDATE or DELETE with a LIMIT clause, which is something that seems to be coming down the pike:

Re: [HACKERS] psql \watch versus \timing

2014-08-28 Thread Fujii Masao
On Tue, Aug 26, 2014 at 4:55 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 08/25/2014 10:48 PM, Heikki Linnakangas wrote: On 08/25/2014 09:22 PM, Fujii Masao wrote: On Tue, Aug 26, 2014 at 1:34 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: I agree that refactoring this

Re: [Fwd: Re: [HACKERS] proposal: new long psql parameter --on-error-stop]

2014-08-28 Thread Fujii Masao
On Thu, Aug 28, 2014 at 8:20 PM, Andres Freund and...@2ndquadrant.com wrote: Hi, On 2014-08-27 22:48:54 +0200, Pavel Stehule wrote: Hi I chose \? xxx, because it is related to psql features. I wrote commands: \? options \? variables comments? Generall I like it. Some stuff I

Re: [Fwd: Re: [HACKERS] proposal: new long psql parameter --on-error-stop]

2014-08-28 Thread Andres Freund
On 2014-08-28 13:20:07 +0200, Andres Freund wrote: I've attached a incremental patch. Apparently I didn't attach the patch, as so much a file containing the name of the patchfile... Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL

Re: [Fwd: Re: [HACKERS] proposal: new long psql parameter --on-error-stop]

2014-08-28 Thread Pavel Stehule
2014-08-28 13:20 GMT+02:00 Andres Freund and...@2ndquadrant.com: Hi, On 2014-08-27 22:48:54 +0200, Pavel Stehule wrote: Hi I chose \? xxx, because it is related to psql features. I wrote commands: \? options \? variables comments? Generall I like it. Some stuff I changed:

Re: [Fwd: Re: [HACKERS] proposal: new long psql parameter --on-error-stop]

2014-08-28 Thread Petr Jelinek
On 28/08/14 13:20, Andres Freund wrote: Hi, Stuff I wondered about: * How about making it --help=variables instead of --help-variables? -1, help is not a variable to be assigned imho * Do we really need the 'Report bugs to' blurb for --help-variables? Probably not. * Since we're not

Re: [Fwd: Re: [HACKERS] proposal: new long psql parameter --on-error-stop]

2014-08-28 Thread Fujii Masao
On Thu, Aug 28, 2014 at 5:48 AM, Pavel Stehule pavel.steh...@gmail.com wrote: comments? +fprintf(output, _( ECHO control what input is written to standard output [all, queries]\n)); The valid values in the help messages should be consistent with the values that the

Re: [HACKERS] Is this code safe?

2014-08-28 Thread Pavan Deolasee
On Thu, Aug 28, 2014 at 11:20 AM, Tom Lane t...@sss.pgh.pa.us wrote: Pavan Deolasee pavan.deola...@gmail.com writes: Can some kind of compiler optimisation reorder things such that the else if expression is evaluated using the old, uninitialised value of optval? Any such behavior is

Re: [HACKERS] re-reading SSL certificates during server reload

2014-08-28 Thread Magnus Hagander
On Thu, Aug 28, 2014 at 3:20 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, Aug 27, 2014 at 6:40 AM, Magnus Hagander mag...@hagander.net wrote: On Wed, Aug 27, 2014 at 11:56 AM, Alexey Klyukin al...@hintbits.com wrote: Greetings, Is there a strong reason to disallow reloading server key

Re: [Fwd: Re: [HACKERS] proposal: new long psql parameter --on-error-stop]

2014-08-28 Thread Pavel Stehule
2014-08-28 14:22 GMT+02:00 Fujii Masao masao.fu...@gmail.com: On Thu, Aug 28, 2014 at 5:48 AM, Pavel Stehule pavel.steh...@gmail.com wrote: comments? +fprintf(output, _( ECHO control what input is written to standard output [all, queries]\n)); The valid values in the

Re: [HACKERS] Escaping from blocked send() reprised.

2014-08-28 Thread Kyotaro HORIGUCHI
Hello, sorry for the dazed reply in the previous mail. I made revised patch for this issue. Attached patches are following, - 0001_Revise_socket_emulation_for_win32_backend.patch Revises socket emulation on win32 backend so that each socket can have its own blocking mode state. -

Re: [HACKERS] Selectivity estimation for inet operators

2014-08-28 Thread Heikki Linnakangas
On 08/26/2014 12:44 PM, Emre Hasegeli wrote: I agree with you that we can support other join type and anti join later, If others don’t have any objection in doing other parts later I will mark as Ready For Committer. I updated the patch to cover semi and anti joins with eqjoinsel_semi(). I

Re: [HACKERS] alter user set local_preload_libraries.

2014-08-28 Thread Kyotaro HORIGUCHI
Hello, On Thu, 2014-07-03 at 13:05 +0900, Kyotaro HORIGUCHI wrote: For the earlier than 9.4, no one seems to have considered seriously to use local_preload_library via ALTER statements so far. Only document fix would be enough for them. I think local_preload_libraries never actually

[HACKERS] Re: Why data of timestamptz does not store value of timezone passed to it?

2014-08-28 Thread David G Johnston
rohtodeveloper wrote I have a question about data type timestamp with time zone. Why data of timestamptz does not store value of timezone passed to it? The timezone of output(+08) is different with the original input value(+02). It seems not to be good behavior. Its good for the inumerable

Re: [HACKERS] Specifying the unit in storage parameter

2014-08-28 Thread Alvaro Herrera
Michael Paquier wrote: This remark is just to limit the amount of trash in the database used for regression tests. But then if we'd remove everything we would lack handy material for tests on utilities like database-wide thingies of the type VACUUM, REINDEX, pg_dump, etc. And we can just drop

Re: [HACKERS] re-reading SSL certificates during server reload

2014-08-28 Thread Tom Lane
Magnus Hagander mag...@hagander.net writes: On Thu, Aug 28, 2014 at 3:20 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, Aug 27, 2014 at 6:40 AM, Magnus Hagander mag...@hagander.net wrote: Key and cert files are loaded in the postmaster. We'd need to change that. Why? Hmm. That's

Re: [HACKERS] re-reading SSL certificates during server reload

2014-08-28 Thread Magnus Hagander
On Thu, Aug 28, 2014 at 4:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: On Thu, Aug 28, 2014 at 3:20 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, Aug 27, 2014 at 6:40 AM, Magnus Hagander mag...@hagander.net wrote: Key and cert files are loaded

Re: [HACKERS] re-reading SSL certificates during server reload

2014-08-28 Thread Tom Lane
Magnus Hagander mag...@hagander.net writes: On Thu, Aug 28, 2014 at 4:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Why would they need to be BACKEND, as opposed to just PGC_SIGHUP? I just thought semantically - because they do not change in a running backend. Any running backend will continue

Re: [HACKERS] [COMMITTERS] pgsql: Allow units to be specified in relation option setting value.

2014-08-28 Thread Tom Lane
Michael Paquier michael.paqu...@gmail.com writes: The patch attached fixes pg_upgrade by putting quotes when generating reloptions and it passes check-world. I imagine that having quotes by default in the value of reloptions in pg_class is the price to pay for supporting units. If this is not

Re: [HACKERS] re-reading SSL certificates during server reload

2014-08-28 Thread Andres Freund
On 2014-08-28 10:12:19 -0400, Tom Lane wrote: Magnus Hagander mag...@hagander.net writes: On Thu, Aug 28, 2014 at 4:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Why would they need to be BACKEND, as opposed to just PGC_SIGHUP? I just thought semantically - because they do not change in a

Re: [HACKERS] re-reading SSL certificates during server reload

2014-08-28 Thread Tom Lane
Andres Freund and...@2ndquadrant.com writes: On 2014-08-28 10:12:19 -0400, Tom Lane wrote: Hm. Yeah, I guess there is some use in holding onto the values that were actually used to initialize the current session, or at least there would be if we exposed the cert contents in any fashion.

Re: [HACKERS] re-reading SSL certificates during server reload

2014-08-28 Thread Magnus Hagander
On Thu, Aug 28, 2014 at 4:14 PM, Andres Freund and...@2ndquadrant.com wrote: On 2014-08-28 10:12:19 -0400, Tom Lane wrote: Magnus Hagander mag...@hagander.net writes: On Thu, Aug 28, 2014 at 4:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Why would they need to be BACKEND, as opposed to just

Re: [HACKERS] Audit of logout

2014-08-28 Thread Amit Kapila
On Wed, Aug 27, 2014 at 5:19 PM, Fujii Masao masao.fu...@gmail.com wrote: On Sat, Aug 23, 2014 at 3:44 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Tue, Aug 5, 2014 at 8:04 PM, Fujii Masao masao.fu...@gmail.com wrote: Changing PGC_SU_BACKEND parameter (log_connections) is visible even

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

2014-08-28 Thread Andreas Karlsson
On 08/28/2014 04:43 AM, Peter Geoghegan wrote: -- Nesting within wCTE: WITH t AS ( INSERT INTO z SELECT i, 'insert' FROM generate_series(0, 16) i ON CONFLICT UPDATE SET v = v || 'update' -- use of operators/functions in targetlist RETURNING * -- only projects inserted tuples,

Re: [HACKERS] re-reading SSL certificates during server reload

2014-08-28 Thread Andres Freund
On 2014-08-28 10:20:08 -0400, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On 2014-08-28 10:12:19 -0400, Tom Lane wrote: Hm. Yeah, I guess there is some use in holding onto the values that were actually used to initialize the current session, or at least there would be if

Re: [HACKERS] re-reading SSL certificates during server reload

2014-08-28 Thread Tom Lane
Andres Freund and...@2ndquadrant.com writes: On 2014-08-28 10:20:08 -0400, Tom Lane wrote: Having said that, there's a nearby thread about inventing a SUBACKEND GUC category, and that's likely what we'd really want to use here, just on the grounds that superusers would know better. What we

Re: [HACKERS] re-reading SSL certificates during server reload

2014-08-28 Thread Andres Freund
On 2014-08-28 10:30:30 -0400, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On 2014-08-28 10:20:08 -0400, Tom Lane wrote: Having said that, there's a nearby thread about inventing a SUBACKEND GUC category, and that's likely what we'd really want to use here, just on the

Re: [Fwd: Re: [HACKERS] proposal: new long psql parameter --on-error-stop]

2014-08-28 Thread Andres Freund
On 2014-08-28 14:04:27 +0200, Petr Jelinek wrote: On 28/08/14 13:20, Andres Freund wrote: Hi, Stuff I wondered about: * How about making it --help=variables instead of --help-variables? -1, help is not a variable to be assigned imho I don't think variable assignment is a good mental

Re: [HACKERS] postgresql latency bgwriter not doing its job

2014-08-28 Thread Claudio Freire
On Thu, Aug 28, 2014 at 3:27 AM, Fabien COELHO coe...@cri.ensmp.fr wrote: Hello Aidan, If all you want is to avoid the write storms when fsyncs start happening on slow storage, can you not just adjust the kernel vm.dirty* tunables to start making the kernel write out dirty buffers much

Re: [HACKERS] Need Multixact Freezing Docs

2014-08-28 Thread Alvaro Herrera
Josh Berkus wrote: On 04/16/2014 01:30 PM, Alvaro Herrera wrote: Josh Berkus wrote: You can see the current multixact value in pg_controldata output. Keep timestamped values of that somewhere (a table?) so that you can measure consumption rate. I don't think we provide SQL-level

Re: [HACKERS] Per table autovacuum vacuum cost limit behaviour strange

2014-08-28 Thread Robert Haas
On Tue, Aug 26, 2014 at 12:19 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: On Mon, May 5, 2014 at 11:57 AM, Mark Kirkwood mark.kirkw...@catalyst.net.nz wrote: I could think of 2 ways to change this: a. if user has specified cost_limit value for table, then it just uses it rather

[HACKERS] Re: [COMMITTERS] pgsql: Allow units to be specified in relation option setting value.

2014-08-28 Thread Fujii Masao
On Thu, Aug 28, 2014 at 11:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: Michael Paquier michael.paqu...@gmail.com writes: The patch attached fixes pg_upgrade by putting quotes when generating reloptions and it passes check-world. I imagine that having quotes by default in the value of reloptions

Re: [HACKERS] implement subject alternative names support for SSL connections

2014-08-28 Thread Alexey Klyukin
On Mon, Aug 25, 2014 at 12:02 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 08/24/2014 03:11 PM, Alexey Klyukin wrote: On Wed, Aug 20, 2014 at 11:53 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: The patch doesn't seem to support wildcards in alternative names. Is

[HACKERS] Re: [COMMITTERS] pgsql: Allow units to be specified in relation option setting value.

2014-08-28 Thread Robert Haas
On Thu, Aug 28, 2014 at 12:22 PM, Fujii Masao masao.fu...@gmail.com wrote: On Thu, Aug 28, 2014 at 11:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: Michael Paquier michael.paqu...@gmail.com writes: The patch attached fixes pg_upgrade by putting quotes when generating reloptions and it passes

Re: [HACKERS] Need Multixact Freezing Docs

2014-08-28 Thread Josh Berkus
On 08/28/2014 09:09 AM, Alvaro Herrera wrote: Josh Berkus wrote: On 04/16/2014 01:30 PM, Alvaro Herrera wrote: Josh Berkus wrote: You can see the current multixact value in pg_controldata output. Keep timestamped values of that somewhere (a table?) so that you can measure consumption rate.

Re: [HACKERS] implement subject alternative names support for SSL connections

2014-08-28 Thread Alexey Klyukin
On Mon, Aug 25, 2014 at 12:33 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 08/25/2014 01:07 PM, Andres Freund wrote: On 2014-08-25 13:02:50 +0300, Heikki Linnakangas wrote: But actually, I wonder if we should delegate the whole hostname matching to OpenSSL? There's a function

Re: [HACKERS] Switch pg_basebackup to use -X stream instead of -X fetch by default?

2014-08-28 Thread Robert Haas
On Wed, Aug 27, 2014 at 2:55 AM, Magnus Hagander mag...@hagander.net wrote: On Wed, Aug 27, 2014 at 5:16 AM, Fujii Masao masao.fu...@gmail.com wrote: On Wed, Aug 27, 2014 at 6:16 AM, Magnus Hagander mag...@hagander.net wrote: On Tue, Aug 26, 2014 at 10:46 PM, Andres Freund

Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?

2014-08-28 Thread Steve Crawford
On 08/28/2014 01:51 AM, rohtodeveloper wrote: Hi,all I have a question about data type timestamp with time zone. Why data of timestamptz does not store value of timezone passed to it? Considering the following example. postgres=# select '2014-08-28 14:30:30.423602+02'::timestamp with time

Re: [HACKERS] Per table autovacuum vacuum cost limit behaviour strange

2014-08-28 Thread Alvaro Herrera
Robert Haas wrote: Now, in the case where you are setting an overall limit, there is at least an argument to be made that you can determine the overall rate of autovacuum-induced I/O activity that the system can tolerate, and set your limits to stay within that budget, and then let the system

Re: [HACKERS] Missing plpgsql.o Symbols on OS X

2014-08-28 Thread David E. Wheeler
On Aug 27, 2014, at 9:53 PM, Ashesh Vashi ashesh.va...@enterprisedb.com wrote: Please add -arch x86_64 to your LD_FLAGS and CFLAGS in your make file. This made no difference: LDFLAGS = -arch x86_64 CFLAGS = -arch x86_64 Best, David signature.asc Description: Message signed with

Re: [HACKERS] Missing plpgsql.o Symbols on OS X

2014-08-28 Thread David E. Wheeler
On Aug 27, 2014, at 4:20 PM, Tom Lane t...@sss.pgh.pa.us wrote: Yeah, but plpgsql.so is mentioned nowhere on your command line. I'm not too sure about the dynamic-linking rules on OS X, but I'd not be surprised if you need to provide a reference to plpgsql.so in its final installed location

Re: [HACKERS] postgresql latency bgwriter not doing its job

2014-08-28 Thread Fabien COELHO
I tried that by setting: vm.dirty_expire_centisecs = 100 vm.dirty_writeback_centisecs = 100 So it should start writing returned buffers at most 2s after they are returned, if I understood the doc correctly, instead of at most 35s. The result is that with a 5000s 25tps pretty small load

Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?

2014-08-28 Thread Kevin Grittner
Steve Crawford scrawf...@pinpointresearch.com wrote: I have always considered timestamp with time zone to be a bad description of that data type but it appears to be a carryover from the specs. It is really a point in time I agree.  While what timestamptz implements is a very useful data

Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?

2014-08-28 Thread Pavel Stehule
2014-08-28 20:26 GMT+02:00 Kevin Grittner kgri...@ymail.com: Steve Crawford scrawf...@pinpointresearch.com wrote: I have always considered timestamp with time zone to be a bad description of that data type but it appears to be a carryover from the specs. It is really a point in time I

Re: [HACKERS] Similar to csvlog but not really, json logs?

2014-08-28 Thread Josh Berkus
On 08/27/2014 09:53 AM, Andres Freund wrote: Perhaps instead of doing this in-core it would be better to make log handling more extensible? I'm thinking add a specific binary format and an external tool that can parse that and do whatever the user wants with it. That means we don't have

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

2014-08-28 Thread Peter Geoghegan
On Thu, Aug 28, 2014 at 7:29 AM, Andreas Karlsson andr...@proxel.se wrote: Personally I would find it surprising if RETURNING did not also return the updated tuples. In many use cases for upsert the user does not care if the row was new or not. I'm not attached to that particular behavior, but

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

2014-08-28 Thread Andreas Karlsson
On 08/28/2014 09:05 PM, Peter Geoghegan wrote: On Thu, Aug 28, 2014 at 7:29 AM, Andreas Karlsson andr...@proxel.se wrote: Personally I would find it surprising if RETURNING did not also return the updated tuples. In many use cases for upsert the user does not care if the row was new or not.

Re: [HACKERS] Per table autovacuum vacuum cost limit behaviour strange

2014-08-28 Thread Robert Haas
On Thu, Aug 28, 2014 at 1:36 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Robert Haas wrote: Now, in the case where you are setting an overall limit, there is at least an argument to be made that you can determine the overall rate of autovacuum-induced I/O activity that the system can

Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?

2014-08-28 Thread Bruce Momjian
On Thu, Aug 28, 2014 at 11:26:53AM -0700, Kevin Grittner wrote: Steve Crawford scrawf...@pinpointresearch.com wrote: I have always considered timestamp with time zone to be a bad description of that data type but it appears to be a carryover from the specs. It is really a point in time

Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?

2014-08-28 Thread k...@rice.edu
On Thu, Aug 28, 2014 at 03:33:56PM -0400, Bruce Momjian wrote: On Thu, Aug 28, 2014 at 11:26:53AM -0700, Kevin Grittner wrote: Steve Crawford scrawf...@pinpointresearch.com wrote: I have always considered timestamp with time zone to be a bad description of that data type but it appears

Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?

2014-08-28 Thread Tom Lane
k...@rice.edu k...@rice.edu writes: On Thu, Aug 28, 2014 at 03:33:56PM -0400, Bruce Momjian wrote: So the standard requires storing of original timezone in the data type? I was not aware of that. I do not have a copy of the SQL 92 spec, but several references to the spec mention that it

Re: [HACKERS] Per table autovacuum vacuum cost limit behaviour strange

2014-08-28 Thread Alvaro Herrera
Robert Haas wrote: I agree that you might not like that. But you might not like having the table vacuumed slower than the configured rate, either. My impression is that the time between vacuums isn't really all that negotiable for some people. I had one customer who had horrible bloat

Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?

2014-08-28 Thread Kevin Grittner
k...@rice.edu k...@rice.edu wrote: On Thu, Aug 28, 2014 at 03:33:56PM -0400, Bruce Momjian wrote: So the standard requires storing of original timezone in the data type?  I was not aware of that. I do not have a copy of the SQL 92 spec, but several references to the spec mention that it

Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?

2014-08-28 Thread Alvaro Herrera
Kevin Grittner wrote: I just took a quick look at the spec to refresh my memory, and it seems to require that the WITH TIME ZONE types store UTC (I suppose for fast comparisons), it requires the time zone in the form of a hour:minute offset to be stored with it, so you can determine the

Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?

2014-08-28 Thread Kevin Grittner
Alvaro Herrera alvhe...@2ndquadrant.com wrote: Kevin Grittner wrote: I just took a quick look at the spec to refresh my memory, and it seems to require that the WITH TIME ZONE types store UTC (I suppose for fast comparisons), it requires the time zone in the form of a hour:minute offset to

Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?

2014-08-28 Thread Alvaro Herrera
Kevin Grittner wrote: But the standard doesn't say anything about storing a time zone *name* or *abbreviation* -- it requires that it be stored as UTC with the *offset* (in hours and minutes).  That makes it pretty close to what we have -- it's all about a difference in presentation.  And as

[HACKERS] Multithreaded SIGPIPE race in libpq on Solaris

2014-08-28 Thread Thomas Munro
Hello, A while back someone showed me a program blocking in libpq 9.2 on Solaris 11, inside sigwait called by pq_reset_sigpipe. It had happened a couple of times before during a period of instability/crashing with a particular DB (a commercial PostgreSQL derivative, but the client was using

Re: [HACKERS] Per table autovacuum vacuum cost limit behaviour strange

2014-08-28 Thread Robert Haas
On Thu, Aug 28, 2014 at 4:56 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Robert Haas wrote: I agree that you might not like that. But you might not like having the table vacuumed slower than the configured rate, either. My impression is that the time between vacuums isn't really all

Re: [HACKERS] 9.5: Memory-bounded HashAgg

2014-08-28 Thread Tomas Vondra
On 26.8.2014 21:38, Jeff Davis wrote: On Tue, 2014-08-26 at 12:39 +0300, Heikki Linnakangas wrote: I think this is enough for this commitfest - we have consensus on the design. For the next one, please address those open items, and resubmit. Agreed, return with feedback. I need to get

Re: [HACKERS] Function to know last log write timestamp

2014-08-28 Thread Robert Haas
On Thu, Aug 28, 2014 at 3:34 AM, Fujii Masao masao.fu...@gmail.com wrote: Theoretically it's not safe without a barrier on a machine with weak memory ordering. No? Why not? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers

Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?

2014-08-28 Thread Kevin Grittner
Alvaro Herrera alvhe...@2ndquadrant.com wrote: Kevin Grittner wrote: But the standard doesn't say anything about storing a time zone *name* or *abbreviation* -- it requires that it be stored as UTC with the *offset* (in hours and minutes).  That makes it pretty close to what we have -- it's

Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?

2014-08-28 Thread Josh Berkus
On 08/28/2014 02:25 PM, Kevin Grittner wrote: But the standard doesn't say anything about storing a time zone *name* or *abbreviation* -- it requires that it be stored as UTC with the *offset* (in hours and minutes). That makes it pretty close to what we have -- it's all about a difference in

Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?

2014-08-28 Thread Tom Lane
Kevin Grittner kgri...@ymail.com writes: But the standard doesn't say anything about storing a time zone *name* or *abbreviation* -- it requires that it be stored as UTC with the *offset* (in hours and minutes).  That makes it pretty close to what we have -- it's all about a difference in

Re: [HACKERS] Multithreaded SIGPIPE race in libpq on Solaris

2014-08-28 Thread Tom Lane
Thomas Munro mu...@ip9.org writes: My theory is that if two connections accessed by different threads get shut down around the same time, there is a race scenario where each of them fails to write to its socket, sees errno == EPIPE and then sees a pending SIGPIPE with sigpending(), but only

Re: [HACKERS] [BUGS] BUG #10823: Better REINDEX syntax.

2014-08-28 Thread Alvaro Herrera
Vik Fearing wrote: Here are two patches for this. The first one, reindex_user_tables.v1.patch, implements the variant that only hits user tables, as suggested by you. The second one, reindex_no_dbname.v1.patch, allows the three database-wide variants to omit the database name (voted for

Re: [HACKERS] Multithreaded SIGPIPE race in libpq on Solaris

2014-08-28 Thread Thomas Munro
On 28 August 2014 23:45, Tom Lane t...@sss.pgh.pa.us wrote: I don't claim to be an expert on this stuff, but I had the idea that multithreaded environments were supposed to track signal state per-thread not just per-process, precisely because of issues like this. After some googling, I found

Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?

2014-08-28 Thread Bruce Momjian
On Thu, Aug 28, 2014 at 03:25:49PM -0700, Josh Berkus wrote: On 08/28/2014 02:25 PM, Kevin Grittner wrote: But the standard doesn't say anything about storing a time zone *name* or *abbreviation* -- it requires that it be stored as UTC with the *offset* (in hours and minutes). That makes

[HACKERS] PATCH: Allow distdir to be overridden on make command line

2014-08-28 Thread Craig Ringer
Not just a one line patch, a one character patch. Use ?= instead of = in distdir assignment, so it can be overridden on the command line when building dist tarballs with patches. Yes, you can just modify GNUMakefile.in, but that's extra noise in a diff, adds merge conflicts, etc. Please apply.

Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?

2014-08-28 Thread Craig Ringer
On 08/29/2014 04:59 AM, Kevin Grittner wrote: I just took a quick look at the spec to refresh my memory, and it seems to require that the WITH TIME ZONE types store UTC (I suppose for fast comparisons), it requires the time zone in the form of a hour:minute offset to be stored with it, so you

Re: [HACKERS] possible optimization: push down aggregates

2014-08-28 Thread Craig Ringer
On 08/28/2014 03:46 AM, Claudio Freire wrote: You can't with mean and stddev, only with associative aggregates. That's min, max, sum, bit_and, bit_or, bool_and, bool_or, count. You could with a new helper function to merge the temporary states for each scan though. In the case of mean, for

Re: [HACKERS] [BUGS] BUG #9652: inet types don't support min/max

2014-08-28 Thread Tom Lane
Haribabu Kommi kommi.harib...@gmail.com writes: Thanks for your review. Please find the rebased patch to latest HEAD. Committed with minor (mostly cosmetic) alterations. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make

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

2014-08-28 Thread Peter Geoghegan
On Wed, Aug 27, 2014 at 7:43 PM, Peter Geoghegan p...@heroku.com wrote: There are some restrictions on what this auxiliary update may do, but FWIW there are considerably fewer than those that the equivalent MySQL or SQLite feature imposes on their users. I realized that I missed a few cases

Re: [HACKERS] Optimization for updating foreign tables in Postgres FDW

2014-08-28 Thread Etsuro Fujita
(2014/08/13 12:40), Etsuro Fujita wrote: (2014/08/12 18:34), Shigeru Hanada wrote: Issues addressed by Eitoku-san were fixed properly, but he found a bug and a possible enhancement in the v2 patch. * push-down check misses delete triggers update_is_pushdown_safe() seems to have a bug that

Re: [HACKERS] Optimization for updating foreign tables in Postgres FDW

2014-08-28 Thread Etsuro Fujita
(2014/08/26 12:20), Etsuro Fujita wrote: (2014/08/25 21:58), Albe Laurenz wrote: I played with it, and apart from Hanada's comments I have found the following: test= EXPLAIN (ANALYZE, VERBOSE) UPDATE rtest SET val=NULL WHERE id 3;

Re: [HACKERS] Per table autovacuum vacuum cost limit behaviour strange

2014-08-28 Thread Mark Kirkwood
On 29/08/14 08:56, Alvaro Herrera wrote: Robert Haas wrote: I agree that you might not like that. But you might not like having the table vacuumed slower than the configured rate, either. My impression is that the time between vacuums isn't really all that negotiable for some people. I had