Re: [HACKERS] Auto-delete large objects when referencing row is deleted

2009-04-08 Thread higepon
Hi.
 itagaki.takah...@oss.ntt.co.jp wrote:

 (It would be a rare case, but) A large object might be referenced
 by two or more rows because LO interface is split into two steps;
 allocating oid and storing data for it. The oid could be stored in
 two or more places and auto deletion would break such usecases.

Indeed. We have to check the references on garbage collecting.
For this reason, my plan B Merge contrib/vacumelo to VACUUM is
easier to implement.

 BTW, bytea and TOASTing would works perfectly as you expected.
 Why don't you use bytea instead of large objects? In other words,
 what you want actually is not LO improvement but efficient TOASTing, no?

First of all, what I want is to contribute to PostgreSQL community by
writing patches.
And picked this issue up from TODO list.
So if there's no need to do about this issue, I will pick up another one :-)

I've checked some articles about Oid large objects vs bytea.
If I understand them correctly, I think
both large objects and bytea are useful for different situations.
Neither of them are obsolete.

Is there no need to do about this issue?

Cheers.

==
the negative points of bytea:
  memory hungry.
  slower than large objects.
  1GB limitation.

the negative points of large objects:
  ghost problem (no auto-delete).
  unable to store number of objects greater than 2^32.
==

-
Taro Minowa(Higepon)

Cybozu Labs, Inc.   

http://www.monaos.org/
http://code.google.com/p/mosh-scheme/


On Wed, Apr 8, 2009 at 1:15 PM, Itagaki Takahiro
itagaki.takah...@oss.ntt.co.jp wrote:

 higepon hige...@gmail.com wrote:

 As a user of database, I think contrib/lo is not the best way.
 Because it's not a part of core PostgreSQL, users may forget to use them.
 Or it is a little messy to use.
 So I think we need to implement *Auto* delete functionality in PostgreSQL 
 core.

 (It would be a rare case, but) A large object might be referenced
 by two or more rows because LO interface is split into two steps;
 allocating oid and storing data for it. The oid could be stored in
 two or more places and auto deletion would break such usecases.

 BTW, bytea and TOASTing would works perfectly as you expected.
 Why don't you use bytea instead of large objects? In other words,
 what you want actually is not LO improvement but efficient TOASTing, no?

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




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] psql \d commands and information_schema

2009-04-08 Thread Martin Pihlak
Tom Lane wrote:
 Well, if they're all in your search_path then plain old \df will do
 fine.  If they're not in your search path then I think it gets pretty
 questionable whether they're user defined in a real sense.  It seems
 more likely that you've got a pile of modules loaded, and which of those
 modules is user defined for your immediate purposes is something that
 psql can't hope to intuit.
 

I my environment schemas are used for namespace separation, so it doesn't
make much sense to use search_path to pull everything back into a single
namespace. Might as well use public for everything then. And these are not
really modules, just ordinary user objects in separate namespaces.

The main benefit of the U switch is that it enables to get a quick overview
of whats deployed to the database. At the moment this is not possible as the
*.* listings are polluted with system objects.

PS. The original \dfU suggestion was made by you in
http://archives.postgresql.org/message-id/28027.1206976...@sss.pgh.pa.us.
And seems, it didn't receive too much negative feedback then.

regards,
Martin


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Array types

2009-04-08 Thread John Lister

brilliant i'll give it a go...  Now to sort out java :)

James Pye wrote:

On Apr 7, 2009, at 12:54 PM, John Lister wrote:

Cheers, nice to know it is possible... Now to see if i can get 
java/python to do the same :) or to use a modified libpq somehow...


http://python.projects.postgresql.org will do it for Python. =D

tho, only supports Python 3, which is still quite new.



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Solution of the file name problem of copy on windows.

2009-04-08 Thread Hiroshi Inoue
Tom Lane wrote:
 Hiroshi Saito z-sa...@guitar.ocn.ne.jp writes:
 I want to solve one problem before the release of 8.4.
 However, since it also seems to be the new feature,
 if not enough for 8.4, you may suggest that it is 8.5.
 
 I'm not too clear on what this is really supposed to accomplish, but
 we are hardly going to put code like that into every single file access
 in Postgres, which is what seems to be the logical implication.
 Shouldn't we just tell people to use a database encoding that matches
 their system environment?

Unfortunately (as usual) under Japanese Windows there's no database
encoding that matches the system environment.
As for the file name in COPY command, there's little meaning to
convert it to the server encoding because the file name is irrelevant
to the database. Because Windows is Unicode(UTF-16) based, it seems
natural to convert the file name to wide characters once.

regards,
Hiroshi Inoue


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Re: [COMMITTERS] pgsql: Explicitly bind gettext to the correct encoding on Windows.

2009-04-08 Thread Heikki Linnakangas

Magnus Hagander wrote:

Tom Lane wrote:

Magnus Hagander mag...@hagander.net writes:

Tom Lane wrote:

What makes more sense to me is to add a table to encnames.c that
provides the gettext name of every encoding that we support.

Do you mean a separate table there, or should we add a new column to one
of the existing tables?

Whichever seems to make more sense is fine with me.  I just don't want
add-an-encoding maintenance requirements spread across N different
source files.


I was about to start looking at this when that other thread
(http://archives.postgresql.org//pgsql-hackers/2009-03/msg01270.php)
started about related issues on other platforms. Seems we should have a
coordinated fix for this, so I'm going to want and see what come sout
of that one. Unless I'm misunderstanding thigns and they're not related?


I've committed a fairly trivial patch per Peter's suggestion to fix the 
other thread's issue. I left the table as is, so whatever refactorings 
were planned can now be applied.


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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] NaN support in NUMERIC data type

2009-04-08 Thread Sam Mason
On Tue, Apr 07, 2009 at 12:51:21PM -0400, Tom Lane wrote:
 Sam Mason s...@samason.me.uk writes:
SELECT 'NaN'::float8;
SELECT ' NaN'::float8;
SELECT 'NaN '::float8;
SELECT '+NaN'::float8;
SELECT '-NaN'::float8;
 
 Well, the +- part must be an artifact of your strtod() implementation;
 our own code isn't doing anything to accept that.  I think it's pretty
 bogus --- NaNs do not have signs.

OK, didn't actually check the code with that; no point worrying about it
then.

 IIRC, the explicit support for leading/trailing spaces is something that
 we added in float8in long after numeric_in was written, and I think just
 nobody thought about numeric at the time.  But it's clearly inconsistent
 to allow spaces around a regular value but not a NaN.

Good, I wanted to make sure it wasn't a deliberate thing before doing
too much.

 Possibly the logic for leading/trailing spaces could be pulled out
 of set_var_from_str and executed in numeric_in instead?

Yes, I didn't want to do this before because it's called from a
couple of other places.  I looked again and realised that we're
generating those in very fixed formats so don't need to worry about
leading/trailing spaces and hence can move the code up to numeric_in.

The attached patch gives set_var_from_str an endptr similar to strtod
so handling is closer to the float[48]in code.  I moved error reporting
code outside as well to cut down on the multiple identical calls.

The included patch was generated against 8.3.5 (because that's what I
had lying around when I started playing) but applies with a little fuzz
to the latest snapshot and does the right thing for me in both versions.

-- 
  Sam  http://samason.me.uk/
*** src/backend/utils/adt/numeric.c	2009-04-07 16:49:08.0 +0100
--- src/backend/utils/adt/numeric.c	2009-04-08 11:53:13.0 +0100
***
*** 238,244 
  static void free_var(NumericVar *var);
  static void zero_var(NumericVar *var);
  
! static void set_var_from_str(const char *str, NumericVar *dest);
  static void set_var_from_num(Numeric value, NumericVar *dest);
  static void set_var_from_var(NumericVar *value, NumericVar *dest);
  static char *get_str_from_var(NumericVar *var, int dscale);
--- 238,244 
  static void free_var(NumericVar *var);
  static void zero_var(NumericVar *var);
  
! static void set_var_from_str(const char *str, const char ** endptr, NumericVar *dest);
  static void set_var_from_num(Numeric value, NumericVar *dest);
  static void set_var_from_var(NumericVar *value, NumericVar *dest);
  static char *get_str_from_var(NumericVar *var, int dscale);
