Re: [HACKERS] review: CHECK FUNCTION statement

2012-03-02 Thread Pavel Stehule
Hello 2012/3/1 Alvaro Herrera alvhe...@commandprompt.com: Why does CollectCheckedFunctions skip trigger functions?  My only guess is that at one point the checker was not supposed to know how to check them, and a later version learned about it and this bit wasn't updated; but maybe there's

Re: [HACKERS] review: CHECK FUNCTION statement

2012-03-02 Thread Pavel Stehule
2012/3/2 Alvaro Herrera alvhe...@commandprompt.com: I've cleaned up the backend code a bit -- see attached.  More yet to go through; I'm mainly sending it out for you (and everyone, really) to give your opinion on my changes so far. (I split out the plpgsql checker for the time being into a

Re: [HACKERS] COPY with hints, rebirth

2012-03-02 Thread Simon Riggs
On Thu, Mar 1, 2012 at 8:49 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 01.03.2012 18:40, Simon Riggs wrote: On Sun, Feb 26, 2012 at 7:16 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com  wrote: On 24.02.2012 22:55, Simon Riggs wrote: What exactly does

Re: [HACKERS] Allowing multi -t and adding -n to vacuumdb ?

2012-03-02 Thread Jehan-Guillaume (ioguix) de Rorthais
On 01/03/2012 23:13, Tom Lane wrote: Jehan-Guillaume (ioguix) de Rorthais j...@dalibo.com writes: One of our customer send us a patch he wrote for his needs (on src/bin/scripts/vacuumdb.c, no doc were included). He's using one schema per application and would like to be able to run vacuumdb

[HACKERS] autovacuum locks

2012-03-02 Thread Gregg Jaskiewicz
Folks, I got a system here (8.3.7), that is locked up. Few queries waiting for autovacuum aquired locks on a table or two. But it looks like autovacuum is also waiting for some semaphore: #0 0x00f07410 in __kernel_vsyscall () #1 0x00252d2b in semop () from /lib/libc.so.6 #2 0x081ca1ce in

Re: [HACKERS] autovacuum locks

2012-03-02 Thread Alvaro Herrera
Excerpts from Gregg Jaskiewicz's message of vie mar 02 07:44:07 -0300 2012: Folks, I got a system here (8.3.7), that is locked up. Few queries waiting for autovacuum aquired locks on a table or two. But it looks like autovacuum is also waiting for some semaphore: #0 0x00f07410 in

Re: [HACKERS] autovacuum locks

2012-03-02 Thread Gregg Jaskiewicz
On 2 March 2012 11:03, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Gregg Jaskiewicz's message of vie mar 02 07:44:07 -0300 2012: Folks, I got a system here (8.3.7), that is locked up. Few queries waiting for autovacuum aquired locks on a table or two. But it looks like

Re: [HACKERS] autovacuum locks

2012-03-02 Thread Gregg Jaskiewicz
Looking at the system bit more now, it look like 'waiting' states are changing for both the query and autovacuum in pg_stat_activity. But very slowly. It looks like they both got into that sort of state that keeps them on the edge of starvation. So this isn't really a deadlock, but rather very

Re: [HACKERS] Bugs/slowness inserting and indexing cubes

2012-03-02 Thread Heikki Linnakangas
On 20.02.2012 10:54, Alexander Korotkov wrote: On Wed, Feb 15, 2012 at 7:28 PM, Tom Lanet...@sss.pgh.pa.us wrote: Alexander Korotkovaekorot...@gmail.com writes: On Wed, Feb 15, 2012 at 4:26 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: So, I think we should go with

Re: [HACKERS] Website stylesheet for local docs

2012-03-02 Thread Magnus Hagander
On Mon, Feb 27, 2012 at 17:26, Magnus Hagander mag...@hagander.net wrote: On Mon, Feb 27, 2012 at 16:20, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: On Mon, Feb 27, 2012 at 04:37, Robert Haas robertmh...@gmail.com wrote: Why not change the default?  Does

Re: [HACKERS] Reducing bgwriter wakeups

2012-03-02 Thread Heikki Linnakangas
On 20.02.2012 00:18, Robert Haas wrote: On Sun, Feb 19, 2012 at 4:11 PM, Simon Riggssi...@2ndquadrant.com wrote: On Sun, Feb 19, 2012 at 8:15 PM, Robert Haasrobertmh...@gmail.com wrote: On Sun, Feb 19, 2012 at 1:53 PM, Simon Riggssi...@2ndquadrant.com wrote: Recent changes for power

Re: [HACKERS] pg_basebackup -x stream from the standby gets stuck

2012-03-02 Thread Magnus Hagander
On Tue, Feb 28, 2012 at 09:22, Fujii Masao masao.fu...@gmail.com wrote: On Thu, Feb 23, 2012 at 1:02 AM, Magnus Hagander mag...@hagander.net wrote: On Tue, Feb 7, 2012 at 12:30, Fujii Masao masao.fu...@gmail.com wrote: Hi,

Re: [HACKERS] autovacuum locks

2012-03-02 Thread Alvaro Herrera
Excerpts from Gregg Jaskiewicz's message of vie mar 02 08:22:22 -0300 2012: Looking at the system bit more now, it look like 'waiting' states are changing for both the query and autovacuum in pg_stat_activity. But very slowly. It looks like they both got into that sort of state that keeps

Re: [HACKERS] review: CHECK FUNCTION statement

2012-03-02 Thread Alvaro Herrera
Excerpts from Pavel Stehule's message of vie mar 02 05:29:26 -0300 2012: you cannot to check trigger function without assigned relation - TupleDescription should be assigned to NEW and OLD variables. Oh, I see, that makes sense. After mulling over this a bit, I'm dubious about having two

Re: [HACKERS] Patch pg_is_in_backup()

2012-03-02 Thread Gilles Darold
Le 03/02/2012 10:52, Magnus Hagander a écrit : On Fri, Feb 3, 2012 at 10:47, Fujii Masao masao.fu...@gmail.com wrote: On Fri, Feb 3, 2012 at 6:10 PM, Bernd Helmle maili...@oopsware.de wrote: --On 3. Februar 2012 13:21:11 +0900 Fujii Masao masao.fu...@gmail.com wrote: It seems to be more

Re: [HACKERS] review: CHECK FUNCTION statement

2012-03-02 Thread Pavel Stehule
Hello 2012/3/2 Alvaro Herrera alvhe...@commandprompt.com: Excerpts from Pavel Stehule's message of vie mar 02 05:29:26 -0300 2012: you cannot to check trigger function without assigned relation - TupleDescription should be assigned to NEW and OLD variables. Oh, I see, that makes sense.

Re: [HACKERS] autovacuum locks

2012-03-02 Thread Robert Haas
On Fri, Mar 2, 2012 at 6:22 AM, Gregg Jaskiewicz gryz...@gmail.com wrote: Looking at the system bit more now, it look like 'waiting' states are changing for both the query and autovacuum in pg_stat_activity. But very slowly. It looks like they both got into that sort of state that keeps them

Re: [HACKERS] autovacuum locks

2012-03-02 Thread Tom Lane
Alvaro Herrera alvhe...@commandprompt.com writes: Right. I think I can explain how this locking works: autovacuum needs a cleanup lock on the page being processed, which is a special exclusive lock which also requires that no one is holding a pin on the buffer. Any process running a query

Re: [HACKERS] autovacuum locks

2012-03-02 Thread Kevin Grittner
Tom Lane t...@sss.pgh.pa.us wrote: So the real problem is slow removal of prepared transactions, which most likely is an application logic problem. It's certainly not autovac's fault. Yeah, I've seen way too much Java code lately which fails to close ResultSet or Statement (which includes

Re: [HACKERS] ECPG FETCH readahead

2012-03-02 Thread Noah Misch
On Thu, Dec 29, 2011 at 10:46:23AM +0100, Boszormenyi Zoltan wrote: 2011-11-16 20:51 keltez?ssel, Boszormenyi Zoltan ?rta: 2010-10-14 11:56 keltez?ssel, Boszormenyi Zoltan ?rta: On Thu, Jun 24, 2010 at 8:19 AM, Michael Meskes mes...@postgresql.org wrote: On Thu, Jun 24, 2010 at

Re: [HACKERS] Collect frequency statistics for arrays

2012-03-02 Thread Tom Lane
Still working through this patch ... there are some things that bother me about the entries being made in pg_statistic: 1. You re-used STATISTIC_KIND_MCELEM for something that, while similar to tsvector's usage, is not the same. In particular, tsvector adds two extra elements to the stanumbers

Re: [HACKERS] 16-bit page checksums for 9.2

2012-03-02 Thread Peter Geoghegan
On 23 February 2012 01:39, Robert Haas robertmh...@gmail.com wrote: Thanks for testing this.  The graph obscures a bit how much percentage change we're talking about here - could you post the raw tps numbers? Sorry for not following up on this until now. The report is available from:

Re: [HACKERS] Re: pg_stat_statements normalisation without invasive changes to the parser (was: Next steps on pg_stat_statements normalisation)

2012-03-02 Thread Josh Berkus
It would probably be prudent to concentrate on getting the core infrastructure committed first. That way, we at least know that if this doesn't get into 9.2, we can work on getting it into 9.3 knowing that once committed, people won't have to wait over a year at the very I don't see why we

Re: [HACKERS] Re: pg_stat_statements normalisation without invasive changes to the parser (was: Next steps on pg_stat_statements normalisation)

2012-03-02 Thread Tom Lane
Josh Berkus j...@agliodbs.com writes: It would probably be prudent to concentrate on getting the core infrastructure committed first. That way, we at least know that if this doesn't get into 9.2, we can work on getting it into 9.3 knowing that once committed, people won't have to wait over a

Re: [HACKERS] Runtime SHAREDIR for testing CREATE EXTENSION

2012-03-02 Thread Peter Eisentraut
On tis, 2012-02-28 at 11:00 -0800, Daniel Farina wrote: I'd really like to support libraries (C or otherwise) of multiple versions at the same time, when the underlying library permits. What's preventing you from doing that now? You need to name all the symbols differently, of course. --

Re: [HACKERS] Runtime SHAREDIR for testing CREATE EXTENSION

2012-03-02 Thread Daniel Farina
On Fri, Mar 2, 2012 at 10:37 AM, Peter Eisentraut pete...@gmx.net wrote: On tis, 2012-02-28 at 11:00 -0800, Daniel Farina wrote: I'd really like to support libraries (C or otherwise) of multiple versions at the same time, when the underlying library permits. What's preventing you from doing

Re: [HACKERS] review of: collation for (expr)

2012-03-02 Thread Peter Eisentraut
On tor, 2012-03-01 at 20:30 -0500, Jaime Casanova wrote: besides a clash in the oid and the value of leakproof missing in the pg_proc entry, everything works fine. Fixed. The only thing is that i don't see a reason for these includes in src/backend/utils/adt/misc.c: + #include

Re: [HACKERS] Re: pg_stat_statements normalisation without invasive changes to the parser (was: Next steps on pg_stat_statements normalisation)

2012-03-02 Thread Robert Haas
On Fri, Mar 2, 2012 at 12:48 PM, Tom Lane t...@sss.pgh.pa.us wrote: Josh Berkus j...@agliodbs.com writes: It would probably be prudent to concentrate on getting the core infrastructure committed first. That way, we at least know that if this doesn't get into 9.2, we can work on getting it into

Re: [HACKERS] 16-bit page checksums for 9.2

2012-03-02 Thread Simon Riggs
On Fri, Mar 2, 2012 at 2:11 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: One thing I'm not too sure about is how to extend the page format to handle optional features. For example, suppose we want to add 2 bytes to the page header for a checksum (or 4

Re: [HACKERS] 16-bit page checksums for 9.2

2012-03-02 Thread Jeff Janes
On Sun, Feb 19, 2012 at 1:49 PM, Simon Riggs si...@2ndquadrant.com wrote: On Thu, Feb 16, 2012 at 11:16 AM, Simon Riggs si...@2ndquadrant.com wrote: v8 attached v10 attached. This patch covers all the valid concerns discussed and has been extensively tested. If I turn wal_level to

Re: [HACKERS] Re: pg_stat_statements normalisation without invasive changes to the parser (was: Next steps on pg_stat_statements normalisation)

2012-03-02 Thread Simon Riggs
On Fri, Mar 2, 2012 at 5:48 PM, Tom Lane t...@sss.pgh.pa.us wrote: Josh Berkus j...@agliodbs.com writes: It would probably be prudent to concentrate on getting the core infrastructure committed first. That way, we at least know that if this doesn't get into 9.2, we can work on getting it into

Re: [HACKERS] Re: pg_stat_statements normalisation without invasive changes to the parser (was: Next steps on pg_stat_statements normalisation)

2012-03-02 Thread Josh Berkus
We'll get to it in due time. In case you haven't noticed, there's a lot of stuff in this commitfest. And I don't follow the logic that says that because Simon is trying to push through a not-ready-for-commit patch we should drop our standards for other patches. What I'm pointing out is

Re: [HACKERS] Re: pg_stat_statements normalisation without invasive changes to the parser (was: Next steps on pg_stat_statements normalisation)

2012-03-02 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Fri, Mar 2, 2012 at 12:48 PM, Tom Lane t...@sss.pgh.pa.us wrote: We'll get to it in due time. In case you haven't noticed, there's a lot of stuff in this commitfest. And I don't follow the logic that says that because Simon is trying to push

Re: [HACKERS] Re: pg_stat_statements normalisation without invasive changes to the parser (was: Next steps on pg_stat_statements normalisation)

2012-03-02 Thread Tom Lane
Josh Berkus j...@agliodbs.com writes: This is exactly why I'm not keen on checksums for 9.2. We've reached the point where the attention on the checksum patch is pushing aside other patches which are more ready and have had more work. IMO the reason why it's sucking so much attention is

Re: [HACKERS] index-only quals vs. security_barrier views

2012-03-02 Thread Noah Misch
On Thu, Feb 09, 2012 at 12:02:29PM -0500, Robert Haas wrote: When Heikki worked up his original index-only scan patches (which didn't end up looking much like what eventually got committed), he had the notion of an index-only qual. That is, given a query like this: select sum(1) from foo

Re: [HACKERS] Collect frequency statistics for arrays

2012-03-02 Thread Tom Lane
I wrote: ... So my preference is to align the two definitions of STATISTIC_KIND_MCELEM by adding a null-element frequency to tsvector's usage (where it'll always be zero) and getting rid of the average distinct element count here. Actually, there's a way we can do this without code changes in

[HACKERS] sortsupport for text

2012-03-02 Thread Robert Haas
I decided to investigate the possible virtues of allowing text to use the sortsupport infrastructure, since strings are something people often want to sort. I generated 100,000 random alphanumeric strings, each 30 characters in length, and loaded them into a single-column table, froze it, ran

Re: [HACKERS] COPY with hints, rebirth

2012-03-02 Thread Noah Misch
On Fri, Mar 02, 2012 at 08:46:45AM +, Simon Riggs wrote: On Thu, Mar 1, 2012 at 8:49 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: It's still broken: [BEGIN;TRUNCATE;SAVEPOINT;COPY;ROLLBACK TO] So this approach isn't the one... The COPY FREEZE patch provides a

Re: [HACKERS] COPY with hints, rebirth

2012-03-02 Thread Kevin Grittner
Noah Misch n...@leadboat.com wrote: Incidentally, I contend that we should write frozen tuples to new/truncated tables unconditionally. +1 The current behavior of making old snapshots see the table as empty violates atomicity at least as badly as letting those snapshots see the

Re: [HACKERS] Runtime SHAREDIR for testing CREATE EXTENSION

2012-03-02 Thread Robert Haas
On Fri, Mar 2, 2012 at 1:53 PM, Daniel Farina dan...@heroku.com wrote: On Fri, Mar 2, 2012 at 10:37 AM, Peter Eisentraut pete...@gmx.net wrote: On tis, 2012-02-28 at 11:00 -0800, Daniel Farina wrote: I'd really like to support libraries (C or otherwise) of multiple versions at the same time,

Re: [HACKERS] Re: pg_stat_statements normalisation without invasive changes to the parser (was: Next steps on pg_stat_statements normalisation)

2012-03-02 Thread Simon Riggs
On Fri, Mar 2, 2012 at 8:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: Josh Berkus j...@agliodbs.com writes: This is exactly why I'm not keen on checksums for 9.2.  We've reached the point where the attention on the checksum patch is pushing aside other patches which are more ready and have had

Re: [HACKERS] Runtime SHAREDIR for testing CREATE EXTENSION

2012-03-02 Thread Daniel Farina
On Fri, Mar 2, 2012 at 1:50 PM, Robert Haas robertmh...@gmail.com wrote: But is it unsurmountable? -- dlsym returns a function pointer, and one would build up the operator table for the version of the extension at hand, so one might have ltree version 1.01 and ltree version 2.3 fields in the

Re: [HACKERS] possible new option for wal_sync_method

2012-03-02 Thread Dan Scales
Hi, Got any result so far? I measured the results with barrier=0, and yes, you are correct -- it seems that most of the benefit of the open_direct wal_sync_method is probably from not doing the barrier operation at the end of fsync(): wal_sync_method

Re: [HACKERS] COPY with hints, rebirth

2012-03-02 Thread Simon Riggs
On Fri, Mar 2, 2012 at 8:58 PM, Noah Misch n...@leadboat.com wrote: On Fri, Mar 02, 2012 at 08:46:45AM +, Simon Riggs wrote: On Thu, Mar 1, 2012 at 8:49 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: It's still broken: [BEGIN;TRUNCATE;SAVEPOINT;COPY;ROLLBACK TO] So

Re: [HACKERS] COPY with hints, rebirth

2012-03-02 Thread Kevin Grittner
Simon Riggs si...@2ndquadrant.com wrote: I like Marti's idea. At present, making his idea work could easily mean checksums sink, so not sure whether to attempt to make that work in detail. For my part, improving bulk load performance and TRUNCATE transactional semantics would trump

Re: [HACKERS] Command Triggers, patch v11

2012-03-02 Thread Thom Brown
On 2 March 2012 22:32, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Hi, Please find attached v13 of the command trigger patch, fixing most of known items and rebased against master. Two important items remain to be done, but I figured I should keep you posted in the meantime. Thanks

Re: [HACKERS] Re: pg_stat_statements normalisation without invasive changes to the parser (was: Next steps on pg_stat_statements normalisation)

2012-03-02 Thread Robert Haas
On Fri, Mar 2, 2012 at 4:56 PM, Simon Riggs si...@2ndquadrant.com wrote: Checksums patch isn't sucking much attention at all but admittedly there are some people opposed to the patch that want to draw out the conversation until the patch is rejected, Wow. Sounds like a really shitty thing for

Re: [HACKERS] Command Triggers, patch v11

2012-03-02 Thread Thom Brown
On 2 March 2012 23:33, Thom Brown t...@linux.com wrote: On 2 March 2012 22:32, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: test=# CREATE TABLE badname (id int, a int, b text); ERROR:  invalid relation name: badname test=# CREATE TABLE badname AS SELECT 1::int id, 1::int a, ''::text b;

Re: [HACKERS] Command Triggers, patch v11

2012-03-02 Thread Robert Haas
On Tue, Feb 28, 2012 at 10:09 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Tom Lane t...@sss.pgh.pa.us wrote: This seems over-complicated.  Triggers on tables do not have alterable properties, why should command triggers?  I vote for       CREATE COMMAND TRIGGER name ... properties

Re: [HACKERS] Command Triggers, patch v11

2012-03-02 Thread anara...@anarazel.de
anara...@anarazel.de and...@anarazel.de schrieb: Thom Brown t...@linux.com schrieb: On 2 March 2012 23:33, Thom Brown t...@linux.com wrote: On 2 March 2012 22:32, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: test=# CREATE TABLE badname (id int, a int, b text); ERROR:  invalid relation

Re: [HACKERS] Command Triggers, patch v11

2012-03-02 Thread Thom Brown
On 3 March 2012 00:08, Thom Brown t...@linux.com wrote: On 2 March 2012 23:33, Thom Brown t...@linux.com wrote: On 2 March 2012 22:32, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: test=# CREATE TABLE badname (id int, a int, b text); ERROR:  invalid relation name: badname test=# CREATE TABLE

Re: [HACKERS] review: CHECK FUNCTION statement

2012-03-02 Thread Alvaro Herrera
Excerpts from Pavel Stehule's message of mar feb 28 16:30:58 -0300 2012: Hello Dne 28. února 2012 17:48 Alvaro Herrera alvhe...@commandprompt.com napsal(a): I have a few comments about this patch: I didn't like the fact that the checker calling infrastructure uses SPI instead of

Re: [HACKERS] review: CHECK FUNCTION statement

2012-03-02 Thread Pavel Stehule
Hello It wasn't all that difficult -- see below.  While at this, I have a question: how attached you are to the current return format for CHECK FUNCTION? TupleDescr is created by language creator. This ensure exactly expected format, because there are no possible registry check function with

Re: [HACKERS] review: CHECK FUNCTION statement

2012-03-02 Thread Pavel Stehule
2012/3/3 Pavel Stehule pavel.steh...@gmail.com: Hello It wasn't all that difficult -- see below.  While at this, I have a question: how attached you are to the current return format for CHECK FUNCTION? TupleDescr is created by language creator. This ensure exactly expected format, because

Re: [HACKERS] review: CHECK FUNCTION statement

2012-03-02 Thread Alvaro Herrera
Excerpts from Pavel Stehule's message of sáb mar 03 02:45:06 -0300 2012: Without correct registration you cannot to call PL check function directly simply. I don't thing so this is good price for removing a few SPI lines. I don't understand why you don't like SPI. I don't dislike SPI in

Re: [HACKERS] review: CHECK FUNCTION statement

2012-03-02 Thread Pavel Stehule
2012/3/3 Alvaro Herrera alvhe...@commandprompt.com: Excerpts from Pavel Stehule's message of sáb mar 03 02:45:06 -0300 2012: Without correct registration you cannot to call PL check function directly simply. I don't thing so this is good price for removing a few SPI lines. I don't