date:20050916

 -Original Message-
 From: [EMAIL PROTECTED] 
 [mailto:[EMAIL PROTECTED] On Behalf Of Neil Conway
 Sent: 16 September 2005 03:48
 To: Tom Lane
 Cc: Marc G. Fournier; pgsql-hackers@postgresql.org
 Subject: Re: [HACKERS] Beta2 Wrap Up ...

 On Thu, 2005-15-09 at 22:31 -0400, Tom Lane wrote:
  I thought we'd more or less dropped that idea based on Andreas'
  responses.

 I've heard no argument against renaming pg_complete_relation_size() to
 pg_total_relation_size() 

Having spent days, no, weeks deciding on that name on list I do not want
to see it change this late, especially as we'll now need to go and
update pgAdmin again!

 and changing the functions that return an
 integer status code to make them return a boolean (but I'm 
 content with
 not making them return void and report errors via elog).

Similarly I'm not overly keen on seeing these changed again. These
functions were discussed to death earlier in the cycle, and this is what
everyone finally agreed on.

Regards, Dave.

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

Re: [HACKERS] Beta2 Wrap Up ...

 -Original Message-
 From: [EMAIL PROTECTED] 
 [mailto:[EMAIL PROTECTED] On Behalf Of Neil Conway
 Sent: 16 September 2005 06:38
 To: Marc G. Fournier
 Cc: pgsql-hackers@postgresql.org
 Subject: Re: [HACKERS] Beta2 Wrap Up ...

 On Thu, 2005-15-09 at 22:06 -0400, Neil Conway wrote:
  One change that I would like to get into beta2 is the proposed
  refactoring of some of the new system info / administration 
 functions.

 Ok, this is done: the changes have been committed to CVS HEAD and the
 catalog version number has been bumped.

Hi Neil,

I appreciate that you want to get things done, but you realise that the
timeframe between your 'I'll commit this later post' and when you
actually committed was almost precisely when those of us that argued to
get these functions included in the first place were all asleep? 

Perhaps you could allow 24 hours before committing potentially
controversial changes in future?

Regards, Dave

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

[HACKERS] New dot releases

2005-09-16 Thread Devrim Gunduz



Hi,

After / before 8.1 Beta 2, are there any plans to release new dot releases 
for the back branches?


Regards,
--
Devrim GUNDUZ
Kivi Bilişim Teknolojileri - http://www.kivi.com.tr
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
  http://www.gunduz.org
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[HACKERS] 8.1 win32 beta?

2005-09-16 Thread Tony Caduto


With there be a win32 version of beta2? or a beta1?

Thanks,

Tony


---(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] inet increment with int


This has been saved for the 8.2 release:

http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---

Patrick Welche wrote:
 Ilya Kovalenko posted some code at in a thread starting at
 
   http://archives.postgresql.org/pgsql-hackers/2005-04/msg00417.php
 
 which lead to the TODO item:
 
 * Allow INET + INT4 to increment the host part of the address, or
   throw an error on overflow
 
 I think that the naively coded function attached does what is needed, e.g.,
 
 CREATE OR REPLACE FUNCTION inet_inc(inet, int4)
 RETURNS inet
 AS '/tmp/inet.so','inet_inc'
 LANGUAGE C STRICT;
 
 CREATE OPERATOR + (
 leftarg = inet,
 rightarg = int4,
 procedure = inet_inc
 );
 
 test=# select '192.168.0.1/24'::inet + 300;
 ERROR:  Increment (300) too big for network (/24)
 test=# select '192.168.0.1/24'::inet + 254;
  ?column? 
 --
  192.168.0.255/24
 (1 row)
 
 test=# select '192.168.0.1/24'::inet + 255;
 ERROR:  Increment (255) takes address (192.168.0.1) out of its network (/24)
 test=# select '192.168.0.1/24'::inet + -2;
 ERROR:  Increment (-2) takes address (192.168.0.1) out of its network (/24)
 test=# select '255.255.255.254/0'::inet + 2;
 ERROR:  Increment (2) takes address (255.255.255.254) out of its network (/0)
 
 and just for fun:
 
 create table list (
 host inet
 );
 
 insert into list values ('192.168.0.1/24');
 insert into list values ('192.168.0.2/24');
 insert into list values ('192.168.0.4/24');
 insert into list values ('192.168.0.5/24');
 insert into list values ('192.168.0.6/24');
 insert into list values ('192.168.0.8/24');
 insert into list values ('192.168.0.9/24');
 insert into list values ('192.168.0.10/24');
 insert into list values ('192.168.1.1/24');
 insert into list values ('192.168.1.3/24');
 
 select host+1 from list
  where host+1 = '192.168.1.0/24'
and not exists
( select 1
from list
   where host=host+1
 and host  '192.168.1.0/24' )
  limit 1;
 
 
 
 If you agree that this is the right thing, I can code it less
 naively, (Ilya rightly uses ntohl/htonl), create the operator's
 commutator, provide a patch which makes it a built-in, and some
 obvious documentation.
 
 Cheers,
 
 Patrick

[ Attachment, skipping... ]

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

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

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

[HACKERS] when started century? PostgreSQL vs Oracle diff

2005-09-16 Thread Pavel Stehule


Hello

I am testing comformity between PostgreSQL and Oracle. I found one 
difference. Century started 1900-01-01 for Oracle and 1901-01-01 for 
PostgreSQL. What value is good?


Best regards
Pavel Stehule

_
Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/


---(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] 8.1 win32 beta?

2005-09-16 Thread Petr Jelinek


Tony Caduto napsal(a):

With there be a win32 version of beta2? or a beta1?



ftp://ftp.postgresql.org/pub/binary/v8.1beta/win32/
(join -hackers-win32 if you want to be informed about win32 specific stuff)

--
Regards
Petr Jelinek (PJMODOS)
www.parba.cz

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

Re: [HACKERS] 8.1 win32 beta?

 -Original Message-
 From: [EMAIL PROTECTED] 
 [mailto:[EMAIL PROTECTED] On Behalf Of Tony Caduto
 Sent: 16 September 2005 11:19
 To: pgsql-hackers@postgresql.org
 Subject: [HACKERS] 8.1 win32 beta?

 With there be a win32 version of beta2? or a beta1?

There will be a beta2. There was no beta1 because it didn't compile on
Windows due to an issue with ipv6, however there is already a post-beta1
release available at
http://www.postgresql.org/ftp/binary/v8.1beta/win32/

Regards, Dave

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

[HACKERS] Time to close hackers-win32?

It occurs to me that there is no longer any great need to have a
separate hackers list for win32 development. Perhaps we should close it
down now and keep all development on -hackers?

Regards, Dave.

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

Re: [HACKERS] statement logging / extended query protocol issues


Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.

---


Simon Riggs wrote:
 On Thu, 2005-09-08 at 13:14 +1200, Oliver Jowett wrote:
  Simon Riggs wrote:
   Oliver, would it be possible to show a simplified call sequence and what
   you would like to see logged for each call? 
 
 These are good: Maybe it should even be in the docs for the driver?
 It would be good if it could be written as a test within the driver, so
 we can expand it and test the logging.
 
  The JDBC driver generates one of these sequences:
  
  (1)
Parse (unnamed statement) SELECT 1
Bind (unnamed statement - unnamed portal)
Execute (unnamed portal, no row limit)
  
  (2)
Parse (named statement S_1) SELECT 1
repeatedly:
  Bind (named statement S_1 - unnamed portal)
  Execute (unnamed portal, no row limit)
  
  (3)
Parse (named statement S_2) SELECT 1
repeatedly:
  Bind (named statement S_2 - named portal C_2)
  repeatedly:
Execute (named portal C_2, row limit 42)
 
 Are we sure there is just 3 cases?
 
  Ideal output is:
  
  (1)  LOG: statement: SELECT 1
  
  (2)  LOG: statement: SELECT 1
   LOG: statement: SELECT 1
   LOG: statement: SELECT 1
  
  (3)  LOG: statement: SELECT 1
   LOG: statement: SELECT 1
   LOG: statement: SELECT 1
  
  In case (3), that's one log line per repeat of the outer loop,
  regardless of how many Executes are sent in the inner loop.
 
  Note that case (1) is the most common case for application queries via
  the JDBC driver, and case (2) is the most common for internally
  generated statements like BEGIN.
 
 Even if case (3) is not that common, I still want to know it is
 occurring, to see what effect or overhead it has.
 
  As you can see from the output I'd like, I don't think that synthesizing
  FETCH / EXECUTE queries that don't actually exist [is a]
  useful thing to do, at least at the Joe Average User
  level.
 
 Your original point at the top of this thread was valid: a get-next-rows
 shouldn't look like a re-execute. We can call it something else if you
 like, as long as we can tell the difference.
 
 We'll only see the output for case (3) when someone has programmed
 things that way by using setFetchSize.
 
  Also note that the JDBC driver doesn't exercise all of the extended
  protocol -- for example it's possible to re-Bind the same unnamed
  statement repeatedly, or have multiple Executes on an unnamed portal
  with a row limit, but the JDBC driver never does that.
 
 I agree there's not much gained from displaying the BIND statement as it
 is. I argued previously against including the BIND parameters. Now I
 would say we should either include them or leave out BIND altogether.
 
 Here's a new suggestion and patch that brings together
 - Oliver and Simon's wish to remove BIND from normal logging
 - Oliver's suggestion to remove the PREPARE logging for unnamed
 statements, which would otherwise double-up logging for case(1)
 - Bruce and Simon's view to keep some form of FETCH logging
 - Tom's view to rationalise the way ROWS is mentioned
 
 (lines beginning jdbc don't show in the log, but are just there to show
 clearly the time sequence of activities and what gets subsequently
 logged)
 
 (1)
Parse (unnamed statement) SELECT * from pg_proc
Bind (unnamed statement - unnamed portal)
Execute (unnamed portal, no row limit)
 
 (1)
 jdbc parse
 jdbc bind
 jdbc execute
 LOG:  statement: SELECT * from pg_proc
 
 jdbc parse
 jdbc bind
 jdbc execute
 LOG:  statement: SELECT * from pg_proc
 
 jdbc parse
 jdbc bind
 jdbc execute
 LOG:  statement: SELECT * from pg_proc
 
 
 Notice that the parse of the unnamed statement does *not* now generate a
 log record.
 
 (2)
Parse (named statement S_1) SELECT * from pg_proc
repeatedly:
  Bind (named statement S_1 - unnamed portal)
  Execute (unnamed portal, no row limit)
 
 (2)
 jdbc parse S_1
 LOG:  statement: PREPARE S_1 AS SELECT * from pg_proc
 (perhaps this should be logged at BIND time, just like the
 optimization?)
 
 jdbc bind S_1
 jdbc execute
 LOG:  statement: EXECUTE unnamed [PREPARE:  SELECT * from pg_proc]
 
 jdbc bind S_1
 jdbc execute
 LOG:  statement: EXECUTE unnamed [PREPARE:  SELECT * from pg_proc]
 
 jdbc bind S_1
 jdbc execute
 LOG:  statement: EXECUTE unnamed [PREPARE:  SELECT * from pg_proc]
 
 
 ...I wonder if unnamed just confuses what is going on here? I've left
 it in for now, but suggest that we take that out again?
 
 (3)
Parse (named statement S_2) SELECT * from pg_proc
repeatedly:
  Bind (named statement S_2 - named portal C_2)
  repeatedly:
Execute (named portal C_2, row limit 42)
 
 (3)
 jdbc prepare S_2
 LOG:  statement: PREPARE S_2 AS SELECT * from pg_proc
 
 jdbc bind S_2 to C_2

Re: [HACKERS] statement logging / extended query protocol issues

Oliver Jowett wrote:
 Simon Riggs wrote:
 
  Are we sure there is just 3 cases?
 
 I haven't exhaustively checked, but I think those are the main cases.
 
  Even if case (3) is not that common, I still want to know it is
  occurring, to see what effect or overhead it has.
 
 I don't want it to be more verbose than the other cases when I set
 log_statement = all.

I think it is more verbose because no FETCH is logged in this type of
prepare/execute.  The goal, I think, is for these type of queries to
look as similar to normal PREPARE/EXECUTE and DECLARE/FETCH as possible.

  We'll only see the output for case (3) when someone has programmed
  things that way by using setFetchSize.
 
 Can we put extra output in this case into log_statement = verbose only
 please?

We don't have a log_statement = verbose mode.

  (1)
  jdbc parse
  jdbc bind
  jdbc execute
  LOG:  statement: SELECT * from pg_proc
 
  Notice that the parse of the unnamed statement does *not* now generate a
  log record.
 
 What about the syntax error case?

Good point, but when do we parse?  Could you set log_min_error_statement
to error?  I don't think that would work either.

  (2)
  jdbc parse S_1
  LOG:  statement: PREPARE S_1 AS SELECT * from pg_proc
  (perhaps this should be logged at BIND time, just like the
  optimization?)
  
  jdbc bind S_1
  jdbc execute
  LOG:  statement: EXECUTE unnamed [PREPARE:  SELECT * from pg_proc]
 
 I do not like logging queries that the driver never sent (the driver
 sends neither PREPARE nor EXECUTE).
 
 I also don't see why it's useful to log the statement and portal names.
 
 Can we reword this to what I suggested previously?
 
   LOG: parse statement: SELECT * from pg_proc
   LOG: execute statement: SELECT * from pg_proc

The problem here is that scripts that look for LOG: statement: would
now need to look for additional words at the start.  It also makes this
type of prepare/execute look different in the logs, while internally it
is quite similar.

  (3)
  jdbc prepare S_2
  LOG:  statement: PREPARE S_2 AS SELECT * from pg_proc
  
  jdbc bind S_2 to C_2
  jdbc execute C_2
  LOG:  statement: EXECUTE C_2 ROWS 42 [PREPARE:  SELECT * from pg_proc]
  jdbc next (after cache has run out on 42nd row)
  v3 protocol sends E for Execute, execution halts at 49 rows for this set
  of bind parameters
  LOG:  statement: FETCH C_2 ROWS 7
 
 Again I do not like logging synthetic queries that the driver never sent
 (PREPARE / EXECUTE / FETCH). BTW, if you do it this way, you could get
 the bizarre PREPARE S_2 AS PREPARE xyz AS SELECT .. result if the
 application used PREPARE itself.
 
 I think that logging the second and subsequent Executes is not normally
 useful and shouldn't happen when log_statement = all. In that case you
 don't need to log the portal name either.
 
 So for the normal case:
 
   LOG: parse statement: SELECT * from pg_proc
   LOG: execute statement: SELECT * from pg_proc
 
 and for the verbose case perhaps something like:
 
   LOG: parse statement: SELECT * from pg_proc
   LOG: execute statement (C_2, 42 rows): SELECT * from pg_proc
   LOG: fetch statement results (C_2, 7 rows)

We don't have a verbose case.  Why should it look different from
client-side stuff? 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

   http://archives.postgresql.org

Re: [HACKERS] statement logging / extended query protocol issues

2005-09-16 Thread Oliver Jowett


Bruce Momjian wrote:


We don't have a log_statement = verbose mode.


Please see my earlier email where I suggested adding one if you really 
wanted all this protocol-level detail logged.


-O

---(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

Re: [HACKERS] statement logging / extended query protocol issues

2005-09-16 Thread Oliver Jowett


Bruce Momjian wrote:


I think it is more verbose because no FETCH is logged in this type of
prepare/execute.  The goal, I think, is for these type of queries to
look as similar to normal PREPARE/EXECUTE and DECLARE/FETCH as possible.


I do not understand why this is a useful thing to do as part of 
log_statement.


My point is that given JDBC code like this:

  Statement s = connection.createStatement();
  ResultSet rs = s.executeQuery(SELECT * FROM pg_proc);
  while (rs.next()) {
 // Process results
  }

it seems that the least surprising thing to get logged is simply SELECT 
* FROM pg_proc.


I don't see how logging a synthetic PREPARE/EXECUTE/FETCH sequence (and 
DECLARE, now?) is useful. They're not necessarily syntactically correct, 
and they're certainly not queries that were actually sent to the 
backend. I thought log_statement was meant to answer the question what 
queries were submitted to the backend?, rather than to provide a trace 
of protocol-level activity..


-O

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

Re: [HACKERS] when started century? PostgreSQL vs Oracle diff

2005-09-16 Thread Adrian Maier


Pavel Stehule wrote:

Hello

I am testing comformity between PostgreSQL and Oracle. I found one 
difference. Century started 1900-01-01 for Oracle and 1901-01-01 for 
PostgreSQL. What value is good?


Hello,

I think that 1901 is the correct year.

( The first century started in year 1 and ended in year 100.
There was no year 0 ...

Also, the 2nd millenium started in 2001, not in 2000 :
http://staff-www.uni-marburg.de/~schittek/millenni.htm   )



Best wishes,
Adrian Maier


Best regards
Pavel Stehule

_
Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/


---(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




---(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] Request to clarify on sql_numeric datatype

2005-09-16 Thread Raghavendra Reddy

Title: Message



Hi,

 I have a query on sql_numeric 
datatype Can you please clarify on this?
 While inserting numeric data type into a 
table can you please let me know how you are storing the value in the 
database.
 
Whether it is stored as double or character or 
directly storing it as structure.
 

 I am implementing the slq_numeric datatype 
feature for our database and I am stuck up how to store the value in the 
database.
 Can you please help out regarding 
this.

Thanks in 
advance.

Thanks and 
Regards,
Raghavendra

Re: [HACKERS] 8.1 win32 beta?

2005-09-16 Thread Andrew Dunstan




Dave Page wrote:


[mailto:[EMAIL PROTECTED] On Behalf Of Tony Caduto


With there be a win32 version of beta2? or a beta1?
   



There will be a beta2. There was no beta1 because it didn't compile on
Windows due to an issue with ipv6, however there is already a post-beta1
release available at
http://www.postgresql.org/ftp/binary/v8.1beta/win32/


 



And you can always roll your own.

cheers

andrew

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

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

Re: [HACKERS] Beta2 Wrap Up ...

2005-09-16 Thread Neil Conway

On Fri, 2005-16-09 at 08:47 +0100, Dave Page wrote:
 Perhaps you could allow 24 hours before committing potentially
 controversial changes in future?

My apologies for the rush -- I normally would wait longer for a
consensus. In fact, I _was_ waiting for a consensus until I saw that the
wrap for beta2 was imminent, and therefore I couldn't afford to wait any
longer. I talked to Bruce, and he recommended essentially what I did.

FWIW, you did have more than 24 hours to object or comment on the
proposed changes, as the original email on the subject was sent on
Tuesday:

http://archives.postgresql.org/pgsql-hackers/2005-09/msg00635.php

-Neil



---(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

Re: [HACKERS] Beta2 Wrap Up ...

 -Original Message-
 From: Neil Conway [mailto:[EMAIL PROTECTED] 
 Sent: 16 September 2005 14:57
 To: Dave Page
 Cc: Marc G. Fournier; pgsql-hackers@postgresql.org
 Subject: RE: [HACKERS] Beta2 Wrap Up ...

 On Fri, 2005-16-09 at 08:47 +0100, Dave Page wrote:
  Perhaps you could allow 24 hours before committing potentially
  controversial changes in future?

 My apologies for the rush -- I normally would wait longer for a
 consensus. In fact, I _was_ waiting for a consensus until I 
 saw that the
 wrap for beta2 was imminent, and therefore I couldn't afford 
 to wait any
 longer. I talked to Bruce, and he recommended essentially what I did.

 FWIW, you did have more than 24 hours to object or comment on the
 proposed changes, as the original email on the subject was sent on
 Tuesday:

Yes, I did see that, but then I saw Andreas' response and left it at
that as Tom did.

Oh well...

Regards, Dave

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

Re: [HACKERS] New dot releases

Devrim Gunduz [EMAIL PROTECTED] writes:
 After / before 8.1 Beta 2, are there any plans to release new dot releases 
 for the back branches?

Some day ;-)

What's holding up the back branches at the moment is that the gerbil
buildfarm member is showing failures in the 8.0 branch that started
right after I patched the vacuum/ctid-chain stuff.  That probably
indicates a problem, but the owner of the machine has been completely
unhelpful about providing any information to track it down.

regards, tom lane

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

Re: [HACKERS] Request to clarify on sql_numeric datatype

Raghavendra Reddy [EMAIL PROTECTED] writes:
   While inserting numeric data type into a table can you please let me
 know how you are storing the value in the database.

From src/include/utils/numeric.h:

/*
 * The Numeric data type stored in the database
 *
 * NOTE: by convention, values in the packed form have been stripped of
 * all leading and trailing zero digits (where a digit is of base NBASE).
 * In particular, if the value is zero, there will be no digits at all!
 * The weight is arbitrary in that case, but we normally set it to zero.
 */
typedef struct NumericData
{
int32   varlen; /* Variable size (std varlena header) */
int16   n_weight;   /* Weight of 1st digit  */
uint16  n_sign_dscale;  /* Sign + display scale */
charn_data[1];  /* Digits (really array of NumericDigit) */
} NumericData;

See also the comments at the top of src/backend/utils/adt/numeric.c.

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

Re: [HACKERS] inverse OR distributive law?

2005-09-16 Thread Peter Eisentraut

Am Donnerstag, 15. September 2005 05:04 schrieb Tom Lane:
 I'm not sure that inverse OR distributive law is standard terminology,
 but I believe the implication in the other direction is usually called
 the OR distributive law.  Anyone know of better terminology?

It's still the OR distributive law, it's just the inverse application 
thereof.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

Re: [HACKERS] New dot releases

2005-09-16 Thread Joshua D. Drake




What's holding up the back branches at the moment is that the gerbil
buildfarm member is showing failures in the 8.0 branch that started
right after I patched the vacuum/ctid-chain stuff.  That probably
indicates a problem, but the owner of the machine has been completely
unhelpful about providing any information to track it down.
 


I have a Solaris 9 machine on Sparc that I could let you have
access to. Would that help?

Sincerely,

Joshua D. Drake




regards, tom lane

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




--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/


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

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

Re: [HACKERS] statement logging / extended query protocol issues

Oliver Jowett wrote:
 Bruce Momjian wrote:
 
  We don't have a log_statement = verbose mode.
 
 Please see my earlier email where I suggested adding one if you really 
 wanted all this protocol-level detail logged.

We can't add that in feature freeze/beta, at least.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(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

Re: [HACKERS] inet increment with int

2005-09-16 Thread Patrick Welche

On Fri, Sep 16, 2005 at 06:36:56AM -0400, Bruce Momjian wrote:
 
 This has been saved for the 8.2 release:

It isn't actually a patch for application yet ;-) It is the function in
a state that is easy to test. I take it that as I have basically had
no comments back, I will just go ahead and make a patch for the
function as a built-in...

Cheers,

Patrick

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

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

Re: [HACKERS] statement logging / extended query protocol issues

Oliver Jowett wrote:
 Bruce Momjian wrote:
 
  I think it is more verbose because no FETCH is logged in this type of
  prepare/execute.  The goal, I think, is for these type of queries to
  look as similar to normal PREPARE/EXECUTE and DECLARE/FETCH as possible.
 
 I do not understand why this is a useful thing to do as part of 
 log_statement.
 
 My point is that given JDBC code like this:
 
Statement s = connection.createStatement();
ResultSet rs = s.executeQuery(SELECT * FROM pg_proc);
while (rs.next()) {
   // Process results
}
 
 it seems that the least surprising thing to get logged is simply SELECT 
 * FROM pg_proc.
 
 I don't see how logging a synthetic PREPARE/EXECUTE/FETCH sequence (and 
 DECLARE, now?) is useful. They're not necessarily syntactically correct, 
 and they're certainly not queries that were actually sent to the 
 backend. I thought log_statement was meant to answer the question what 
 queries were submitted to the backend?, rather than to provide a trace 
 of protocol-level activity..

Well, from the application writer perspective, you are right it doesn't
make sense, but this is only because jdbc is using prepare internally. 
If you were to have written it in libpq, it would make sense, I think,
and internally, this is what is happening.  We can't assume only
interface libraries like jdbc are using this feature.

As far as I understand things, the protocol-level prepare/execute is
identical to the SQL-level prepare/execute, except that there is no need
to parse the execute, so it should log like the SQL-level statements, if
possible.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [HACKERS] New dot releases

2005-09-16 Thread Michael Fuhr

On Fri, Sep 16, 2005 at 07:57:08AM -0700, Joshua D. Drake wrote:
 What's holding up the back branches at the moment is that the gerbil
 buildfarm member is showing failures in the 8.0 branch that started
 right after I patched the vacuum/ctid-chain stuff.  That probably
 indicates a problem, but the owner of the machine has been completely
 unhelpful about providing any information to track it down.
 
 I have a Solaris 9 machine on Sparc that I could let you have
 access to. Would that help?

FWIW, I have a Solaris 9/sparc box with gcc 3.4.2 (same setup as
gerbil) and have no problems with REL7_2_STABLE through HEAD.  I'll
test REL8_0_STABLE with gerbil's configure options when I get a
chance.

Most of gerbil's errors are:

creating information schema ... Bus Error - core dumped

but a few are:

creating template1 database in 
/home/pgbuildfarm/build-farm-2.05/REL8_0_STABLE/pgsql.5942/src/test/regress/./tmp_check/data/base/1
 ... FATAL:  shmat(id=8326) failed: Not enough space

-- 
Michael Fuhr

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

Re: [HACKERS] US Census database (Tiger 2004FE) - 4.4G

2005-09-16 Thread Joshua D. Drake


Darcy Buskermolen wrote:

On Thursday 04 August 2005 09:37, Gavin M. Roy wrote:


You can send it to me, and ehpg will host it.  I'll send you a
private email with my info.

Gavin

On Aug 4, 2005, at 8:26 AM, Mark Woodward wrote:


It is 4.4G in space in a gzip package.

I'll mail a DVD to two people who promise to host it for Hackers.


Command Prompt would be willing to host it.

Sincerely,

Joshua D. Drake





I'm wondering if this is now available for consumption by the rest of us??  

(ie what's the link) 






--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

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

  http://archives.postgresql.org

[HACKERS] Why does VACUUM FULL bother locking pages?

2005-09-16 Thread Alvaro Herrera

Hackers,

I'm reading the vacuum code and I just noticed that the routines
move_plain_tuple and move_chain_tuple expect the dest and source blocks
to be locked, and unlock them at exit.  The only caller of both is
repair_frag, whose only caller in turn is full_vacuum_rel.  Same thing
for update_hint_bits.

So, the only callers of both has already acquired appropiate locks at
the relation level -- nobody is going to be modifying the blocks while
they proceed.  So why bother locking the pages at all?  Is there a
reason or is this an historical accident?

-- 
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
Puedes vivir solo una vez, pero si lo haces bien, una vez es suficiente

---(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] Spinlocks, yet again: analysis and proposed patches

2005-09-16 Thread Josh Berkus

Tom,

 What I think this means is that the kernel is scheduling the 2 processes
 onto 2 processors chosen-at-random, without awareness of whether those
 two processors are on the same chip (in the Xeon case) or have closer
 NUMA affinity (in the Opteron case).

That would be consistent with my experience with HT, and the reason why many 
software vendors recommend disabling it.  Not sure about NUMA.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

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

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

Re: [HACKERS] Remove xmin and cmin from frozen tuples


This is now in the TODO list:

* Merge xmin/xmax/cmin/cmax back into three header fields

  Before subtransactions, there used to be only three fields needed to
  store these four values. This was possible because only the current
  transaction looks at the cmin/cmax values. If the current transaction
  created and expired the row the fields stored where xmin (same as
  xmax), cmin, cmax, and if the transaction was expiring a row from a
  another transaction, the fields stored were xmin (cmin was not
  needed), xmax, and cmax. Such a system worked because a transaction
  could only see rows from another completed transaction. However,
  subtransactions can see rows from outer transactions, and once the
  subtransaction completes, the outer transaction continues, requiring
  the storage of all four fields. With subtransactions, an outer
  transaction can create a row, a subtransaction expire it, and when the
  subtransaction completes, the outer transaction still has to have
  proper visibility of the row's cmin, for example, for cursors.

  One possible solution is to create a phantom cid which represents a
  cmin/cmax pair and is stored in local memory.  Another idea is to
  store both cmin and cmax only in local memory.


---

Tom Lane wrote:
 Alvaro Herrera [EMAIL PROTECTED] writes:
  I'm curious to know how can you store the cmin/cmax pair completely out
  of the tuple.  It's easy to see how to store a single identifier in each
  tuple that would be an index to a structure in local memory.  However,
  to eliminate both you'd have to keep a list of all tuples you have
  created or obsoleted, with the cmin and cmax of each.  This seems like
  an awful amount of memory.
 
 Yeah.  I think a reasonable compromise scheme is to try to get down to
 three fields per tuple:
 
   xminsame as now
   xmaxsame as now
   cid/xvac
 
 xvac can share storage with the command ID info as long as VACUUM FULL
 never tries to move a tuple whose originating or deleting transaction
 is still running ... which is pretty much the same restriction we had
 before.
 
 For the command IDs, I am imagining:
 
 if created in current transaction: use cid to store cmin
 
 if deleted in current transaction: use cid to store cmax
 
 if both created and deleted in current transaction: cid is an index
 into an in-memory data structure that contains cmin and cmax.
 
 current transaction would have to have the loose definition that
 includes any subxact of the current top xact, but still, I think that
 the case where you need both fields is relatively uncommon.
 
 The in-memory data structure would only need to contain an entry for
 each distinct combination of cmin and cmax used in the current xact,
 so I think we could assume that it would never get unreasonably large.
 The entries would be created on demand much like we do for
 multixact ids (I guess you'd want a hash table to map requested
 cmin/cmax to an existing entry ID quickly).
 
   regards, tom lane
 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(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

Re: [HACKERS] inet increment with int

Patrick Welche wrote:
 On Fri, Sep 16, 2005 at 06:36:56AM -0400, Bruce Momjian wrote:
  
  This has been saved for the 8.2 release:
 
 It isn't actually a patch for application yet ;-) It is the function in
 a state that is easy to test. I take it that as I have basically had
 no comments back, I will just go ahead and make a patch for the
 function as a built-in...

Right.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(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] Rendezvous/Bonjour broken in 8.1 beta


Added to TODO:

* Update Bonjour to work with newer cross-platform SDK


---

Sandy McArthur wrote:
 FYI: The DNSServiceDiscovery.h is Mac OS X specific and only kept
 around for Mac OS X 10.2 and older compatiability. Apple has moved to
 a cross platform impelentation with the dns_sd.h API available for OS
 X, Win32, and most any posix type system.
 
 Any reference to DNSServiceDiscovery.h in pgsql should go away (unless
 you want to keep support for pre-10.3 Mac OS X) and be replaced with
 the newer dns_sd.h interfaces.
 
 On 9/8/05, Andrew Dunstan [EMAIL PROTECTED] wrote:
  It gets a lot worse. The Windows SDK does not export the same API. None
  of the DNSfoo things we refer to in postmaster.c are defined in the
  supplied header file (not to mention other idiocies I was able to
  negotiate past). Apparently Apple have never heard of code portability.
 
 -- 
 Sandy McArthur
 
 ---(end of broadcast)---
 TIP 2: Don't 'kill -9' the postmaster
 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [HACKERS] New dot releases

2005-09-16 Thread Michael Fuhr

On Fri, Sep 16, 2005 at 09:28:39AM -0600, Michael Fuhr wrote:
 FWIW, I have a Solaris 9/sparc box with gcc 3.4.2 (same setup as
 gerbil) and have no problems with REL7_2_STABLE through HEAD.  I'll
 test REL8_0_STABLE with gerbil's configure options when I get a
 chance.

I just built REL8_0_STABLE with the following configure options
(same as gerbil):

./configure --enable-cassert --enable-debug --enable-nls \
--enable-integer-datetimes --with-perl --with-python \
--with-openssl --with-pgport=5682

gmake check returned the following:

==
 All 96 tests passed. 
==

-- 
Michael Fuhr

---(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] Beta2 Bundled ...

2005-09-16 Thread Marc G. Fournier



'k, beta2 is bundled and available on the main ftp server ... will 
propogate over the next little while to the various mirrors ... the 
announcement for this will go out late Sun/early Mon, to give Dave a 
chance to get the Windows Installer built and available at the same time 
...


I'm going to be offline for the next ~36hrs or so, but if there are any 
problems with the bundle, please let me know and I'll get it fixed as soon 
as I do get back online ...



---(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] when started century? PostgreSQL vs Oracle diff

2005-09-16 Thread Greg Stark

Pavel Stehule [EMAIL PROTECTED] writes:

 Hello
 
 I am testing comformity between PostgreSQL and Oracle. I found one difference.
 Century started 1900-01-01 for Oracle and 1901-01-01 for PostgreSQL. What 
 value
 is good?

out of curiosity, what was your actual test?

-- 
greg


---(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] Spinlocks, yet again: analysis and proposed patches

2005-09-16 Thread Greg Stark

Josh Berkus josh@agliodbs.com writes:

 Tom,
 
  What I think this means is that the kernel is scheduling the 2 processes
  onto 2 processors chosen-at-random, without awareness of whether those
  two processors are on the same chip (in the Xeon case) or have closer
  NUMA affinity (in the Opteron case).
 
 That would be consistent with my experience with HT, and the reason why many 
 software vendors recommend disabling it.  Not sure about NUMA.

What version of linux was this test with? What you describe was certainly well
known for Linux 2.4 and earlier. It was well on its way to becoming
established dogma cargo-cult style. 

However I was under the impression that 2.6 had moved beyond that problem.
It would be very interesting to know if 2.6 still suffers from this.

Also, those who recommend disabling HT on this basis should realize it didn't
penalize the 2 process case the same way. You may be getting a benefit only
in the cases where your system isn't heavily loaded. Ie, only when it doesn't
really matter.


-- 
greg


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

   http://archives.postgresql.org

Re: [HACKERS] statement_timeout logging

Simon Riggs wrote:
 Currently, when we set a statement_timeout and a query runs over that
 time there is no log message to say that the statement has timed out. We
 do get a message which says 
   ERROR:  canceling query due to user request
 and so in the server log it is impossible to tell the difference between
 a query that has been cancelled deliberately by the user and a query
 whose time(out) has come.

I have updated the message to:

 errmsg(canceling query due to user request or statement timeout)));

 The timeout causes the query to be cancelled, which is an ERROR, so it
 is possible that the query will be logged if log_min_error_statement is
 set to ERROR or below. Again, there is no way to tell the difference
 between an ERROR statement that has occurred during execution and an
 ERROR statement that is generated by a syntax error. So, if you do set
 log_min_error_statement=ERROR then you get swamped by syntax errors
 rather than being able to see statement timeouts.
 
 Logging these queries ought to be a valuable source of tuning
 information, but as a result of the above, this is not currently the
 case.
 
 For now, I've written a quick patch to the log the query text with a
 suitable error message.
 
 I expect some debate as to whether the line
  if (log_min_error_statement  WARNING)
 should be there at all, so comments are sought.

I don't see why printing the query cancel from a timeout is any more
special than a user request for cancel or a simple query error.  If
users want statements to be printed on error, they will configure things
that way, if not, we should not print them.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(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

Re: [HACKERS] Spinlocks, yet again: analysis and proposed patches

2005-09-16 Thread Stephen Frost

* Greg Stark ([EMAIL PROTECTED]) wrote:
 However I was under the impression that 2.6 had moved beyond that problem.
 It would be very interesting to know if 2.6 still suffers from this.

The tests on the em64t at my place were using 2.6.12.  I had thought 2.6
was better about this too, but I don't have another explanation for it.
CONFIG_SMT was enabled, etc..

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] statement_timeout logging

pgman wrote:
 Simon Riggs wrote:
  Currently, when we set a statement_timeout and a query runs over that
  time there is no log message to say that the statement has timed out. We
  do get a message which says 
  ERROR:  canceling query due to user request
  and so in the server log it is impossible to tell the difference between
  a query that has been cancelled deliberately by the user and a query
  whose time(out) has come.
 
 I have updated the message to:
 
  errmsg(canceling query due to user request or statement timeout)));

Oops.  Did we freeze the message strings already for this release?

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(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] Why does VACUUM FULL bother locking pages?

2005-09-16 Thread Jonah H. Harris

Was it relcache related?On 9/16/05, Alvaro Herrera [EMAIL PROTECTED] wrote:
Hackers,I'm reading the vacuum code and I just noticed that the routinesmove_plain_tuple and move_chain_tuple expect the dest and source blocksto be locked, and unlock them at exit.The only caller of both is
repair_frag, whose only caller in turn is full_vacuum_rel.Same thingfor update_hint_bits.So, the only callers of both has already acquired appropiate locks atthe relation level -- nobody is going to be modifying the blocks while
they proceed.So why bother locking the pages at all?Is there areason or is this an historical accident?--Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
Puedes vivir solo una vez, pero si lo haces bien, una vez es suficiente---(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-- Respectfully,Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation
http://www.enterprisedb.com/

Re: [HACKERS] statement_timeout logging

2005-09-16 Thread Alvaro Herrera

On Fri, Sep 16, 2005 at 03:41:11PM -0400, Bruce Momjian wrote:
 pgman wrote:

  I have updated the message to:
  
   errmsg(canceling query due to user request or statement timeout)));
 
 Oops.  Did we freeze the message strings already for this release?

Not yet.

I'm not sure I agree with the wording though -- is there no way to tell
the difference?

-- 
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
No single strategy is always right (Unless the boss says so)
  (Larry Wall)

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

Re: [HACKERS] Why does VACUUM FULL bother locking pages?

2005-09-16 Thread Alvaro Herrera

On Fri, Sep 16, 2005 at 04:41:39PM -0400, Jonah H. Harris wrote:
 Was it relcache related?

I don't see how -- any user of a relcache entry needs to heap_open() or
relation_open() the table and acquire an appropiate lock.  Any such call
would block because of the lock that VACUUM FULL acquires on the relation.

 On 9/16/05, Alvaro Herrera [EMAIL PROTECTED] wrote:
  
  Hackers,
  
  I'm reading the vacuum code and I just noticed that the routines
  move_plain_tuple and move_chain_tuple expect the dest and source blocks
  to be locked, and unlock them at exit. The only caller of both is
  repair_frag, whose only caller in turn is full_vacuum_rel. Same thing
  for update_hint_bits.
  
  So, the only callers of both has already acquired appropiate locks at
  the relation level -- nobody is going to be modifying the blocks while
  they proceed. So why bother locking the pages at all? Is there a
  reason or is this an historical accident?

-- 
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
Now I have my system running, not a byte was off the shelf;
It rarely breaks and when it does I fix the code myself.
It's stable, clean and elegant, and lightning fast as well,
And it doesn't cost a nickel, so Bill Gates can go to hell.

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

Re: [HACKERS] statement logging / extended query protocol issues

2005-09-16 Thread Oliver Jowett

Bruce Momjian wrote:

 Well, from the application writer perspective, you are right it doesn't
 make sense,

This is exactly what the end user is going to say.

 but this is only because jdbc is using prepare internally. 

Isn't this mostly irrelevant to the result we want to see? It's a detail
of how the interface layer chooses to execute its queries, and 90% of
the time the end user is not going to know or care about it.

 If you were to have written it in libpq, it would make sense, I think,
 and internally, this is what is happening.  We can't assume only
 interface libraries like jdbc are using this feature.

Wait, so is the extended query protocol the poor cousin of what libpq
does, or what? You can do Parse/Bind using libpq, can't you?

The *meaning* of the Parse/Bind/Execute sequence is quite clear
regardless of what interface library is used. I still think that logging
just the queries that were actually executed, once per execution, is the
sensible thing to do here. I can't see a sequence of protocol messages
that would produce a strange result if we used the rules I suggested --
do you have an example where it breaks?

 As far as I understand things, the protocol-level prepare/execute is
 identical to the SQL-level prepare/execute, except that there is no need
 to parse the execute, so it should log like the SQL-level statements, if
 possible.

You can Parse any SQL statement, but you can't PREPARE any SQL
statement. So, no, they're not equivalent. That's one aspect of what I
meant about generating synthetic statements that weren't syntactially
correct (the strange FETCH syntax with ROWS/MAXROWS that Simon was
suggesting is another case).

-O

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

Re: [HACKERS] Why does VACUUM FULL bother locking pages?

2005-09-16 Thread Jonah H. Harris

I'm probably wrong, but I thought vacuum may invalidate stuff which
semi-required the cache to be flushed. :) I'll go take a
look through as-well but it's hard to imagine this being overlooked for
so long.

Sorry Alvaro, I haven't gone out to look at vacuum in awhile so I ain't much help.
On 9/16/05, Alvaro Herrera [EMAIL PROTECTED] wrote:
On Fri, Sep 16, 2005 at 04:41:39PM -0400, Jonah H. Harris wrote: Was it relcache related?I don't see how -- any user of a relcache entry needs to heap_open() orrelation_open() the table and acquire an appropiate lock.Any such call
would block because of the lock that VACUUM FULL acquires on the relation. On 9/16/05, Alvaro Herrera [EMAIL PROTECTED] wrote: Hackers,
I'm reading the vacuum code and I just noticed that the routines move_plain_tuple and move_chain_tuple expect the dest and source blocks to be locked, and unlock them at exit. The only caller of both is
repair_frag, whose only caller in turn is full_vacuum_rel. Same thing for update_hint_bits. So, the only callers of both has already acquired appropiate locks at the relation level -- nobody is going to be modifying the blocks while
they proceed. So why bother locking the pages at all? Is there a reason or is this an historical accident?--Alvaro Herrera -- Valdivia, Chile Architect,
www.EnterpriseDB.comNow I have my system running, not a byte was off the shelf;It rarely breaks and when it does I fix the code myself.It's stable, clean and elegant, and lightning fast as well,And it doesn't cost a nickel, so Bill Gates can go to hell.
-- Respectfully,Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporationhttp://www.enterprisedb.com/

Re: [HACKERS] Why does VACUUM FULL bother locking pages?

2005-09-16 Thread Simon Riggs

 Alvaro Herrera wrote
 The only caller of both is
 repair_frag, whose only caller in turn is full_vacuum_rel.

...bgwriter still needs to access blocks. The WAL system relies on the
locking behaviour for recoverability, see comments in LockBuffer() and
SyncOneBuffer().

...I do think there's lots still to optimise in VACUUM FULL though...

Best Regards, Simon Riggs


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

Re: [HACKERS] statement_timeout logging

2005-09-16 Thread Simon Riggs

 From: Bruce Momjian [mailto:[EMAIL PROTECTED]
 I don't see why printing the query cancel from a timeout is any more
 special than a user request for cancel or a simple query error.  If
 users want statements to be printed on error, they will
 configure things
 that way, if not, we should not print them.

The log is for admins, not users.

If an admin sets statement_timeout, then it is a good idea to have the
SQL logged as a way of gaining performance information  for the *admin*,
who knows less about what users do, yet is still charged with the need
to tune if one user is effecting other users/jobs. That's a different
scenario than a user cancelling their query. The user knows they've
cancelled it, so they already know the SQL and can ask the admin if they
want it faster.  Generally the admin doesn't care if a user cancelled a
query, since it might be for a whole host of reasons, whereas a query
cancelled for statement_timeout has one specific cause.

So, I still want this functionality, so the bad query is in the logs.

Best Regards, Simon Riggs


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

Re: [DOCS] [HACKERS] statement_timeout logging

2005-09-16 Thread Simon Riggs

Alvaro Herrera
 On Fri, Sep 16, 2005 at 03:41:11PM -0400, Bruce Momjian wrote:
   I have updated the message to:
  
errmsg(canceling query due to user request or
 statement timeout)));
 
  Oops.  Did we freeze the message strings already for this release?

 Not yet.

 I'm not sure I agree with the wording though -- is there no
 way to tell
 the difference?

Yes, my patch. :-)

