Re: [HACKERS] [PATCH] Fix ScalarArrayOpExpr estimation for GIN indexes

2011-12-21 Thread Marti Raudsepp
On Wed, Dec 21, 2011 at 03:03, Tom Lane t...@sss.pgh.pa.us wrote: I've applied a revised version of this patch that factors things in a way I found nicer. Nice, thanks! Regards, Marti -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

[HACKERS] [PATCH] Fix float8 parsing of denormal values (on some platforms?)

2011-12-21 Thread Marti Raudsepp
Hi list, Back in June we had a discussion about parsing denormal floating-point values. A float8-text conversion could result in a number that can't be converted back to float8 anymore for some values. Among other things, this could break backups (though my searches didn't turn up any reports of

Re: [HACKERS] [PATCH] Fix float8 parsing of denormal values (on some platforms?)

2011-12-21 Thread Marti Raudsepp
On Wed, Dec 21, 2011 at 18:21, Marti Raudsepp ma...@juffo.org wrote: I think the least invasive fix, as proposed by Jeroen, is to fail only when ERANGE is set *and* the return value is 0.0 or +/-HUGE_VAL. Reading relevant specifications, this seems to be a fairly safe assumption. That's what

Re: [HACKERS] [PATCH] Enable min/max optimization for bool_and/bool_or/every

2011-12-22 Thread Marti Raudsepp
On Thu, Dec 22, 2011 at 18:41, Robert Haas robertmh...@gmail.com wrote: On Mon, Dec 19, 2011 at 5:16 AM, Marti Raudsepp ma...@juffo.org wrote: PS: It seems that the min/max optimization isn't documented in the manual (apart from release notes), so I didn't include any doc changes in this patch

[HACKERS] Caching for stable expressions with constant arguments v6

2012-01-16 Thread Marti Raudsepp
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

Re: [HACKERS] logging in high performance systems.

2012-01-17 Thread Marti Raudsepp
On Thu, Nov 24, 2011 at 04:28, Theo Schlossnagle je...@omniti.com wrote: So... here's my first whack at solving this with some flexibility. The first thing I did was add hook points where immediate statement logging happens pre_exec and those that present duration post_exec.  These should,

[HACKERS] Patch review for logging hooks (CF 2012-01)

2012-01-17 Thread Marti Raudsepp
Hi, Here's my review for the logging hooks patch queued for the 2012-01 CommitFest by Martin Pihlak. Submission review The patch is in context diff format and applies fine. Tests are not included and don't seem practical for this patch. More documentation would always be nice, but as other

Re: [HACKERS] Patch review for logging hooks (CF 2012-01)

2012-01-17 Thread Marti Raudsepp
Hi! On Tue, Jan 17, 2012 at 23:07, Martin Pihlak martin.pih...@gmail.com wrote: I think the hook warrants a comment that, whether the messages will be seen, depends on the log_min_messages setting. Comment added. Nice :) It seems you missed a comment, that the current implementation is also

Re: [HACKERS] Patch review for logging hooks (CF 2012-01)

2012-01-18 Thread Marti Raudsepp
On Wed, Jan 18, 2012 at 22:56, Martin Pihlak martin.pih...@gmail.com wrote: We need to keep the if separate though -- the hook might want to omit the message from server log so the output_to_server needs to be rechecked. Oh, yes makes sense. The updated patch looks good, marking as 'Ready for

Re: [HACKERS] Simulating Clog Contention

2012-01-19 Thread Marti Raudsepp
On Thu, Jan 19, 2012 at 18:12, Robert Haas robertmh...@gmail.com wrote: Right, but the point is that to address Heikki's objection that this is a special-purpose hack, we should try to make it general, so that it can be used by other people for other things. Personally I would like to see

Re: [HACKERS] Patch review for logging hooks (CF 2012-01)

2012-01-20 Thread Marti Raudsepp
On Fri, Jan 20, 2012 at 17:01, Robert Haas robertmh...@gmail.com wrote: We should probably at least have a working example for testing purposes, though, whether or not we end up committing it. Martin Pihlak sent a short description of how to test the patch with his pg_logforward module:

Re: [HACKERS] WIP: index support for regexp search

2012-01-20 Thread Marti Raudsepp
On Fri, Jan 20, 2012 at 01:33, Erik Rijkers e...@xs4all.nl wrote: Btw, it seems impossible to Ctrl-C out of a search once it is submitted; I suppose this is normally necessary for perfomance reasons, but it would be useful te be able to compile a test version that allows it. I believe

Re: [HACKERS] [PATCH] Fix float8 parsing of denormal values (on some platforms?)

2012-02-02 Thread Marti Raudsepp
On Wed, Feb 1, 2012 at 20:17, Tom Lane t...@sss.pgh.pa.us wrote: Applied with minor revisions. Thanks! :) We're already seeing first buildfarm failures, on system narwhal using an msys/mingw compiler. http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=narwhaldt=2012-02-02%2005%3A00%3A02 No idea

Re: [HACKERS] Patch pg_is_in_backup()