***
*** 310,315 
--- 310,317 
  numeric_in(PG_FUNCTION_ARGS)
  {
  	char	   *str = PG_GETARG_CSTRING(0);
+ 	char	   *orig_str;
+ 	const char *endptr;
  
  #ifdef NOT_USED
  	Oid			typelem = PG_GETARG_OID(1);
***
*** 318,335 
  	NumericVar	value;
  	Numeric		res;
  
! 	/*
! 	 * Check for NaN
  	 */
! 	if (pg_strcasecmp(str, NaN) == 0)
! 		PG_RETURN_NUMERIC(make_result(const_nan));
  
  	/*
  	 * Use set_var_from_str() to parse the input string and return it in the
  	 * packed DB storage format
  	 */
  	init_var(value);
! 	set_var_from_str(str, value);
  
  	apply_typmod(value, typmod);
  
--- 320,369 
  	NumericVar	value;
  	Numeric		res;
  
! 	/* 
! 	 * To allow us to generate sensible error messages we tuck the
! 	 * original start of the string away so we can use it later.
  	 */
! 	orig_str = str;
! 
! 	/* skip leading spaces */
! 	while (isspace((unsigned char) *str))
! 		str++;
  
  	/*
  	 * Use set_var_from_str() to parse the input string and return it in the
  	 * packed DB storage format
  	 */
  	init_var(value);
! 	set_var_from_str(str, endptr, value);
! 
! 	/*
! 	 * we didn't see anything that looked like a numeric value
! 	 */
! 	if (str == endptr) {
! 		/*
! 		 * Check for NaN
! 		 */
! 		if (pg_strncasecmp(str, NaN, 3) == 0) {
! 			value = const_nan;
! 			endptr = str + 3;
! 		} else
! 			ereport(ERROR,
! 	(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
! 	 errmsg(invalid input syntax for type numeric: \%s\,
! 			orig_str)));
! 	}
! 
! 	/* skip trailing spaces */
! 	while (isspace((unsigned char) *endptr))
! 		endptr++;
! 
! 	if (*endptr != '\0') {
! 		ereport(ERROR,
! (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
!  errmsg(invalid input syntax for type numeric: \%s\,
! 		orig_str)));
! 	}
  
  	apply_typmod(value, typmod);
  
***
*** 2056,2061 
--- 2090,2096 
  	Numeric		res;
  	NumericVar	result;
  	char		buf[DBL_DIG + 100];
+ 	const char *endptr;
  
  	if (isnan(val))
  		PG_RETURN_NUMERIC(make_result(const_nan));
***
*** 2064,2070 
  
  	init_var(result);
  
! 	set_var_from_str(buf, result);
  	res = make_result(result);
  
  	free_var(result);
--- 2099,2111 
  
  	init_var(result);
  
! 	set_var_from_str(buf, endptr, result);
! 	if (endptr == buf) {
! 		ereport(ERROR,
! 

Re: [HACKERS] More message encoding woes

2009-04-08 Thread Heikki Linnakangas

Peter Eisentraut wrote:

On Tuesday 07 April 2009 13:09:42 Heikki Linnakangas wrote:

Patch attached. Instead of checking for LC_CTYPE == C, I'm checking
pg_get_encoding_from_locale(NULL) == encoding which is more close to
what we actually want. The downside is that
pg_get_encoding_from_locale(NULL) isn't exactly free, but the upside is
that we don't need to keep this in sync with the rules we have in CREATE
DATABASE that enforce that locale matches encoding.


I would have figured we can skip this whole thing when LC_CTYPE != C, because 
it should be guaranteed that LC_CTYPE matches the database encoding in this 
case, no?


Ok, committed it like that after all.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [COMMITTERS] pgsql: Tell gettext which codeset to use by calling

2009-04-08 Thread Teodor Sigaev

Log Message:
---
Tell gettext which codeset to use by calling bind_textdomain_codeset(). We
already did that on Windows, but it's needed on other platforms too when
LC_CTYPE=C. With other locales, we enforce (or trust) that the codeset of
the locale matches the server encoding so we don't need to bind it
explicitly. It should do no harm in that case either, but I don't have
full faith in the PG encoding - OS codeset mapping table yet. Per recent
discussion on pgsql-hackers.


% CFLAGS=-O0 ./configure  --enable-depend --enable-cassert --enable-debug 
--disable-coverage

% gmake
.
utils/init/postinit.o(.text+0x697): In function `CheckMyDatabase':
/spool/home/teodor/pgsql/src/backend/utils/init/postinit.c:269: undefined 
reference to `textdomain'

gmake[2]: *** [postgres] Ошибка 1
gmake[2]: Leaving directory `/spool/home/teodor/pgsql/src/backend'
gmake[1]: *** [all] Ошибка 2
gmake[1]: Leaving directory `/spool/home/teodor/pgsql/src'
gmake: *** [all] Ошибка 2


May be call of  pg_bind_textdomain_codeset(textdomain(NULL)); should be wrapped 
by ENABLE_NLS?


--
Teodor Sigaev   E-mail: teo...@sigaev.ru
   WWW: http://www.sigaev.ru/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [COMMITTERS] pgsql: Tell gettext which codeset to use by calling

2009-04-08 Thread Heikki Linnakangas

Teodor Sigaev wrote:
May be call of  pg_bind_textdomain_codeset(textdomain(NULL)); should be 
wrapped by ENABLE_NLS?


Yep, fixed. Thanks.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Closing some 8.4 open items

2009-04-08 Thread Robert Haas
On Wed, Apr 8, 2009 at 1:17 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com writes:
 On Tue, Apr 7, 2009 at 10:42 PM, Josh Berkus j...@agliodbs.com wrote:
 So has fadvise been completely dropped from 8.4, or only partially?

 Bitmap scans will support it, but index scans will not.

 And please note that we think bitmap scans are the larger part of
 the win anyway.  What's left undone there is some marginal mopup.

Can you elaborate on this?  I'm fuzzy on why index scans can't benefit
from this as much as bitmap index scans.

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] plpgsql debugger (pldbg) absent from 8.4?

2009-04-08 Thread Kevin Field
 I'll see if I can get an updated build pushed out sometime today.

I finally got around to trying this out using the March 24th build,
and it has the same issue...

Kev

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Solution of the file name problem of copy on windows.

2009-04-08 Thread Hiroshi Saito

Hi.

- Original Message - 
From: Hiroshi Inoue in...@tpf.co.jp




Tom Lane wrote:

Hiroshi Saito z-sa...@guitar.ocn.ne.jp writes:

I want to solve one problem before the release of 8.4.
However, since it also seems to be the new feature,
if not enough for 8.4, you may suggest that it is 8.5.


I'm not too clear on what this is really supposed to accomplish, but
we are hardly going to put code like that into every single file access
in Postgres, which is what seems to be the logical implication.
Shouldn't we just tell people to use a database encoding that matches
their system environment?


Unfortunately (as usual) under Japanese Windows there's no database
encoding that matches the system environment.
As for the file name in COPY command, there's little meaning to
convert it to the server encoding because the file name is irrelevant
to the database. Because Windows is Unicode(UTF-16) based, it seems
natural to convert the file name to wide characters once.


Yes, If server encoding can be chosen by windows, the facilities 
in good working order. It was not possible though it was regrettable. 


Regards,
Hiroshi Saito


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] plpgsql debugger (pldbg) absent from 8.4?

2009-04-08 Thread Dave Page
On Wed, Apr 8, 2009 at 2:48 PM, Kevin Field kevinjamesfi...@gmail.com wrote:
 I'll see if I can get an updated build pushed out sometime today.

 I finally got around to trying this out using the March 24th build,
 and it has the same issue...

I just installed it here on a clean VM and I see the docs, the SQL
script and all the right libraries. What are you missing?

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Closing some 8.4 open items

2009-04-08 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 On Wed, Apr 8, 2009 at 1:17 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 And please note that we think bitmap scans are the larger part of
 the win anyway.  What's left undone there is some marginal mopup.

 Can you elaborate on this?  I'm fuzzy on why index scans can't benefit
 from this as much as bitmap index scans.

The main point is that the planner will prefer a bitmap scan for any
query that's estimated to return more than quite a small number of rows.
(In my experience the cutover point is in the single digits.)  So
there's just not that much room to win for plain indexscans.  Their
principal application is really for fetching single rows, a case where
prefetch is entirely useless because you have nothing to overlap.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Array types

2009-04-08 Thread John Lister
Following this up, is there any docs on the binary wire format for arrays?

Thanks
  - Original Message - 
  From: John Lister 
  To: pgsql-hackers@postgresql.org 
  Sent: Tuesday, April 07, 2009 7:54 PM
  Subject: [HACKERS] Array types


  Hi, using v8.3.5 and a number of client libraries (java, python, pgadmin) and 
playing about with arrays.

  They all return arrays as text, is it possible to configure postgresql to 
return an array in native form (does postgresql support such a thing)? This is 
using both the simple and extended query forms - i couldn't see a way to say 
what return type i wanted in the protocol docs...

  This would seem much better in terms of performance, both size and 
speed(conversion).

  Thanks

  --

  Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/


Re: [HACKERS] psql \d commands and information_schema

2009-04-08 Thread Bruce Momjian
Martin Pihlak wrote:
 Tom Lane wrote:
  Well, if they're all in your search_path then plain old \df will do
  fine.  If they're not in your search path then I think it gets pretty
  questionable whether they're user defined in a real sense.  It seems
  more likely that you've got a pile of modules loaded, and which of those
  modules is user defined for your immediate purposes is something that
  psql can't hope to intuit.
  
 
 I my environment schemas are used for namespace separation, so it doesn't
 make much sense to use search_path to pull everything back into a single
 namespace. Might as well use public for everything then. And these are not
 really modules, just ordinary user objects in separate namespaces.
 
 The main benefit of the U switch is that it enables to get a quick overview
 of whats deployed to the database. At the moment this is not possible as the
 *.* listings are polluted with system objects.
 
 PS. The original \dfU suggestion was made by you in
 http://archives.postgresql.org/message-id/28027.1206976...@sss.pgh.pa.us.
 And seems, it didn't receive too much negative feedback then.

We already had a huge discussion over 'S' and I think we did as good as
we can.  I think we risk overcomplicating the API by adding U, but we
can revisit this in 8.5 once we get more feedback from users.

But a larger issue is that if we try to make everyone happy with the
psql API, the API will be unusablely complex.  We have just not seen
enough demand for U yet.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Array types

2009-04-08 Thread Andrew Chernow

John Lister wrote:

Following this up, is there any docs on the binary wire format for arrays?
 


None that I know of.

Check out the backend source: (array_recv() and array_send() functions)
http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/arrayfuncs.c?rev=1.154

Or, look at libpqtypes array.c:
http://libpqtypes.esilo.com/browse_source.html?file=array.c

--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] plpgsql debugger (pldbg) absent from 8.4?

