Re: [HACKERS] Re: PostgreSql - access modified rows in prepare transaction command

2013-02-21 Thread Heikki Linnakangas
On 20.02.2013 13:39, pierpaolo.cincilla wrote: Thank you Heikki for your reply. As you suggest, I will explain better what I'm trying to accomplish. What I'm writing a ditributed two-phase-commit termination protocol that work in this manner: 1) Each site has a replica of the database. A site

Re: [HACKERS] Materialized views WIP patch

2013-02-21 Thread Tom Lane
Greg Stark st...@mit.edu writes: The way I was thinking about it, whatever the command is named, you might be able to tell the database to drop the storage associated with the view but that would make the view invalid until it was refreshed. It wouldn't make it appear to be empty. Actually,

Re: [HACKERS] Unarchived WALs deleted after crash

2013-02-21 Thread Heikki Linnakangas
On 21.02.2013 02:59, Daniel Farina wrote: On Fri, Feb 15, 2013 at 9:29 AM, Simon Riggssi...@2ndquadrant.com wrote: On 15 February 2013 17:07, Heikki Linnakangashlinnakan...@vmware.com wrote: Unfortunately in HEAD, xxx.done file is not created when restoring archived file because of absence

Re: [HACKERS] Unarchived WALs deleted after crash

2013-02-21 Thread Daniel Farina
On Thu, Feb 21, 2013 at 12:39 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 21.02.2013 02:59, Daniel Farina wrote: On Fri, Feb 15, 2013 at 9:29 AM, Simon Riggssi...@2ndquadrant.com wrote: On 15 February 2013 17:07, Heikki Linnakangashlinnakan...@vmware.com wrote: Unfortunately

Re: [HACKERS] Materialized views WIP patch

2013-02-21 Thread Andres Freund
On 2013-02-21 04:14:09 +, Greg Stark wrote: On Wed, Feb 20, 2013 at 9:25 PM, Peter Eisentraut pete...@gmx.net wrote: More generally, I would consider the invalidation of a materialized view a DDL command, whereas truncating a table is a DML command. That's not entirely true. From the

Re: [HACKERS] FDW for PostgreSQL

2013-02-21 Thread Tom Lane
Shigeru Hanada shigeru.han...@gmail.com writes: [ postgres_fdw.v5.patch ] Applied with a lot of revisions. There are still a number of loose ends and things that need to be discussed: I'm not at all happy with the planner support functions --- as designed, that code is basically incapable of

Re: [HACKERS] posix_fadvise missing in the walsender

2013-02-21 Thread Robert Haas
On Wed, Feb 20, 2013 at 9:49 PM, Joachim Wieland j...@mcknight.de wrote: So given the above, I think it's possible to come up with benchmarks that prove whatever you want to prove :-) Yeah. :-( -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company --

Re: [HACKERS] Materialized views WIP patch

2013-02-21 Thread Robert Haas
On Wed, Feb 20, 2013 at 11:14 PM, Greg Stark st...@mit.edu wrote: On Wed, Feb 20, 2013 at 9:25 PM, Peter Eisentraut pete...@gmx.net wrote: More generally, I would consider the invalidation of a materialized view a DDL command, whereas truncating a table is a DML command. That's not entirely

Re: [HACKERS] Materialized views WIP patch

2013-02-21 Thread Peter Eisentraut
On 2/20/13 5:03 PM, Kevin Grittner wrote: Peter Eisentraut pete...@gmx.net wrote: On 2/20/13 2:30 PM, Kevin Grittner wrote: Are there TRUNCATE triggers on materialized views? No. Nor SELECT, INSERT, UPDATE, or DELETE triggers. You can't create a trigger of any type on a materialized view.

Re: [HACKERS] Materialized views WIP patch