The message is generated following receipt of a SIGINT which is sent by
kill() after the statement_timeout.

My patch was able to generate a different message by logging just before
the kill() is called. (Obviously, there isn't much after in this
circumstance).

Best Regards, Simon Riggs


---(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] statement_timeout logging

Simon Riggs wrote:
  From: Bruce Momjian [mailto:[EMAIL PROTECTED]
  I don't see why printing the query cancel from a timeout is any more
  special than a user request for cancel or a simple query error.  If
  users want statements to be printed on error, they will
  configure things
  that way, if not, we should not print them.
 
 The log is for admins, not users.
 
 If an admin sets statement_timeout, then it is a good idea to have the
 SQL logged as a way of gaining performance information  for the *admin*,
 who knows less about what users do, yet is still charged with the need
 to tune if one user is effecting other users/jobs. That's a different
 scenario than a user cancelling their query. The user knows they've
 cancelled it, so they already know the SQL and can ask the admin if they
 want it faster.  Generally the admin doesn't care if a user cancelled a
 query, since it might be for a whole host of reasons, whereas a query
 cancelled for statement_timeout has one specific cause.
 
 So, I still want this functionality, so the bad query is in the logs.

Well, the user can use use statement_timeout too, so it isn't just the
admin.  Also, logging queries can be a security issue, so the idea of
logging the query on duration timeout by default seems like a bad idea. 

We can go three ways.  We can add a boolean GUC to control printing of
the query during a timeout, but that seems like overkill.  We can add a
new level for log_min_error_statement that is just above error, but that
seems confusing.  I think the right solution would be to allow
log_min_duration_statement to work for canceled queries.  Right now,
log_min_duration_statement doesn't work for canceled queries because the
query never completes to give a final duration and hit the test code. 
Should that be fixed now or added to the TODO list?

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(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] BEOS code

Can we remove the BEOS-specific code?  I don't think anyone has reported
it working for quite a few releases.  If someone wants to take it up
later, they can pull from the CVS commit that removed it.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

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

Re: [DOCS] [HACKERS] statement_timeout logging

Simon Riggs wrote:
 Alvaro Herrera
  On Fri, Sep 16, 2005 at 03:41:11PM -0400, Bruce Momjian wrote:
I have updated the message to:
   
 errmsg(canceling query due to user request or
  statement timeout)));
  
   Oops.  Did we freeze the message strings already for this release?
 
  Not yet.
 
  I'm not sure I agree with the wording though -- is there no
  way to tell
  the difference?
 
 Yes, my patch. :-)
 
 The message is generated following receipt of a SIGINT which is sent by
 kill() after the statement_timeout.
 
 My patch was able to generate a different message by logging just before
 the kill() is called. (Obviously, there isn't much after in this
 circumstance).