2009-04-08 Thread Kevin Field
On Apr 8, 10:32 am, dp...@pgadmin.org (Dave Page) wrote:
 On Wed, Apr 8, 2009 at 2:48 PM, Kevin Field kevinjamesfi...@gmail.com wrote:
  I'll see if I can get an updated build pushed out sometime today.

  I finally got around to trying this out using the March 24th build,
  and it has the same issue...

 I just installed it here on a clean VM and I see the docs, the SQL
 script and all the right libraries. What are you missing?

Well, the 2 DLLs are there, and actually, the SQL file is too, so I
ran it, and it complained about types already existing, so I took out
each of those and retried until all that were left were the CREATE OR
REPLACE FUNCTION lines, and those ran successfully.  But then I got
the same error again when actually trying to do Set Breakpoint in
pgAdmin.  What else would I check for?

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Array types

2009-04-08 Thread Andrew Chernow

Andrew Chernow wrote:

John Lister wrote:
Following this up, is there any docs on the binary wire format for 
arrays?
 


None that I know of.

Check out the backend source: (array_recv() and array_send() functions)
http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/arrayfuncs.c?rev=1.154 



Or, look at libpqtypes array.c:
http://libpqtypes.esilo.com/browse_source.html?file=array.c



Forgot to mention, this is not as simple as understanding the array 
format.  You have to understand the wire format for all types that can 
be array elements.  The array wire format serializes its array elements 
as [elem_len][elem_data].  elem_data is the wire format of the array 
element type, like an int, timestamp, polygon, bytea, etc...  So once 
you unravel the array container format, you still have to demarshal the 
type data.


--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Array types

2009-04-08 Thread Merlin Moncure
On Wed, Apr 8, 2009 at 10:48 AM, John Lister
john.lister...@kickstone.com wrote:
 Following this up, is there any docs on the binary wire format for arrays?

 Thanks


Does java wrap libpq? If so, your best bet is probably going to be to
go the libpqtypes route.  If you want help doing that, you are more
than welcome to ask (probably should move this thread to the
libqptypes list).  If not, you are headed for a 'much bigger than it
looks on the surface' challenge...there are a lot of types...trust me
on this one.  If you want help with libpqtypes you can ask on our list
on pgfoundry.

merlin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Array types

2009-04-08 Thread John Lister
Cheers for the pointers. Am i right in thinking that if i get an array of 
arrays, the nested arrays are sent in wire format as well - it seems to be 
from the docs.


Secondly, comments are a bit scarse in the code, but am i also right in 
thinking that an array indexing can start at an arbitrary value? This seems 
to be what the lbound value is for... or is this a addition to deal with 
nulls eg, {null, null, null, 4} would have a lbound of 3 (or both)


Thanks

- Original Message - 
From: Andrew Chernow a...@esilo.com

To: John Lister john.lister...@kickstone.com
Cc: pgsql-hackers@postgresql.org
Sent: Wednesday, April 08, 2009 4:07 PM
Subject: Re: [HACKERS] Array types



Andrew Chernow wrote:

John Lister wrote:
Following this up, is there any docs on the binary wire format for 
arrays?




None that I know of.

Check out the backend source: (array_recv() and array_send() functions)
http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/arrayfuncs.c?rev=1.154 
Or, look at libpqtypes array.c:

http://libpqtypes.esilo.com/browse_source.html?file=array.c



Forgot to mention, this is not as simple as understanding the array 
format.  You have to understand the wire format for all types that can be 
array elements.  The array wire format serializes its array elements as 
[elem_len][elem_data].  elem_data is the wire format of the array element 
type, like an int, timestamp, polygon, bytea, etc...  So once you unravel 
the array container format, you still have to demarshal the type data.


--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/




--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Array types

2009-04-08 Thread John Lister
No unfortunately not, it is a JDBC type 4 java which is entirely written in 
java. I've patched (as pointed out in another list) the base version to 
handle binary data (still a couple of issues that seem unfinished) which has 
given me clues, but the patch only supports simple types. I'm looking to 
create translator for arrays now..


I was hoping to use the java type handling for the internals of the array 
and throw an error on any unknown ones... I only use ints and floats in my 
arrays, so may leave it at that if it works and look at doing it properly 
later...




On Wed, Apr 8, 2009 at 10:48 AM, John Lister
john.lister...@kickstone.com wrote:
Following this up, is there any docs on the binary wire format for 
arrays?


Thanks



Does java wrap libpq? If so, your best bet is probably going to be to
go the libpqtypes route.  If you want help doing that, you are more
than welcome to ask (probably should move this thread to the
libqptypes list).  If not, you are headed for a 'much bigger than it
looks on the surface' challenge...there are a lot of types...trust me
on this one.  If you want help with libpqtypes you can ask on our list
on pgfoundry.

merlin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers




--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Array types

2009-04-08 Thread Andrew Dunstan



Merlin Moncure wrote:

On Wed, Apr 8, 2009 at 10:48 AM, John Lister
john.lister...@kickstone.com wrote:
  

Following this up, is there any docs on the binary wire format for arrays?

Thanks




Does java wrap libpq? 
  


No. The JDBC driver is a Type 4 pure java driver. It implements the wire 
protocol.


That's is what makes it portable.

Cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] psql \d commands and information_schema

2009-04-08 Thread Greg Stark
On Wed, Apr 8, 2009 at 3:49 PM, Bruce Momjian br...@momjian.us wrote:
 We already had a huge discussion over 'S' and I think we did as good as
 we can.  I think we risk overcomplicating the API by adding U, but we
 can revisit this in 8.5 once we get more feedback from users.

I think we'll need to take stock before 8.4 actually. Tom's pointed
out a whole pile of problems with the current approach and I'm
becoming convinced he's right. I know I was one of the proponents of
the change but I didn't realize how bad the problems were.

As I understand his proposal is that \df with no pattern could list
all user functions but \df pattern should always follow the
search_path and show the same functions that would actually be called.

One possibility for reducing clutter would be moving a whole slew of
the system functions which are never intended for users to call
explicitly to a different schema which isn't implicitly added to
search_path. That would at least get all the RI functions, bt procs,
maybe even the operator functions out of the way.

