Re: [HACKERS] what is difference between LOCAL and GLOBAL TEMPTABLES in PostgreSQL

2007-07-05 Thread Simon Riggs
On Wed, 2007-07-04 at 22:27 +0100, Gregory Stark wrote: Pavel Stehule [EMAIL PROTECTED] writes: Catalog bloat is one unwanted effect. Second is different behave of temp tables than other mayor rdbms, and uncomfortable work with temp tables in stored procedures. Third argument for

Re: [HACKERS] what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL

2007-07-05 Thread Gregory Stark
Tom Lane [EMAIL PROTECTED] writes: Gregory Stark [EMAIL PROTECTED] writes: 2007/7/4, Bruce Momjian [EMAIL PROTECTED]: The solution is to fix the bloat, not add a work-around. The bloat is a direct consequence of performing DDL in the midst of an OLTP transaction. Hardly. It's a

Re: [HACKERS] Still recommending daily vacuum...

2007-07-05 Thread Michael Paesold
Alvaro Herrera wrote: So what you are proposing above amounts to setting scale factor = 0.05. The threshold is unimportant -- in the case of a big table it matters not if it's 0 or 1000, it will be almost irrelevant in calculations. In the case of small tables, then the table will be vacuumed

Re: [HACKERS] GRANT ROLE and triggers

2007-07-05 Thread Richard Huxton
Claudio Rossi wrote: Hello, I'm trying to write a trigger function which uses GRANT ROLE statement. Scenario is: I have a group role (let's call it A) which has not superuser privileges and I want to grant A membership to current user after an insert on a particular table, then revoke it after a

Re: [HACKERS] SetBufferCommitInfoNeedsSave and race conditions

2007-07-05 Thread Simon Riggs
On Thu, 2007-06-28 at 20:23 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: On Thu, 2007-06-28 at 15:16 -0400, Tom Lane wrote: A quick grep suggests that VACUUM FULL might be at risk here. No we're clear: I caught that issue specifically for VACUUM FULL fairly early on. VF

Re: [HACKERS] SetBufferCommitInfoNeedsSave and race conditions

2007-07-05 Thread Gregory Stark
Simon Riggs [EMAIL PROTECTED] writes: I'd guess that storing 8 per page would be optimal, so each stored xid would track 4,000 transactions - probably around 1 sec worth of transactions when the feature is used. This is something we can experiment with but I suspect that while 8 might be

Re: [HACKERS] todo: Hash index creation

2007-07-05 Thread Heikki Linnakangas
Kenneth Marshall wrote: I definitely agree with Tom's assessment. If we cannot need to make the hash index as performant as it is in theory, none of the other refinements are worth it. You would need to use BTree if you were concerned about speed. (and who isn't) I just got an idea. Hash

Re: [HACKERS] Still recommending daily vacuum...

2007-07-05 Thread Florian G. Pflug
Michael Paesold wrote: Alvaro Herrera wrote: So what you are proposing above amounts to setting scale factor = 0.05. The threshold is unimportant -- in the case of a big table it matters not if it's 0 or 1000, it will be almost irrelevant in calculations. In the case of small tables, then the

Re: [HACKERS] Still recommending daily vacuum...

2007-07-05 Thread Kevin Grittner
On Tue, Jul 3, 2007 at 5:34 PM, in message [EMAIL PROTECTED], Alvaro Herrera [EMAIL PROTECTED] wrote: Kevin Grittner wrote: Autovacuum is enabled with very aggressive settings, to cover small tables, including one with about 75 rows that can be updated 100 or more times per second. Even

Re: [HACKERS] [PATCH] A crash and subsequent recovery of themaster can cause the slave to get out-of-sync

2007-07-05 Thread Tom Lane
[ back to dealing with this patch, finally ] Florian G. Pflug [EMAIL PROTECTED] writes: While creating the patch, I've been thinking if it might be worthwile to note that we just did recovery in the ShutdownCheckpoint (or create a new checkpoint type RecoveryCheckpoint). This wouldl allow for

Re: [HACKERS] [PATCH] A crash and subsequent recovery of themaster can cause the slave to get out-of-sync

2007-07-05 Thread Florian G. Pflug
Tom Lane wrote: [ back to dealing with this patch, finally ] Florian G. Pflug [EMAIL PROTECTED] writes: While creating the patch, I've been thinking if it might be worthwile to note that we just did recovery in the ShutdownCheckpoint (or create a new checkpoint type RecoveryCheckpoint). This

Re: [HACKERS] [PATCH] A crash and subsequent recovery of themaster can cause the slave to get out-of-sync

2007-07-05 Thread Tom Lane
Florian G. Pflug [EMAIL PROTECTED] writes: Tom Lane wrote: Conclusion: we should apply Florian's patch as-is in 8.2, do something morally equivalent in 8.1 and before, and invent a CrashRecoveryCheckpoint record type in HEAD. Sounds good. Actually, now that I look closer, this patch seems

