Re: [HACKERS] Patch: improve selectivity estimation for IN/NOT IN

2012-03-04 Thread Euler Taveira de Oliveira
On 04-03-2012 00:20, Daniele Varrazzo wrote:
 It looks like you have grand plans for array estimation. My patch has
 a much more modest scope, and I'm hoping it could be applied to
 currently maintained PG versions, as I consider the currently produced
 estimations a bug.
 
We don't normally add new features to stable branches unless it is a bug. In
the optimizer case, planner regression is a bug (that this case is not).


-- 
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

-- 
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] LIST OWNED BY...

2012-02-29 Thread Euler Taveira de Oliveira
On 29-02-2012 14:20, Thom Brown wrote:
 No, the cascade part is fine.  It's the objects which won't cause a
 cascade that are an issue.  Putting it in a transaction for rolling
 back doesn't help find out what it intends to drop.
 
DROP OWNED BY foo VERBOSE?


-- 
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

-- 
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] LIST OWNED BY...

2012-02-29 Thread Euler Taveira de Oliveira
On 29-02-2012 15:23, Thom Brown wrote:
 Or just change it to output a verbose notice without changing the syntax?
 
I can't see why we will do it only for DROP OWNED. Chat messages are annoying
unless the user asks for it (that's why I suggested VERBOSE).


-- 
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

-- 
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] xlog location arithmetic

2012-02-25 Thread Euler Taveira de Oliveira
On 25-02-2012 09:23, Magnus Hagander wrote:
 Do we even *need* the validate_xlog_location() function? If we just
 remove those calls, won't we still catch all the incorrectly formatted
 ones in the errors of the sscanf() calls? Or am I too deep into
 weekend-mode and missing something obvious?
 
sscanf() is too fragile for input sanity check. Try
pg_xlog_location_diff('12/3', '-10/0'), for example. I won't object removing
that function if you protect xlog location input from silly users.


-- 
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

-- 
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] temporal algebra and data type

2012-02-22 Thread Euler Taveira de Oliveira
On 22-02-2012 09:50, Peter Padua Krauss wrote:
 1) There are another  project, similar or better than my!? There are pure SQL
 standard libraries for this?
 
Range Types [1]. It is 9.2 material but it is already in the repository.


[1] http://www.postgresql.org/docs/devel/static/rangetypes.html


-- 
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

-- 
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] wal_buffers

2012-02-19 Thread Euler Taveira de Oliveira
On 19-02-2012 02:24, Robert Haas wrote:
 I have attached tps scatterplots.  The obvious conclusion appears to
 be that, with only 16MB of wal_buffers, the buffer wraps around with
 some regularity

Isn't it useful to print some messages on the log when we have wrap around?
In this case, we have an idea that wal_buffers needs to be increased.


-- 
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

-- 
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] xlog location arithmetic

2012-02-09 Thread Euler Taveira de Oliveira
On 08-02-2012 09:35, Fujii Masao wrote:

Fujii, new patch attached. Thanks for your tests.

 But another problem happened. When I changed pg_proc.h so that the unused
 OID was assigned to pg_xlog_location_diff(), and executed the above again,
 I encountered the segmentation fault:
 
I reproduced the problems in my old 32-bit laptop. I fixed it casting to
int64. I also updated the duplicated OID.

 Why OID needs to be reassigned?
 
There isn't a compelling reason. It is just a way to say: hey, it is another
function with the same old name.

I'll not attach another version for pg_size_pretty because it is a matter of
updating a duplicated OID.


-- 
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 236a60a..826f002 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14446,11 +14446,15 @@ SELECT set_config('log_statement_stats', 'off', false);
indexterm
 primarypg_xlogfile_name_offset/primary
/indexterm
+   indexterm
+primarypg_xlog_location_diff/primary
+   /indexterm
 
para
 The functions shown in xref
 linkend=functions-admin-backup-table assist in making on-line backups.
-These functions cannot be executed during recovery.
+	These functions cannot be executed during recovery (except
+	functionpg_xlog_location_diff/function).
/para
 
table id=functions-admin-backup-table
@@ -14518,6 +14522,13 @@ SELECT set_config('log_statement_stats', 'off', false);
entrytypetext/, typeinteger//entry
entryConvert transaction log location string to file name and decimal byte offset within file/entry
   /row
+  row
+   entry
+literalfunctionpg_xlog_location_diff(parameterlocation/ typetext/, parameterlocation/ typetext/)/function/literal
+/entry
+   entrytypenumeric//entry
+   entryCalculate the difference between two transaction log locations/entry
+  /row
  /tbody
 /tgroup
/table
@@ -14611,6 +14622,13 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
/para
 
para
+	functionpg_xlog_location_diff/ calculates the difference in bytes
+	between two transaction log locations. It can be used with
+	structnamepg_stat_replication/structname or some functions shown in
+	xref linkend=functions-admin-backup-table to get the replication lag.
+   /para
+
+   para
 For details about proper usage of these functions, see
 xref linkend=continuous-archiving.
/para
diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c
index 2e10d4d..be7d388 100644
--- a/src/backend/access/transam/xlogfuncs.c
+++ b/src/backend/access/transam/xlogfuncs.c
@@ -26,6 +26,7 @@
 #include replication/walreceiver.h
 #include storage/smgr.h
 #include utils/builtins.h
+#include utils/numeric.h
 #include utils/guc.h
 #include utils/timestamp.h
 
@@ -465,3 +466,83 @@ pg_is_in_recovery(PG_FUNCTION_ARGS)
 {
 	PG_RETURN_BOOL(RecoveryInProgress());
 }
