Re: [HACKERS] Bug in ExecModifyTable function and trigger issues for foreign tables

On 27 November 2017 at 16:35, Tom Lane <t...@sss.pgh.pa.us> wrote: > I wrote: >> Dean Rasheed <dean.a.rash...@gmail.com> writes: >>> A separate point -- it might be marginally more efficient to have the >>> work of rewriteTargetListUD() done after expand

Re: NaNs in numeric_power (was Re: Postgres 11 release notes)

On 15 May 2018 at 22:55, Tom Lane wrote: > David Rowley writes: >> On 16 May 2018 at 02:01, Tom Lane wrote: >>> I'm not particularly fussed about getting credit for that. However, >>> looking again at how that patch series

Re: NaNs in numeric_power (was Re: Postgres 11 release notes)

On 16 May 2018 at 14:44, Tom Lane <t...@sss.pgh.pa.us> wrote: > Dean Rasheed <dean.a.rash...@gmail.com> writes: >> In the case 1 ^ NaN = 1, what should the result scale be? > > The result is exact, so I don't see a reason to be worried about its > scale. Messing wit

Re: MCV lists for highly skewed distributions

lve > right now. The logic suggested by Dean Rasheed in [1] always produces > no MCVs for a perfectly uniform distribution (which is good), but very > often also for other distributions, which is not good. My efforts to > tweak that didn't work, so I didn't get as far as adapting it for t

Re: stricter MCV tests for uniform distributions (was Re: MCV lists for highly skewed distributions)

On 22 January 2018 at 08:07, John Naylor <jcnay...@gmail.com> wrote: > On 1/21/18, Dean Rasheed <dean.a.rash...@gmail.com> wrote: >> It occurs to me that maybe a better test to exclude a value from the >> MCV list would be to demand that its relative standard error not

Re: MCV lists for highly skewed distributions

On 1 February 2018 at 13:16, Simon Riggs wrote: > On 25 January 2018 at 22:19, Tom Lane wrote: >> In any case, since it looks like the next step is for someone to come >> up with a new proposal, I'm going to set this to Waiting on Author. > > Dean and

Re: WINDOW RANGE patch versus leakproofness

On 31 January 2018 at 21:51, Robert Haas <robertmh...@gmail.com> wrote: > On Wed, Jan 31, 2018 at 5:52 AM, Dean Rasheed <dean.a.rash...@gmail.com> > wrote: >> On 30 January 2018 at 16:42, Tom Lane <t...@sss.pgh.pa.us> wrote: >>> So I'm thinking that (a) we d

Re: WINDOW RANGE patch versus leakproofness

On 30 January 2018 at 16:42, Tom Lane wrote: > So I'm thinking that (a) we do not need to check for leaky functions used > in window support, and (b) therefore there's no need to avoid leaky > behavior in in_range support functions. Objections? > Yes, I concur. Since window

Re: MCV lists for highly skewed distributions

On 1 February 2018 at 17:49, Robert Haas wrote: > One point which I want to emphasize is that the length of the MCV list > bounds the estimated frequency of non-MCVs in two ways: no non-MCV is > ever thought to be more frequent than the least-common MCVs, and > however many

Re: MCV lists for highly skewed distributions

On 4 February 2018 at 12:18, John Naylor wrote: > I did the same basic eyeball testing I did on earlier patches, and > this is the best implementation so far. I've attached some typical > pg_stats contents for HEAD and this patch. More rigorous testing, > including of planner

Re: MCV lists for highly skewed distributions

On 7 February 2018 at 15:25, Robert Haas wrote: > Do you plan to press forward with this, then, or what's > the next step? > Yes, I think the results are pretty good so far, especially for the more non-uniform distributions. AFAICS it solves the 2 original complaints, and

Re: [HACKERS] [PATCH] Overestimated filter cost and its mitigation

On 26 July 2018 at 07:12, Ashutosh Bapat wrote: > In the patch clauselist_selectivity() gets called repeatedly for every > new qual added to the clause list. Instead, if we try to combine the > cost/row estimation with order_qual_clauses() or > clauselist_selectivity(), we might be able to what

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On 13 July 2018 at 18:27, Tomas Vondra wrote: > I'm not so sure. The issue is that a lot of the MCV deductions depends > on whether we can answer questions like "Is there a single match?" or > "If we got a match in MCV, do we need to look at the non-MCV part?" This > is not very different from

