Re: [HACKERS] Should pg_dump dump larger tables first?

2013-01-31 Thread Dimitri Fontaine
Tom Lane t...@sss.pgh.pa.us writes: Also, it's far from obvious to me that largest first is the best rule anyhow; it's likely to be more complicated than that. But anyway, the right place to add this sort of consideration is in pg_restore --parallel, not pg_dump. I don't know how hard it

Re: [HACKERS] Should pg_dump dump larger tables first?

2013-01-31 Thread John R Pierce
On 1/31/2013 2:06 AM, Dimitri Fontaine wrote: Loading several big'o'tables in parallel tend not to give benefits in the tests I've done so far, but that might be an artefact of python multi threading, I will do some testing with proper tooling later. or insufficient IO parallelism in your disk

Re: [HACKERS] pg_dump --pretty-print-views

2013-01-31 Thread Dimitri Fontaine
Andrew Dunstan and...@dunslane.net writes: Well, we could actually set the wrap value to 0, which would mean always wrap. That wouldn't be making any assumption about the user's terminal window size ;-) +1 Personally I find the wrapped case MUCH more readable. I guess anything is an

Re: [HACKERS] backend hangs at immediate shutdown

2013-01-31 Thread MauMau
As I promised yesterday, I'll show you the precise call stack: #0 0x003fa0cf542e in __lll_lock_wait_private () from /lib64/libc.so.6 #1 0x003fa0c7bed5 in _L_lock_9323 () from /lib64/libc.so.6 #2 0x003fa0c797c6 in malloc () from /lib64/libc.so.6 #3 0x003fa0c2fd99 in

[HACKERS] [PATCH] HOT on tables with oid indexes broken

2013-01-31 Thread Andres Freund
Hi, The fklocks patch moved HeapSatisfiesHOTandKeyUpdate (or rather HeapSatisfiesHOTUpdate back then) to be called way earlier in heap_update as its needed to know which lock level is required. Unfortunately the oid of the new tuple isn't yet setup at that point. Due to this everytime there's

Re: [HACKERS] backend hangs at immediate shutdown

2013-01-31 Thread MauMau
From: Tom Lane t...@sss.pgh.pa.us MauMau maumau...@gmail.com writes: How about the case where some backend crashes due to a bug of PostgreSQL? In this case, postmaster sends SIGQUIT to all backends, too. The instance is expected to disappear cleanly and quickly. Doesn't the hanging backend

Re: [HACKERS] Performance Improvement by reducing WAL for Update Operation

2013-01-31 Thread Amit Kapila
On Wednesday, January 30, 2013 8:32 PM Amit Kapila wrote: On Tuesday, January 29, 2013 7:42 PM Amit Kapila wrote: On Tuesday, January 29, 2013 3:53 PM Heikki Linnakangas wrote: On 29.01.2013 11:58, Amit Kapila wrote: Can there be another way with which current patch code can be made

[HACKERS] Suggestion: Issue warning when calling SET TRANSACTION outside transaction block

2013-01-31 Thread Morten Hustveit
Hi! Calling SET TRANSACTION ISOLATION LEVEL ... outside a transaction block has no effect. This is unlike LOCK ... and DECLARE foo CURSOR FOR ..., which both raise an error. This is also unlike MySQL, where such a statement will affect the next transaction performed. There's some risk of data

Re: [HACKERS] Strange Windows problem, lock_timeout test request

2013-01-31 Thread Zoltán Böszörményi
2013-01-30 17:45 keltezéssel, Zoltán Böszörményi írta: 2013-01-30 16:06 keltezéssel, Hari Babu írta: On Wednesday, January 30, 2013 7:59 PM Zoltán Böszörményi wrote: 2013-01-28 15:20 keltezéssel, Hari Babu írta: 2. regress check failed because the expected .out file is not updated properly.

Re: [HACKERS] information schema parameter_default implementation

2013-01-31 Thread Ali Dar
On Wed, Jan 9, 2013 at 4:28 PM, Peter Eisentraut pete...@gmx.net wrote: Here is an implementation of the information_schema.parameters.parameter_default column. I ended up writing a C function to decode the whole thing from the system catalogs, because it was too complicated in SQL, so I

Re: [HACKERS] information schema parameter_default implementation