-- 
greg

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] plpgsql debugger (pldbg) absent from 8.4?

2009-04-08 Thread Dave Page
On Wed, Apr 8, 2009 at 3:47 PM, Kevin Field kevinjamesfi...@gmail.com wrote:
 On Apr 8, 10:32 am, dp...@pgadmin.org (Dave Page) wrote:
 On Wed, Apr 8, 2009 at 2:48 PM, Kevin Field kevinjamesfi...@gmail.com 
 wrote:
  I'll see if I can get an updated build pushed out sometime today.

  I finally got around to trying this out using the March 24th build,
  and it has the same issue...

 I just installed it here on a clean VM and I see the docs, the SQL
 script and all the right libraries. What are you missing?

 Well, the 2 DLLs are there, and actually, the SQL file is too, so I
 ran it, and it complained about types already existing, so I took out
 each of those and retried until all that were left were the CREATE OR
 REPLACE FUNCTION lines, and those ran successfully.  But then I got
 the same error again when actually trying to do Set Breakpoint in
 pgAdmin.  What else would I check for?

Did you add

shared_preload_libraries = '$libdir/plugins/plugin_debugger.dll'

to postgresql.conf and restart the server per the README?


-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Array types

2009-04-08 Thread Tom Lane
John Lister john.lister...@kickstone.com writes:
 Cheers for the pointers. Am i right in thinking that if i get an array of 
 arrays, the nested arrays are sent in wire format as well - it seems to be 
 from the docs.

Postgres doesn't have arrays of arrays.  There are multi-dimensional
arrays, which aren't conceptually the same thing.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Array types

2009-04-08 Thread Greg Stark
On Wed, Apr 8, 2009 at 4:11 PM, John Lister
john.lister...@kickstone.com wrote:
 Cheers for the pointers. Am i right in thinking that if i get an array of
 arrays, the nested arrays are sent in wire format as well - it seems to be
 from the docs.

No, you can't easily get an array of arrays in Postgres. You can get
multi-dimensional arrays but that's one big array with multiple
dimensions.  The text output form does look like an array of arrays
but they don't behave like you might think they would:

postgres=# select array[array[1,2,3,4],array[5,6,7,8]];
 array
---
 {{1,2,3,4},{5,6,7,8}}
(1 row)


postgres=# select '{{1,2,3,4},{5,6,7,8}}'::int[];
 int4
---
 {{1,2,3,4},{5,6,7,8}}
(1 row)

postgres=# select ('{{1,2,3,4},{5,6,7,8}}'::int[])[1];
 int4
--

(1 row)

postgres=# select ('{{1,2,3,4},{5,6,7,8}}'::int[])[1][1];
 int4
--
1
(1 row)



 Secondly, comments are a bit scarse in the code, but am i also right in
 thinking that an array indexing can start at an arbitrary value? This seems
 to be what the lbound value is for... or is this a addition to deal with
 nulls eg, {null, null, null, 4} would have a lbound of 3 (or both)

No, nulls are handled using a bitmap inside the array data structure.

Array bounds don't have to start at 1, they can start below 1 or above 1.

postgres=# select ('[-2:-1][5:8]={{1,2,3,4},{5,6,7,8}}'::int[])[-2][5];
 int4
--
1
(1 row)



-- 
greg

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] psql \d commands and information_schema

2009-04-08 Thread Tom Lane
Greg Stark st...@enterprisedb.com writes:
 On Wed, Apr 8, 2009 at 3:49 PM, Bruce Momjian br...@momjian.us wrote:
 We already had a huge discussion over 'S' and I think we did as good as
 we can.  I think we risk overcomplicating the API by adding U, but we
 can revisit this in 8.5 once we get more feedback from users.

 I think we'll need to take stock before 8.4 actually. Tom's pointed
 out a whole pile of problems with the current approach and I'm
 becoming convinced he's right. I know I was one of the proponents of
 the change but I didn't realize how bad the problems were.

 As I understand his proposal is that \df with no pattern could list
 all user functions but \df pattern should always follow the
 search_path and show the same functions that would actually be called.

Uh, that change got applied last week ...
http://archives.postgresql.org/pgsql-committers/2009-04/msg00014.php

 One possibility for reducing clutter would be moving a whole slew of
 the system functions which are never intended for users to call
 explicitly to a different schema which isn't implicitly added to
 search_path. That would at least get all the RI functions, bt procs,
 maybe even the operator functions out of the way.

Perhaps, but is it really important?  I haven't noticed that those
things were cluttering my \df searches anyway.

BTW, I hesitate to mention this and perhaps upset a fragile consensus,
but should we remove the special-case code in \df that tries to hide I/O
functions by excluding functions that take or return cstring?  I think
that its value has largely disappeared given the new overall behavior.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Array types

2009-04-08 Thread John Lister




On Wed, Apr 8, 2009 at 4:11 PM, John Lister
john.lister...@kickstone.com wrote:

Cheers for the pointers. Am i right in thinking that if i get an array of
arrays, the nested arrays are sent in wire format as well - it seems to 
be

from the docs.


No, you can't easily get an array of arrays in Postgres. You can get
multi-dimensional arrays but that's one big array with multiple
dimensions.  The text output form does look like an array of arrays
but they don't behave like you might think they would:


Cheers, it wasn't clear if you have an array of arrays of which the nested 
ones were of a different type. but it looks like all the values have to be 
the same type,

eg

select (array[array[1,2,3,4],array['test']])

fails..

this makes life simpler :)


Secondly, comments are a bit scarse in the code, but am i also right in
thinking that an array indexing can start at an arbitrary value? This 
seems

to be what the lbound value is for... or is this a addition to deal with
nulls eg, {null, null, null, 4} would have a lbound of 3 (or both)


No, nulls are handled using a bitmap inside the array data structure.

Array bounds don't have to start at 1, they can start below 1 or above 1.

postgres=# select ('[-2:-1][5:8]={{1,2,3,4},{5,6,7,8}}'::int[])[-2][5];
int4
--


Somehow missed the bounds in the docs. Cheers that has cleared that up...

JOHN 



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] psql \d commands and information_schema

2009-04-08 Thread Alvaro Herrera
Tom Lane escribió:

 BTW, I hesitate to mention this and perhaps upset a fragile consensus,
 but should we remove the special-case code in \df that tries to hide I/O
 functions by excluding functions that take or return cstring?  I think
 that its value has largely disappeared given the new overall behavior.

+1

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Array types

2009-04-08 Thread Merlin Moncure
On Wed, Apr 8, 2009 at 11:35 AM, Greg Stark st...@enterprisedb.com wrote:
 On Wed, Apr 8, 2009 at 4:11 PM, John Lister
 john.lister...@kickstone.com wrote:
 Cheers for the pointers. Am i right in thinking that if i get an array of
 arrays, the nested arrays are sent in wire format as well - it seems to be
 from the docs.

 No, you can't easily get an array of arrays in Postgres. You can get
 multi-dimensional arrays but that's one big array with multiple
 dimensions.  The text output form does look like an array of arrays
 but they don't behave like you might think they would:

one note about that: you can have array of composites with arrays in
them, so you can get arbitrary levels of nesting.

merlin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Closing some 8.4 open items

2009-04-08 Thread Robert Haas
On Wed, Apr 8, 2009 at 10:33 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com writes:
 On Wed, Apr 8, 2009 at 1:17 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 And please note that we think bitmap scans are the larger part of
 the win anyway.  What's left undone there is some marginal mopup.

 Can you elaborate on this?  I'm fuzzy on why index scans can't benefit
 from this as much as bitmap index scans.

 The main point is that the planner will prefer a bitmap scan for any
 query that's estimated to return more than quite a small number of rows.
 (In my experience the cutover point is in the single digits.)  So
 there's just not that much room to win for plain indexscans.  Their
 principal application is really for fetching single rows, a case where
 prefetch is entirely useless because you have nothing to overlap.

That makes sense, but what about the nestloop-over-inner-indexscan case?

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] plpgsql debugger (pldbg) absent from 8.4?

2009-04-08 Thread Kevin Field
On Apr 8, 11:26 am, dp...@pgadmin.org (Dave Page) wrote:
 Did you add

 shared_preload_libraries = '$libdir/plugins/plugin_debugger.dll'

 to postgresql.conf and restart the server per the README?

Oh my goodness.  No.  Thank you so much.  It works fine now.  I'll
have to add that to my upgrading procedure...

Kev

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Closing some 8.4 open items