2013-02-21 Thread Peter Eisentraut
On 2/20/13 11:14 PM, Greg Stark wrote: That's not entirely true. From the database's point of view, TRUNCATE is in many ways actually DDL. Whether something is DDL or DML or a read operation (query) is not an implementation detail, it's a user-exposed category. Since TRUNCATE is logically

Re: [HACKERS] 9.2.3 crashes during archive recovery

2013-02-21 Thread Heikki Linnakangas
On 15.02.2013 15:49, Heikki Linnakangas wrote: Attached is a patch for git master. The basic idea is to split InArchiveRecovery into two variables, InArchiveRecovery and ArchiveRecoveryRequested. ArchiveRecoveryRequested is set when recovery.conf exists. But if we don't know how far we need to

Re: [HACKERS] [PATCH] Add PQconninfoParseParams and PQconninfodefaultsMerge to libpq

2013-02-21 Thread Amit Kapila
On Thursday, February 21, 2013 12:46 AM Heikki Linnakangas wrote: On 20.02.2013 11:42, Amit Kapila wrote: The patch for providing connection string for pg_basebackup, pg_receivexlog, pg_dump and pg_dumpall is attached with this mail. Thanks. Now that I look at this patch, I realize that we

Re: [HACKERS] FDW for PostgreSQL

2013-02-21 Thread Albe Laurenz
Tom Lane wrote: Applied with a lot of revisions. I am thrilled. As I mentioned earlier, I think it would be better to force the remote session's search_path setting to just pg_catalog and then reduce the amount of explicit schema naming in the queries --- any opinions about that? I think

Re: [HACKERS] Materialized views WIP patch

2013-02-21 Thread Kevin Grittner
Peter Eisentraut pete...@gmx.net wrote: On 2/20/13 11:14 PM, Greg Stark wrote: That's not entirely true. From the database's point of view, TRUNCATE is in many ways actually DDL. Whether something is DDL or DML or a read operation (query) is not an implementation detail, it's a user-exposed

Re: [HACKERS] FDW for PostgreSQL

2013-02-21 Thread Andres Freund
On 2013-02-21 14:23:35 +, Albe Laurenz wrote: Tom Lane wrote: Another thing I was wondering about, but did not change, is that if we're having the remote transaction inherit the local transaction's isolation level, shouldn't it inherit the READ ONLY property as well? That seems to me

Re: [HACKERS] Materialized views WIP patch

2013-02-21 Thread Kevin Grittner
Tom Lane t...@sss.pgh.pa.us wrote: Greg Stark st...@mit.edu writes: The way I was thinking about it, whatever the command is named, you might be able to tell the database to drop the storage associated with the view but that would make the view invalid until it was refreshed. It wouldn't make

Re: [HACKERS] Materialized views WIP patch

2013-02-21 Thread Kevin Grittner
Greg Stark st...@mit.edu wrote: I actually don't really dislike using TRUNCATE for this command.  I was more asking about whether this meant people were thinking of the view as a thing where you could control the data in it by hand and could have the view be empty rather than just not valid.

Re: [HACKERS] FDW for PostgreSQL

2013-02-21 Thread Tom Lane
Albe Laurenz laurenz.a...@wien.gv.at writes: Tom Lane wrote: As I mentioned earlier, I think it would be better to force the remote session's search_path setting to just pg_catalog and then reduce the amount of explicit schema naming in the queries --- any opinions about that? I think that

Re: [HACKERS] FDW for PostgreSQL

2013-02-21 Thread Tom Lane
Andres Freund and...@2ndquadrant.com writes: On 2013-02-21 14:23:35 +, Albe Laurenz wrote: Tom Lane wrote: Another thing I was wondering about, but did not change, is that if we're having the remote transaction inherit the local transaction's isolation level, shouldn't it inherit the READ

Re: [HACKERS] FDW for PostgreSQL

2013-02-21 Thread Andres Freund
On 2013-02-21 09:58:57 -0500, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On 2013-02-21 14:23:35 +, Albe Laurenz wrote: Tom Lane wrote: Another thing I was wondering about, but did not change, is that if we're having the remote transaction inherit the local

