Re: [HACKERS] HOT is applied

2007-09-20 Thread Pavan Deolasee
On 9/21/07, Tom Lane <[EMAIL PROTECTED]> wrote:
>
>
>
> but control never gets that far because neither xmin nor xmax is
> committed yet.  We could fix that, probably, by considering the
> xmin=xmax case in the xmin-in-progress case further up; but the
> HEAP_UPDATED exclusion is still a problem.  Still, it seems like this
> is leaving some money on the table when you think about pruning a HOT
> chain.  Can we improve on it easily?
>
>
May be we can, but it would get a bit tricky. There might be a transaction
looking at the first tuple in the chain and waiting for this
(inserting-deleting)
transaction to finish. If the waiting transaction is running in READ
COMMITTED
mode, it would then follow the update chain. Removing any intermediate
tuples without fixing the previous tuple's xmax/ctid (or redirected line
pointer)
would be tricky.

Thanks,
Pavan

-- 
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com


Re: [HACKERS] HOT is applied

2007-09-20 Thread Tom Lane
"Pavan Deolasee" <[EMAIL PROTECTED]> writes:
> On 9/21/07, Tom Lane <[EMAIL PROTECTED]> wrote:
>> Shouldn't we be able to prune rows that have been inserted and deleted
>> by the same transaction?  I'd have hoped to see this example use only
>> one heap page ...
>> 
> Not before the transaction commits ? In the test, we update a single tuple
> 1 times in the same transaction. So there is no opportunity to prune.

[ looks a bit more ... ]  Hm, the test I was thinking of was this one
at the end of HeapTupleSatisfiesVacuum:

if (TransactionIdEquals(HeapTupleHeaderGetXmin(tuple),
HeapTupleHeaderGetXmax(tuple)))
{
/*
 * Inserter also deleted it, so it was never visible to anyone else.
 * However, we can only remove it early if it's not an updated tuple;
 * else its parent tuple is linking to it via t_ctid, and this tuple
 * mustn't go away before the parent does.
 */
if (!(tuple->t_infomask & HEAP_UPDATED))
return HEAPTUPLE_DEAD;
}

but control never gets that far because neither xmin nor xmax is
committed yet.  We could fix that, probably, by considering the
xmin=xmax case in the xmin-in-progress case further up; but the
HEAP_UPDATED exclusion is still a problem.  Still, it seems like this
is leaving some money on the table when you think about pruning a HOT
chain.  Can we improve on it easily?

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [HACKERS] HOT is applied

2007-09-20 Thread Pavan Deolasee
On 9/21/07, Tom Lane <[EMAIL PROTECTED]> wrote:
>
>
> Shouldn't we be able to prune rows that have been inserted and deleted
> by the same transaction?  I'd have hoped to see this example use only
> one heap page ...
>
>
Not before the transaction commits ? In the test, we update a single tuple
1 times in the same transaction. So there is no opportunity to prune.

Thanks,
Pavan


-- 
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com


Re: [HACKERS] HOT is applied

2007-09-20 Thread Tom Lane
I wrote:
> ... so basically it's all about the locking.  Maybe the problem is that with
> HOT we lock the buffer too often?  heap_page_prune_opt is designed to
> not take the buffer lock unless there's a good probability of needing
> to prune, but maybe that's not working as intended.

Indeed it seems it's not; gprof shows

0.000.00 111/1389276 index_getnext  [89]
0.05   49.52 1389165/1389276 heapgetpage [6]
[8] 50.90.05   49.52 1389276 heap_page_prune_opt [8]
7.17   42.31 1366696/1366696 heap_page_prune [9]
0.010.03 1366696/1366696 
ConditionalLockBufferForCleanup [106]
0.010.00 278/2780795 PageGetHeapFreeSpace [177]

so this example is getting past the heuristic tests in
heap_page_prune_opt almost every time.  Why is that?  Too tired to poke
at it more tonight.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


[HACKERS] ecpg PREPARE is not thread safe

2007-09-20 Thread ITAGAKI Takahiro
Hi,

I encountered segfaults in a multi-threaded ecpg application. It uses
PREPARE statements concurrently in several threads. The cause seems to
be the global variable 'prep_stmts' in ecpg/ecpglib/prepare.c .
It is accessed without any locks.

I'm trying to fix it, but there are some approaches to fix it.
   1. Add a giant lock to protect prep_stmts.
   2. Put prep_stmts into TSD (Thread Specific Data).
   3. Put prep_stmts into connection specific data.