Re: PG 10: could not generate random cancel key

On 17 July 2018 at 14:04, Michael Paquier wrote: > On Tue, Jul 17, 2018 at 01:33:11PM +0100, Dean Rasheed wrote: >> Looking for precedents elsewhere, I found [2] which does exactly that, >> although I'm slightly dubious about the need for the for-loop there. I >> also foun

PG 10: could not generate random cancel key

Last week I upgraded 15 servers from various pre-10 versions to 10.4. At first everything looked OK, but then (around 4 days later) one of them failed with this in the logs: 2018-07-14 01:53:35.840 BST LOG: could not generate random cancel key 2018-07-14 01:53:37.233 BST LOG: could not

Re: PG 10: could not generate random cancel key

On 18 July 2018 at 03:17, Michael Paquier wrote: >> [1] https://wiki.openssl.org/index.php/Random_Numbers > > This quote from the wiki is scary so that's not quite clean either for > Windows: > "Be careful when deferring to RAND_poll on some Unix systems because it > does not seed the generator.

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On 15 July 2018 at 14:29, Tomas Vondra wrote: > It's quite unclear to me how this algorithm could reliably end up with > hist_sel=0 (in cases where we already don't end up with that). I mean, > if a bucket matches the conditions, then the only way to eliminate is by > deducing that MCV already

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On 16 July 2018 at 21:55, Tomas Vondra wrote: > > > On 07/16/2018 02:54 PM, Dean Rasheed wrote: >> On 16 July 2018 at 13:23, Tomas Vondra wrote: >>>>> The top-level clauses allow us to make such deductions, with deeper >>>>> clauses it's much

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On 16 July 2018 at 13:23, Tomas Vondra wrote: >>> The top-level clauses allow us to make such deductions, with deeper >>> clauses it's much more difficult (perhaps impossible). Because for >>> example with (a=1 AND b=1) there can be just a single match, so if we >>> find it in MCV we're done.

Re: PG 10: could not generate random cancel key

On 18 July 2018 at 14:01, Michael Paquier wrote: > Thanks for the updated version. This looks safer to me. It is possible > to simplify the code by removing the external RAND_status() call and > check for RAND_status() first in the loop as per the attached. OK, thanks. Barring any further

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On 3 September 2018 at 00:17, Tomas Vondra wrote: > Hi, > > Attached is an updated version of the patch series, adopting a couple of > improvements - both for MCV lists and histograms. > > > MCV > --- > > For the MCV list part, I've adopted the approach proposed by Dean, using > base selectivity

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On 24 June 2018 at 20:45, Tomas Vondra wrote: > Attached is a rebased version of this patch series, mostly just fixing > the breakage caused by reworked format of initial catalog data. > > Aside from that, the MCV building now adopts the logic introduced by > commit b5db1d93d2 for single-column

Re: Bogus use of canonicalize_qual

On 10 March 2018 at 20:21, Tom Lane wrote: > I wrote: >> Whilst fooling about with predtest.c, I noticed a rather embarrassing >> error. Consider the following, rather silly, CHECK constraint: >> ... >> So, what to do? We have a few choices, none ideal: > > I'd been assuming

Re: MCV lists for highly skewed distributions

