Re: [HACKERS] global temporary tables

On Sat, 2010-04-24 at 19:01 -0400, Robert Haas wrote: > There is one major problem, though: assigning a > scratch relfilenode to the temporary table requires generating an OID, > which we currently have no way to allow on the standby. Why not have an unlogged counter, which resets each system sta

Re: [HACKERS] psql: Add setting to make '+' on \d implicit

--On 23. April 2010 14:34:45 -0700 Steve Atkins wrote: Or more generally an ability to set aliases via .psqlrc similar to \set, maybe? \alias "\d-" = "\d" \alias "\d" = "\d+" You mean something like this? -- Thanks

Re: [HACKERS] global temporary tables

On Sun, Apr 25, 2010 at 3:49 AM, Simon Riggs wrote: > On Sat, 2010-04-24 at 19:01 -0400, Robert Haas wrote: > >> There is one major problem, though: assigning a >> scratch relfilenode to the temporary table requires generating an OID, >> which we currently have no way to allow on the standby. > >

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

On Sat, Apr 24, 2010 at 5:17 AM, Simon Riggs wrote: >> Both Heikki and I objected to that patch. > > Please explain your objection, based upon the patch and my explanations. Well, we objected to the locking. Having reread the patch a few times though, I think I'm starting to wrap my head around

Re: [HACKERS] global temporary tables

On Sat, Apr 24, 2010 at 11:02 PM, Tom Lane wrote: > Robert Haas writes: >> Pushing it into the RelFileNode has some advantages in terms of being >> able to get at the information from everywhere, but one thing that >> makes me think that's probably not a good decision is that we somtimes >> WAL-l

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

On Sun, 2010-04-25 at 06:53 -0400, Robert Haas wrote: > On Sat, Apr 24, 2010 at 5:17 AM, Simon Riggs wrote: > >> Both Heikki and I objected to that patch. > > > > Please explain your objection, based upon the patch and my explanations. > > Well, we objected to the locking. Having reread the patc

Re: [HACKERS] global temporary tables

On Sun, 2010-04-25 at 06:50 -0400, Robert Haas wrote: > On Sun, Apr 25, 2010 at 3:49 AM, Simon Riggs wrote: > > On Sat, 2010-04-24 at 19:01 -0400, Robert Haas wrote: > > > >> There is one major problem, though: assigning a > >> scratch relfilenode to the temporary table requires generating an OID,

Re: [HACKERS] global temporary tables

On Sun, Apr 25, 2010 at 8:57 AM, Simon Riggs wrote: >> I don't think that quite works, because the standby might assign a >> relfilenode number for a global temp table and then the master might >> subsequently assign the same relfilenode number to a regular table. >> We might be able to make that

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

On Sun, Apr 25, 2010 at 8:50 AM, Simon Riggs wrote: > On Sun, 2010-04-25 at 06:53 -0400, Robert Haas wrote: >> On Sat, Apr 24, 2010 at 5:17 AM, Simon Riggs wrote: >> >> Both Heikki and I objected to that patch. >> > >> > Please explain your objection, based upon the patch and my explanations. >>

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

Robert Haas writes: > On Sat, Apr 24, 2010 at 5:17 AM, Simon Riggs wrote: > Both Heikki and I objected to that patch. >> >> Please explain your objection, based upon the patch and my explanations. > Well, we objected to the locking. Not only is the locking overly complex, but it's outright wro

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

Simon Riggs writes: > [ v2 patch ] BTW, while I'm looking at this, why bother with the separate KnownAssignedXidsValid[] array? Wouldn't it be cheaper (certainly so in storage, probably so in access/update times) to have just the KnownAssignedXids[] array and store InvalidTransactionId in unused

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

On Sun, 2010-04-25 at 11:50 -0400, Tom Lane wrote: > Robert Haas writes: > > On Sat, Apr 24, 2010 at 5:17 AM, Simon Riggs wrote: > > Both Heikki and I objected to that patch. > >> > >> Please explain your objection, based upon the patch and my explanations. > > > Well, we objected to the lockin

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