I think the proper approach is 3, because server-side prepared statements
are independent in each connection. For that matter, are there any problems
in current codes? Prepared statements are managed with a single list in it.
Even if we have some kinds of exclusive controls, current ecpg might not
good at prepared statements when we use multiple connections in a signle
thread or do multiple PREPARE in multiple threads. If so, 1 and 2 are not
correct fixes.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [HACKERS] HOT is applied

2007-09-20 Thread Tom Lane
"Heikki Linnakangas" <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> I'd still like to think about whether we
>> can be smarter about when to invoke pruning, but that's a small enough
>> issue that the patch can go in without it.

> Yeah. I'm doing some micro-benchmarking, and the attached test case is
> much slower with HOT. It's spending a lot of time trying to prune, only
> to find out that it can't.

Not sure if that's an appropriate description or not.  oprofile
(on a dual Xeon running Fedora 6) shows me this:

CPU: P4 / Xeon with 2 hyper-threads, speed 2792.99 MHz (estimated)
Counted GLOBAL_POWER_EVENTS events (time during which processor is not stopped)
with a unit mask of 0x01 (mandatory) count 10
samples  %symbol name
1070003  29.8708  LWLockAcquire
1015097  28.3380  LWLockRelease
2835147.9147  heap_page_prune
2522707.0425  AllocSetAlloc
1489814.1590  HeapTupleSatisfiesMVCC
1464424.0882  TransactionIdIsInProgress
92673 2.5871  AllocSetFree
84966 2.3720  HeapTupleSatisfiesVacuum
83097 2.3198  TransactionIdGetStatus
80737 2.2539  SimpleLruReadPage_ReadOnly
52803 1.4741  TransactionLogFetch
43406 1.2117  heapgetpage
42536 1.1875  HeapTupleHeaderGetCmin
34842 0.9727  TransactionIdIsCurrentTransactionId
27761 0.7750  TransactionIdDidAbort
24833 0.6933  MemoryContextAlloc
16446 0.4591  TransactionIdPrecedes
16089 0.4491  HeapTupleHeaderGetCmax
12919 0.3607  hash_search_with_hash_value
11857 0.3310  pfree
4964  0.1386  heap_page_prune_opt
3683  0.1028  hash_any
3215  0.0898  LWLockConditionalAcquire
3086  0.0862  PinBuffer
2573  0.0718  UnpinBuffer
2009  0.0561  ConditionalLockBufferForCleanup
1854  0.0518  ReadBuffer_common
934   0.0261  XLogInsert
784   0.0219  heapgettup_pagemode

so basically it's all about the locking.  Maybe the problem is that with
HOT we lock the buffer too often?  heap_page_prune_opt is designed to
not take the buffer lock unless there's a good probability of needing
to prune, but maybe that's not working as intended.

[ pokes at it a bit more... ]  Actually the disturbing part is that
pruning doesn't seem to be working at all: after the test finishes,
I see

regression=# vacuum verbose testtable;
INFO:  vacuuming "public.testtable"
INFO:  "testtable": removed 1 row versions in 44 pages
INFO:  "testtable": found 1 removable, 1 nonremovable row versions in 45 
pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 9955 unused item pointers.
45 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

Shouldn't we be able to prune rows that have been inserted and deleted
by the same transaction?  I'd have hoped to see this example use only
one heap page ...

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [HACKERS] curious regression failures

2007-09-20 Thread Tom Lane
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> In any case, at that point we are mostly done with the expensive steps
> of vacuuming, so the transaction finishes not long after this.  I don't
> think this issue is worth inventing a new invalidation mechanism.

Yeah, I agree --- there are only a few catalog updates left to do after
we truncate.  If we held the main-table exclusive lock while vacuuming
the TOAST table, we'd have a problem, but it looks to me like we don't.

Idle thought here: did anything get done with the idea of decoupling
main-table vacuum decisions from toast-table vacuum decisions?  vacuum.c
comments

 * Get a session-level lock too. This will protect our access to the
 * relation across multiple transactions, so that we can vacuum the
 * relation's TOAST table (if any) secure in the knowledge that no one is
 * deleting the parent relation.

and it suddenly occurs to me that we'd need some other way to deal with
that scenario if autovac tries to vacuum toast tables independently.

Also, did you see the thread complaining that autovacuums block CREATE
INDEX?  This seems true given the current locking definitions, and it's
a bit annoying.  Is it worth inventing a new table lock type just for
vacuum?

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


[HACKERS] Beginning Tamil Community for Postgre

2007-09-20 Thread ஆமாச்சு
Hi,

I am Sri Ramadoss from, Chennai, India interested in forming & taking up the 
responsibility of Localising Postgre into my native language Tamil.

