Re: [HACKERS] [v9.3] writable foreign tables
Kohei KaiGai wrote: 2012/8/25 Robert Haas robertmh...@gmail.com: On Thu, Aug 23, 2012 at 1:10 AM, Kohei KaiGai kai...@kaigai.gr.jp wrote: It is a responsibility of FDW extension (and DBA) to ensure each foreign-row has a unique identifier that has 48-bits width integer data type in maximum. It strikes me as incredibly short-sighted to decide that the row identifier has to have the same format as what our existing heap AM happens to have. I think we need to allow the row identifier to be of any data type, and even compound. For example, the foreign side might have no equivalent of CTID, and thus use primary key. And the primary key might consist of an integer and a string, or some such. I assume it is a task of FDW extension to translate between the pseudo ctid and the primary key in remote side. For example, if primary key of the remote table is Text data type, an idea is to use a hash table to track the text-formed primary being associated with a particular 48-bits integer. The pseudo ctid shall be utilized to track the tuple to be modified on the scan-stage, then FDW can reference the hash table to pull-out the primary key to be provided on the prepared statement. And what if there is a hash collision? Then you would not be able to determine which row is meant. I agree with Robert that this should be flexible enough to cater for all kinds of row identifiers. Oracle, for example, uses ten byte identifiers which would give me a headache with your suggested design. Do we have some other reasonable ideas? Would it be too invasive to introduce a new pointer in TupleTableSlot that is NULL for anything but virtual tuples from foreign tables? Yours, Laurenz Albe -- 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] [v9.3] writable foreign tables
Kaigai-san, On Thu, Aug 23, 2012 at 2:10 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: The patched portion at contrib/file_fdw.c does not make sense actually. It just prints messages for each invocation. It is just a proof-of-concept to show possibility of implementation based on real RDBMS. Attached is a tar ball of pgsql_fdw. It's WIP and contains no document, but it would be enough for your PoC purpose. Usage and features are same as the last version posted for 9.2 cycle. # I'll post finished patch in the CF-Sep. Here are random comments for your PoC patch: + As Robert says, using CTID as virtual tuple identifier doesn't seem nice when considering various FDWs for NoSQL or RDBMS. Having abstract layer between FDWs and tuple sounds better, but implementing it by each FDW seems useless effort. Do yo have any idea of generic mechanism for tuple mapping? + Do you have any plan about deparsing local qualifiers into remote query to avoid repeated query submission? This would improve performance of big UPDATE, but its use case might be limited to statements which consist of one foreign table. For this case, we can consider pass-through mode as second way. + I have not read your patch closely yet, but I wonder how we can know which column is actually updated. If we have only updated image of tuple, we have to update all remote columns by new values? -- Shigeru Hanada pgsql_fdw_93.tar.gz Description: application/gzip -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Minor pre-bug in gram.y for DROP INDEX CONCURRENTLY IF_P EXISTS
http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/parser/gram.y;hb=master#l4940 The missing_ok property should be true. Just something I noticed when browsing the code. It appears to be a new language feature, so it probably hasn't been noticed by the general public yet. -- Brendan Byrd p...@resonatorsoft.org Brendan Byrd bb...@cpan.org -- 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] Event Triggers reduced, v1
Hi,
I'm back to PostgreSQL development concerns after some distraction here.
First, thanks for pushing the patch to commit!
I've been reviewing your changes and here's a very small patch with some
details I would have spelled out differently. See what you think, I
mostly needed to edit some code to get back in shape :)
Coming next, catch-up with things I've missed and extending the included
support for event triggers in term of function parameters (rewritten
command string, object kind, etc), and maybe PL support too.
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c
index d725360..9bc699e 100644
--- a/src/backend/commands/event_trigger.c
+++ b/src/backend/commands/event_trigger.c
@@ -17,6 +17,7 @@
#include catalog/dependency.h
#include catalog/indexing.h
#include catalog/objectaccess.h
+#include catalog/pg_collation.h
#include catalog/pg_event_trigger.h
#include catalog/pg_proc.h
#include catalog/pg_trigger.h
@@ -31,6 +32,7 @@
#include utils/builtins.h
#include utils/evtcache.h
#include utils/fmgroids.h
+#include utils/formatting.h
#include utils/lsyscache.h
#include utils/memutils.h
#include utils/rel.h
@@ -337,14 +339,11 @@ filter_list_to_array(List *filterlist)
foreach(lc, filterlist)
{
const char *value = strVal(lfirst(lc));
- char *result,
- *p;
-
- result = pstrdup(value);
- for (p = result; *p; p++)
- *p = pg_ascii_toupper((unsigned char) *p);
- data[i++] = PointerGetDatum(cstring_to_text(result));
- pfree(result);
+
+ data[i++] =
+ PointerGetDatum(
+cstring_to_text(
+ str_toupper(value, strlen(value), DEFAULT_COLLATION_OID)));
}
return PointerGetDatum(construct_array(data, l, TEXTOID, -1, false, 'i'));
@@ -565,6 +564,9 @@ EventTriggerDDLCommandStart(Node *parsetree)
const char *tag;
EventTriggerData trigdata;
+ /* Get the command tag. */
+ tag = CreateCommandTag(parsetree);
+
/*
* We want the list of command tags for which this procedure is actually
* invoked to match up exactly with the list that CREATE EVENT TRIGGER
@@ -579,15 +581,11 @@ EventTriggerDDLCommandStart(Node *parsetree)
* type in question, or you need to adjust check_ddl_tag to accept the
* relevant command tag.
*/
+
#ifdef USE_ASSERT_CHECKING
if (assert_enabled)
- {
- const char *dbgtag;
-
- dbgtag = CreateCommandTag(parsetree);
- if (check_ddl_tag(dbgtag) != EVENT_TRIGGER_COMMAND_TAG_OK)
- elog(ERROR, unexpected command tag \%s\, dbgtag);
- }
+ if (check_ddl_tag(tag) != EVENT_TRIGGER_COMMAND_TAG_OK)
+ elog(ERROR, unexpected command tag \%s\, tag);
#endif
/* Use cache to find triggers for this event; fast exit if none. */
@@ -595,9 +593,6 @@ EventTriggerDDLCommandStart(Node *parsetree)
if (cachelist == NULL)
return;
- /* Get the command tag. */
- tag = CreateCommandTag(parsetree);
-
/*
* Filter list of event triggers by command tag, and copy them into
* our memory context. Once we start running the command trigers, or
@@ -609,7 +604,10 @@ EventTriggerDDLCommandStart(Node *parsetree)
{
EventTriggerCacheItem *item = lfirst(lc);
- /* Filter by session replication role. */
+ /*
+ * Filter by session replication role. Remember that DISABLED event
+ * triggers didn't make it to the cache.
+ */
if (SessionReplicationRole == SESSION_REPLICATION_ROLE_REPLICA)
{
if (item-enabled == TRIGGER_FIRES_ON_ORIGIN)
--
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] emacs configuration for new perltidy settings
Peter Eisentraut pete...@gmx.net writes: This might be useful for some people. Here is an emacs configuration for perl-mode that is compatible with the new perltidy settings. Note that the default perl-mode settings produce indentation that will be completely shredded by the new perltidy settings. Thanks! -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] Performance Improvement by reducing WAL for Update Operation
On 27.08.2012 15:18, Amit kapila wrote: I have implemented the WAL Reduction Patch for the case of HOT Update as pointed out by Simon and Robert. In this patch it only goes for Optimized WAL in case of HOT Update with other restrictions same as in previous patch. The performance numbers for this patch are attached in this mail. It has improved by 90% if the page has fillfactor 80. Now going forward I have following options: a. Upload the patch in Open CF for WAL Reduction which contains reductution for HOT and non-HOT updates. b. Upload the patch in Open CF for WAL Reduction which contains reductution for HOT updates. c. Upload both the patches as different versions. Let's do it for HOT updates only. Simon Robert made good arguments on why this is a bad idea for non-HOT updates. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Statistics and selectivity estimation for ranges
On 24.08.2012 18:51, Heikki Linnakangas wrote: On 20.08.2012 00:31, Alexander Korotkov wrote: New version of patch. * Collect new stakind STATISTIC_KIND_BOUNDS_HISTOGRAM, which is lower and upper bounds histograms combined into single ranges array, instead of STATISTIC_KIND_HISTOGRAM. One worry I have about that format for the histogram is that you deserialize all the values in the histogram, before you do the binary searches. That seems expensive if stats target is very high. I guess you could deserialize them lazily to alleviate that, though. * Selectivity estimations for,=,,= using this histogram. Thanks! I'm going to do the same for this that I did for the sp-gist patch, and punt on the more complicated parts for now, and review them separately. Attached is a heavily edited version that doesn't include the length histogram, and consequently doesn't do anything smart for the and operators. is estimated using the bounds histograms. There's now a separate stakind for the empty range fraction, since it's not included in the length-histogram. I tested this on a dataset containing birth and death dates of persons that have a wikipedia page, obtained from the dbpedia.org project. I can send a copy if someone wants it. The estimates seem pretty accurate. Please take a look, to see if I messed up something. Committed this with some further changes. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Intermittent regression test failures from index-only plan changes
On Fri, Jan 27, 2012 at 01:45:28PM -0500, Robert Haas wrote: On Sat, Jan 7, 2012 at 12:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: I feel like this is a trick question, but I'll ask anyway: Can't we just ignore ANALYZE? AFAICS, no. ANALYZE will run user-defined code: not only user-supplied stats collection functions, but user-defined index expressions. We cannot assume that none of that ever requires a snapshot. The question is: Why would it matter if we expunged tuples from table A while ANALYZE was running on table B? I guess the problem is that the index on B might involve a user-defined function which (under the covers) peeks at table A, possibly now seeing an inconsistent view of the database. It's pretty unfortunate to have to cater to that situation, though, because most of the time an ANALYZE on table A is only going to look at table A and the system catalogs. In fact, it wouldn't even be disastrous (in most cases) if we removed tuples from the table being analyzed - we're engaged in an inherently statistical process anyway, so who really cares if things change on us in medias res? Could we easily detect the cases where user code is being run and ignore ANALYZE when none is? A probably crazy idea is to add an option to vacuum that would cause it, upon discovering that it can't set PD_ALL_VISIBLE on a page because the global xmin is too old, to wait for all of the virtual transaction IDs who might not be able to see every tuple on the page. This would allow us to get into a state where all the PD_ALL_VISIBLE bits are known to be set. But that seems a bit complex for something that we probably don't care about much outside of the regression tests. If none of the above is feasible (and I suspect it isn't), we might just want to tweak the queries to do something that will preclude using an index-only scan, like including tableoid::regclass in the target list. Was this addressed? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Performance Improvement by reducing WAL for Update Operation
From: Heikki Linnakangas [mailto:heikki.linnakan...@enterprisedb.com] Sent: Monday, August 27, 2012 5:58 PM To: Amit kapila On 27.08.2012 15:18, Amit kapila wrote: I have implemented the WAL Reduction Patch for the case of HOT Update as pointed out by Simon and Robert. In this patch it only goes for Optimized WAL in case of HOT Update with other restrictions same as in previous patch. The performance numbers for this patch are attached in this mail. It has improved by 90% if the page has fillfactor 80. Now going forward I have following options: a. Upload the patch in Open CF for WAL Reduction which contains reductution for HOT and non-HOT updates. b. Upload the patch in Open CF for WAL Reduction which contains reductution for HOT updates. c. Upload both the patches as different versions. Let's do it for HOT updates only. Simon Robert made good arguments on why this is a bad idea for non-HOT updates. Okay, I shall do it that way. So now I shall send information about all the testing I have done for this Patch and then Upload it in CF. With Regards, Amit Kapila. -- 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] pgindent README correction
On Mon, Jan 9, 2012 at 11:31:02AM -0600, Kevin Grittner wrote: I found that I needed to adjust the command given in the README file for pgindent. Trivial patch attached. The one other issue I ran into in following the latest pgindent instructions was that I had to add #include stdlib.h to the parse.c file (as included in the pg_bsd_indent-1.1.tar.gz file at ftp://ftp.postgresql.org/pub/dev ). Without it I got this: parse.c: In function *parse*: parse.c:236:6: warning: implicit declaration of function *exit* parse.c:236:6: warning: incompatible implicit declaration of built-in function *exit* Can someone fix that and put up a 1.2 version? Done. Please give the ftp mirrors a little while to update, but you can get pg_bsd_indent 1.2 now at ftpmaster.postgresql.org. pgindent was also updated to require the 1.2 version. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] archive_keepalive_command
Where are we on this?
---
On Mon, Jan 16, 2012 at 01:52:35AM +, Simon Riggs wrote:
On Fri, Dec 16, 2011 at 3:01 PM, Simon Riggs si...@2ndquadrant.com wrote:
archive_command and restore_command describe how to ship WAL files
to/from an archive.
When there is nothing to ship, we delay sending WAL files. When no WAL
files, the standby has no information at all.
To provide some form of keepalive on quiet systems the
archive_keepalive_command provides a generic hook to implement
keepalives. This is implemented as a separate command to avoid storing
keepalive messages in the archive, or at least allow overwrites using
a single filename like keepalive.
Examples
archive_keepalive_command = 'arch_cmd keepalive' # sends a file
called keepalive to archive, overwrites allowed
archive_keepalive_command = 'arch_cmd %f.%t.keepalive #sends a file
like 0001000ABFE.20111216143517.keepalive
If there is no WAL file to send, then we send a keepalive file
instead. Keepalive is a small file that contains same contents as a
streaming keepalive message (re: other patch on that).
If no WAL file is available and we are attempting to restore in
standby_mode, then we execute restore_keepalive_command to see if a
keepalive file is available. Checks for a file in the specific
keepalive format and then uses that to update last received info from
master.
e.g.
restore_keepalive_command = 'restore_cmd keepalive' # gets a file
called keepalive to archive, overwrites allowed
Patch.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
diff --git a/src/backend/access/transam/recovery.conf.sample
b/src/backend/access/transam/recovery.conf.sample
index 5acfa57..fab288c 100644
--- a/src/backend/access/transam/recovery.conf.sample
+++ b/src/backend/access/transam/recovery.conf.sample
@@ -43,6 +43,13 @@
#
#restore_command = ''# e.g. 'cp /mnt/server/archivedir/%f %p'
#
+# restore_keepalive_command
+#
+# specifies an optional shell command to download keepalive files
+# e.g. archive_keepalive_command = 'cp -f %p $ARCHIVE/keepalive /dev/null'
+# e.g. restore_keepalive_command = 'cp $ARCHIVE/keepalive %p'
+#
+#restore_keepalive_command = ''
#
# archive_cleanup_command
#
diff --git a/src/backend/access/transam/xlog.c
b/src/backend/access/transam/xlog.c
index ce659ec..2729141 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -73,8 +73,10 @@ intCheckPointSegments = 3;
int wal_keep_segments = 0;
int XLOGbuffers = -1;
int XLogArchiveTimeout = 0;
+int XLogArchiveKeepaliveTimeout = 10; /* XXX set to
60 before commit */
bool XLogArchiveMode = false;
char*XLogArchiveCommand = NULL;
+char*XLogArchiveKeepaliveCommand = NULL;
bool EnableHotStandby = false;
bool fullPageWrites = true;
bool log_checkpoints = false;
@@ -188,6 +190,7 @@ static bool restoredFromArchive = false;
/* options taken from recovery.conf for archive recovery */
static char *recoveryRestoreCommand = NULL;
+static char *recoveryRestoreKeepaliveCommand = NULL;
static char *recoveryEndCommand = NULL;
static char *archiveCleanupCommand = NULL;
static RecoveryTargetType recoveryTarget = RECOVERY_TARGET_UNSET;
@@ -634,6 +637,7 @@ static intemode_for_corrupt_record(int emode,
XLogRecPtr RecPtr);
static void XLogFileClose(void);
static bool RestoreArchivedFile(char *path, const char *xlogfname,
const char *recovername, off_t
expectedSize);
+static void RestoreKeepaliveFile(void);
static void ExecuteRecoveryCommand(char *command, char *commandName,
bool failOnerror);
static void PreallocXlogFiles(XLogRecPtr endptr);
@@ -2718,7 +2722,10 @@ XLogFileRead(uint32 log, uint32 seg, int emode,
TimeLineID tli,
RECOVERYXLOG,
XLogSegSize);
if (!restoredFromArchive)
+ {
+ RestoreKeepaliveFile();
return -1;
+ }
break;
case XLOG_FROM_PG_XLOG:
@@ -3179,6 +3186,192 @@ not_available:
return false;
}
+static void
+RestoreKeepaliveFile(void)
+{
+ charkeepalivepath[MAXPGPATH];
+ charkeepaliveRestoreCmd[MAXPGPATH];
+ char *dp;
+ char *endp;
+ const char *sp;
+ int
Re: [HACKERS] Caching for stable expressions with constant arguments v6
Where are we on this? --- On Mon, Jan 16, 2012 at 07:06:45PM +0200, Marti Raudsepp wrote: Hi list, Here's v6 of my expression caching patch. The only change in v6 is added expression cost estimation in costsize.c. I'm setting per-tuple cost of CacheExpr to 0 and moving sub-expression tuple costs into the startup cost. As always, this work is also available from my Github cache branch: https://github.com/intgr/postgres/commits/cache This patch was marked as Returned with Feedback from the 2011-11 commitfest. I expected to get to tweak the patch in response to feedback before posting to the next commitfest. But said feedback didn't arrive, and with me being on vacation, I missed the 2012-01 CF deadline. :( I will add it to the 2012-01 commitfest now, I hope that's OK. If not, feel free to remove it and I'll put it in 2012-Next. PS: Jaime, have you had a chance to look at the patch? Even if you're not done with the review, I'd be glad to get some comments earlier. And thanks for reviewing. Regards, Marti -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] hunspell and tsearch2 ?
Hi,
we have issues with compound words in tsearch2 using the german (ispell)
dictionary. This has been discussed before but there is no real solution
using the recommended german dictionary at
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2 (convert old
openoffice dict file to ispell suitable for tsearch):
# select ts_lexize('german_ispell', 'vollklimatisiert');
ts_lexize
{vollklimatisiert}
(1 row)
This should return atleast
{vollklimatisiert, voll, klimatisiert}
The issue with compound words in ispell has been addressed in hunspell.
But this has not been integrated fully to tsearch2 (according to the
documentation).
Are there any plans to fully integrate hunspell into tsearch2? What is
needed to do this? What is the functional delta which is missing? Maybe
we can help...
Thanks for help
Dirk
--
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] Statistics and selectivity estimation for ranges
On Mon, Aug 27, 2012 at 5:00 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 24.08.2012 18:51, Heikki Linnakangas wrote: On 20.08.2012 00:31, Alexander Korotkov wrote: New version of patch. * Collect new stakind STATISTIC_KIND_BOUNDS_**HISTOGRAM, which is lower and upper bounds histograms combined into single ranges array, instead of STATISTIC_KIND_HISTOGRAM. One worry I have about that format for the histogram is that you deserialize all the values in the histogram, before you do the binary searches. That seems expensive if stats target is very high. I guess you could deserialize them lazily to alleviate that, though. * Selectivity estimations for,=,,= using this histogram. Thanks! I'm going to do the same for this that I did for the sp-gist patch, and punt on the more complicated parts for now, and review them separately. Attached is a heavily edited version that doesn't include the length histogram, and consequently doesn't do anything smart for the and operators. is estimated using the bounds histograms. There's now a separate stakind for the empty range fraction, since it's not included in the length-histogram. I tested this on a dataset containing birth and death dates of persons that have a wikipedia page, obtained from the dbpedia.org project. I can send a copy if someone wants it. The estimates seem pretty accurate. Please take a look, to see if I messed up something. Committed this with some further changes. Thanks! Sorry for I didn't provide a feedback for previous message. Commited patch looks nice for me. I'm going to provide additional patch with length-histogram and more selectivity estimates. -- With best regards, Alexander Korotkov.
Re: [HACKERS] Caching for stable expressions with constant arguments v6
On Mon, Aug 27, 2012 at 4:50 PM, Bruce Momjian br...@momjian.us wrote: Where are we on this? TL;DR: Got a review, requires substantial work, current github branch is slightly broken, will get back to this soon. Tom Lane sent a thorough review of the patch here: http://archives.postgresql.org/pgsql-hackers/2012-03/msg00655.php (very much appreciated!) I have addressed some smaller points from that list in my github branch, but it still requires a substantial amount of work (in particular, the bulk of this patch which is the recursion logic in eval_const_expressions_mutator, needs to be changed to prevent unnecessary CacheExpr insertions and to store intermediate state in the context struct). I got a small fragment of this into PostgreSQL 9.2 as commit 81a646febe87964725647a36d839f6b4b405f3ae. I rebased my github branch on top of this commit, but the rebase introduced some test failures that I have not tracked down yet. I don't know if it applies to git HEAD any more. Sadly some other things intervened and I have not had the time to return to hacking on this patch. But I am hopeful I can get it into shape during the 9.3 cycle. Regards, Marti -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A caveat of partitioning tables in the document
2012/8/23 Tom Lane t...@sss.pgh.pa.us: Fujii Masao masao.fu...@gmail.com writes: On Wed, Aug 22, 2012 at 12:59 AM, Kasahara Tatsuhito kasahara.tatsuh...@gmail.com wrote: The latest document (doc/src/sgml/ddl.sgml) says === 2974itemizedlist 2975 listitem 2976 para 2977 Constraint exclusion only works when the query's literalWHERE/ 2978 clause contains constants. A parameterized query will not be 2979 optimized, since the planner cannot know which partitions the 2980 parameter value might select at run time. For the same reason, 2981 quotestable/ functions such as functionCURRENT_DATE/function 2982 must be avoided. 2983 /para 2984 /listitem === but in my understanding, this problem will be solved on 9.2 (with parameterized plans). Or some issues still remain ? At least this limitation A parameterized query will not be optimized, since the planner cannot know which partitions the parameter value might select at run time. has been solved unless I'm missing something. So we should just get rid of that sentence from the document. Yes, I think we can take that out now. The issue with stable functions still remains though. Thanks for your replay. I see we can remove the topic from the doc except a issue with stable functions. Best regards, -- Tatsuhito Kasahara kasahara.tatsuhito _at_ gmail.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] Caching for stable expressions with constant arguments v6
On Mon, Aug 27, 2012 at 05:44:32PM +0300, Marti Raudsepp wrote: On Mon, Aug 27, 2012 at 4:50 PM, Bruce Momjian br...@momjian.us wrote: Where are we on this? TL;DR: Got a review, requires substantial work, current github branch is slightly broken, will get back to this soon. Tom Lane sent a thorough review of the patch here: http://archives.postgresql.org/pgsql-hackers/2012-03/msg00655.php (very much appreciated!) I have addressed some smaller points from that list in my github branch, but it still requires a substantial amount of work (in particular, the bulk of this patch which is the recursion logic in eval_const_expressions_mutator, needs to be changed to prevent unnecessary CacheExpr insertions and to store intermediate state in the context struct). I got a small fragment of this into PostgreSQL 9.2 as commit 81a646febe87964725647a36d839f6b4b405f3ae. I rebased my github branch on top of this commit, but the rebase introduced some test failures that I have not tracked down yet. I don't know if it applies to git HEAD any more. Sadly some other things intervened and I have not had the time to return to hacking on this patch. But I am hopeful I can get it into shape during the 9.3 cycle. OK, thanks for the update, and your work on this. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] [GENERAL] Why extract( ... from timestamp ) is not immutable?
On Wed, Jan 25, 2012 at 11:30:49AM -0500, Tom Lane wrote: hubert depesz lubaczewski dep...@depesz.com writes: anyway - the point is that in \df date_part(, timestamp) says it's immutable, while it is not. Hmm, you're right. I thought we'd fixed that way back when, but obviously not. Or maybe the current behavior of the epoch case postdates that. Has this been addressed? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Minor pre-bug in gram.y for DROP INDEX CONCURRENTLY IF_P EXISTS
Brendan Byrd p...@resonatorsoft.org writes: http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/parser/gram.y;hb=master#l4940 The missing_ok property should be true. [ rolls eyes ] Apparently that patch wasn't ever, you know, tested? Will fix, thanks for spotting it! regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] unsigned and signed chars in libpq API
On Thu, Feb 2, 2012 at 10:33:24AM +0300, Dmitriy Igrishin wrote: Hey all, Could you tell me please an objective reason why PQunescapeBytea() returns unsigned char* rather than just char* ? I am asking because a bit confused. How this intermixes with LO's API, which based on signed chars (although as we all know large object - is a just bytea splitted on chunks)? And also PQgetvalue() returns char* rather than unsigned char*. Can someone answer this? Are we consistent here? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Timing overhead and Linux clock sources
On Sat, Aug 25, 2012 at 10:48 PM, Bruce Momjian br...@momjian.us wrote: On Mon, Aug 20, 2012 at 03:11:51PM -0400, Robert Haas wrote: On Thu, Aug 16, 2012 at 10:28 PM, Bruce Momjian br...@momjian.us wrote: FYI, I am planning to go ahead and package this tool in /contrib for PG 9.3. Isn't this exactly what we already did, in 9.2, in the form of contrib/pg_test_timing? Sorry, not sure how I missed that commit. Anyway, I am attaching a patch for 9.3 that I think improves the output of the tool, plus adds some C comments. The new output has the lowest duration times first: Testing timing overhead for 3 seconds. Per loop time including overhead: 41.31 nsec Histogram of timing durations: usec % of total count 1 95.87135 69627856 2 4.127592997719 4 0.00086628 8 0.00018133 16 0.1 5 32 0.0 1 This should make the output clearer to eyeball for problems --- a good timing has a high percentage on the first line, rather than on the last line. I guess I'm not sure the output format is an improvement. I wouldn't care much one way or the other if we had made this change at the time in AS92, but I'm not sure it's really worth breaking compatibility for a format that may or may not be any better. The person who wrote the original code presumably preferred it way it already is. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] unsigned and signed chars in libpq API
Bruce Momjian br...@momjian.us writes: On Thu, Feb 2, 2012 at 10:33:24AM +0300, Dmitriy Igrishin wrote: Could you tell me please an objective reason why PQunescapeBytea() returns unsigned char* rather than just char* ? I am asking because a bit confused. How this intermixes with LO's API, which based on signed chars (although as we all know large object - is a just bytea splitted on chunks)? And also PQgetvalue() returns char* rather than unsigned char*. Can someone answer this? Are we consistent here? We're not, particularly, but changing any of this seems likely to create more pain than it removes. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Useless removal of duplicate GIN index entries in pg_trgm
Hi, After pg_trgm extracts the trigrams as GIN index keys, generate_trgm() removes duplicate index keys, to avoid generating redundant index entries. Also ginExtractEntries() which is the caller of pg_trgm does the same thing. Why do we need to remove GIN index entries twice? I think that we can get rid of the removal-of-duplicate code block from generate_trgm() because it's useless. Comments? Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Italian PGDay 2012, Call for papers is now open
The sixth edition of the Italian PostgreSQL Day (PGDay.IT 2012) will be held on November 23 in Prato, Tuscany. The International Call for Papers is now open. Talks and presentations in English are accepted. Information in English for papers submission is available at: http://2012.pgday.it/call-for-papers/international-call-for-papers/ For any kind of information, do not hesitate to contact the organising committee via email at pgday2...@itpug.org. Thank you. Cheers, Gabriele Gabriele Bartolini Italian PostgreSQL Users Group (ITPUG), President -- 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] unsigned and signed chars in libpq API
On Mon, Aug 27, 2012 at 12:40:37PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: On Thu, Feb 2, 2012 at 10:33:24AM +0300, Dmitriy Igrishin wrote: Could you tell me please an objective reason why PQunescapeBytea() returns unsigned char* rather than just char* ? I am asking because a bit confused. How this intermixes with LO's API, which based on signed chars (although as we all know large object - is a just bytea splitted on chunks)? And also PQgetvalue() returns char* rather than unsigned char*. Can someone answer this? Are we consistent here? We're not, particularly, but changing any of this seems likely to create more pain than it removes. OK, thanks. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Timing overhead and Linux clock sources
On Mon, Aug 27, 2012 at 12:39:02PM -0400, Robert Haas wrote: On Sat, Aug 25, 2012 at 10:48 PM, Bruce Momjian br...@momjian.us wrote: On Mon, Aug 20, 2012 at 03:11:51PM -0400, Robert Haas wrote: On Thu, Aug 16, 2012 at 10:28 PM, Bruce Momjian br...@momjian.us wrote: FYI, I am planning to go ahead and package this tool in /contrib for PG 9.3. Isn't this exactly what we already did, in 9.2, in the form of contrib/pg_test_timing? Sorry, not sure how I missed that commit. Anyway, I am attaching a patch for 9.3 that I think improves the output of the tool, plus adds some C comments. The new output has the lowest duration times first: Testing timing overhead for 3 seconds. Per loop time including overhead: 41.31 nsec Histogram of timing durations: usec % of total count 1 95.87135 69627856 2 4.127592997719 4 0.00086628 8 0.00018133 16 0.1 5 32 0.0 1 This should make the output clearer to eyeball for problems --- a good timing has a high percentage on the first line, rather than on the last line. I guess I'm not sure the output format is an improvement. I wouldn't care much one way or the other if we had made this change at the time in AS92, but I'm not sure it's really worth breaking compatibility for a format that may or may not be any better. The person who wrote the original code presumably preferred it way it already is. He wrote it that way to allow for simpler C code --- he could just start from 31 and keeping skipping entries until he hit a non-zero. My format makes it easy to see which line should have the majority of the entries, e.g. first line should be 90%. I doubt there are enough people running this cross-version that consistency in output makes any difference between major PG versions. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] spinlocks on HP-UX
On Sun, Aug 26, 2012 at 9:45 AM, Bruce Momjian br...@momjian.us wrote: On Thu, Dec 29, 2011 at 11:37:22AM +0900, Manabu Ori wrote: a configure test only proves whether the build machine can deal with the flag, not whether the machine the executables will ultimately run on knows what the flag means. We cannot assume that the build and execution boxes are the same. (In general, AC_TRY_RUN tests are best avoided because of this.) I understand why that is important in general, but as a shop which builds from source, and is fine with a separate build for each hardware model / OS version combination, it would be great if any optimizations which are only available if you *do* assume that the build machine and the run machine are the same (or at lease identical) could be enabled with some configure switch. Maybe something like --enable-platform-specific-optimizations. I don't know if any such possible optimizations currently exist, I'm just saying that if any are identified, it would be nice to have the option of using them. I can't say the right way to go for now, but I'd like binary packages could enjoy the effect of my patch as far as possible so that I made lwarx hint test run in configure runtime. Was there any conclusion to this discussion? I don't think so. Broadly, I'm skeptical of hints. The CPU already does prefetching, so there is value in hints only if we can be smarter than the CPU. That's kind of an iffy proposition, particularly since different CPUs have very different behavior. What may be a good idea on one platform can stink on another platform. Also, something that works well in low-contention situations can blow up in high-contention situations, and visca versa. Consider this typical sequence: - prefetch something - do a little bit of other work - use the thing we prefetched If the system isn't under heavy contention, this can be a big win. The work is overlayed with the prefetch, so that by the time we get to step 3, we don't have to wait. But if the system now becomes contended, the prefetch may grab the cache line that we need, and then some other process may steal it, and then in step 3 we have to steal it back. So now our huge win becomes a huge loss, because we're fetching the same cache lines twice per backend instead of once. Now you can (maybe) fix this by jiggering the amount of other work that you do in between the prefetch and the usage, but that's likely to be different on every architecture, so the whole thing feels like a fairly impossible problem. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Timing overhead and Linux clock sources
On Mon, Aug 27, 2012 at 01:18:51PM -0400, Bruce Momjian wrote: This should make the output clearer to eyeball for problems --- a good timing has a high percentage on the first line, rather than on the last line. I guess I'm not sure the output format is an improvement. I wouldn't care much one way or the other if we had made this change at the time in AS92, but I'm not sure it's really worth breaking compatibility for a format that may or may not be any better. The person who wrote the original code presumably preferred it way it already is. He wrote it that way to allow for simpler C code --- he could just start from 31 and keeping skipping entries until he hit a non-zero. My format makes it easy to see which line should have the majority of the entries, e.g. first line should be 90%. I doubt there are enough people running this cross-version that consistency in output makes any difference between major PG versions. The real weird part is that this tool outputs a variable number of rows/buckets, depending on the hightest non-zero bucket, so I had trouble understanding it when the last line was the one to look at, especially for multiple runs. Also, we heavily adjusted the output of pg_test_fsync for several major releases and that wasn't a problem for anyone. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 support patch
On Sat, Aug 25, 2012 at 1:30 PM, David Johnston pol...@yahoo.com wrote: My internals knowledge is basically zero but it would seem that If you simply wanted the end-of-transaction result you could just record nothing during the transaction and then copy whatever values are present at commit to whatever logging mechanism you need. Whatever values are present and commit could be a terabyte of data. Or it could be a kilobyte of changed data within a terabyte database. You'd need some way to identify which data actually needs to be copied, since you surely don't want to copy the whole database. And even if you can identify it, going back and visiting all those blocks a second time will be expensive. Since these temporal/audit tables are intended to be maintained by the system if you do not ask the users to identify themselves but instead take the information directly from the environment, you never have to give a I'm sorry Dave response because Dave is never given the chance to submit a proposed value. Well, the point is that I think many people have requirements that are (1) different from each other and (2) more complicated than the simplest case we can come up with. Some people will want to log the application user (or some other piece of extra data); others won't. Some people will want to record every change in a transaction; others won't. Some people will want to log time stamps; others won't; others still may want a range per row indicating the time that row version lived. Some people will want to delete history before it fills up the disk; others will want to keep it forever. Some people will want to clean up history created by accidental changes; others will want to make sure that the history is as tamper-proof as possible. That's why, of everything that's been said on this topic, I mostly agree with what Josh Berkus said upthread: # If you want something in core which will be useful to a lot of our # users, it needs to be simple and flexible. Not ornate with lots of # dependancies. The first version of it should be as simple and minimalist # as possible. # # Personally, I would prefer a tool which just made it simpler to build my # own triggers, and made it automatic for the history table to track # changes in the live table. I think anything we build which controls # what goes into the history table, etc., will only narrow the user base. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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 support patch
Well, the point is that I think many people have requirements that are (1) different from each other and (2) more complicated than the simplest case we can come up with. Some people will want to log the application user (or some other piece of extra data); others won't. Some people will want to record every change in a transaction; others won't. Some people will want to log time stamps; others won't; others still may want a range per row indicating the time that row version lived. Some people will want to delete history before it fills up the disk; others will want to keep it forever. Some people will want to clean up history created by accidental changes; others will want to make sure that the history is as tamper-proof as possible. That's why, of everything that's been said on this topic, I mostly agree with what Josh Berkus said upthread: # If you want something in core which will be useful to a lot of our # users, it needs to be simple and flexible. Not ornate with lots of # dependancies. The first version of it should be as simple and minimalist # as possible. # # Personally, I would prefer a tool which just made it simpler to build my # own triggers, and made it automatic for the history table to track # changes in the live table. I think anything we build which controls # what goes into the history table, etc., will only narrow the user base. I can't agree - why we need a some simple solution based on tools, that are available now? I don't think we have to be hurry in support own proprietary solutions - when isn't difficult do it just with available tools now. Regards Pavel -- 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] Optimize referential integrity checks (todo item)
Any status on this? --- On Mon, Feb 13, 2012 at 04:34:51PM +0100, Vik Reykja wrote: On Mon, Feb 13, 2012 at 15:25, Robert Haas robertmh...@gmail.com wrote: On Sat, Feb 11, 2012 at 9:06 PM, Vik Reykja vikrey...@gmail.com wrote: I decided to take a crack at the todo item created from the following post: http://archives.postgresql.org/pgsql-performance/2005-10/msg00458.php The attached patch makes the desired changes in both code and function naming. It seemed quite easy to do but wasn't marked as easy on the todo, so I'm wondering if I've missed something. It's kind of hard to say whether you've missed something, because you haven't really explained what problem this is solving; the thread you linked too isn't very clear about that either. At first blush, it seems like you've renamed a bunch of stuff without making very much change to what actually happens. Changing lots of copies of equal to unchanged doesn't seem to me to be accomplishing anything. It's very simple really, and most of it is indeed renaming the functions. The problem this solves is that foreign key constraints are sometimes checked when they don't need to be. See my example below. All regression tests pass. You should add some new ones showing how this patch improves the behavior relative to the previous code. Or if you can't, then you should provide a complete, self-contained test case that a reviewer can use to see how your proposed changes improve things. I have no idea how a regression test would be able to see this change, so here's a test case that you can follow with the debugger. /* initial setup */ create table a (x int, y int, primary key (x, y)); create table b (x int, y int, z int, foreign key (x, y) references a); insert into a values (1, 2); insert into b values (1, null, 3); /* seeing the difference */ update b set z=0; When that update is run, it will check if the FK (x, y) has changed to know if it needs to verify that the values are present in the other table. The equality functions that do that don't consider two nulls to be equal (per sql logic) and so reverified the constraint. Tom noticed that it didn't need to because it hadn't really changed. In the above example, the current code will recheck the constraint and the new code won't. It's not really testing equality anymore (because null does not equal null), so I renamed them causing a lot of noise in the diff. We're in the middle of a CommitFest right now, Yes, I wasn't expecting this to be committed, I just didn't want to lose track of it. so please add this patch to the next one if you would like it reviewed: https://commitfest.postgresql.org/action/commitfest_view/open Will do. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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_upgrade's exec_prog() coding improvement
Excerpts from Alvaro Herrera's message of vie ago 24 11:44:33 -0400 2012: Actually it seems better to just get rid of the extra varargs function and just have a single exec_prog. The extra NULL argument is not troublesome enough, it seems. Updated version attached. Applied (with some very minor changes). -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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 search query is not executing in Postgres DB
On Fri, Feb 17, 2012 at 02:52:20PM -0500, Robert Haas wrote: Here's yet another case where the current rules are thoroughly disagreeable. rhaas=# create or replace function z(smallint) returns smallint as $$select $1+1$$ language sql; ERROR: return type mismatch in function declared to return smallint DETAIL: Actual return type is integer. CONTEXT: SQL function z So cast the result from an integer to a smallint. What's the big deal? But, OK, I'll do it your way: rhaas=# create or replace function z(smallint) returns smallint as $$select $1+1::smallint$$ language sql; CREATE FUNCTION rhaas=# select z(1); ERROR: function z(integer) does not exist LINE 1: select z(1); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. Come on, really? Note that the above example works without casts if you use int *or* bigint *or* numeric, but not smallint. That could be fixed by causing sufficiently-small integers to lex as smallints, but if you think implicit datatype coercions are evil, you ought to be outraged by the fact that we are already going out of our way to blur the line between int, bigint, and numeric. We let people write 2.0 + 3 and get 5.0 - surely it's only a short step from there to human sacrifice, cats and dogs living together... mass hysteria! I mean, the whole reason for rejecting integer = text is that we aren't sure whether to coerce the text to an integer or the integer to a string, and it's better to throw an error than to guess. But in the case of 2.0 + 3, we feel 100% confident in predicting that the user will be happy to convert the integer to a numeric rather than the numeric to an integer, so no error. We do that because we know that the domain of numeric is a superset of the domain of integer, or in other words, we are using context clues to deduce what the user probably meant rather than forcing them to be explicit about it. Is there any general interest in adjusting smallint casting? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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_restore ignores PGDATABASE
On Sun, Feb 19, 2012 at 10:25:55AM -0500, Andrew Dunstan wrote: On 02/19/2012 08:02 AM, Robert Haas wrote: On Sun, Feb 19, 2012 at 1:18 AM, Erik Rijkerse...@xs4all.nl wrote: On Sun, February 19, 2012 06:27, Robert Haas wrote: On Sat, Feb 18, 2012 at 11:58 AM, Erik Rijkerse...@xs4all.nl wrote: pg_restore ignores environment variable PGDATABASE. What exactly do you mean by ignores? pg_restore prints results to standard output unless a database name is specified. AFAIK, there's no syntax to say I want a direct-to-database restore to whatever you think the default database is. That's right, and that seems contradictory with: This utility [pg_restore], like most other PostgreSQL utilities, also uses the environment variables supported by libpq (see Section 31.13). as pg_restore does 'ignore' (for want of a better word) PGDATABASE. But I think I can conclude from your reply that that behaviour is indeed intentional. It is, because we want there to be a way of converting a custom or tar format archive back to text. I think that probably works out for the best anyway, since pg_restore is a sufficiently dangerous operation that you want to be darn sure you're not doing it on the wrong database. dropdb also requires a database name, while createdb does not, for similar reasons... Right, I think we probably need to adjust the docs slightly to match this reality. Done, with the attached patch. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml new file mode 100644 index bc3d2b7..b276da6 *** a/doc/src/sgml/ref/pg_restore.sgml --- b/doc/src/sgml/ref/pg_restore.sgml *** *** 686,692 para This utility, like most other productnamePostgreSQL/ utilities, also uses the environment variables supported by applicationlibpq/ !(see xref linkend=libpq-envars). /para /refsect1 --- 686,693 para This utility, like most other productnamePostgreSQL/ utilities, also uses the environment variables supported by applicationlibpq/ !(see xref linkend=libpq-envars). However, it does not read !envarPGDATABASE/envar when a database name is not supplied. /para /refsect1 -- 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] Optimize referential integrity checks (todo item)
On 27 August 2012 19:09, Bruce Momjian br...@momjian.us wrote: Any status on this? Tom took care of it in the last commitfest - http://archives.postgresql.org/pgsql-hackers/2012-06/msg01075.php I think that todo item can now be marked as done. Regards, Dean -- 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] Useless removal of duplicate GIN index entries in pg_trgm
Fujii Masao masao.fu...@gmail.com writes: After pg_trgm extracts the trigrams as GIN index keys, generate_trgm() removes duplicate index keys, to avoid generating redundant index entries. Also ginExtractEntries() which is the caller of pg_trgm does the same thing. Why do we need to remove GIN index entries twice? I think that we can get rid of the removal-of-duplicate code block from generate_trgm() because it's useless. Comments? I see eight different callers of generate_trgm(). It might be that gin_extract_value_trgm() doesn't really need this behavior, but that doesn't mean the other seven don't want it. Also, seeing that generate_trgm() is able to use relatively cheap trigram-specific comparison operators for this, it's not impossible that getting rid of duplicates internal to it is a net savings even for the gin_extract_value case, because it'd reduce the number of much-more-heavyweight comparisons done by ginExtractEntries... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wal_buffers
Added to TODO: Allow reporting of stalls due to wal_buffer wrap-around http://archives.postgresql.org/pgsql-hackers/2012-02/msg00826.php --- On Sun, Feb 19, 2012 at 12:24:12AM -0500, Robert Haas wrote: Just for kicks, I ran two 30-minute pgbench tests at scale factor 300 tonight on Nate Boley's machine, with -n -l -c 32 -j 32. The configurations were identical, except that on one of them, I set wal_buffers=64MB. It seemed to make quite a lot of difference: wal_buffers not set (thus, 16MB): tps = 3162.594605 (including connections establishing) wal_buffers=64MB: tps = 6164.194625 (including connections establishing) Rest of config: shared_buffers = 8GB, maintenance_work_mem = 1GB, synchronous_commit = off, checkpoint_segments = 300, checkpoint_timeout = 15min, checkpoint_completion_target = 0.9, wal_writer_delay = 20ms 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: we can't insert more WAL because the buffer we need to use still contains WAL that hasn't yet been fsync'd, leading to long stalls. More buffer space ameliorates the problem. This is not very surprising, when you think about it: it's clear that the peak tps rate approaches 18k/s on these tests; right after a checkpoint, every update will force a full page write - that is, a WAL record 8kB. So we'll fill up a 16MB WAL segment in about a tenth of a second. That doesn't leave much breathing room. I think we might want to consider adjusting our auto-tuning formula for wal_buffers to allow for a higher cap, although this is obviously not enough data to draw any firm conclusions. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Optimize referential integrity checks (todo item)
On Mon, Aug 27, 2012 at 08:35:00PM +0100, Dean Rasheed wrote: On 27 August 2012 19:09, Bruce Momjian br...@momjian.us wrote: Any status on this? Tom took care of it in the last commitfest - http://archives.postgresql.org/pgsql-hackers/2012-06/msg01075.php I think that todo item can now be marked as done. Is there a TODO item for this? https://wiki.postgresql.org/wiki/Todo -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 search query is not executing in Postgres DB
Bruce Momjian br...@momjian.us writes: On Fri, Feb 17, 2012 at 02:52:20PM -0500, Robert Haas wrote: Come on, really? Note that the above example works without casts if you use int *or* bigint *or* numeric, but not smallint. That could be fixed by causing sufficiently-small integers to lex as smallints, Is there any general interest in adjusting smallint casting? We tried that once, years ago, and it was a miserable failure: it opened up far too many ambiguities, eg should int4col + 1 invoke int4pl or int42pl? (That particular case works, because there's an exact match to int42pl, but we found an awful lot of cases where the parser couldn't resolve a best choice. IIRC there were dozens of failures in the regression tests then, and there would be more now.) There's also the problem that if 2 + 2 starts getting parsed as smallint int2pl smallint, cases like 2 + 2 will overflow when they didn't before. IMO smallint is a bit too narrow to be a useful general-purpose integer type, so we'd end up wanting int2pl to yield int4 to avoid unexpected overflows --- and that opens up more cans of worms, like which version of f() gets called for f(2+2). It's conceivable that a change in the lexer behavior combined with a massive reorganization of the integer-related operators would bring us to a nicer place than where we are now. But it'd be a lot of work for dubious reward, and it would almost certainly generate a pile of application compatibility problems. Some history: http://archives.postgresql.org/pgsql-hackers/2002-11/msg00468.php http://archives.postgresql.org/pgsql-hackers/2010-09/msg00223.php (A lot of the specific details in the 2002 thread are obsolete now, but the general point remains, I fear.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimize referential integrity checks (todo item)
On 27 August 2012 20:42, Bruce Momjian br...@momjian.us wrote: On Mon, Aug 27, 2012 at 08:35:00PM +0100, Dean Rasheed wrote: On 27 August 2012 19:09, Bruce Momjian br...@momjian.us wrote: Any status on this? Tom took care of it in the last commitfest - http://archives.postgresql.org/pgsql-hackers/2012-06/msg01075.php I think that todo item can now be marked as done. Is there a TODO item for this? https://wiki.postgresql.org/wiki/Todo It's listed under https://wiki.postgresql.org/wiki/Todo#Referential_Integrity I think the main points mentioned there have now all been taken care of. Regards, Dean -- 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 search query is not executing in Postgres DB
On Mon, Aug 27, 2012 at 04:03:05PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: On Fri, Feb 17, 2012 at 02:52:20PM -0500, Robert Haas wrote: Come on, really? Note that the above example works without casts if you use int *or* bigint *or* numeric, but not smallint. That could be fixed by causing sufficiently-small integers to lex as smallints, Is there any general interest in adjusting smallint casting? We tried that once, years ago, and it was a miserable failure: it opened up far too many ambiguities, eg should int4col + 1 invoke int4pl or int42pl? (That particular case works, because there's an exact match to int42pl, but we found an awful lot of cases where the parser couldn't resolve a best choice. IIRC there were dozens of failures in the regression tests then, and there would be more now.) There's also the problem that if 2 + 2 starts getting parsed as smallint int2pl smallint, cases like 2 + 2 will overflow when they didn't before. IMO smallint is a bit too narrow to be a useful general-purpose integer type, so we'd end up wanting int2pl to yield int4 to avoid unexpected overflows --- and that opens up more cans of worms, like which version of f() gets called for f(2+2). It's conceivable that a change in the lexer behavior combined with a massive reorganization of the integer-related operators would bring us to a nicer place than where we are now. But it'd be a lot of work for dubious reward, and it would almost certainly generate a pile of application compatibility problems. Some history: http://archives.postgresql.org/pgsql-hackers/2002-11/msg00468.php http://archives.postgresql.org/pgsql-hackers/2010-09/msg00223.php (A lot of the specific details in the 2002 thread are obsolete now, but the general point remains, I fear.) Thanks, just asking. Odd int2 is so much harder than int8/numberic casts. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Optimize referential integrity checks (todo item)
On Mon, Aug 27, 2012 at 09:10:35PM +0100, Dean Rasheed wrote: On 27 August 2012 20:42, Bruce Momjian br...@momjian.us wrote: On Mon, Aug 27, 2012 at 08:35:00PM +0100, Dean Rasheed wrote: On 27 August 2012 19:09, Bruce Momjian br...@momjian.us wrote: Any status on this? Tom took care of it in the last commitfest - http://archives.postgresql.org/pgsql-hackers/2012-06/msg01075.php I think that todo item can now be marked as done. Is there a TODO item for this? https://wiki.postgresql.org/wiki/Todo It's listed under https://wiki.postgresql.org/wiki/Todo#Referential_Integrity I think the main points mentioned there have now all been taken care of. Ah, got it. Marked as done. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] FATAL: bogus data in lock file postmaster.pid:
Bruce Momjian br...@momjian.us writes: I have developed the attached patch to report a zero-length file, as you suggested. DIRECTORY_LOCK_FILE is entirely incorrect there. Taking a step back, I don't think this message is much better than the existing behavior of reporting bogus data. Either way, it's not obvious to typical users what the problem is or what to do about it. If we're going to emit a special message I think it should be more user friendly than this. Perhaps something like: FATAL: lock file foo is empty HINT: This may mean that another postmaster was starting at the same time. If not, remove the lock file and try again. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Intermittent regression test failures from index-only plan changes
On Mon, Aug 27, 2012 at 9:18 AM, Bruce Momjian br...@momjian.us wrote: Was this addressed? See commit d6d5f67b5b98b1685f9158e9d00a726afb2ae789. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Optimize referential integrity checks (todo item)
Bruce Momjian br...@momjian.us writes: On Mon, Aug 27, 2012 at 09:10:35PM +0100, Dean Rasheed wrote: It's listed under https://wiki.postgresql.org/wiki/Todo#Referential_Integrity I think the main points mentioned there have now all been taken care of. Ah, got it. Marked as done. IMO the second point is done but the first is not: there's still a question of whether we could remove the trigger-time checks for equality now that there's an upstream filter. Possibly break the TODO entry in two so that you can properly show what's done. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimize referential integrity checks (todo item)
On Mon, Aug 27, 2012 at 04:37:25PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: On Mon, Aug 27, 2012 at 09:10:35PM +0100, Dean Rasheed wrote: It's listed under https://wiki.postgresql.org/wiki/Todo#Referential_Integrity I think the main points mentioned there have now all been taken care of. Ah, got it. Marked as done. IMO the second point is done but the first is not: there's still a question of whether we could remove the trigger-time checks for equality now that there's an upstream filter. Possibly break the TODO entry in two so that you can properly show what's done. OK, can someone do this for me? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] [GENERAL] Why extract( ... from timestamp ) is not immutable?
Bruce Momjian br...@momjian.us writes: On Wed, Jan 25, 2012 at 11:30:49AM -0500, Tom Lane wrote: hubert depesz lubaczewski dep...@depesz.com writes: anyway - the point is that in \df date_part(, timestamp) says it's immutable, while it is not. Hmm, you're right. I thought we'd fixed that way back when, but obviously not. Or maybe the current behavior of the epoch case postdates that. Has this been addressed? Yes: Author: Tom Lane t...@sss.pgh.pa.us Branch: master Release: REL9_2_BR [0d9819f7e] 2012-04-10 12:04:42 -0400 Measure epoch of timestamp-without-time-zone from local not UTC midnight. This patch reverts commit 191ef2b407f065544ceed5700e42400857d9270f and thereby restores the pre-7.3 behavior of EXTRACT(EPOCH FROM timestamp-without-tz). Per discussion, the more recent behavior was misguided on a couple of grounds: it makes it hard to get a non-timezone-aware epoch value for a timestamp, and it makes this one case dependent on the value of the timezone GUC, which is incompatible with having timestamp_part() labeled as immutable. The other behavior is still available (in all releases) by explicitly casting the timestamp to timestamp with time zone before applying EXTRACT. This will need to be called out as an incompatible change in the 9.2 release notes. Although having mutable behavior in a function marked immutable is clearly a bug, we're not going to back-patch such a change. The description of this in the 9.2 release notes could perhaps use some refinement though. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Timing overhead and Linux clock sources
On Mon, Aug 27, 2012 at 1:18 PM, Bruce Momjian br...@momjian.us wrote: He wrote it that way to allow for simpler C code --- he could just start from 31 and keeping skipping entries until he hit a non-zero. My format makes it easy to see which line should have the majority of the entries, e.g. first line should be 90%. I doubt there are enough people running this cross-version that consistency in output makes any difference between major PG versions. I don't see why it's better for the first line to have a big number than the last line. What difference does it make? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] PGDay Ecuador 2012: Call for papers
Hi, This year Ecuador's PGDay will be held at Quito city on November 17th. We are currently accepting talks/workshops. These could go from basic to advanced. We do not require academic-like papers. If you are doing something interesting with PostgreSQL, please send a proposal. It's not necessary for you to be a hacker. If you are using PostgreSQL in your project you could share why you choose it instead of some of the commercial options. Or if you faced performance problems and learnt how to solve them or if you use PostgreSQL-related tools (pgBouncer, pgPool, slony, skytools, pgbarman, etc) to solve specific problems you could share about that. Maybe you migrated from another DBMS so can share about the details. Both, users and developers are welcome to share their experience- Please, let us know about your proposal until October 10th and we will let you know if it was approved until October 20th. -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- 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] archive_keepalive_command
On Mon, Aug 27, 2012 at 9:48 AM, Bruce Momjian br...@momjian.us wrote: Where are we on this? It didn't make it into 9.2, and the patch hasn't been resubmitted for 9.3. It's still not really 100% clear to me what problem it lets us solve that we can't solve otherwise. Maybe that is just a question of adding documentation; I don't know. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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 search query is not executing in Postgres DB
On Mon, Aug 27, 2012 at 4:03 PM, Tom Lane t...@sss.pgh.pa.us wrote: Bruce Momjian br...@momjian.us writes: On Fri, Feb 17, 2012 at 02:52:20PM -0500, Robert Haas wrote: Come on, really? Note that the above example works without casts if you use int *or* bigint *or* numeric, but not smallint. That could be fixed by causing sufficiently-small integers to lex as smallints, Is there any general interest in adjusting smallint casting? We tried that once, years ago, and it was a miserable failure: it opened up far too many ambiguities, eg should int4col + 1 invoke int4pl or int42pl? (That particular case works, because there's an exact match to int42pl, but we found an awful lot of cases where the parser couldn't resolve a best choice. IIRC there were dozens of failures in the regression tests then, and there would be more now.) There's also the problem that if 2 + 2 starts getting parsed as smallint int2pl smallint, cases like 2 + 2 will overflow when they didn't before. IMO smallint is a bit too narrow to be a useful general-purpose integer type, so we'd end up wanting int2pl to yield int4 to avoid unexpected overflows --- and that opens up more cans of worms, like which version of f() gets called for f(2+2). I agree that redefining the lexer behavior is a can of worms. What I don't understand is why f(2+2) can't call f(smallint) when that's the only extant f. It seems to me that we could do that without breaking anything that works today: if you look for candidates and don't find any, try again, allowing assignment casts the second time. We really ought to put some effort into solving this problem. I've seen a few Oracle-migration talks at conferences, and *every one* of them has mentioned the smallint problem. It hits our customers, too. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Bug in pg_env.bat from one-click installer
Hello all, the EDB one-click installer has a slightly annoying bug in its pg_env.bat script: @SET PATH=C:\Program Files\PostgreSQL\9.1\bin;%PATH% PATH entries should not be quoted. As it is, every time a program is started from this path, I get a message along the lines of could not find a ... to execute , with the name of the program in place of the Nevertheless, the program starts fine (interactively), but with the default locale. If I put the unquoted directory into PATH instead, the message does not appear, and the messages are translated. -- Christian -- 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] FATAL: bogus data in lock file postmaster.pid:
On Mon, Aug 27, 2012 at 4:29 PM, Tom Lane t...@sss.pgh.pa.us wrote: Bruce Momjian br...@momjian.us writes: I have developed the attached patch to report a zero-length file, as you suggested. DIRECTORY_LOCK_FILE is entirely incorrect there. Taking a step back, I don't think this message is much better than the existing behavior of reporting bogus data. Either way, it's not obvious to typical users what the problem is or what to do about it. If we're going to emit a special message I think it should be more user friendly than this. Perhaps something like: FATAL: lock file foo is empty HINT: This may mean that another postmaster was starting at the same time. If not, remove the lock file and try again. The problem with this is that it gives the customer only one remedy, which they will (if experience is any guide) try whether it is actually correct to do so or not. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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 support patch
On Mon, Aug 27, 2012 at 1:50 PM, Pavel Stehule pavel.steh...@gmail.com wrote: I can't agree - why we need a some simple solution based on tools, that are available now? I don't think we have to be hurry in support own proprietary solutions - when isn't difficult do it just with available tools now. Who said anything about proprietary solutions? I would agree that it is POSSIBLE to do this with the tools that are available now. I am not sure that I'd agree that it is easy. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] FATAL: bogus data in lock file postmaster.pid:
Excerpts from Robert Haas's message of lun ago 27 18:02:06 -0400 2012: On Mon, Aug 27, 2012 at 4:29 PM, Tom Lane t...@sss.pgh.pa.us wrote: Bruce Momjian br...@momjian.us writes: I have developed the attached patch to report a zero-length file, as you suggested. DIRECTORY_LOCK_FILE is entirely incorrect there. Taking a step back, I don't think this message is much better than the existing behavior of reporting bogus data. Either way, it's not obvious to typical users what the problem is or what to do about it. If we're going to emit a special message I think it should be more user friendly than this. Perhaps something like: FATAL: lock file foo is empty HINT: This may mean that another postmaster was starting at the same time. If not, remove the lock file and try again. The problem with this is that it gives the customer only one remedy, which they will (if experience is any guide) try whether it is actually correct to do so or not. How about having it sleep for a short while, then try again? I would expect that it would cause the second postmaster to fail during the second try, which is okay because the first one is then operational. The problem, of course, is how long to sleep so that this doesn't fail when load is high enough that the first postmaster still hasn't written the file after the sleep. Maybe LOG: lock file foo is empty, sleeping to retry -- sleep 100ms and recheck LOG: lock file foo is empty, sleeping to retry -- sleep, dunno, 1s, recheck LOG: lock file foo is empty, sleeping to retry -- sleep maybe 5s? recheck FATAL: lock file foo is empty HINT: Is another postmaster running on data directory bar? -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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 search query is not executing in Postgres DB
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Tom Lane replied: Come on, really? Note that the above example works without casts if you use int *or* bigint *or* numeric, but not smallint. That could be fixed by causing sufficiently-small integers to lex as smallints, Is there any general interest in adjusting smallint casting? ... It's conceivable that a change in the lexer behavior combined with a massive reorganization of the integer-related operators would bring us to a nicer place than where we are now. But it'd be a lot of work for dubious reward, and it would almost certainly generate a pile of application compatibility problems. Okay, but what about a more targeted solution to the original poster's problem? That seems doable without causing major breakage elsewhere - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201208271818 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAlA78m0ACgkQvJuQZxSWSshW2gCg1Xcx5zLORMIDQo2yE6QTLVuD P88AniE9rh4Dojg0o416cWK7cYHWaq0b =NOAR -END PGP SIGNATURE- -- 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] Timing overhead and Linux clock sources
On Mon, Aug 27, 2012 at 04:42:34PM -0400, Robert Haas wrote: On Mon, Aug 27, 2012 at 1:18 PM, Bruce Momjian br...@momjian.us wrote: He wrote it that way to allow for simpler C code --- he could just start from 31 and keeping skipping entries until he hit a non-zero. My format makes it easy to see which line should have the majority of the entries, e.g. first line should be 90%. I doubt there are enough people running this cross-version that consistency in output makes any difference between major PG versions. I don't see why it's better for the first line to have a big number than the last line. What difference does it make? When you are looking at that output, the 1 usec is where you want to see most of the percentage, and it trails off after that. Here is an example from the current output format: Histogram of timing durations: usec: count percent 16: 3 0.7% 8:563 0.01357% 4: 3241 0.07810% 2:2990371 72.05956% 1:1155682 27.84870% That first line is pretty meaningless. You have to look at the last line, see that only 27% of 1, then look up to see that 72% is 12, which isn't good. My format shows: usec % of total count 1 27.848701155682 2 72.059562990371 4 0.07810 3241 8 0.01357563 16 0.7 3 First line, 27%, that's a problem, look down for more details. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Incorrect behaviour when using a GiST index on points
I need someone to review this patch for 9.3. We have already missed
fixing this for 9.2.
---
On Thu, Jun 21, 2012 at 10:53:43PM +0400, Alexander Korotkov wrote:
On Wed, Feb 22, 2012 at 5:56 PM, Alexander Korotkov aekorot...@gmail.com
wrote:
Attached patch fixes GiST behaviour without altering operators behaviour.
I think we definitely should apply this patch before 9.2 release, because it
is
a bug fix. Otherwise people will continue produce incorrect GiST indexes with
in-core geometrical opclasses until 9.3. Patch is very simple and only changes
few lines of code.
Any thoughts?
--
With best regards,
Alexander Korotkov.
*** a/src/backend/access/gist/gistproc.c
--- b/src/backend/access/gist/gistproc.c
***
*** 836,842 gist_box_picksplit(PG_FUNCTION_ARGS)
}
/*
! * Equality method
*
* This is used for both boxes and points.
*/
--- 836,843
}
/*
! * Equality method. Returns true only when boxes are exact same. We can't
! * ignore small extents because of index consistency.
*
* This is used for both boxes and points.
*/
***
*** 848,856 gist_box_same(PG_FUNCTION_ARGS)
bool *result = (bool *) PG_GETARG_POINTER(2);
if (b1 b2)
! *result = DatumGetBool(DirectFunctionCall2(box_same,
!
PointerGetDatum(b1),
!
PointerGetDatum(b2)));
else
*result = (b1 == NULL b2 == NULL) ? TRUE : FALSE;
PG_RETURN_POINTER(result);
--- 849,857
bool *result = (bool *) PG_GETARG_POINTER(2);
if (b1 b2)
! *result = (b1-low.x == b2-low.x b1-low.y == b2-low.y
!b1-high.x == b2-high.x b1-high.y ==
b2-high.y)
! ? TRUE : FALSE;
else
*result = (b1 == NULL b2 == NULL) ? TRUE : FALSE;
PG_RETURN_POINTER(result);
***
*** 1326,1331 gist_point_consistent(PG_FUNCTION_ARGS)
--- 1327,1333
bool *recheck = (bool *) PG_GETARG_POINTER(4);
boolresult;
StrategyNumber strategyGroup = strategy / GeoStrategyNumberOffset;
+ BOX*query, *key;
switch (strategyGroup)
{
***
*** 1337,1348 gist_point_consistent(PG_FUNCTION_ARGS)
*recheck = false;
break;
case BoxStrategyNumberGroup:
! result = DatumGetBool(DirectFunctionCall5(
!
gist_box_consistent,
!
PointerGetDatum(entry),
!
PG_GETARG_DATUM(1),
!
Int16GetDatum(RTOverlapStrategyNumber),
!
0, PointerGetDatum(recheck)));
break;
case PolygonStrategyNumberGroup:
{
--- 1339,1356
*recheck = false;
break;
case BoxStrategyNumberGroup:
! /*
! * This code repeats logic of on_ob which uses simple
comparison
! * rather than FP* functions.
! */
! query = PG_GETARG_BOX_P(1);
! key = DatumGetBoxP(entry-key);
!
! *recheck = false;
! result = key-high.x = query-low.x
! key-low.x = query-high.x
! key-high.y = query-low.y
! key-low.y = query-high.y;
break;
case PolygonStrategyNumberGroup:
{
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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 search query is not executing in Postgres DB
Robert Haas robertmh...@gmail.com writes: I agree that redefining the lexer behavior is a can of worms. What I don't understand is why f(2+2) can't call f(smallint) when that's the only extant f. It seems to me that we could do that without breaking anything that works today: if you look for candidates and don't find any, try again, allowing assignment casts the second time. Yeah, possibly. Where would you fit that in the existing sequence of tests? http://www.postgresql.org/docs/devel/static/typeconv-func.html regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FATAL: bogus data in lock file postmaster.pid:
Robert Haas robertmh...@gmail.com writes: On Mon, Aug 27, 2012 at 4:29 PM, Tom Lane t...@sss.pgh.pa.us wrote: Perhaps something like: FATAL: lock file foo is empty HINT: This may mean that another postmaster was starting at the same time. If not, remove the lock file and try again. The problem with this is that it gives the customer only one remedy, which they will (if experience is any guide) try whether it is actually correct to do so or not. Which other remedy(s) do you think the hint should suggest? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FATAL: bogus data in lock file postmaster.pid:
Alvaro Herrera alvhe...@2ndquadrant.com writes: How about having it sleep for a short while, then try again? I could get behind that, but I don't think the delay should be more than 100ms or so. It's important for the postmaster to acquire the lock (or not) pretty quickly, or pg_ctl is going to get confused. If we keep it short, we can also dispense with the log spam you were suggesting. (Actually, I wonder if this type of scenario isn't going to confuse pg_ctl already --- it might think the lockfile belongs to the postmaster *it* started, not some pre-existing one. Does that matter?) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Incorrect behaviour when using a GiST index on points
Bruce Momjian br...@momjian.us writes: I need someone to review this patch for 9.3. We have already missed fixing this for 9.2. So put it in the next commitfest. FWIW, I looked at this last week, and concluded I didn't have enough confidence in it to push it into 9.2 at the last minute. There's also the big-picture question of whether we should just get rid of fuzzy comparisons in the geometric types instead of trying to hack indexes to work around them. I'd really rather have us debate that question and resolve it one way or the other before spending time on the details of patches that take the second approach. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimize referential integrity checks (todo item)
Bruce Momjian br...@momjian.us writes: On Mon, Aug 27, 2012 at 04:37:25PM -0400, Tom Lane wrote: IMO the second point is done but the first is not: there's still a question of whether we could remove the trigger-time checks for equality now that there's an upstream filter. Possibly break the TODO entry in two so that you can properly show what's done. OK, can someone do this for me? Done. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Tablefunc crosstab error messages
Hi All,
I was recently struggling with getting a large crosstab (
http://www.postgresql.org/docs/current/static/tablefunc.html) query to work
and noticed the error messages were not super helpful: return and sql
tuple descriptions are incompatible.
I had to manually take the query into pieces and check each type, because
it wouldn't tell me which types were not matching. This is not fun
especially when you're using an ORM and composing queries piece by piece.
Reading into contrib/tablefunc/tablefunc.c, it looks like this shouldn't be
too hard to fix.
crosstab() calls compatCrosstabTupleDescs():
1545 /*
1546 * - attribute [1] of the sql tuple is the category; no need to
check it -
1547 * attribute [2] of the sql tuple should match attributes [1] to
[natts]
1548 * of the return tuple
1549 */
1550 sql_attr = sql_tupdesc-attrs[2];
1551 for (i = 1; i ret_tupdesc-natts; i++)
1552 {
1553 ret_attr = ret_tupdesc-attrs[i];
1554
1555 if (ret_attr-atttypid != sql_attr-atttypid)
1556 return false;
1557 }
Returning the type information to the caller seems like a pain
but compatCrosstabTupleDescs already has instances in it where it fails
with an error message, so I propose we just do that and tell the user the
expected and actual types here, instead of returning false here and
throwing a generic error message in the caller.
What do you think? Let me know so I can write up a patch for you guys.
Also, just curious, is the reason the query passed into crosstab is a
string (rather than an SQL expression) that it's just easier to implement
that way?
Cheers,
~Mali
Re: [HACKERS] FATAL: bogus data in lock file postmaster.pid:
On Mon, Aug 27, 2012 at 07:39:35PM -0400, Tom Lane wrote:
Alvaro Herrera alvhe...@2ndquadrant.com writes:
How about having it sleep for a short while, then try again?
I could get behind that, but I don't think the delay should be more than
100ms or so. It's important for the postmaster to acquire the lock (or
not) pretty quickly, or pg_ctl is going to get confused. If we keep it
short, we can also dispense with the log spam you were suggesting.
(Actually, I wonder if this type of scenario isn't going to confuse
pg_ctl already --- it might think the lockfile belongs to the postmaster
*it* started, not some pre-existing one. Does that matter?)
I took Alvaro's approach of a sleep. The file test was already in a
loop that went 100 times. Basically, if the lock file exists, this
postmaster isn't going to succeed, so I figured there is no reason to
rush in the testing. I gave it 5 tries with one second between
attempts. Either the file is being populated, or it is stale and empty.
I checked pg_ctl and that has a default wait of 60 second, so 5 seconds
to exit out of the postmaster should be fine.
Patch attached.
FYI, I noticed we have a similar 5-second creation time requirement in
pg_ctl:
/*
* The postmaster should create postmaster.pid very soon after being
* started. If it's not there after we've waited 5 or more seconds,
* assume startup failed and give up waiting. (Note this covers both
* cases where the pidfile was never created, and where it was created
* and then removed during postmaster exit.) Also, if there *is* a
* file there but it appears stale, issue a suitable warning and give
* up waiting.
*/
if (i = 5)
This is for the case where the file has an old pid, rather than it is
empty.
FYI, I fixed the filename problem Tom found.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c
new file mode 100644
index 775d71f..0309494
*** a/src/backend/utils/init/miscinit.c
--- b/src/backend/utils/init/miscinit.c
*** CreateLockFile(const char *filename, boo
*** 766,771
--- 766,793
filename)));
close(fd);
+ if (len == 0)
+ {
+ /*
+ * An empty lock file exits; either is it from another postmaster
+ * that is still starting up, or left from a crash. Check for
+ * five seconds, then if it still empty, it must be from a crash,
+ * so fail and recommend lock file removal.
+ */
+ if (ntries 5)
+ {
+ sleep(1);
+ continue;
+ }
+ else
+ ereport(FATAL,
+ (errcode(ERRCODE_LOCK_FILE_EXISTS),
+ errmsg(lock file \%s\ is empty, filename),
+ errhint(
+ Empty lock file probably left from operating system crash during\n
+ database startup; file deletion suggested.)));
+ }
+
buffer[len] = '\0';
encoded_pid = atoi(buffer);
--
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] FATAL: bogus data in lock file postmaster.pid:
Bruce Momjian br...@momjian.us writes: On Mon, Aug 27, 2012 at 07:39:35PM -0400, Tom Lane wrote: I could get behind that, but I don't think the delay should be more than 100ms or so. I took Alvaro's approach of a sleep. The file test was already in a loop that went 100 times. Basically, if the lock file exists, this postmaster isn't going to succeed, so I figured there is no reason to rush in the testing. I gave it 5 tries with one second between attempts. Either the file is being populated, or it is stale and empty. How did 100ms translate to 5 seconds? I checked pg_ctl and that has a default wait of 60 second, so 5 seconds to exit out of the postmaster should be fine. pg_ctl is not the only consideration here. In particular, there are a lot of initscripts out there (all of Red Hat's, for instance) that don't use pg_ctl and expect the postmaster to come up (or not) in a couple of seconds. I don't see a need for more than about one retry with 100ms delay. There is no evidence that the case we're worried about has ever occurred in the real world anyway, so slowing down error failures to make really really really sure there's not a competing postmaster doesn't seem like a good tradeoff. I'm not terribly impressed with that errhint, either. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CREATE SCHEMA IF NOT EXISTS
Hello, I reviewed this v5 of patch: - https://commitfest.postgresql.org/action/patch_view?id=907 The patch is small and implements a new syntax to CREATE SCHEMA that allow the creation of a schema be skipped when IF NOT EXISTS is used. It was applied to 483c2c1071c45e275782d33d646c3018f02f9f94 with two hunks offset, was compiled without errors or new warnings and pass all tests, even the tests that covers the expected results for it self. The docs was updated with the information about the expected behavior. I tested against ambiguity, i. e. creating a schema with name 'if' and got the expected results when try to create it if not exists. Two questions: - Should this patch implements others INEs like ADD COLUMN IF NOT EXISTS? - Should pg_dump or pg_restore support some kind of flag to use a CREATE SCHEMA IF NOT EXISTS ... instead CREATE SCHEMA ...? Regards, -- Dickson S. Guedes mail/xmpp: gue...@guedesoft.net - skype/twitter: guediz ~ github.com/guedes http://guedesoft.net ~ http://www.postgresql.org.br create_schema_if_not_exists_v5.patch Description: Binary data -- 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] FATAL: bogus data in lock file postmaster.pid:
On Mon, Aug 27, 2012 at 09:59:10PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: On Mon, Aug 27, 2012 at 07:39:35PM -0400, Tom Lane wrote: I could get behind that, but I don't think the delay should be more than 100ms or so. I took Alvaro's approach of a sleep. The file test was already in a loop that went 100 times. Basically, if the lock file exists, this postmaster isn't going to succeed, so I figured there is no reason to rush in the testing. I gave it 5 tries with one second between attempts. Either the file is being populated, or it is stale and empty. How did 100ms translate to 5 seconds? That was the no need to rush, let's just be sure of what we report. I checked pg_ctl and that has a default wait of 60 second, so 5 seconds to exit out of the postmaster should be fine. pg_ctl is not the only consideration here. In particular, there are a lot of initscripts out there (all of Red Hat's, for instance) that don't use pg_ctl and expect the postmaster to come up (or not) in a couple of seconds. I don't see a need for more than about one retry with 100ms delay. There is no evidence that the case we're worried about has ever occurred in the real world anyway, so slowing down error failures to make really really really sure there's not a competing postmaster doesn't seem like a good tradeoff. I'm not terribly impressed with that errhint, either. I am concerned at 100ms that we can't be sure if it is still being created, and if we can't be sure, I am not sure there is much point in trying to clarify the odd error message we omit. FYI, here is what the code does now with a zero-length pid file, with my patch: $ postmaster [ wait 5 seconds ] FATAL: lock file postmaster.pid is empty HINT: Empty lock file probably left from operating system crash during database startup; file deletion suggested. $ pg_ctl start pg_ctl: invalid data in PID file /u/pgsql/data/postmaster.pid $ pg_ctl -w start pg_ctl: invalid data in PID file /u/pgsql/data/postmaster.pid Seems pg_ctl would also need some cleanup if we change the error message and/or timing. I am thinking we should just change the error message in the postmaster and pg_ctl to say the file is empty, and call it done (no hint message). If we do want a hint, say that either the file is stale from a crash or another postmaster is starting up, and let the user diagnose it. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Incorrect behaviour when using a GiST index on points
On Mon, Aug 27, 2012 at 07:43:49PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I need someone to review this patch for 9.3. We have already missed fixing this for 9.2. So put it in the next commitfest. FWIW, I looked at this last week, and concluded I didn't have enough confidence in it to push it into 9.2 at the last minute. There's also the big-picture question of whether we should just get rid of fuzzy comparisons in the geometric types instead of trying to hack indexes to work around them. I'd really rather have us debate that question and resolve it one way or the other before spending time on the details of patches that take the second approach. Done. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Incorrect behaviour when using a GiST index on points
On Mon, Aug 27, 2012 at 07:43:49PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I need someone to review this patch for 9.3. We have already missed fixing this for 9.2. So put it in the next commitfest. Done. I have linked to your comment below too. --- FWIW, I looked at this last week, and concluded I didn't have enough confidence in it to push it into 9.2 at the last minute. There's also the big-picture question of whether we should just get rid of fuzzy comparisons in the geometric types instead of trying to hack indexes to work around them. I'd really rather have us debate that question and resolve it one way or the other before spending time on the details of patches that take the second approach. regards, tom lane -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Advisory Lock BIGINT Values
On Fri, Mar 9, 2012 at 04:36:08PM -0800, David E. Wheeler wrote: Hackers, The documentation for pg_locks says that, for BIGINT advisory locks: A bigint key is displayed with its high-order half in the classid column, its low-order half in the objid column I was in need of knowing what the bigint is that is waiting on a lock, and Andrew Dunstan was kind enough to help me out with that. Since other folks might also need it, here’s a doc patch. diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml new file mode 100644 index 9564e01..de1c266 *** a/doc/src/sgml/catalogs.sgml --- b/doc/src/sgml/catalogs.sgml *** *** 7313,7319 A typebigint/type key is displayed with its high-order half in the structfieldclassid/ column, its low-order half in the structfieldobjid/ column, and structfieldobjsubid/ equal !to 1. Integer keys are displayed with the first key in the structfieldclassid/ column, the second key in the structfieldobjid/ column, and structfieldobjsubid/ equal to 2. The actual meaning of the keys is up to the user. Advisory locks are local to each database, --- 7313,7322 A typebigint/type key is displayed with its high-order half in the structfieldclassid/ column, its low-order half in the structfieldobjid/ column, and structfieldobjsubid/ equal !to 1. The original typebigint/type value can be reassembled with the !expression literal(classid::int::bit(64) lt;lt; 32 | !objid::int::bit(64))::bigint/literal. Integer keys are displayed with the !first key in the structfieldclassid/ column, the second key in the structfieldobjid/ column, and structfieldobjsubid/ equal to 2. The actual meaning of the keys is up to the user. Advisory locks are local to each database, Thanks, applied. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Timing overhead and Linux clock sources
On 08/27/2012 06:20 PM, Bruce Momjian wrote: On Mon, Aug 27, 2012 at 04:42:34PM -0400, Robert Haas wrote: I don't see why it's better for the first line to have a big number than the last line. What difference does it make? When you are looking at that output, the1 usec is where you want to see most of the percentage, and it trails off after that. After staring at all the examples I generated again, I think Bruce is right that the newer format he's suggesting is better. I know I never thought about whether reordering for easier interpretation made sense before, and I'd also guess it was less coding for the existing order was the only reason Ants did it that way. Where I think this is a most useful improvement is when comparing two systems with different results that don't end at the same boundary. The proposed formatting would show the good vs. bad examples I put in the docs like this: usec: count percent 1: 27694571 90.23734% 2:2993204 9.75277% 4: 3010 0.00981% 8: 22 0.7% 16: 1 0.0% 32: 1 0.0% usec: count percent 1:1155682 27.84870% 2:2990371 72.05956% 4: 3241 0.07810% 8:563 0.01357% 16: 3 0.7% And I think it's easier to compare those two in that order. The docs do specifically suggest staring at the 1 usec numbers first, and having just mocked up both orders I do prefer this one for that job. The way this was originally written, it's easier to come to an initially misleading conclusion. The fact that the first system sometimes spikes to the 32 usec range is the first thing that jumps out at you in the originally committed ordering, and that's not where people should focus first. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.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] Timing overhead and Linux clock sources
On Mon, Aug 27, 2012 at 11:13:00PM -0400, Greg Smith wrote: On 08/27/2012 06:20 PM, Bruce Momjian wrote: On Mon, Aug 27, 2012 at 04:42:34PM -0400, Robert Haas wrote: I don't see why it's better for the first line to have a big number than the last line. What difference does it make? When you are looking at that output, the1 usec is where you want to see most of the percentage, and it trails off after that. After staring at all the examples I generated again, I think Bruce is right that the newer format he's suggesting is better. I know I never thought about whether reordering for easier interpretation made sense before, and I'd also guess it was less coding for the existing order was the only reason Ants did it that way. Where I think this is a most useful improvement is when comparing two systems with different results that don't end at the same boundary. The proposed formatting would show the good vs. bad examples I put in the docs like this: usec: count percent 1: 27694571 90.23734% 2:2993204 9.75277% 4: 3010 0.00981% 8: 22 0.7% 16: 1 0.0% 32: 1 0.0% usec: count percent 1:1155682 27.84870% 2:2990371 72.05956% 4: 3241 0.07810% 8:563 0.01357% 16: 3 0.7% And I think it's easier to compare those two in that order. The docs do specifically suggest staring at the 1 usec numbers first, and having just mocked up both orders I do prefer this one for that job. The way this was originally written, it's easier to come to an initially misleading conclusion. The fact that the first system sometimes spikes to the 32 usec range is the first thing that jumps out at you in the originally committed ordering, and that's not where people should focus first. Yes, I was totally confused how you would explain how to analyze this. Once the patch is applied you might find it easier to clearly state that in the docs. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Minor comment fixups
I noticed a couple comments that look wrong to me. Patch attached. Regards, Jeff Davis *** a/src/backend/commands/tablecmds.c --- b/src/backend/commands/tablecmds.c *** *** 8784,8792 copy_relation_data(SMgrRelation src, SMgrRelation dst, pfree(buf); /* ! * If the rel isn't temp, we must fsync it down to disk before it's safe ! * to commit the transaction. (For a temp rel we don't care since the rel ! * will be uninteresting after a crash anyway.) * * It's obvious that we must do this when not WAL-logging the copy. It's * less obvious that we have to do it even if we did WAL-log the copied --- 8784,8791 pfree(buf); /* ! * If the rel is WAL-logged, must fsync before commit. We use heap_sync ! * to ensure that the toast table gets fsync'd too. * * It's obvious that we must do this when not WAL-logging the copy. It's * less obvious that we have to do it even if we did WAL-log the copied *** a/src/backend/storage/file/reinit.c --- b/src/backend/storage/file/reinit.c *** *** 337,343 ResetUnloggedRelationsInDbspaceDir(const char *dbspacedirname, int op) copy_file(srcpath, dstpath); } - /* Done with the first pass. */ FreeDir(dbspace_dir); } } --- 337,342 -- 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] Advisory Lock BIGINT Values
Bruce Momjian br...@momjian.us writes: On Fri, Mar 9, 2012 at 04:36:08PM -0800, David E. Wheeler wrote: A typebigint/type key is displayed with its high-order half in the structfieldclassid/ column, its low-order half in the structfieldobjid/ column, and structfieldobjsubid/ equal !to 1. The original typebigint/type value can be reassembled with the !expression literal(classid::int::bit(64) lt;lt; 32 | !objid::int::bit(64))::bigint/literal. Integer keys are displayed with the !first key in the structfieldclassid/ column, the second key in the structfieldobjid/ column, and structfieldobjsubid/ equal to 2. The actual meaning of the keys is up to the user. Advisory locks are local to each database, Thanks, applied. This formula is not actually correct, as you'd soon find out if you experimented with values with the high-order bit of the low-order word set. (Hint: sign extension.) The correct formula is both simpler and far more efficient: (classid::int8 32) | objid::int8 This works because oidtoi8 correctly treats the OID value as unsigned. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wal_buffers
From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Bruce Momjian Added to TODO: Allow reporting of stalls due to wal_buffer wrap-around http://archives.postgresql.org/pgsql-hackers/2012-02/msg00826.php Isn't this indicates that while writing XLOG, it needs some tuning such that when some thresh hold buffers(2/3) are full, then trigger LOGWriter. --- On Sun, Feb 19, 2012 at 12:24:12AM -0500, Robert Haas wrote: Just for kicks, I ran two 30-minute pgbench tests at scale factor 300 tonight on Nate Boley's machine, with -n -l -c 32 -j 32. The configurations were identical, except that on one of them, I set wal_buffers=64MB. It seemed to make quite a lot of difference: wal_buffers not set (thus, 16MB): tps = 3162.594605 (including connections establishing) wal_buffers=64MB: tps = 6164.194625 (including connections establishing) Rest of config: shared_buffers = 8GB, maintenance_work_mem = 1GB, synchronous_commit = off, checkpoint_segments = 300, checkpoint_timeout = 15min, checkpoint_completion_target = 0.9, wal_writer_delay = 20ms 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: we can't insert more WAL because the buffer we need to use still contains WAL that hasn't yet been fsync'd, leading to long stalls. More buffer space ameliorates the problem. This is not very surprising, when you think about it: it's clear that the peak tps rate approaches 18k/s on these tests; right after a checkpoint, every update will force a full page write - that is, a WAL record 8kB. So we'll fill up a 16MB WAL segment in about a tenth of a second. That doesn't leave much breathing room. I think we might want to consider adjusting our auto-tuning formula for wal_buffers to allow for a higher cap, although this is obviously not enough data to draw any firm conclusions. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] emacs configuration for new perltidy settings
On Thu, Jul 12, 2012 at 6:35 AM, Peter Eisentraut pete...@gmx.net wrote: This might be useful for some people. Here is an emacs configuration for perl-mode that is compatible with the new perltidy settings. Note that the default perl-mode settings produce indentation that will be completely shredded by the new perltidy settings. (defun pgsql-perl-style () Perl style adjusted for PostgreSQL project (interactive) (setq tab-width 4) (setq perl-indent-level 4) (setq perl-continued-statement-offset 4) (setq perl-continued-brace-offset 4) (setq perl-brace-offset 0) (setq perl-brace-imaginary-offset 0) (setq perl-label-offset -2)) (add-hook 'perl-mode-hook (lambda () (if (string-match postgresql buffer-file-name) (pgsql-perl-style Cool thanks! Very helpful. -- Michael Paquier http://michael.otacoo.com