[HACKERS] usleep feature for pgbench

2007-07-05 Thread Jan Wieck
To test some changes in Slony I needed a \usleep [microseconds|:variable] in pgbench's scripting language to be able to have hundreds of concurrent running transactions without totally swamping the system. I was wondering if anyone would object to permanently adding this to the pgbench

Re: [HACKERS] unclean SPI_scroll_cursor_move documentation, is SPI_tuptable valid?

2007-07-05 Thread Tom Lane
Pavel Stehule [EMAIL PROTECTED] writes: in doc there are for returned value: SPI_processed and SPI_tuptable are set as in SPI_execute if successful. But for move statement is SPI_tuptable undefined. Move statement only move cursor. Doesn't return anything. Fixed, thanks.

Re: [HACKERS] usleep feature for pgbench

2007-07-05 Thread Tom Lane
Jan Wieck [EMAIL PROTECTED] writes: To test some changes in Slony I needed a \usleep [microseconds|:variable] in pgbench's scripting language to be able to have hundreds of concurrent running transactions without totally swamping the system. I was wondering if anyone would object to

Re: [HACKERS] [PATCH] A crash and subsequent recovery of themaster can cause the slave to get out-of-sync

2007-07-05 Thread Florian G. Pflug
Tom Lane wrote: Florian G. Pflug [EMAIL PROTECTED] writes: Tom Lane wrote: Conclusion: we should apply Florian's patch as-is in 8.2, do something morally equivalent in 8.1 and before, and invent a CrashRecoveryCheckpoint record type in HEAD. Sounds good. Actually, now that I look closer,

Re: [HACKERS] usleep feature for pgbench

2007-07-05 Thread Jan Wieck
On 7/5/2007 3:34 PM, Tom Lane wrote: Jan Wieck [EMAIL PROTECTED] writes: To test some changes in Slony I needed a \usleep [microseconds|:variable] in pgbench's scripting language to be able to have hundreds of concurrent running transactions without totally swamping the system. I was

[HACKERS] Bgwriter strategies

2007-07-05 Thread Heikki Linnakangas
I ran some DBT-2 tests to compare different bgwriter strategies: http://community.enterprisedb.com/bgwriter/ imola-336 was run with minimal bgwriter settings, so that most writes are done by backends. imola-337 was patched with an implementation of Tom's bgwriter idea, trying to aggressively

Re: [HACKERS] Bgwriter strategies

2007-07-05 Thread Tom Lane
Heikki Linnakangas [EMAIL PROTECTED] writes: imola-336 imola-337 imola-340 writes by checkpoint38302 30410 39529 writes by bgwriter 350113 2205782 1418672 writes by backends1834333 265755 787633

Re: [HACKERS] usleep feature for pgbench

2007-07-05 Thread Tom Lane
Jan Wieck [EMAIL PROTECTED] writes: I think I've coded it in a way that if one doesn't use the \usleep command at all, it will never even call gettimeofday() and use a NULL timeout in select() as it used to. Did you check that the observed performance for non-usleep-using scripts didn't

Re: [HACKERS] usleep feature for pgbench

2007-07-05 Thread Alvaro Herrera
Tom Lane wrote: Jan Wieck [EMAIL PROTECTED] writes: I think I've coded it in a way that if one doesn't use the \usleep command at all, it will never even call gettimeofday() and use a NULL timeout in select() as it used to. Did you check that the observed performance for

Re: [HACKERS] usleep feature for pgbench

2007-07-05 Thread Tom Lane
Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane wrote: Did you check that the observed performance for non-usleep-using scripts didn't change? If this extra overhead causes a reduction in reported TPS rates it would make it hard to compare older and newer tests. I keep wondering, why is

Re: [HACKERS] Bgwriter strategies

2007-07-05 Thread Greg Smith
On Thu, 5 Jul 2007, Heikki Linnakangas wrote: It looks like Tom's idea is not a winner; it leads to more writes than necessary. What I came away with as the core of Tom's idea is that the cleaning/LRU writer shouldn't ever scan the same section of the buffer cache twice, because anything

[HACKERS] enable-integer-datetimes vs datetime hash functions

2007-07-05 Thread Tom Lane
The hash opclasses for time, timestamptz, timestamp use the hashfloat8() hash function even when we are using integer datetimes. I had been aware of this for awhile but thought it was just harmless ugliness ... so long as you get a hash value, who cares how it was computed? But on second

Re: [HACKERS] usleep feature for pgbench

2007-07-05 Thread Jan Wieck
On 7/5/2007 5:30 PM, Tom Lane wrote: Jan Wieck [EMAIL PROTECTED] writes: I think I've coded it in a way that if one doesn't use the \usleep command at all, it will never even call gettimeofday() and use a NULL timeout in select() as it used to. Did you check that the observed performance for