2013-01-31 Thread Ali Dar
Another thing I forget: The patch does not apply because of the changes in catversion.h Regards, Ali Dar On Thu, Jan 31, 2013 at 6:59 PM, Ali Dar ali.munir@gmail.com wrote: On Wed, Jan 9, 2013 at 4:28 PM, Peter Eisentraut pete...@gmx.net wrote: Here is an implementation of the

Re: [HACKERS] Strange Windows problem, lock_timeout test request

2013-01-31 Thread Alvaro Herrera
Zoltán Böszörményi wrote: I have found a little time to look into this problem and found a way to make pg_regress use prepared_xacts_1.out. I had to change line 2193 in pg_regress.c from fputs(max_prepared_transactions = 2\n, pg_conf); to fputs(max_prepared_transactions = 0\n,

Re: [HACKERS] Strange Windows problem, lock_timeout test request

2013-01-31 Thread Zoltán Böszörményi
2013-01-31 15:22 keltezéssel, Alvaro Herrera írta: Zoltán Böszörményi wrote: I have found a little time to look into this problem and found a way to make pg_regress use prepared_xacts_1.out. I had to change line 2193 in pg_regress.c from fputs(max_prepared_transactions = 2\n, pg_conf);

Re: [HACKERS] Strange Windows problem, lock_timeout test request

2013-01-31 Thread Andrew Dunstan
On 01/31/2013 09:55 AM, Zoltán Böszörményi wrote: 2013-01-31 15:22 keltezéssel, Alvaro Herrera írta: Zoltán Böszörményi wrote: I have found a little time to look into this problem and found a way to make pg_regress use prepared_xacts_1.out. I had to change line 2193 in pg_regress.c from

Re: [HACKERS] Strange Windows problem, lock_timeout test request

2013-01-31 Thread Tom Lane
=?ISO-8859-1?Q?Zolt=E1n_B=F6sz=F6rm=E9nyi?= z...@cybertec.at writes: 2013-01-31 15:22 keltezéssel, Alvaro Herrera írta: That sounds a lot more difficult than just using make installcheck and configure the running server with zero prepared xacts ... It didn't occur to me to use make

Re: [HACKERS] Should pg_dump dump larger tables first?

2013-01-31 Thread Jeff Janes
On Tue, Jan 29, 2013 at 3:34 PM, Tom Lane t...@sss.pgh.pa.us wrote: David Rowley dgrowle...@gmail.com writes: If pg_dump was to still follow the dependencies of objects, would there be any reason why it shouldn't backup larger tables first? Pretty much every single discussion/complaint about

Re: [HACKERS] Should pg_dump dump larger tables first?

2013-01-31 Thread Christopher Browne
On Thu, Jan 31, 2013 at 5:06 AM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Tom Lane t...@sss.pgh.pa.us writes: Also, it's far from obvious to me that largest first is the best rule anyhow; it's likely to be more complicated than that. But anyway, the right place to add this sort of

Re: [HACKERS] Strange Windows problem, lock_timeout test request

2013-01-31 Thread Zoltán Böszörményi
2013-01-31 16:39 keltezéssel, Tom Lane írta: =?ISO-8859-1?Q?Zolt=E1n_B=F6sz=F6rm=E9nyi?= z...@cybertec.at writes: 2013-01-31 15:22 keltezéssel, Alvaro Herrera írta: That sounds a lot more difficult than just using make installcheck and configure the running server with zero prepared xacts ...

Re: [HACKERS] Strange Windows problem, lock_timeout test request

2013-01-31 Thread Tom Lane
=?ISO-8859-2?Q?Zolt=E1n_B=F6sz=F6rm=E9nyi?= z...@cybertec.at writes: Thanks. A question though: how does make check or make installcheck chooses between the *.out and its different *_N.out incarnations? I couldn't find traces of prepared_xacts_1.out in any file saying this is the one to be

Re: [HACKERS] autovacuum not prioritising for-wraparound tables

2013-01-31 Thread Alvaro Herrera
Christopher Browne escribió: On Fri, Jan 25, 2013 at 12:00 PM, Andres Freund and...@2ndquadrant.com wrote: I'd be inclined to do something a bit more sophisticated than just age(relfrozenxid) for wraparound; I'd be inclined to kick off large tables' wraparound vacuums earlier than those for

Re: [HACKERS] autovacuum not prioritising for-wraparound tables

2013-01-31 Thread Alvaro Herrera
Alvaro Herrera escribió: Okay, here's a patch along these lines. I haven't considered Jim's suggestion downthread about discounting dead tuples from relpages; maybe we can do that by subtracting the pages attributed to dead ones, estimating via tuple density (reltuples/relpages). Patch

Re: [HACKERS] autovacuum not prioritising for-wraparound tables

2013-01-31 Thread Robert Haas
On Thu, Jan 31, 2013 at 2:40 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Alvaro Herrera escribió: Okay, here's a patch along these lines. I haven't considered Jim's suggestion downthread about discounting dead tuples from relpages; maybe we can do that by subtracting the pages

Re: [HACKERS] autovacuum not prioritising for-wraparound tables

2013-01-31 Thread Robert Haas
On Thu, Jan 31, 2013 at 2:36 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Instead, what I propose (and is not really in the patch), as a backpatchable item, is an approach in which the functions to compute each rel's Browne strength and sort are hooks. Normal behavior is not to sort at

Re: [HACKERS] autovacuum not prioritising for-wraparound tables

2013-01-31 Thread Tom Lane
Alvaro Herrera alvhe...@2ndquadrant.com writes: Instead, what I propose (and is not really in the patch), as a backpatchable item, is an approach in which the functions to compute each rel's Browne strength and sort are hooks. Normal behavior is not to sort at all, as currently, and sites

Re: [HACKERS] autovacuum not prioritising for-wraparound tables

2013-01-31 Thread Alvaro Herrera
Tom Lane escribió: Alvaro Herrera alvhe...@2ndquadrant.com writes: Instead, what I propose (and is not really in the patch), as a backpatchable item, is an approach in which the functions to compute each rel's Browne strength and sort are hooks. Normal behavior is not to sort at all, as

Re: [HACKERS] autovacuum not prioritising for-wraparound tables

2013-01-31 Thread Alvaro Herrera
Robert Haas escribió: On Thu, Jan 31, 2013 at 2:40 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Alvaro Herrera escribió: Okay, here's a patch along these lines. I haven't considered Jim's suggestion downthread about discounting dead tuples from relpages; maybe we can do that by

Re: [HACKERS] autovacuum not prioritising for-wraparound tables

2013-01-31 Thread Christopher Browne
On Thu, Jan 31, 2013 at 3:18 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Robert Haas escribió: On Thu, Jan 31, 2013 at 2:40 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Alvaro Herrera escribió: Okay, here's a patch along these lines. I haven't considered Jim's suggestion

Re: backend hangs at immediate shutdown (Re: [HACKERS] Back-branch update releases coming in a couple weeks)

2013-01-31 Thread Peter Eisentraut
On 1/30/13 9:11 AM, MauMau wrote: When I ran pg_ctl stop -mi against the primary, some applications connected to the primary did not stop. The cause was that the backends was deadlocked in quickdie() with some call stack like the following. I'm sorry to have left the stack trace file on the

Re: [HACKERS] Re: proposal: a width specification for s specifier (format function), fix behave when positional and ordered placeholders are used

2013-01-31 Thread Pavel Stehule
Hello 2013/1/29 Dean Rasheed dean.a.rash...@gmail.com: On 29 January 2013 08:19, Dean Rasheed dean.a.rash...@gmail.com wrote: * The width field is optional, even if the '-' flag is specified. So '%-s' is perfectly legal and should be interpreted as '%s'. The current implementation treats it

[HACKERS] Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: [DOCS] Small clarification in 34.41. schemata)

