> > We seem to be going in circles. You suggested having two options,
> > --feedback, and --fsync, which is almost exactly what Furuya posted
> > originally. I objected to that, because I think that user interface
> is
> > too complicated. Instead, I suggested having just a single option
> > called
On Thu, Oct 30, 2014 at 5:01 PM, Andres Freund
wrote:
>
> On 2014-10-30 10:23:56 +0530, Amit Kapila wrote:
> > I have a feeling that this might also have some regression at higher
> > loads (like scale_factor = 5000, shared_buffers = 8GB,
> > client_count = 128, 256) for the similar reasons as bgr
Various ways of tweaking Foreign Keys are suggested that are helpful
for larger databases.
* Deferrable Enforcement Timing Clause
* NOT DEFERRABLE - immediate execution
* DEFERRABLE
*INITIALLY IMMEDIATE - existing
*INITIALLY DEFERRED - existing
*INITIALLY NOT ENFORCED
FK created, but
Recent work on parallel query has opened my eyes to exactly how
frequently we request locks on various catalog tables. (Attached file
is a lock analysis on a representative Pg server).
Given these are catalog tables, we aren't doing much to them that
requires a strong lock. Specifically, only CLUS
On 31 October 2014 04:42, Amit Kapila wrote:
>> In fact it would be more sensible to lock the toast table earlier.
>>
>
> It might make some sense to lock the toast table earlier for this
> particular case, but I don't think in general it will be feasible to lock
> all the tables (including catal
On Fri, Oct 31, 2014 at 6:41 AM, Simon Riggs wrote:
> Is it genuinely required for most parallel operations? I think it's
> clear that none of us knows the answer. Sure, the general case needs
> it, but is the general case the same thing as the reasonably common
> case?
Well, I think that the ans
On Fri, Oct 31, 2014 at 6:35 AM, Simon Riggs wrote:
> Recent work on parallel query has opened my eyes to exactly how
> frequently we request locks on various catalog tables. (Attached file
> is a lock analysis on a representative Pg server).
That analysis is interesting.
> Given these are catal
On 2014-10-31 08:54:54 -0400, Robert Haas wrote:
> On Fri, Oct 31, 2014 at 6:41 AM, Simon Riggs wrote:
> > Is it genuinely required for most parallel operations? I think it's
> > clear that none of us knows the answer. Sure, the general case needs
> > it, but is the general case the same thing as
On 10/30/2014 09:17 PM, Andres Freund wrote:
On 2014-10-30 21:03:43 -0400, Tom Lane wrote:
Andres Freund writes:
On 2014-10-30 20:13:53 -0400, Tom Lane wrote:
As I said upthread, that approach seems to me to be contrary to the
project policy about how configure should behave.
I don't think
Simon Riggs writes:
> Recent work on parallel query has opened my eyes to exactly how
> frequently we request locks on various catalog tables. (Attached file
> is a lock analysis on a representative Pg server).
> Given these are catalog tables, we aren't doing much to them that
> requires a stron
On 2014-10-27 09:30:33 -0400, Tom Lane wrote:
> Andres Freund writes:
> > On 2014-10-27 12:51:44 +0200, Heikki Linnakangas wrote:
> >> replication/slot.c and replication/logical/snapbuild.c use a CRC on the
> >> physical slot and snapshot files. It uses the same algorithm as used e.g.
> >> for the
Robert Haas writes:
> On a related note, I've previously had the thought that it would be
> nice to have a "big DDL lock" - that is, a lock that prevents
> concurrent DDL without preventing anything else - so that pg_dump
> could get just that one lock and then not worry about the state of the
> w
On 2014-10-31 09:48:52 -0400, Tom Lane wrote:
> Robert Haas writes:
> > On a related note, I've previously had the thought that it would be
> > nice to have a "big DDL lock" - that is, a lock that prevents
> > concurrent DDL without preventing anything else - so that pg_dump
> > could get just tha
On Fri, Oct 31, 2014 at 9:54 AM, Andres Freund wrote:
>> But more to the point, this seems like optimizing pg_dump startup by
>> adding overhead everywhere else, which doesn't really sound like a
>> great tradeoff to me.
>
> Well, it'd finally make pg_dump "correct" under concurrent DDL. That's
>
On Tue, Dec 10, 2013 at 2:48 PM, Robert Haas wrote:
>> Speaking of the functionality this does offer, it seems pretty limited. A
>> commit timestamp is nice, but it isn't very interesting on its own. You
>> really also want to know what the transaction did, who ran it, etc. ISTM
>> some kind of a
Andres Freund writes:
> On 2014-10-31 09:48:52 -0400, Tom Lane wrote:
>> But more to the point, this seems like optimizing pg_dump startup by
>> adding overhead everywhere else, which doesn't really sound like a
>> great tradeoff to me.
> Well, it'd finally make pg_dump "correct" under concurrent
Merlin Moncure writes:
> It's also requested now and then in the context of auditing and
> forensic analysis of application problems. But I also agree that the
> tolerance for performance overhead is got to be quite low. If a GUC
> is introduced to manage the tradeoff, it should be defaulted to
On Fri, Oct 31, 2014 at 9:07 AM, Andres Freund wrote:
> On 2014-10-31 08:54:54 -0400, Robert Haas wrote:
>> On Fri, Oct 31, 2014 at 6:41 AM, Simon Riggs wrote:
>> > Is it genuinely required for most parallel operations? I think it's
>> > clear that none of us knows the answer. Sure, the general c
I have not kept up with PostgreSQL changes and have just been using it. A
co-worker recently told me that you need to word "CONCURRENTLY" in "CREATE
INDEX" to avoid table locking. I called BS on this because to my knowledge
PostgreSQL does not lock tables. I referenced this page in the
documentatio
On Thu, Oct 30, 2014 at 11:45 PM, Noah Misch wrote:
>> Given the lack of prior complaints about this
>> loop, I'm not sure I see the need to work harder than that; corruption
>> of this sort must be quite rare.
>
> Looks like _bt_getstackbuf() is always called with some buffer lock held, so
> CHEC
On 31/10/14 15:07, Tom Lane wrote:
Merlin Moncure writes:
It's also requested now and then in the context of auditing and
forensic analysis of application problems. But I also agree that the
tolerance for performance overhead is got to be quite low. If a GUC
is introduced to manage the tradeo
On 16 October 2014 01:29, Claudio Freire wrote:
> But in any case, if the deterrence isn't enough, and you get attacked,
> anything involving redaction as fleshed out in the OP is good for
> nothing. The damage has been done already. The feature doesn't
> meaningfully slow down extraction of data
On 2014-10-31 10:08:59 -0400, Robert Haas wrote:
> On Fri, Oct 31, 2014 at 9:07 AM, Andres Freund wrote:
> > On 2014-10-31 08:54:54 -0400, Robert Haas wrote:
> >> On Fri, Oct 31, 2014 at 6:41 AM, Simon Riggs wrote:
> >> > Is it genuinely required for most parallel operations? I think it's
> >> >
On 10/31/2014 10:28 AM, Mark Woodward wrote:
I have not kept up with PostgreSQL changes and have just been using
it. A co-worker recently told me that you need to word "CONCURRENTLY"
in "CREATE INDEX" to avoid table locking. I called BS on this because
to my knowledge PostgreSQL does not lock
On Fri, Oct 31, 2014 at 2:28 PM, Mark Woodward
wrote:
> I have not kept up with PostgreSQL changes and have just been using it. A
> co-worker recently told me that you need to word "CONCURRENTLY" in "CREATE
> INDEX" to avoid table locking. I called BS on this because to my knowledge
> PostgreSQL d
On 31 October 2014 13:39, Tom Lane wrote:
> I doubt that this can ever be safe, because it will effectively assume
> that all operations on catalog tables are done by code that knows that it
> is accessing a catalog.
> What about manual DML, or even DDL, on a catalog?
I've never really understo
On 2014-10-31 10:02:28 -0400, Tom Lane wrote:
> Andres Freund writes:
> > On 2014-10-31 09:48:52 -0400, Tom Lane wrote:
> >> But more to the point, this seems like optimizing pg_dump startup by
> >> adding overhead everywhere else, which doesn't really sound like a
> >> great tradeoff to me.
>
>
On 31 October 2014 13:03, Robert Haas wrote:
>> Given these are catalog tables, we aren't doing much to them that
>> requires a strong lock. Specifically, only CLUSTER and VACUUM FULL
>> touch those tables like that. When we do that, pretty much everything
>> else hangs, cos you can't get much do
While investigating how to reduce logging of AccessExclusiveLocks for
temp tables, I came up with the attached patch.
My feeling was "that's an ugly patch", but it set me thinking that a
more specialised code path around temp tables could be useful in other
ways, and therefore worth pursuing furth
On 31 October 2014 12:54, Robert Haas wrote:
> 1. Turing's theorem being what it is, predicting what catalog tables
> the child might lock is not necessarily simple.
The Pareto principle offers ways to cope with the world's lack of simplicity.
You mentioned earlier that functions would need to
On 31 October 2014 14:53, Simon Riggs wrote:
> While investigating how to reduce logging of AccessExclusiveLocks for
> temp tables, I came up with the attached patch.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
temp_
On 31 October 2014 14:49, Andres Freund wrote:
> On 2014-10-31 10:02:28 -0400, Tom Lane wrote:
>> Andres Freund writes:
>> > On 2014-10-31 09:48:52 -0400, Tom Lane wrote:
>> >> But more to the point, this seems like optimizing pg_dump startup by
>> >> adding overhead everywhere else, which doesn'
Hi,
Attached is the v2 of the patch with the review comments addressed (see
below).
On 29/10/14 21:08, Petr Jelinek wrote:
On 29/10/14 20:27, Asif Naeem wrote:
1. It seems that following log message need to be more descriptive about
reason for shutdown i.e.
+ if (recov
Hi,
On 28/10/14 13:25, Simon Riggs wrote:
On 13 October 2014 10:05, Petr Jelinek wrote:
I worked bit on this patch to make it closer to committable state.
Here is updated version that works with current HEAD for the October
committfest.
I've reviewed this and it looks good to me. Clean,
I'm on PostgreSQL 9.3. This should reproduce on any table with 100,000+
rows. The EXPLAIN ANALYZE shows many more rows getting scanned with LIMIT
2, but I can't figure out why.
Limit 1:
EXPLAIN ANALYZE WITH base AS (
SELECT *, ROW_NUMBER() OVER () AS rownum FROM a_big_table
), filter AS (
S
On 31 October 2014 15:46, Petr Jelinek wrote:
> Attached version with the above comments near the relevant code.
Looks cooked and ready to serve. Who's gonna commit this? Alvaro, or
do you want me to?
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 S
Chris Rogers wrote
> I'm on PostgreSQL 9.3. This should reproduce on any table with 100,000+
> rows. The EXPLAIN ANALYZE shows many more rows getting scanned with LIMIT
> 2, but I can't figure out why.
>
> EXPLAIN ANALYZE WITH base AS (
> SELECT *, ROW_NUMBER() OVER () AS rownum FROM a_big_tab
Chris Rogers writes:
> I'm on PostgreSQL 9.3. This should reproduce on any table with 100,000+
> rows. The EXPLAIN ANALYZE shows many more rows getting scanned with LIMIT
> 2, but I can't figure out why.
This is not -hackers material.
The first row pulled from the nestloop LEFT JOIN is created
All,
FWIW, I've cleanly applied v8 of this patch to master (252e652) and
check-world was successful. I also successfully ran through a few manual
test cases.
-Adam
--
Adam Brightwell - adam.brightw...@crunchydatasolutions.com
Database Engineer - www.crunchydatasolutions.com
On Fri, Oct 31, 2014 at 2:46 PM, Adam Brightwell <
adam.brightw...@crunchydatasolutions.com> wrote:
>
> All,
>
> FWIW, I've cleanly applied v8 of this patch to master (252e652) and
check-world was successful. I also successfully ran through a few manual
test cases.
>
Thanks for your review!
Rega
Am Freitag, den 31.10.2014, 14:43 + schrieb Greg Stark:
> On Fri, Oct 31, 2014 at 2:28 PM, Mark Woodward
> wrote:
> > I have not kept up with PostgreSQL changes and have just been using it. A
> > co-worker recently told me that you need to word "CONCURRENTLY" in "CREATE
> > INDEX" to avoid tab
On Fri, Oct 31, 2014 at 10:38 AM, Andres Freund wrote:
> I have serious doubts about the number of cases where it's correct to
> access relations in a second backend that are exclusively locked. Not so
> much when that happens for a user issued LOCK statement of course, but
> when the system has d
On 31 October 2014 18:29, Robert Haas wrote:
> Suppose somebody fires off a parallel sort on a text column, or a
> parallel sequential scan involving a qual of the form textcol = 'zog'.
> We launch a bunch of workers to do comparisons; they do lookups
> against pg_collation. After some but not a
On Fri, Oct 31, 2014 at 11:02 AM, Simon Riggs wrote:
> You mentioned earlier that functions would need to be marked proisparallel
> etc..
>
> What conditions will that be protecting against? If we aren't going to
> support the general case where every single thing works, can we at
> least discuss
On 2014-10-31 14:29:57 -0400, Robert Haas wrote:
> On Fri, Oct 31, 2014 at 10:38 AM, Andres Freund
> wrote:
> > I have serious doubts about the number of cases where it's correct to
> > access relations in a second backend that are exclusively locked. Not so
> > much when that happens for a user
On Fri, Oct 31, 2014 at 2:46 PM, Simon Riggs wrote:
> On 31 October 2014 18:29, Robert Haas wrote:
>> Suppose somebody fires off a parallel sort on a text column, or a
>> parallel sequential scan involving a qual of the form textcol = 'zog'.
>> We launch a bunch of workers to do comparisons; they
On Fri, Oct 31, 2014 at 3:32 PM, Andres Freund wrote:
>> > So just refusing parallelism as soon as anything has taken an access
>> > exclusive lock doesn't sound too bad to me.
>>
>> That restriction seems onerous to me; for example, it would prevent a
>> parallel sort for CLUSTER or a parallel in
On 2014-10-31 15:56:42 -0400, Robert Haas wrote:
> On Fri, Oct 31, 2014 at 3:32 PM, Andres Freund wrote:
> >> > So just refusing parallelism as soon as anything has taken an access
> >> > exclusive lock doesn't sound too bad to me.
> >>
> >> That restriction seems onerous to me; for example, it wo
On 27 October 2014 20:24, David Rowley wrote:
> I've had a quick look at this and it seems like a great win! I'm quite
> surprised that we've not got this already. I think this technology could
> also really help performance of queries such as SELECT * from bigtable bt
> WHERE EXISTS(SELECT 1 FROM
All,
> I agree that we should probably seperate the concerns here. Personally,
> I like the idea of being able to say "CURRENT_USER" in utility commands
> to refer to the current user where a role would normally be expected, as
> I could see it simplifying things for some applications, but that'
PostgreSQL has two bits of obsolete, incomplete functionality which
entrap and frustrate new users in large numbers. Both of these features
share the following characteristics:
* added more than 10 years ago
* have the same names as useful features from other databases
* were never finished and l
Josh Berkus writes:
> Of course, I'm talking about the MONEY type and hash indexes (not the
> hash ops class, which is useful, just the index type). It's time to put
> both of these features out to pasture. Certainly neither of theise
> features would be accepted into PostgreSQL today given the
Hi,
On 2014-03-31 12:10:01 +0200, Andres Freund wrote:
> I recently have seen some perf profiles in which _mdfd_getseg() was in
> the top #3 when VACUUMing large (~200GB) relations. Called by mdread(),
> mdwrite(). Looking at it's implementation, I am not surprised. It
> iterates over all segment
On Sat, Nov 1, 2014 at 1:15 AM, Simon Riggs wrote:
> On 31 October 2014 15:46, Petr Jelinek wrote:
>
> > Attached version with the above comments near the relevant code.
>
> Looks cooked and ready to serve. Who's gonna commit this? Alvaro, or
> do you want me to?
>
Could you hold on a bit? I'd l
Andres Freund writes:
> I wrote the attached patch that get rids of that essentially quadratic
> behaviour, by replacing the mdfd chain/singly linked list with an
> array. Since we seldomly grow files by a whole segment I can't see the
> slightly bigger memory reallocations matter significantly. I
On 2014-10-31 18:48:45 -0400, Tom Lane wrote:
> Andres Freund writes:
> > I wrote the attached patch that get rids of that essentially quadratic
> > behaviour, by replacing the mdfd chain/singly linked list with an
> > array. Since we seldomly grow files by a whole segment I can't see the
> > slig
On 10/24/14, 6:17 PM, Jim Nasby wrote:
- Does anyone have a tangible suggestion for how to reduce the code
duplication in patch #6?
Between execute_sql_string() and tcop/exec_simple_query()? Is there stuff in
exec_simple that's not safe for bgwriter? I'm not seeing why we can't use
exec_simple.
On Fri, Oct 31, 2014 at 10:29:53AM -0400, Robert Haas wrote:
> On Thu, Oct 30, 2014 at 11:45 PM, Noah Misch wrote:
> >> Given the lack of prior complaints about this
> >> loop, I'm not sure I see the need to work harder than that; corruption
> >> of this sort must be quite rare.
> >
> > Looks like
On Fri, Oct 31, 2014 at 4:10 PM, Andres Freund wrote:
>> I don't think that's correct. We only need to process local
>> invalidation messages after CommandCounterIncrement(), which I
>> anticipate prohibiting during parallel execution (short thought should
>> convince you that anything else is co
On Fri, Oct 31, 2014 at 6:07 PM, Tom Lane wrote:
> I don't know if/when that will happen as such, but Simon was making noises
> about writing code to treat hash indexes as unlogged automatically, which
> would more or less fix the worst risks. That's not just a special case
> for hash indexes, b
On 31 October 2014 19:36, Robert Haas wrote:
>> It's an obscure case and its not the only solution either.
>
> I don't think that's an obscure situation at all. Do you really think
> a patch that could cause an attempt to VACUUM FULL a system catalog to
> suffer an undetected deadlock meets this
On 31 October 2014 18:47, Robert Haas wrote:
> On Fri, Oct 31, 2014 at 11:02 AM, Simon Riggs wrote:
>> You mentioned earlier that functions would need to be marked proisparallel
>> etc..
>>
>> What conditions will that be protecting against? If we aren't going to
>> support the general case wher
62 matches
Mail list logo