2009-04-08 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 On Wed, Apr 8, 2009 at 10:33 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 The main point is that the planner will prefer a bitmap scan for any
 query that's estimated to return more than quite a small number of rows.

 That makes sense, but what about the nestloop-over-inner-indexscan case?

What about it?  The provided patch made no attempt to optimize that
case.

Doing so might well be interesting, but it's not getting done for 8.4.
I think it would be quite an invasive patch --- it's hard to see how to
do it without explicit support at the nestloop join level, so that you
could pipeline the processing of multiple key values coming from the outer
side of the join.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] psql \d commands and information_schema

2009-04-08 Thread Bruce Momjian
Alvaro Herrera wrote:
 Tom Lane escribi?:
 
  BTW, I hesitate to mention this and perhaps upset a fragile consensus,
  but should we remove the special-case code in \df that tries to hide I/O
  functions by excluding functions that take or return cstring?  I think
  that its value has largely disappeared given the new overall behavior.
 
 +1

Agreed.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Solution of the file name problem of copy on windows.

2009-04-08 Thread Hiroshi Saito

Hi Itagaki-san.

Um,  I had a focus in help the problem which is not avoided. 
I am not sensitive to a problem being avoided depending on usage. 
However, I will wish to work spontaneously, when it is help much. 


Regards,
Hiroshi Saito

- Original Message - 
From: Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp




Hi,

Hiroshi Saito z-sa...@guitar.ocn.ne.jp wrote:


At this time, a copy file name is UTF-8.  It was troubled by handling.:-(
Then,  I make this proposal patch.


I think the problem is not only in Windows but also in all platforms
where the database encoding doesn't match their OS's encoding.

Instead of Windows specific codes, how about adding GetPlatformEncoding()
and convert all of *absolute* paths? It would be performed at the lowest
API layer; i.e, BasicOpenFile(). Standard database file accesses with
RelFileNode are not affected because is uses *relative* paths.

There are some issues:
   * Is it possible to determine the platform encoding?
   * The above cannot handle non-ascii path under $PGDATA.
 Is it acceptable?
   * In Windows, the native encoding is UTF-16, but we will use SJIS
 if we take on the above method. Is the limitation acceptable?

Comments welcome.

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



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers




--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] plpgsql debugger (pldbg) absent from 8.4?

2009-04-08 Thread Dave Page
On Wed, Apr 8, 2009 at 4:35 PM, Kevin Field kevinjamesfi...@gmail.com wrote:
 On Apr 8, 11:26 am, dp...@pgadmin.org (Dave Page) wrote:
 Did you add

 shared_preload_libraries = '$libdir/plugins/plugin_debugger.dll'

 to postgresql.conf and restart the server per the README?

 Oh my goodness.  No.  Thank you so much.  It works fine now.  I'll
 have to add that to my upgrading procedure...

Excellent :-). FYI, I'm currently working on improving pgAdmin's
ability to detect if the plugin is properly installed.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Closing some 8.4 open items

2009-04-08 Thread Robert Haas
On Wed, Apr 8, 2009 at 11:59 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com writes:
 On Wed, Apr 8, 2009 at 10:33 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 The main point is that the planner will prefer a bitmap scan for any
 query that's estimated to return more than quite a small number of rows.

 That makes sense, but what about the nestloop-over-inner-indexscan case?

 What about it?  The provided patch made no attempt to optimize that
 case.

 Doing so might well be interesting, but it's not getting done for 8.4.
 I think it would be quite an invasive patch --- it's hard to see how to
 do it without explicit support at the nestloop join level, so that you
 could pipeline the processing of multiple key values coming from the outer
 side of the join.

OK, I think I'm now understanding your line of thinking.  Thanks for
the explanation.

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Closing some 8.4 open items

2009-04-08 Thread Josh Berkus

On 4/7/09 10:17 PM, Tom Lane wrote:

Robert Haasrobertmh...@gmail.com  writes:

On Tue, Apr 7, 2009 at 10:42 PM, Josh Berkusj...@agliodbs.com  wrote:

So has fadvise been completely dropped from 8.4, or only partially?



Bitmap scans will support it, but index scans will not.


What about seq scans?


--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Closing some 8.4 open items

2009-04-08 Thread Tom Lane
Josh Berkus j...@agliodbs.com writes:
 What about seq scans?

If the kernel can't read-ahead a seqscan by itself, it's unlikely to
be smart enough to be helped by posix_fadvise ... or at least so I
would think.  Do you have reason to think differently?

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A renewed plea for inclusion of zone.tab

2009-04-08 Thread Chris Browne
and...@tao11.riddles.org.uk (Andrew Gierth) writes:
 The usual conversation goes something like this (generally following
 on from some discussion of how to do timezone conversions):

  Q: how do I get the list of available zone names?

  A: see pg_timezone_names

  Q: but there's 1650/1400/560/452 [delete as applicable] entries in
 there!  how do I know which one to use for any given user? Can I
 work it out from the user's location?

  A: Some locations have timezones that vary by county level, so it's
 hard to automate unless you have a street address and detailed
 maps/database of Indiana and other awkward places. Best bet is to
 ask the user themselves, once you know what country they're in.

  Q: How do you know what zones are in what countries?

  A: that info is in zone.tab, which you can find either from your OS's
 timezone directory or from the postgres source for your postgres
 version. Put that data in a table or something and use it to prompt
 the user; it has text to help disambiguate the obscure cases.

  Q: ... wtf? why is that not installed anywhere?

I can confirm having recently hit something rather like this...

We wanted to indicate which timezone people were in, but in the
absence of having something like zone.tab conveniently available,
people were starting to talk about designing their own ad-hoc, buggy
version of something looking like about half of zone.tab.

  Tom Any such application is far more likely to be looking at the
  Tom system tzdata files.

 Only if it's using the system TZ functions to do conversions rather
 than doing them inside pg, which certainly isn't how _I'd_ recommend
 an app writer do it.

Right.  Having zone.tab data available in the DB would be *way* more
convenient, especially in that it is not at all obvious that it's
always available on systems, let alone in a consistent place.

This isn't an argument for it must be considered standard,
yesterday.  But it's plenty valuable to look to having a way to parse
zone.tab and turn it into a table or two.
-- 
let name=cbbrowne and tld=cbbrowne.com in name ^ @ ^ tld;;
http://linuxdatabases.info/info/linuxxian.html
The only thing  better than TV with the  sound off is  Radio with the
sound off. -- Dave Moon

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Closing some 8.4 open items

2009-04-08 Thread Josh Berkus

On 4/8/09 9:44 AM, Tom Lane wrote:

Josh Berkusj...@agliodbs.com  writes:

What about seq scans?


If the kernel can't read-ahead a seqscan by itself, it's unlikely to
be smart enough to be helped by posix_fadvise ... or at least so I
would think.  Do you have reason to think differently?


Well, Solaris 10 + UFS should be helped by fadvise -- in theory at 
least, it would eliminate the need to modify your mount points for 
better readahead when setting up a PG-Solaris server.  Solaris-UFS quite 
lazy about readahead.  Zdenek, Jignesh?


You're probably correct about Linux and FreeBSD.  I don't know if OSX + 
HFS supports fadvise.  If so, it could only help; readahead on HFS right 
now is nonexistant.


Presumably fadvise is useless on Windows.  Anyone know?


--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Closing some 8.4 open items

2009-04-08 Thread Dave Page
On Wednesday, April 8, 2009, Josh Berkus j...@agliodbs.com wrote:
 On 4/8/09 9:44 AM, Tom Lane wrote:

 Josh Berkusj...@agliodbs.com  writes:

 What about seq scans?


 If the kernel can't read-ahead a seqscan by itself, it's unlikely to
 be smart enough to be helped by posix_fadvise ... or at least so I
 would think.  Do you have reason to think differently?


 Well, Solaris 10 + UFS should be helped by fadvise -- in theory at least, it 
 would eliminate the need to modify your mount points for better readahead 
 when setting up a PG-Solaris server.  Solaris-UFS quite lazy about readahead. 
  Zdenek, Jignesh?

 You're probably correct about Linux and FreeBSD.  I don't know if OSX + HFS 
 supports fadvise.  If so, it could only help; readahead on HFS right now is 
 nonexistant.

 Presumably fadvise is useless on Windows.  Anyone know?

It is.



-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Closing some 8.4 open items

2009-04-08 Thread Heikki Linnakangas

Josh Berkus wrote:

On 4/8/09 9:44 AM, Tom Lane wrote:

Josh Berkusj...@agliodbs.com  writes:

What about seq scans?


If the kernel can't read-ahead a seqscan by itself, it's unlikely to
be smart enough to be helped by posix_fadvise ... or at least so I
would think.  Do you have reason to think differently?


Well, Solaris 10 + UFS should be helped by fadvise -- in theory at 
least, it would eliminate the need to modify your mount points for 
better readahead when setting up a PG-Solaris server.  Solaris-UFS quite 
lazy about readahead.  Zdenek, Jignesh?


You're probably correct about Linux and FreeBSD.  I don't know if OSX + 
HFS supports fadvise.  If so, it could only help; readahead on HFS right 
now is nonexistant.


Presumably fadvise is useless on Windows.  Anyone know?


It's important to distinguish what kind of fadvise we're talking about. 
The bitmap scan code issues hints about individual pages, using 
posix_fadvise(... POSIX_FADV_WILLNEED). For increasing the readahead of 
a sequential scan, you'd want to use POSIX_FADV_SEQUENTIAL. I believe 
the support for the latter is much more widespread than for the former.


xlog.c now also uses POSIX_FADV_WONTNEED to drop WAL pages from the OS 
cache after writing them.


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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Closing some 8.4 open items

2009-04-08 Thread Kevin Grittner
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: 
 xlog.c now also uses POSIX_FADV_WONTNEED to drop WAL pages from the
 OS cache after writing them.
 
Even when archiving is on?
 
-Kevin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Closing some 8.4 open items

2009-04-08 Thread Heikki Linnakangas

Dave Page wrote:

On Wednesday, April 8, 2009, Josh Berkus j...@agliodbs.com wrote:

Presumably fadvise is useless on Windows.  Anyone know?


It is.


cygwin supports POSIX_FADV_SEQUENTIAL (and POSIX_FADV_NORMAL to revert 
it), but not any of the other flags. It maps it to 
NtSetInformationFile() like this:



  if (advice == POSIX_FADV_SEQUENTIAL)
fmi.Mode |= FILE_SEQUENTIAL_ONLY;
  status = NtSetInformationFile (get_handle (), io, fmi, sizeof fmi,
 FileModeInformation);


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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Closing some 8.4 open items

2009-04-08 Thread Heikki Linnakangas

Kevin Grittner wrote:
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: 

xlog.c now also uses POSIX_FADV_WONTNEED to drop WAL pages from the
OS cache after writing them.
 
Even when archiving is on?


No, not in that case.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Closing some 8.4 open items

2009-04-08 Thread Magnus Hagander
Heikki Linnakangas wrote:
 Dave Page wrote:
 On Wednesday, April 8, 2009, Josh Berkus j...@agliodbs.com wrote:
 Presumably fadvise is useless on Windows.  Anyone know?

 It is.
 
 cygwin supports POSIX_FADV_SEQUENTIAL (and POSIX_FADV_NORMAL to revert
 it), but not any of the other flags. It maps it to
 NtSetInformationFile() like this:

We set this in our open() wrapper in the code today. That doesn't
support changing it after the fact, of course.

//Magnus


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Closing some 8.4 open items

2009-04-08 Thread Josh Berkus

Heikki,


It's important to distinguish what kind of fadvise we're talking about.
The bitmap scan code issues hints about individual pages, using
posix_fadvise(... POSIX_FADV_WILLNEED). For increasing the readahead of
a sequential scan, you'd want to use POSIX_FADV_SEQUENTIAL. I believe
the support for the latter is much more widespread than for the former.


OK, so this is potentially useful (pending testing) but it's a different 
feature.  We'll discuss it for 8.5.


The other thing I was going to ask you about is using posix_fadvise as 
an alternative to O_DIRECT for the xlog.  O_DIRECT is, AFAIK, 
linux-only, whereas there are direct write fadvise flags which work on 
multiple OSes.


--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Closing some 8.4 open items

2009-04-08 Thread Heikki Linnakangas

Josh Berkus wrote:
The other thing I was going to ask you about is using posix_fadvise as 
an alternative to O_DIRECT for the xlog.  O_DIRECT is, AFAIK, 
linux-only, whereas there are direct write fadvise flags which work on 
multiple OSes.


What flags are those? I don't see any posix_fadvise flags that would do 
anything like O_DIRECT.


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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Closing some 8.4 open items

2009-04-08 Thread Tom Lane
Magnus Hagander mag...@hagander.net writes:
 Heikki Linnakangas wrote:
 cygwin supports POSIX_FADV_SEQUENTIAL (and POSIX_FADV_NORMAL to revert
 it), but not any of the other flags. It maps it to
 NtSetInformationFile() like this:

 We set this in our open() wrapper in the code today.

Really?  Where?  I didn't find any of the mentioned symbols in a quick
grep.

I'm not sure how Windows interprets FILE_SEQUENTIAL_ONLY, but if that
really means that it assumes *only* sequential accesses will happen,
I'm not sure that we'd want to turn it on.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Closing some 8.4 open items

2009-04-08 Thread Magnus Hagander
Tom Lane wrote:
 Magnus Hagander mag...@hagander.net writes:
 Heikki Linnakangas wrote:
 cygwin supports POSIX_FADV_SEQUENTIAL (and POSIX_FADV_NORMAL to revert
 it), but not any of the other flags. It maps it to
 NtSetInformationFile() like this:
 
 We set this in our open() wrapper in the code today.
 
 Really?  Where?  I didn't find any of the mentioned symbols in a quick
 grep.

We pass FILE_FLAG_SEQUENTIAL_SCAN to the open call if O_SEQUENTIAL is
specified.


 I'm not sure how Windows interprets FILE_SEQUENTIAL_ONLY, but if that
 really means that it assumes *only* sequential accesses will happen,
 I'm not sure that we'd want to turn it on.

It's an access-optimization hint, that's all.

//Magnus

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Closing some 8.4 open items

2009-04-08 Thread Dave Page
On Wed, Apr 8, 2009 at 6:42 PM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
 Dave Page wrote:

 On Wednesday, April 8, 2009, Josh Berkus j...@agliodbs.com wrote:

 Presumably fadvise is useless on Windows.  Anyone know?

 It is.

 cygwin supports POSIX_FADV_SEQUENTIAL (and POSIX_FADV_NORMAL to revert it),
 but not any of the other flags. It maps it to NtSetInformationFile() like
 this:

      if (advice == POSIX_FADV_SEQUENTIAL)
        fmi.Mode |= FILE_SEQUENTIAL_ONLY;
      status = NtSetInformationFile (get_handle (), io, fmi, sizeof fmi,
                                     FileModeInformation);

Which is only useful with async IO as far as I'm aware.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Closing some 8.4 open items

2009-04-08 Thread Josh Berkus

Tom,


change cardinality() for multi-dim arrays?

Drop; there's no consensus that this should be changed


Andrew pinged me on this. While there's no consensus that it should be 
changed, there's no consensus it shouldn't, either.  And once we release 
it, we've set the way it operates in cement, so I'd like to get a 
consensus one way or the other.  I think if we *can't* get a consensus, 
it's better to omit the syntax from 8.4 then risk deploying syntax we'll 
want to change later.


For my part, I'd like to know what things other than arrays 
collection_expression in the standard applies to.  I think the most 
sensible course is to make cardinality(array[]) behave consistently with 
cardinality(other_stuff) when we get around to implementing it.


--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Closing some 8.4 open items

2009-04-08 Thread Greg Smith

On Wed, 8 Apr 2009, Heikki Linnakangas wrote:


Josh Berkus wrote:
The other thing I was going to ask you about is using posix_fadvise as an 
alternative to O_DIRECT for the xlog.  O_DIRECT is, AFAIK, linux-only, 
whereas there are direct write fadvise flags which work on multiple OSes.


What flags are those? I don't see any posix_fadvise flags that would do 
anything like O_DIRECT.