2012-02-02 Thread Marti Raudsepp
On Mon, Jan 30, 2012 at 20:33, Gilles Darold gilles.dar...@dalibo.com wrote: After some time searching for a Pg system administration function like pg_is_in_recovery(), let's say pg_is_in_backup(), I couldn't find one. The minor patch attached here adds this administrative function that can be

Re: [HACKERS] Caching for stable expressions with constant arguments v6

2012-02-04 Thread Marti Raudsepp
On Sat, Feb 4, 2012 at 09:49, Jaime Casanova ja...@2ndquadrant.com wrote: i little review... Thanks! By the way, you should update to the v7 patch. first, i notice a change of behaviour... i'm not sure if i can say this is good or not. if you execute: select *, cached_random() from (select

[HACKERS] Text-any concatenation volatility acting as optimization barrier

2012-02-07 Thread Marti Raudsepp
Hi list, Andrew Dunstan reported an awkward-seeming case on IRC where shifting around a concatenation expression in a view made the planner choose a good or a bad execution plan. Simplified, it boils down to this: db=# create table foo(i int); db=# explain verbose select i from (select i,

Re: [HACKERS] Text-any concatenation volatility acting as optimization barrier

2012-02-07 Thread Marti Raudsepp
On Tue, Feb 7, 2012 at 22:31, Andrew Dunstan and...@dunslane.net wrote: It gets worse if you replace the expression with a call to a (non-sql) function returning text, which was in fact the original use case. Then you're pretty  much hosed. Oh, if it's a non-SQL function then marking it as

Re: [HACKERS] Text-any concatenation volatility acting as optimization barrier

2012-02-08 Thread Marti Raudsepp
On Wed, Feb 8, 2012 at 06:21, Tom Lane t...@sss.pgh.pa.us wrote: Marti Raudsepp ma...@juffo.org writes: Case #1 uses the normal textcat(text, text) operator by automatically coercing 'x' as text. However, case #2 uses the anytextcat(anynonarray, text), which is marked as volatile thus acts

Re: [HACKERS] Text-any concatenation volatility acting as optimization barrier

2012-02-08 Thread Marti Raudsepp
On Wed, Feb 8, 2012 at 18:20, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Wed, Feb 8, 2012 at 4:53 AM, Marti Raudsepp ma...@juffo.org wrote: Patch attached (in git am format). Passes all regression tests (except 'json' which fails on my machine even on git

Re: [HACKERS] [PATCH] Enable min/max optimization for bool_and/bool_or/every

2012-02-08 Thread Marti Raudsepp
On Wed, Feb 8, 2012 at 19:48, Tom Lane t...@sss.pgh.pa.us wrote: I applied this patch, since I was busy applying catalog changes from you anyway ;-). Thanks :) I did think of a possible reason to reject the patch: with this change, the planner will take longer to plan queries involving

[HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-02-09 Thread Marti Raudsepp
Hi! I've always been a little wary of using the TRUNCATE command due to the warning in the documentation about it not being MVCC-safe: queries may silently give wrong results and it's hard to tell when they are affected. That got me thinking, why can't we handle this like a standby server does

Re: [HACKERS] bitfield and gcc

2012-02-13 Thread Marti Raudsepp
On Sat, Feb 11, 2012 at 01:54, Gaetano Mendola mend...@gmail.com wrote: I wonder if somewhere in Postgres source we are relying on the GCC correct behaviour regarding the read-modify-write of bitfield in structures. Probably not. I'm pretty sure that we don't have any bitfields, since not all

Re: [HACKERS] Removing special case OID generation

2012-02-13 Thread Marti Raudsepp
On Mon, Feb 13, 2012 at 15:08, Robert Haas robertmh...@gmail.com wrote: Honestly, I think the biggest hassle of the OID code is not so much the way they're generated as the way they're stored within heap tuples.  I've wondered whether we should go through the system catalogs and replace all of

Re: [HACKERS] CUDA Sorting

2012-02-15 Thread Marti Raudsepp
On Mon, Feb 13, 2012 at 20:48, Greg Stark st...@mit.edu wrote: I don't think we should be looking at either CUDA or OpenCL directly. We should be looking for a generic library that can target either and is well maintained and actively developed. I understand your point about using some

Re: [HACKERS] possible new option for wal_sync_method

2012-02-16 Thread Marti Raudsepp
On Thu, Feb 16, 2012 at 19:18, Dan Scales sca...@vmware.com wrote: fsync/fdatasync can be very slow on ext3, because it seems to have to always wait for the current filesystem meta-data transaction to complete, even if that meta-data operation is completely unrelated to the file being

Re: [HACKERS] Copyright notice for contrib/cube?

2012-02-17 Thread Marti Raudsepp
On Fri, Feb 17, 2012 at 17:42, Jay Levitt jay.lev...@gmail.com wrote: Should it be something like Portions Copyright (c) 1996-2011, PostgreSQL Global Development Group Portions Copyright (c) 2012, TipTap Inc. Please don't add that, just change 2011 to 2012. This is what the wiki says: Q: May

Re: [HACKERS] pg_test_timing tool for EXPLAIN ANALYZE overhead

2012-02-22 Thread Marti Raudsepp
On Wed, Feb 22, 2012 at 18:44, Greg Smith g...@2ndquadrant.com wrote: As far as I've been able to tell, there aren't any issues unique to Windows there.  Multiple cores can have their TSC results get out of sync on Windows for the same reason they do on Linux systems, and there's also the same

Re: [HACKERS] pg_test_timing tool for EXPLAIN ANALYZE overhead

2012-02-22 Thread Marti Raudsepp
On Wed, Feb 22, 2012 at 19:36, Greg Smith g...@2ndquadrant.com wrote: From the patch: Newer operating systems may check for the known TSC problems and switch to a slower, more stable clock source when they are seen. If your system supports TSC time but doesn't default to that, it may be

[HACKERS] [PATCH] Cleanup: use heap_open/heap_close consistently

2012-02-27 Thread Marti Raudsepp
Hi, Here's a tiny cleanup: currently get_tables_to_cluster uses heap_open() to open the relation, but then closes it with relation_close(). Currently relation_close=heap_close, but it seems like good idea to be consistent -- in case these functions need to diverge in the future. Regards, Marti

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-03 Thread Marti Raudsepp
On Sat, Mar 3, 2012 at 14:53, Simon Riggs si...@2ndquadrant.com wrote: Thanks Noah for drawing attention to this thread. I hadn't been watching. As you say, this work would allow me to freeze rows at load time and avoid the overhead of hint bit setting, which avoids performance issues from

[HACKERS] PGXS ignores SHLIB_LINK when linking modules

2012-03-07 Thread Marti Raudsepp
Hi, I'm trying to write my first PostgreSQL C extension. I used the pgxn-utils skeleton as a base and specified some external libraries in SHLIB_LINK. However, this variable was ignored when linking the library (using pgxs from current git master). After spending quite a bit of time trying to

[HACKERS] [PATCH] Optimize IS DISTINCT FROM NULL = IS NOT NULL

2012-03-08 Thread Marti Raudsepp
Hi list, This patch enables a simple optimization in eval_const_expressions_mutator. If we know that one argument to DistinctExpr is NULL then we can optimize it to a NullTest, which can be an indexable expression. For example the query: EXPLAIN (costs off) SELECT * FROM foo WHERE j IS NOT

Re: [HACKERS] [PATCH] Optimize IS DISTINCT FROM NULL = IS NOT NULL

2012-03-08 Thread Marti Raudsepp
On Thu, Mar 8, 2012 at 19:35, Tom Lane t...@sss.pgh.pa.us wrote: Uh ... how much do we care about that?  I can't say that I've heard many people complain about the fact that IS [NOT] DISTINCT FROM is poorly optimized -- which it is, in general, and this patch chips away at that only a tiny

Re: [HACKERS] Caching for stable expressions with constant arguments v6

2012-03-10 Thread Marti Raudsepp
On Sat, Mar 10, 2012 at 02:05, Tom Lane t...@sss.pgh.pa.us wrote: Marti Raudsepp ma...@juffo.org writes: [ cacheexpr-v8.patch ] A few comments Whoa, that's quite a few. Thanks for the review. * There's a lot of stuff that seems wrong in detail in eval_const_expressions_mutator, eg it'll

[HACKERS] Refactoring simplify_function (was: Caching constant stable expressions)

2012-03-10 Thread Marti Raudsepp
Hi list, Per Tom's request, I split out this refactoring from my CacheExpr patch. Basically I'm just centralizing the eval_const_expressions_mutator() call on function arguments, from multiple different places to a single location. Without this, it would be a lot harder to implement argument

[HACKERS] GitHub mirror is not updating

2012-03-12 Thread Marti Raudsepp
Hi list, I don't know who's maintaining the PostgreSQL GitHub mirror, but it hasn't been updated for 6 days now: https://github.com/postgres/postgres Just letting you know. Regards, Marti -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your

Re: [HACKERS] unlogged tables

2010-12-08 Thread Marti Raudsepp
A very useful feature for unlogged tables would be the ability to switch them back to normal tables -- this way you could do bulk loading into an unlogged table and then turn it into a regular table using just fsync(), bypassing all the WAL-logging overhead. It seems this could even be implemented

Re: [HACKERS] We really ought to do something about O_DIRECT and data=journalled on ext4

2010-12-08 Thread Marti Raudsepp
On Tue, Dec 7, 2010 at 03:34, Tom Lane t...@sss.pgh.pa.us wrote: To my mind, O_DIRECT is not really the key issue here, it's whether to prefer O_DSYNC or fdatasync. Since different platforms implement these primitives differently, and it's not always clear from the header file definitions which

Re: [HACKERS] Why don't we accept exponential format for integers?

2010-12-17 Thread Marti Raudsepp
On Sat, Dec 18, 2010 at 00:05, Josh Berkus j...@agliodbs.com wrote: Well, that's stupidly arbitrary.  If we're not going to accept '1.234e+01'::Integer, then we shouldn't accept 1.234e+01::Integer either. Not surprising to me. This is how many languages implement type conversion. Python:

Re: [HACKERS] pl/python improvements

2010-12-23 Thread Marti Raudsepp
On Thu, Dec 23, 2010 at 04:08, Jan Urbański wulc...@wulczer.org wrote:  * providing custom exceptions for SPI errors, so you can catch only UniqueViolations and not have to muck around with SQLCODE py-postgresql already has a mapping from error codes to Python exceptions. I think it makes sense

Re: [HACKERS] Why is sorting on two columns so slower than sorting on one column?

2010-12-23 Thread Marti Raudsepp
On Thu, Dec 23, 2010 at 09:33, Jie Li jay23j...@gmail.com wrote: While the first sorting takes about only 6 seconds, the second one takes over 30 seconds,  Is this too much than expected? Is there any possible optimization ? If you're doing these queries often, you should: CREATE INDEX

Re: [HACKERS] Sync Rep Design

2010-12-30 Thread Marti Raudsepp
Most of your doc uses the terms primary and standby, but a few instances of master and slave have slipped in. I think it's better to stick to consistent terminology. On Thu, Dec 30, 2010 at 21:04, Simon Riggs si...@2ndquadrant.com wrote: With synchronous replication options specified at the

Re: [HACKERS] Streaming base backups

2011-01-06 Thread Marti Raudsepp
On Wed, Jan 5, 2011 at 23:58, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: * Stefan mentiond it might be useful to put some posix_fadvise(POSIX_FADV_DONTNEED)   in the process that streams all the files out. Seems useful, as long as that   doesn't kick them out of the cache *completely*,

Re: [HACKERS] [PATCH] Return command tag 'REPLACE X' for CREATE OR REPLACE statements.

2011-01-14 Thread Marti Raudsepp
Thanks for reviewing! On Fri, Jan 14, 2011 at 13:40, Robert Haas robertmh...@gmail.com wrote: Did you check whether this updated the code for 100% of the object types where this could apply? I walked through all the CREATE statements in the documentation and these four seem to be the only ones

Re: [HACKERS] [PATCH] Return command tag 'REPLACE X' for CREATE OR REPLACE statements.

2011-01-15 Thread Marti Raudsepp
better ideas? The 2nd patch also moves MOVE/FETCH command tag formatting up to ProcessUtility, hopefully this change is for the better. Regards, Marti From 25a19ca972e6f02ba350b6e4112935ff1ed44b24 Mon Sep 17 00:00:00 2001 From: Marti Raudsepp ma...@juffo.org Date: Sun, 28 Nov 2010 16:49:41 +0200

Re: [HACKERS] Spread checkpoint sync

2011-01-15 Thread Marti Raudsepp
On Sat, Jan 15, 2011 at 14:05, Robert Haas robertmh...@gmail.com wrote: Idea #4: For ext3 filesystems that like to dump the entire buffer cache instead of only the requested file, write a little daemon that runs alongside of (and completely indepdently of) PostgreSQL.  Every 30 s, it opens a

Re: [HACKERS] auto-sizing wal_buffers

2011-01-16 Thread Marti Raudsepp
On Sun, Jan 16, 2011 at 00:34, Josh Berkus j...@agliodbs.com wrote: I think we can be more specific on that last sentence; is there even any *theoretical* benefit to settings above 16MB, the size of a WAL segment?  Certainly there have been no test results to show any. I don't know if it's

Re: [HACKERS] FOR KEY LOCK foreign keys

2011-01-28 Thread Marti Raudsepp
On Thu, Jan 13, 2011 at 23:58, Alvaro Herrera alvhe...@commandprompt.com wrote: It goes like this: instead of acquiring a shared lock on the involved tuple, we only acquire a key lock, that is, something that prevents the tuple from going away entirely but not from updating fields that are not

Re: [HACKERS] [PATCH] Return command tag 'REPLACE X' for CREATE OR REPLACE statements.

2011-01-28 Thread Marti Raudsepp
On Fri, Jan 28, 2011 at 21:04, Robert Haas robertmh...@gmail.com wrote: On Sat, Jan 22, 2011 at 9:11 PM, Robert Haas robertmh...@gmail.com wrote: It seems no one wants to put any further effort into this problem.  Bummer. Since no one has felt the need to dispute the above statement in the

[HACKERS] [PATCH] pg_upgrade -o/-O regression in 9.2.2

2012-12-10 Thread Marti Raudsepp
Hi! It seems that PostgreSQL 9.2.2 has a regression in pg_upgrade, the -o and -O options forget to add a space before passing on user options, thereby generating unparsable command lines. For example: pg_upgrade -b /usr/local/pg91/bin -B /usr/bin -d /tmp/91 -D /tmp/92 -O -F [...] Creating

Re: [HACKERS] count(*) of zero rows returns 1

2013-01-21 Thread Marti Raudsepp
On Tue, Jan 15, 2013 at 5:47 AM, Gurjeet Singh singh.gurj...@gmail.com wrote: postgres=# select * from test_0_col_table ; -- (20 rows) Interestingly, PostgreSQL 9.2 has regressed here. Not sure if we care, but worth mentioning: psql (9.2.2) test=# select count(*) from foo1; count

Re: [HACKERS] count(*) of zero rows returns 1

2013-01-21 Thread Marti Raudsepp
On Mon, Jan 21, 2013 at 9:33 PM, Tom Lane t...@sss.pgh.pa.us wrote: test=# select * from foo1; (No rows) Time: 1012.567 ms How did you get that? I don't believe it's possible in the default output format. Oh I see, it's because I have \x auto in my .psqlrc. If I set \x auto or \x on then

[HACKERS] UNION ALL with WHERE clause does not use Merge Append

2012-08-16 Thread Marti Raudsepp
Hi list, I have a table with (start_time, end_time) and I'd want to tally up the number of concurrent connections at any point in time. The Merge Append plan node introduced in 9.1 would be perfect for this purpose. It seems to work out fine for the most trivial case, but just when I add an WHERE

Re: [HACKERS] Caching for stable expressions with constant arguments v6

2012-08-27 Thread Marti Raudsepp
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:

Re: [HACKERS] Caching for stable expressions with constant arguments v6

2012-10-31 Thread Marti Raudsepp
On Wed, Oct 31, 2012 at 2:21 AM, Josh Berkus j...@agliodbs.com wrote: Hey, are you going to work on this for 9.3? Yes, I do plan to get back to it. Thanks for the push :) I really could use the feature ... If you're not aware already, you can work around the limitation using a subquery.