Re: [HACKERS] Materialized views WIP patch

2013-02-21 Thread Kevin Grittner
Peter Eisentraut pete...@gmx.net wrote: I'll need more time to ponder your other points, but... You don't need a new command or key word for this.  How about ALTER MATERIALIZED VIEW DISCARD? I don't like this because we don't have ALTER TABLE REINDEX.  But the fact that DISCARD is a keyword

Re: [HACKERS] Materialized views WIP patch

2013-02-21 Thread Heikki Linnakangas
On 21.02.2013 16:38, Kevin Grittner wrote: Tom Lanet...@sss.pgh.pa.us wrote: Greg Starkst...@mit.edu writes: The way I was thinking about it, whatever the command is named, you might be able to tell the database to drop the storage associated with the view but that would make the view

Re: [HACKERS] Materialized views WIP patch

2013-02-21 Thread Kevin Grittner
Andres Freund and...@2ndquadrant.com wrote: giving an error so its an easy to find distinction to a normal table seems like a good idea. I'm not sure I understood your concerns entirely, but wonder whether this helps?: test=# \d   List of relations  Schema | Name  |   Type  

Re: [HACKERS] Materialized views WIP patch

2013-02-21 Thread Tom Lane
Kevin Grittner kgri...@ymail.com writes: Tom Lane t...@sss.pgh.pa.us wrote: That being the case, lumping them as being the same operation feels like the wrong thing, and so we should choose a different name for the MV operation. There is currently no truncation of MV data without rendering

Re: [HACKERS] Materialized views WIP patch

2013-02-21 Thread Kevin Grittner
Heikki Linnakangas hlinnakan...@vmware.com wrote: On 21.02.2013 16:38, Kevin Grittner wrote: Tom Lanet...@sss.pgh.pa.us  wrote: Greg Starkst...@mit.edu  writes: The way I was thinking about it, whatever the command is named, you might be able to tell the database to drop the storage

Re: [HACKERS] Materialized views WIP patch

2013-02-21 Thread Andres Freund
On 2013-02-21 07:10:09 -0800, Kevin Grittner wrote: Andres Freund and...@2ndquadrant.com wrote: giving an error so its an easy to find distinction to a normal table seems like a good idea. I'm not sure I understood your concerns entirely, but wonder whether this helps?: To explain it a

Re: [HACKERS] FDW for PostgreSQL

2013-02-21 Thread Tom Lane
Andres Freund and...@2ndquadrant.com writes: On 2013-02-21 09:58:57 -0500, Tom Lane wrote: How exactly would it do that via an FDW? Surely if the user tries to execute INSERT/UPDATE/DELETE against a foreign table, the command would get rejected in a read-only transaction, long before we even

Re: [HACKERS] Materialized views WIP patch

2013-02-21 Thread Kevin Grittner
Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kgri...@ymail.com writes: Tom Lane t...@sss.pgh.pa.us wrote: That being the case, lumping them as being the same operation feels like the wrong thing, and so we should choose a different name for the MV operation. There is currently no

Re: [HACKERS] FDW for PostgreSQL

2013-02-21 Thread Andres Freund
On 2013-02-21 10:21:34 -0500, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On 2013-02-21 09:58:57 -0500, Tom Lane wrote: How exactly would it do that via an FDW? Surely if the user tries to execute INSERT/UPDATE/DELETE against a foreign table, the command would get

Re: [HACKERS] FDW for PostgreSQL