On 6 March 2018 at 08:51, John Naylor <jcnay...@gmail.com> wrote: > On 3/5/18, Dean Rasheed <dean.a.rash...@gmail.com> wrote: >> Attached is an updated patch. > Nice. The results look good. Thanks for the review. > I agree it should be in a separate function. As

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On 7 April 2018 at 15:12, Bruce Momjian wrote: > Uh, where are we on this patch? It isn't going to make it into PG 11? > Feature development for this has been going on for years. Unfortunately, I think there's no way that this will be ready for PG11. So far, I have only read

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On 27 March 2018 at 01:36, Tomas Vondra wrote: > BTW I think there's a bug in handling the fullmatch flag - it should not > be passed to AND/OR subclauses the way it is, because then > > WHERE a=1 OR (a=2 AND b=2) > > will probably set it to 'true' because of

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On 27 March 2018 at 01:36, Tomas Vondra wrote: > 4) handling of NOT clauses in MCV lists (and in histograms) > > The query you posted does not fail anymore... > Ah, it turns out the previous query wasn't actually failing for the reason I thought it was -- it was

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On 27 March 2018 at 14:58, Dean Rasheed <dean.a.rash...@gmail.com> wrote: > On 27 March 2018 at 01:36, Tomas Vondra <tomas.von...@2ndquadrant.com> wrote: >> 4) handling of NOT clauses in MCV lists (and in histograms) >> >> The query you posted does not fail

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On 18 March 2018 at 23:57, Tomas Vondra wrote: > Attached is an updated version of the patch series, addressing issues > pointed out by Alvaro. I'm just starting to look at this now, and I think I'll post individual comments/questions as I get to them rather than

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On 28 March 2018 at 15:50, Tomas Vondra wrote: > After thinking about this a bit more, I'm not sure if updating the info > based on recursive calls makes sense. The fullmatch flag was supposed to > answer a simple question - can there be just a single matching item?

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On 26 March 2018 at 14:08, Tomas Vondra <tomas.von...@2ndquadrant.com> wrote: > On 03/26/2018 12:31 PM, Dean Rasheed wrote: >> A wider concern I have is that I think this function is trying to be >> too clever by only resetting selected stats. IMO it should just reset >>

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On 18 March 2018 at 23:57, Tomas Vondra wrote: > Attached is an updated version of the patch series, addressing issues > pointed out by Alvaro. I've just been reading the new code in statext_clauselist_selectivity() and mcv_clauselist_selectivity(), and I'm having a

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On 28 March 2018 at 01:34, Tomas Vondra wrote: > Attached is a patch fixing this. In the end I've decided to keep both > branches - one handling boolean Vars and one for NOT clauses. I think > you're right we can only see (NOT var) cases, but I'm not sure about that.

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On 26 March 2018 at 20:17, Tomas Vondra <tomas.von...@2ndquadrant.com> wrote: > On 03/26/2018 09:01 PM, Dean Rasheed wrote: >> Also, just above that, in statext_clauselist_selectivity(), it >> computes the list stat_clauses, then doesn't appear to use it >> anywhere. I

Re: MCV lists for highly skewed distributions

On 17 March 2018 at 17:16, Dean Rasheed <dean.a.rash...@gmail.com> wrote: > Using the calculator above, you can see that the distribution is > fairly normal-like, but with a noticeable positive skew. The 2-stddev > interval is 0.6 to 9.4, and according to the calculator th

Re: MCV lists for highly skewed distributions

On 16 March 2018 at 15:26, Tomas Vondra wrote: > Actually, one question - when deciding whether to keep the item in the > MCV list, analyze_mcv_list only compares it's frequency with an average > of the rest. But as we're removing items from the MCV list, the average

Re: MCV lists for highly skewed distributions

On 17 March 2018 at 18:40, Tomas Vondra wrote: > Currently, analyze_mcv_list only checks if the frequency of the current > item is significantly higher than the non-MCV selectivity. My question > is if it shouldn't also consider if removing the item from MCV would

Re: MCV lists for highly skewed distributions

On 13 March 2018 at 08:39, John Naylor wrote: >> Also, this is common enough that in fact that distribution >> can be reasonably approximated by a normal distribution. > > For the archives, because it's typically seen 10 times in the sample, > per the rule of thumb mention

Re: MCV lists for highly skewed distributions

On 19 March 2018 at 16:59, John Naylor <jcnay...@gmail.com> wrote: > On 3/19/18, Dean Rasheed <dean.a.rash...@gmail.com> wrote: >> As promised, here is a new patch, with comment updates, per John and >> Tomas' suggestions, plus the continuity correction, which seem

Re: MCV lists for highly skewed distributions

On 18 March 2018 at 12:24, John Naylor wrote: > Over the weekend I tried out a test to measure: > -The size of the MCV list > -The ratio between actual and estimated cardinality of the least > common value in the MCV list. > -The ratio between actual and estimated cardinality

Re: MCV lists for highly skewed distributions

> On 03/11/2018 10:23 AM, Dean Rasheed wrote: >> I'm moving this back to a status of "Needs review" partly because the >> code has changed significantly, but also because I want to do more >> testing, particularly with larger datasets. >> John, Tomas, Thanks

Re: MCV lists for highly skewed distributions

On 18 March 2018 at 22:52, Dean Rasheed <dean.a.rash...@gmail.com> wrote: > I'll post something tomorrow, once I've finished wordsmithing the comments. > As promised, here is a new patch, with comment updates, per John and Tomas' suggestions, plus the continuity correction, which

Re: MCV lists for highly skewed distributions

On 1 March 2018 at 21:01, Andres Freund wrote: > This sounds like the patch's status of "waiting on author" isn't right, > and it should more be ready for committer? > Yes, I'll take a look at it this weekend. Regards, Dean

Re: MCV lists for highly skewed distributions

On 7 February 2018 at 15:58, Dean Rasheed <dean.a.rash...@gmail.com> wrote: > On 7 February 2018 at 15:25, Robert Haas <robertmh...@gmail.com> wrote: >> Do you plan to press forward with this, then, or what's >> the next step? > > I plan to do more testing TL;DR:

Re: BUG #15307: Low numerical precision of (Co-) Variance

On 27 September 2018 at 06:12, Madeleine Thompson wrote: > This is my first PostgreSQL review. Hopefully I'm getting it right. I > independently ran into the bug in question and found that the author had > already written a patch. I'm happy the author wrote this. > Thanks for the review. And

Re: BUG #15307: Low numerical precision of (Co-) Variance

On Thu, 27 Sep 2018 at 14:22, Dean Rasheed wrote: > I'll post an updated patch in a while. > I finally got round to looking at this again. Here is an update, based on the review comments. The next question is whether or not to back-patch this. Although this was reported as a bug, my incli

Re: BUG #15307: Low numerical precision of (Co-) Variance

On Wed, 3 Oct 2018 at 15:58, Madeleine Thompson wrote: > This diff looks good to me. Also, it applies cleanly against > abd9ca377d669a6e0560e854d7e987438d0e612e and passes `make > check-world`. > > I agree that this is not suitable for a patch release. > Pushed to master. Thanks for the review.

Re: [HACKERS] proposal: schema variables

AFAICS this patch does nothing to consider parallel safety -- that is, as things stand, a variable is allowed in a query that may be parallelised, but its value is not copied to workers, leading to incorrect results. For example: create table foo(a int); insert into foo select * from

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On Thu, 17 Jan 2019 at 03:42, David Rowley wrote: > 39. I don't see analyze_mcv_list() being used anywhere around this comment: > > * If we can fit all the items onto the MCV list, do that. Otherwise use > * analyze_mcv_list to decide how many items to keep in the MCV list, just > * like for the

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On Thu, 17 Jan 2019 at 03:42, David Rowley wrote: > 35. The evaluation order of this macro is wrong. > > #define ITEM_SIZE(ndims) \ > (ndims * (sizeof(uint16) + sizeof(bool)) + 2 * sizeof(double)) > > You'd probably want ITEM_SIZE(10) to return 170, but: > > select (10 * (2 + 1) + 2 * 8); >

Re: Statement-level Triggers For Uniqueness Checks

On Mon, 24 Dec 2018 at 23:57, Corey Huinker wrote: > > So I took a first pass at this, and I got stuck. > > [snip] > > Any idea where I went wrong? Take a look at this code in AfterTriggerSaveEvent(): /* * If the trigger is a deferred unique constraint check trigger, only

Re: Statement-level Triggers For Uniqueness Checks

On Tue, 25 Dec 2018 at 08:04, Dean Rasheed wrote: > Take a look at this code in AfterTriggerSaveEvent(): > Note that the intention behind that code is that in the (fairly common) case where an insert or update operation is known to not lead to any potential PK/UNIQUE index viol

Re: COPY FROM WHEN condition

On Sat, 24 Nov 2018 at 02:09, Tomas Vondra wrote: > On 11/23/18 12:14 PM, Surafel Temesgen wrote: > > On Sun, Nov 11, 2018 at 11:59 PM Tomas Vondra > > mailto:tomas.von...@2ndquadrant.com>> wrote: > > So, what about using FILTER here? We already use it for aggregates when > > filtering

Re: BUG #15446: Crash on ALTER TABLE

On Wed, 9 Jan 2019 at 23:24, Andrew Dunstan wrote: > Here's another attempt. For the rewrite case it kept the logic of the > previous patch to clear all the missing attributes, but if we're not > rewriting we reconstruct the missing value according to the new type > settings. > Looks good to me.

Policy on cross-posting to multiple lists

Has the policy on cross-posting to multiple lists been hardened recently? The "Crash on ALTER TABLE" thread [1] started on -bugs, but Andrew's message on 8 Jan with an initial proposed patch and my response later that day both CC'ed -hackers and seem to have been rejected, and so are missing from

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On Wed, 9 Jan 2019 at 15:40, Tomas Vondra wrote: > On 1/8/19 3:18 PM, Dean Rasheed wrote: > > So actually, the estimate for a group of values will be either the MCV > > item's frequency (if the MCV item is kept), or (roughly) the MCV > > item's base_frequency (if the

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On Wed, 26 Dec 2018 at 22:09, Tomas Vondra wrote: > > Attached is an updated version of the patch - rebased and fixing the > warnings reported by Thomas Munro. > Here are a few random review comments based on what I've read so far: On the CREATE STATISTICS doc page, the syntax in the new

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On Mon, 7 Jan 2019 at 00:45, Tomas Vondra wrote: > > FWIW the main unsolved issue (at least on the MCV part) is how it > decides which items to keep in the list. > > As explained in [1], in the multivariate case we can't simply look at > the group frequency and compare it to the average frequency

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On Fri, 11 Jan 2019, 21:18 Tomas Vondra > On 1/10/19 4:20 PM, Dean Rasheed wrote: > > ... > > > > So perhaps what we should do for multivariate stats is simply use the > > relative standard error approach (i.e., reuse the patch in [2] with a > > 20% RSE c

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On Sun, 13 Jan 2019 at 00:04, Tomas Vondra wrote: > On 1/12/19 8:49 AM, Dean Rasheed wrote: > > A possible refinement would be to say that if there are more than > > stats_target items more common than this mincount threshold, rather than > > excluding the least common o

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

(Removing Adrien from the CC list, because messages to that address keep bouncing) On Sun, 13 Jan 2019 at 00:31, Tomas Vondra wrote: > > On 1/10/19 6:09 PM, Dean Rasheed wrote: > > > > In the previous discussion around UpdateStatisticsForTypeChange(), the > > consensu

Re: BUG #15708: RLS 'using' running as wrong user when called from a view

On Mon, 25 Mar 2019 at 20:27, Stephen Frost wrote: > > * Dean Rasheed (dean.a.rash...@gmail.com) wrote: > > > It looks like the best place to fix it is in > > get_policies_for_relation(), since that's where all the policies to be > > applied for a given RTE are pull

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On Tue, 26 Mar 2019 at 11:59, Dean Rasheed wrote: > > On Mon, 25 Mar 2019 at 23:36, Tomas Vondra > wrote: > > > > Attached is an updated patch... > > I just looked through the latest set of changes and I have a couple of > additional review comments: > I

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On Mon, 25 Mar 2019 at 23:36, Tomas Vondra wrote: > > Attached is an updated patch, fixing all the issues pointed out so far. > Unless there are some objections, I plan to commit the 0001 part by the > end of this CF. Part 0002 is a matter for PG13, as previously agreed. > Yes, I think that's

Re: BUG #15708: RLS 'using' running as wrong user when called from a view

On Thu, 21 Mar 2019 at 00:39, PG Bug reporting form wrote: > > This fails, seemingly because the RLS on 'bar' is being checked by alice, > instead of the view owner bob: > Yes I agree, that appears to be a bug. The subquery in the RLS policy should be checked as the view owner -- i.e., we need

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On Sun, 24 Mar 2019 at 00:17, David Rowley wrote: > > On Sun, 24 Mar 2019 at 12:41, Tomas Vondra > wrote: > > > > On 3/21/19 4:05 PM, David Rowley wrote: > > > > 29. Looking at the tests I see you're testing that you get bad > > > estimates without extended stats. That does not really seem

Re: BUG #15623: Inconsistent use of default for updatable view

On Thu, 28 Feb 2019 at 07:47, Amit Langote wrote: > > +if (attrno == 0) > +elog(ERROR, "Cannot set value in column %d to > DEFAULT", i); > > Maybe: s/Cannot/cannot/g > Ah yes, you're right. That is the convention. > +Assert(list_length(sublist) ==

Re: BUG #15623: Inconsistent use of default for updatable view

On Tue, 12 Feb 2019 at 10:33, Dean Rasheed wrote: > Here's an updated patch ... So I pushed that. However, ... Playing around with it some more, I realised that whilst this appeared to fix the reported problem, it exposes another issue which is down to the interaction betw

Re: Row Level Security − leakproof-ness and performance implications

On Thu, 28 Feb 2019 at 14:13, Robert Haas wrote: > A wild idea might be to let > proleakproof take on three values: yes, no, and maybe. When 'maybe' > functions are involved, we tell them whether or not the current query > involves any security barriers, and if so they self-censor. > Does

INSERT ... OVERRIDING USER VALUE vs GENERATED ALWAYS identity columns

So I started looking into the bug noted in [1], but before getting to multi-row inserts, I concluded that the current single-row behaviour isn't spec-compliant. In particular, Syntax Rule 11b of section 14.11 says that an INSERT statement on a GENERATED ALWAYS identity column must specify an

Re: INSERT ... OVERRIDING USER VALUE vs GENERATED ALWAYS identity columns

On Mon, 25 Feb 2019 at 12:47, Peter Eisentraut wrote: > > - and the column in new rows will automatically have values from the > > - sequence assigned to it. > > + and new rows in the column will automatically have values from the > > + sequence assigned to them. > > The "it"

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On Fri, 15 Mar 2019 at 00:06, Tomas Vondra wrote: > I've noticed an annoying thing when modifying type of column not > included in a statistics... > > That is, we don't remove the statistics, but the estimate still changes. > But that's because the ALTER TABLE also resets reltuples/relpages: > >

Re: pgsql: Add support for hyperbolic functions, as well as log10().

On Thu, 14 Mar 2019 at 04:41, Tom Lane wrote: > > Dean Rasheed writes: > > I'm amazed that jacana's asinh() returned -0 for an input of +0. > > Even more amusingly, it returns NaN for acosh('infinity'), cf > https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=jacana=2019

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On Fri, 15 Mar 2019 at 00:06, Tomas Vondra wrote: > ... attached patch ... Some more review comments, carrying on from where I left off: 16). This regression test fails for me: @@ -654,11 +654,11 @@ -- check change of unrelated column type does not reset the MCV statistics ALTER TABLE

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On Sat, 16 Mar 2019 at 23:44, Tomas Vondra wrote: > > > 21). For consistency with other bms_ functions, I think the name of > > the Bitmapset argument for bms_member_index() should just be called > > "a". Nitpicking, I'd also put bms_member_index() immediately after > > bms_is_member() in the

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On Sat, 16 Mar 2019 at 23:44, Tomas Vondra wrote: > > > 28). I just spotted the 1MB limit on the serialised MCV list size. I > > think this is going to be too limiting. For example, if the stats > > target is at its maximum of 1, that only leaves around 100 bytes > > for each item's values,

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On Sat, 16 Mar 2019 at 23:44, Tomas Vondra wrote: > > > > 16). This regression test fails for me: > > > > @@ -654,11 +654,11 @@ > > -- check change of unrelated column type does not reset the MCV statistics > > ALTER TABLE mcv_lists ALTER COLUMN d TYPE VARCHAR(64); > > SELECT * FROM

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On Sun, 10 Mar 2019 at 13:09, Dean Rasheed wrote: > Here are some more comments: > One more thing --- the comment for statext_clauselist_selectivity() says: * So (simple_selectivity - base_selectivity) may be seen as a correction for * the part not covered by the MCV list. That's not

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On Sun, 10 Mar 2019 at 17:36, Tomas Vondra wrote: > On 3/10/19 2:09 PM, Dean Rasheed wrote: > > 14). The attnums Bitmapset passed to > > statext_is_compatible_clause_internal() is an input/output argument > > that it updates. That should probably be documented. Whe

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On Sun, 10 Mar 2019 at 22:28, David Rowley wrote: > > On Mon, 11 Mar 2019 at 06:36, Tomas Vondra > wrote: > > > > On 3/9/19 7:33 PM, Dean Rasheed wrote: > > > I wonder if it's possible to write smaller, more targeted tests. > > > Currently "stats_

Re: [Patch] Log10 and hyperbolic functions for SQL:2016 compliance

On Sun, 3 Feb 2019 at 15:12, Tom Lane wrote: > > Andrew Gierth writes: > > The spec doesn't require the inverse functions (asinh, acosh, atanh), > > but surely there is no principled reason to omit them? > > +1 --- AFAICS, the C library has offered all six since C89. > +1 for including the

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On Thu, 28 Feb 2019 at 19:56, Tomas Vondra wrote: > Attached is an updated version of this patch series. Here are some random review comments. I'll add more later, but I'm out of energy for today. 1). src/test/regress/expected/type_sanity.out has bit-rotted. 2). Duplicate OIDs (3425). 3). It

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On Sat, 9 Mar 2019 at 18:33, Dean Rasheed wrote: > > On Thu, 28 Feb 2019 at 19:56, Tomas Vondra > wrote: > > Attached is an updated version of this patch series. > > Here are some random review comments. I'll add more later, but I'm out > of energy for today. > Here

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

On Wed, 6 Feb 2019 at 23:44, Tomas Vondra wrote: > > On 2/6/19 10:59 PM, David Rowley wrote: > > On Thu, 7 Feb 2019 at 03:16, Alvaro Herrera > > wrote: > >> I wonder what should we be doing with this series -- concretely, should > >> the effort concentrate on one of the two patches, and leave

Multivariate MCV lists -- pg_mcv_list_items() seems to be broken

I just noticed the following: CREATE TABLE foo (a int, b int); INSERT INTO foo SELECT x/10, x/100 FROM generate_series(1, 100) x; CREATE STATISTICS foo_s ON a,b FROM foo; ANALYSE foo; SELECT pg_mcv_list_items(stxmcv) from pg_statistic_ext WHERE stxname = 'foo_s'; which fails with ERROR: cache

Re: pgsql: Add support for hyperbolic functions, as well as log10().

On Wed, 13 Mar 2019, 21:56 Tom Lane, wrote: > > Of these, probably the least bad is #3, even though it might require > a few rounds of experimentation to find the best extra_float_digits > setting to use. I'll go try it without any roundoff, just to see > what the raw results look like ... >

Re: Choosing values for multivariate MCV lists

On Tue, 18 Jun 2019 at 21:59, Tomas Vondra wrote: > > The current implementation of multi-column MCV lists (added in this > cycle) uses a fairly simple algorithm to pick combinations to include in > the MCV list. We just compute a minimum number of occurences, and then > include all entries

Re: Multivariate MCV list vs. statistics target

On Thu, 20 Jun 2019 at 23:12, Tomas Vondra wrote: > > On Thu, Jun 20, 2019 at 08:08:44AM +0100, Dean Rasheed wrote: > >On Tue, 18 Jun 2019 at 22:34, Tomas Vondra > >wrote: > >> > >> So I'm thinking we should allow tweaking the statistics fo

Re: Choosing values for multivariate MCV lists

On Thu, 20 Jun 2019 at 23:35, Tomas Vondra wrote: > > On Thu, Jun 20, 2019 at 06:55:41AM +0100, Dean Rasheed wrote: > > >I'm not sure it's easy to justify ordering by Abs(freq-base_freq)/freq > >though, because that would seem likely to put too much weight on the > &

Re: Multivariate MCV stats can leak data to unprivileged users

On Mon, 13 May 2019 at 23:36, Tomas Vondra wrote: > > On Fri, May 10, 2019 at 10:19:44AM +0100, Dean Rasheed wrote: > >While working on 1aebfbea83c, I noticed that the new multivariate MCV > >stats feature suffers from the same problem, and also the original > >

Re: Choosing values for multivariate MCV lists

On Sat, 22 Jun 2019 at 15:10, Tomas Vondra wrote: > One annoying thing I noticed is that the base_frequency tends to end up > being 0, most likely due to getting too small. It's a bit strange, though, > because with statistic target set to 10k the smallest frequency for a > single column is

Re: Choosing values for multivariate MCV lists

On Mon, 24 Jun 2019 at 00:42, Tomas Vondra wrote: > > On Sun, Jun 23, 2019 at 10:23:19PM +0200, Tomas Vondra wrote: > >On Sun, Jun 23, 2019 at 08:48:26PM +0100, Dean Rasheed wrote: > >>On Sat, 22 Jun 2019 at 15:10, Tomas Vondra > >>wrote: >

Re: Multivariate MCV stats can leak data to unprivileged users

On Thu, 6 Jun 2019 at 21:33, Tomas Vondra wrote: > > Hi, > > Attached are three patches tweaking the stats - two were already posted > in this thread, the third one is just updating docs. > > 1) 0001 - split pg_statistic_ext into definition + data > > This is pretty much the patch Dean posted

Re: Multivariate MCV stats can leak data to unprivileged users

On Sat, 18 May 2019 at 16:13, Tom Lane wrote: > > Dean Rasheed writes: > > On the other hand, pg_dump relies on pg_statistic_ext to work out > > which extended statistics objects to dump. If we were to change that > > to use pg_stats_ext, then a user dumpin

Re: Multivariate MCV stats can leak data to unprivileged users

On Sun, 19 May 2019 at 15:28, Tom Lane wrote: > > > I wonder ... another way we could potentially do this is > > > create table pg_statistic_ext_data( > > stxoid oid, -- OID of owning pg_statistic_ext entry > > stxkind char, -- what kind of data > > stxdata bytea -- the data, in some

Re: Multivariate MCV stats can leak data to unprivileged users

On Sun, 19 May 2019 at 00:48, Stephen Frost wrote: > > * Tom Lane (t...@sss.pgh.pa.us) wrote: > > Tomas Vondra writes: > > > > > I think we have four options - rework it before beta1, rework it after > > > beta1, rework it in PG13 and leave it as it is now. > > > > Yup, that's about what the

Re: Multivariate MCV stats can leak data to unprivileged users

On Sun, 19 May 2019 at 23:45, Tomas Vondra wrote: > > Oh, right. It still has the disadvantage that it obfuscates the actual > data stored in the pg_stats_ext_data (or whatever would it be called), > so e.g. functions would have to do additional checks to make sure it > actually is the right

Re: Multivariate MCV stats can leak data to unprivileged users

On Mon, 20 May 2019 at 14:32, Tom Lane wrote: > > Dean Rasheed writes: > > On Sun, 19 May 2019 at 23:45, Tomas Vondra > > wrote: > >> Oh, right. It still has the disadvantage that it obfuscates the actual > >> data stored in the pg_stats_ext_data (or whate

Re: Multivariate MCV stats can leak data to unprivileged users

On Sat, 18 May 2019 at 10:11, Dean Rasheed wrote: > > On Fri, 17 May 2019 at 21:29, Andres Freund wrote: > > > > On 2019-05-16 14:28:03 +0100, Dean Rasheed wrote: > > > 5). Some columns from pg_statistic_ext have to be made visible for > > > psql \d to

Re: Multivariate MCV stats can leak data to unprivileged users

On Fri, 17 May 2019 at 21:29, Andres Freund wrote: > > On 2019-05-16 14:28:03 +0100, Dean Rasheed wrote: > > 5). Some columns from pg_statistic_ext have to be made visible for > > psql \d to work. Basically, it needs to be able to query for the > > existence of extended st

  1   2   3   4   5   6   >