Simon Riggs writes: > On Sun, 2010-04-25 at 11:50 -0400, Tom Lane wrote: >> This needs a redesign before it can be considered committable. I don't >> really care whether it makes things faster; it's too complicated and too >> poorly documented to be maintainable. > There are more than 60 lines o

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

On Sun, 2010-04-25 at 12:43 -0400, Tom Lane wrote: > Simon Riggs writes: > > [ v2 patch ] > > BTW, while I'm looking at this, why bother with the separate > KnownAssignedXidsValid[] array? Wouldn't it be cheaper > (certainly so in storage, probably so in access/update times) > to have just the K

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

On Sun, 2010-04-25 at 12:51 -0400, Tom Lane wrote: > Simon Riggs writes: > > On Sun, 2010-04-25 at 11:50 -0400, Tom Lane wrote: > >> This needs a redesign before it can be considered committable. I don't > >> really care whether it makes things faster; it's too complicated and too > >> poorly doc

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

Simon Riggs writes: > On Sun, 2010-04-25 at 12:51 -0400, Tom Lane wrote: >> If the comments were correct, I wouldn't be complaining. They're >> misleading or outright wrong on many points. In particular, I don't >> think you actually understand the weak-memory-ordering issue, because >> the comm

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

On Sun, 2010-04-25 at 13:33 -0400, Tom Lane wrote: > If you like I'll have a go at rewriting the comments for this patch, > because I am currently thinking that the problem is not so much with > the code as with the poor explanation of what it's doing. Sometimes > the author is too close to the c

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

Simon Riggs writes: > On Sun, 2010-04-25 at 13:33 -0400, Tom Lane wrote: >> If you like I'll have a go at rewriting the comments for this patch, >> because I am currently thinking that the problem is not so much with >> the code as with the poor explanation of what it's doing. Sometimes >> the au

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

On Sat, April 24, 2010 01:17, Erik Rijkers wrote: > On Sat, April 24, 2010 00:39, Simon Riggs wrote: >> On Fri, 2010-04-23 at 11:32 -0400, Robert Haas wrote: >>> > >>> > 99% of transactions happen in similar times between primary and standby, >>> > everything dragged down by rare but severe spikes.

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