+
+static void
+validate_xlog_location(char *str)
+{
+#define	MAXLSNCOMPONENT		8
+
+	int	len1, len2;
+
+	len1 = strspn(str, 0123456789abcdefABCDEF);
+	if (len1  1 || len1  MAXLSNCOMPONENT || str[len1] != '/')
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+			 errmsg(invalid input syntax for transaction log location: \%s\, str)));
+	len2 = strspn(str + len1 + 1, 0123456789abcdefABCDEF);
+	if (len2  1 || len2  MAXLSNCOMPONENT || str[len1 + 1 + len2] != '\0')
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+			 errmsg(invalid input syntax for transaction log location: \%s\, str)));
+}
+
+/*
+ * Compute the difference in bytes between two WAL locations.
+ */
+Datum
+pg_xlog_location_diff(PG_FUNCTION_ARGS)
+{
+	text		*location1 = PG_GETARG_TEXT_P(0);
+	text		*location2 = PG_GETARG_TEXT_P(1);
+	char		*str1, *str2;
+	XLogRecPtr	loc1, loc2;
+	Numeric		result;
+
+	/*
+	 * Read and parse input
+	 */
+	str1 = text_to_cstring(location1);
+	str2 = text_to_cstring(location2);
+
+	validate_xlog_location(str1);
+	validate_xlog_location(str2);
+
+	if (sscanf(str1, %X/%X, loc1.xlogid, loc1.xrecoff) != 2)
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+			errmsg(could not parse transaction log location \%s\, str1)));
+	if (sscanf(str2, %X/%X, loc2.xlogid, loc2.xrecoff) != 2)
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+			errmsg(could not parse transaction log location \%s\, str2)));
+
+	/*
+	 * Sanity check
+	 */
+	if (loc1.xrecoff  XLogFileSize)
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+			errmsg(xrecoff \%X\ is out of valid range, 0..%X, loc1.xrecoff, XLogFileSize)));
+	if (loc2.xrecoff  XLogFileSize)
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+			errmsg(xrecoff \%X\ is out of valid range, 0..%X, loc2.xrecoff, XLogFileSize)));
+
+	/*
+	 * result

Re: [HACKERS] xlog location arithmetic

2012-02-07 Thread Euler Taveira de Oliveira
On 26-01-2012 06:19, Fujii Masao wrote:

Thanks for your review. Comments below.

 When I compiled the source with xlogdiff.patch, I got the following warnings.
 
 xlogfuncs.c:511:2: warning: format '%lX' expects argument of type
 'long unsigned int *', but argument 3 has type 'uint64 *' [-Wformat]
 
What is your compiler? I'm using gcc 4.6.2. I refactored the patch so I'm now
using XLogRecPtr and %X.

 postgres=# SELECT pg_xlog_location_diff('0/274', '0/274');
 ERROR:  xrecoff 274 is out of valid range, 0..A4A534C
 
Ugh? I can't reproduce that. It seems to be related to long int used by the
prior version.

 Since pg_xlog_location_diff() can be executed during recovery,
 the above needs to be updated.
 
Fixed.

 While the output was int8 I could use
 pg_size_pretty but now I couldn't. I attached another patch that implements
 pg_size_pretty(numeric).
 
I realized that it collides with the pg_size_pretty(int8) if we don't specify
a type. Hence, I decided to drop the pg_size_pretty(int8) in favor of
pg_size_pretty(numeric). It is slower than the former but it is not a
performance critical function.

 According to the above source code comment in pg_proc.h, ISTM
 pg_size_pretty() for numeric also needs to have its own DESCR().
 
Fixed.

 According to man strcat, the dest string must have enough space for
 the result.
 buf has enough space?
 
Ops. Fixed.


-- 
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 236a60a..511a918 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14942,7 +14942,7 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
   /row
   row
entry
-literalfunctionpg_size_pretty(typebigint/type)/function/literal
+literalfunctionpg_size_pretty(typenumeric/type)/function/literal
 /entry
entrytypetext/type/entry
entryConverts a size in bytes into a human-readable format with size units/entry
diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c
index 26a8c01..d4a142b 100644
--- a/src/backend/utils/adt/dbsize.c
+++ b/src/backend/utils/adt/dbsize.c
@@ -24,6 +24,7 @@
 #include storage/fd.h
 #include utils/acl.h
 #include utils/builtins.h
+#include utils/numeric.h
 #include utils/rel.h
 #include utils/relmapper.h
 #include utils/syscache.h
@@ -506,48 +507,101 @@ pg_total_relation_size(PG_FUNCTION_ARGS)
 	PG_RETURN_INT64(size);
 }
 
-/*
- * formatting with size units
- */
 Datum
 pg_size_pretty(PG_FUNCTION_ARGS)
 {
-	int64		size = PG_GETARG_INT64(0);
-	char		buf[64];
-	int64		limit = 10 * 1024;
-	int64		limit2 = limit * 2 - 1;
+	Numeric		size = PG_GETARG_NUMERIC(0);
+	Numeric		limit, limit2;
+
+	char		*buf, *result;
 
-	if (size  limit)
-		snprintf(buf, sizeof(buf), INT64_FORMAT  bytes, size);
+	limit = DatumGetNumeric(DirectFunctionCall1(int8_numeric, Int64GetDatum((int64) (10 * 1024;
+	limit2 = DatumGetNumeric(DirectFunctionCall1(int8_numeric, Int64GetDatum((int64) (10 * 1024 * 2 - 1;
+
+	if (DatumGetBool(DirectFunctionCall2(numeric_lt, NumericGetDatum(size), NumericGetDatum(limit
+	{
+		buf = DatumGetCString(DirectFunctionCall1(numeric_out, NumericGetDatum(size)));
+		result = palloc(strlen(buf) + 7);
+		strcpy(result, buf);
+		strcat(result,  bytes);
+	}
 	else
 	{
-		size = 9;/* keep one extra bit for rounding */
-		if (size  limit2)
-			snprintf(buf, sizeof(buf), INT64_FORMAT  kB,
-	 (size + 1) / 2);
+		Numeric		arg2;
+
+		/* keep one extra bit for rounding */
+		/* size = 9 */
+		arg2 = DatumGetNumeric(DirectFunctionCall1(int8_numeric, Int64GetDatum((int64) pow(2, 9;
+		size = DatumGetNumeric(DirectFunctionCall2(numeric_div_trunc, NumericGetDatum(size), NumericGetDatum(arg2)));
+
+		if (DatumGetBool(DirectFunctionCall2(numeric_lt, NumericGetDatum(size), NumericGetDatum(limit2
+		{
+			/* size = (size + 1) / 2 */
+			size = DatumGetNumeric(DirectFunctionCall2(numeric_add, NumericGetDatum(size), 
+	DirectFunctionCall1(int8_numeric, Int64GetDatum(1;
+			size = DatumGetNumeric(DirectFunctionCall2(numeric_div_trunc, NumericGetDatum(size), 
+	DirectFunctionCall1(int8_numeric, Int64GetDatum(2;
+			buf = DatumGetCString(DirectFunctionCall1(numeric_out, NumericGetDatum(size)));
+			result = palloc(strlen(buf) + 4);
+			strcpy(result, buf);
+			strcat(result,  kB);
+		}
 		else
 		{
-			size = 10;
-			if (size  limit2)
-snprintf(buf, sizeof(buf), INT64_FORMAT  MB,
-		 (size + 1) / 2);
+			Numeric		arg3;
+
+			/* size = 10 */
+			arg3 = DatumGetNumeric(DirectFunctionCall1(int8_numeric, Int64GetDatum((int64) pow(2, 10;
+			size = DatumGetNumeric(DirectFunctionCall2(numeric_div_trunc, NumericGetDatum(size), NumericGetDatum(arg3)));
+
+			if (DatumGetBool(DirectFunctionCall2(numeric_lt, NumericGetDatum(size), NumericGetDatum(limit2
+			{
+/* size

Re: [HACKERS] Patch pg_is_in_backup()

2012-02-02 Thread Euler Taveira de Oliveira
On 02-02-2012 20:06, Magnus Hagander wrote:
 If there is more than one concurrent backup running, which one do you
 return? The first one or the latest one? Or perhaps you need an
 interface thta can return them all...
 
IMHO, pg_is_in_backup() should return true if one or more backup copies are
running. As about returning the backup timestamp, we could return an array
like array[['label_1', '2012-01-28 02:00:01 BRST'], ['label_2', '2012-01-28
03:40:34 BRST']] or NULL if none.


-- 
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

-- 
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] Patch pg_is_in_backup()

2012-01-30 Thread Euler Taveira de Oliveira
On 30-01-2012 15:33, Gilles Darold wrote:
 Yesterday I was facing a little issue with a backup software (NetBackup)
 that do not report error when a post backup script is run. The problem
 is that this script execute pg_stop_backup() and if there's any failure
 PostgreSQL keeps running in on-line backup mode. So the backup is not
 completed and the next one too because the call to pg_start_backup()
 will fail.
 
I use something similar to your pl/PgSQL version and was about to propose
something along these lines to core. +1 to include it. Please, add your patch
to the next CF.


-- 
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

-- 
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] xlog location arithmetic

2012-01-21 Thread Euler Taveira de Oliveira
On 23-12-2011 12:05, Tom Lane wrote:
 I too think a datatype is overkill, if we're only planning on providing
 one function.  Just emit the values as numeric and have done.
 
Here it is. Output changed to numeric. While the output was int8 I could use
pg_size_pretty but now I couldn't. I attached another patch that implements
pg_size_pretty(numeric).


-- 
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 48631cc..04bc24d 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14378,6 +14378,9 @@ SELECT set_config('log_statement_stats', 'off', false);
indexterm
 primarypg_xlogfile_name_offset/primary
/indexterm
+   indexterm
+primarypg_xlog_location_diff/primary
+   /indexterm
 
para
 The functions shown in xref
@@ -14450,6 +14453,13 @@ SELECT set_config('log_statement_stats', 'off', false);
entrytypetext/, typeinteger//entry
entryConvert transaction log location string to file name and decimal byte offset within file/entry
   /row
+  row
+   entry
+literalfunctionpg_xlog_location_diff(parameterlocation/ typetext/, parameterlocation/ typetext/)/function/literal
+/entry
+   entrytypenumeric//entry
+   entryCalculate the difference between two transaction log locations/entry
+  /row
  /tbody
 /tgroup
/table
@@ -14543,6 +14553,13 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
/para
 
para
+	functionpg_xlog_location_diff/ calculates the difference in bytes
+	between two transaction log locations. It can be used with
+	structnamepg_stat_replication/structname or some functions shown in
+	xref linkend=functions-admin-backup-table to get the replication lag.
+   /para
+
+   para
 For details about proper usage of these functions, see
 xref linkend=continuous-archiving.
/para
diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c
index 2e10d4d..e03c5e8 100644
--- a/src/backend/access/transam/xlogfuncs.c
+++ b/src/backend/access/transam/xlogfuncs.c
@@ -26,6 +26,7 @@
 #include replication/walreceiver.h
 #include storage/smgr.h
 #include utils/builtins.h
+#include utils/numeric.h
 #include utils/guc.h
 #include utils/timestamp.h
 
@@ -465,3 +466,84 @@ pg_is_in_recovery(PG_FUNCTION_ARGS)
 {
 	PG_RETURN_BOOL(RecoveryInProgress());
 }
+
+static void
+validate_xlog_location(char *str)
+{
+#define	MAXLSNCOMPONENT		8
+
+	int	len1, len2;
+
+	len1 = strspn(str, 0123456789abcdefABCDEF);
+	if (len1  1 || len1  MAXLSNCOMPONENT || str[len1] != '/')
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+			 errmsg(invalid input syntax for transaction log location: \%s\, str)));
+	len2 = strspn(str + len1 + 1, 0123456789abcdefABCDEF);
+	if (len2  1 || len2  MAXLSNCOMPONENT || str[len1 + 1 + len2] != '\0')
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+			 errmsg(invalid input syntax for transaction log location: \%s\, str)));
+}
+
+/*
+ * Compute the difference in bytes between two WAL locations.
+ */
+Datum
+pg_xlog_location_diff(PG_FUNCTION_ARGS)
+{
+	text	*location1 = PG_GETARG_TEXT_P(0);
+	text	*location2 = PG_GETARG_TEXT_P(1);
+	char	*str1, *str2;
+	uint64	xlogid1, xrecoff1;
+	uint64	xlogid2, xrecoff2;
+	Numeric	result;
+
+	/*
+	 * Read and parse input
+	 */
+	str1 = text_to_cstring(location1);
+	str2 = text_to_cstring(location2);
+
+	validate_xlog_location(str1);
+	validate_xlog_location(str2);
+
+	if (sscanf(str1, %8lX/%8lX, xlogid1, xrecoff1) != 2)
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+			errmsg(could not parse transaction log location \%s\, str1)));
+	if (sscanf(str2, %8lX/%8lX, xlogid2, xrecoff2) != 2)
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+			errmsg(could not parse transaction log location \%s\, str2)));
+
+	/*
+	 * Sanity check
+	 */
+	if (xrecoff1  XLogFileSize)
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+			errmsg(xrecoff \%lX\ is out of valid range, 0..%X, xrecoff1, XLogFileSize)));
+	if (xrecoff2  XLogFileSize)
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+			errmsg(xrecoff \%lX\ is out of valid range, 0..%X, xrecoff2, XLogFileSize)));
+
+	/*
+	 * result = XLogFileSize * (xlogid1 - xlogid2) + xrecoff1 - xrecoff2
+	 */
+	result = DatumGetNumeric(DirectFunctionCall2(numeric_sub,
+			DirectFunctionCall1(int8_numeric, Int64GetDatum(xlogid1)),
+			DirectFunctionCall1(int8_numeric, Int64GetDatum(xlogid2;
+	result = DatumGetNumeric(DirectFunctionCall2(numeric_mul,
+			DirectFunctionCall1(int8_numeric, Int64GetDatum(XLogFileSize)),
+			NumericGetDatum(result)));
+	result = DatumGetNumeric(DirectFunctionCall2(numeric_add,
+			NumericGetDatum(result),
+			DirectFunctionCall1(int8_numeric, Int64GetDatum(xrecoff1

Re: [HACKERS] xlog location arithmetic

2012-01-14 Thread Euler Taveira de Oliveira
On 14-01-2012 11:06, Fujii Masao wrote:
 I think that this function is very useful. Can you add the patch into
 CommitFest 2012-1 ?
 
Sure. But I must adjust the patch based on the thread comments (basically,
numeric output). I have a new patch but need to test it before submitting it.
I'll post this weekend.


-- 
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

-- 
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] 9.3 feature proposal: vacuumdb -j #

2012-01-13 Thread Euler Taveira de Oliveira
On 13-01-2012 18:50, Josh Berkus wrote:
 It occurs to me that I would find it quite personally useful if the
 vacuumdb utility was multiprocess capable.
 
It is in the mid of my TODO list. reindexdb is in the plans too.


-- 
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

-- 
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] log messages for archive recovery progress

2012-01-10 Thread Euler Taveira de Oliveira
On 08-01-2012 11:59, Satoshi Nagayasu / Uptime Technologies, LLC. wrote:
 [2011-12-08 15:14:36 JST] 16758: LOG:  restored log file 
 00080046 from archive
 [2011-12-08 15:14:36 JST] 16758: LOG:  recoverying 00080046
 [2011-12-08 15:14:36 JST] 16758: LOG:  restored log file 
 00080047 from archive
 [2011-12-08 15:14:36 JST] 16758: LOG:  recoverying 00080047
 cp: cannot stat `/backups/archlog/00080048': No such file or 
 directory
 [2011-12-08 15:14:37 JST] 16758: LOG:  could not restore file 
 00080048 from archive
 [2011-12-08 15:14:37 JST] 16758: LOG:  attempting to look into pg_xlog
 [2011-12-08 15:14:37 JST] 16758: LOG:  recoverying 00080048
 
What about just 'restored log file 00080048 from pg_xlog'
instead of the last two messages? If you can't read from pg_xlog emit 'could
not restore file 00080048 from pg_xlog'.


-- 
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

-- 
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] xlog location arithmetic

2011-12-20 Thread Euler Taveira de Oliveira
On 20-12-2011 07:27, Magnus Hagander wrote:
 On Tue, Dec 6, 2011 at 19:06, Robert Haas robertmh...@gmail.com wrote:
 On Tue, Dec 6, 2011 at 1:00 PM, Euler Taveira de Oliveira
 eu...@timbira.com wrote:
 On 06-12-2011 13:11, Robert Haas wrote:
 On Tue, Dec 6, 2011 at 5:14 AM, Magnus Hagander mag...@hagander.net 
 wrote:
 I've been considering similar things, as you can find in the archives,
 but what I was thinking of was converting the number to just a plain
 bigint, then letting the user apply whatever arithmetic wanted at the
 SQL level. I never got around to acutally coding it, though. It could
 easily be extracted from your patch of course - and I think that's a
 more flexible approach. Is there some advantage to your method that
 I'm missing?

 I went so far as to put together an lsn data type.  I didn't actually
 get all that far with it, which is why I haven't posted it sooner, but
 here's what I came up with.  It's missing indexing support and stuff,
 but that could be added if people like the approach.  It solves this
 problem by implementing -(lsn,lsn) = numeric (not int8, that can
 overflow since it is not unsigned), which allows an lsn = numeric
 conversion by just subtracting '0/0'::lsn.

 Interesting approach. I don't want to go that far. If so, you want to change
 all of those functions that deal with LSNs and add some implicit conversion
 between text and lsn data types (for backward compatibility). As of int8, 
 I'm
 
 As long as you have the conversion, you don't really need to change
 them, do you? It might be nice in some ways, but this is still a
 pretty internal operation, so I don't see it as critical.
 
For correctness, yes.

At this point, my question is: do we want to support the lsn data type idea or
a basic function that implements the difference between LSNs?


-- 
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

-- 
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] Postgres 9.1: Adding rows to table causing too much latency in other queries

2011-12-19 Thread Euler Taveira de Oliveira
On 19-12-2011 12:30, Sushant Sinha wrote:
 I recently upgraded my postgres server from 9.0 to 9.1.2 and I am
 finding a peculiar problem.I have a program that periodically adds rows
 to this table using INSERT. Typically the number of rows is just 1-2
 thousand when the table already has 500K rows. Whenever the program is
 adding rows, the performance of the search query on the same table is
 very bad. The query uses the gin index and the tsearch ranking function
 ts_rank_cd. 
 
How bad is bad? It seems you are suffering from don't-fit-on-cache problem, no?

 This never happened earlier with postgres 9.0 Is there a known issue
 with Postgres 9.1? Or how to report this problem?
 
Test case? Query times? Query plans? Are you sure you the compile options are
the same? What about the configuration parameters? What is the exact version
of both installations?


-- 
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

-- 
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] xlog location arithmetic

2011-12-06 Thread Euler Taveira de Oliveira
On 06-12-2011 07:14, Magnus Hagander wrote:
 On Tue, Dec 6, 2011 at 05:19, Euler Taveira de Oliveira
 eu...@timbira.com wrote:
 Hi,

 A while ago when blogging about WAL [1], I noticed a function to deal with
 xlog location arithmetic is wanted. I remembered Depez [2] mentioning it and
 after some questions during trainings and conferences I decided to translate
 my shell script function in C.

 The attached patch implements the function pg_xlog_location_diff (bikeshed
 colors are welcome). It calculates the difference between two given
 transaction log locations. Now that we have pg_stat_replication view, it will
 be easy to get the lag just passing columns as parameters. Also, the
 monitoring tools could take advantage of it instead of relying on a fragile
 routine to get the lag.
 
 I've been considering similar things, as you can find in the archives,
 but what I was thinking of was converting the number to just a plain
 bigint, then letting the user apply whatever arithmetic wanted at the
 SQL level. I never got around to acutally coding it, though. It could
 easily be extracted from your patch of course - and I think that's a
 more flexible approach. Is there some advantage to your method that
 I'm missing?
 
The only advantage is that you don't expose the arithmetic, e.g., user doesn't
need to know the xlog internals (like I described in a recent blog post). If
one day we consider changes in xlog arithmetic (for example, XLogFileSize), we
don't need to worry too much about external tools.

 Also, why do you use DirectFunctionCall to do the simple math, and not
 just do the math right there in the function?
 
I use it because I don't want to duplicate the overflow code.


-- 
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

-- 
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] xlog location arithmetic

2011-12-06 Thread Euler Taveira de Oliveira
On 06-12-2011 13:11, Robert Haas wrote:
 On Tue, Dec 6, 2011 at 5:14 AM, Magnus Hagander mag...@hagander.net wrote:
 I've been considering similar things, as you can find in the archives,
 but what I was thinking of was converting the number to just a plain
 bigint, then letting the user apply whatever arithmetic wanted at the
 SQL level. I never got around to acutally coding it, though. It could
 easily be extracted from your patch of course - and I think that's a
 more flexible approach. Is there some advantage to your method that
 I'm missing?
 
 I went so far as to put together an lsn data type.  I didn't actually
 get all that far with it, which is why I haven't posted it sooner, but
 here's what I came up with.  It's missing indexing support and stuff,
 but that could be added if people like the approach.  It solves this
 problem by implementing -(lsn,lsn) = numeric (not int8, that can
 overflow since it is not unsigned), which allows an lsn = numeric
 conversion by just subtracting '0/0'::lsn.
 
Interesting approach. I don't want to go that far. If so, you want to change
all of those functions that deal with LSNs and add some implicit conversion
between text and lsn data types (for backward compatibility). As of int8, I'm
not aware of any modern plataform that int8 is not 64 bits. I'm not against
numeric use; I'm just saying that int8 is sufficient.


-- 
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

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


[HACKERS] xlog location arithmetic

2011-12-05 Thread Euler Taveira de Oliveira
Hi,

A while ago when blogging about WAL [1], I noticed a function to deal with
xlog location arithmetic is wanted. I remembered Depez [2] mentioning it and
after some questions during trainings and conferences I decided to translate
my shell script function in C.

The attached patch implements the function pg_xlog_location_diff (bikeshed
colors are welcome). It calculates the difference between two given
transaction log locations. Now that we have pg_stat_replication view, it will
be easy to get the lag just passing columns as parameters. Also, the
monitoring tools could take advantage of it instead of relying on a fragile
routine to get the lag.

I noticed that pg_xlogfile_name* functions does not sanity check the xrecoff
boundaries but that is material for another patch.


[1] http://eulerto.blogspot.com/2011/11/understanding-wal-nomenclature.html
[2]
http://www.depesz.com/index.php/2011/01/24/waiting-for-9-1-pg_stat_replication/


-- 
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index ddfb29a..cce218a 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14342,6 +14342,9 @@ SELECT set_config('log_statement_stats', 'off', false);
indexterm
 primarypg_xlogfile_name_offset/primary
/indexterm
+   indexterm
+primarypg_xlog_location_diff/primary
+   /indexterm
 
para
 The functions shown in xref
@@ -14414,6 +14417,13 @@ SELECT set_config('log_statement_stats', 'off', false);
entrytypetext/, typeinteger//entry
entryConvert transaction log location string to file name and decimal byte offset within file/entry
   /row
+  row
+   entry
+literalfunctionpg_xlog_location_diff(parameterlocation/ typetext/, parameterlocation/ typetext/)/function/literal
+/entry
+   entrytypebigint//entry
+   entryCalculate the difference between two transaction log locations/entry
+  /row
  /tbody
 /tgroup
/table
@@ -14507,6 +14517,13 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
/para
 
para
+	functionpg_xlog_location_diff/ calculates the difference in bytes
+	between two transaction log locations. It can be used with
+	structnamepg_stat_replication/structname or some functions shown in
+	xref linkend=functions-admin-backup-table to get the replication lag.
+   /para
+
+   para
 For details about proper usage of these functions, see
 xref linkend=continuous-archiving.
/para
diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c
index 22c6ca0..09e8369 100644
--- a/src/backend/access/transam/xlogfuncs.c
+++ b/src/backend/access/transam/xlogfuncs.c
@@ -26,6 +26,7 @@
 #include replication/walreceiver.h
 #include storage/smgr.h
 #include utils/builtins.h
+#include utils/int8.h
 #include utils/guc.h
 #include utils/timestamp.h
 
@@ -465,3 +466,57 @@ pg_is_in_recovery(PG_FUNCTION_ARGS)
 {
 	PG_RETURN_BOOL(RecoveryInProgress());
 }
+
+/*
+ * Compute the difference in bytes between two WAL locations.
+ */
+Datum
+pg_xlog_location_diff(PG_FUNCTION_ARGS)
+{
+	text	*location1 = PG_GETARG_TEXT_P(0);
+	text	*location2 = PG_GETARG_TEXT_P(1);
+	char	*str1, *str2;
+	uint32	xlogid1, xrecoff1;
+	uint32	xlogid2, xrecoff2;
+	int64	tmp;
+	int64	result;
+
+	/*
+	 * Read and parse input
+	 */
+	str1 = text_to_cstring(location1);
+	str2 = text_to_cstring(location2);
+
+	if (sscanf(str1, %8X/%8X, xlogid1, xrecoff1) != 2)
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+			errmsg(could not parse transaction log location \%s\, str1)));
+	if (sscanf(str2, %8X/%8X, xlogid2, xrecoff2) != 2)
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+			errmsg(could not parse transaction log location \%s\, str2)));
+
+	/*
+	 * Sanity check
+	 */
+	if (xrecoff1  XLogFileSize)
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+			errmsg(xrecoff \%X\ is out of valid range, 0..%X, xrecoff1, XLogFileSize)));
+	if (xrecoff2  XLogFileSize)
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+			errmsg(xrecoff \%X\ is out of valid range, 0..%X, xrecoff2, XLogFileSize)));
+
+	/*
+	 * Use the int8 functions mainly for overflow detection
+	 *
+	 * result = XLogFileSize * (xlogid1 - xlogid2) + xrecoff1 - xrecoff2
+	 */
+	tmp = DirectFunctionCall2(int8mi, xlogid1, xlogid2);
+	tmp = DirectFunctionCall2(int8mul, XLogFileSize, tmp);
+	tmp = DirectFunctionCall2(int8pl, tmp, xrecoff1);
+	result = DirectFunctionCall2(int8mi, tmp, xrecoff2);
+
+	PG_RETURN_INT64(result);
+}
diff --git a/src/include/access/xlog_internal.h b/src/include/access/xlog_internal.h
index cb43879..3e7340b 100644
--- a/src/include/access/xlog_internal.h
+++ b/src/include/access/xlog_internal.h
@@ -279,5 +279,6 @@ extern Datum pg_is_in_recovery(PG_FUNCTION_ARGS);
 extern Datum pg_xlog_replay_pause(PG_FUNCTION_ARGS

Re: [HACKERS] includeifexists in configuration file

2011-11-16 Thread Euler Taveira de Oliveira
On 16-11-2011 02:28, Greg Smith wrote:
 By recent popular request in the ongoing discussion saga around merging the
 recovery.conf, I've added an includeifexists directive to the
 postgresql.conf in the attached patch.
 
I'm not following the merging recovery.conf thread but isn't it worth emitting
at least an WARNING message when the file does not exist?

Something like

WARNING:  could not open configuration file /foo/missing.conf, skipping

Let's suppose a DBA is using this new feature to include some general company
recommendations. If (s)he mistyped the name of the file, the general
recommendations will not be applied and the DBA won't be even warned. That's
not what a DBA would expect.


-- 
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

-- 
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] proposal : backend startup hook / after logon trigger

2011-11-10 Thread Euler Taveira de Oliveira
On 10-11-2011 21:12, Tomas Vondra wrote:
 I occasionally need to perform some action whenever a user connects, and
 there's nothing like an AFTER LOGON trigger (available in some other
 databases).
 
Are you proposing an on-logon hook or an on-connect trigger? It is two
separate things. The former can't solve some tasks (e.g. execute whatever pl
code) and the latter can't be implemented with a simple hook (you will have to
propose a syntax and offer some machinery to execute the pl code).

Of course, if you want to propose any of these ideas, keep in mind that a
symmetric functionality (e.g. on-logoff hook or on-disconnect trigger)  should
be implemented too.


-- 
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

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


[HACKERS] autovacuum workers warning

2011-10-26 Thread Euler Taveira de Oliveira

Hi,

Some time ago [1], I proposed print a message every time there isn't 
autovacuum slots available and it asks for another one. It is not a complete 
solution for autovacuum tuning but it would at least give us a hint that 
number of workers is insufficient to keep up with the current load. The 
accurate number of slots needed would be the optimal solution but that 
information is not free (it would have to check every table in the databases 
available to get the approximate number of slots needed. Approximate because 
some table could be finishing the operation). A new warning is better than 
nothing. If we decided to improve this area in a future we should remove the 
warning but right now it would be an excelent hint to tune autovacuum.



[1] http://archives.postgresql.org/pgsql-hackers/2011-06/msg00678.php


--
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 3b71232..4ec0f87 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -656,6 +656,12 @@ AutoVacLauncherMain(int argc, char *argv[])
 
 		can_launch = (AutoVacuumShmem-av_freeWorkers != NULL);
 
+		if (!can_launch)
+			ereport(LOG,
+	(errmsg(maximum number of autovacuum workers reached),
+	 errhint(Consider increasing autovacuum_max_workers (currently %d).,
+		 	autovacuum_max_workers)));
+
 		if (AutoVacuumShmem-av_startingWorker != NULL)
 		{
 			int			waittime;

-- 
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] autovacuum workers warning

2011-10-26 Thread Euler Taveira de Oliveira

On 26-10-2011 16:14, Alvaro Herrera wrote:

Well, just increasing the number of workers would do nothing to solve
the problem, because the more workers there are, the slower they work.
The actual solution to the problem would be decreasing
autovacuum_vacuum_delay_cost, and/or related knobs.

Why not? You're saying that parallelizing the work won't help? As about 
autovacuum_vacuum_cost_delay, don't you think that 20ms isn't small enough to 
suggest decreasing instead of increasing the number of workers?



Wasn't there some discussion recently on measuring the length of the
work queue, or something like that?

Yes, there is. As I said, it is an expensive and approximate measure. I'm not 
saying that is not the right direction, I'm arguing that a hint is better than 
nothing. Right now the only way to know if it is out of workers is to query 
pg_stat_activity frequently.



--
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

--
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] autovacuum and orphaned large objects

2011-10-24 Thread Euler Taveira de Oliveira

On 24-10-2011 10:57, Robert Haas wrote:

I think the main reason why vacuumlo is a contrib module rather than
in core is that it is just a heuristic, and it might not be what
everyone wants to do.  You could store a bunch of large objects in the
database and use the returned OIDs to generate links that you email to
users, and then when the user clicks on the link we retrieve the
corresponding LO and send it to the user over HTTP.  In that design,
there are no tables in the database at all, yet the large objects
aren't orphaned.

Uau, what a strange method to solve a problem and possibly bloat your 
database. No, I'm not suggesting that we forbid it. The proposed method could 
cleanup orphaned LO in 95% (if not 99%) of the use cases.


I've never heard someone using LO like you describe it. It seems strange that 
someone distributes an OID number but (s)he does not store its reference at 
the same database. Yes, it is a possibility but ...



--
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

--
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] autovacuum and orphaned large objects

2011-10-24 Thread Euler Taveira de Oliveira

On 24-10-2011 11:36, Tom Lane wrote:

Euler Taveira de Oliveiraeu...@timbira.com  writes:

The main point of autovacuum is maintenance tasks. Currently, it executes
VACUUM and ANALYZE commands. I want to propose that we incorporate vacuumlo
functionality into it.


I'm not terribly thrilled with that because (a) large objects seem like
mostly a legacy feature from here, and


Right, but there isn't a solution for  1 GB column data besides LO.


(b) it's hard to see how to
implement it without imposing overhead on everybody, whether they use
LOs or not.  This is especially problematic if you're proposing that
cleanup triggers not be required.

I was thinking about starting the LO cleanup after autovacuum finishes the 
VACUUM command (so no trigger, no new mechanism). And about the overhead 
imposed, it will only execute the cleanup code in the tables that have oid/lo 
columns (this information will be collected when the autovacuum collects table 
information).



--
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

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


[HACKERS] autovacuum and orphaned large objects

2011-10-23 Thread Euler Taveira de Oliveira

Hi,

The main point of autovacuum is maintenance tasks. Currently, it executes 
VACUUM and ANALYZE commands. I want to propose that we incorporate vacuumlo 
functionality into it. While dealing with large objects (LO), we have lo 
contrib module that helps with LO maintenance but has some limitations (does 
not handle DROP TABLE and TRUNCATE cases) and vacuumlo that does an excellent 
job but have to be executed outside DBMS. The proposal is to clean up LO when 
autovacuum triggers VACUUM; cleanup LO routine will starts after(?) VACUUM 
command.


In a near future I want to propose that orphaned LO be cleaned up by VACUUM 
but that a history for another thread...


Comments?


--
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

--
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] pg_cancel_backend by non-superuser

2011-10-01 Thread Euler Taveira de Oliveira

On 01-10-2011 17:44, Daniel Farina wrote:

On Fri, Sep 30, 2011 at 9:30 PM, Tom Lanet...@sss.pgh.pa.us  wrote:

ISTM it would be reasonably non-controversial to allow users to issue
pg_cancel_backend against other sessions logged in as the same userID.
The question is whether to go further than that, and if so how much.


In *every* case -- and there are many -- where we've had people
express pain, this would have sufficed.

I see. What about passing this decision to DBA? I mean a GUC 
can_cancel_session = user, dbowner (default is '' -- only superuser). You can 
select one or both options. This GUC can only be changed by superuser.



--
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

--
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] unaccent contrib

2011-09-22 Thread Euler Taveira de Oliveira

On 22-09-2011 12:39, Daniel Vázquez wrote:

Before 9.x, how do unaccent full text searches ?

Perform pre-processing (normalization) of the string *before* inserting and 
*before* searching.



--
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

--
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] Hot Backup with rsync fails at pg_clog if under load

2011-09-22 Thread Euler Taveira de Oliveira

On 22-09-2011 11:24, Linas Virbalas wrote:

In order to check more cases, I have changed the procedure to force an
immediate checkpoint, i.e. pg_start_backup('backup_under_load', true). With
the same load generator running, pg_start_backup returned almost
instantaneously compared to how long it took previously.

Most importantly, after doing this change, I cannot reproduce the pg_clog
error message anymore. In other words, with immediate checkpoint hot backup
succeeds under this load!

Interesting. I remembered someone reporting this same problem but it was not 
reproducible by some of us.



Do you have ideas why does the Hot Backup operation with
pg_start_backup('backup_under_load', true) succeed while
pg_start_backup('backup_under_load') fails under the same load?


I don't but if you show us the output of the steps above...


If needed, I could do that, if I had the exact procedure... Currently,
during the start of the backup I take the following information:

Just show us the output of pg_start_backup and part of the standby log with 
the following message 'redo starts at' and the subsequent messages up to the 
failure.



--
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

--
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] Hot Backup with rsync fails at pg_clog if under load

2011-09-21 Thread Euler Taveira de Oliveira

On 21-09-2011 11:44, Linas Virbalas wrote:

[This question doesn't belong to -hackers. Please post it in -general or -admin]


Procedure:

1. Start load generator on the master (WAL archiving enabled).
2. Prepare a Streaming Replication standby (accepting WAL files too):
2.1. pg_switch_xlog() on the master;

You don't need this.


2.2. pg_start_backup(Obackup_under_load¹) on the master (this will take a
while as master is loaded up);

No. if you use pg_start_backup('foo', true) it will be fast. Check the manual.


2.3. rsync data/global/pg_control to the standby;

Why are you doing this? If ...


2.4. rsync all other data/ (without pg_xlog) to the standby;

you will copy it again or no? Don't understand your point.


2.5. pg_stop_backup() on the master;
2.6. Wait to receive all WAL files, generated during the backup, on the
standby;
2.6. Start the standby PG instance.

The last step will, usually, fail with a similar error:

The problem could be that the minimum recovery point (step 2.3) is different 
from the end of rsync if you are under load.



--
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

--
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] unaccent contrib

2011-09-21 Thread Euler Taveira de Oliveira

On 21-09-2011 13:28, Daniel Vázquez wrote:

unaccent is compatible with postgresql 8.4 (but not is in their contrib
version distribution)


No, it is not. AFAICS it is necessary to add some backend code that is not in 
8.4.


--
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

--
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] unaccent contrib

2011-09-21 Thread Euler Taveira de Oliveira

On 21-09-2011 15:23, Daniel Vázquez wrote:

No alternatives for unaccent on 8.4?


Not that I know of.


--
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

--
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] WIP: Collecting statistics on CSV file data

2011-09-20 Thread Euler Taveira de Oliveira

On 20-09-2011 11:12, Marti Raudsepp wrote:

2011/9/12 Etsuro Fujitafujita.ets...@lab.ntt.co.jp:

This is called when ANALYZE command is executed. (ANALYZE
command should be executed because autovacuum does not analyze foreign
tables.)


This is a good idea.

However, if adding these statistics requires an explicit ANALYZE
command, then we should also have a command for resetting the
collected statistics -- to get it back into the un-analyzed state.

Why would you want this? If the stats aren't up to date, run ANALYZE 
periodically. Remember that it is part of the DBA maintenance tasks [1].



[1] http://www.postgresql.org/docs/current/static/maintenance.html


--
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

--
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] unite recovery.conf and postgresql.conf

2011-09-16 Thread Euler Taveira de Oliveira

On 15-09-2011 23:54, Fujii Masao wrote:

#1
Use empty recovery.ready file to enter arhicve recovery. recovery.conf
is not read automatically. All recovery parameters are expected to be
specified in postgresql.conf. If you must specify them in recovery.conf,
you need to add include 'recovery.conf' into postgresql.conf. But note
that that recovery.conf will not be renamed to recovery.done at the
end of recovery. This is what the patch I've posted does. This is
simplest approach, but might confuse people who use the tools which
depend on recovery.conf.

more or less +1. We don't need two config files.; just one: postgresql.conf. 
Just turn all recovery.conf parameters to GUCs. As already said, the 
recovery.conf settings are not different from archive settings, we just need a 
way to trigger the recovery. And that trigger could be pulled by a GUC 
(standby_mode) or a file (say recovery - recovery.done). Also, recovery.done 
could be filled with recovery information just for DBA record. standby_mode 
does not create any file, it just trigger the recovery (as it will be used 
mainly for replication purposes).



--
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

--
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] Is there really no interest in SQL Standard?

2011-09-16 Thread Euler Taveira de Oliveira

On 16-09-2011 10:26, Susanne Ebrecht wrote:

On 16.09.2011 08:49, Heikki Linnakangas wrote:


Even if you can't share drafts, would it be possible to give a summary of
things that are being discussed in the committee? That way if there's people
in the community with interests in particular topics, they could contact you
and get involved.


Of course it is. I just not wanted to spam hackers.


But if it is community interest, of course it will bother no one here...


--
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

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


[HACKERS] typo

2011-09-07 Thread Euler Taveira de Oliveira

Hi,

While updating the translation I noticed a typo in
src/backend/commands/collationcmds.c circa line 126.

parameter \lc_collate\ parameter must be specified


--
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

--
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] Windows env returns error while running select pgstatindex

2011-08-24 Thread Euler Taveira de Oliveira

Em 24-08-2011 11:27, Tom Lane escreveu:

Hmm.  I agree we need to avoid executing 0/0 here, but should we force
the result to 0, or to NaN?


If it returns NaN on other platforms, let's be consistent.


--
  Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
  PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

--
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] Displaying accumulated autovacuum cost

2011-08-18 Thread Euler Taveira de Oliveira

Em 18-08-2011 03:39, Magnus Hagander escreveu:

Also, unrelated to that, wouldn't this information be interesting for
non-autovacuum queries as well?

Yes, it would. AFAICS, the patch will display that message in process titles. 
However, analyze code also uses the vacuum_delay_point(). How do you handle it?


It would be another patch... autovacuum has an option to display summarized 
information but vacuum don't. Isn't it time to be symmetrical here?



--
  Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
  PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

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


[HACKERS] vacuum rusage fix

2011-08-18 Thread Euler Taveira de Oliveira

Hi,

While looking at Greg's patch I spotted that resource usage code for vacuum is 
initialized even if we won't use it. Attached is a small patch that moves it 
to the right place (this code mimics do_analyze_rel function).



--
  Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
  PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c
index c5bf32e..b5547c5 100644
*** a/src/backend/commands/vacuumlazy.c
--- b/src/backend/commands/vacuumlazy.c
*** lazy_vacuum_rel(Relation onerel, VacuumS
*** 155,165 
  	bool		scan_all;
  	TransactionId freezeTableLimit;
  
- 	pg_rusage_init(ru0);
- 
  	/* measure elapsed time iff autovacuum logging requires it */
! 	if (IsAutoVacuumWorkerProcess()  Log_autovacuum_min_duration  0)
! 		starttime = GetCurrentTimestamp();
  
  	if (vacstmt-options  VACOPT_VERBOSE)
  		elevel = INFO;
--- 155,167 
  	bool		scan_all;
  	TransactionId freezeTableLimit;
  
  	/* measure elapsed time iff autovacuum logging requires it */
! 	if (IsAutoVacuumWorkerProcess()  Log_autovacuum_min_duration = 0)
! 	{
! 		pg_rusage_init(ru0);
! 		if (Log_autovacuum_min_duration  0)
! 			starttime = GetCurrentTimestamp();
! 	}
  
  	if (vacstmt-options  VACOPT_VERBOSE)
  		elevel = INFO;

-- 
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] Displaying accumulated autovacuum cost

2011-08-18 Thread Euler Taveira de Oliveira

Em 18-08-2011 12:54, Greg Smith escreveu:

I was hoping to eventually take the useful summary bits at the end, the
totals, and save those into statistics somewhere each time a VACUUM of
either sort finishes. It would fit with the information shown in
pg_stat_tables, but that's obviously getting too wide. Breaking out a
pg_stat_autovacuum view that contains all the relevant bits currently
shown in that view, plus these 3 new fields, would be a reasonable start.

IMHO the useful summary bits belongs to log. If you want to add it to stats 
collector go for it. But if you go to the latter road, it is recommended to 
move some fields (time-related fields) from pg_stat_*_tables to this new view 
(pg_stat_maintenance?). I don't know how generic you want to go but have in 
mind I would like to cover automatic and manual maintenance commands.


Besides that another view will cover the maintenance activity. This new view 
could contain at least datname, schemaname, relname, command_start, 
command_schedule, operation, progress (?), procpid, and current_command. The 
name has to be generic to cover all maintenance commands (perhaps 
pg_maintenance_activity).



--
  Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
  PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

--
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] Displaying accumulated autovacuum cost

2011-08-17 Thread Euler Taveira de Oliveira

Em 17-08-2011 18:04, Greg Smith escreveu:

Attached is a patch that tracks and displays the accumulated cost when
autovacuum is running. Code by Noah Misch and myself. I hope this idea
will bring a formal process to vacuum tuning, which is currently too
hard to do. I was about to add without... to that, but I then realized
it needs no disclaimer; it's just too hard, period. Vacuum issues are
enemy #1 at all the terabyte scale customer sites I've been fighting
with lately.

Interesting patch. I drafted a similar idea but didn't have a chance to 
publish it. It is a complement to the idea about autovacuum tuning [1]. Hope I 
will have time to post something for the next CF. And, of course, I will 
review this patch.



The patch updates the command string just before the workers sleep to
show how much work they've done so far. And at the end, it adds a few
new lines to the information written to the logs, when the autovacuum is
notable enough to be logged at all. The overhead it adds is at most a
few integer operations per buffer processed and a slower title string
update once per sleep. It's trivial compared to both the vacuum itself,
and to the instrumentation's value to sites with vacuum issues.

I don't like exposing this information only on title processes. It would be 
difficult for client apps (for example, PGAdmin) to track this kind of 
information and it is restricted to local access. I'm not objecting to display 
this information in process title; I'm just saying that that information 
should be exposed in  functions (say pg_stat_get_vacuum_[hit|miss|dirty]) too. 
I'm not sure about adding this information to incremental counters but that 
would be useful to trace a vacuum work pattern.



[1] http://archives.postgresql.org/pgsql-hackers/2011-06/msg00678.php


--
  Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
  PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

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


[HACKERS] tuning autovacuum

2011-06-08 Thread Euler Taveira de Oliveira

Hi,

There are some releases that autovacuum was enabled by default and, up to now 
there is an easy way to estimate the number of autovacuum workers. I tune it 
observing if the number of slots are saturated for a period of time. I'm 
having a hard time trying to do this. I want to add a LOG message such as


LOG: maximum number of autovacuum workers reached
HINT: Consider increasing autovacuum_max_workers (currently 5).

And also a view (say pg_stat_autovacuum) to expose some autovacuum information 
such as (i) number of autovacuum workers (ii) number of tables that needs 
analyze/vacuum and are scheduled to (iii) number of autovacuum count (iv) 
number of autoanalyze count. While I am in this topic, it would be nice to 
expose the analyze/vacuum count and threshold per table. This information 
should go to pg_stat_*_tables but it already has too much fields. Maybe it is 
time to split autovacuum information into another statistic view?


Comments?


--
  Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
  PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

--
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] tuning autovacuum

2011-06-08 Thread Euler Taveira de Oliveira

Em 08-06-2011 20:35, Robert Haas escreveu:

Is the hint correct?  I mean, what if there were 100 small tables that
needed vacuuming all at the same time.  We'd hit this limit no matter
how high you set autovacuum_max_workers, but it wouldn't be right to
set it to 101 just because every once in a blue moon you might trip
over the limit.

I think so. You are picturing a scene with only one message. It is the same 
case of the too-frequent-checkpoint messages; i.e., you should look if those 
messages have some periodicity.



I think it'd be really useful to expose some more data in this area
though.  One random idea is - remember the time at which a table was
first observed to need vacuuming. Clear the timestamp when it gets
vacuumed.  Then you can do:

Hmmm. But this fine grained information alone doesn't help tuning the number 
of autovacuum workers. I consider counters easier to implement and simpler to 
analyze. But the timestamp idea has its merit because we already have a 
similar statistic (last timestamp table was vacuumed or analyzed).



--
  Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
  PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

--
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] Why not install pgstattuple by default?

2011-05-07 Thread Euler Taveira de Oliveira

Em 07-05-2011 13:42, Peter Eisentraut escreveu:

Do you need pg_config to install extensions?


No. But we need it to build other extensions.


--
  Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
  PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

--
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] Why not install pgstattuple by default?

2011-05-06 Thread Euler Taveira de Oliveira

Em 06-05-2011 05:06, Magnus Hagander escreveu:

On Fri, May 6, 2011 at 00:34, Josh Berkusjosh.ber...@pgexperts.com  wrote:

Hackers,

I've run into a couple of occasions lately where I really wanted
pgstattuple on a production server in order to check table/index bloat.
  However, in the production environment at a large site installing a
contrib module can involve a process which takes days or weeks.



I already faced that problem too.


 From 9.1, it'll be a simple CREATE EXTENSION command - so much of the
problem goes away. Well. It doesn't go away, but it gets a lot more
neatly swept under the rug.

That's half of the history. Admin needs to install postgresql-contrib package. 
Sometimes it takes too much time to convince clients that some additional 
supplied modules are useful for them.


Now that we have extensions, why not build and package the contrib modules by 
default? 'make world' is not the answer. There is not an option for install 
all pieces of software. Let's install pg+contrib and leave only 'CREATE 
EXTENSION foo' for the admins.



--
  Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
  PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

--
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] Debug contrib/cube code

2011-05-06 Thread Euler Taveira de Oliveira

Em 06-05-2011 02:14, Nick Raj escreveu:

I am using postgresql-8.4.6. I want to debug the contrib/cube code. Can
we able to debug that cube code?  Because there is no .configure  file
to enable debug. Is there is any way to change make file to enable debug?

What do you want to debug? AFAICS you need to change the code to achieve what 
you want.



--
  Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
  PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

--
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] Why not install pgstattuple by default?

2011-05-06 Thread Euler Taveira de Oliveira

Em 06-05-2011 14:55, Christopher Browne escreveu:

The improvement
would come from drawing contrib a bit closer to core, and encouraging
packagers (dpkg, rpm, ports) to fold contrib into base rather than
separating it.  I'm sure that would get some pushback, though.


I'm in favor of find out what are the popular extensions and make them into 
base; the other ones could be moved to PGXN.



--
  Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
  PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

--
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] Comments on system tables and columns

2011-03-28 Thread Euler Taveira de Oliveira

Em 28-03-2011 08:14, Thom Brown escreveu:

I notice that none of the system tables or columns thereof bear any
comments.  Is this intentional, or an oversight?  I would have thought
comments would be useful since the column names aren't exactly always
self-explanatory.

It could be useful in some cases. IIRC the comments are not there to avoid 
bloating the catalog. One month ago or so I saw a commit to comment operator 
support functions. Maybe it is worth comment system catalog too [1].



[1] http://eulerto.blogspot.com/2010/11/comment-on-catalog-tables.html


--
  Euler Taveira de Oliveira
  http://www.timbira.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] Open issues for collations

2011-03-28 Thread Euler Taveira de Oliveira

Em 28-03-2011 22:27, Alvaro Herrera escreveu:

Excerpts from Tom Lane's message of lun mar 28 21:02:40 -0300 2011:

I tried.  The upper/lower test cases require Turkish characters that
aren't in Latin1.  I'm not sure if we can readily produce test cases
that cover both sorting changes and case-folding changes in just one
single-byte encoding --- anybody?


ISO-8859-9?

I'm afraid we have to map lang to single byte character set. Not all languages 
prefer ISO-8859.



--
  Euler Taveira de Oliveira
  http://www.timbira.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] When and where do PG invoke PLs module?

2011-03-22 Thread Euler Taveira de Oliveira

Em 21-03-2011 06:26, _ʯͷ escreveu:

I've tried to find when and where do PG invoke PLs module,but
failed.There are four procedures for a query string--parer, rewrite,plan
and execute. I want to know which part invoke the PLs module,and which
function is the entry to do that.

Look at src/pl/foo and search for foo_call_handler function (it is the entry 
point).



--
  Euler Taveira de Oliveira
  http://www.timbira.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] Why our counters need to be time-based WAS: WIP: cross column correlation ...

2011-02-28 Thread Euler Taveira de Oliveira

Em 28-02-2011 15:50, Tom Lane escreveu:

Ultimately we need to think of a reporting mechanism that's a bit
smarter than rewrite the whole file for any update ...


What about splitting statistic file per database?


--
  Euler Taveira de Oliveira
  http://www.timbira.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] Named restore points

2011-02-24 Thread Euler Taveira de Oliveira

Em 08-02-2011 17:35, Thom Brown escreveu:

This could do with a bit more documentation about usage.  Below the
Backup Control Functions table
(http://developer.postgresql.org/pgdocs/postgres/functions-admin.html#FUNCTIONS-ADMIN-BACKUP-TABLE),
each function has a paragraph detailing what it does.


I forgot to check it.


Also, I notice you can easily write over a label.  The case I'm
thinking of is someone in psql creating a named restore point, then
later on, they go in again, accidentally cursor up and select the
previous statement and create it again.  Would this mean that the
previous label is lost, or would it be the case that any subsequent
duplicate labels would have no effect unless the WAL files with the
original label in were consumed?  In either case, a note in the docs
about this would be useful.

This is a limitation that I pointed out [1] but people decided to postpone 
named restore point management. The first one is used as restore point. I 
added it in the attached patch.



And I don't see these label creations getting logged either.  Could we
output that to the log because at least then users can grep the
directory for labels, and, in most cases, the time they occurred?

Good point. I included location instead of time; time is already supplied by 
log file.


The following patch implements the Thom's suggestions.


[1] http://archives.postgresql.org/message-id/4d48209c.7050...@timbira.com


--
  Euler Taveira de Oliveira
  http://www.timbira.com/
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 736eb67..fe7e42b 100644
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*** postgres=# select pg_start_backup('label
*** 14070,14075 
--- 14070,14084 
 /para
  
 para
+functionpg_create_restore_point/ creates a named transaction log record
+that can be used as recovery point, and then returns the transaction log
+record location. The given name can be used in xref
+linkend=recovery-target-name that specifies the point up to which recovery
+will proceed. Avoid creating restore points that have the same name, recovery
+stops at the first one.
+/para
+ 
+para
  functionpg_current_xlog_location/ displays the current transaction log write
  location in the same format used by the above functions.  Similarly,
  functionpg_current_xlog_insert_location/ displays the current transaction log
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 3ba1f29..b4eb4ac 100644
*** a/src/backend/access/transam/xlog.c
--- b/src/backend/access/transam/xlog.c
*** XLogRestorePoint(const char *rpName)
*** 8144,8149 
--- 8144,8153 
  
  	RecPtr = XLogInsert(RM_XLOG_ID, XLOG_RESTORE_POINT, rdata);
  
+ 	ereport(LOG,
+ 			(errmsg(restore point \%s\ created at %X/%X,
+ 	rpName,	RecPtr.xlogid, RecPtr.xrecoff)));
+ 
  	return RecPtr;
  }
  

-- 
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] Named restore points

2011-02-08 Thread Euler Taveira de Oliveira

Em 08-02-2011 11:05, Simon Riggs escreveu:

On Fri, 2011-02-04 at 21:15 -0500, Jaime Casanova wrote:


+   else if (recoveryTarget == RECOVERY_TARGET_NAME)
+   snprintf(buffer, sizeof(buffer),
+%s%u\t%s\t%s named restore point %

s\n,

+(srcfd  0) ?  : \n,
+parentTLI,
+xlogfname,
+recoveryStopAfter ? after :

before,

+recoveryStopNamedRestorePoint);

It doesn't matter if it is after or before the restore point.

After/Before

only make sense when we're dealing with transaction or time.

Removed.




you're right


Not sure I understand the comment only make sense when we're dealing
with transaction or time.  Why?

Because named restore point is a noop xlog record; besides, transaction and 
time involves xlog records that contain data.



--
  Euler Taveira de Oliveira
  http://www.timbira.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] Allow pg_archivecleanup to ignore extensions

2011-02-08 Thread Euler Taveira de Oliveira

Em 08-02-2011 04:57, Greg Smith escreveu:

We recenty got some on-list griping that pg_standby doesn't handle
archive files that are compressed, either. Given how the job I'm working
on this week is going, I'll probably have to add that feature next.
That's actually an easier source code hack than this one, because of how
the pg_standby code modularizes the concept of a restore command.

This was already proposed a few years ago [1]. I have used a modified 
pg_standby with this feature for a year or so.



[1] 
http://archives.postgresql.org/message-id/e4ccc24e0810222010p12bae2f4xa3a11cb2bc51bd89%40mail.gmail.com



--
  Euler Taveira de Oliveira
  http://www.timbira.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] 64-bit pgbench V2

2011-02-06 Thread Euler Taveira de Oliveira

Em 06-02-2011 13:09, Bruce Momjian escreveu:


What happened to this idea/patch?


I refactored the patch [1] to not depend on strtoll.


[1] http://archives.postgresql.org/message-id/4d2cccd9@timbira.com


--
  Euler Taveira de Oliveira
  http://www.timbira.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] Named restore points

2011-02-01 Thread Euler Taveira de Oliveira

Em 14-01-2011 17:41, Jaime Casanova escreveu:

Here is a patch that implements named restore points.


Sorry, I was swamped with work. :(

Your patch no longer applied so I rebased it and slightly modified it. Review 
is below...


+ The default is to recover to the end of the WAL log.
+ The precise stopping point is also influenced by
+ xref linkend=recovery-target-inclusive.
+/para

This isn't valid. recovery_target_name are not influenced by 
recovery_target_inclusive. Sentence removed.


+ static char recoveryStopNamedRestorePoint[MAXFNAMELEN];

Is MAXFNAMELEN appropriate? AFAICS it is used for file name length. [Looking 
at code...] It seems to be used for backup label too so it is not so 
inappropriate.


+ typedef struct xl_named_restore_points
+ {
+   TimestampTz xtime;
+   charname[MAXFNAMELEN];
+ } xl_named_restore_points;
+

I prefixed those struct members so it won't get confused elsewhere.

+   else if (recoveryTarget == RECOVERY_TARGET_NAME)
+   snprintf(buffer, sizeof(buffer),
+%s%u\t%s\t%s named restore point %s\n,
+(srcfd  0) ?  : \n,
+parentTLI,
+xlogfname,
+recoveryStopAfter ? after : before,
+recoveryStopNamedRestorePoint);

It doesn't matter if it is after or before the restore point. After/Before 
only make sense when we're dealing with transaction or time. Removed.


else if (strcmp(item-name, recovery_target_xid) == 0)
{
+   /*
+* if recovery_target_name specified, then this 
overrides
+* recovery_target_xid
+*/
+   if (recoveryTarget == RECOVERY_TARGET_NAME)
+   continue;
+

IMHO the right recovery precedence is xid - name - time. If you're 
specifying xid that's because you know what you are doing. Name takes 
precedence over time because it is easier to remember a name than a time. I 
implemented this order in the updated patch.


+   recoveryTargetName = pstrdup(item-value);

I also added a check for long names.

+   if ((record-xl_rmid == RM_XLOG_ID)  (record_info == 
XLOG_RESTORE_POINT))
+   couldStop = true;
+
+   if (!couldStop)
+   return false;
+

I reworked this code path because it seems confusing.

+   recordNamedRestorePoint = (xl_named_restore_points *) 
XLogRecGetData(record);
+   recordXtime = recordNamedRestorePoint-xtime;

Why don't you store the named restore point here too? You will need it a few 
lines below.


+   char name[MAXFNAMELEN];
+
+   memcpy(xlrec, rec, sizeof(xl_named_restore_points));
+   strncpy(name, xlrec.name, MAXFNAMELEN);

Is it really necessary? I removed it.

+ Datum
+ pg_create_restore_point(PG_FUNCTION_ARGS)
+ {

You should have added a check for long restore point names. Added in the 
updated patch.


+ ereport(NOTICE,
+  (errmsg(WAL archiving is not enabled; you must ensure that 
WAL segments are copied through other means for restore points to be usefull 
for you)));

+

Sentence was rewritten as WAL archiving is not enabled; you must ensure that 
WAL segments are copied through other means to recover up to named restore point.


Finally, this is a nice feature iif we have a way to know what named restore 
points are available. DBAs need to take note of this list (that is not good) 
and the lazy ones will have a hard time to recover the right name (possibly 
with a xlog dump tool).


So how could we store this information? Perhaps a file in 
$PGDATA/pg_xlog/restore_label that contains the label (and possibly the WAL 
location). Also it must have a way to transmit the restore_label when we add 
another restore point. I didn't implement this part (Jaime?) and it seems as 
important as the new xlog record type that is in the patch. It seems 
complicate but I don't have ideas. Anyone? The restore point names could be 
obtained by querying a function (say, pg_restore_point_names or 
pg_restore_point_list).


Someone could argue that this feature could be reached if we store label and 
WAL location in a file (say restore_label). This mechanism doesn't need a new 
WAL record but the downside is that if we lost restore_label we are dead. I'm 
not in favor of this approach because it seems too fragile.


I will mark this patch waiting on author because of those open issues.

This patch needs to bump catalog version because of the new function. I'm not 
sure if the new record type requires bumping the xlog magic number.


I'm attaching the updated patch and two scripts that I used to play with the 
patch.



--
  Euler Taveira de Oliveira
  http://www.timbira.com/


a.sh
Description

Re: [HACKERS] pg_dump directory archive format / parallel pg_dump

2011-01-21 Thread Euler Taveira de Oliveira

Em 21-01-2011 12:47, Andrew Dunstan escreveu:

Maybe we could change the hint to say --file=DESTINATION or
--file=FILENAME|DIRNAME ?


... --file=OUTPUT or --file=OUTPUTNAME.


--
  Euler Taveira de Oliveira
  http://www.timbira.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] reviewers needed!

2011-01-16 Thread Euler Taveira de Oliveira

Em 16-01-2011 16:30, Andy Colson escreveu:

I reviewed a couple patched, and I added my review to the commitfest page.

If I find a problem, its obvious I should mark the patch as returned
with feedback.

But what if I'm happy with it? I'm not a hacker so cannot do C code
review, should I leave it alone? Mark it as ready for committer?

Did you take a look at [1]? If your patch involves C code and you're not C 
proficient then there must be another reviewer to give his/her opinion (of 
course, the other person could be the committer). I wouldn't mark it ready 
for committer instead leave it as is (needs review); just be sure to add 
your comments in the commitfest app.



[1] http://wiki.postgresql.org/wiki/RRReviewers


--
  Euler Taveira de Oliveira
  http://www.timbira.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] pg_basebackup for streaming base backups

2011-01-15 Thread Euler Taveira de Oliveira

Em 15-01-2011 15:10, Magnus Hagander escreveu:

One thing I'm thinking about - right now the tool just takes -c
conninfo  to connect to the database. Should it instead be taught to
take the connection parameters that for example pg_dump does - one for
each of host, port, user, password? (shouldn't be hard to do..)


+1.


--
  Euler Taveira de Oliveira
  http://www.timbira.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] Named restore points

2011-01-14 Thread Euler Taveira de Oliveira

Em 14-01-2011 17:41, Jaime Casanova escreveu:

Here is a patch that implements named restore points.

Nice feature. I only read the provided documentation and it seems inconsistent 
to allow name, time, and xid at recovery_target_name because (i) someone could 
name the recovery point as '1234567' (xid) or '2011-01-14' (I use this format 
a lot) and (ii) if the suffix name is *_name* it shouldn't allow xid and time. 
IMHO, recovery_target_name should allow only names.



--
  Euler Taveira de Oliveira
  http://www.timbira.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] Per-column collation, the finale

2011-01-14 Thread Euler Taveira de Oliveira

Em 14-01-2011 20:47, Robert Haas escreveu:

On Fri, Jan 14, 2011 at 4:41 PM, Peter Eisentrautpete...@gmx.net  wrote:

I've been going over this patch with a fine-tooth comb for the last two
weeks, fixed some small bugs, added comments, made initdb a little
friendlier, but no substantial changes.

I'm going to start working on SQL-level CREATE/DROP/ALTER COLLATION
support and associated things now.


Maybe I'm all wet here, but isn't it time to commit what you've got
and punt the things that aren't done to 9.2?

I think Peter want another person to take a look at his patch. I personally 
would like to eyeball his patch (but it will be during the week).



--
  Euler Taveira de Oliveira
  http://www.timbira.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] Named restore points

2011-01-14 Thread Euler Taveira de Oliveira

Em 14-01-2011 19:50, Jaime Casanova escreveu:

On Fri, Jan 14, 2011 at 5:42 PM, Euler Taveira de Oliveira
eu...@timbira.com  wrote:

Em 14-01-2011 17:41, Jaime Casanova escreveu:


Here is a patch that implements named restore points.


Nice feature. I only read the provided documentation and it seems
inconsistent to allow name, time, and xid at recovery_target_name


it only allow names, but those names could be anything


OK. I will review your patch at the beginning of the week.


--
  Euler Taveira de Oliveira
  http://www.timbira.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] [PERFORM] pgbench to the MAXINT

2011-01-11 Thread Euler Taveira de Oliveira

Em 10-01-2011 05:25, Greg Smith escreveu:

Euler Taveira de Oliveira wrote:

Em 07-01-2011 22:59, Greg Smith escreveu:

setrandom: invalid maximum number -2147467296


It is failing at atoi() circa pgbench.c:1036. But it just the first
one. There are some variables and constants that need to be converted
to int64 and some functions that must speak 64-bit such as getrand().
Are you working on a patch?


http://archives.postgresql.org/pgsql-hackers/2010-01/msg02868.php
http://archives.postgresql.org/message-id/4c326f46.4050...@2ndquadrant.com

Greg, I just improved your patch. I tried to work around the problems pointed 
out in the above threads. Also, I want to raise some points:


(i) If we want to support and scale factor greater than 21474 we have to 
convert some columns to bigint; it will change the test. From the portability 
point it is a pity but as we have never supported it I'm not too worried about 
it. Why? Because it will use bigint columns only if the scale factor is 
greater than 21474. Is it a problem? I don't think so because generally people 
compare tests with the same scale factor.


(ii) From the performance perspective, we need to test if the modifications 
don't impact performance. I don't create another code path for 64-bit 
modifications (it is too ugly) and I'm afraid some modifications affect the 
32-bit performance. I'm in a position to test it though because I don't have a 
big machine ATM. Greg, could you lead these tests?


(iii) I decided to copy scanint8() (called strtoint64 there) from backend 
(Robert suggestion [1]) because Tom pointed out that strtoll() has portability 
issues. I replaced atoi() with strtoint64() but didn't do any performance tests.


Comments?


[1] http://archives.postgresql.org/pgsql-hackers/2010-07/msg00173.php


--
  Euler Taveira de Oliveira
  http://www.timbira.com/
diff --git a/contrib/pgbench/pgbench.c b/contrib/pgbench/pgbench.c
index 7c2ca6e..e9eb720 100644
*** a/contrib/pgbench/pgbench.c
--- b/contrib/pgbench/pgbench.c
***
*** 60,65 
--- 60,67 
  #define INT64_MAX	INT64CONST(0x7FFF)
  #endif
  
+ #define MAX_RANDOM_VALUE64	INT64_MAX
+ 
  /*
   * Multi-platform pthread implementations
   */
*** usage(const char *progname)
*** 364,378 
  		   progname, progname);
  }
  
  /* random number generator: uniform distribution from min to max inclusive */
! static int
! getrand(int min, int max)
  {
  	/*
  	 * Odd coding is so that min and max have approximately the same chance of
  	 * being selected as do numbers between them.
  	 */
! 	return min + (int) (((max - min + 1) * (double) random()) / (MAX_RANDOM_VALUE + 1.0));
  }
  
  /* call PQexec() and exit() on failure */
--- 366,451 
  		   progname, progname);
  }
  
+ /*
+  * strtoint64 -- convert a string to 64-bit integer
+  *
+  * this function is a modified version of scanint8() from
+  * src/backend/utils/adt/int8.c.
+  *
+  * XXX should it have a return value?
+  *
+  */
+ static int64
+ strtoint64(const char *str)
+ {
+ 	const char *ptr = str;
+ 	int64		result = 0;
+ 	int			sign = 1;
+ 
+ 	/*
+ 	 * Do our own scan, rather than relying on sscanf which might be broken
+ 	 * for long long.
+ 	 */
+ 
+ 	/* skip leading spaces */
+ 	while (*ptr  isspace((unsigned char) *ptr))
+ 		ptr++;
+ 
+ 	/* handle sign */
+ 	if (*ptr == '-')
+ 	{
+ 		ptr++;
+ 
+ 		/*
+ 		 * Do an explicit check for INT64_MIN.	Ugly though this is, it's
+ 		 * cleaner than trying to get the loop below to handle it portably.
+ 		 */
+ 		if (strncmp(ptr, 9223372036854775808, 19) == 0)
+ 		{
+ 			result = -INT64CONST(0x7fff) - 1;
+ 			ptr += 19;
+ 			goto gotdigits;
+ 		}
+ 		sign = -1;
+ 	}
+ 	else if (*ptr == '+')
+ 		ptr++;
+ 
+ 	/* require at least one digit */
+ 	if (!isdigit((unsigned char) *ptr))
+ 		fprintf(stderr, invalid input syntax for integer: \%s\\n, str);
+ 
+ 	/* process digits */
+ 	while (*ptr  isdigit((unsigned char) *ptr))
+ 	{
+ 		int64		tmp = result * 10 + (*ptr++ - '0');
+ 
+ 		if ((tmp / 10) != result)		/* overflow? */
+ 			fprintf(stderr, value \%s\ is out of range for type bigint\n, str);
+ 		result = tmp;
+ 	}
+ 
+ gotdigits:
+ 
+ 	/* allow trailing whitespace, but not other trailing chars */
+ 	while (*ptr != '\0'  isspace((unsigned char) *ptr))
+ 		ptr++;
+ 
+ 	if (*ptr != '\0')
+ 		fprintf(stderr, invalid input syntax for integer: \%s\\n, str);
+ 
+ 	return ((sign  0) ? -result : result);
+ }
+ 
  /* random number generator: uniform distribution from min to max inclusive */
! static int64
! getrand(int64 min, int64 max)
  {
  	/*
  	 * Odd coding is so that min and max have approximately the same chance of
  	 * being selected as do numbers between them.
  	 */
! 	return min + (int64) (((max - min + 1) * (double) random()) / (MAX_RANDOM_VALUE64 + 1.0));
  }
  
  /* call PQexec() and exit() on failure */
*** top:
*** 887,893 
  		if (commands[st-state] == NULL)
  		{
  			st-state = 0;
! 			st-use_file = getrand(0

Re: [HACKERS] system views for walsender activity

2011-01-10 Thread Euler Taveira de Oliveira

Em 10-01-2011 12:05, Heikki Linnakangas escreveu:

So how does a walsender that's waiting for a command from the client
show up? Surely it's not in catchup mode yet?

It is kind of initializing catchup. I think it is not worth representing 
those short lifespan states (in normal scenarios).



--
  Euler Taveira de Oliveira
  http://www.timbira.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] Fixing GIN for empty/null/full-scan cases

2011-01-06 Thread Euler Taveira de Oliveira

Em 06-01-2011 21:31, Tom Lane escreveu:

I think I like option #2 better.  Comments?


+1.


--
  Euler Taveira de Oliveira
  http://www.timbira.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] pg_streamrecv for 9.1?

2010-12-29 Thread Euler Taveira de Oliveira

Em 29-12-2010 07:47, Magnus Hagander escreveu:

Would people be interested in putting pg_streamrecv
(http://github.com/mhagander/pg_streamrecv) in bin/ or contrib/ for
9.1? I think it would make sense to do so.


+1 but...


It could/should then also become the default tool for doing
base-backup-over-libpq, assuming me or Heikki (or somebody else)
finishes off the patch for that before 9.1.

I think that the base backup feature is more important than simple streaming 
chunks of the WAL (SR already does this). Talking about the base backup over 
libpq, it is something we should implement to fulfill people's desire that 
claim an easy replication setup.


IIRC, Dimitri already coded a base backup over libpq tool [1] but it is 
written in Python.



[1] https://github.com/dimitri/pg_basebackup/


--
  Euler Taveira de Oliveira
  http://www.timbira.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] To Signal The postmaster

2010-12-07 Thread Euler Taveira de Oliveira
aaliya zarrin escreveu:
 I want to signal the postmaster (User defined signal) once I created the
 trigger file (for switch over).
  
Send a SIGHUP to postmaster. Have in mind that it doesn't work on all
supported platforms.


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] Hi- How frequently Postgres Poll for trigger file

2010-12-01 Thread Euler Taveira de Oliveira
Heikki Linnakangas escreveu:
 On 01.12.2010 13:27, aaliya zarrin wrote:
 I want to know how frequently postgres search for trigger file to switch
 over.
 
 In 9.0, every 100ms while streaming replication is active and connected.
 5 seconds otherwise. In current git master branch, it's always 5 s.
 
 Can this switch over time be reduced?
 
 Not without hacking the sources and compiling.
 
 Although, on many platforms, Linux included I believe, sending a signal
 to the startup process should wake it up from the sleep and make it
 check the trigger file immediately. pg_ctl reload for example should
 do it. So if ýou send a signal to the startup process immediately after
 creating the trigger file, it should take notice sooner.
 
Isn't it an ugly solution for stopping the replication immediately? At the top
of my head, I don't remember the reason for not turn the interval for pooling
trigger file into a configurable option. IMHO, high availability fits into
those cases (switchover immediately).

[poking the git history a little...]

I agree the a short polling interval is not energy efficient [1] but some
scenarios need this short interval. So if we want to stop the replication
immediately, we have two options: (i) advertise that we need to signal the
postmaster after creating a trigger file or (ii) made the pooling interval
configurable. As you said, there are platforms that a signal doesn't wake up a
process, so I suggest (ii) but I'm fine to include (i) at docs too.

Comments?


[1]
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=723d0184e2972f21db0f85feef3d35f0cb9b3298


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] pg_ctl init doc bug

2010-11-09 Thread Euler Taveira de Oliveira
Hi,

While executing the following command I got:

$ pg_ctl init -D /tmp/foo -o -N 11
/home/euler/pg/bin/initdb: invalid option -- N
Try initdb --help for more information.
pg_ctl: database system initialization failed

I tried -N 11 (postgres option) after looking at the manual but the -o
option only accept initdb options so I think there is a documentation bug.
Patch is attached.

While at it, is it worth adding a pg_ctl init example?


-- 
  Euler Taveira de Oliveira
  http://www.timbira.com/
diff --git a/doc/src/sgml/ref/pg_ctl-ref.sgml b/doc/src/sgml/ref/pg_ctl-ref.sgml
index 29bea24..0cc82cd 100644
*** a/doc/src/sgml/ref/pg_ctl-ref.sgml
--- b/doc/src/sgml/ref/pg_ctl-ref.sgml
*** PostgreSQL documentation
*** 25,31 
 arg choice=plaininit[db]/arg
 arg-s/arg
 arg-D replaceabledatadir/replaceable/arg
!arg-o replaceableoptions/replaceable/arg
/cmdsynopsis
  
cmdsynopsis
--- 25,31 
 arg choice=plaininit[db]/arg
 arg-s/arg
 arg-D replaceabledatadir/replaceable/arg
!arg-o replaceableinit-options/replaceable/arg
/cmdsynopsis
  
cmdsynopsis
*** PostgreSQL documentation
*** 263,268 
--- 263,282 
   /varlistentry
  
   varlistentry
+   termoption-o replaceable class=parameterinit-options/replaceable/option/term
+   listitem
+para
+ Specifies options to be passed directly to the
+ commandinitdb/command command.
+/para
+para
+ The options are usually surrounded by single or double
+ quotes to ensure that they are passed through as a group.
+/para
+   /listitem
+  /varlistentry
+ 
+  varlistentry
termoption-m replaceable class=parametermode/replaceable/option/term
listitem
 para

-- 
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] create custom collation from case insensitive portuguese

2010-11-02 Thread Euler Taveira de Oliveira
Alexandre Riveira escreveu:
 I've achieved some success in changing collate operating system (linux)
 to generate sort of way of Brazil Portuguese hopes by adding the
 following code in LC_COLLATE
 
This was already discussed; search the archives [1] [2].

 So far, I understood the mechanism of change collate and reproduce in
 postgresql, and I could not generate a case-insensitive search, I
 believe that would change within the LC_COLLATE variable, but could not
 go any further than that.
 
PostgreSQL doesn't support case-insensitive searches specifying the collate
per column yet. Look at [3]. But you could use ILIKE or regular expression to
achieve  that.


[1] http://pgfoundry.org/pipermail/brasil-usuarios/20060330/001667.html
[2] http://www.mail-archive.com/brasil-usuar...@pgfoundry.org/msg00895.html
[3] http://archives.postgresql.org/pgsql-hackers/2010-07/msg00512.php


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] external pid file

2010-09-29 Thread Euler Taveira de Oliveira
Hi,

Is there any reason the postmaster.pid and external_pid_file contents to be
different?


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] Help with User-defined function in PostgreSQL with Visual C++

2010-09-28 Thread Euler Taveira de Oliveira
Magnus Hagander escreveu:
 We might, however, want to add a specific section to the
 *documentation* about building extensions on Windows.
 
+1.


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] Help with User-defined function in PostgreSQL with Visual C++

2010-09-27 Thread Euler Taveira de Oliveira
Itagaki Takahiro escreveu:
 I had the same problems before, and I wrote some hacks for VC++.
 
Isn't there such a code in core or am i missing something? Is it worth
supporting the VC++ standalone projects?


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] SQLSTATE of notice PGresult

2010-08-25 Thread Euler Taveira de Oliveira
Tom Lane escreveu:
 You didn't actually read what I said, did you?  That patch will have
 precisely zero effect on the OP's example.
 
Oh, I see your point. Didn't pay attention at the OP's example. I was only
worried about the successful queries that doesn't return SQLSTATE but as you
point out, that part of the code deserves a refactoring to cover OP's case too.


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] SQLSTATE of notice PGresult

2010-08-24 Thread Euler Taveira de Oliveira
Robert Haas escreveu:
 On Fri, Aug 20, 2010 at 11:05 AM, Euler Taveira de Oliveira
 eu...@timbira.com wrote:
 Dmitriy Igrishin escreveu:
   /* NOT presents - NULL. Why not 0 ? */
   const char* sqlstate = Pg::PQresultErrorField(res, PG_DIAG_SQLSTATE);

 That's because the protocol doesn't set error field when the command
 succeeded. IMHO it's an oversight (the documentation is correct but the code
 is not) and should be correct because the spec enforces it.
 
 Seems like a waste of bytes.
 
Ugh? It is a matter of correctness. I'm not arguing in favor of it but if we
don't implement it, it is better document it. I don't actually rely on sql
state to check errors but can have applications out there that expect the spec
behavior but we don't provide it and, also fail to document it. Talking about
the patch, it is just pqSaveMessageField() calls in *Complete messages. I can
provide a patch for it.


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] SQLSTATE of notice PGresult

2010-08-24 Thread Euler Taveira de Oliveira
Robert Haas escreveu:
 It appears to me that it already is documented.  The very first
 sentence of the documentation reads:
 
 Returns an individual field of an error report.
 
 And a few sentences later it says:
 
 NULL is returned if the PGresult is not an error or warning result
 
I'm referring to [1].

 I suppose we could change the function to return 0 always when the
 operation is not an error or warning report, rather than NULL, but
 certainly we wouldn't want to include those bytes in *every* success
 message, so they'd have to be something that the libpq inferred.  And
 I'm not clear why that behavior would be any more useful than what we
 have now; indeed, it seems like it would needlessly break backward
 compatibility.  If you're arguing that this behavior is required by
 the spec, let's have a cite.  I find it a bit surprising that the spec
 would cover the behavior of individual libpq functions in this level
 of detail.
 
It seems we can't infer the success message from libpq; it is necessary to
build the sql state message field. As I said both behaviors have the same goal
(in this case, NULL means success, i.e. sqlstate is not assigned) but it
doesn't match the spec.


[1] http://www.postgresql.org/docs/9.0/static/errcodes-appendix.html


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] SQLSTATE of notice PGresult

2010-08-24 Thread Euler Taveira de Oliveira
Tom Lane escreveu:
 The real issue
 here is that there are no SQLSTATEs assigned for any error/warning
 conditions generated internally in libpq.
 
Did you mean successful conditions? Only warning/error conditions produce a
SQLSTATE.

 As far as this particular example goes, I think it's highly debatable
 whether out of range parameter number should be only a NOTICE, and
 almost certainly wrong to say that it ought to be associated with an
 0 SQLSTATE.  But figuring out what it ought to be is part of the
 dogwork that nobody's done yet.
 
It should match the actual PostgreSQL behavior. There are two classes (01xxx
and 02xxx) for warnings.

What I'm thinking is something like

*** src/interfaces/libpq/fe-protocol3.c 28 Apr 2010 13:46:23 -  1.43
--- src/interfaces/libpq/fe-protocol3.c 21 Aug 2010 02:41:01 -
***
*** 206,211 
--- 206,219 
if (!conn-result)
return;
}
+   /*
+* If the command was successful completed, set the
+* appropriate SQLSTATE. Pre-9.1 don't set it.
+* ERRCODE_SUCCESSFUL_COMPLETION code (aka 0) is
+* hardcoded here because we avoid including elog routines
+* here.
+*/
+   pqSaveMessageField(conn-result, PG_DIAG_SQLSTATE, 0);
strncpy(conn-result-cmdStatus, conn-workBuffer.data,
CMDSTATUS_LEN);
conn-asyncStatus = PGASYNC_READY;


(I only patch the 'Command Complete' message here but it is necessary to patch
other success messages too.)


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] More vacuum stats

2010-08-22 Thread Euler Taveira de Oliveira
Magnus Hagander escreveu:
 Was there any particular reason why this wasn't exposed before that
 I've missed, making this a bad addition? :-)
 
Not that I know of. Good catch. ;)


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] SQLSTATE of notice PGresult

2010-08-20 Thread Euler Taveira de Oliveira
Dmitriy Igrishin escreveu:
   /* NOT presents - NULL. Why not 0 ? */
   const char* sqlstate = Pg::PQresultErrorField(res, PG_DIAG_SQLSTATE);
 
That's because the protocol doesn't set error field when the command
succeeded. IMHO it's an oversight (the documentation is correct but the code
is not) and should be correct because the spec enforces it.


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] knngist - 0.8

2010-08-09 Thread Euler Taveira de Oliveira
Alexander Korotkov escreveu:
 Such approach
 can give benefit when we need to filter by similarity. For example, in
 pg_trgm % is used for similarity filtering, but similarity threshold
 is global for session. That's why we can't create complex queries which
 contain similarity filtering with different threshold.
 
What do you mean by complex queries? You can always use the SET command. Sadly
it doesn't work when you have different thresholds within distinct subqueries.
 (In pg_similarity I use this approach to set the function's thresholds). What
I am investigating is a way to build an index with some user-defined
parameters. (We already have some infra-structure in reloptions for that but
it needs some work to support my idea). I have some half-baked patch that I'm
planning to submit to some of the CFs. Unfortunately, I don't have time for it
 ATM.


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] Patch for 9.1: initdb -C option

2010-07-23 Thread Euler Taveira de Oliveira
David Christensen escreveu:
 Like I said in the original submission, I found it helpful for the 
 programmatic configuration of a number of simultaneous node, but if it's not 
 generally useful to the community at large, I'll understand if it's punted.
 
I'm afraid it is the only use case for this new option. If it is, it doesn't
deserve a new option. We can live with echo + initdb for those cases.


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] PGXS: REGRESS_OPTS=--load-language=plpgsql

2010-02-18 Thread Euler Taveira de Oliveira
David Fetter escreveu:
 OK, I know it's late, but having PL/pgsql on by default has caused an
 unforeseen need: --require-language.
 
Why? IMHO pg_regress should be used with the same postgres version it was
built with. So any tests that use --load-language=plpgsql should be fixed.
Besides we could  patch pg_regress.c to ignore loading plpgsql language into
the database (instead of adding another parameter -- we already have too many
options).


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] Problem with 8.4 stats collector high load

2010-02-16 Thread Euler Taveira de Oliveira
Jakub Ouhrabka escreveu:
 These databases are archive databases, so there is no user activity - no
 connected users. But the stats collector generates load - 20-40% of
 modern 2.8GHz core all the time.
 
Did you try to set stats_temp_directory in a RAM based filesystem?

 Any clues what does it cause and how to investigate it?
 
OProfile?


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] TCP keepalive support for libpq

2010-02-15 Thread Euler Taveira de Oliveira
Fujii Masao escreveu:
 Here is the patch which provides those three parameters as conninfo
 options. Should this patch be added into the first CommitFest for v9.1?
 
Go ahead.


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] TCP keepalive support for libpq

2010-02-15 Thread Euler Taveira de Oliveira
Magnus Hagander escreveu:
 If we want to do this, I'd be inclined to say we sneak this into 9.0..
 It's small enough ;)
 
I'm afraid Robert will say a big NO. ;) I'm not against your idea; so if
nobody objects go for it *now*.


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] TCP keepalive support for libpq

2010-02-12 Thread Euler Taveira de Oliveira
Marko Kreen escreveu:
 3) Support all 3 parameters (keepidle, keepintvl, keepcnt)
  and ignore parameters not supported by OS.
 
+1. AFAIR, we already do that for the backend.


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL

2010-02-11 Thread Euler Taveira de Oliveira
Simon Riggs escreveu:
 It would mean that pg_standby would act appropriately according to the
 setting of standby_mode. So you wouldn't need multiple examples of use,
 it would all just work whatever the setting of standby_mode. Nice simple
 entry in the docs.
 
+1. I like the %s idea. IMHO fixing pg_standby for SR is a must-fix. I'm
foreseeing a lot of users asking why pg_standby doesn't work on SR mode ...


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] psql tab completion for DO blocks

2010-02-11 Thread Euler Taveira de Oliveira
Takahiro Itagaki escreveu:
 Should we fix the documentation when we add the tab completion?
 
Yes, it seems consistent with other commands having optional parameters in the
middle of the command rather than at the end.


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] psql tab completion for DO blocks

2010-02-11 Thread Euler Taveira de Oliveira
David Fetter escreveu:
 It's consistent with how we do CREATE FUNCTION, where the order of
 parameters after RETURNS is arbitrary.
 
If it is arbitrary the synopsis is wrong because it is imposing that code
should be written after DO. It should be:

DO { [ LANGUAGE lang_name ] | code } ...


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] Hostnames in pg_hba.conf

2010-02-11 Thread Euler Taveira de Oliveira
Mark Mielke escreveu:
 Of course, then I'll ask for the ability to simplify specifying multiple
 databases:
 
We already support multiple users and/or databases for a single pg_hba.conf
line ...


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] [PATCH] Output configuration status after ./configure run.

2010-02-10 Thread Euler Taveira de Oliveira
Alvaro Herrera escreveu:
 The general idea seems sensible to me.  I can't comment on the
 specifics.
 
+1. A lot of other programs have this summary at the end of configure
execution. The problem is that PostgreSQL has too many options. Do we want to
list all of them?


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] [PATCH] Output configuration status after ./configure run.

2010-02-10 Thread Euler Taveira de Oliveira
Tom Lane escreveu:
 I'm still quite dubious about the usefulness, but I could live with this
 if someone explains to me how the printout is going to stay within 24x80
 given the inevitable growth in number of configure options ...
 
AFAICS, we have  40 configure options. If we want this to fit in 24 rows (i)
we should choose popular options or (ii) print only features/packages that
have a non-default option/value. Both ideas aren't ideal for machine-readable
format (as someone mentioned pgbuildfarm) because the summary is partial i.e.
the software needs to know beforehand what are the default configure options.
Of course, parsing the configure output and greping the interested options is
a boring task but at least it is all there; but it's not a summary. :(


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] Postgres Triggers issue

2010-02-10 Thread Euler Taveira de Oliveira
u235sentinel escreveu:
 I'm curious what context you were expecting and which group this should
 go to.  I've posted similar questions in the other groups and they
 seem... lost at times.
 
What group? AFAICS this question belongs to -general. What about starting to
show us the definition of m_a, temp_m_t, and related tables?


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] development setup and libdir

2010-01-31 Thread Euler Taveira de Oliveira
Ivan Sergio Borgonovo escreveu:
 I'm pretty sure that what you're pointing at is not going to work
 unless you specify a bunch of other parameters.
 
Ugh? Are you saying there is something wrong in our *official* documentation?
It is just a normal compilation command; if you're a C programmer you'll have
no problem.

 Once you ask... people direct you to pgxs. pgxs seems a good
 choice... a newcomer like me thinks... well that was made exactly to
 pick up all the information it needs from the environment and build
 my module.
 
Again, PGXS was developed to make packagers' life easier. Why on earth I want
to install my library in a path different from $libdir? Packagers don't.
Besides, PGXS won't work on Windows unless you're using a installation built
using MinGW.

 connection, but still different version of pgxs gives different
 names to .so.
 
What the problem with that? If it set up the right name in sql file that's OK.

IMHO, you're trying to complicate a simple process. If you messed up your
installation you can always do:

$ cd mymodule
$ USE_PGXS=1 make uninstall


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] PG 9.0 and standard_conforming_strings

2010-01-30 Thread Euler Taveira de Oliveira
Peter Eisentraut escreveu:
 Maybe the next step should be to leave standard_conforming_strings off
 but make the warning an error.
 
It will break application in the same way as enabling the parameter. Besides
that the parameter should be renamed to escape_string_*error* to reflect the
fact that it doesn't emit an error anymore. I don't think it is a good idea.

The main problem of enabling standard_conforming_strings is that applications
and/or programming language DB APIs are not prepared to support this. I don't
see a change in DB APIs (that I know of -- Python, Perl, and PHP) to add
support for producing a string according to standard_conforming_strings 
parameter.

IMHO we need to encourage such languages to modify their functions so we can
produce strings according to this parameter. These change will minimize the
number of problems in applications. Of course, there will be some problems in
those applications that doesn't use the escape function of the DB API but they
could always disable this parameter. ;)

As for enabling it by default, I'm afraid we will have to wait a few cycles of
development because of those changes in DB APIs. A reasonable target is 10.0. ;)


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] development setup and libdir

2010-01-30 Thread Euler Taveira de Oliveira
Ivan Sergio Borgonovo escreveu:
 That's pretty expensive.
 
Ugh?

 I mean... I just would like my .so end up with the expected name
 somewhere else.
It's just two command lines [1].

 Wouldn't it be better if make install could install stuff where I
 ask so I could put modules in different places *even* for the same
 installation of postgres?
Why do you want to mess your installation? Besides, you will need to add the
install path in dynamic_library_path and or the absolute path to your CREATE
FUNCTION script. Man, that's too much work for a packager! Of course, you can
always build on your way; that's up to you.

 I mean... what's so weird about being able to develop postgres
 modules without requiring people build the whole postgresql or
 switching back and forward from root?
 
Ugh? You don't need root; I mean, you can always set absolute path or even the
dynamic_library_path [2] parameter.


[1] http://www.postgresql.org/docs/8.4/static/xfunc-c.html#DFUNC
[2]
http://www.postgresql.org/docs/8.4/static/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-OTHER


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] Resetting a single statistics counter

2010-01-24 Thread Euler Taveira de Oliveira
Magnus Hagander escreveu:
 Off to make it two separate functions.. (seems much more user-friendly
 than a single function with an extra argument, IMHO)
 
+1. But as Simon said _single_ is too ugly. What about
pg_stat_reset_user_{function,relation}?

Another thing that is not a problem of your patch but it needs to be fixed is
that resetting functions remove the line from pg_stat_user_functions; that a
different behavior from other pg_stat_user_* functions.


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] Resetting a single statistics counter

2010-01-24 Thread Euler Taveira de Oliveira
Tom Lane escreveu:
 That implies that the operations wouldn't work against system tables;
 which they do.  I think a bigger problem is that reset_single_table
 seems like it might be talking about something like a TRUNCATE, ie,
 it's not clear that it means to reset counters rather than data.
 The pg_stat_ prefix is some help but not enough IMO.  So I suggest
 pg_stat_reset_table_counters and pg_stat_reset_function_counters.
 
Sure, much better. +1.

 (BTW, a similar complaint could be made about the previously committed
 patch: reset shared what?)
 
BTW, what about that idea to overload pg_stat_reset()? The
pg_stat_reset_shared should be renamed to pg_stat_reset('foo') [1] where foo
is the class of objects that it is resetting. pg_stat_reset is not a so
suggestive name but that's one we already have; besides, it will be intuitive
for users.


[1] http://archives.postgresql.org/pgsql-hackers/2010-01/msg01317.php


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] MySQL-ism help patch for psql

2010-01-19 Thread Euler Taveira de Oliveira
David Christensen escreveu:
 I whipped up a quick patch for supporting some of the common mysql-based
 meta commands; this is different than some things which have been
 discussed in the past, in that it provides just a quick direction to the
 appropriate psql command, not an actual alternative syntax for the same
 action.  This is not intended to be comprehensive, but just to provide
 proper direction
 
This idea was proposed and rejected later; search the archives. IMHO it's more
appropriated for a wiki page than a PostgreSQL-*especific* help command. If we
do that, we'll see requests like why don't you add _my-favorite-db-here_ help
too?. So, -1.


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] Streaming replication and non-blocking I/O

2010-01-16 Thread Euler Taveira de Oliveira
Dimitri Fontaine escreveu:
 It should be possible to be in contrib and installed by default, even
 
And it could be uninstall too. Let's not do it for core functionalities.


-- 
  Euler Taveira de Oliveira
  http://www.timbira.com/

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


  1   2   3   >