Re: [HACKERS] Record comparison compiler warning
On 10/31/2013 07:51 PM, Kevin Grittner wrote: Bruce Momjian br...@momjian.us wrote: On Wed, Oct 16, 2013 at 11:49:13AM -0700, Kevin Grittner wrote: Bruce Momjian br...@momjian.us wrote: I am seeing this compiler warning in git head: rowtypes.c: In function 'record_image_cmp': rowtypes.c:1433: warning: 'cmpresult' may be used uninitialized in this function rowtypes.c:1433: note: 'cmpresult' was declared here I had not gotten a warning under either gcc or clang, but that was probably because I was doing assert-enabled builds, and the Assert(false) saved me. That seemed a little marginal anyway, so how about this?: Would you please send the file as ASCII, e.g. not: A0A0A0A0A0A0A0A0A0A0A0A0A0A0A0A0A0A0A0 default: Huh, I did not see anything remotely like that in my email or in the archives: http://www.postgresql.org/message-id/1381949353.78943.yahoomail...@web162902.mail.bf1.yahoo.com http://www.postgresql.org/message-id/raw/1381949353.78943.yahoomail...@web162902.mail.bf1.yahoo.com Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fast insertion indexes: why no developments
On 29 October 2013 16:10, Peter Geoghegan p...@heroku.com wrote: On Tue, Oct 29, 2013 at 7:53 AM, Leonardo Francalanci m_li...@yahoo.it wrote: I don't see much interest in insert-efficient indexes. Presumably someone will get around to implementing a btree index insertion buffer one day. I think that would be a particularly compelling optimization for us, because we could avoid ever inserting index tuples that are already dead when the deferred insertion actually occurs. That's pretty much what the LSM-tree is. -- Simon Riggs http://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: Handle LIMIT/OFFSET before select clause (was: [HACKERS] Feature request: optimizer improvement)
On Sat, Nov 2, 2013 at 2:00 AM, Tom Lane t...@sss.pgh.pa.us wrote: Jim Nasby j...@nasby.net writes: On Oct 31, 2013, at 11:04 AM, Joe Love j...@primoweb.com wrote: In postgres 9.2 I have a function that is relatively expensive. When I write a query such as: select expensive_function(o.id),o.* from offeirng o where valid='Y' order by name limit 1; Does anyone know what the SQL standard says about this, if anything? The computational model is that you evaluate the SELECT list before sorting; this must be so since you can write select somefunc(x) as y from tab order by y; In the general case, therefore, it's impossible to avoid evaluating the function at all rows. I'm not sure what the spec says about whether it's okay to skip evaluation of functions that would be evaluated in a naive implementation of the computational model, so it's possible that what the OP is asking for is directly contrary to spec. But more likely they permit implementations to skip unnecessary calls, if indeed they address this at all. As far as PG goes, I think the excess calls would only occur if the plan includes an explicit sort step, since the select list would be evaluated before the sort step. If you've got an index on name (or maybe you'd need (valid, name) if there aren't many rows with valid = 'Y') I'd expect it to pick out the minimal name row with the index, avoiding any sort, and then the function would only be evaluated on the single fetched row. But these are implementation details not anything you're going to find support for in the spec. regards, tom lane Doesnt that make the index mandatory here? If I understand correctly, if an index is present, the sort will be avoided altogether. IMHO, thats avoiding the problem. The question here is that whether we can add planner heuristics for understanding this case and executing the LIMIT part first (before executing the funtion). I understand the reasons for executing SELECT before the sort. But, couldnt we get the planner to see the LIMIT part and push the sort node above the select node for this specific case? So, seeing the LIMIT, the dataset is first sorted, then LIMITed, then the function applied. Is this process possible? Regards, Atri -- Regards, Atri l'apprenant -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] appendPQExpBufferVA vs appendStringInfoVA
On Sat, Nov 2, 2013 at 2:27 PM, Tom Lane t...@sss.pgh.pa.us wrote: David Rowley dgrowle...@gmail.com writes: Tom commited some changes to appendStringInfoVA a few weeks ago which allows it to return the required buffer size if the current buffer is not big enough. On looking at appendPQExpBufferVA I'm thinking it would be nice if it could make use of the new pvsnprintf function to bring the same potential performance improvement in to there too. Uh ... it does contain pretty much the same algorithm now. We can't simply use pvsnprintf there because exit-on-error is no good for libpq's purposes, so unless we want to rethink that, a certain amount of code duplication is unavoidable. But they both understand about C99 vsnprintf semantics now. I only just noticed the changes you made to appendPQExpBufferVA(). I had wondered if making pvsnprintf return int instead of size_t and having it return -1 if there are problems, then letting the caller deal with those, but I'm starting to see why you did it the way you did it... There's also quite a few subtle differences with things like max allocation size that would have to be dealt with differently I guess. I'm low on ideas on how to improve things much around here for now, but for what it's worth, I did create a patch which changes unnecessary calls to appendPQExpBuffer() into calls to appendPQExpBufferStr() similar to the recent one for appendStringInfo and appendStringInfoString. Regards David Rowley regards, tom lane appendPQExpBufferStr_v0.1.patch.gz Description: GNU Zip compressed 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] Record comparison compiler warning
Stefan Kaltenbrunner wrote: On 10/31/2013 07:51 PM, Kevin Grittner wrote: Bruce Momjian br...@momjian.us wrote: On Wed, Oct 16, 2013 at 11:49:13AM -0700, Kevin Grittner wrote: Bruce Momjian br...@momjian.us wrote: Would you please send the file as ASCII, e.g. not: A0A0A0A0A0A0A0A0A0A0A0A0A0A0A0A0A0A0A0 default: Huh, I did not see anything remotely like that in my email or in the archives: http://www.postgresql.org/message-id/1381949353.78943.yahoomail...@web162902.mail.bf1.yahoo.com http://www.postgresql.org/message-id/raw/1381949353.78943.yahoomail...@web162902.mail.bf1.yahoo.com I would blame Bruce's MUA, or surrounding configuration, for this problem. It looks fine in mine, and as far as I can see, Kevin's message correctly declares the email to be in Latin-1 quoted-printable encoding, which declares A0 to mean non-breaking space. Maybe, for instance, Bruce is running Mutt in Latin-1 mode with the terminal set to UTF-8 or some such? -- Á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: Handle LIMIT/OFFSET before select clause (was: [HACKERS] Feature request: optimizer improvement)
Atri Sharma atri.j...@gmail.com writes: I understand the reasons for executing SELECT before the sort. But, couldnt we get the planner to see the LIMIT part and push the sort node above the select node for this specific case? [ Shrug... ] I don't see the point. If the OP actually cares about the speed of this query, he's going to want to avoid the sort step too, which is what makes the index a good idea. More generally, this is not a transformation we could apply unconditionally --- at the very least it'd need to be avoided when volatile functions are involved, and I don't think it's invariably a win from a cost standpoint even if there aren't semantic blockers. But right now the planner has no real ability to reason about placement of SELECT-list evaluation: it's done in a fixed spot in any particular plan structure. I don't think it's practical to add such considerations to the rat's nest that is grouping_planner and friends. I have ambitions to replace all that with a Path-generation-and-comparison approach, and the Paths used for this would need to carry some indication of which expressions would be evaluated where. So maybe once that's done we could think about whether this is worth doing. I remain dubious 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] Record comparison compiler warning
Alvaro Herrera alvhe...@2ndquadrant.com writes: Stefan Kaltenbrunner wrote: http://www.postgresql.org/message-id/raw/1381949353.78943.yahoomail...@web162902.mail.bf1.yahoo.com I would blame Bruce's MUA, or surrounding configuration, for this problem. It looks fine in mine, and as far as I can see, Kevin's message correctly declares the email to be in Latin-1 quoted-printable encoding, which declares A0 to mean non-breaking space. I agree with Bruce: this patch is broken. A0 may be a non-breaking space, but the fact remains that it isn't a space, and since we're talking about a diff, the whitespace needs to be the same as what it is in the original file. (I believe that some of those whitespace runs involve tabs not just spaces, making the diff even more wrong.) Admittedly, if you're just eyeballing it, it might look fine. But try feeding it to patch and you'll find out it ain't. 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] Patch for fail-back without fresh backup
On Fri, Oct 25, 2013 at 8:08 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-10-24 13:51:52 -0700, Josh Berkus wrote: It entirely depends on your workload. If it happens to be something like: INSERT INTO table (lots_of_data); CHECKPOINT; SELECT * FROM TABLE; i.e. there's a checkpoint between loading the data and reading it - not exactly all that uncommon - we'll need to log something for every page. That can be rather noticeable. Especially as I think it will be rather hard to log anything but a real FPI. I really don't think everyone will want this. I am absolutely not against providing an option to log enough information to make pg_rewind work, but I think providing a command to do *safe* *planned* failover will help in many more. I think it is better providing as option to log enough information such as new wal_level. If user doesn't realize until it's too late, such information is contained in checkpoint record? For example if checkpoint record contained information of wal_level then we can inform to user using by such information. BTW this information is useful only for pg_rewind? Is there for anything else? (Sorry it might has already been discussed..) Regards, --- Sawada Masahiko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Add accurate option to pgbench
Logic of this option is under following. 1. execute cluster command to sort records. 2. execute checkpoint to clear dirty-buffers in shared_buffers. 3. execute sync command to clear dirty-file-caches in OS. 4. waiting 10 seconds for raid cache is until empty . 5. execute checkpoint to init checkpoint_timeout and checkpoint_segments. 6. start benchmark. I have similar logic in some of my benchmarking scripts but I don't see a compelling reason to include it in pgbench itself. I agree that this looks more like script material. However I think that part of this interesting checklist and discussion could make it to some caveat section about reproducible performance measures in pgbench documentation, though? -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Add accurate option to pgbench
Fabien COELHO coe...@cri.ensmp.fr writes: However I think that part of this interesting checklist and discussion could make it to some caveat section about reproducible performance measures in pgbench documentation, though? +1. There's already a section of advice about how to get reproducible numbers from pgbench --- we could certainly extend that to cover more things. BTW, even if we were going to put code for these things into pgbench, driving them all off a single switch would be very bad design. I see no reason to think that all and only these issues would be appropriate to control for any particular user of pgbench. 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] gcc 4.8 compiler warning in ecpg check
With gcc 4.8, I get a compiler warning when building the ecpg test files: define.pgc: In function 'main': define.pgc:21:19: warning: typedef 'string' locally defined but not used [-Wunused-local-typedefs] typedef char string[NAMELEN]; ^ This could be removed, but is it something that is actually part of what is being tested? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #8573: int4range memory consumption
Jim Nasby deci...@decibel.org writes:
On Nov 1, 2013, at 2:08 PM, Tom Lane t...@sss.pgh.pa.us wrote:
While we could doubtless hack range_out to release those strings again,
it seems to me that that's just sticking a finger in the dike. I'm
inclined to think that we really ought to solve this class of problems
once and for all by fixing printtup.c to run the output functions in a
temporary memory context,
...
we're already using a reset-per-row approach to memory management of
output function calls in COPY OUT, and I know for a fact that we've
squeezed that code path as hard as we could.
+1. COPY is actually the case I was worried about
if you're dealing with
large amounts of data in other clients ISTM that other things will bottleneck
before the extra memory context.
Attached is a proposed patch for this. It fixes most of the functions
in printtup.c to use a per-row memory context. (I did not bother to
fix debugtup(), which is used only in standalone mode. If you're doing
queries large enough for mem leaks to be problematic in standalone mode,
you're nuts.) I also simplified some other places that had copied the
notion of forced detoasting before an output function call, as that seems
dubious at best, and wasn't being done uniformly anyway.
My original thought had been to get rid of all pfree's of output function
results, so as to make the world safe for returning constant strings when
such functions find it appropriate. However, I ran into a showstopper
problem: SPI_getvalue(), which is little more than a wrapper around the
appropriate type output function, is documented as returning a pfree'able
string. Some though not all of the callers in core and contrib take the
hint and pfree the result, and I'm sure there are more in third-party
extensions. So if we want to allow output functions to return
non-palloc'd strings, it seems we have to either change SPI_getvalue()'s
API contract or insert a pstrdup() call in it. Neither of these is
attractive, mainly because the vast majority of output function results
would still be palloc'd even if we made aggressive use of the option not
to. That means that if we did the former, light testing wouldn't
necessarily show a problem if someone forgot to remove a pfree() after a
SPI_getvalue() call, and it also means that if we did the latter, the
pstrdup() would usually be a waste of cycles and space.
So I've abandoned that idea for now, and just recommend applying the
attached patch as far back as 9.2, where range_out was added.
Comments?
regards, tom lane
diff --git a/src/backend/access/common/printtup.c b/src/backend/access/common/printtup.c
index 8daac9e..0e8c947 100644
*** a/src/backend/access/common/printtup.c
--- b/src/backend/access/common/printtup.c
***
*** 21,26
--- 21,27
#include tcop/pquery.h
#include utils/lsyscache.h
#include utils/memdebug.h
+ #include utils/memutils.h
static void printtup_startup(DestReceiver *self, int operation,
*** typedef struct
*** 61,66
--- 62,68
TupleDesc attrinfo; /* The attr info we are set up for */
int nattrs;
PrinttupAttrInfo *myinfo; /* Cached info about each attr */
+ MemoryContext tmpcontext; /* Memory context for per-row workspace */
} DR_printtup;
/*
*** printtup_create_DR(CommandDest dest)
*** 87,92
--- 89,95
self-attrinfo = NULL;
self-nattrs = 0;
self-myinfo = NULL;
+ self-tmpcontext = NULL;
return (DestReceiver *) self;
}
*** printtup_startup(DestReceiver *self, int
*** 124,129
--- 127,144
DR_printtup *myState = (DR_printtup *) self;
Portal portal = myState-portal;
+ /*
+ * Create a temporary memory context that we can reset once per row to
+ * recover palloc'd memory. This avoids any problems with leaks inside
+ * datatype output routines, and should be faster than retail pfree's
+ * anyway.
+ */
+ myState-tmpcontext = AllocSetContextCreate(CurrentMemoryContext,
+ printtup,
+ ALLOCSET_DEFAULT_MINSIZE,
+ ALLOCSET_DEFAULT_INITSIZE,
+ ALLOCSET_DEFAULT_MAXSIZE);
+
if (PG_PROTOCOL_MAJOR(FrontendProtocol) 3)
{
/*
*** printtup(TupleTableSlot *slot, DestRecei
*** 289,294
--- 304,310
{
TupleDesc typeinfo = slot-tts_tupleDescriptor;
DR_printtup *myState = (DR_printtup *) self;
+ MemoryContext oldcontext;
StringInfoData buf;
int natts = typeinfo-natts;
int i;
*** printtup(TupleTableSlot *slot, DestRecei
*** 300,307
/* Make sure the tuple is fully deconstructed */
slot_getallattrs(slot);
/*
! * Prepare a DataRow message
*/
pq_beginmessage(buf, 'D');
--- 316,326
/* Make sure the tuple is fully deconstructed */
slot_getallattrs(slot);
+ /* Switch into per-row context so we can recover memory below */
+ oldcontext =
[HACKERS] pg_ctl status with nonexistent data directory
This doesn't seem right: $ pg_ctl -D /nowhere status pg_ctl: no server running It does exit with status 3, so it's not all that broken, but I think the error message could be more accurate. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SP-GiST bug and fix
Teodor Sigaev teo...@sigaev.ru writes: SP-GiST has a bug during creation: % create table ranges as select int4range( (random()*5)::int, (random()*5)::int+5) as range from generate_series(1,100) x; % create index ranges_range_spgist_idx on ranges using spgist(range); ERROR: unexpected spgdoinsert() failure Bug is discovered by Jonathan S. Katz jonathan.k...@excoventures.com When it was found deadlock possibility it was fixed by using ConditionalLockBuffer() instead of LockBuffer(EXCLUSIVE) and retrying insertion from the scratch. Build index method doesn't believe in concurrent access and throws an error if ConditionalLockBuffer() fails. But I missed that checkpointer process could take a share lock on buffer to write it on disk. Attached patch just intoduces retrying during index creation. The comments could use some work (both here and in spgdoinsert), but I agree this is a real bug and the fix is sane. Will fix the comments and commit. 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] Creating Empty Index
Hello Is there a way by which we can create an empty index on a relation and later fill it manually by inserting tuples. I want to know how it can be done in Postgresql code. Thanks in advance -- View this message in context: http://postgresql.1045698.n5.nabble.com/Creating-Empty-Index-tp5776606.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] Creating Empty Index
On 11/01/2013 02:11 AM, naman.iitb wrote: Hello Is there a way by which we can create an empty index on a relation and later fill it manually by inserting tuples. I want to know how it can be done in Postgresql code. Thanks in advance What exactly would be the point? Indexes are automatically maintained by postgres. Something that isn't doesn't seem to me to qualify for the description of index. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Creating Empty Index
On Sat, Nov 2, 2013 at 10:28 PM, Andrew Dunstan and...@dunslane.net wrote: What exactly would be the point? Indexes are automatically maintained by postgres. Something that isn't doesn't seem to me to qualify for the description of index. Perhaps an index without data that could be used by the planner for automatic query tuning to evaluate how a query could run if the index exists? Like the concept of hypothetical indexes or something like the possibility to do a CREATE/ALTER INDEX ... WITH [ NO ] DATA. -- Michael -- Sent 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] Cannot create matview when referencing another not-populated-yet matview in subquery
Kevin Grittner kgri...@ymail.com wrote: Laurent Sartran lsart...@gmail.com wrote: CREATE MATERIALIZED VIEW t1 AS SELECT text 'foo' AS col1 WITH NO DATA; CREATE MATERIALIZED VIEW t2b AS SELECT * FROM t1 WHERE col1 = (SELECT LEAST(col1) FROM t1) WITH NO DATA; ERROR: materialized view t1 has not been populated HINT: Use the REFRESH MATERIALIZED VIEW command. Is this behavior expected? No, and git bisect shows that it worked until commit 5194024d72f33fb209e10f9ab0ada7cc67df45b7. Fix committed. Thanks for the report! -- Kevin Grittner EDB: 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] missing locking in at least INSERT INTO view WITH CHECK
Andres Freund and...@2ndquadrant.com wrote: the matview patch (0002) This is definitely needed as a bug fix. Will adjust comments and commit, back-patched to 9.3. Thanks for spotting this, and thanks for the fix! Also attached is 0004 which just adds a heap_lock() around a newly created temporary table in the matview code which shouldn't be required for correctness but gives warm and fuzzy feelings as well as less debugging noise. Will think about this. I agree is is probably worth doing something to reduce the noise when looking for cases that actually matter. Wouldn't it be a good idea to tack such WARNINGs (in a proper and clean form) to index_open (checking the underlying relation is locked), relation_open(..., NoLock) (checking the relation has previously been locked) and maybe RelationIdGetRelation() when cassert is enabled? ISTM we frequently had bugs around this. It would be nice to have such omissions pointed out during early testing. -- Kevin Grittner EDB: 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] Creating Empty Index
Michael Paquier michael.paqu...@gmail.com writes: On Sat, Nov 2, 2013 at 10:28 PM, Andrew Dunstan and...@dunslane.net wrote: What exactly would be the point? Indexes are automatically maintained by postgres. Something that isn't doesn't seem to me to qualify for the description of index. Perhaps an index without data that could be used by the planner for automatic query tuning to evaluate how a query could run if the index exists? Like the concept of hypothetical indexes or something like the possibility to do a CREATE/ALTER INDEX ... WITH [ NO ] DATA. But we already provide support for hypothetical indexes via planner plugins. Why would you need an actual empty index underlying that? I agree with Andrew that the use-case for this hasn't been explained. 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