I am also interested in forming a Community for Postgre in TamilNadu, a State 
of India. Tamil is spoken by around 70 million people across the globe.

Please help me as to how to take this up further and guide me. My previous 
experiences in Localisation include KDE Localisation and GNU Website 
Localisation. 

-- 
Regards,

Sri Ramadoss M.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [HACKERS] curious regression failures

2007-09-20 Thread Alvaro Herrera
Tom Lane wrote:

> There might be another way to manage this, but we're not inventing
> a new invalidation mechanism for 8.3.  This patch will have to be
> reverted for the time being :-(

Thanks.  Seems it was a good judgement call to apply it only to HEAD,
after all.

In any case, at that point we are mostly done with the expensive steps
of vacuuming, so the transaction finishes not long after this.  I don't
think this issue is worth inventing a new invalidation mechanism.

-- 
Alvaro Herrera  http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"La victoria es para quien se atreve a estar solo"

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


Re: [HACKERS] stored procedure stats in collector

2007-09-20 Thread Neil Conway
On Thu, 2007-09-20 at 16:08 +0300, Martin Pihlak wrote:
> The GUC variable stats_function_level now takes 3 values: on, off and all.

That seems a confusing set of values. Perhaps "off", "pl", and "all"
would be clearer?

I'm curious if you've measured the performance overhead of enabling this
functionality.

> PS. Would something like this be a canditate for 8.4 inclusion (if polished 
> up)?

It sounds like a useful feature to me.

-Neil



---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [HACKERS] Hash index todo list item

2007-09-20 Thread Tom Raney

We are pleased to announce an upcoming patch to the hash index code
which improves build time and index size, based on this item in the
TODO list:
During index creation, pre-sort the tuples to improve build speed
http://archives.postgresql.org/pgsql-hackers/2007-03/msg01199.php

Using our implementation, build times and index sizes are
comparable with btree index build times and index sizes.
For example, for a particular 12 million row relation, the
8.2.4 release requires over 2.8 hours to build the hash index. 
With our patch, the index is built in 80 seconds.

Here is the link for a graph, showing a comparative analysis of
btree and hash index builds and describing the benchmark data.
http://web.cecs.pdx.edu/~raneyt/pg/

We are currently cleaning up the patch and will submit it asap.

Regards,
Shreya Bhargava <[EMAIL PROTECTED]>
Tom Raney <[EMAIL PROTECTED]>


Kenneth Marshall wrote:

Dear PostgreSQL Hackers:

After following the hackers mailing list for quite a while,
I am going to start investigating what will need to be done
to improve hash index performance. Below are the pieces of
this project that I am currently considering:

1. Characterize the current hash index implementation against
   the BTree index, with a focus on space utilization and
   lookup performance against a collection of test data. This
   will give a baseline performance test to evaluate the impact
   of changes. I initially do not plan to bench the hash creation
   process since my initial focus will be on lookup performance.

2. Evaluate the performance of different hash index implementations
   and/or changes to the current implementation. My current plan is
   to keep the implementation as simple as possible and still provide
   the desired performance. Several hash index suggestions deal with
   changing the layout of the keys on a page to improve lookup
   performance, including reducing the bucket size to a fraction of
   a page or only storing the hash value on the page, instead of
   the index value itself. My goal in this phase is to produce one
   or more versions with better performance than the current BTree.
   
3. Look at build time and concurrency issues with the addition of

   some additional tests to the test bed. (1)

4. Repeat as needed.

This is the rough plan. Does anyone see anything critical that
is missing at this point? Please send me any suggestions for test
data and various performance test ideas, since I will be working
on that first.

Regards,
Ken Marshall 


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings




---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [HACKERS] [COMMITTERS] pgsql: Silence Solaris compiler warnings, per buildfarm.

2007-09-20 Thread Tom Lane
Gregory Stark <[EMAIL PROTECTED]> writes:
>> Silence Solaris compiler warnings, per buildfarm.

> This one was also lost in the tsearch merge. 

Done.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [HACKERS] HOT is applied

2007-09-20 Thread Merlin Moncure
On 9/20/07, Heikki Linnakangas <[EMAIL PROTECTED]> wrote:
> Tom Lane wrote:
> > I've committed the HOT patch.
>
> Thanks, much easier to work with it now that it's in.
>
> >  I'd still like to think about whether we
> > can be smarter about when to invoke pruning, but that's a small enough
> > issue that the patch can go in without it.
>
> Yeah. I'm doing some micro-benchmarking, and the attached test case is
> much slower with HOT. It's spending a lot of time trying to prune, only
> to find out that it can't.
>
> Instead of/in addition to avoiding pruning when it doesn't help, maybe
> we could make HeapTupleSatisfiesVacuum cheaper.
>
> I'm going to continue testing, this is just a heads-up that HOT as
> committed seriously hurts performance in some cases. (though one can
> argue that this test case isn't a very realistic one.)

well, I ran your test on my box and here are the results:
pre hot:
run 1: 3617.641 ms
run 2: 5195.215 ms
run 3: 6760.449 ms
after vacuum:
run 1: 4171.362 ms
run 2: 5513.317 ms
run 3: 6884.125 ms
post hot:
run 1: Time: 7286.292 ms
run 2: Time: 7477.089 ms
run 3: Time: 7701.229 ms

those results aren't exactly terrible, and this case is highly artificial.

merlin

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [HACKERS] HOT is applied

2007-09-20 Thread Heikki Linnakangas
Tom Lane wrote:
> I've committed the HOT patch.

Thanks, much easier to work with it now that it's in.

>  I'd still like to think about whether we
> can be smarter about when to invoke pruning, but that's a small enough
> issue that the patch can go in without it.

Yeah. I'm doing some micro-benchmarking, and the attached test case is
much slower with HOT. It's spending a lot of time trying to prune, only
to find out that it can't.

Instead of/in addition to avoiding pruning when it doesn't help, maybe
we could make HeapTupleSatisfiesVacuum cheaper.

I'm going to continue testing, this is just a heads-up that HOT as
committed seriously hurts performance in some cases. (though one can
argue that this test case isn't a very realistic one.)

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com
DROP TABLE IF EXISTS testtable;
CREATE TABLE testtable (key integer);

-- Note that there's no indexes, so updates have to do a seq scan.

CREATE OR REPLACE FUNCTION testfunc(data int, key1 int) RETURNS int AS $$
DECLARE
BEGIN
  FOR cnt IN 1..1 LOOP
UPDATE testtable SET key = data WHERE key = key1;
  END LOOP;
  RETURN 1;
END;
$$ LANGUAGE plpgsql;

INSERT INTO testtable VALUES (1);
BEGIN;
SELECT testfunc(1,1);
COMMIT;

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [HACKERS] [PATCHES] minor compiler warning in backend/utils/adt/tsrank.c

2007-09-20 Thread Teodor Sigaev

Thank you, committed

Hannes Eder wrote:
while rebuilding postgres with msvc 2005 I noticed some minor compiler 
warnings:


.\src\backend\utils\adt\tsrank.c(24): warning C4305: 'initializing' : 
truncation from 'double' to 'float'
.\src\backend\utils\adt\tsrank.c(24): warning C4305: 'initializing' : 
truncation from 'double' to 'float'
.\src\backend\utils\adt\tsrank.c(24): warning C4305: 'initializing' : 
truncation from 'double' to 'float'
.\src\backend\utils\adt\tsrank.c(46): warning C4305: 'return' : 
truncation from 'double' to 'float4'
.\src\backend\utils\adt\tsrank.c(339): warning C4305: '=' : truncation 
from 'double' to 'float'


see attached diff

-Hannes





---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


--
Teodor Sigaev   E-mail: [EMAIL PROTECTED]
   WWW: http://www.sigaev.ru/

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match


[HACKERS] HOT is applied

2007-09-20 Thread Tom Lane
I've committed the HOT patch.  I'd still like to think about whether we
can be smarter about when to invoke pruning, but that's a small enough
issue that the patch can go in without it.

regards, tom lane

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate


Re: [HACKERS] like/ilike improvements

2007-09-20 Thread Andrew Dunstan



I wrote:



I can't see an obvious way around the problem for multi-byte case - 
lower() then requires converting to and from wchar, and I don't see a 
way of avoiding calling lower().


There is one way we could reduce the use of lower() by up to (almost) 
50% in the common case where the pattern is a constant expression (or a 
literal, as it usually is) - cache the result of lower() on the pattern 
rather than call it for every text the pattern is being compared to. I'm 
not quite sure how to achieve that though.


Anyone have good ideas?

cheers

andrew

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [HACKERS] Improving the Performance of Full Table Updates

2007-09-20 Thread Tom Lane
"Gokulakannan Somsundaram" <[EMAIL PROTECTED]> writes:
> I propose to change this row-by-row approach, when it is a full table
> update. I plan to send a extra flag(which will be set for Full table
> Deletes/Updates). this would make the access method directly acquire the
> exclusive lock and update the existing record.

This sounds like a recipe for utter destruction of what little
modularity and layering we've got left.  And I rather doubt it will buy
anything interesting performance-wise.

To cite just one concrete objection: surely the tuple-fetch code has got
no business firing triggers.

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


Re: [HACKERS] like/ilike improvements

2007-09-20 Thread Andrew Dunstan



Guillaume Smet wrote:

app_hls


On 9/20/07, Andrew Dunstan <[EMAIL PROTECTED]> wrote:
  

Can you retry both sets of tests but this time in C locale? The lower()
code works differently in C locale, and it might be that we need to look
at tweaking just one case.



  



Please try the attached patch, which goes back to using a special case 
for single-byte ILIKE. I want to make sure that at the very least we 
don't cause a performance regression with the code done this release. I 
can't see an obvious way around the problem for multi-byte case - 
lower() then requires converting to and from wchar, and I don't see a 
way of avoiding calling lower(). If this is a major blocker I would 
suggest you look at an alternative to using ILIKE for your UTF8 data.


cheers

andrew
Index: src/backend/utils/adt/like.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/adt/like.c,v
retrieving revision 1.69
diff -c -r1.69 like.c
*** src/backend/utils/adt/like.c	2 Jun 2007 02:03:42 -	1.69
--- src/backend/utils/adt/like.c	20 Sep 2007 13:12:39 -
***
*** 36,41 
--- 36,43 
  
  static int	UTF8_MatchText(char *t, int tlen, char *p, int plen);
  
+ static int	SB_IMatchText(char *t, int tlen, char *p, int plen);
+ 
  static int	GenericMatchText(char *s, int slen, char* p, int plen);
  static int	Generic_Text_IC_like(text *str, text *pat);
  
***
*** 104,109 
--- 106,117 
  
  #include "like_match.c"
  
+ /* setup to compile like_match.c for single byte case insensitive matches */
+ #define MATCH_LOWER
+ #define NextChar(p, plen) NextByte((p), (plen))
+ #define MatchText SB_IMatchText
+ 
+ #include "like_match.c"
  
  /* setup to compile like_match.c for UTF8 encoding, using fast NextChar */
  
***
*** 132,146 
  	int			slen,
  plen;
  
! 	/* Force inputs to lower case to achieve case insensitivity */
! 	str = DatumGetTextP(DirectFunctionCall1(lower, PointerGetDatum(str)));
! 	pat = DatumGetTextP(DirectFunctionCall1(lower, PointerGetDatum(pat)));
! 	s = VARDATA(str);
! 	slen = (VARSIZE(str) - VARHDRSZ);
! 	p = VARDATA(pat);
! 	plen = (VARSIZE(pat) - VARHDRSZ);
  
! 	return GenericMatchText(s, slen, p, plen);
  }
  
  /*
--- 140,171 
  	int			slen,
  plen;
  
! 	/* For efficiency reasons, in the single byte case we don't call
! 	 * lower() on the pattern and text, but instead call to_lower on each
! 	 * character.  In the multi-byte case we don't have much choice :-(
! 	 */
  
! 	if (pg_database_encoding_max_length() > 1)
! 	{
! 		pat = DatumGetTextP(DirectFunctionCall1(lower, PointerGetDatum(pat)));
! 		p = VARDATA(pat);
! 		plen = (VARSIZE(pat) - VARHDRSZ);
! 		str = DatumGetTextP(DirectFunctionCall1(lower, PointerGetDatum(str)));
! 		s = VARDATA(str);
! 		slen = (VARSIZE(str) - VARHDRSZ);
! 		if (GetDatabaseEncoding() == PG_UTF8)
! 			return UTF8_MatchText(s, slen, p, plen);
! 		else
! 			return MB_MatchText(s, slen, p, plen);
! 	}
! 	else
! 	{
! 		p = VARDATA(pat);
! 		plen = (VARSIZE(pat) - VARHDRSZ);
! 		s = VARDATA(str);
! 		slen = (VARSIZE(str) - VARHDRSZ);
! 		return SB_IMatchText(s, slen, p, plen);
! 	}
  }
  
  /*
Index: src/backend/utils/adt/like_match.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/adt/like_match.c,v
retrieving revision 1.16
diff -c -r1.16 like_match.c
*** src/backend/utils/adt/like_match.c	2 Jun 2007 02:03:42 -	1.16
--- src/backend/utils/adt/like_match.c	20 Sep 2007 13:12:39 -
***
*** 13,18 
--- 13,19 
   * NextChar 
   * MatchText - to name of function wanted
   * do_like_escape - name of function if wanted - needs CHAREQ and CopyAdvChar
+  * MATCH_LOWER - define iff using to_lower on text chars
   *
   * Copyright (c) 1996-2007, PostgreSQL Global Development Group
   *
***
*** 68,73 
--- 69,80 
   *
   */
  
+ #ifdef MATCH_LOWER
+ #define TCHAR(t) tolower((t))
+ #else
+ #define TCHAR(t) (t)
+ #endif
+ 
  static int
  MatchText(char *t, int tlen, char *p, int plen)
  {
***
*** 143,155 
  			else
  			{
  
! char firstpat = *p ;
  
  if (*p == '\\')
  {
  	if (plen < 2)
  		return LIKE_FALSE;
! 	firstpat = p[1];
  }
  
  while (tlen > 0)
--- 150,162 
  			else
  			{
  
! char firstpat = TCHAR(*p) ;
  
  if (*p == '\\')
  {
  	if (plen < 2)
  		return LIKE_FALSE;
! 	firstpat = TCHAR(p[1]);
  }
  
  while (tlen > 0)
***
*** 158,164 
  	 * Optimization to prevent most recursion: don't recurse
  	 * unless first pattern byte matches first text byte.
  	 */
! 	if (*t == firstpat)
  	{
  		int			matched = MatchText(t, tlen, p, plen);
  		
--- 165,171 
  	 * Optimization to prevent most recursion: don't recurse
  	 * unless first pattern byte matches first text byte.
  	 */
! 	if (TCHAR(*t)

Re: [HACKERS] stored procedure stats in collector

2007-09-20 Thread Martin Pihlak

Howdy,

Here's an updated version of the function stats patch. The biggest change is
that by default only procedural language functions are counted. The GUC variable
stats_function_level now takes 3 values: on, off and all. The latter also
counts SQL and C language functions, "on" means only to count procedural
language functions. The decision is now made in fmgr_info_cxt_security()
Trigger functions are now also counted.

Sample output:

select procname, calls, total_time, total_cpu, self_time, self_cpu
from pg_stat_user_functions order by self_cpu desc limit 5;

  procname  | calls | total_time | total_cpu | self_time | self_cpu
+---++---+---+--
 next_batch | 32765 |  27139 |  8574 | 27139 | 8574
 fetch_batch_events |  3636 |   9252 |  5622 |  3771 | 2717
 batch_event_sql|  3636 |   5454 |  2888 |  3910 | 1962
 finish_batch   |  3420 |   3215 |  1475 |  3215 | 1475
 batch_event_tables |  3636 |   1448 |   865 |  1434 |  858
(5 rows)

This is still 8.2 only (tested on 8.2.4 and 8.2.5), has seen some production
usage here at Skype (about a month on reasonably busy boxes). So far so good.

Couple of issues:
- sometimes self_cpu > self_time - currently blaming it on Linux version of
gettimeofday().
- dropped functions are not purged from stats, might bloat the stats file for
some usage patterns.

PS. Would something like this be a canditate for 8.4 inclusion (if polished up)?

Regards,
Martin



8.2-procstat.patch.gz
Description: GNU Zip compressed data

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate


[HACKERS] minor compiler warning in backend/utils/adt/tsrank.c

2007-09-20 Thread Hannes Eder
while rebuilding postgres with msvc 2005 I noticed some minor compiler 
warnings:


.\src\backend\utils\adt\tsrank.c(24): warning C4305: 'initializing' : 
truncation from 'double' to 'float'
.\src\backend\utils\adt\tsrank.c(24): warning C4305: 'initializing' : 
truncation from 'double' to 'float'
.\src\backend\utils\adt\tsrank.c(24): warning C4305: 'initializing' : 
truncation from 'double' to 'float'
.\src\backend\utils\adt\tsrank.c(46): warning C4305: 'return' : 
truncation from 'double' to 'float4'
.\src\backend\utils\adt\tsrank.c(339): warning C4305: '=' : truncation 
from 'double' to 'float'


see attached diff

-Hannes

*** ../pgsql-cvshead/src/backend/utils/adt/tsrank.c Thu Sep 20 10:30:19 2007
--- src/backend/utils/adt/tsrank.c  Thu Sep 20 12:20:10 2007
***
*** 21,27 
  #include "miscadmin.h"
  
  
! static float weights[] = {0.1, 0.2, 0.4, 1.0};
  
  #define wpos(wep) ( w[ WEP_GETWEIGHT(wep) ] )
  
--- 21,27 
  #include "miscadmin.h"
  
  
! static float weights[] = {0.1f, 0.2f, 0.4f, 1.0f};
  
  #define wpos(wep) ( w[ WEP_GETWEIGHT(wep) ] )
  
***
*** 43,49 
  word_distance(int4 w)
  {
if (w > 100)
!   return 1e-30;
  
return 1.0 / (1.005 + 0.05 * exp(((float4) w) / 1.5 - 2));
  }
--- 43,49 
  word_distance(int4 w)
  {
if (w > 100)
!   return 1e-30f;
  
return 1.0 / (1.005 + 0.05 * exp(((float4) w) / 1.5 - 2));
  }
***
*** 336,342 
calc_rank_and(w, t, q) : calc_rank_or(w, t, q);
  
if (res < 0)
!   res = 1e-20;
  
if ((method & RANK_NORM_LOGLENGTH) && t->size > 0)
res /= log((double) (cnt_length(t) + 1)) / log(2.0);
--- 336,342 
calc_rank_and(w, t, q) : calc_rank_or(w, t, q);
  
if (res < 0)
!   res = 1e-20f;
  
if ((method & RANK_NORM_LOGLENGTH) && t->size > 0)
res /= log((double) (cnt_length(t) + 1)) / log(2.0);

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


[HACKERS] compiler warnings in ecpglib/execute.c (uninitialized local variable 'prepname' used)

2007-09-20 Thread Hannes Eder

while rebuilding pgsql with msvc 2005 I noticed this compiler warning:

.\src\interfaces\ecpg\ecpglib\execute.c(1495): warning C4700: 
uninitialized local variable 'prepname' used


ECPGfree(prepname) is called in line 1495, prepname was not
unitialized befor. Below the line 1495 ECPGfree(prepname) is not
called in the function ECPGdo. I didn't investigate the code in
detail, but I assume that at least in some error conditions (when the
function returns false?) ECPGfree(prepname) should be called.

-Hannes


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate


Re: [HACKERS] Timezones change - never ending story

2007-09-20 Thread Zdenek Kotala

Gregory Stark napsal(a):

"Zdenek Kotala" <[EMAIL PROTECTED]> writes:


Just for information. Venezuela is going to have new timezone change (30minutes
shift) on this weekend. This change is not yet integrated in the last version
in Olson database. (Original announcement said it happens on 1.1.2008)


Is there still time to slip this into the upcoming 8.2.5?

I haven't been able to find anything which specifies precisely when it'll
happen though. Just knowing the week or even day isn't enough.


There is latest info

http://www.worldtimezone.com/dst_news/dst_news_venezuela02.html

but I think until Olson database will not be updated, we can do nothing. Maybe 
put some info on webpage.



Zdenek



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match


Re: [HACKERS] Raw device I/O for large objects

2007-09-20 Thread Georgi Chulkov
Thank you everyone for your valuable input! I will have a look at some other 
part of PostgreSQL, and maybe find something else to do instead.

Best,
Georgi

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [HACKERS] Debugger

2007-09-20 Thread Hannes Eder

Peter Eisentraut schrieb:

Pedro Belmino wrote:
  

I am with a problem. When I am using debugger, breakpoints that they
are inside of main function (stop the execution in the marked point)
but when breakpoint is marked in another archive (index.c for
example) breakpoint does not function (the execution in the point
does not stop), because this happens?



There is a fork() happen between the main function and what you are 
probably looking for break at.  You need to attach to the session 
process after it forks, not to the main postmaster process.


  

If you just want a single backend, and entering sql commands to the
"backend>" prompt is enough for you, you should consider using the
command line argument --single DATABASE for debugging.

see also:

http://developer.postgresql.org/pgdocs/postgres/app-postgres.html

-Hannes


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match


Re: [HACKERS] like/ilike improvements

2007-09-20 Thread Guillaume Smet
On 9/20/07, Andrew Dunstan <[EMAIL PROTECTED]> wrote:
> Can you retry both sets of tests but this time in C locale? The lower()
> code works differently in C locale, and it might be that we need to look
> at tweaking just one case.

Here we go with SQL_ASCII:

** 8.1 **

cityvox_c=# SELECT e.numeve FROM evenement e WHERE e.libgeseve LIKE
'%hocus pocus%';
 numeve

(0 rows)

Time: 117.485 ms

cityvox_c=# SELECT e.numeve FROM evenement e WHERE e.libgeseve ILIKE
'%hocus pocus%';
  numeve
---
 900024298
 87578
(2 rows)

Time: 132.823 ms

** 8.2 **

cityvox_c=# SELECT e.numeve FROM evenement e WHERE e.libgeseve LIKE
'%hocus pocus%';
 numeve

(0 rows)

Time: 100.008 ms
cityvox_c=# SELECT e.numeve FROM evenement e WHERE e.libgeseve ILIKE
'%hocus pocus%';
  numeve
---
 900024298
 87578
(2 rows)

Time: 113.579 ms

** 8.3 **

cityvox_c=# SELECT e.numeve FROM evenement e WHERE e.libgeseve LIKE
'%hocus pocus%';
 numeve

(0 rows)

Time: 112.462 ms
cityvox_c=# SELECT e.numeve FROM evenement e WHERE e.libgeseve ILIKE
'%hocus pocus%';
  numeve
---
 900024298
 87578
(2 rows)

Time: 160.961 ms

--
Guillaume

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [HACKERS] Improving the Performance of Full Table Updates

2007-09-20 Thread Gokulakannan Somsundaram
The obvious advantages are
a) Avoidance of one read lock per page
b) One Big write lock instead of multiple write locks.

But as you said, i will do some initial profiling and get back.

Thanks,
Gokul.

On 9/20/07, Heikki Linnakangas <[EMAIL PROTECTED]> wrote:
>
> Gokulakannan Somsundaram wrote:
> > Hi,
> >The Current architecture of Updates in PostgreSQL is
> > 1) Make a select query out of update. It involves a READ
> lock/BUFFER_SHARE
> > 2) Get the tupleid
> > 3) Goto the buffer containing the tupleid, make a BUFFER_EXCLUSIVE lock
> on
> > it
> > 4) update it
> > 5) Repeat the above process for subsequent rows
> >
> > I propose to change this row-by-row approach, when it is a full table
> > update. I plan to send a extra flag(which will be set for Full table
> > Deletes/Updates). this would make the access method directly acquire the
> > exclusive lock and update the existing record.
> >
> > For Deletes this is simple. But for updates, the projection tuple has to
> be
> > made before re-inserting it. So there will be a list of Heap tuples
> stored
> > in memory for each page getting updated. these tuples will be inserted
> after
> > the deletion part of update is done. This is just a rough design. I may
> get
> > involved in a detail design once i get a nod from the mailing list
> > community.
>
> I doubt the locking overhead is that significant. Have you done any
> profiling to show that it's worth it?
>
> --
>   Heikki Linnakangas
>   EnterpriseDB   http://www.enterprisedb.com
>