Re: [HACKERS] [PATCH] Prefetch index pages for B-Tree index scans

2012-11-01 Thread Marti Raudsepp
On Tue, Oct 30, 2012 at 1:50 AM, Claudio Freire klaussfre...@gmail.com wrote: On Mon, Oct 29, 2012 at 7:07 PM, Cédric Villemain ced...@2ndquadrant.com wrote: Well, informing linux hackers may help. I agree. I'm a bit hesitant to subscribe to yet another mailing list FYI you can send messages

Re: [HACKERS] TRUNCATE SERIALIZABLE and frozen COPY

2012-11-09 Thread Marti Raudsepp
On Wed, Nov 7, 2012 at 5:34 PM, Simon Riggs si...@2ndquadrant.com wrote: I was unhappy with changing the behaviour of TRUNCATE, and still am. So the proposal here is to have a specific modifier on TRUNCATE command that makes it MVCC safe by throwing a serialization error. That new behaviour

Re: [HACKERS] TRUNCATE SERIALIZABLE and frozen COPY

2012-11-09 Thread Marti Raudsepp
On Fri, Nov 9, 2012 at 4:24 PM, Simon Riggs si...@2ndquadrant.com wrote: We need a fast lookup structure that is expandable to accommodate arbitrary numbers of truncates. Shared hash table, with some form of overflow mechanism. Surely you only need to remember the last completed truncate for

Re: [HACKERS] Refactoring simplify_function (was: Caching constant stable expressions)

2012-03-23 Thread Marti Raudsepp
On Sat, Mar 24, 2012 at 01:17, Tom Lane t...@sss.pgh.pa.us wrote: I've applied a slightly-modified version of this after reconciling it with the protransform fixes. Cool, thanks!  I assume you are going to submit a rebased version of the main CacheExpr patch? Yeah, I'm still working on

[HACKERS] [PATCH] Make skipped sort explicit in EXPLAIN ANALYZE

2012-05-25 Thread Marti Raudsepp
Hi, This is just a small clarity improvement. tuplesort_performsort() skips sorting entirely when the result set has 0 or 1 tuples, but EXPLAIN still says it's using quicksort. The patch changes that to skipped For example: db=# explain analyze select * from now() order by 1; Sort

Re: [HACKERS] [PATCH] Make skipped sort explicit in EXPLAIN ANALYZE

2012-05-25 Thread Marti Raudsepp
On Fri, May 25, 2012 at 4:49 PM, Tom Lane t...@sss.pgh.pa.us wrote: I'm not convinced this is an improvement; it's just one more value to confuse people with. I understand where you're coming from, but personally I think the current output is more confusing: Gee Postgres is stupid, it's sorting

[HACKERS] Bogus nestloop rows estimate in 8.4.7

2012-05-28 Thread Marti Raudsepp
Hi list, This bug isn't causing me any immediate problems -- the plan works out well regardless -- but PostgreSQL 8.4.7 is somehow overestimating the number of rows coming from a nestloop join, when joining 2 large partitioned tables. Maybe it's been fixed in more recent versions, sadly it's an

Re: [HACKERS] Bogus nestloop rows estimate in 8.4.7

2012-05-28 Thread Marti Raudsepp
On Mon, May 28, 2012 at 10:45 AM, Marti Raudsepp ma...@juffo.org wrote: Query: SELECT '2012-05-28T09:00:00', count(*), uniq(sort(array_agg(visitor_id))), banner_id, client_body_id, partner_body_id, space_id, campaign_id, evt_type_id FROM stats_request WHERE stats_request.request_time = '2012

Re: [HACKERS] Bogus nestloop rows estimate in 8.4.7

2012-05-28 Thread Marti Raudsepp
On Mon, May 28, 2012 at 8:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: Also, what do you have constraint_exclusion set to? The only sane value, partition This sounds familiar, but a quick trawl through the commit logs didn't immediately turn up any related-looking patches.  Can you put together

Re: [HACKERS] Bogus nestloop rows estimate in 8.4.7

2012-05-28 Thread Marti Raudsepp
On Mon, May 28, 2012 at 10:32 PM, Marti Raudsepp ma...@juffo.org wrote: There was a similar case in 9.0.4 with WHERE i=1, but that has been fixed in 9.0.7 Oh, it's been fixed in 9.0.7, but apparently not in 8.4.11; the empty parent tables are confusing the estimate: explain select * from

Re: [HACKERS] Bogus nestloop rows estimate in 8.4.7

2012-05-28 Thread Marti Raudsepp
On Mon, May 28, 2012 at 11:23 PM, Tom Lane t...@sss.pgh.pa.us wrote: However, the error in your original example is far too large to be explained by that, so I think it was tripping over something different. Good point. But I generated a bigger data set with the above test case and it gets

Re: [HACKERS] Bogus nestloop rows estimate in 8.4.7

2012-05-29 Thread Marti Raudsepp
On Tue, May 29, 2012 at 1:37 AM, Tom Lane t...@sss.pgh.pa.us wrote: So, nothing to see here ... 8.4 is just not very good with this type of problem. Fair enough, thanks for your time. :) Regards, Marti -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to

[HACKERS] Release versioning inconsistency

2012-06-20 Thread Marti Raudsepp
Hi list, The recent 9.2 beta releases have used a slightly different numbering scheme than all previous releases. It used to be that tarballs for version $VER were always available at: http://ftp.postgresql.org/pub/source/v$VER/postgresql-$VER.tar.bz2 However, the new releases now use

Re: [HACKERS] Release versioning inconsistency

2012-06-20 Thread Marti Raudsepp
On Wed, Jun 20, 2012 at 12:18 PM, Magnus Hagander mag...@hagander.net wrote: (I do believe that using the v9.2.0beta marker is *better*, because then it sorts properly. But likely not enough much better to be inconsistent with previous versions) Good point. Maybe that's a reason to change the

[HACKERS] [PATCH] Allow star syntax in GROUP BY, as a shorthand for all table columns

2011-06-08 Thread Marti Raudsepp
Raudsepp ma...@juffo.org Date: Wed, 8 Jun 2011 19:21:16 +0300 Subject: [PATCH] Allow star syntax in GROUP BY, as a shorthand for all table columns Marti Raudsepp --- src/backend/catalog/pg_constraint.c |2 +- src/backend/parser/parse_agg.c |2 +- src/test/regress/expected

Re: [HACKERS] [PATCH] Allow star syntax in GROUP BY, as a shorthand for all table columns

2011-06-09 Thread Marti Raudsepp
On Wed, Jun 8, 2011 at 20:22, Tom Lane t...@sss.pgh.pa.us wrote: Is this really necessary now that we know about GROUP BY primary key? You're right. I was just looking for something easy to hack on and didn't put much thought into usefulness. I'll try to do better next time. :) Regards, Marti

[HACKERS] [BUG] Denormal float values break backup/restore

2011-06-10 Thread Marti Raudsepp
Hi list, I was playing around with denormal float values in response to the Itanium thread and noticed that Postgres' float conversion functions behave inconsistently, at least on Linux with glibc 2.14 It can successfully convert denormal float values to strings: marti=# select

Re: [HACKERS] [BUG] Denormal float values break backup/restore

2011-06-10 Thread Marti Raudsepp
On Fri, Jun 10, 2011 at 17:20, Tom Lane t...@sss.pgh.pa.us wrote: I put this right about on par with the occasional suggestions that we implement our own filesystem.  It's not our core competency and in the end there is no value-add.  If you need to work with denormals, find a platform that

Re: [HACKERS] [BUG] Denormal float values break backup/restore

2011-06-20 Thread Marti Raudsepp
Hi, thanks for chiming in. On Sat, Jun 11, 2011 at 22:08, Jeroen Vermeulen j...@xs4all.nl wrote: AIUI that is defined to be a little vague, but includes denormalized numbers that would undergo any rounding at all.  It says that on overflow the conversion should return the appropriate HUGE_VAL

Re: [HACKERS] Hugetables question

2011-06-22 Thread Marti Raudsepp
On Sun, Jun 19, 2011 at 12:56, Radosław Smogura rsmog...@softperience.eu wrote: I want to implement hugepages for shared memory Hi, Have you read this post by Tom Lane about the performance estimation and a proof-of-concept patch with hugepages?

[HACKERS] Bogus nestloop join estimate, ignores WHERE clause

2011-08-31 Thread Marti Raudsepp
values to NULLs, then this JOIN query starts estimating correctly. So this one is probably confused because the empty values would result in a cartesian join. Are there any reasons why nestloop can't use the known (1 * 41) as its estimate? Regards, Marti Raudsepp voicecom.ee -- Sent via pgsql

Re: [HACKERS] Bogus nestloop join estimate, ignores WHERE clause

2011-08-31 Thread Marti Raudsepp
On Wed, Aug 31, 2011 at 16:34, Peter Eisentraut pete...@gmx.net wrote: On ons, 2011-08-31 at 15:38 +0300, Marti Raudsepp wrote: I'm getting really surprising planner estimates for a query that's joining another table via a varchar field. All of this was tested on PostgreSQL 8.4.8, 9.0.4

[HACKERS] [PATCH] Generate column names for subquery expressions

2011-08-31 Thread Marti Raudsepp
, lowering the strength of subquery names in FigureColnameInternal would resort to the old behavior. How this affects different queries can be seen from the regression diffs. Does this sound like a good idea? Should I submit this to the CommitFest? Regards, Marti Raudsepp From

[HACKERS] Release notes git attribution

2013-02-08 Thread Marti Raudsepp
Hi, The 9.2.3 release notes say: * Fix pg_upgrade's -O/-o options (Bruce Momjian) You got it the wrong way around, he was the one who introduced the bug! ;) I originally found, debugged and provided the fix:

[HACKERS] [PATCH] Version info mixes up host and target platform when cross-compiling

2013-03-17 Thread Marti Raudsepp
I tried running PostgreSQL on the ARM64 (aka AArch64) emulator and noticed that the version() string mixes up the host and target architecture. Before: PostgreSQL 9.3devel on x86_64-pc-linux-gnu, compiled by aarch64-linux-gnu-gcc-4.7 (Ubuntu/Linaro 4.7.2-21ubuntu3) 4.7.2, 64-bit Now:

Re: [HACKERS] [PATCH] Version info mixes up host and target platform when cross-compiling

2013-03-17 Thread Marti Raudsepp
On Sun, Mar 17, 2013 at 3:17 PM, Marti Raudsepp ma...@juffo.org wrote: Now: PostgreSQL 9.3devel on aarch64-linux-gnu-gcc-4.7 (Ubuntu/Linaro 4.7.2-21ubuntu3) 4.7.2, compiled by x86_64-pc-linux-gnu, 64-bit Sorry, that is clearly wrong. I'll come up with a better patch soon (and actually check

Re: [HACKERS] [PATCH] Version info mixes up host and target platform when cross-compiling

2013-03-17 Thread Marti Raudsepp
On Sun, Mar 17, 2013 at 3:38 PM, Marti Raudsepp ma...@juffo.org wrote: Sorry, that is clearly wrong. I'll come up with a better patch soon (and actually check that it makes sense! :) Sorry about the noise, I just misunderstood the configure --target and --host arguments. If I set them correctly

Re: [HACKERS] 9.3 release notes suggestions

2013-05-04 Thread Marti Raudsepp
Hi! On Wed, Apr 24, 2013 at 6:41 AM, Bruce Momjian br...@momjian.us wrote: Thanks for the many suggestions on improving the 9.3 release notes. There were many ideas I would have never thought of. Please keep the suggestions coming. Some observations: 1. Allow CREATE TABLE IF NOT EXISTS to

[HACKERS] PostgreSQL 9.3 beta breaks some extensions make install

2013-05-13 Thread Marti Raudsepp
Hi list, While testing out PostgreSQL 9.3beta1, I stumbled upon a problem installing some extensions (pgTAP and semver among others): % make [...] % make DESTDIR=/tmp/foo install [...] /usr/bin/install -c -m 644 ./sql/semver--unpackaged--0.2.1.sql ./sql/semver--0.2.4--0.3.0.sql

Re: [HACKERS] PostgreSQL 9.3 beta breaks some extensions make install

2013-05-14 Thread Marti Raudsepp
On Tue, May 14, 2013 at 5:27 AM, Peter Eisentraut pete...@gmx.net wrote: On Tue, 2013-05-14 at 04:12 +0300, Marti Raudsepp wrote: It's caused by this common pattern in extension makefiles: DATA = $(wildcard sql/*--*.sql) sql/$(EXTENSION)--$(EXTVERSION).sql What is the point of this? Why have

Re: [HACKERS] PostgreSQL 9.3 beta breaks some extensions make install