2013-01-31 Thread Peter Eisentraut
On 1/9/13 8:56 PM, Tom Lane wrote: However, it seems to me that this behavior is actually wrong for our purposes, as it represents a too-literal reading of the spec. The SQL standard has no concept of privileges on schemas, only ownership. We do have privileges on schemas, so it seems to me

Re: [HACKERS] Strange Windows problem, lock_timeout test request

2013-01-31 Thread Zoltán Böszörményi
2013-01-31 19:38 keltezéssel, Tom Lane írta: =?ISO-8859-2?Q?Zolt=E1n_B=F6sz=F6rm=E9nyi?= z...@cybertec.at writes: Thanks. A question though: how does make check or make installcheck chooses between the *.out and its different *_N.out incarnations? I couldn't find traces of prepared_xacts_1.out

Re: [HACKERS] json api WIP patch

2013-01-31 Thread Peter Eisentraut
On 1/10/13 6:42 PM, Andrew Dunstan wrote: This updated patch contains all the intended functionality, including operators for the json_get_path functions, so you can say things like select jsonval-array['f1','0','f2] ... I would like to not create any - operators, so that that syntax

Re: [HACKERS] json api WIP patch

2013-01-31 Thread Andrew Dunstan
On 01/31/2013 05:06 PM, Peter Eisentraut wrote: On 1/10/13 6:42 PM, Andrew Dunstan wrote: This updated patch contains all the intended functionality, including operators for the json_get_path functions, so you can say things like select jsonval-array['f1','0','f2] ... I would like to

Re: backend hangs at immediate shutdown (Re: [HACKERS] Back-branch update releases coming in a couple weeks)

2013-01-31 Thread MauMau
From: Peter Eisentraut pete...@gmx.net On 1/30/13 9:11 AM, MauMau wrote: When I ran pg_ctl stop -mi against the primary, some applications connected to the primary did not stop. The cause was that the backends was deadlocked in quickdie() with some call stack like the following. I'm sorry to

Re: backend hangs at immediate shutdown (Re: [HACKERS] Back-branch update releases coming in a couple weeks)

2013-01-31 Thread Kevin Grittner
MauMau maumau...@gmail.com wrote: Just doing pkill postgres will unexpectedly terminate postgres of other instances. Not if you run each instance under a different OS user, and execute pkill with the right user.  (Never use root for that!)  This is just one of the reasons that you should not

Re: [HACKERS] json api WIP patch

2013-01-31 Thread Merlin Moncure
On Thu, Jan 31, 2013 at 4:20 PM, Andrew Dunstan and...@dunslane.net wrote: On 01/31/2013 05:06 PM, Peter Eisentraut wrote: On 1/10/13 6:42 PM, Andrew Dunstan wrote: This updated patch contains all the intended functionality, including operators for the json_get_path functions, so you can

Re: [HACKERS] json api WIP patch

2013-01-31 Thread Tom Lane
Merlin Moncure mmonc...@gmail.com writes: On Thu, Jan 31, 2013 at 4:20 PM, Andrew Dunstan and...@dunslane.net wrote: On 01/31/2013 05:06 PM, Peter Eisentraut wrote: I would like to not create any - operators, so that that syntax could be used in the future for method invocation or something

Re: [HACKERS] json api WIP patch

2013-01-31 Thread David E. Wheeler
On Jan 31, 2013, at 2:20 PM, Andrew Dunstan and...@dunslane.net wrote: I'm happy to take opinions about this, and I expected some bikeshedding, but your reaction is contrary to everything others have told me. Mostly they love the operators. I guess that '~' and '~' would work as well as

Re: [HACKERS] json api WIP patch

2013-01-31 Thread Andrew Dunstan
On 01/31/2013 07:16 PM, David E. Wheeler wrote: On Jan 31, 2013, at 2:20 PM, Andrew Dunstan and...@dunslane.net wrote: I'm happy to take opinions about this, and I expected some bikeshedding, but your reaction is contrary to everything others have told me. Mostly they love the operators. I

Re: [HACKERS] json api WIP patch

2013-01-31 Thread Tom Lane
Andrew Dunstan and...@dunslane.net writes: On 01/31/2013 07:16 PM, David E. Wheeler wrote: I suppose that := is out of the question? Even if it were I would not on any account use it. As an old Ada programmer my mind just revolts at the idea of using this for anything but assignment. Ada

Re: [HACKERS] json api WIP patch

2013-01-31 Thread David E. Wheeler
On Jan 31, 2013, at 4:32 PM, Tom Lane t...@sss.pgh.pa.us wrote: Ada or no, its use in plpgsql would render that a seriously bad idea. I assumed that its use in function params would be the main reason not to use it. David -- Sent via pgsql-hackers mailing list

Re: [HACKERS] json api WIP patch

2013-01-31 Thread Gavin Flower
On 01/02/13 13:26, Andrew Dunstan wrote: On 01/31/2013 07:16 PM, David E. Wheeler wrote: On Jan 31, 2013, at 2:20 PM, Andrew Dunstan and...@dunslane.net wrote: I'm happy to take opinions about this, and I expected some bikeshedding, but your reaction is contrary to everything others have

Re: [HACKERS] Cascading replication: should we detect/prevent cycles?

2013-01-31 Thread Josh Berkus
If we're going to start installing safeguards against doing stupid things, there's a long list of scenarios that happen far more regularly than this ever will and cause far more damage. What's wrong with making it easier for sysadmins to troubleshoot things? Again, I'm not talking about

[HACKERS] Streaming-only cascading replica won't come up without writes on the master

2013-01-31 Thread Josh Berkus
Heikki, I thought this was only a 9.3 issue, but it turns out to be reproduceable on 9.2.2. Basically, I did: 1. master is queicent ... no writes occuring. 2. createded cascading replica (reprep1) from replica (repmaster) 3. reprep1 remains in recovery mode until a write occurs on master I've

[HACKERS] parameter info?

2013-01-31 Thread Andrew Dunstan
What's the best way for me to find out if a given parameter of a function is a constant? The context is that it's expensive to process, and in most cases will in fact be a constant, so if it is in fact a constant I'd like to process it once and stash the results. cheers andrew -- Sent

Re: [HACKERS] Cascading replication: should we detect/prevent cycles?

2013-01-31 Thread Josh Berkus
On 02/01/2013 12:01 PM, Josh Berkus wrote: If we're going to start installing safeguards against doing stupid things, there's a long list of scenarios that happen far more regularly than this ever will and cause far more damage. What's wrong with making it easier for sysadmins to

Re: [HACKERS] find libxml2 using pkg-config

2013-01-31 Thread Peter Eisentraut
On Mon, 2013-01-14 at 10:25 -0500, Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: The attached patch looks for pkg-config first, and finds libxml2 using that if available. Otherwise it falls back to using xml2-config. What happens if pkg-config is installed but doesn't know

Re: [HACKERS] recursive view syntax

2013-01-31 Thread Peter Eisentraut
On Fri, 2013-01-18 at 10:00 -0500, Stephen Frost wrote: I've done another review of this patch and it looks pretty good to me. My only complaint is that there isn't a single comment inside makeRecursiveViewSelect(). Added some of that and committed. One other thought is- I'm guessing this

Re: [HACKERS] parameter info?

2013-01-31 Thread Pavel Stehule
hello 2013/2/1 Andrew Dunstan and...@dunslane.net: What's the best way for me to find out if a given parameter of a function is a constant? The context is that it's expensive to process, and in most cases will in fact be a constant, so if it is in fact a constant I'd like to process it once

Re: [HACKERS] parameter info?

2013-01-31 Thread Tom Lane
Andrew Dunstan and...@dunslane.net writes: What's the best way for me to find out if a given parameter of a function is a constant? The context is that it's expensive to process, and in most cases will in fact be a constant, so if it is in fact a constant I'd like to process it once and

Re: [HACKERS] Suggestion: Issue warning when calling SET TRANSACTION outside transaction block

2013-01-31 Thread Amit Kapila
On Wednesday, January 30, 2013 6:53 AM Morten Hustveit wrote: Hi! Calling SET TRANSACTION ISOLATION LEVEL ... outside a transaction block has no effect. This is unlike LOCK ... and DECLARE foo CURSOR FOR ..., which both raise an error. This is also unlike MySQL, where such a statement

Re: [HACKERS] proposal - assign result of query to psql variable

2013-01-31 Thread Pavel Stehule
Hello can you look, please, on updated version - it respects Tom's proposal and it is significantly reduced? Thank you Pavel Stehule 2013/1/28 Pavel Stehule pavel.steh...@gmail.com: Hello 2013/1/26 Tom Lane t...@sss.pgh.pa.us: Andrew Dunstan and...@dunslane.net writes: +1. This looks

Re: [HACKERS] Re: proposal: a width specification for s specifier (format function), fix behave when positional and ordered placeholders are used

2013-01-31 Thread Pavel Stehule
Hello minor update - fix align NULL for %L Regards Pavel 2013/1/31 Pavel Stehule pavel.steh...@gmail.com: Hello 2013/1/29 Dean Rasheed dean.a.rash...@gmail.com: On 29 January 2013 08:19, Dean Rasheed dean.a.rash...@gmail.com wrote: * The width field is optional, even if the '-' flag is

Re: [HACKERS] Cascading replication: should we detect/prevent cycles?

2013-01-31 Thread Peter Geoghegan
On Sunday, 27 January 2013, Robert Haas robertmh...@gmail.com wrote: If we're going to start installing safeguards against doing stupid things, there's a long list of scenarios that happen far more regularly than this ever will and cause far more damage. +1 -- Regards, Peter Geoghegan