Re: [HACKERS] Improving the Performance of Full Table Updates

2007-09-20 Thread Heikki Linnakangas
Gokulakannan Somsundaram wrote:
> Hi,
>The Current architecture of Updates in PostgreSQL is
> 1) Make a select query out of update. It involves a READ lock/BUFFER_SHARE
> 2) Get the tupleid
> 3) Goto the buffer containing the tupleid, make a BUFFER_EXCLUSIVE lock on
> it
> 4) update it
> 5) Repeat the above process for subsequent rows
> 
> I propose to change this row-by-row approach, when it is a full table
> update. I plan to send a extra flag(which will be set for Full table
> Deletes/Updates). this would make the access method directly acquire the
> exclusive lock and update the existing record.
> 
> For Deletes this is simple. But for updates, the projection tuple has to be
> made before re-inserting it. So there will be a list of Heap tuples stored
> in memory for each page getting updated. these tuples will be inserted after
> the deletion part of update is done. This is just a rough design. I may get
> involved in a detail design once i get a nod from the mailing list
> community.

I doubt the locking overhead is that significant. Have you done any
profiling to show that it's worth it?

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

---(end of broadcast)---
TIP 6: explain analyze is your friend


[HACKERS] Improving the Performance of Full Table Updates

2007-09-20 Thread Gokulakannan Somsundaram
Hi,
   The Current architecture of Updates in PostgreSQL is
1) Make a select query out of update. It involves a READ lock/BUFFER_SHARE
2) Get the tupleid
3) Goto the buffer containing the tupleid, make a BUFFER_EXCLUSIVE lock on
it
4) update it
5) Repeat the above process for subsequent rows

I propose to change this row-by-row approach, when it is a full table
update. I plan to send a extra flag(which will be set for Full table
Deletes/Updates). this would make the access method directly acquire the
exclusive lock and update the existing record.

For Deletes this is simple. But for updates, the projection tuple has to be
made before re-inserting it. So there will be a list of Heap tuples stored
in memory for each page getting updated. these tuples will be inserted after
the deletion part of update is done. This is just a rough design. I may get
involved in a detail design once i get a nod from the mailing list
community.

Thanks,
Gokul.