2013-02-21 Thread Tom Lane
Andres Freund and...@2ndquadrant.com writes: Sure, it might fail if you use READ ONLY explicitly. Or the code might check it. The point is that one might not have choice about the READ ONLY state of the local transaction if its a HS standby as all transactions are READ ONLY there. [ shrug...

Re: [HACKERS] Materialized views WIP patch

2013-02-21 Thread Kevin Grittner
Andres Freund and...@2ndquadrant.com wrote: I assume that at some point matviews will get (auto-)updateable, just as normal views recently got. I'm dubious about that.  Every use case I've seen for MVs involves aggregation, although they are a generalized feature, so that won't always be true. 

Re: [HACKERS] FDW for PostgreSQL

2013-02-21 Thread Albe Laurenz
Tom Lane wrote: I think that the question what to push down is a different question from checking column data types, because there we can rely on the type input functions to reject bad values. Unfortunately, that's a very myopic view of the situation: there are many cases where datatype

Re: [HACKERS] Materialized views WIP patch

2013-02-21 Thread Tom Lane
Kevin Grittner kgri...@ymail.com writes: Tom Lane t...@sss.pgh.pa.us wrote: Peter claimed upthread that REFRESH is a subcommand of ALTER MATERIALIZE VIEW It's not, nor do I think it should be. Oh, never mind then. and that this operation should be another one.  That sounds pretty

Re: [HACKERS] JSON Function Bike Shedding

2013-02-21 Thread Merlin Moncure
On Wed, Feb 20, 2013 at 9:42 AM, Robert Haas robertmh...@gmail.com wrote: On Tue, Feb 19, 2013 at 10:00 AM, Merlin Moncure mmonc...@gmail.com wrote: Anyways, as to overloading in general, well, SQL is heavily overloaded. We don't have int_max, float_max, etc. and it would be usability

Re: [HACKERS] is it bug? - printing boolean domains in sql/xml function

2013-02-21 Thread Pavel Stehule
Hello here is patch where it should be pushed - 9.3 or 9.4 ? I vote 9.3 - I know a users, that should to do workarounds (he should not to use domains) now, so early is better. And this patch is one line size patch Regards Pavel 2013/2/16 Pavel Stehule pavel.steh...@gmail.com: 2013/2/16

Re: [HACKERS] is it bug? - printing boolean domains in sql/xml function

2013-02-21 Thread Pavel Stehule
pink Peter as xml feature commiter. Regards Pavel 2013/2/21 Pavel Stehule pavel.steh...@gmail.com: Hello here is patch where it should be pushed - 9.3 or 9.4 ? I vote 9.3 - I know a users, that should to do workarounds (he should not to use domains) now, so early is better. And this

Re: [HACKERS] JSON Function Bike Shedding

2013-02-21 Thread Robert Haas
On Thu, Feb 21, 2013 at 10:51 AM, Merlin Moncure mmonc...@gmail.com wrote: Sure: but that's another straw man: abuse of + operator is case of combining arbitrarily different behaviors (concatenation and arithmetic aggregation) into uniform syntax. This is bad, but a different thing. The

Re: [HACKERS] posix_fadvise missing in the walsender

2013-02-21 Thread Jeff Janes
On Wed, Feb 20, 2013 at 7:54 AM, Robert Haas robertmh...@gmail.com wrote: On Tue, Feb 19, 2013 at 5:48 PM, Simon Riggs si...@2ndquadrant.com wrote: I agree with Merlin and Joachim - if we have the call in one place, we should have it in both. We might want to assess whether we even want to

Re: [HACKERS] Unarchived WALs deleted after crash

2013-02-21 Thread Jeff Janes
On Thu, Feb 21, 2013 at 12:39 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Are you talking about the patch to avoid restored WAL segments from being re-archived (commit 6f4b8a4f4f7a2d683ff79ab59d3693714b965e3d), or the bug that that unarchived WALs were deleted after crash (commit

Re: [HACKERS] sql_drop Event Trigger

2013-02-21 Thread Robert Haas
On Wed, Feb 20, 2013 at 10:48 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: I have added some protections so that these do not fire on undesirable events (such as dropping an event trigger); also event triggers and other object types are filtered out of pg_dropped_objects, in case

Re: [HACKERS] posix_fadvise missing in the walsender

2013-02-21 Thread Robert Haas
On Thu, Feb 21, 2013 at 12:16 PM, Jeff Janes jeff.ja...@gmail.com wrote: Does the kernel really read a data block from disk into memory in order to immediately overwrite it? I would have thought it would optimize that away, at least if the writes are sized and aligned to 512 or 1024 bytes

[HACKERS] relpath() to src/common

2013-02-21 Thread Alvaro Herrera
Just a heads up: I intend to push this shortly, and after it has seen some BF activity, pg_xlogdump as well. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services *** a/src/backend/Makefile --- b/src/backend/Makefile ***

Re: [HACKERS] sql_drop Event Trigger

2013-02-21 Thread Robert Haas
On Thu, Feb 21, 2013 at 12:25 PM, Robert Haas robertmh...@gmail.com wrote: There's also code to re-obtain the list of objects to drop after the event trigger functions have run; the second list is compared to the first one, and if they differ, an error is raised. This is definitely an

Re: [HACKERS] [DOCS] Contrib module xml2 status

2013-02-21 Thread Robert Haas
On Wed, Feb 20, 2013 at 11:58 AM, Ian Lawrence Barwick barw...@gmail.com wrote: I'm not sure if this is a documentation or hackers issue, but the documentation page for contrib module xml2 refers to PostgreSQL 8.4 in the future tense: It is planned that this module will be removed in

Re: [HACKERS] sql_drop Event Trigger

2013-02-21 Thread Alvaro Herrera
Robert Haas escribió: On Wed, Feb 20, 2013 at 10:48 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: I have added some protections so that these do not fire on undesirable events (such as dropping an event trigger); also event triggers and other object types are filtered out of

Re: [HACKERS] sql_drop Event Trigger

2013-02-21 Thread Alvaro Herrera
Robert Haas escribió: On Thu, Feb 21, 2013 at 12:25 PM, Robert Haas robertmh...@gmail.com wrote: There's also code to re-obtain the list of objects to drop after the event trigger functions have run; the second list is compared to the first one, and if they differ, an error is raised.

Re: [HACKERS] [DOCS] Contrib module xml2 status

2013-02-21 Thread Magnus Hagander
On Thu, Feb 21, 2013 at 6:39 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Feb 20, 2013 at 11:58 AM, Ian Lawrence Barwick barw...@gmail.com wrote: I'm not sure if this is a documentation or hackers issue, but the documentation page for contrib module xml2 refers to PostgreSQL 8.4 in the

Re: [HACKERS] [DOCS] Contrib module xml2 status

2013-02-21 Thread Andrew Dunstan
On 02/21/2013 12:56 PM, Magnus Hagander wrote: On Thu, Feb 21, 2013 at 6:39 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Feb 20, 2013 at 11:58 AM, Ian Lawrence Barwick barw...@gmail.com wrote: I'm not sure if this is a documentation or hackers issue, but the documentation page for

Re: [HACKERS] JSON Function Bike Shedding

2013-02-21 Thread Merlin Moncure
On Thu, Feb 21, 2013 at 11:02 AM, Robert Haas robertmh...@gmail.com wrote: On Thu, Feb 21, 2013 at 10:51 AM, Merlin Moncure mmonc...@gmail.com wrote: Sure: but that's another straw man: abuse of + operator is case of combining arbitrarily different behaviors (concatenation and arithmetic

Re: [HACKERS] bugfix: --echo-hidden is not supported by \sf statements

2013-02-21 Thread Pavel Stehule
2013/2/20 Josh Kupershmidt schmi...@gmail.com: On Wed, Jan 23, 2013 at 12:06 PM, Pavel Stehule pavel.steh...@gmail.com wrote: 2013/1/14 Tom Lane t...@sss.pgh.pa.us: Well, fine, but then it should fix both of them and remove minimal_error_message altogether. I would however suggest

Re: [HACKERS] Materialized views WIP patch

2013-02-21 Thread Peter Eisentraut
On 2/21/13 9:25 AM, Kevin Grittner wrote: Peter Eisentraut pete...@gmx.net wrote: On 2/20/13 11:14 PM, Greg Stark wrote: That's not entirely true. From the database's point of view, TRUNCATE is in many ways actually DDL. Whether something is DDL or DML or a read operation (query) is not an

Re: [HACKERS] Materialized views WIP patch

2013-02-21 Thread Kevin Grittner
Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kgri...@ymail.com writes: What did you think of the idea of something like DISCARD MATERIALIZED VIEW DATA as a new statment?  Or maybe RESET MATERIALIZED VIEW? I could live with either DISCARD or RESET. I figured this was worth a pass

Re: [HACKERS] Materialized views WIP patch

2013-02-21 Thread Greg Stark
On Thu, Feb 21, 2013 at 2:38 PM, Kevin Grittner kgri...@ymail.com wrote: It doesn't. That was one of the more contentious points in the earlier bikeshedding phases. Some felt that throwing away the contents was a form of making the MV out of date and as such didn't require any special

Re: [HACKERS] Materialized views WIP patch

2013-02-21 Thread Kevin Grittner
Greg Stark st...@mit.edu wrote: Kevin Grittner kgri...@ymail.com wrote: It doesn't.  That was one of the more contentious points in the earlier bikeshedding phases.  Some felt that throwing away the contents was a form of making the MV out of date and as such didn't require any special

Re: [HACKERS] Call for Google Summer of Code mentors, admins

2013-02-21 Thread Fabrízio de Royes Mello
On Tue, Feb 19, 2013 at 5:43 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 19.02.2013 20:07, Fabrízio de Royes Mello wrote: I would like to propose implement a way to track creation times to database objects. This was discussed before in this thread [1]. This was discussed

Re: [HACKERS] [DOCS] Contrib module xml2 status

2013-02-21 Thread Ian Lawrence Barwick
2013/2/22 Andrew Dunstan and...@dunslane.net: On 02/21/2013 12:56 PM, Magnus Hagander wrote: On Thu, Feb 21, 2013 at 6:39 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Feb 20, 2013 at 11:58 AM, Ian Lawrence Barwick barw...@gmail.com wrote: I'm not sure if this is a documentation

Re: [HACKERS] Materialized views WIP patch

2013-02-21 Thread Andres Freund
On 2013-02-21 14:11:10 -0800, Kevin Grittner wrote: Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kgri...@ymail.com writes: What did you think of the idea of something like DISCARD MATERIALIZED VIEW DATA as a new statment?  Or maybe RESET MATERIALIZED VIEW? I could live with

Re: [HACKERS] 9.2.3 crashes during archive recovery

2013-02-21 Thread Michael Paquier
On Thu, Feb 21, 2013 at 11:09 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 15.02.2013 15:49, Heikki Linnakangas wrote: Attached is a patch for git master. The basic idea is to split InArchiveRecovery into two variables, InArchiveRecovery and ArchiveRecoveryRequested.

[HACKERS] use_remote_explain missing in docs of postgres_fdw

2013-02-21 Thread Michael Paquier
Hi all, While testing a bit this feature, I noticed that use_remote_explain is available in the list of options for FOREIGN TABLE and SERVER but this is not specified in the docs: http://www.postgresql.org/docs/devel/static/postgres-fdw.html postgres=# CREATE FOREIGN TABLE foo (a int, b int)

[HACKERS] OSSP UUID present but cannot be compiled

2013-02-21 Thread David E. Wheeler
While building 9.2.3 on OS X 10.8.2 today: checking ossp/uuid.h usability... no checking ossp/uuid.h presence... yes configure: WARNING: ossp/uuid.h: present but cannot be compiled configure: WARNING: ossp/uuid.h: check for missing prerequisite headers? configure: WARNING: ossp/uuid.h: see