A good implementation of FADV_NOREUSE would work similarly to O_DIRECT, 
writing things out but not keeping them around the OS cache.  (suggested 
long ago even: 
http://archives.postgresql.org//pgsql-hackers/2003-10/msg01492.php )


I know there's a problem with O_DIRECT not working on Solaris; see the 
following:


http://blogs.sun.com/jkshah/entry/postgresql_wal_sync_method_and
http://blogs.sun.com/roch/entry/zfs_and_directio
http://docs.sun.com/app/docs/doc/816-5168/directio-3c

I'm not sure whether using an fadvise call like FADV_NOREUSE will work any 
better though; it may be the case that only that directio call is 
sufficient on Solaris.  A Solaris-specific code path that calls directio 
is what MySQL does here: 
http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_flush_method


I wanted to include such a patch in 8.4 but my one Solaris project got 
sidelined.


Saying that O_DIRECT is linux-only doesn't seem right though.  The same 
thread referenced above started by announcing O_DIRECT support on FreeBSD: 
http://archives.postgresql.org//pgsql-hackers/2003-10/msg01482.php and the 
above MySQL documentation supports that it works on FreeBSD, too.  I've 
seen claims that it works fine on Mac OS X, too, although MySQL may not 
support that:

http://labs.cybozu.co.jp/blog/kazuhoatwork/2009/02/using_o_direct_on_mac_os_x.php

--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Closing some 8.4 open items

2009-04-08 Thread Greg Smith

On Wed, 8 Apr 2009, Tom Lane wrote:


If the kernel can't read-ahead a seqscan by itself, it's unlikely to
be smart enough to be helped by posix_fadvise ... or at least so I
would think.


There's some interesting comments on this subject (and about what fadvise 
DONTNEED does) in the RRD research paper about managing their buffer 
cache:


http://www.usenix.org/event/lisa07/tech/full_papers/plonka/plonka_html/index.html

They suggest the Linux read-ahead is pretty aggressive by default, which 
might explain why I wasn't able to replicate any speed-up with the 
seqeuential scan patch on my system.  (The original submission showed a 
significant speedup on Linux, but was from what sounded like a somewhat 
broken kernel--known buggy controller driver I seem to recall)


I suspect we may need to find a platform where the default OS readahead is 
a slacker, *and* that pays attention to POSIX_FADV_SEQUENTIAL, in order to 
show any improvement from that patch.


--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Closing some 8.4 open items

2009-04-08 Thread Tom Lane
Josh Berkus j...@agliodbs.com writes:
 Tom,
 change cardinality() for multi-dim arrays?
 
 Drop; there's no consensus that this should be changed

 Andrew pinged me on this. While there's no consensus that it should be 
 changed, there's no consensus it shouldn't, either.  And once we release 
 it, we've set the way it operates in cement, so I'd like to get a 
 consensus one way or the other.

Yeah.  I would like to change it; Peter evidently thinks it's good
as-is.  Where do we go from here?

 For my part, I'd like to know what things other than arrays 
 collection_expression in the standard applies to.  I think the most 
 sensible course is to make cardinality(array[]) behave consistently with 
 cardinality(other_stuff) when we get around to implementing it.

There is no equivalent of multi-dimensional arrays in other kinds of
collections, so I'm not seeing that there is any good guide there.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Closing some 8.4 open items

2009-04-08 Thread Josh Berkus

Tom,


There is no equivalent of multi-dimensional arrays in other kinds of
collections, so I'm not seeing that there is any good guide there.


What else *does* SQL:2008 consider a collection?


--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] Clean shutdown and warm standby

2009-04-08 Thread Guillaume Smet
Hi,

Following the discussion here
http://archives.postgresql.org/message-id/49d9e986.8010...@pse-consulting.de
, I wrote a small patch which rotates the last XLog file on shutdown
so that the archive command is also executed for this file and we are
sure we have all the useful XLog files when we perform a clean
shutdown of master + switch to the failover server. This rotation is
done only if the archive mode is active and an archive command is set.

It's currently really difficult to switch easily (ie without copying
the file manually) to the failover server without any data loss.

Is there any problem I've missed? Could we consider the inclusion of
such a patch or something similar?

Comments?

Regards,

-- 
Guillaume
Index: src/backend/access/transam/xlog.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.335
diff -c -r1.335 xlog.c
*** src/backend/access/transam/xlog.c	7 Apr 2009 00:31:26 -	1.335
--- src/backend/access/transam/xlog.c	8 Apr 2009 13:18:40 -
***
*** 5950,5956 
--- 5950,5965 
  	if (RecoveryInProgress())
  		CreateRestartPoint(CHECKPOINT_IS_SHUTDOWN | CHECKPOINT_IMMEDIATE);
  	else
+ 	{
  		CreateCheckPoint(CHECKPOINT_IS_SHUTDOWN | CHECKPOINT_IMMEDIATE);
+ 		/*
+ 		 * if archiving is active and an archive command is set, we need to
+ 		 * rotate the last XLog file containing useful information so that
+ 		 * the archive command is also executed for it
+ 		 */
+ 		if(XLogArchivingActive()  XLogArchiveCommandSet())
+ 			RequestXLogSwitch();
+ 	}
  	ShutdownCLOG();
  	ShutdownSUBTRANS();
  	ShutdownMultiXact();

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] problem with variable length user defined type

2009-04-08 Thread kverdecia
Hi,

Some one knows where can I download a full example of a variable length
user defined type? I'm trying to define one but I have problems with de
output function.


Thanks

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] problem with variable length user defined type

2009-04-08 Thread Merlin Moncure
On Wed, Apr 8, 2009 at 3:49 PM, kverdecia kverde...@uci.cu wrote:
 Hi,

 Some one knows where can I download a full example of a variable length
 user defined type? I'm trying to define one but I have problems with de
 output function.

have you looked at contrib? for example hstore?

merlin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] problem with variable length user defined type

2009-04-08 Thread Tom Lane
kverdecia kverde...@uci.cu writes:
 Some one knows where can I download a full example of a variable length
 user defined type?

There are several in the contrib modules.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Error message and infinite date and timestamp conversion in XML

2009-04-08 Thread Peter Eisentraut
On Friday 27 March 2009 20:59:23 Tom Lane wrote:
 Done, but I noticed while testing that it's not real consistent:

 regression=# select xmlelement(name foo, 'infinity'::timestamp);
 ERROR:  timestamp out of range
 DETAIL:  XML does not support infinite timestamp values.
 regression=# select xmlelement(name foo,
 xmlattributes('infinity'::timestamp as bar)); xmlelement
 ---
  foo bar=infinity/
 (1 row)

 Should we consider doing something about that, or is it okay as-is?

Fixed.  It was a bug.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] GUC inconsistency in 8.4?

2009-04-08 Thread Bruce Momjian
How that debug_print_parse outputs as LOG instead of DEBUG in 8.4,
should it be log_print_parse?

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] NaN support in NUMERIC data type

2009-04-08 Thread Tom Lane
Sam Mason s...@samason.me.uk writes:
 On Tue, Apr 07, 2009 at 12:51:21PM -0400, Tom Lane wrote:
 IIRC, the explicit support for leading/trailing spaces is something that
 we added in float8in long after numeric_in was written, and I think just
 nobody thought about numeric at the time.  But it's clearly inconsistent
 to allow spaces around a regular value but not a NaN.

 The included patch was generated against 8.3.5 (because that's what I
 had lying around when I started playing) but applies with a little fuzz
 to the latest snapshot and does the right thing for me in both versions.

Hmm, did it do the right thing for NaN with a typmod?  I doubt
apply_typmod is safe for a NaN.  Anyway, I revised this a bit and
applied to HEAD.  I'm disinclined to back-patch; it's not really a bug
but a definition change, and we get flack when we put that sort of
change into stable branches ...

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] psql \d commands and information_schema

2009-04-08 Thread Tom Lane
Bruce Momjian br...@momjian.us writes:
 Alvaro Herrera wrote:
 Tom Lane escribi?:
 BTW, I hesitate to mention this and perhaps upset a fragile consensus,
 but should we remove the special-case code in \df that tries to hide I/O
 functions by excluding functions that take or return cstring?  I think
 that its value has largely disappeared given the new overall behavior.
 
 +1

 Agreed.

Done.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] GUC inconsistency in 8.4?

2009-04-08 Thread Tom Lane
Bruce Momjian br...@momjian.us writes:
 How that debug_print_parse outputs as LOG instead of DEBUG in 8.4,
 should it be log_print_parse?

No, it's still a debugging tool.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Closing some 8.4 open items

2009-04-08 Thread Peter Eisentraut
On Wednesday 08 April 2009 21:56:38 Tom Lane wrote:
  For my part, I'd like to know what things other than arrays
  collection_expression in the standard applies to.  I think the most
  sensible course is to make cardinality(array[]) behave consistently with
  cardinality(other_stuff) when we get around to implementing it.

 There is no equivalent of multi-dimensional arrays in other kinds of
 collections, so I'm not seeing that there is any good guide there.