Your patch just prints a statement before printing the cancel message.
What we should do is to print the proper cause of the cancel, and I am
working on a patch for that.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [HACKERS] Spinlocks, yet again: analysis and proposed patches

Stephen Frost [EMAIL PROTECTED] writes:
 * Greg Stark ([EMAIL PROTECTED]) wrote:
 However I was under the impression that 2.6 had moved beyond that problem.
 It would be very interesting to know if 2.6 still suffers from this.

 The tests on the em64t at my place were using 2.6.12.  I had thought 2.6
 was better about this too, but I don't have another explanation for it.

The 4-way Opteron I've been using at Red Hat is running
2.6.12-1.1398_FC4smp (Fedora Core 4 obviously).  Red Hat in particular
has been working hard in this area, and I thought that their recent
kernels included NUMA fixes that weren't yet accepted upstream (at least
not in the stable kernel branches).  But it seems there's still a ways
to go yet.

It'd be real interesting to see comparable numbers from some non-Linux
kernels, particularly commercial systems like Solaris.

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] Spinlocks, yet again: analysis and proposed patches

2005-09-16 Thread Gavin Sherry

On Sat, 17 Sep 2005, Tom Lane wrote:

 Stephen Frost [EMAIL PROTECTED] writes:
  * Greg Stark ([EMAIL PROTECTED]) wrote:
  However I was under the impression that 2.6 had moved beyond that problem.
  It would be very interesting to know if 2.6 still suffers from this.

  The tests on the em64t at my place were using 2.6.12.  I had thought 2.6
  was better about this too, but I don't have another explanation for it.

 The 4-way Opteron I've been using at Red Hat is running
 2.6.12-1.1398_FC4smp (Fedora Core 4 obviously).  Red Hat in particular
 has been working hard in this area, and I thought that their recent
 kernels included NUMA fixes that weren't yet accepted upstream (at least
 not in the stable kernel branches).  But it seems there's still a ways
 to go yet.

 It'd be real interesting to see comparable numbers from some non-Linux
 kernels, particularly commercial systems like Solaris.