"Erik Rijkers" writes: > FWIW, here are some more results from pgbench comparing > primary and standby (both with Simon's patch). That seems weird. Why do most of the runs show primary and standby as having comparable speed, but a few show the standby as much slower? The parameters for those run

Re: [HACKERS] global temporary tables

On Apr 24, 2010, at 10:02 PM, Tom Lane wrote: > Robert Haas writes: >> Pushing it into the RelFileNode has some advantages in terms of being >> able to get at the information from everywhere, but one thing that >> makes me think that's probably not a good decision is that we somtimes >> WAL-log re

Re: [HACKERS] inlining SQL functions

On Apr 2, 2010, at 12:12 PM, Tom Lane wrote: > Alexey Klyukin writes: >> Is there a reason why only a table free SQL functions are allowed to >> be inlined ? I wonder why a simple SQL function containing only a >> SELECT * FROM table can't be expanded inline ? > > If you're thinking of just repl

Re: [HACKERS] proposal - structured funcid and lineno as new fields in error message

On Mar 29, 2010, at 11:47 AM, Pavel Stehule wrote: > 2010/3/29 Tom Lane : >> Pavel Stehule writes: >>> can we add well structured information about function id and lineno to >>> ErrorData? >> >> The idea that I was toying with was to report the function OID and line >> number, which might as well

Re: [HACKERS] global temporary tables

Jim Nasby writes: > On Apr 24, 2010, at 10:02 PM, Tom Lane wrote: >> Yeah. I think we also use RelFileNode as a hash tag in places, and >> so adding a bool to it would be problematic for a couple of reasons: >> possibly uninitialized pad bytes, and uselessly incorporating more bytes >> into the h

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

On Sun, 2010-04-25 at 20:25 +0200, Erik Rijkers wrote: > Sorry if it's too much data, but to me at least it was illuminating; > I now understand the effects of the different parameters better. That's great, many thanks. A few observations * Standby performance is actually slightly above normal

Re: [HACKERS] inlining SQL functions

Jim Nasby writes: > On Apr 2, 2010, at 12:12 PM, Tom Lane wrote: >> If you're thinking of just replacing the call with a sub-SELECT >> construct, that's no good in general because it would change the >> semantics. > Since Alexey was working on this for us, I'll elaborate. The actual > use case is

Re: [HACKERS] global temporary tables

Robert, (1). What I *think* it is supposed to mean is that the table is a permanent object which is "globally" visible - that is, it's part of some non-temp schema like public or $user and it's column definitions etc. are visible to all backends - and it's not automatically removed on commit, b

Re: [HACKERS] global temporary tables

Josh Berkus wrote: Robert, (1). What I *think* it is supposed to mean is that the table is a permanent object which is "globally" visible - that is, it's part of some non-temp schema like public or $user and it's column definitions etc. are visible to all backends - and it's not automaticall

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

On Sun, April 25, 2010 20:55, Tom Lane wrote: > > That seems weird. Why do most of the runs show primary and standby > as having comparable speed, but a few show the standby as much slower? > The parameters for those runs don't seem obviously different from cases > where it's fast. I think there

Re: [HACKERS] global temporary tables

On Sun, Apr 25, 2010 at 5:29 PM, Josh Berkus wrote: > Robert, > >> (1).  What I *think* it is supposed to mean is that the table is a >> permanent object which is "globally" visible - that is, it's part of >> some non-temp schema like public or $user and it's column definitions >> etc. are visible

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

Simon Riggs writes: > [ v2 patch ] I've been studying this some more while making notes for improved comments, and I've about come to the conclusion that having readers move the tail pointer (at the end of KnownAssignedXidsGetAndSetXmin) is overly tricky and probably not a performance improvement

Re: [HACKERS] [GENERAL] trouble with to_char('L')

Bruce Momjian wrote: Takahiro Itagaki wrote: Takahiro Itagaki wrote: Revised patch attached. Please test it. I applied this version of the patch. Please check wheter the bug is fixed and any buildfarm failures. Great. I have merged in my C comments into the code with the attached patch so

Re: [HACKERS] inlining SQL functions

On Apr 25, 2010, at 2:13 PM, Tom Lane wrote: > Jim Nasby writes: >> On Apr 2, 2010, at 12:12 PM, Tom Lane wrote: >>> If you're thinking of just replacing the call with a sub-SELECT >>> construct, that's no good in general because it would change the >>> semantics. > >> Since Alexey was working on

[HACKERS] including PID or backend ID in relpath of temp rels

Time for a new thread specific to this subject. For previous discussion, see here: http://archives.postgresql.org/pgsql-hackers/2010-04/msg01140.php http://archives.postgresql.org/pgsql-hackers/2010-04/msg01152.php I attempted to implement this by adding an isTemp argument to relpath, but ran in

Re: [HACKERS] including PID or backend ID in relpath of temp rels

On Sun, Apr 25, 2010 at 8:07 PM, Robert Haas wrote: > > 1. We could move the responsibility for removing the files associated > with temp rels from the background writer to the owning backend.  I > think the reason why we initially truncate the files and only later > remove them is because somebod

[HACKERS] improve plpgsql's EXECUTE 'select into' message with a hint

Hi, while TFM says that we can use EXECUTE 'select ' INTO instead of the non implemented EXECUTE 'select ... into ', the message in plpgsql fails to say the same thing... seems like a HINT to me -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas

Re: [HACKERS] standbycheck was:(Re: [HACKERS] testing hot standby

On Wed, Apr 14, 2010 at 9:16 AM, Jaime Casanova wrote: > On Sat, Apr 10, 2010 at 12:23 AM, Jaime Casanova > wrote: >> On Fri, Apr 9, 2010 at 3:39 PM, Jaime Casanova >> wrote: >>> >> >> i think "make standbycheck" needs a little more work, why it isn't >> accesible from top of source dir? >> > >

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

On Mon, Apr 26, 2010 at 3:25 AM, Erik Rijkers wrote: > FWIW, here are some more results from pgbench comparing > primary and standby (both with Simon's patch). Was there a difference in CPU utilization between the primary and standby? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE COR