Re: [HACKERS] Patch: improve selectivity estimation for IN/NOT IN
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...
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...
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
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
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
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
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
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()
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()
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
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
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 #
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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?
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
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
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
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
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
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
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
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
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?
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?
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
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?
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
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
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?
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 ...
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
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
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
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
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
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
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!
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
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
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
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
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
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
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
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?
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
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
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
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
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
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++
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++
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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.
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.
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
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
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
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
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
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
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
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
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