Here is my thinking, and considering that that would basically involve a 
forward-looking design decision right now, I would support dropping the 
cardinality() function from 8.4 (if people agree that this is in fact the 
design decision to make).

Collection types in SQL are arrays and multisets.  Multisets are essentially 
arrays without ordering.  Many people already use arrays like that, and I 
would find it interesting to support real multisets in the future.

Currently, we don't support collections of collections, specifically arrays of 
arrays.  We only have multidimensional arrays.  Multidimensional arrays in 
PostgreSQL and arrays of arrays in SQL are actually pretty close in the 
interface they present, except that the subscript order is reversed.  If you 
ignore that, the current cardinality() function gives you pretty much 
conforming behavior on nested arrays, at least for the first level.

The question now is, if we want to move toward supporting multisets and 
arbitrary nested collections in the future, do we

1. Transform our view of a multidimensional array into nested arrays, and then 
extend that to allow multisets.  (The implementation could stay quite the 
same; just mark some dimensions as this is a multiset.)  And then perhaps 
address the subscript ordering issue by some hitherto unknown plan.

- or -

2. Extend the system so you can have nested multidimensional arrays (e.g., a 
4x4 array containing 3x3 arrays), and then extend that to also allow nesting 
with a separate multiset structure (possibly also multidimensional).  I think 
this would probably make a mess out of the subscripting.

- or -

3. SQL DIE DIE DIE!!!

If you think (1) then the current implementation of cardinality() is correct, 
if you think (2) then Tom's proposed change is correct, if you think (3) the 
function should be removed.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Closing some 8.4 open items

2009-04-08 Thread Tom Lane
Peter Eisentraut pete...@gmx.net writes:
 Here is my thinking, and considering that that would basically involve a 
 forward-looking design decision right now, I would support dropping the 
 cardinality() function from 8.4 (if people agree that this is in fact the 
 design decision to make).

At this point I'd support that too.  It doesn't seem that getting
cardinality() into 8.4 is important enough to risk making a decision
that we'd regret later.  And I think it's not hard to make the case
that we might regret either of the other choices later, depending on
where we go with arrays.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] default parameters for built-in functions

2009-04-08 Thread Peter Eisentraut
On Tuesday 07 April 2009 03:36:43 Tom Lane wrote:
 You won't get far with doing it to pg_proc: internal functions *have to*
 have entries in there, else the fmgrtab infrastructure for them doesn't
 get created.  (Yeah, I suppose there are other ways to drive that, but
 the fact remains that they need more than just a SQL command.)

What is the purpose of fmgrtab anyway?  Is it speed (how much) or some 
bootstrapping issue (in which case converting most as proposed by Greg would 
still work)?

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] default parameters for built-in functions

2009-04-08 Thread Tom Lane
Peter Eisentraut pete...@gmx.net writes:
 What is the purpose of fmgrtab anyway?

It's so we can find the addresses of internal functions to call them.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A renewed plea for inclusion of zone.tab

2009-04-08 Thread Josh Berkus

Tom,


 Like what?  I do not actually believe that anyone needs an
 interactive geographical timezone selector based on
 pg_timezone_names.


Actually, considering that PostgreSQL is the leading open source GIS 
database, I expect that a *lot* of people want this.  Or, at least, 
enough data to ad-hoc something themselves.


--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] 8.4 open items list

2009-04-08 Thread Peter Eisentraut
On Thursday 02 April 2009 21:38:06 Tom Lane wrote:
 Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes:
  Now, what about the idea of providing a shorthand LOCALE='foo',
  mirroring --locale=foo initdb option? It seems like a good idea, because
  you almost never want to set LC_COLLATE and LC_CTYPE differently. If we
  do that, should LOCALE=foo also imply a per-database lc_messages,
  lc_monetary, lc_numeric and lc_time settings? It seems like it should
  for the sake of consistency.

 The comment upthread was that we can/should leave that for 8.5.
 I agree with that at this point.  I think the above proposal is
 not as straightforward as it looks (in particular per-DB lc_messages
 has unpleasant implications for the postmaster log) and we should
 not tackle it in a hasty manner.

Those are good points, but note that createdb already *has* a --locale option 
that does something specific, so in light of your earlier argument that 
createdb and CREATE DATABASE options should be the same, the possibilities for 
a future CREATE DATABASE ... LOCALE=foo are already being constrained.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Solution of the file name problem of copy on windows.

2009-04-08 Thread Itagaki Takahiro

Hiroshi Saito z-sa...@guitar.ocn.ne.jp wrote:

 Um,  I had a focus in help the problem which is not avoided. 
 I am not sensitive to a problem being avoided depending on usage. 
 However, I will wish to work spontaneously, when it is help much. 

I'll research whether encoding of filesystem path is affected by
locale settings or not in some platforms. Also, we need to research
where we should get the system encoding when the locale is set to C,
which is popular in Japanese users.

I'll report to you the progress :)

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



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] NaN support in NUMERIC data type

2009-04-08 Thread Sam Mason
On Wed, Apr 08, 2009 at 06:11:59PM -0400, Tom Lane wrote:
 Sam Mason s...@samason.me.uk writes:
  On Tue, Apr 07, 2009 at 12:51:21PM -0400, Tom Lane wrote:
  IIRC, the explicit support for leading/trailing spaces is something that
  we added in float8in long after numeric_in was written, and I think just
  nobody thought about numeric at the time.  But it's clearly inconsistent
  to allow spaces around a regular value but not a NaN.
 
  The included patch was generated against 8.3.5 (because that's what I
  had lying around when I started playing) but applies with a little fuzz
  to the latest snapshot and does the right thing for me in both versions.
 
 Hmm, did it do the right thing for NaN with a typmod?  I doubt
 apply_typmod is safe for a NaN.

Oops, good catch.  Didn't think to check for that.

 Anyway, I revised this a bit and applied to HEAD.

I've not tested; but your changes look as though they will break:

  SELECT 'Infinity'::float::numeric;

I think you'll get '0' back instead of an error; either that or it's too
late for me to be thinking straight!

 I'm disinclined to back-patch; it's not really a bug
 but a definition change, and we get flack when we put that sort of
 change into stable branches ...

OK


Out of personal interest; why did you translate:

  while(isspace(*p)) p++;

into more verbose forms?  Is it so it fits in with the style in rest of
the code, or does it actually do something different that I'm missing?

-- 
  Sam  http://samason.me.uk/

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] NaN support in NUMERIC data type

2009-04-08 Thread Tom Lane
Sam Mason s...@samason.me.uk writes:
 On Wed, Apr 08, 2009 at 06:11:59PM -0400, Tom Lane wrote:
 Anyway, I revised this a bit and applied to HEAD.

 I've not tested; but your changes look as though they will break:
   SELECT 'Infinity'::float::numeric;

That gives an error now, just as it did before, so I'm not sure what you
think is broken about it ...

 Out of personal interest; why did you translate:
   while(isspace(*p)) p++;
 into more verbose forms?

I just copied-and-pasted what was there before (inside the subroutine).
Didn't see much reason to change it.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] default parameters for built-in functions

2009-04-08 Thread Peter Eisentraut
On Thursday 09 April 2009 02:24:53 Tom Lane wrote:
 Peter Eisentraut pete...@gmx.net writes:
  What is the purpose of fmgrtab anyway?

 It's so we can find the addresses of internal functions to call them.

Ah yes of course.  But then the table can just as well be built by something 
based on rgrep PG_FUNCTION_ARGS src/backend or something like that.  Which of 
course wasn't possible 10 years ago.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Clean shutdown and warm standby

2009-04-08 Thread Fujii Masao
Hi,

On Thu, Apr 9, 2009 at 4:11 AM, Guillaume Smet guillaume.s...@gmail.com wrote:
 Hi,

 Following the discussion here
 http://archives.postgresql.org/message-id/49d9e986.8010...@pse-consulting.de
 , I wrote a small patch which rotates the last XLog file on shutdown
 so that the archive command is also executed for this file and we are
 sure we have all the useful XLog files when we perform a clean
 shutdown of master + switch to the failover server. This rotation is
 done only if the archive mode is active and an archive command is set.

 It's currently really difficult to switch easily (ie without copying
 the file manually) to the failover server without any data loss.

 Is there any problem I've missed?

RequestXLogSwitch() doesn't wait until the switched WAL file has
actually been archived. So, some WAL files still may not exist in
the standby server also after clean shutdown of the primary.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers