Re: [HACKERS] pg_stat_transaction patch

2010-05-25 Thread Takahiro Itagaki
Joel Jacobson j...@gluefinance.com wrote: I applied all the changes on 9.0beta manually and then it compiled without any assertion failures. I also changed the oids to a different unused range, since the ones I used before had been taken in 9.0beta1. Thanks, but you still need to test

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-25 Thread Heikki Linnakangas
On 24/05/10 22:49, Alvaro Herrera wrote: Excerpts from Josh Berkus's message of vie may 21 17:57:35 -0400 2010: Problem: currently, if your database has a large amount of cold data, such as 350GB of 3-year-old sales transactions, in 8.4 vacuum no longer needs to touch it thanks to the

Re: [HACKERS] ExecutorCheckPerms() hook

2010-05-25 Thread KaiGai Kohei
(2010/05/25 12:19), Robert Haas wrote: On Mon, May 24, 2010 at 9:27 PM, Stephen Frostsfr...@snowman.net wrote: * KaiGai Kohei (kai...@ak.jp.nec.com) wrote: We have two options; If the checker function takes the list of RangeTblEntry, it will be comfortable to ExecCheckRTPerms(), but not

Re: [HACKERS] JSON manipulation functions

2010-05-25 Thread Joseph Adams
I started a wiki article for brainstorming the JSON API: http://wiki.postgresql.org/wiki/JSON_API_Brainstorm . I also made substantial changes to the draft of the API based on discussion here and on the #postgresql IRC channel. Is it alright to use the wiki for brainstorming, or should it stay

Re: [HACKERS] ROLLBACK TO SAVEPOINT

2010-05-25 Thread Florian Pflug
On May 25, 2010, at 6:08 , Sam Vilain wrote: http://www.postgresql.org/docs/8.4/static/sql-savepoint.html Lead us to believe that if you roll back to the same savepoint name twice in a row, that you might start walking back through the savepoints. I guess I missed the note on ROLLBACK TO

Re: [HACKERS] recovery getting interrupted is not so unusual as it used to be

2010-05-25 Thread Fujii Masao
On Mon, May 17, 2010 at 5:33 PM, Fujii Masao masao.fu...@gmail.com wrote: On Sat, May 15, 2010 at 3:20 AM, Robert Haas robertmh...@gmail.com wrote: Hmm, OK, I think that makes sense.  Would you care to propose a patch? Yep. Here is the patch. This patch distinguishes normal shutdown from

[HACKERS] Hot Standby performance and deadlocking

2010-05-25 Thread Simon Riggs
Some performance problems have been reported on HS from two users: Erik and Stefan. The characteristics of those issues have been that performance is * sporadically reduced, though mostly runs at full speed * context switch storms reported as being associated So we're looking for something that

Re: [HACKERS] ROLLBACK TO SAVEPOINT

2010-05-25 Thread Heikki Linnakangas
On 25/05/10 13:03, Florian Pflug wrote: On May 25, 2010, at 6:08 , Sam Vilain wrote: http://www.postgresql.org/docs/8.4/static/sql-savepoint.html Lead us to believe that if you roll back to the same savepoint name twice in a row, that you might start walking back through the savepoints. I

Re: [HACKERS] Stefan's bug (was: max_standby_delay considered harmful)

2010-05-25 Thread Fujii Masao
On Tue, May 18, 2010 at 3:09 PM, Fujii Masao masao.fu...@gmail.com wrote: (2) pg_ctl -ms stop emits the following warning whenever there is the backup_label file in $PGDATA.       WARNING: online backup mode is active       Shutdown will not complete until pg_stop_backup() is called. This

Re: [HACKERS] Regression testing for psql

2010-05-25 Thread Stephen Frost
* Robert Haas (robertmh...@gmail.com) wrote: Of course, if people want to suggest tests that just shouldn't be included, I can go through and strip things out. Well... I'm a little reluctant to believe that we should have 3.3M of tests for the entire backend and 5M of tests just for psql.

Re: [HACKERS] ExecutorCheckPerms() hook

2010-05-25 Thread Stephen Frost
KaiGai, * KaiGai Kohei (kai...@ak.jp.nec.com) wrote: OK, the attached patch reworks it according to the way. I havn't looked at it yet, but the hook was added to ExecCheckRTPerms(), not RTE. This was for two main reasons- it seemed simpler to us and it meant that any security module

Re: [HACKERS] ROLLBACK TO SAVEPOINT

2010-05-25 Thread Florian Pflug
On May 25, 2010, at 12:18 , Heikki Linnakangas wrote: On 25/05/10 13:03, Florian Pflug wrote: On May 25, 2010, at 6:08 , Sam Vilain wrote: http://www.postgresql.org/docs/8.4/static/sql-savepoint.html Lead us to believe that if you roll back to the same savepoint name twice in a row, that

Re: [HACKERS] JSON manipulation functions

2010-05-25 Thread Robert Haas
On Tue, May 25, 2010 at 5:37 AM, Joseph Adams joeyadams3.14...@gmail.com wrote: I started a wiki article for brainstorming the JSON API: http://wiki.postgresql.org/wiki/JSON_API_Brainstorm .  I also made substantial changes to the draft of the API based on discussion here and on the

Re: [HACKERS] Exposing the Xact commit order to the user

2010-05-25 Thread Florian Pflug
On May 25, 2010, at 3:21 , Tom Lane wrote: Florian Pflug f...@phlo.org writes: The subtle point here is whether you consider the view from the outside (in the sense of what a read-only transaction started at an arbitrary time can or cannot observe), or from the inside (what updating

Re: [HACKERS] libpq, PQexecPrepared, data size sent to FE vs. FETCH_COUNT

2010-05-25 Thread Alex Goncharov
,--- I/Alex (Mon, 24 May 2010 12:25:18 -0400) * | No equivalent of FETCH_COUNT is available at the libpq level, so I | assume that the interface I am using is smart enough not to send | gigabytes of data to FE. | | Where does the result set (GBs of data) reside after I call | PQexecPrepared?

Re: [HACKERS] Exposing the Xact commit order to the user

2010-05-25 Thread Nicolas Barbier
2010/5/25 Dan Ports d...@csail.mit.edu: On Mon, May 24, 2010 at 10:24:07AM -0500, Kevin Grittner wrote: Replicating or recreating the whole predicate locking and conflict detection on slaves is not feasible for performance reasons. (I won't elaborate unless someone feels that's not

Re: [HACKERS] libpq, PQexecPrepared, data size sent to FE vs. FETCH_COUNT

2010-05-25 Thread Yeb Havinga
Alex Goncharov wrote: ,--- I/Alex (Mon, 24 May 2010 12:25:18 -0400) * | No equivalent of FETCH_COUNT is available at the libpq level, so I | assume that the interface I am using is smart enough not to send | gigabytes of data to FE. | | Where does the result set (GBs of data) reside after I

Re: [HACKERS] libpq, PQexecPrepared, data size sent to FE vs. FETCH_COUNT

2010-05-25 Thread Abhijit Menon-Sen
At 2010-05-25 07:35:34 -0400, alex-goncha...@comcast.net wrote: | Where does the result set (GBs of data) reside after I call | PQexecPrepared? On BE, I hope? Unless you explicitly declare and fetch from an SQL-level cursor, your many GBs of data are going to be transmitted to libpq, which

Re: [HACKERS] JSON manipulation functions

2010-05-25 Thread Magnus Hagander
On Tue, May 25, 2010 at 12:57, Robert Haas robertmh...@gmail.com wrote: On Tue, May 25, 2010 at 5:37 AM, Joseph Adams joeyadams3.14...@gmail.com wrote: I started a wiki article for brainstorming the JSON API: http://wiki.postgresql.org/wiki/JSON_API_Brainstorm .  I also made substantial

Re: [HACKERS] ExecutorCheckPerms() hook

2010-05-25 Thread Stephen Frost
KaiGai, * KaiGai Kohei (kai...@ak.jp.nec.com) wrote: OK, the attached patch reworks it according to the way. Reviewing this patch, there are a whole slew of problems. #1: REALLY BIG ISSUE- Insufficient comment updates. You've changed function definitions in a pretty serious way as well as

Re: [HACKERS] libpq, PQexecPrepared, data size sent to FE vs. FETCH_COUNT

2010-05-25 Thread Alex Goncharov
,--- Abhijit Menon-Sen (Tue, 25 May 2010 17:26:18 +0530) * | Unless you explicitly declare and fetch from an SQL-level cursor, your | many GBs of data are going to be transmitted to libpq, which will eat | lots of memory. (The wire protocol does have something like cursors, | but libpq does

Re: [HACKERS] pg_upgrade docs

2010-05-25 Thread Robert Haas
On Mon, May 24, 2010 at 11:35 PM, Bruce Momjian br...@momjian.us wrote: Have you read the docs?  It does mention the issue with /contrib and stuff.  How do I document a limitation I don't know about?  This is all very vague.  Please suggest some wording. OK, here's an attempt. Please

Re: [HACKERS] libpq, PQexecPrepared, data size sent to FE vs. FETCH_COUNT

2010-05-25 Thread Andrew Chernow
On 05/25/2010 07:35 AM, Alex Goncharov wrote: ,--- I/Alex (Mon, 24 May 2010 12:25:18 -0400) * | No equivalent of FETCH_COUNT is available at the libpq level, so I | assume that the interface I am using is smart enough not to send | gigabytes of data to FE. | | Where does the result set (GBs

Re: [HACKERS] ExecutorCheckPerms() hook

2010-05-25 Thread Stephen Frost
* KaiGai Kohei (kai...@ak.jp.nec.com) wrote: * DoCopy() and RI_Initial_Check() were reworked to call ExecCheckRTEPerms() with locally built RangeTblEntry. Maybe I missed it somewhere, but we still need to address the case where the user doesn't have those SELECT permissions that we're looking

[HACKERS] [PATCH] Add XMLEXISTS function from the SQL/XML standard (was: Add xpath_exists Function)

2010-05-25 Thread Mike Fowler
I've been reading the SQL/XML standard and discovered that it defines a function named XMLEXISTS that does exactly what the todo item xpath_exists defines. My original patch named the function as per the todo but I think using the function name from the standard is a better idea. So this patch

Re: [HACKERS] libpq, PQexecPrepared, data size sent to FE vs. FETCH_COUNT

2010-05-25 Thread Andrew Dunstan
Alex Goncharov wrote: ,--- I/Alex (Mon, 24 May 2010 12:25:18 -0400) * | No equivalent of FETCH_COUNT is available at the libpq level, so I | assume that the interface I am using is smart enough not to send | gigabytes of data to FE. | | Where does the result set (GBs of data) reside after

Re: [HACKERS] JSON manipulation functions

2010-05-25 Thread Joseph Adams
Well, I think it's fine to use the wiki for brainstorming, but before you change the design you probably need to talk about it here.  You can't rely on everyone on -hackers to follow changes on a wiki page somewhere.  It looks like the API has been overhauled pretty heavily since the last

Re: [HACKERS] pg_upgrade docs

2010-05-25 Thread Bruce Momjian
Robert Haas wrote: On Mon, May 24, 2010 at 11:35 PM, Bruce Momjian br...@momjian.us wrote: Have you read the docs? ?It does mention the issue with /contrib and stuff. ?How do I document a limitation I don't know about? ?This is all very vague. ?Please suggest some wording. OK, here's an

Re: [HACKERS] Clearing psql`s input buffer after auto-reconnect

2010-05-25 Thread Greg Sabino Mullane
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 3. Have CheckConnection do longjmp(sigint_interrupt_jmp) after resetting ... Now #1 might be the best long-term solution but I have no particular appetite to tackle it, and #2 is just too ugly to contemplate. That leaves #3, which is a bit

Re: [HACKERS] [PATCH] Add XMLEXISTS function from the SQL/XML standard (was: Add xpath_exists Function)

2010-05-25 Thread Erik Rijkers
On Tue, May 25, 2010 16:31, Mike Fowler wrote: I've been reading the SQL/XML standard and discovered that it defines a function named XMLEXISTS that does exactly what the todo item xpath_exists defines. My original patch named the function as per the todo but I think using the function name

Re: [HACKERS] [PATCH] Add XMLEXISTS function from the SQL/XML standard

2010-05-25 Thread Mike Fowler
Erik Rijkers wrote: libxml2.x86_64 2.6.26-2.1.2.8 installed libxml2-devel.x86_642.6.26-2.1.2.8 installed Thanks for testing my patch Erik. It turns out I've got libxml2 installed at version 2.7.5. Searching the gnome mailing lists, it turns out xmlXPathCompiledEvalToBoolean

Re: [HACKERS] Exposing the Xact commit order to the user

2010-05-25 Thread Simon Riggs
On Sun, 2010-05-23 at 16:21 -0400, Jan Wieck wrote: In some systems (data warehousing, replication), the order of commits is important, since that is the order in which changes have become visible. This information could theoretically be extracted from the WAL, but scanning the entire WAL

Re: [HACKERS] Synchronization levels in SR

2010-05-25 Thread Simon Riggs
On Tue, 2010-05-25 at 12:40 +0900, Fujii Masao wrote: On Tue, May 25, 2010 at 10:29 AM, Josh Berkus j...@agliodbs.com wrote: I agree that #4 should be done last, but it will be needed, not in the least by your employer ;-) . I don't see any obvious way to make #4 compatible with any

Re: [HACKERS] Synchronization levels in SR

2010-05-25 Thread Simon Riggs
On Mon, 2010-05-24 at 22:20 +0900, Fujii Masao wrote: Second, we need to discuss about how to specify the synch level. There are three approaches: * Per standby Since the purpose, location and H/W resource often differ from one standby to another, specifying level per standby (i.e.,

Re: [HACKERS] Synchronization levels in SR

2010-05-25 Thread Simon Riggs
On Mon, 2010-05-24 at 18:29 -0700, Josh Berkus wrote: If people agree that the above is our roadmap, implementing per-standby first makes sense, and then we can implement per-session GUC later. IMHO per-standby sounds simple, but is dangerously simplistic, explained on another part of the

Re: [HACKERS] recovery getting interrupted is not so unusual as it used to be

2010-05-25 Thread Simon Riggs
On Tue, 2010-05-25 at 19:12 +0900, Fujii Masao wrote: On Mon, May 17, 2010 at 5:33 PM, Fujii Masao masao.fu...@gmail.com wrote: On Sat, May 15, 2010 at 3:20 AM, Robert Haas robertmh...@gmail.com wrote: Hmm, OK, I think that makes sense. Would you care to propose a patch? Yep. Here is the

Re: [HACKERS] Synchronization levels in SR

2010-05-25 Thread Robert Haas
On Tue, May 25, 2010 at 12:28 PM, Simon Riggs si...@2ndquadrant.com wrote: Synchronous replication implies that a commit should wait. This wait is experienced by the transaction, not by other parts of the system. If we define robustness at the standby level then robustness depends upon unseen

Re: [HACKERS] recovery getting interrupted is not so unusual as it used to be

2010-05-25 Thread Robert Haas
On Tue, May 25, 2010 at 12:36 PM, Simon Riggs si...@2ndquadrant.com wrote: On Tue, 2010-05-25 at 19:12 +0900, Fujii Masao wrote: On Mon, May 17, 2010 at 5:33 PM, Fujii Masao masao.fu...@gmail.com wrote: On Sat, May 15, 2010 at 3:20 AM, Robert Haas robertmh...@gmail.com wrote: Hmm, OK, I

Re: [HACKERS] [PATCH] Add XMLEXISTS function from the SQL/XML standard

2010-05-25 Thread Robert Haas
On Tue, May 25, 2010 at 12:04 PM, Mike Fowler m...@mlfowler.com wrote: Erik Rijkers wrote: libxml2.x86_64          2.6.26-2.1.2.8  installed libxml2-devel.x86_64    2.6.26-2.1.2.8  installed Thanks for testing my patch Erik. It turns out I've got libxml2 installed at version 2.7.5.

Re: [HACKERS] Synchronization levels in SR

2010-05-25 Thread Joshua D. Drake
On Tue, 2010-05-25 at 12:40 -0400, Robert Haas wrote: On Tue, May 25, 2010 at 12:28 PM, Simon Riggs si...@2ndquadrant.com wrote: Synchronous replication implies that a commit should wait. This wait is experienced by the transaction, not by other parts of the system. If we define robustness

[HACKERS] Confused about the buffer pool size

2010-05-25 Thread MMK
Hello All: In the code (costsize.c), I see that effective_cache_size is set to DEFAULT_EFFECTIVE_CACHE_SIZE. This is defined as follows in cost.h #define DEFAULT_EFFECTIVE_CACHE_SIZE 16384 But when I say  show shared_buffers in psql I get, shared_buffers  28MB In postgresql.conf

Re: [HACKERS] JSON manipulation functions

2010-05-25 Thread Robert Haas
On Tue, May 25, 2010 at 10:52 AM, Joseph Adams joeyadams3.14...@gmail.com wrote: Well, I think it's fine to use the wiki for brainstorming, but before you change the design you probably need to talk about it here.  You can't rely on everyone on -hackers to follow changes on a wiki page

Re: [HACKERS] Synchronization levels in SR

2010-05-25 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote: Simon Riggs si...@2ndquadrant.com wrote: If we define robustness at the standby level then robustness depends upon unseen administrators, as well as the current up/down state of standbys. This is action-at-a-distance in its worst form. Maybe, but I

[HACKERS] [PATCH] Add _PG_init to PL language handler documentation

2010-05-25 Thread Jonathan Leto
Howdy, This tiny doc patch adds _PG_init to the skeleton example code for a PL. The information is quite valuable to PL authors, who might miss it when it is described in the shared library documentation. This patch was based off of 6e2ba96 in the git mirror and a colorized diff can be viewed

Re: [HACKERS] Synchronization levels in SR

2010-05-25 Thread Simon Riggs
On Tue, 2010-05-25 at 12:40 -0400, Robert Haas wrote: On Tue, May 25, 2010 at 12:28 PM, Simon Riggs si...@2ndquadrant.com wrote: Synchronous replication implies that a commit should wait. This wait is experienced by the transaction, not by other parts of the system. If we define robustness

Re: [HACKERS] [PATCH] Add XMLEXISTS function from the SQL/XML standard

2010-05-25 Thread Mike Fowler
Robert Haas wrote: On Tue, May 25, 2010 at 12:04 PM, Mike Fowler m...@mlfowler.com wrote: Erik Rijkers wrote: libxml2.x86_64 2.6.26-2.1.2.8 installed libxml2-devel.x86_642.6.26-2.1.2.8 installed Thanks for testing my patch Erik. It turns out I've got libxml2

Re: [HACKERS] Synchronization levels in SR

2010-05-25 Thread Simon Riggs
On Tue, 2010-05-25 at 11:52 -0500, Kevin Grittner wrote: Robert Haas robertmh...@gmail.com wrote: Simon Riggs si...@2ndquadrant.com wrote: If we define robustness at the standby level then robustness depends upon unseen administrators, as well as the current up/down state of standbys.

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-25 Thread Alvaro Herrera
Excerpts from Heikki Linnakangas's message of mar may 25 04:41:30 -0400 2010: On 24/05/10 22:49, Alvaro Herrera wrote: I think this is nonsense. If you have 3-years-old sales transactions, and your database has any interesting churn, tuples those pages have been frozen for a very long

Re: [HACKERS] Synchronization levels in SR

2010-05-25 Thread Robert Haas
On Tue, May 25, 2010 at 1:10 PM, Simon Riggs si...@2ndquadrant.com wrote: On Tue, 2010-05-25 at 11:52 -0500, Kevin Grittner wrote: Robert Haas robertmh...@gmail.com wrote: Simon Riggs si...@2ndquadrant.com wrote: If we define robustness at the standby level then robustness depends upon

Re: [HACKERS] Confused about the buffer pool size

2010-05-25 Thread Heikki Linnakangas
On 25/05/10 19:49, MMK wrote: Hello All: In the code (costsize.c), I see that effective_cache_size is set to DEFAULT_EFFECTIVE_CACHE_SIZE. This is defined as follows in cost.h #define DEFAULT_EFFECTIVE_CACHE_SIZE 16384 But when I say show shared_buffers in psql I get, shared_buffers

Re: [HACKERS] Synchronization levels in SR

2010-05-25 Thread Simon Riggs
On Tue, 2010-05-25 at 13:31 -0400, Robert Haas wrote: On Tue, May 25, 2010 at 1:10 PM, Simon Riggs si...@2ndquadrant.com wrote: On Tue, 2010-05-25 at 11:52 -0500, Kevin Grittner wrote: Robert Haas robertmh...@gmail.com wrote: Simon Riggs si...@2ndquadrant.com wrote: If we define

Re: [HACKERS] [PATCH] Add XMLEXISTS function from the SQL/XML standard

2010-05-25 Thread Robert Haas
On Tue, May 25, 2010 at 1:09 PM, Mike Fowler m...@mlfowler.com wrote: We're unlikely to accept this patch if it changes the minimum version of libxml2 required to compile PostgreSQL Why? 2.6.27 is almost 4 years old. Because we work hard to minimize our dependencies and make them as

Re: [HACKERS] Synchronization levels in SR

2010-05-25 Thread Simon Riggs
On Tue, 2010-05-25 at 19:08 +0200, Alastair Turner wrote: On Tue, May 25, 2010 at 6:28 PM, Simon Riggs si...@2ndquadrant.com wrote: ... The best parameter we can specify is the number of servers that we wish to wait for confirmation from. That is a definition that easily manages the

Re: [HACKERS] [PATCH] Add XMLEXISTS function from the SQL/XML standard

2010-05-25 Thread Michael Tharp
On 05/25/2010 01:09 PM, Mike Fowler wrote: Why? 2.6.27 is almost 4 years old. RHEL 5 ships with 2.6.26. I imagine that supporting it is very desirable, regardless of its age, since that is unfortunately still the latest version of RHEL. -- m. tharp -- Sent via pgsql-hackers mailing list

Re: [HACKERS] Synchronization levels in SR

2010-05-25 Thread Simon Riggs
On Tue, 2010-05-25 at 13:31 -0400, Robert Haas wrote: So I agree that we need to talk about whether or not we want to do this. I'll give my opinion. I am not sure how useful this really is. Consider a master with two standbys. The master commits a transaction and waits for one of the two

Re: [HACKERS] Synchronization levels in SR

2010-05-25 Thread Alastair Turner
On Tue, May 25, 2010 at 6:28 PM, Simon Riggs si...@2ndquadrant.com wrote: ... The best parameter we can specify is the number of servers that we wish to wait for confirmation from. That is a definition that easily manages the complexity of having various servers up/down at any one time. It

Re: [HACKERS] [PATCH] Add XMLEXISTS function from the SQL/XML standard

2010-05-25 Thread Andrew Dunstan
Robert Haas wrote: On Tue, May 25, 2010 at 1:09 PM, Mike Fowler m...@mlfowler.com wrote: We're unlikely to accept this patch if it changes the minimum version of libxml2 required to compile PostgreSQL Why? 2.6.27 is almost 4 years old. Because we work hard to minimize our

Re: [HACKERS] [PATCH] Add XMLEXISTS function from the SQL/XML standard

2010-05-25 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Tue, May 25, 2010 at 1:09 PM, Mike Fowler m...@mlfowler.com wrote: We're unlikely to accept this patch if it changes the minimum version of libxml2 required to compile PostgreSQL Why? 2.6.27 is almost 4 years old. Because we work hard to

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-25 Thread Tom Lane
Alvaro Herrera alvhe...@alvh.no-ip.org writes: This sounds like extending Xid to 64 bits, without having to store the high bits everywhere. Was this discussed in the PGCon devs meeting? Yeah, that's what it would amount to. It was not discussed at the dev meeting --- it was an idea that came

Re: [HACKERS] Exposing the Xact commit order to the user

2010-05-25 Thread Dan Ports
On Tue, May 25, 2010 at 02:00:42PM +0200, Nicolas Barbier wrote: I don't understand the problem. According to me, in the context of SSI, a read-only slave can just map SERIALIZABLE to the technical implementation of REPEATABLE READ (i.e., the currently-existing SERIALIZABLE). The union of the

Re: [HACKERS] Exposing the Xact commit order to the user

2010-05-25 Thread Florian Pflug
On May 25, 2010, at 20:18 , Dan Ports wrote: On Tue, May 25, 2010 at 02:00:42PM +0200, Nicolas Barbier wrote: I don't understand the problem. According to me, in the context of SSI, a read-only slave can just map SERIALIZABLE to the technical implementation of REPEATABLE READ (i.e., the

Re: [HACKERS] tsvector pg_stats seems quite a bit off.

2010-05-25 Thread Alvaro Herrera
Excerpts from Jesper Krogh's message of mié may 19 15:01:18 -0400 2010: But the distribution is very flat at the end, the last 128 values are excactly 1.00189e-05 which means that any term sitting outside the array would get an estimate of 1.00189e-05 * 350174 / 2 = 1.75 ~ 2 rows I don't

Re: [HACKERS] Exposing the Xact commit order to the user

2010-05-25 Thread Kevin Grittner
Florian Pflug f...@phlo.org wrote: Hm, so in fact SSI sometimes allows the database to be inconsistent, but only as long as nobody tries to observe it? Not exactly. The eventually-persisted state is always consistent, but there can be a transitory committed state which would violate

Re: [HACKERS] Exposing the Xact commit order to the user

2010-05-25 Thread Dan Ports
On Tue, May 25, 2010 at 08:35:44PM +0200, Florian Pflug wrote: Hm, so in fact SSI sometimes allows the database to be inconsistent, but only as long as nobody tries to observe it? Yes. Note that even while it's in an inconsistent state, you can still perform any query that doesn't observe the

Re: [HACKERS] Exposing the Xact commit order to the user

2010-05-25 Thread Kevin Grittner
Jan Wieck janwi...@yahoo.com wrote: Have you ever looked at one of those queries, that Londiste or Slony issue against the provider DB in order to get all the log data that has been committed between two snapshots? Is that really the best you can think of? No, I admit I haven't. In fact,

Re: [HACKERS] Synchronization levels in SR

2010-05-25 Thread Yeb Havinga
Simon Riggs wrote: How we handle degraded mode is important, yes. Whatever parameters we choose the problem will remain the same. Should we just ignore degraded mode and respond as if nothing bad had happened? Most people would say not. If we specify server1 = synch and server2 = async we then

Re: [HACKERS] Synchronization levels in SR

2010-05-25 Thread Dimitri Fontaine
Hi, Simon Riggs si...@2ndquadrant.com writes: On Tue, 2010-05-25 at 19:08 +0200, Alastair Turner wrote: On Tue, May 25, 2010 at 6:28 PM, Simon Riggs si...@2ndquadrant.com wrote: The best parameter we can specify is the number of servers that we wish to wait for confirmation from. This

Re: [HACKERS] Confused about the buffer pool size

2010-05-25 Thread MMK
Hello Heikki: This is what the documentation says (see below). But it does not tell my anything about what the actual buffer size is. How do I know what the real buffer size is? I am using 8.4.4 and I am running only one query at a time. Cheers, MMK. Sets the planner's assumption about the

Re: [HACKERS] Exposing the Xact commit order to the user

2010-05-25 Thread Nicolas Barbier
2010/5/25 Dan Ports d...@csail.mit.edu: On Tue, May 25, 2010 at 02:00:42PM +0200, Nicolas Barbier wrote: I don't understand the problem. According to me, in the context of SSI, a read-only slave can just map SERIALIZABLE to the technical implementation of REPEATABLE READ (i.e., the

Re: [HACKERS] Exposing the Xact commit order to the user

2010-05-25 Thread Florian Pflug
On May 25, 2010, at 20:48 , Dan Ports wrote: On Tue, May 25, 2010 at 08:35:44PM +0200, Florian Pflug wrote: Hm, so in fact SSI sometimes allows the database to be inconsistent, but only as long as nobody tries to observe it? Yes. Note that even while it's in an inconsistent state, you can

Re: [HACKERS] Exposing the Xact commit order to the user

2010-05-25 Thread Nicolas Barbier
2010/5/25 Florian Pflug f...@phlo.org: On May 25, 2010, at 20:18 , Dan Ports wrote: T3, which is a read-only transaction, sees the incremented date and an empty list of receipts. But T1 later commits a new entry in the receipts table with the old date. No serializable ordering allows this.

[HACKERS] Fwd: Hiding data in postgresql

2010-05-25 Thread Hector Beyers
Hi guys, (I tried the question in another forum first) Does someone have any ideas how I can hide data without the meta data noticing? To explain further, I would like to save some collection of data where the meta-data does not see it. I am trying to do some security through obscurity. It is

Re: [HACKERS] Exposing the Xact commit order to the user

2010-05-25 Thread Nicolas Barbier
2010/5/25 Florian Pflug f...@phlo.org: Hm, but for there to be an actual problem (and not a false positive), an actual dangerous circle has to exist in the dependency graph. The existence of a dangerous structure is just a necessary (but not sufficient) and easily checked-for condition for

Re: [HACKERS] Exposing the Xact commit order to the user

2010-05-25 Thread Kevin Grittner
Florian Pflug f...@phlo.org wrote: Hm, but for there to be an actual problem (and not a false positive), an actual dangerous circle has to exist in the dependency graph. The existence of a dangerous structure is just a necessary (but not sufficient) and easily checked-for condition for

Re: [HACKERS] Exposing the Xact commit order to the user

2010-05-25 Thread Jan Wieck
On 5/24/2010 9:30 AM, Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 In light of the proposed purging scheme, how would it be able to distinguish between those two cases (nothing there yet vs. was there but purged)? There is a difference between an empty

[HACKERS] mergejoin null handling (was Re: [PERFORM] merge join killing performance)

2010-05-25 Thread Tom Lane
Scott Marlowe scott.marl...@gmail.com writes: So, Tom, so you think it's possible that the planner isn't noticing all those nulls and thinks it'll just take a row or two to get to the value it needs to join on? I dug through this and have concluded that it's really an oversight in the patch I

Re: [HACKERS] Fwd: Hiding data in postgresql

2010-05-25 Thread Joseph Adams
On Tue, May 25, 2010 at 3:39 PM, Hector Beyers hqbey...@gmail.com wrote: Hi guys, (I tried the question in another forum first) Does someone have any ideas how I can hide data without the meta data noticing? To explain further, I would like to save some collection of data where the meta-data

Re: [HACKERS] Exposing the Xact commit order to the user

2010-05-25 Thread Tom Lane
Jan Wieck janwi...@yahoo.com writes: No, I meant how will the *function* know, if a superuser and/or some background process can purge records at any time? The data contains timestamps which are supposedly taken in commit order. You can *not* rely on the commit timestamps to be in exact

Re: [HACKERS] Synchronization levels in SR

2010-05-25 Thread Simon Riggs
On Tue, 2010-05-25 at 21:19 +0200, Yeb Havinga wrote: Simon Riggs wrote: How we handle degraded mode is important, yes. Whatever parameters we choose the problem will remain the same. Should we just ignore degraded mode and respond as if nothing bad had happened? Most people would say

Re: [HACKERS] Exposing the Xact commit order to the user

2010-05-25 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote: maybe we should get serializable working and committed on one node first and then worry about how to distribute it. I think there might be other approaches to this problem Well, I've got two or three other ideas on how we can manage this for HS, but

Re: [HACKERS] Exposing the Xact commit order to the user

2010-05-25 Thread Jan Wieck
On 5/25/2010 12:03 PM, Simon Riggs wrote: On Sun, 2010-05-23 at 16:21 -0400, Jan Wieck wrote: In some systems (data warehousing, replication), the order of commits is important, since that is the order in which changes have become visible. This information could theoretically be extracted from

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-25 Thread Josh Berkus
Alvaro, This sounds like extending Xid to 64 bits, without having to store the high bits everywhere. Was this discussed in the PGCon devs meeting? Essentially, yes. One of the main objections to raising XID to 64-bit has been the per-row overhead. But adding 4 bytes per page wouldn't be

Re: [HACKERS] Exposing the Xact commit order to the user

2010-05-25 Thread Simon Riggs
On Tue, 2010-05-25 at 16:41 -0400, Jan Wieck wrote: On 5/25/2010 12:03 PM, Simon Riggs wrote: On Sun, 2010-05-23 at 16:21 -0400, Jan Wieck wrote: In some systems (data warehousing, replication), the order of commits is important, since that is the order in which changes have become

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-25 Thread Jan Wieck
On 5/24/2010 9:30 AM, Heikki Linnakangas wrote: On 22/05/10 16:35, Tom Lane wrote: Josh Berkusj...@agliodbs.com writes: From a discussion at dinner at pgcon, I wanted to send this to the list for people to poke holes in it: Somebody (I think Joe or Heikki) poked a big hole in this last

Re: [HACKERS] Confused about the buffer pool size

2010-05-25 Thread Josh Berkus
MMK, But it does not tell my anything about what the actual buffer size is. How do I know what the real buffer size is? I am using 8.4.4 and I am running only one query at a time. Please move this discussion to the pgsql-general or pgsql-performance lists. pgsql-hackers is for working on

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-25 Thread Josh Berkus
Correct. The problem actually are aborted transactions. Just because an XID is really old doesn't mean it was committed. Yes, that's the main issue with my idea; XIDs which fell off the CLOG would become visible even if they'd aborted. Do we get a bit in the visibility map for a page which

Re: [HACKERS] Exposing the Xact commit order to the user

2010-05-25 Thread Jan Wieck
On 5/25/2010 4:50 PM, Simon Riggs wrote: On Tue, 2010-05-25 at 16:41 -0400, Jan Wieck wrote: On 5/25/2010 12:03 PM, Simon Riggs wrote: On Sun, 2010-05-23 at 16:21 -0400, Jan Wieck wrote: In some systems (data warehousing, replication), the order of commits is important, since that is the

Re: [spf:guess] Re: [HACKERS] ROLLBACK TO SAVEPOINT

2010-05-25 Thread Sam Vilain
Florian Pflug wrote: On May 25, 2010, at 12:18 , Heikki Linnakangas wrote: On 25/05/10 13:03, Florian Pflug wrote: On May 25, 2010, at 6:08 , Sam Vilain wrote: http://www.postgresql.org/docs/8.4/static/sql-savepoint.html Lead us to believe that if you roll back to the same

Re: [HACKERS] tsvector pg_stats seems quite a bit off.

2010-05-25 Thread Jan Urbański
On 19/05/10 21:01, Jesper Krogh wrote: The document base is arount 350.000 documents and I have set the statistics target on the tsvector column to 1000 since the 100 seems way of. So for tsvectors the statistics target means more or less at any time track at most 10 * target lexemes

Re: [HACKERS] ExecutorCheckPerms() hook

2010-05-25 Thread KaiGai Kohei
(2010/05/25 21:44), Stephen Frost wrote: KaiGai, * KaiGai Kohei (kai...@ak.jp.nec.com) wrote: OK, the attached patch reworks it according to the way. Reviewing this patch, there are a whole slew of problems. #1: REALLY BIG ISSUE- Insufficient comment updates. You've changed function

Re: [HACKERS] ExecutorCheckPerms() hook

2010-05-25 Thread KaiGai Kohei
(2010/05/25 22:59), Stephen Frost wrote: * KaiGai Kohei (kai...@ak.jp.nec.com) wrote: * DoCopy() and RI_Initial_Check() were reworked to call ExecCheckRTEPerms() with locally built RangeTblEntry. Maybe I missed it somewhere, but we still need to address the case where the user doesn't

Re: [HACKERS] Synchronization levels in SR

2010-05-25 Thread Florian Pflug
On May 25, 2010, at 22:16 , Simon Riggs wrote: All of these issues show why I want to specify the synchronisation mode as a USERSET. That will allow us to specify more easily which parts of our application are important when the cluster is degraded and which data is so critical it must reach

Re: [HACKERS] Fwd: Hiding data in postgresql

2010-05-25 Thread Stephen Frost
Hector, * Hector Beyers (hqbey...@gmail.com) wrote: Does someone have any ideas how I can hide data without the meta data noticing? To explain further, I would like to save some collection of data where the meta-data does not see it. I am trying to do some security through obscurity. It is

[HACKERS] Open Item: pg_controldata - machine readable?

2010-05-25 Thread Takahiro Itagaki
There is an open item pg_controldata - machine readable? in the list: http://wiki.postgresql.org/wiki/PostgreSQL_9.0_Open_Items The proposal by Joe Conway is adding a new contib module. http://archives.postgresql.org/message-id/4b959d7a.6010...@joeconway.com

[HACKERS] Fwd: PDXPUG Day at OSCON 2010

2010-05-25 Thread Mark Wong
It was recommended to me to forward this to -hackers. Regards, Mark -- Forwarded message -- From: Mark Wong mark...@gmail.com Date: Tue, May 18, 2010 at 6:57 AM Subject: PDXPUG Day at OSCON 2010 To: pgsql-annou...@postgresql.org Thanks to the generosity of O'Reilly, we will be

Re: [HACKERS] ExecutorCheckPerms() hook

2010-05-25 Thread Stephen Frost
* KaiGai Kohei (kai...@ak.jp.nec.com) wrote: #2: REALLY BIG ISSUE- You've added ExecutorCheckPerms_hook as part of this patch- don't, we're in feature-freeze right now and should not be adding hooks at this time. The patch is intended to submit for the v9.1 development, not v9.0, isn't

Re: [HACKERS] ExecutorCheckPerms() hook

2010-05-25 Thread Stephen Frost
* KaiGai Kohei (kai...@ak.jp.nec.com) wrote: The reason why user must have SELECT privileges on the PK/FK tables is the validateForeignKeyConstraint() entirely calls SPI_execute() to verify FK constraints can be established between two tables (even if fallback path). And, the reason why

[HACKERS] Open Item: invalid declspec for PG_MODULE_MAGIC

2010-05-25 Thread Takahiro Itagaki
This open item is for replacing PGDLLIMPORT markers for PG_MODULE_MAGIC and PG_FUNCTION_INFO_V1 to __declspec(dllexport) because they are always expored by user modules rather than by the core codes. http://archives.postgresql.org/message-id/20100329184705.a60e.52131...@oss.ntt.co.jp The fix is

[HACKERS] libpq should not be using SSL_CTX_set_client_cert_cb

2010-05-25 Thread Tom Lane
I've been experimenting with SSL setups involving chains of CA certificates, ie, where the server or client cert itself is signed by an intermediate CA rather than a trusted root CA. This appears to work well enough on the server side if you configure the server correctly (see discussion of bug

Re: [HACKERS] Exposing the Xact commit order to the user

2010-05-25 Thread Jan Wieck
On 5/25/2010 4:16 PM, Tom Lane wrote: Jan Wieck janwi...@yahoo.com writes: No, I meant how will the *function* know, if a superuser and/or some background process can purge records at any time? The data contains timestamps which are supposedly taken in commit order. You can *not* rely on

  1   2   >