Did you see the Solaris results I posted?

Gavin

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

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

Re: [HACKERS] Why does VACUUM FULL bother locking pages?

Alvaro Herrera [EMAIL PROTECTED] writes:
 So, the only callers of both has already acquired appropiate locks at
 the relation level -- nobody is going to be modifying the blocks while
 they proceed.  So why bother locking the pages at all?  Is there a
 reason or is this an historical accident?

No, because operations such as checkpointing and bgwriter will feel free
to write out pages that aren't exclusive-locked; they don't try to get
a lock at the table level.  Failing to lock the buffer would risk
allowing an invalid page state to be written to disk --- which, if we
then crashed before writing the WAL record for the vacuum operation,
would represent unrecoverable corruption.

regards, tom lane

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

Re: [HACKERS] Spinlocks, yet again: analysis and proposed patches

Gavin Sherry [EMAIL PROTECTED] writes:
 On Sat, 17 Sep 2005, Tom Lane wrote:
 It'd be real interesting to see comparable numbers from some non-Linux
 kernels, particularly commercial systems like Solaris.

 Did you see the Solaris results I posted?

Are you speaking of
http://archives.postgresql.org/pgsql-hackers/2005-09/msg00715.php
?

That doesn't seem directly relevant to the point, because it's for a
2-CPU machine; so there's no way to run a test case that uses more than
one but less than all the processors.  In either the one or all
cases, performance ought to be pretty stable regardless of whether the
kernel understands about any processor asymmetries that may exist in
the hardware.  Not to mention that I don't know of any asymmetries in
a dual SPARC anyway.  We really need to test this on comparable
hardware, which I guess means we need Solaris/x86 on something with
hyperthreading or known NUMA asymmetry.

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

Re: [HACKERS] BEOS code