2013-05-14 Thread Marti Raudsepp
On Tue, May 14, 2013 at 2:59 PM, Peter Eisentraut pete...@gmx.net wrote: Perhaps, but fixing the extensions is not a solution at this point. A large number of extensions use this exact code (it comes from David Wheeler's template AFAIK). So far, the number is still less than the number of

[HACKERS] [PATCH] pgcrypto: Test for NULL before dereferencing pointer

2010-10-20 Thread Marti Raudsepp
the condition which was the intended behavior. Regards, Marti From a2500cda9e0e82883854a412ea12942e174e3dd2 Mon Sep 17 00:00:00 2001 From: Marti Raudsepp ma...@juffo.org Date: Wed, 20 Oct 2010 18:32:17 +0300 Subject: [PATCH] pgcrypto: Test for NULL before dereferencing pointer If pk is NULL, the backend would

Re: [HACKERS] [PATCH] pgcrypto: Test for NULL before dereferencing pointer

2010-10-20 Thread Marti Raudsepp
On Wed, Oct 20, 2010 at 22:34, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Did coccicheck find anything else interesting? There's a file descriptor leak in psql/command.c function process_file() -- on errors it just returns without closing the file. But since it's quitting

[HACKERS] psql: Don't close stdin, don't leak file descriptor with ON_ERROR_STOP

2010-10-20 Thread Marti Raudsepp
as the number of executions of '-' because stdin is never closed. Regards, Marti From 43b7595fdcc69cc9db0d066a53f53c5e71c965aa Mon Sep 17 00:00:00 2001 From: Marti Raudsepp ma...@juffo.org Date: Wed, 20 Oct 2010 23:44:36 +0300 Subject: [PATCH] psql: Don't close stdin, don't leak file descriptor

[HACKERS] [PATCH] Cleanup: Compare pointers to NULL instead of 0

2010-10-29 Thread Marti Raudsepp
Sep 17 00:00:00 2001 From: Marti Raudsepp ma...@juffo.org Date: Fri, 29 Oct 2010 18:59:44 +0300 Subject: [PATCH] Cleanup: Compare pointers to NULL instead of 0 Most of these were discovered with Coccinelle (badzero.cocci from coccicheck) Marti Raudsepp --- src/backend/utils/adt/tsrank.c |2

[HACKERS] [PATCH] More Coccinelli cleanups

2010-10-29 Thread Marti Raudsepp
a point where code cleanups defeat their purpose and become a burden. So this will probably be my last one, I'll go to doing productive things instead. :) Regards, Marti From aadcc5c31df00e940a89abf392e7c71b54e00b6a Mon Sep 17 00:00:00 2001 From: Marti Raudsepp ma...@juffo.org Date: Sat, 30 Oct 2010

[HACKERS] [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

2010-11-05 Thread Marti Raudsepp
/linux.h. I don't know if this is the best way to do it. Regards, Marti From fbf61c6536b7060e5c6745c8221a5a4fb9a53c92 Mon Sep 17 00:00:00 2001 From: Marti Raudsepp ma...@juffo.org Date: Fri, 5 Nov 2010 17:40:22 +0200 Subject: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+ Linux

Re: [HACKERS] [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

2010-11-05 Thread Marti Raudsepp
On Fri, Nov 5, 2010 at 20:13, Tom Lane t...@sss.pgh.pa.us wrote: I'm less than convinced this is the right approach ... If open_dsync is so bad for performance on Linux, maybe it's bad everywhere?  Should we be rethinking the default preference order? Sure, maybe for PostgreSQL 9.1 But the

Re: [HACKERS] [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

2010-11-05 Thread Marti Raudsepp
On Fri, Nov 5, 2010 at 21:20, Tom Lane t...@sss.pgh.pa.us wrote: Marti Raudsepp ma...@juffo.org writes: On Fri, Nov 5, 2010 at 20:13, Tom Lane t...@sss.pgh.pa.us wrote: If open_dsync is so bad for performance on Linux, maybe it's bad everywhere?  Should we be rethinking the default preference

Re: [HACKERS] [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

2010-11-05 Thread Marti Raudsepp
On Fri, Nov 5, 2010 at 22:16, Tom Lane t...@sss.pgh.pa.us wrote: I don't really think that change the default on Linux is that much nicer than change the default everywhere when it comes to what we ought to consider back-patching.  In any case, you're getting ahead of the game: we need to

Re: [HACKERS] multi-platform, multi-locale regression tests

2010-11-10 Thread Marti Raudsepp
On Wed, Nov 10, 2010 at 15:31, Kevin Grittner kevin.gritt...@wicourts.gov wrote: For the Serializable Snapshot Isolation (SSI) patch I needed a test suite which would handle concurrent sessions which interleaved statements in predictable ways.  I was told pgTAP wasn't a good choice for that

Re: [HACKERS] renaming contrib. (was multi-platform, multi-locale regression tests)

2010-11-11 Thread Marti Raudsepp
On Thu, Nov 11, 2010 at 17:24, Tom Lane t...@sss.pgh.pa.us wrote: Given that we have, in fact, never renamed any files in the history of the project, I'm wondering exactly why it thinks that the number of potential rename/copy targets isn't zero.  The whole thing smells broken to me, which is

Re: [HACKERS] Bug in plpython's Python Generators

2010-11-14 Thread Marti Raudsepp
On Sun, Oct 24, 2010 at 01:32, Jan Urbański wulc...@wulczer.org wrote: The error handling in plpython is well-known to be a mess, hence the confusing error message that OP got. Another annoying thing is that SPI calls are not done in a subtransaction, which means you can't trap errors with a

Re: [HACKERS] unlogged tables

2010-11-15 Thread Marti Raudsepp
On Sun, Nov 14, 2010 at 02:16, Robert Haas robertmh...@gmail.com wrote: Here is a series of three patches related to unlogged tables. Just wondering, have you thought of any mechanisms how application code might detect that an unlogged table was truncated due to restart? While polling with

Re: [HACKERS] unlogged tables

2010-11-15 Thread Marti Raudsepp
On Mon, Nov 15, 2010 at 18:25, Tom Lane t...@sss.pgh.pa.us wrote: Marti Raudsepp ma...@juffo.org writes: Would there be a problem with invoking this trigger from the session that first touches the table? Other than security? Right, I guess that would only make sense with SECURITY DEFINER

  1   2   3   >