Re: [HACKERS] 2016-03 Commitfest Manager
On Wed, Mar 02, 2016 at 10:49:01AM +0900, Michael Paquier wrote: > On Wed, Mar 2, 2016 at 7:22 AM, Michael Paquier > wrote: > > On Wed, Mar 2, 2016 at 6:15 AM, David Steele wrote: > >> On 3/1/16 3:04 PM, Tom Lane wrote: > >>> David Steele writes: > >>>> I volunteered a while back to be the CFM and I haven't seen any other > >>>> volunteers or objections to my offer. > >>> > >>>> I am still ready, eager, and willing! > >>> > >>> I haven't heard any other volunteers either. You have the conn, sir. > >> > >> Aye aye! Once the 2016-03 CF has been closed I'll send out a kickoff > >> report. > > > > Magnus, should David have administration rights in the CF app? I > > believe that he cannot change the CF status now. We should switch to > > "In Progress". I can do that at least. > > I am planning to switch 2016-03 from "Open" to "Processing" in a > couple of hours, at which stage nobody will be able to register new > patches to it. I guess it's more than time. How do I get my weighted stats patch in? Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] 2016-03 Commitfest Manager
On Wed, Mar 02, 2016 at 03:34:39PM +0900, Michael Paquier wrote: > On Wed, Mar 2, 2016 at 2:53 PM, David Fetter wrote: > > On Wed, Mar 02, 2016 at 10:49:01AM +0900, Michael Paquier wrote: > >> On Wed, Mar 2, 2016 at 7:22 AM, Michael Paquier > >> wrote: > >> > On Wed, Mar 2, 2016 at 6:15 AM, David Steele wrote: > >> >> On 3/1/16 3:04 PM, Tom Lane wrote: > >> >>> David Steele writes: > >> >>>> I volunteered a while back to be the CFM and I haven't seen any other > >> >>>> volunteers or objections to my offer. > >> >>> > >> >>>> I am still ready, eager, and willing! > >> >>> > >> >>> I haven't heard any other volunteers either. You have the conn, sir. > >> >> > >> >> Aye aye! Once the 2016-03 CF has been closed I'll send out a kickoff > >> >> report. > >> > > >> > Magnus, should David have administration rights in the CF app? I > >> > believe that he cannot change the CF status now. We should switch to > >> > "In Progress". I can do that at least. > >> > >> I am planning to switch 2016-03 from "Open" to "Processing" in a > >> couple of hours, at which stage nobody will be able to register new > >> patches to it. I guess it's more than time. > > > > How do I get my weighted stats patch in? > > Why didn't you register it before and why didn't you send a new > version yet? Looking at this stuff now the last activity for this > patch was the 12th of January, with no new version, just a mention > that the patch will be fixed: > http://www.postgresql.org/message-id/cajrrpgf1kt-5bn+rpunxmkcigxnqs5_ratyqseun-xff9h+...@mail.gmail.com I have been pretty busy with some high-priority family and work stuff, including a terminal illness (not mine) and a new job. None of that is a reason to go around the CF process. I'll see whether I can wheedle a committer's time on this once I have the new patch. If I can't, I'll queue it for the next one. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Weighted Stats
On Fri, Jan 08, 2016 at 04:37:36PM +1100, Haribabu Kommi wrote: > On Mon, Dec 21, 2015 at 1:50 PM, David Fetter wrote: > > On Sun, Dec 20, 2015 at 06:13:33PM -0600, Jim Nasby wrote: > >> On 11/2/15 5:46 PM, David Fetter wrote: > >> >I'd like to add weighted statistics to PostgreSQL > >> > >> Anything happen with this? If community isn't interested, ISTM it'd be good > >> to put this in PGXN. > > > > I think it's already in PGXN as an extension, and I'll get another > > version out this early this week, as it involves mostly adding some > > tests. > > > > I'll do the float8 ones for core this week, too, and unless there's a > > really great reason to do more data types on the first pass, it should > > be in committable shape. > > I reviewed the patch, following are my observations. > > 1. + precision, numeric, or interval > > with interval type it is giving problem. As interval data type is not > supported, > so remove it in the list of supported inputs. Done. > > 2. +float8_weighted_avg(PG_FUNCTION_ARGS) > > It will be helpful, if you provide some information as a function header, > how the weighted average is calculated similar like other weighted functions. Done. > 3. + transvalues = check_float8_array(transarray, > "float8_weighted_stddev_accum", 4); > > The second parameter to check_float8_array should be "float8_weighted_accum". Done. > 4. There is an OID conflict of 4066 with latest master code. Fixed. > 5.+ A += newvalW * ( newvalX - transvalues[2] ) / W; > + CHECKFLOATVAL(A, isinf(newvalW) || isinf(newvalX - transvalues[2]) > || isinf(1.0/W), true); > > + Q += newvalW * (newvalX - transvalues[2]) * (newvalX - A); > + CHECKFLOATVAL(A, isinf(newvalX - transvalues[3]) || isinf(newvalX - > A) || isinf(1.0/W), true); > > > Is the need of calculation also needs to be passed to CHECKFLOATVAL? > Just passing > the variables involved in the calculation isn't enough? If expressions > are required then > it should be something as follows? > > CHECKFLOATVAL(A, isinf(transvalues[2]) || isinf(newvalW) || > isinf(newvalX - transvalues[2]) || isinf(1.0/W), true); > > CHECKFLOATVAL(Q, isinf(transvalues[3]) || isinf(newvalX - > transvalues[2]) || isinf(newvalX - A) || isinf(1.0/W), true); Done. Please find attached a patch that uses the float8 version to cover the numeric types. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 000489d..09ada7e 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -12645,6 +12645,29 @@ NULL baz(3 rows) +weighted_average + + +weighted_avg + + weighted_avg(value expression, weight expression) + + + smallint, int, + bigint, real, double + precision, numeric, or interval + + + numeric for any integer-type argument, + double precision for a floating-point argument, + otherwise the same as the argument data type + + the average (arithmetic mean) of all input values, weighted by the input weights + + + + + bit_and bit_and(expression) @@ -13288,6 +13311,29 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; +weighted standard deviation +population + + +weighted_stddev_pop + + weighted_stddev_pop(value expression, weight expression) + + + smallint, int, + bigint, real, double + precision, or numeric + + + double precision for floating-point arguments, + otherwise numeric + + weighted population standard deviation of the input values + + + + + standard deviation sample @@ -13311,6 +13357,29 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; +weighted standard deviation +sample + + +weighted_stddev_samp + + weighted_stddev_samp(value expression, weight expression) + + + smallint, int, + bigint, real, double + precision, or numeric + + + double precision for floating-point arguments, + otherwise numeric + + weighted sample standard deviation of the input values + + + + + variance variance(expression) diff -
Re: [HACKERS] Weighted Stats
On Fri, Mar 18, 2016 at 06:12:12PM -0700, Jeff Janes wrote: > On Tue, Mar 15, 2016 at 8:36 AM, David Fetter wrote: > > > > Please find attached a patch that uses the float8 version to cover the > > numeric types. > > Is there a well-defined meaning for having a negative weight? If no, > should it be disallowed? Opinions on this appear to vary. A Wikipedia article defines weights as non-negative, while a manual to which it refers only uses non-zero. https://en.wikipedia.org/wiki/Weighted_arithmetic_mean#Mathematical_definition https://www.gnu.org/software/gsl/manual/html_node/Weighted-Samples.html I'm not sure which if either would be authoritative, but I could certainly make up variants for each assumption. The assumption they have in common about weights is that a zero weight is not part of the calculation, which assumption is implemented in the previously submitted code. > I don't know what I was expecting, but not this: > > select weighted_avg(x,1000-2*x) from generate_series(1,1000) f(x); >weighted_avg > -- > 1671666717.1 I'm guessing that negative weights can cause bizarre outcomes, assuming it turns out we should allow them. > Also, I think it might not give the correct answer even without > negative weights: > > create table foo as select floor(random()*1)::int val from > generate_series(1,1000); > > create table foo2 as select val, count(*) from foo group by val; > > Shouldn't these then give the same result: > > select stddev_samp(val) from foo; > stddev_samp > --- > 2887.054977297105 > > select weighted_stddev_samp(val,count) from foo2; > weighted_stddev_samp > -- > 2887.19919651336 > > The 5th digit seems too early to be seeing round-off error. Please pardon me if I've misunderstood, but you appear to be assuming that SELECT val, count(*) FROM foo GROUP BY val will produce precisely identical count(*)s at each row, which it overwhelmingly likely won't, producing the difference you see above. What have I misunderstood? Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Weighted Stats
On Sat, Mar 19, 2016 at 05:04:08PM +0100, Tomas Vondra wrote: > Hi, > > On 03/19/2016 07:34 AM, David Fetter wrote: > >On Fri, Mar 18, 2016 at 06:12:12PM -0700, Jeff Janes wrote: > >>On Tue, Mar 15, 2016 at 8:36 AM, David Fetter wrote: > >>> > >>>Please find attached a patch that uses the float8 version to cover the > >>>numeric types. > >> > >>Is there a well-defined meaning for having a negative weight? If no, > >>should it be disallowed? > > > >Opinions on this appear to vary. A Wikipedia article defines weights > >as non-negative, while a manual to which it refers only uses non-zero. > > > >https://en.wikipedia.org/wiki/Weighted_arithmetic_mean#Mathematical_definition > >https://www.gnu.org/software/gsl/manual/html_node/Weighted-Samples.html > > I don't think that actually allows negative weights. It says that > >w_i = 1/\sigma_i^2 > > and variance is always > 0, thus w_i > 0. The zero is used as a special flag > to remove the sample from the data set in a simple way. > > >I'm not sure which if either would be authoritative, but I could > >certainly make up variants for each assumption. > > > >The assumption they have in common about weights is that a zero > >weight is not part of the calculation, which assumption is > >implemented in the previously submitted code. > > I think that if we're not sure what should happen with negative weights, > then we should disallow them. It's easy to allow them later once we have a > reasonable definition, but if we allow them now and later realize it should > behave differently, we'll be in trouble because of breaking existing uses. OK > I can't really come up with a reasonable example that would actually use > negative weights. Can you? That would probably help with defining the > behavior correctly. No, but I'm not a statistician. I've seen them mentioned in contexts that appear to be discussions among same, and again opinions vary. > Allowing negative weights has other consequences. For example, what if > sum(W) ends up being 0? For example > > CREATE TABLE t (a float, b float); > INSERT INTO t SELECT i, 1 FROM generate_series(1,1000) s(i); > INSERT INTO t SELECT i, -1 FROM generate_series(1,1000) s(i); > > SELECT weighted_avg(a,b) FROM t; > weighted_avg > -- > NaN > (1 row) > > Is that the correct behavior? Why? It's not, and you're right. I will send a patch that disallows negative weights this evening or tomorrow. It will be slightly more complicated as I believe I will need to create a new accumulator function for the weighted_avg() case where I had been using an extant one before. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] [COMMITTERS] pgsql: Support parallel aggregation.
On Mon, Mar 21, 2016 at 01:33:28PM +, Robert Haas wrote: > Support parallel aggregation. ...and there was much rejoicing! Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Interesting read on SCM upending software and hardware architecture
On Sun, Jan 17, 2016 at 11:13:33PM -0500, Bruce Momjian wrote: > On Thu, Jan 7, 2016 at 02:30:06PM -0600, Jim Nasby wrote: > > https://queue.acm.org/detail.cfm?id=2874238 discusses how modern > > Storage Class Memory (SCM), such as PCIe SSD and NVDIMMs are > > completely upending every assumption made about storage. To put > > this in perspective, you can now see storage latency that is > > practically on-par with things like lock acquisition[1]. > > How is this different from Fusion I/O devices, which have been > around for years? Price. As these things come down in price, it'll start being more and more reasonable to treat rotating media as exotic. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] File based Incremental backup v8
On Tue, Mar 10, 2015 at 08:25:27AM +0900, Michael Paquier wrote: > On Tue, Mar 10, 2015 at 1:50 AM, Robert Haas wrote: > > I think there's absolutely no point in spending more time on this for > > 9.5. At least 4 committers have looked at it and none of them are > > convinced by the current design; feedback from almost half a year ago > > hasn't been incorporated; obviously-needed parts of the patch > > (pg_restorebackup) are missing weeks after the last CF deadline. > > Let's mark this Rejected in the CF app and move on. > > Agreed. I lost a bit interest in this patch lately, but if all the > necessary parts of the patch were not posted before the CF deadline > that's not something we should consider for integration at this point. > Let's give it a couple of months of fresh air and, Gabriele, I am sure > you will be able to come back with something far more advanced for the > first CF of 9.6. What's the latest on this patch? Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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 generate_series(date,date) and generate_series(date,date,integer)
On Tue, Jan 26, 2016 at 09:53:26PM +0900, Michael Paquier wrote: > On Tue, Jan 26, 2016 at 7:00 PM, Torsten Zuehlsdorff > wrote: > > > > On 26.01.2016 07:52, Simon Riggs wrote: > > > >>> Imagine for example a script that in some rare cases passes > >>> happens to pass infinity into generate_series() - in that case > >>> I'd much rather error out than wait till the end of the > >>> universe. > >>> > >>> So +1 from me to checking for infinity. > >>> > >> > >> +1 > >> > >> ERROR infinite result sets are not supported, yet > > > > > > Maybe we should skip the "yet". Or do we really plan to support > > them in (infinite) future? ;) > > > > +1 from me to check infinity also. > > Something like the patch attached would be fine? This wins a > backpatch because the query continuously running eats memory, no? +1 for back-patching. There's literally no case where an infinite input could be correct as the start or end of an interval for generate_series. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] New committer
On Thu, Jan 28, 2016 at 03:37:33PM +0100, Magnus Hagander wrote: > Hello! > > The PostgreSQL core team would like to welcome Dean Rasheed as a new > committer for the PostgreSQL project. > > Dean - welcome! Now let's see how quickly you can break the > buildfarm! Kudos! Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Template for commit messages
On Thu, Jan 28, 2016 at 03:52:25PM +0100, Tom Lane wrote: > Robert Haas writes: > > On Thu, Jan 28, 2016 at 8:04 AM, Tomas Vondra > > wrote: > >> Why can't we do both? That is, have a free-form text with the nuances, and > >> then Reviewed-By listing the main reviewers? The first one is for humans, > >> the other one for automated tools. > > > I'm not objecting to or endorsing any specific proposal, just asking > > what we want to do about this. I think the trick if we do it that way > > will be to avoid having it seem like too much duplication, but there > > may be a way to manage that. > > FWIW, I'm a bit suspicious of the idea that we need to make the commit > messages automated-tool-friendly. What tools are there that would need > to extract this info, and would we trust them if they didn't understand > "nuances"? > > I'm on board with Bruce's template as being a checklist of points to be > sure to cover when composing a commit message. I'm not sure we need > fixed-format rules. I've been asking for them for years so I can spend my time on the PostgreSQL Weekly News more efficiently. Maybe it's more efficient for me to do this arranging than for each committer to do it. I'd like to imagine that committers are in a better position than I to summarize their work. Whatever we decide on here, I'd really appreciate it if every patch sent to the list came with a sentence describing what that version of it does, as scope drift frequently makes Subject: lines completely wrong. While I'm at it, I'd like to thank Andres Freund, Peter Geoghegan, and Robert Haas in particular for making a habit of writing detailed summaries and splitting patches into logical chunks. All errors in the PostgreSQL Weekly News are mine, but a little organization like theirs would go a very long way, and not just for me. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Sanity checking for ./configure options?
On Wed, Feb 03, 2016 at 06:02:57PM -0600, Jim Nasby wrote:
> I just discovered that ./configure will happily accept '--with-pgport=' (I
> was actually doing =$PGPORT, and didn't realize $PGPORT was empty). What you
> end up with is a compile error in guc.c, with no idea why it's broken. Any
> reason not to have configure or at least make puke if pgport isn't valid?
That seems like a good idea.
I've been getting rejection to happen with phrases like
--with-pgport=${PGPORT:?}
which while it looks a little odd, only adds 4 characters to each
shell variable.
Cheers,
David.
--
David Fetter http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
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] Sanity checking for ./configure options?
On Tue, Feb 23, 2016 at 04:09:00PM -0600, Jim Nasby wrote:
> On 2/23/16 9:37 AM, Alvaro Herrera wrote:
> >Jim Nasby wrote:
> >>On 2/5/16 10:08 AM, David Fetter wrote:
> >>>On Wed, Feb 03, 2016 at 06:02:57PM -0600, Jim Nasby wrote:
> >>>>I just discovered that ./configure will happily accept '--with-pgport=' (I
> >>>>was actually doing =$PGPORT, and didn't realize $PGPORT was empty). What
> >>>>you
> >>>>end up with is a compile error in guc.c, with no idea why it's broken. Any
> >>>>reason not to have configure or at least make puke if pgport isn't valid?
> >>>
> >>>That seems like a good idea.
> >>
> >>Patch attached. I've verified it with --with-pgport=, =0, =7 and =1. It
> >>catches what you'd expect it to.
> >
> >Does it work to specify port numbers below 1024?
>
> Presumably not if you're trying to open a network port. But I just checked
> and if listen_addresses='' then you can use a low port number:
>
> select name,quote_nullable(setting) from pg_settings where name in
> ('port','listen_addresses');
>name | quote_nullable
> --+
> listen_addresses | ''
> port | '1'
> (2 rows)
>
> Plus, the GUC check allows 1-1024, so I'm inclined to do the same in the
> config check. But I don't have a strong opinion about it.
I'm thinking that both the GUC check and the configure one should
restrict it to [1024..65535].
Cheers,
David.
--
David Fetter http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
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] Sanity checking for ./configure options?
On Fri, Feb 26, 2016 at 04:55:23PM +0530, Robert Haas wrote: > On Wed, Feb 24, 2016 at 4:01 AM, David Fetter wrote: > > I'm thinking that both the GUC check and the configure one should > > restrict it to [1024..65535]. > > Doesn't sound like a good idea to me. If somebody has a reason they > want to do that, they shouldn't have to hack the source code and > recompile to make it work. I'm not sure I understand a use case here. On *n*x, we already disallow running as root pretty aggressively, using the "have to hack the source code and recompile" level of effort you aptly described. This is just cleanup work on that project, as I see it. What am I missing? Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Renaming some binaries
On Fri, Aug 26, 2016 at 01:26:39PM -0300, Euler Taveira wrote: > Hi, > > I'm bringing this $subject into discussion again. Historically, we are > carrying binary names that have been confused newbies. createuser is the > worst name so for. Also, names like createdb, initdb, reindexdb, and > droplang does not suggest what product it is referring to. Adding a > prefix (pg_, pg, ...) would 'make things clear'. +1 for pg_ . We should have done this long ago, but this is better fixed than left broken. > If we have a consensus > about this change, I suggest renaming the following binaries: > > clusterdb > createdb > createlang > createuser > dropdb > droplang > dropuser > initdb > oid2name > reindexdb > vacuumdb > vacuumlo > > Another major change related to this topic is assemble functionalities > from binaries. We currently have 34 binaries (is that a lot for a single > software?). Also, some of them have the same principle: execute a > administrative or maintenance command. IMHO, from the list above, we > could reduce it to: > > pg_command: clusterdb, createdb, dropdb, createuser, dropuser, > createlang, droplang, reindexdb, vacuumdb, vacuumlo. It also has the > advantage to allow adding new administrative/maintenance commands to it > in the future; Would these make sense as pg_ctl options, or are you separating them out because they're not instance-wide? If separating them is important on those grounds, how about something like pg_db or pg_db_command? > pg_oid2name: I don't have a strong opinion that it fits in pg_command; I vaguely knew that this existed, but I can't recall having heard of anybody actually using it. I suppose it's under pg_ctl if the split above between instance-wide and db-specific holds. > initdb: we already have 'pg_ctl init' (since 9.0) and could remove initdb. > > Opinions? +1 for removing initdb. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Renaming some binaries
On Fri, Aug 26, 2016 at 04:33:47PM -0300, Euler Taveira wrote: > On 26-08-2016 14:03, David Fetter wrote: > > Would these make sense as pg_ctl options, or are you separating them > > out because they're not instance-wide? If separating them is > > important on those grounds, how about something like pg_db or > > pg_db_command? > > > It doesn't make sense because pg_ctl is server-side and pg_command would > be client-side. Perfect! > >> pg_oid2name: I don't have a strong opinion that it fits in pg_command; > > > > I vaguely knew that this existed, but I can't recall having heard of > > anybody actually using it. I suppose it's under pg_ctl if the split > > above between instance-wide and db-specific holds. > > > I don't use it for a long time. It also a client-side binary then better > place for it is pg_command. BTW, is anybody using it? If so, we could > add this functionality to psql and remove it. Sure. For server versions 10 or better, it could be a call to a new server-side function. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Suggestions for first contribution?
On Mon, Sep 05, 2016 at 01:25:03PM -0400, Christian Convey wrote: > Hi guys, > > Can anyone suggest a project for my first PG contribution? How about adding PIVOT tables? MS SQL Server and Oracle both have them. If you're interested, I have some ideas about the UI parts and a few about the implementation. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] PoC: Make it possible to disallow WHERE-less UPDATE and DELETE
On Fri, Sep 09, 2016 at 09:57:21AM -0400, Peter Eisentraut wrote:
> Review of the patch in the commit fest:
>
> - Various naming/spelling inconsistencies: In the source, the module
> is require_where, the documentation titles it require-where, the GUC
> parameters are requires_where.*, but incorrectly documented.
Fixed.
> - Unusual indentation in the Makefile
Fixed.
> - Needs tests
Still needs some fixing.
> - Not sure about errcode(ERRCODE_CARDINALITY_VIOLATION), which is
> documented in the code as "this means something returned the wrong
> number of rows". I think ERRCODE_SYNTAX_ERROR or something from
> nearby there would be better.
Changed to ERRCODE_SYNTAX_ERROR. CARDINALITY_VIOLATION was a bit too
cute.
> - errhint() string should end with a period.
Fixed.
> - The 7th argument of DefineCustomBoolVariable() is of type int, not
> bool, so passing false is somewhat wrong, even if it works.
Fixed.
> - There ought to be a _PG_fini() function that undoes what _PG_init()
> does.
Fixed.
> - The documentation should be expanded and clarified. Given that this
> is a "training wheels" module, we can be extra clear here. I would
> like to see some examples at least.
Working on this.
> - The documentation is a bit incorrect about the ways to load this
> module. shared_preload_libraries is not necessary. session_ and
> local_ (with prep) should also work.
I'm not 100% sure I understand what you want here. I did manage to
get the thing loaded without a restart via LOAD, but that's it so far.
Will continue to poke at it.
> - The claim in the documentation that only superusers can do things
> with this module is not generally correct.
I think that the claims are fixed. This is SUSET, at least in this
patch, because anything short of that that changes query behavior
seems incautious.
Best,
David.
--
David Fetter http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
diff --git a/contrib/Makefile b/contrib/Makefile
index 25263c0..4bd456f 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -40,6 +40,7 @@ SUBDIRS = \
pgstattuple \
pg_visibility \
postgres_fdw\
+ require_where \
seg \
spi \
tablefunc \
diff --git a/contrib/require_where/Makefile b/contrib/require_where/Makefile
new file mode 100644
index 000..0cf3663
--- /dev/null
+++ b/contrib/require_where/Makefile
@@ -0,0 +1,17 @@
+# contrib/require_where/Makefile
+
+MODULE_big = require_where
+OBJS = require_where.o
+
+PGFILEDESC = 'require_where - require DELETE and/or UPDATE to have a WHERE
clause'
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS = $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/require_where
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_builddir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/require_where/require_where.c
b/contrib/require_where/require_where.c
new file mode 100644
index 000..181b3bb
--- /dev/null
+++ b/contrib/require_where/require_where.c
@@ -0,0 +1,92 @@
+/*
+ * --
+ *
+ * require_where.c
+ *
+ * Copyright (C) 2016, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * contrib/require_where/require_where.c
+ *
+ * --
+ */
+#include "postgres.h"
+
+#include "fmgr.h"
+
+#include "parser/analyze.h"
+
+#include "utils/elog.h"
+#include "utils/guc.h"
+
+PG_MODULE_MAGIC;
+
+void _PG_init(void);
+void _PG_fini(void);
+
+static post_parse_analyze_hook_type original_post_parse_analyze_hook =
NULL;
+static boolrequire_where_delete = false;
+static boolrequire_where_update = false;
+
+static void
+require_where_check(ParseState *pstate, Query *query)
+{
+
+ if (require_where_delete && query->commandType == CMD_DELETE)
+ {
+ Assert(query->jointree != NULL);
+ if (query->jointree->quals == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+errmsg("DELETE requires a WHERE
clause"),
+errhint("To delete all rows, use
\"WHERE true\" or similar.")));
+ }
+
+ if (require_where_update && query->commandType == CMD_UPDATE)
+ {
+ Assert(query->jointree !=
Re: [HACKERS] PoC: Make it possible to disallow WHERE-less UPDATE and DELETE
On Mon, Sep 19, 2016 at 03:00:51PM -0400, Peter Eisentraut wrote: > On 9/19/16 12:02 AM, David Fetter wrote: > >> - The claim in the documentation that only superusers can do things > >> > with this module is not generally correct. > > I think that the claims are fixed. This is SUSET, at least in this > > patch, because anything short of that that changes query behavior > > seems incautious. > > Your last patch, which I looked at, had them as USERSET. I think that > is the right setting. Will work one up this evening that has that. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] PoC: Make it possible to disallow WHERE-less UPDATE and DELETE
On Fri, Sep 09, 2016 at 09:57:21AM -0400, Peter Eisentraut wrote: > Review of the patch in the commit fest: > > - The documentation is a bit incorrect about the ways to load this > module. shared_preload_libraries is not necessary. session_ and > local_ (with prep) should also work. Would you be so kind as to describe how you got local_preload_libraries to work? I'm stuck on getting Makefile to realize that the hook should be installed in $libdir/plugins rather than $libdir itself. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] New SQL counter statistics view (pg_stat_sql)
On Wed, Sep 21, 2016 at 11:25:14AM -0400, Peter Eisentraut wrote: > On 9/14/16 4:01 PM, Robert Haas wrote: > > I think it is not a good idea to make the command names used here the > > plural forms of the command tags. Instead of "inserts", "updates", > > "imports", etc. just use "INSERT", "UPDATE", "IMPORT". That's simpler > > and less error prone - e.g. you won't end up with things like > > "refreshs", which is not a word. > > How about having the tag not be a column name but a row entry. So you'd > do something like > > SELECT * FROM pg_stat_sql WHERE tag = 'ALTER VIEW'; +1 for this. It's MUCH easier to deal with changes in row counts than changes in row type. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] New SQL counter statistics view (pg_stat_sql)
On Wed, Sep 21, 2016 at 02:05:24PM -0300, Alvaro Herrera wrote: > Another consideration is that the present patch lumps together all > ALTER cases in a single counter. This isn't great, but at the same > time we don't want to bloat the stat files by having hundreds of > counters per database, do we? I count 37 documented versions of ALTER as of git master. Is there some multiplier I'm missing? Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Cursor With_Hold Performance Workarounds/Optimization
On Thu, Oct 19, 2017 at 03:20:48PM +0200, Leon Winter wrote:
> Hi,
>
> I originally brought up this issue on the pgsql-performance mailing list [^]
> to
> no avail so I am trying again here.
>
> During implementation of a runtime environment and the adjoining database
> abstraction layer I noticed (like many before me [0] and as correctly
> mentioned
> in the documentation) the sizeable performance impact of declaring a cursor
> "with hold" for queries with large result sets.
>
> Our use case very often looks like this:
>
> open cursor for select from table1
> loop
> { fetch some entries from cursor
> update table2
> commit
> }
This seems like a very odd construct based on ideas about databases
that aren't actually true of PostgreSQL, e.g. that joins are
expensive, or that some substantial benefit comes of committing at
some higher frequency than the logical transaction.
What other things did you try, and how did they fail? In particular,
what happened when you used
UPDATE table2
SET [things based on table1]
FROM table1 [qualified] JOIN table2 ON ([conditions])
Best,
David.
--
David Fetter http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
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] Cursor With_Hold Performance Workarounds/Optimization
On Thu, Oct 19, 2017 at 04:06:47PM +0200, Leon Winter wrote:
> > What other things did you try, and how did they fail? In particular,
> > what happened when you used
> >
> > UPDATE table2
> > SET [things based on table1]
> > FROM table1 [qualified] JOIN table2 ON ([conditions])
>
> well, it is not the ideal way of doing things but then again this SQL is
> merely
> a consequence of the legacy 4GL language and runtime environment we are
> running
> (and trying to migrate to Postgres). We have a lot of those SQL structures and
> would prefer not to change all of them to avoid this situation. Currently
> there
> are also two database backends, one being the old legacy database and the
> other
> being Postgres and we are extremely limited from the (lacking) capabilities
> of
> the old database. We are surely planning to change many SQL statements to make
> better use of the database but not at this point for this issue.
>
> The loops are more complex in reality of course, more like:
>
> open cursor for select from table1
> loop
> { fetch some entries from cursor
> call some external application
> do some crazy complicated calculations based on some user input in the UI *
> update table2
> commit
> }
>
> The calculations inside the loop are written in some dynamic
> high-level language and cannot easily be translated into SQL.
>
> tl;dr: Existing code base has a lot of these patterns. General
> solution on database (interfacing) level is required.
I don't know quite how to put this, but it's not clear to me that the
difficulties in this situation are things PostgreSQL could resolve
even with much larger development resources than are currently
available.
If you're updating what are perforce small batches of records in the
UI, there's excellent reason to pull only those batches, mark them as
being "in process," process them, then update the marked ones as
"done" or whatever other states they can get to.
As to "crazy complicated calculations," this is what active databases
are all about. SQL is Turing complete, so you really can do it.
Would you want something that compiles from the user inputs to SQL?
Might that have a more general utility?
Best,
David.
--
David Fetter http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
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] Jsonb transform for pl/python
On Wed, Oct 25, 2017 at 02:51:00PM +0300, Anthony Bykov wrote:
> Hi.
> I've implemented jsonb transform
> (https://www.postgresql.org/docs/9.5/static/sql-createtransform.html)
> for pl/python.
>
> 1. '{"1":1}'::jsonb is transformed into dict {"1"=>1}, while
> '["1",2]'::jsonb is transformed into list(not tuple!) ["1", 2]
>
> 2. If there is a numeric value appear in jsonb, it will be transformed
> to decimal through string (Numeric->String->Decimal). Not the best
> solution, but as far as I understand this is usual practise in
> postgresql to serialize Numerics and de-serialize them.
>
> 3. Decimal is transformed into jsonb through string
> (Decimal->String->Numeric).
>
> An example may also be helpful to understand extension. So, as an
> example, function "test" transforms incoming jsonb into python,
> transforms it back into jsonb and returns it.
>
> create extension jsonb_plpython2u cascade;
Thanks for your hard work!
Should there also be one for PL/Python3U?
Best,
David.
--
David Fetter http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
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] Jsonb transform for pl/python
On Mon, Oct 30, 2017 at 11:15:00AM +0300, Anthony Bykov wrote: > On Sun, 29 Oct 2017 19:11:02 +0100 > David Fetter wrote: > > > Thanks for your hard work! > > > > Should there also be one for PL/Python3U? > > > > Best, > > David. > Hi. > Actually, there is one for PL/Python3U. This patch contains following > extensions: > jsonb_plpythonu > jsonb_plpython2u > jsonb_plpython3u > "make install" checks which python major version was your postgresql > configured with and installs corresponding extension. My mistake. Sorry about the noise. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Client Connection redirection support for PostgreSQL
On Thu, Nov 02, 2017 at 06:02:43AM +, Satyanarayana Narlapuram wrote: > Proposal: > Add the ability to the PostgreSQL server instance to route the > traffic to a different server instance based on the rules defined in > server's pg_bha.conf configuration file. At a high level this > enables offloading the user requests to a different server instance > based on the rules defined in the pg_hba.conf configuration file. > Some of the interesting scenarios this enables include but not > limited to - rerouting traffic based on the client hosts, users, > database, etc. specified, redirecting read-only query traffic to the > hot stand by replicas, and in multi-master scenarios. What advantages do you see in doing this in the backend over the current system where the concerns are separated, i.e. people use connection poolers like pgbouncer to do the routing? > The rules to route the traffic will be provided in the pg_hba.conf > file. The proposal is to add a new optional field 'RoutingList' to > the record format. The RoutingList contains comma-seperated list of > one or more servers that can be routed the traffic to. In the > absence of this new field there is no change to the current login > code path for both the server and the client. RoutingList can be > updated for each new connection to balance the load across multiple > server instances > RoutingList format: > server_address1:port, server_address2:port... Would it make sense also to include an optional routing algorithm or pointer to a routing function for each RoutingList, or do you see this as entirely the client's responsibility? > The message flow > > 1. Client connects to the server, and server accepts the connections How does this work with SSL? > 2. Client sends the startup message > 3. Server looks at the rules configured in the pg_hba.conf file and > * If the rule matches redirection >i. Send a special message with the RoutingList described above >ii. Server disconnects > > * If the rule doesn't have RoutingList defined > >i. Server proceeds in the existing code path and sends auth request > > 1. Client gets the list of addresses and attempts to connect to a > server in the list provided until the first successful connections > is established or the list is exhausted. If the client can't > connect to any server instance on the RoutingList, client reports > the login failure message. > > Backward compatibility: > There are a few ways to provide the backward compatibility, and each > approach has their own advantages and disadvantage and are listed > below > > 1. Bumping the protocol version - old server instances may not > understand the new client protocol This sounds more attractive, assuming that the feature is. > 2. Adding additional optional parameter routing_enabled, without > bumping the protocol version. In this approach, old Postgres > server instances may not understand this and fail the connections. Silently changing an API without bumping the protocol version seems like a bad plan, even when it's an additive change as you propose here. > 3. The current proposal - to keep it in the hba.conf and let the > server admin deal with the configuration by taking conscious > choice on the configuration of routing list based on the clients > connecting to the server instance. How would clients identify themselves as eligible without a protocol version bump? > Backward compatibility scenarios: > > * The feature is not usable for the existing clients, and the > new servers shouldn't set the routing list if they expect any > connections from the legacy clients. We should do either (1) or > (2) in the above list to achieve this. Otherwise need to rely on > the admin to take care of the settings. > * For the new client connecting to the old server, there is no > change in the message flow So to DoS the server, what's required is a flock of old clients? I presume there's a good reason to reroute rather than serve these requests. > * For the new clients to the new server, the message flow will be based > on the routing list filed in the configuration. > This proposal is in very early stage, comments and feedback is very much > appreciated. Comments and feedback have begun. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Skip unneeded temp file in 'make html'
Folks,
Please find attached a patch for $Subject.
Best,
David.
--
David Fetter http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Don't make an unneeded temp file
In passing, make a slight correction to the regex.
---
doc/src/sgml/Makefile | 8 +++-
1 file changed, 3 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/Makefile b/doc/src/sgml/Makefile
index 428eb569fc..f4ded1b3eb 100644
--- a/doc/src/sgml/Makefile
+++ b/doc/src/sgml/Makefile
@@ -147,14 +147,12 @@ INSTALL.xml: standalone-profile.xsl standalone-install.xml postgres.xml
# if we try to do "make all" in a VPATH build without the explicit
# $(srcdir) on the postgres.sgml dependency in this rule. GNU make bug?
postgres.xml: $(srcdir)/postgres.sgml $(ALLSGML)
- $(OSX) $(SPFLAGS) $(SGMLINCLUDE) -x lower $< >$@.tmp
- $(call mangle-xml,book)
+ $(OSX) $(SPFLAGS) $(SGMLINCLUDE) -x lower $< | $(call mangle-xml,book)
define mangle-xml
-$(PERL) -p -e 's/\[(aacute|acirc|aelig|agrave|amp|aring|atilde|auml|bull|copy|eacute|egrave|gt|iacute|lt|mdash|nbsp|ntilde|oacute|ocirc|oslash|ouml|pi|quot|scaron|uuml) *\]/\&\1;/gi;' \
+$(PERL) -p -e 's/\[(aacute|acirc|aelig|agrave|amp|aring|atilde|auml|bull|copy|eacute|egrave|gt|iacute|lt|mdash|nbsp|ntilde|oacute|ocirc|oslash|ouml|pi|quot|scaron|uuml) *\]/\&$$1;/gio;' \
-e '$$_ .= qq{http://www.oasis-open.org/docbook/xml/4.2/docbookx.dtd";>\n} if $$. == 1;' \
- <$@.tmp > $@
-rm $@.tmp
+ > $@
endef
--
2.13.6
--
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] Skip unneeded temp file in 'make html'
On Fri, Nov 03, 2017 at 11:34:18AM -0400, Peter Eisentraut wrote: > On 11/2/17 22:07, David Fetter wrote: > > postgres.xml: $(srcdir)/postgres.sgml $(ALLSGML) > > - $(OSX) $(SPFLAGS) $(SGMLINCLUDE) -x lower $< >$@.tmp > > - $(call mangle-xml,book) > > + $(OSX) $(SPFLAGS) $(SGMLINCLUDE) -x lower $< | $(call mangle-xml,book) > > The reason why it's not done that way is that this would not catch > errors of the command before the pipe. Thanks for clarifying. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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: psql tab completion for SELECT
On Mon, Nov 06, 2017 at 05:28:55PM +1300, Edmund Horner wrote:
> Hi pgsql-hackers,
>
> Here's a little draft patch to add *some* tab completion ability for
> SELECT in psql. I have often missed the ability, especially with
> invocations of utility functions.
>
> It would be nice to be able to suggest column names from the
> relevant tables in the query, but, as the SQL language puts the
> column list before the FROM clause, we have to use columns from all
> tables; I'm certainly not the first to be frustrated by this
> language feature, but we can't do much about it.
>
> What my patch does:
>
> For a command line with the single word SELECT, column names and
> function names will be used for completions of the next word.
> Function names have a "(" suffix added, largely because it makes
> them easier to distinguish in the completion list.
>
> Only the first select-list item can be tab-completed; i.e. SELECT
> foo, b won't find column bar.
That can be fixed later.
> Examples:
>
> postgres=# select rel
> relacl relchecksrelhasindex
> relhassubclass (etc.)
>
> postgres=# select str
> string_to_array( strip(strpos(
Neat!
Please add this to the upcoming (2018-01) commitfest at
https://commitfest.postgresql.org/
Best,
David.
--
David Fetter http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
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] PoC: Make it possible to disallow WHERE-less UPDATE and DELETE
On Thu, Sep 29, 2016 at 11:12:11AM +1300, Thomas Munro wrote: > On Mon, Sep 26, 2016 at 5:11 PM, Thomas Munro > wrote: > > On Mon, Sep 26, 2016 at 1:18 PM, Thomas Munro > > wrote: > >> > >> On Mon, Sep 19, 2016 at 4:02 PM, David Fetter wrote: > >> > > >> > [training_wheels_004.patch] > >> > >> [review] > > Ping. I'll have another revision out as soon as I get some more test cases. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] PoC: Make it possible to disallow WHERE-less UPDATE and DELETE
On Thu, Sep 29, 2016 at 11:12:11AM +1300, Thomas Munro wrote:
> On Mon, Sep 26, 2016 at 5:11 PM, Thomas Munro
> wrote:
> > On Mon, Sep 26, 2016 at 1:18 PM, Thomas Munro
> > wrote:
> >>
> >> On Mon, Sep 19, 2016 at 4:02 PM, David Fetter wrote:
> >> >
> >> > [training_wheels_004.patch]
> >>
> >> [review]
>
> Ping.
Please find attached the next revision.
Best,
David.
--
David Fetter http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
diff --git a/contrib/Makefile b/contrib/Makefile
index 25263c0..4bd456f 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -40,6 +40,7 @@ SUBDIRS = \
pgstattuple \
pg_visibility \
postgres_fdw\
+ require_where \
seg \
spi \
tablefunc \
diff --git a/contrib/require_where/Makefile b/contrib/require_where/Makefile
new file mode 100644
index 000..0cf3663
--- /dev/null
+++ b/contrib/require_where/Makefile
@@ -0,0 +1,17 @@
+# contrib/require_where/Makefile
+
+MODULE_big = require_where
+OBJS = require_where.o
+
+PGFILEDESC = 'require_where - require DELETE and/or UPDATE to have a WHERE
clause'
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS = $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/require_where
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_builddir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/require_where/data/test_require_where.data
b/contrib/require_where/data/test_require_where.data
new file mode 100644
index 000..d4a29d8
--- /dev/null
+++ b/contrib/require_where/data/test_require_where.data
@@ -0,0 +1,16 @@
+Four
+score
+and
+seven
+years
+ago
+our
+fathers
+brought
+forth
+on
+this
+continent
+a
+new
+nation
diff --git a/contrib/require_where/expected/require_where.out
b/contrib/require_where/expected/require_where.out
new file mode 100644
index 000..0876e13
--- /dev/null
+++ b/contrib/require_where/expected/require_where.out
@@ -0,0 +1,12 @@
+LOAD
+CREATE TABLE
+COPY 16
+UPDATE 16
+SET
+psql:sql/require_where.sql:17: ERROR: UPDATE requires a WHERE clause
+HINT: To update all rows, use "WHERE true" or similar.
+SET
+psql:sql/require_where.sql:21: ERROR: DELETE requires a WHERE clause
+HINT: To delete all rows, use "WHERE true" or similar.
+SET
+DELETE 16
diff --git a/contrib/require_where/require_where.c
b/contrib/require_where/require_where.c
new file mode 100644
index 000..27cbc25
--- /dev/null
+++ b/contrib/require_where/require_where.c
@@ -0,0 +1,92 @@
+/*
+ * --
+ *
+ * require_where.c
+ *
+ * Copyright (C) 2016, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * contrib/require_where/require_where.c
+ *
+ * --
+ */
+#include "postgres.h"
+
+#include "fmgr.h"
+
+#include "parser/analyze.h"
+
+#include "utils/elog.h"
+#include "utils/guc.h"
+
+PG_MODULE_MAGIC;
+
+void _PG_init(void);
+void _PG_fini(void);
+
+static post_parse_analyze_hook_type original_post_parse_analyze_hook =
NULL;
+static boolrequire_where_delete = false;
+static boolrequire_where_update = false;
+
+static void
+require_where_check(ParseState *pstate, Query *query)
+{
+
+ if (require_where_delete && query->commandType == CMD_DELETE)
+ {
+ Assert(query->jointree != NULL);
+ if (query->jointree->quals == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+errmsg("DELETE requires a WHERE clause
when require_where.delete is set to on"),
+errhint("To delete all rows, use
\"WHERE true\" or similar.")));
+ }
+
+ if (require_where_update && query->commandType == CMD_UPDATE)
+ {
+ Assert(query->jointree != NULL);
+ if (query->jointree->quals == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+errmsg("UPDATE requires a WHERE clause
when require_where.delete is set to on"),
+errhint("To update all rows, use
\"WHERE true\" or similar.")));
+ }
+
+ if (original_post_parse_analyze_hook != NULL)
+
Re: [HACKERS] PoC: Make it possible to disallow WHERE-less UPDATE and DELETE
On Fri, Sep 30, 2016 at 06:37:17PM +0200, Julien Rouhaud wrote: > On 30/09/2016 05:23, Thomas Munro wrote: > > > > It would be really nice to be able to set this to 'Ready for > > Committer' in this CF. Do you want to post a v6 patch or are you > > happy for me to ask a committer to look at v5 + these three > > corrections? > > I just looked at the patch, and noticed that only plain DELETE and > UPDATE commands are handled. Is it intended that writable CTE without > WHERE clauses are not detected by this extension? I personally think > that wCTE should be handled (everyone can forget a WHERE clause), but if > not it should at least be documented. You are correct in that it should work for every unqualified UPDATE or DELETE, not just some. Would you be so kind as to send along the tests cases you used so I can add them to the patch? Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Renaming of pg_xlog and pg_clog
On Thu, Oct 20, 2016 at 02:23:32PM -0400, Tom Lane wrote: > Robert Haas writes: > > On Thu, Oct 20, 2016 at 2:09 PM, Tom Lane wrote: > >> We have the two precedents "pg_subtrans" and "pg_multixact", so > >> unless we want to get into renaming those too, I think "pg_trans" > >> and "pg_xact" are really the only options worth considering. > >> > >> Personally I'd go for "pg_trans", but it's only a weak preference. > > > Heaven forfend we actually use enough characters to make it > > self-documenting. > > $ ls $PGDATA > PG_VERSION pg_dynshmem/ pg_notify/ pg_stat_tmp/ > postgresql.auto.conf > base/ pg_hba.confpg_replslot/ pg_subtrans/ postgresql.conf > global/pg_ident.conf pg_serial/ pg_tblspc/postmaster.opts > pg_clog/ pg_logical/pg_snapshots/ pg_twophase/ postmaster.pid > pg_commit_ts/ pg_multixact/ pg_stat/ pg_wal/ > > I don't see one single one of those subdirectory names that I'd call > self-documenting. That's a problem we should do something about, even if we can't do it by renaming these all in one go. At the very least, we can do this for any new names. > Are you proposing we rename them all with carpal- > tunnel-syndrome-promoting names? Are you saying that people are getting carpal tunnel syndrome from hitting the tab key, which has been standard for completion in shells for decades? I'm pretty sure that doesn't actually happen. > There's certainly some case to be made for renaming at least one of > "pg_subtrans" and "pg_multixact" so that these three similarly-purposed > subdirectories can all have similar names. But I think on the whole > that's (a) fixing what ain't broken, and (b) making it even more unlikely > that we'll ever get to consensus on changing anything. We've managed to > agree that we need to change the names ending in "log"; let's do that > and be happy that we've removed one foot-gun from the system. Removing foot guns, un-sexy as it may be from a developer's perspective, is very useful work. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Radix tree for character conversion
On Fri, Oct 28, 2016 at 09:18:08AM -0400, Robert Haas wrote: > On Thu, Oct 27, 2016 at 3:23 AM, Kyotaro HORIGUCHI > wrote: > > | COPYRIGHT AND PERMISSION NOTICE > > | > > | Copyright (c) 1991-2016 Unicode, Inc. All rights reserved. > > | Distributed under the Terms of Use in > > http://www.unicode.org/copyright.html. > > | > > | Permission is hereby granted, free of charge, to any person obtaining > > | a copy of the Unicode data files and any associated documentation > > | (the "Data Files") or Unicode software and any associated documentation > > | (the "Software") to deal in the Data Files or Software > > | without restriction, including without limitation the rights to use, > > | copy, modify, merge, publish, distribute, and/or sell copies of > > | the Data Files or Software, and to permit persons to whom the Data Files > > | or Software are furnished to do so, provided that either > > | (a) this copyright and permission notice appear with all copies > > | of the Data Files or Software, or > > | (b) this copyright and permission notice appear in associated > > | Documentation. > > > > Perhaps we can put the files into our repositoy by providing some > > notifications. > > Uggh, I don't much like advertising clauses. Your dislike is pretty common. Might it be worth reaching out to the Unicode consortium about this? They may well have added that as boilerplate without really considering the effects, and they even have a popup that specifically addresses licensing. http://www.unicode.org/reporting.html Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] tablesample with partitioned tables
On Wed, Feb 22, 2017 at 04:51:46PM +0900, Amit Langote wrote: > Attached patch fixes an oversight that tablesample cannot be used with > partitioned tables: > > create table p (a int) partition by list (a); > select * from p tablesample bernoulli (50); > ERROR: TABLESAMPLE clause can only be applied to tables and materialized > views Thanks! Should the error message change somehow to reflect that partitioned tables are included? Is complete transparency of partitioned tables the goal, and reasonable in this context? Also, is there a good reason apart from tuits not to expand TABLESAMPLE to the rest of our SQL-visible relation structures? I'm guessing this could have something to do with the volatility they might have, whether in views that call volatile functions or in foreign tables that might not make the right guarantees... Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Allow pg_dumpall to work without pg_authid
On Wed, Feb 22, 2017 at 06:33:10PM +1100, Robins Tharakan wrote: > Stephen, > > On 20 February 2017 at 08:50, Stephen Frost wrote: > > > The other changes to use pg_roles instead of pg_authid when rolpassword > > isn't being used look like they should just be changed to use pg_roles > > instead of using one or the other. That should be an independent patch > > from the one which adds the option we are discussing. > > > > Sure. Attached are 2 patches, of which 1 patch just replaces > pg_authid with pg_roles in pg_dumpall. The only exceptions > there are buildShSecLabels() & > pg_catalog.binary_upgrade_set_next_pg_authid_oid() which I thought > should still use pg_authid. Thanks for doing this! That pg_dumpall didn't work with RDS Postgres (and possibly others) was a pretty large wart. In future, could you please leave patches uncompressed so they're easier to see in the archives? Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Replication vs. float timestamps is a disaster
On Mon, Feb 20, 2017 at 11:58:12AM +0100, Petr Jelinek wrote: > On 20/02/17 08:03, Andres Freund wrote: > > On 2017-02-19 10:49:29 -0500, Tom Lane wrote: > >> Robert Haas writes: > >>> On Sun, Feb 19, 2017 at 3:31 AM, Tom Lane wrote: > >>>> Thoughts? Should we double down on trying to make this work according > >>>> to the "all integer timestamps" protocol specs, or cut our losses and > >>>> change the specs? > >> > >>> I vote for doubling down. It's bad enough that we have so many > >>> internal details that depend on this setting; letting that cascade > >>> into the wire protocol seems like it's just letting the chaos spread > >>> farther and wider. > >> > >> How do you figure that it's not embedded in the wire protocol already? > >> Not only the replicated data for a timestamp column, but also the > >> client-visible binary I/O format, depend on this. I think having some > >> parts of the protocol use a different timestamp format than other parts > >> is simply weird, and as this exercise has shown, it's bug-prone as all > >> get out. > > > > I don't think it's that closely tied together atm. Things like > > pg_basebackup, pg_receivexlog etc should work, without having to match > > timestamp storage. Logical replication, unless your output plugin dumps > > data in binary / "raw" output, also works just fine across the timestamp > > divide. > > > > It doesn't sound that hard to add a SystemToIntTimestamp() function, > > given it only needs to do something if float timestamps are enabled? > > > > It's definitely not hard, we already have > IntegerTimestampToTimestampTz() which does the opposite conversion anyway. > > That being said, I did wonder myself if we should just deprecate float > timestamps as well. +1 for deprecating them. If we need a timestamp(tz) with a wider range, we are getting options we didn't have before for implementing it. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] bytea_output output of base64
On Thu, Feb 23, 2017 at 05:55:37PM -0500, Bruce Momjian wrote: > On Thu, Feb 23, 2017 at 04:08:58PM -0500, Tom Lane wrote: > > Bruce Momjian writes: > > > Is there a reason we don't support base64 as a bytea_output output > > > option, except that no one has implemented it? > > > > How about "we already have one too many bytea output formats"? > > I don't think forcing code to try to support still another one > > is a great thing ... especially not if it couldn't be reliably > > distinguished from the hex format. > > Is there a reason we chose hex over base64? Whether there was or not, there's not a compelling reason now to break people's software. When people want compression, methods a LOT more effective than base64 are common. Gzip, for example. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Make subquery alias optional in FROM clause
On Thu, Feb 23, 2017 at 01:27:29PM +, Greg Stark wrote: > On 22 February 2017 at 15:08, Tom Lane wrote: > > Indeed. When I wrote the comment you're referring to, quite a few years > > ago now, I thought that popular demand might force us to allow omitted > > aliases. But the demand never materialized. At this point it seems > > clear to me that there isn't really good reason to exceed the spec here. > > It just encourages people to write unportable SQL code. > > > Oh my. This bothers me all the time. I always assumed the reason it > was like this was because the grammar would be ambiguous without it > and it would require extreme measures to hack the grammar to work. If > it's this easy I would totally be for it. > > Offhand I think there are plenty of solutions for the problem of > inventing names and I suspect any of them would work fine: > > 1) Don't assign a name -- I would guess this would require some > adjustments in the rule deparsing (i.e. views). > > 2) Assign a name but add a flag indicating the name is autogenerated > and shouldn't be used for resolving references and shouldn't be > dumped. Then it shouldn't really matter if there's a conflict since > the name is only used for things like error messages, not resolving > references. > > 3) thumb through all the names in the query and pick one that doesn't > conflict. > > For what it's worth while it wouldn't be a *bad* thing to avoid > conflicts I think this is being held to an inconsistent standard here. > It's not like there aren't similar situations elsewhere in the > codebase where we just don't worry about this kind of thing: > > => SELECT "?column"? FROM (select 1+1 as "?column?", 1+1) AS x; > ERROR: 42703: column "?column" does not exist > LINE 2: SELECT "?column"? FROM (select 1+1 as "?column?", 1+1) AS x; >^ > HINT: Perhaps you meant to reference the column "x.?column?" or the > column "x.?column?". That's because you transposed the two characters after column in your target list: XX SELECT "?column"? FROM (select 1+1 as "?column?", 1+1) AS x; SELECT "?column?" FROM (select 1+1 as "?column?", 1+1) AS x; This is what you get when you do the second, which I'm assuming is what you meant to do: ERROR: column reference "?column?" is ambiguous LINE 1: SELECT "?column?" FROM (select 1+1 as "?column?", 1+1) AS x; Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Statement timeout behavior in extended queries
On Wed, Feb 22, 2017 at 11:50:44AM +0900, Tatsuo Ishii wrote: > Last year I have proposed an enhancement regarding behavior of the > statement timeout in extended queries. > > https://www.postgresql.org/message-id/20160528.220442.1489791680347556026.t-ishii%40sraoss.co.jp > > IMO the current behavior is counter intuitive and I would like to > change it toward PostgreSQL 10.0. > > For example, suppose that the timeout is set to 4 seconds and the > first query takes 2 seconds and the second query takes 3 seconds. Then > the statement timeout is triggered if a sync message is sent to > backend after the second query. > > Moreover, log_duration or log_min_duration_statement shows that each > query took 2 or 3 seconds of course, which is not very consistent with > the statement timeout IMO. > > Attached patch tries to change the behavior, by checking statement > timeout against each phase of an extended query. > > To test the patch, I have created a small tool called "pgproto", which > can issue arbitrary sequence of frontend/backend message, reading from a > text file. > > https://github.com/tatsuo-ishii/pgproto > (to build the program, you need C compiler and libpq) Does it seem reasonable to start making this into a regression test and/or fuzz test for the protocol itself? Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] PGSERVICEFILE as a connection string parameter
On Sun, Feb 26, 2017 at 10:03:40PM -0800, Andres Freund wrote: > Hi, > > On 2017-02-27 14:43:49 +0900, Michael Paquier wrote: > > I bumped into a case where it would have been rather useful to > > specify a service file path in a connection string with a service > > name. In my case, I have finished by setting up PGSERVICEFILE, but > > now like PGPASSFILE I think that being able to define the service > > file available as well as a connection parameter would be useful > > as well. > > > > I am not planning to work on that immediately (there is one day > > left for the last CF of PG10!), but I was wondering if people > > would be interested in something like that. > > Hm - I'm not sure that's a good idea. service files are a libpq > feature, but connection strings are a bit more universal than just > libpq... You bring up a salient point. What say we make pg_services a little more universal? I'm guessing that the Java port wouldn't be too complicated. It's already well defined. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] rename pg_log directory?
On Mon, Feb 27, 2017 at 09:05:16AM -0500, Peter Eisentraut wrote: > How about changing the default for log_directory from 'pg_log' to, say, > 'log'? +1 A lot of work has already gone into this release to clarify what things are PostgreSQL internals and which ones are not. This will help. Yes, monitoring tools will need to take this into account. The people this will help will be the majority of our users, namely people who have yet to adopt PostgreSQL[1]. Best, David. [1] The project could fail completely next week, making our past and current users could be the majority, but that would be very sad. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] IF (NOT) EXISTS in psql-completion
On Mon, Feb 27, 2017 at 11:53:17PM -0500, Stephen Frost wrote: > * Kyotaro HORIGUCHI (horiguchi.kyot...@lab.ntt.co.jp) wrote: > > I suppose it is for suggesting what kind of word should come > > there, or avoiding silence for a tab. Or for symmetry with other > > types of manipulation, like DROP. Another possibility is creating > > multiple objects with similar names, say CREATE TABLE employee_x1, > > CREATE TABLE employee_x2. Just trying to complete existing > > *schema* is one more another possible objective. > > I don't buy any of these arguments either. I *really* don't want us > going down some road where we try to make sure that hitting 'tab' > never fails... Wouldn't that just be a correct, grammar-aware implementation of tab completion? Why wouldn't you want that? Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] New Committer - Andrew Gierth
On Tue, Feb 28, 2017 at 01:22:22PM -0500, Stephen Frost wrote: > Greetings! > > The PostgreSQL committers would like to welcome Andrew Gierth as a > new committer for the PostgreSQL project. Congratulations! Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] ANALYZE command progress checker
On Wed, Mar 01, 2017 at 09:45:40AM -0500, Peter Eisentraut wrote: > On 2/28/17 04:24, vinayak wrote: > > The view provides the information of analyze command progress details as > > follows > > postgres=# \d pg_stat_progress_analyze > >View "pg_catalog.pg_stat_progress_analyze" > > Hmm, do we want a separate "progress" system view for every kind of > command? What if someone comes up with a progress checker for CREATE > INDEX, REINDEX, CLUSTER, etc.? Some kind of design for progress seems like a good plan. Some ideas: - System view(s) This has the advantage of being shown to work at least to a PoC by this patch, and is similar to extant system views like pg_stat_activity in the sense of capturing a moment in time. - NOTIFY Event-driven model as opposed to a polling one. This is attractive on efficiency grounds, less so on reliability ones. - Something added to the wire protocol More specialized, limits the information to the session where the command was issued - Other things not named here Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] ANALYZE command progress checker
On Wed, Mar 01, 2017 at 10:28:23AM -0800, Andres Freund wrote: > On 2017-03-01 10:25:49 -0800, Andres Freund wrote: > > We now have a framework for this [1] (currently used by vacuum, but > > extensible). The question is about presentation. I'm fairly sure that > > we shouldn't just add yet another framework, and I doubt that that's > > what's proposed by Peter. > > > > [1] > > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c16dc1aca5e > > Majority of that is actually in > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b6fb6471f6afaf649e52f38269fd8c5c60647669 If that's even vaguely usable, I'd say we should use it for this. I notice that that commit has no SGML component. Should it have one? Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Adding support for Default partition in partitioning
On Fri, Mar 03, 2017 at 08:10:52AM +0530, Robert Haas wrote: > On Wed, Mar 1, 2017 at 6:29 AM, Rahila Syed wrote: > > 3. Handling adding a new partition to a partitioned table > >with default partition. > >This will require moving tuples from existing default partition to > > newly created partition if they satisfy its partition bound. > > Considering that this patch was submitted at the last minute and isn't > even complete, I can't see this getting into v10. But that doesn't > mean we can't talk about it. I'm curious to hear other opinions on > whether we should have this feature. On the point mentioned above, I > don't think adding a partition should move tuples, necessarily; seems > like it would be good enough - maybe better - for it to fail if there > are any that would need to be moved. I see this as a bug fix. The current state of declarative partitions is such that you need way too much foresight in order to use them. Missed adding a partition? Writes fail and can't be made to succeed. This is not a failure mode we should be forcing on people, especially as it's a massive regression from the extant inheritance-based partitioning. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Bug: timestamp without time zone column allows timestamptz default
Folks, What happens now: CREATE TABLE bar(id int DEFAULT 'a'); ERROR: invalid input syntax for integer: "a" Good so far. Now with a different data type. CREATE TABLE bar(t TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP); CREATE TABLE This is a foot gun we need not have. TIMESTAMP WITHOUT TIME ZONE is already a foot gun for the vast majority of real world cases. We should not be making it worse by papering this over. Ideally, this would look more like: CREATE TABLE bar(t TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP); ERROR: invalid input syntax for timestamp without time zone: current_timestamp HINT: perhaps you meant to use timestamp with time zone. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] multivariate statistics (v25)
On Tue, Mar 14, 2017 at 07:10:49PM -0300, Alvaro Herrera wrote: > Alvaro Herrera wrote: > > I tried patch 0002 today and again there are conflicts, so I rebased and > > fixed the merge problems. > > ... and attached the patch. Is the plan to convert completely from "multivariate" to "extended?" I ask because I found a "multivariate" in there. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] delta relations in AFTER triggers
On Sat, Jun 14, 2014 at 04:56:44PM -0700, Kevin Grittner wrote: > Attached is a WIP patch for implementing the capture of delta > relations for a DML statement, in the form of two tuplestores -- > one for the old versions and one for the new versions. Thanks! Any chance we might be able to surface the old version for the case of UPDATE ... RETURNING? Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] delta relations in AFTER triggers
On Tue, Jun 17, 2014 at 04:07:55PM -0400, Robert Haas wrote:
> On Sat, Jun 14, 2014 at 7:56 PM, Kevin Grittner wrote:
> > I looked at the standard, and initially tried to implement the
> > standard syntax for this; however, it appeared that the reasons
> > given for not using standard syntax for the row variables also
> > apply to the transition relations (the term used by the standard).
> > There isn't an obvious way to tie that in to all the PLs we
> > support. It could be done, but it seems like it would intolerably
> > ugly, and more fragile than what we have done so far.
>
> I'm not too familiar with this area. Can you describe what the
> standard syntax for the row variables is, as opposed to our syntax?
> Also, what's the standard syntax for the the transition relations?
The good:
- Generating the tuplestores. Yay!
The bad:
- Generating them exactly and only for AFTER triggers
- Requiring that the tuplestores both be generated or not at all.
There are real use cases described below where only one would be relevant.
- Generating the tuplestores unconditionally.
The ugly:
- Attaching tuplestore generation to tables rather than callers (triggers,
DML, etc.)
The SQL standard says:
::=
CREATE TRIGGER
ON [ REFERENCING ]
::=
BEFORE
| AFTER
| INSTEAD OF
::=
INSERT
| DELETE
| UPDATE [ OF ]
::=
::=
[ FOR EACH { ROW | STATEMENT } ]
[ ]
::=
WHEN
::=
| BEGIN ATOMIC { }... END
::=
...
::=
OLD [ ROW ] [ AS ]
| NEW [ ROW ] [ AS ]
| OLD TABLE [ AS ]
| NEW TABLE [ AS ]
::=
::=
::=
::=
::=
Sorry that was a little verbose, but what it does do is give us what we need at
trigger definition time. I'd say it's pilot error if a trigger
definition says "make these tuplestores" and the trigger body then
does nothing with them, which goes to Robert's point below re:
unconditional overhead.
> > Some things which I *did* follow from the standard: these new
> > relations are only allowed within AFTER triggers, but are available
> > in both AFTER STATEMENT and AFTER ROW triggers. That is, an AFTER
> > UPDATE ... FOR EACH ROW trigger could use both the OLD and NEW row
> > variables as well as the delta relations (under whatever names we
> > pick). That probably won't be used very often, but I can imagine
> > some cases where it might be useful. I expect that these will
> > normally be used in FOR EACH STATEMENT triggers.
>
> I'm concerned about the performance implications of capturing the
> delta relations unconditionally.
Along that same line, we don't always need to capture both the before
tuplestores and the after ones. Two examples of this come to mind:
- BEFORE STATEMENT triggers accessing rows, where there is no after part to
use, and
- DML (RETURNING BEFORE, e.g.) which only touches one of them. This
applies both to extant use cases of RETURNING and to planned ones.
I'm sure if I can think of two, there are more.
In summary, I'd like to propose that the tuplestores be generated
separately in general and attached to callers. We can optimize this by
not generating redundant tuplestores.
Cheers,
David.
--
David Fetter http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
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] delta relations in AFTER triggers
On Wed, Jun 18, 2014 at 03:30:34PM -0700, Kevin Grittner wrote: > David Fetter wrote: > > Robert Haas wrote: > >> Kevin Grittner wrote: > > > The good: > > - Generating the tuplestores. Yay! > > Thanks for that. ;-) Sorry, I just can't resist references to Spaghetti Westerns. https://en.wikipedia.org/wiki/The_Good,_the_Bad_and_the_Ugly > > The bad: > > - Generating them exactly and only for AFTER triggers > > The standard only allows them for AFTER triggers, and I'm not sure > what the semantics would be for any others. As, so here's where we differ. You're looking at deltas, a very nice capability to have. I'm looking at the before and after tuplestores as components of which deltas, among many other things, could be composed. > > - Requiring that the tuplestores both be generated or not at > > all. There are real use cases described below where only > > one would be relevant. > > Yeah. > > > - Generating the tuplestores unconditionally. > > Well, there are conditions. Only when the reloption allows and > only if there is an AFTER trigger for the type of operation in > progress. For deltas, this is just the thing. I'm vaguely picturing the following as infrastructure: - Instead of modifying Rel, we modify Query to contain two more bools default false: hasBeforeTuplestore and hasAfterTuplestore - Each use case we implement would set 0 or more of these to true. For the delta use case, appropriate trigger definitions would set both. This is vague because I haven't really gotten hacking on it, just exploring what I hope are the relevant parts of the code. > > The ugly: > > - Attaching tuplestore generation to tables rather than > callers (triggers, DML, etc.) > > I'm not sure what you're getting at here. This patch is > specifically only concerned with generating delta relations for DML > AFTER triggers, although my hope is that it will be a basis for > delta relations used for other purposes. This seems to me like the > right place to initially capture the data for incremental > maintenance of materialized views, and might be of value for other > purposes, too. Hrm. I don't really see this stuff as table properties. The materialized view case is an obvious example where the matview, not the relations underneath, wants this information. The relations underneath may have their own concerns, but it's the matview whose existence should ensure that the tuplestores are being generated. Once the last depending-on-one-of-the-tuplestores things is gone, and this could simply be the end of a RETURNING query, the tuplestores go away. > > [formal definition of standard CREATE TRIGGER statement] > > > Sorry that was a little verbose, but what it does do is give us > > what we need at trigger definition time. I'd say it's pilot > > error if a trigger definition says "make these tuplestores" and > > the trigger body then does nothing with them, which goes to > > Robert's point below re: unconditional overhead. > > Yeah, the more I think about it (and discuss it) the more I'm > inclined to suffer the additional complexity of the standard syntax > for specifying transition relations in order to avoid unnecessary > overhead creating them when not needed. I'm also leaning toward > just storing TIDs in the tuplestores, even though it requires mixed > snapshots in executing queries in the triggers. So in this case one tuplestore with two TIDs, either of which might be NULL? > just seems like there will otherwise be to much overhead in copying > around big, unreferenced columns for some situations. Yeah, it'd be nice to have the minimal part be as slim as possible. > > Along that same line, we don't always need to capture both the > > before tuplestores and the after ones. Two examples of this come > > to mind: > > > > - BEFORE STATEMENT triggers accessing rows, where there is no > > after part to use, > > Are you talking about an UPDATE for which the AFTER trigger(s) only > reference the before transition table, and don't look at AFTER?If > so, using the standard syntax would cover that just fine. If not, > can you elaborate? Sorry I was unclear. I was looking at one of the many things having these tuplestores around could enable. As things stand now, there is no access of any kind to rows with any per-statement trigger, modulo user-space hacks like this one: http://people.planetpostgresql.org/dfetter/index.php?/archives/71-Querying-Rows-in-Statement-Triggers.html Having the "before" tuplestore available to a BEFORE STATEMENT trigger would make it possible to do
Re: [HACKERS] delta relations in AFTER triggers
On Sat, Jun 21, 2014 at 11:06:26AM -0700, Kevin Grittner wrote: > Kevin Grittner wrote: > > Kevin Grittner wrote: > > > >> I've already said that I now think we should use the standard > >> CREATE TRIGGER syntax to specify the transition tables, and that > >> if we do that we don't need the reloption that's in the patch. > >> If you ignore the 19 lines of new code to add that reloption, > >> absolutely 100% of the code changes in the patch so far are in > >> trigger.c and trigger.h. > > > > Although nobody has actually framed their feedback as a review, I > > feel that I have enough to work with to throw the patch into > > Waiting on Author status. Since I started with the assumption > > that I was going to be using standard syntax and got a ways into > > that before convincing myself it was a bad idea, I should have a > > new version of the patch working that way in a couple days. > > Here is v2. Thanks! I've taken the liberty of making an extension that uses this. Preliminary tests indicate a 10x performance improvement over the user-space hack I did that's similar in functionality. Please find attached the extension, etc., which I've published to https://github.com/davidfetter/postgresql_projects/tree/test_delta_v2 Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate diff --git a/contrib/statement_trigger_row/Makefile b/contrib/statement_trigger_row/Makefile new file mode 100644 index 000..e0cf006 --- /dev/null +++ b/contrib/statement_trigger_row/Makefile @@ -0,0 +1,17 @@ +# contrib/statement_trigger_row/Makefile + +MODULES = statement_trigger_row + +EXTENSION = statement_trigger_row +DATA = statement_trigger_row--1.0.sql + +ifdef USE_PGXS +PG_CONFIG = pg_config +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir = contrib/statement_trigger_row +top_builddir = ../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif diff --git a/contrib/statement_trigger_row/sql/easy_way.sql b/contrib/statement_trigger_row/sql/easy_way.sql new file mode 100644 index 000..019ae7f --- /dev/null +++ b/contrib/statement_trigger_row/sql/easy_way.sql @@ -0,0 +1,85 @@ +/* + * If these were surfaced to PL/pgsql, this is what it might look like. + */ + +BEGIN; + +CREATE TABLE a( +id SERIAL PRIMARY KEY, +i INT +); + +CREATE FUNCTION summarize_a_inserts() +RETURNS TRIGGER LANGUAGE plpgsql +AS $$ +DECLARE +the_sum BIGINT; +BEGIN +SELECT INTO the_sum sum(NEW.i) +FROM +new_a; +RAISE NOTICE 'Total change: %.', the_sum; +RETURN NULL; +END; +$$; + +CREATE FUNCTION summarize_a_updates() +RETURNS TRIGGER LANGUAGE plpgsql +AS $$ +DECLARE +the_sum BIGINT; +BEGIN +SELECT INTO the_sum sum(COALESCE(NEW.i,0) - COALESCE(OLD.i, 0)) +FROM +old_a +JOIN +new_a +ON(old_a.id = new_a.id); +RAISE NOTICE 'Total change: %.', the_sum; +RETURN NULL; +END; +$$; + +CREATE FUNCTION summarize_a_deletes() +RETURNS TRIGGER LANGUAGE plpgsql +AS $$ +DECLARE +the_sum BIGINT; +BEGIN +SELECT INTO the_sum -1 * sum(OLD.i) +FROM +old_a; +RAISE NOTICE 'Total change: %.', the_sum; +RETURN NULL; +END; +$$; + +CREATE TRIGGER statement_after_insert_a +AFTER INSERT ON a +REFERENCING +NEW TABLE AS new_a +FOR EACH STATEMENT +EXECUTE PROCEDURE summarize_a_inserts(); + +CREATE TRIGGER statement_after_update_a +AFTER UPDATE ON a +REFERENCING +OLD TABLE AS old_a +NEW TABLE AS new_a +FOR EACH STATEMENT +EXECUTE PROCEDURE summarize_a_updates(); + +CREATE TRIGGER statement_after_delete_a +AFTER DELETE ON a +REFERENCING +OLD TABLE AS old_a +FOR EACH STATEMENT +EXECUTE PROCEDURE summarize_a_deletes(); + +INSERT INTO a(i) +SELECT * FROM generate_series(1,1); + +UPDATE a SET i=i+1; + +ROLLBACK; + diff --git a/contrib/statement_trigger_row/sql/hard_way.sql b/contrib/statement_trigger_row/sql/hard_way.sql new file mode 100644 index 000..c6f7c1d --- /dev/null +++ b/contrib/statement_trigger_row/sql/hard_way.sql @@ -0,0 +1,68 @@ +CREATE TABLE IF NOT EXISTS h( +i INTEGER +); + +CREATE FUNCTION set_up_h_rows() +RETURNS TRIGGER LANGUAGE plpgsql +AS $$ +BEGIN +CREATE TEMPORARY TABLE IF NOT EXISTS h_rows(LIKE a) ON COMMIT DROP; +RETURN NULL; +END; +$$; + +CREATE TRIGGER statement_before_writing_h +BEFORE INSERT OR UPDATE OR DELETE ON a +FOR EACH STATEMENT +EXECUTE PROCEDURE set_up_h_rows(); + +CREATE OR REPLACE FUNCTION stash_h_row_deltas()
Re: [HACKERS] [PATCH 0/3] Tau support
On Fri, Jun 27, 2014 at 06:03:33PM -0700, Tom Lane wrote: > =?UTF-8?q?Asbj=C3=B8rn=20Sloth=20T=C3=B8nnesen?= writes: > > Please see the following patches for implementing support for and > > migrating to Tau in PostgreSQL. > > While I don't particularly object to adding a tau() function, the > rest of this seems like change for the sake of change. What's the > point? And it had better be a darn good point, because > cross-version code differences are a constant source of maintenance > pain for us. We don't need ones that have no concrete value. It's Tau day (6.28) in some parts of the world already. Might that be the cause? Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] delta relations in AFTER triggers
On Sat, Jun 28, 2014 at 07:35:10AM -0700, Kevin Grittner wrote: > David Fetter wrote: > > On Sat, Jun 21, 2014 at 11:06:26AM -0700, Kevin Grittner wrote: > > >> Here is v2. > > > I've taken the liberty of making an extension that uses this. > > Preliminary tests indicate a 10x performance improvement over the > > user-space hack I did that's similar in functionality. > > Wow, this goes well beyond what I expected for a review! Thanks! It was the minimum I could come up with to test whether the patch worked. > As I said in an earlier post, I think that this is best committed > as a series of patches, one for the core portion and one for each > PL which implements the ability to use the transition (delta) > relations in AFTER triggers. Right. I'm still holding out hope of having the transition relations available in some more general way, but that seems more like a refactoring job than anything fundamental. > Your extension covers the C trigger angle, and it seems to me to be > worth committing to contrib as a sample of how to use this feature > in C. It's missing a few pieces like surfacing transition table names. I'll work on those. Also, it's not clear to me how to access the pre- and post- relations at the same time, this being necessary for many use cases. I guess I need to think more about how that would be done. > It is very encouraging that you were able to use this without > touching what I did in core, and that it runs 10x faster than the > alternatives before the patch. The alternative included was pretty inefficient, so there's that. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] delta relations in AFTER triggers
On Mon, Jun 30, 2014 at 11:03:06AM -0400, Robert Haas wrote: > On Sat, Jun 28, 2014 at 10:35 AM, Kevin Grittner wrote: > > David Fetter wrote: > >> On Sat, Jun 21, 2014 at 11:06:26AM -0700, Kevin Grittner wrote: > > > >>> Here is v2. > > > >> I've taken the liberty of making an extension that uses this. > >> Preliminary tests indicate a 10x performance improvement over the > >> user-space hack I did that's similar in functionality. > > > > Wow, this goes well beyond what I expected for a review! Thanks! > > > > As I said in an earlier post, I think that this is best committed > > as a series of patches, one for the core portion and one for each > > PL which implements the ability to use the transition (delta) > > relations in AFTER triggers. Your extension covers the C trigger > > angle, and it seems to me to be worth committing to contrib as a > > sample of how to use this feature in C. > > > > It is very encouraging that you were able to use this without > > touching what I did in core, and that it runs 10x faster than the > > alternatives before the patch. > > > > Because this review advances the patch so far, it may be feasible > > to get it committed in this CF. I'll see what is needed to get > > there and maybe have a patch toward that end in a few days. The > > minimum that would require, IMV, is a plpgsql implementation, > > moving the new pg_trigger columns to the variable portion of the > > record so they can be null capable, more docs, and regression > > tests. > > Not to rain on your parade, but this patch hasn't really had a serious > code review yet. Performance testing is good, but it's not the same > thing. Happy to help with that, too. What I wanted to start with is whether there was even rudimentary functionality, which I established by writing that extension. I happened to notice, basically as a sanity check, that doing this via tuplestores happened, at least in one case, to be quicker than doing it in user space with temp tables. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] LEFT JOINs not optimized away when not needed
On Tue, Jul 08, 2014 at 11:19:31AM -0400, Tom Lane wrote: > Moshe Jacobson writes: > > > Seeing that there is only one output column, and that the results are > > grouped by this output column, it seems to me that the optimizer should not > > even look at the rest of the tables. > > The GROUP BY has nothing to do with it, but if all the other tables' join > keys are primary keys (or at least unique), I'd expect the planner to get > rid of the joins. However, I'm not sure whether it works completely when > there are more than join_collapse_limit relations to worry about. Eliminating JOINs seems orthogonal, at least in theory, to join_collapse_limit. What have I missed here, and how might they have dependencies? Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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 autocomplete for CREATE DATABASE
On Thu, Jul 10, 2014 at 08:32:01PM +0100, Magnus Hagander wrote: > It seems psql is missing autocomplete entries for LC_COLLATE and > LC_CTYPE for the CREATE DATABASE command. Attached patch adds that. > > I doubt this is important enough to backpatch - thoughts? I don't see how it could hurt to fix this bug. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Behavior of "OFFSET -1"
On Tue, Jul 22, 2014 at 12:49:37PM -0400, Tom Lane wrote: > Before 9.3, you got an error from this: > > regression=# select * from tenk1 offset -1; > ERROR: OFFSET must not be negative That seems eminently sane, and should continue to error out, IM. The only circumstance I can imagine where this could be argued not to be is just casuistry, namely LIMIT m OFFSET -n might be argued to mean LIMIT m-n. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump bug in 9.4beta2 and HEAD
On Thu, Aug 14, 2014 at 10:03:57AM +0300, Heikki Linnakangas wrote: > On 08/14/2014 06:53 AM, Joachim Wieland wrote: > >I'm seeing an assertion failure with "pg_dump -c --if-exists" which is > >not ready to handle BLOBs it seems: > > > >pg_dump: pg_backup_archiver.c:472: RestoreArchive: Assertion `mark != > >((void *)0)' failed. > > > >To reproduce: > > > >$ createdb test > >$ pg_dump -c --if-exists test (works, dumps empty database) > >$ psql test -c "select lo_create(1);" > >$ pg_dump -c --if-exists test (fails, with the above mentioned assertion) > > The code tries to inject an "IF EXISTS" into the already-construct DROP > command, but it doesn't work for large objects, because the deletion command > looks like "SELECT pg_catalog.lo_unlink(xxx)". There is no DROP there. The lo_* functions are probably too entrenched to be deprecated, but maybe we could come up with DML (or DDL, although that seems like a bridge too far) equivalents and use those. Not for 9.4, obviously. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] delta relations in AFTER triggers
On Fri, Mar 31, 2017 at 12:20:51PM -0500, Kevin Grittner wrote: > On Thu, Mar 30, 2017 at 11:51 AM, Kevin Grittner wrote: > > > New version attached. It needs some of these problem cases added > > to the testing, and a mention in the docs that only C and plpgsql > > triggers can use the feature so far. I'll add those tomorrow. > > Done and attached. > > Now the question is, should it be pushed? Yes. Among other things, that'll get it buildfarm tested and give people interested in other PLs better visibility. That, and I suspect that people will start using this infrastructure for some very cool projects. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Quals not pushed down into lateral
On Thu, Apr 13, 2017 at 01:39:07PM -0700, Andres Freund wrote: > On 2017-04-13 16:34:12 -0400, Robert Haas wrote: > > On Thu, Mar 16, 2017 at 4:45 AM, Andres Freund wrote: > > > During citus development we noticed that restrictions aren't pushed down > > > into lateral subqueries, even if they semantically could. For example, > > > in this dumbed down example: > > > > > > postgres[31776][1]=# CREATE TABLE t_2(id serial primary key); > > > postgres[31776][1]=# CREATE TABLE t_1(id serial primary key); > > > > > > Comparing: > > > > > > postgres[31776][1]=# EXPLAIN SELECT * FROM t_1 JOIN (SELECT * FROM t_2 > > > GROUP BY id) s ON (t_1.id = s.id) WHERE t_1.id = 3; > > > postgres[31776][1]=# EXPLAIN SELECT * FROM t_1, LATERAL (SELECT * FROM > > > t_2 WHERE t_1.id = t_2.id GROUP BY id) s WHERE t_1.id = 3; > > > > Interesting. That does seem like we are missing a trick. > > Yea. > > > Not exactly related, but I think we need to improve optimization > > around CTEs, too. AFAICT, what we've got right now, almost everybody > > hates. > > That's certainly an issue, but it's a lot harder to resolve because > we've, for years, told people to intentionally use CTEs as optimization > barriers :( If we can get better performance by removing the barriers, we can certainly explain the new hack, assuming there is or needs to be one, in the release notes. We haven't promised to keep the current behavior forever, nor should we. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] ASOF join
On Fri, Jun 16, 2017 at 11:51:34AM +1200, Thomas Munro wrote: > On Fri, Jun 16, 2017 at 4:20 AM, Konstantin Knizhnik > wrote: > > I wonder if there were some discussion/attempts to add ASOF join to Postgres > > (sorry, may be there is better term for it, I am refereeing KDB definition: > > http://code.kx.com/wiki/Reference/aj ). > > Interesting idea. Also in Pandas: > > http://pandas.pydata.org/pandas-docs/version/0.19.0/generated/pandas.merge_asof.html#pandas.merge_asof > > I suppose you could write a function that pulls tuples out of a bunch > of cursors and zips them together like this, as a kind of hand-coded > special merge join "except that we match on nearest key rather than > equal keys" (as they put it). > > I've written code like this before in a trading context, where we > called that 'previous tick interpolation', and in a scientific context > where other kinds of interpolation were called for (so not really > matching a tuple but synthesising one if no exact match). If you view > the former case as a kind of degenerate case of interpolation then it > doesn't feel like a "join" as we know it, but clearly it is. I had > never considered before that such things might belong inside the > database as a kind of join operator. If you turn your head sideways, it's very similar to the range merge join Jeff Davis proposed. https://commitfest.postgresql.org/14/1106/ Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Code quality issues in ICU patch
On Fri, Jun 23, 2017 at 12:31:40PM -0400, Tom Lane wrote: > icu_to_uchar() and icu_from_uchar(), and perhaps other places, are > touchingly naive about integer overflow hazards in buffer size > calculations. I call particular attention to this bit in > icu_from_uchar(): > > len_result = UCNV_GET_MAX_BYTES_FOR_STRING(len_uchar, > ucnv_getMaxCharSize(icu_converter)); > > The ICU man pages say that that macro is defined as > > #define UCNV_GET_MAX_BYTES_FOR_STRING(length, maxCharSize) > (((int32_t)(length)+10)*(int32_t)(maxCharSize)) > > which means that getting this to overflow (resulting in > probably-exploitable memory overruns) would be about as hard as > taking candy from a baby. So it kicks off really loud and persistent alarms, and isn't as easy as you thought, even taking this into account? Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] \set AUTOROLLBACK ON
On Mon, Jun 26, 2017 at 04:00:55PM +0200, Joel Jacobson wrote: > Hi hackers, > > A colleague of mine wondered if there is a way to always run > everything you type into psql in a db txn and automatically rollback > it as soon as it finish. > I couldn't think of any way to do so, but thought it would be a nice > feature and probably quite easy to add to psql, so I thought I should > suggest it here. > > The typical use-case is you are doing something in production that you > just want to > a) test if some query works like expected and then rollback > or, > b) read-only queries that should not commit any changes anyway, so > here the rollback would just be an extra layer of security, since your > SELECT might call volatile functions that are actually not read-only > > Thoughts? Multi-statement transactions: Would flavor of BEGIN TRANSACTION undo the feature? If not, would it auto-munge COMMIT into a ROLLBACK? Side effects: Let's imagine you have a function called ddos_the_entire_internet(message TEXT), or something less drastic which nevertheless has side effects the DB can't control. How should this mode handle it? Should it try to detect calls to volatile functions, or should it just silently fail to do what it's promised to do? Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] \set AUTOROLLBACK ON
On Mon, Jun 26, 2017 at 12:35:47PM -0700, David G. Johnston wrote: > On Mon, Jun 26, 2017 at 12:19 PM, David Fetter wrote: > > > On Mon, Jun 26, 2017 at 04:00:55PM +0200, Joel Jacobson wrote: > > > Hi hackers, > > > > > > A colleague of mine wondered if there is a way to always run > > > everything you type into psql in a db txn and automatically rollback > > > it as soon as it finish. > > > I couldn't think of any way to do so, but thought it would be a nice > > > feature and probably quite easy to add to psql, so I thought I should > > > suggest it here. > > > > > > The typical use-case is you are doing something in production that you > > > just want to > > > a) test if some query works like expected and then rollback > > > or, > > > b) read-only queries that should not commit any changes anyway, so > > > here the rollback would just be an extra layer of security, since your > > > SELECT might call volatile functions that are actually not read-only > > > > > > Thoughts? > > > > Multi-statement transactions: > > > > Would flavor of BEGIN TRANSACTION undo the feature? > > If not, would it auto-munge COMMIT into a ROLLBACK? > > > > We already have SET TRANSACTION READ ONLY. Now there's an interesting and potentially fruitful idea. How about exposing GUCs to psql? That way, it'd be possible to put (some transformation of) them in the prompt, etc. > > Side effects: > > > > Let's imagine you have a function called > > ddos_the_entire_internet(message TEXT), or something less drastic > > which nevertheless has side effects the DB can't control. > > > > How should this mode handle it? Should it try to detect calls to > > volatile functions, or should it just silently fail to do what > > it's promised to do? > > > > It doesn't need to promise anything more than what happens today if > someone manually keys in > > BEGIN; > [...] > ROLLBACK; > > using psql prompts. Seems reasonable :) Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] New partitioning - some feedback
On Fri, Jul 07, 2017 at 10:29:26AM +0900, Amit Langote wrote: > Hi Mark, > > On 2017/07/07 9:02, Mark Kirkwood wrote: > > I've been trying out the new partitioning in version 10. Firstly, I must > > say this is excellent - so much nicer than the old inheritance based method! > > Thanks. :) > > > My only niggle is the display of partitioned tables via \d etc. e.g: > > > > part=# \d > > List of relations > > Schema | Name | Type | Owner > > +--+---+-- > > public | date_fact| table | postgres > > public | date_fact_201705 | table | postgres > > public | date_fact_201706 | table | postgres > > public | date_fact_20170601 | table | postgres > > public | date_fact_2017060100 | table | postgres > > public | date_fact_201707 | table | postgres > > public | date_fact_rest | table | postgres > > (7 rows) Would showing relispartition=tru tables only in \d+ fix this? Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] SCRAM auth and Pgpool-II
On Thu, Jul 06, 2017 at 10:03:37AM +0900, Tatsuo Ishii wrote: > Hi PostgreSQL hackers, > > I would like to hear ideas how Pgpool-II can deal with SCRAM auth > which will be in PostgreSQL 10. > > For those who are not familiar with Pgpool-II[1], it is an external > OSS project to provide some additional features to PostgreSQL, > including load balancing and automatic failover. Pgpool-II works as a > proxy between PostgreSQL client and PostgreSQL server(s). > > When a client wants to connects to PostgreSQL and SCRAM auth is > enabled, it sends user name to server. Then the server sends > information including a salt to the client. The client computes a > "ClientProof" using the salt and other information, and sends it to > the server[2]. > > For Pgpool-II, things would go as follows: > > 1) clients sends user name to Pgpool-II. > 2) Pgpool-II forwards it to PostgreSQL servers. > 3) Each PostgreSQL server sends their own salt to Pgpool-II. > 4) Pgpool-II is confused because there are multiple salts and each has >different values. The client only accepts single salt obviously. > > So my question is, is there any solution or workaround for the problem > #4. Someone at PGCon 2017 suggested that the problem could be avoided > if the auth method between the client and Pgpool-II is "trust" (which > means no auth). But this does not seem to be a best solution for me > because it would weaken the security. In the end, what poolers do is doing is indistinguishable, in security terms, from a man-in-the-middle attack. To the client, the thing with which they're negotiating auth and doing queries is Pgpool-II, in a manner similar to writing to a RAID volume rather than any individual disk in it. Are people actually running Pgpool on an untrusted network to the PostgreSQL nodes? Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] COPY vs. transition tables
Folks,
Using the script attached, I'm getting this very odd result set below.
Audit records from COPY to the "foo bar" table aren't getting
recorded, but audit records from COPY to the baz table are.
Best,
David.
\i copy_oddity.sql
CREATE TABLE
COMMENT
CREATE FUNCTION
CREATE FUNCTION
CREATE EVENT TRIGGER
COMMENT
psql:logging_infra.sql:165: NOTICE: Adding log table(s) for public.foo bar
CREATE TABLE
INSERT 0 1
UPDATE 1
DELETE 1
COPY 5
timestamp | user |action |
table_schema | table_name |old_row|new_row
---+-+---+--++---+---
2017-07-08 01:36:52.368228-07 | shackle | 2017-07-08 01:36:52.368228-07 |
public | foo bar| | {"t": null, "id": 1}
2017-07-08 01:36:52.368228-07 | shackle | 2017-07-08 01:36:52.368228-07 |
public | foo bar| {"t": null, "id": 1} | {"t": "baz", "id": 1}
2017-07-08 01:36:52.368228-07 | shackle | 2017-07-08 01:36:52.368228-07 |
public | foo bar| {"t": "baz", "id": 1} |
(3 rows)
psql:logging_infra.sql:180: NOTICE: Adding log table(s) for public.baz
psql:logging_infra.sql:180: NOTICE: relation "public_log" already exists,
skipping
CREATE TABLE
COPY 5
timestamp | user |action |
table_schema | table_name | old_row | new_row
---+-+---+--++-+
2017-07-08 01:36:52.368228-07 | shackle | 2017-07-08 01:36:52.368228-07 |
public | baz| | {"t": "a"}
2017-07-08 01:36:52.368228-07 | shackle | 2017-07-08 01:36:52.368228-07 |
public | baz| | {"t": "b"}
2017-07-08 01:36:52.368228-07 | shackle | 2017-07-08 01:36:52.368228-07 |
public | baz| | {"t": "c"}
2017-07-08 01:36:52.368228-07 | shackle | 2017-07-08 01:36:52.368228-07 |
public | baz| | {"t": "d"}
2017-07-08 01:36:52.368228-07 | shackle | 2017-07-08 01:36:52.368228-07 |
public | baz| | {"t": "e"}
(5 rows)
--
David Fetter http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
logging_infra.sql
Description: copy_oddity.sql
--
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] COPY vs. transition tables
On Sun, Jul 09, 2017 at 11:46:03AM +1200, Thomas Munro wrote: > On Sat, Jul 8, 2017 at 8:42 PM, David Fetter wrote: > > Using the script attached, I'm getting this very odd result set below. > > > > Audit records from COPY to the "foo bar" table aren't getting > > recorded, but audit records from COPY to the baz table are. > > Thanks for the bug report. I think it's the presence of the index on > "foo bar", not the space in its name (but thanks for that curve > ball!), that causes these tuples not to be captured. Thanks for getting this fixed, and apologies for not trimming this down to a minimal repro. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] New partitioning - some feedback
On Mon, Jul 10, 2017 at 04:15:28PM +0900, Amit Langote wrote:
> On 2017/07/10 15:32, Craig Ringer wrote:
> > On 8 July 2017 at 00:03, David Fetter wrote:
> >
> >> On Fri, Jul 07, 2017 at 10:29:26AM +0900, Amit Langote wrote:
> >>> Hi Mark,
> >>>
> >>> On 2017/07/07 9:02, Mark Kirkwood wrote:
> >>>> I've been trying out the new partitioning in version 10. Firstly, I
> >> must
> >>>> say this is excellent - so much nicer than the old inheritance based
> >> method!
> >>>
> >>> Thanks. :)
> >>>
> >>>> My only niggle is the display of partitioned tables via \d etc. e.g:
> >>>>
> >>>> part=# \d
> >>>> List of relations
> >>>> Schema | Name | Type | Owner
> >>>> +--+---+--
> >>>> public | date_fact| table | postgres
> >>>> public | date_fact_201705 | table | postgres
> >>>> public | date_fact_201706 | table | postgres
> >>>> public | date_fact_20170601 | table | postgres
> >>>> public | date_fact_2017060100 | table | postgres
> >>>> public | date_fact_201707 | table | postgres
> >>>> public | date_fact_rest | table | postgres
> >>>> (7 rows)
> >>
> >> Would showing relispartition=tru tables only in \d+ fix this?
> >> <http://www.postgresql.org/mailpref/pgsql-hackers>
> >>
> >
> > I think so.
>
> I posted a patch upthread which makes \d hide partitions (relispartition =
> true relations) and include them if the newly proposed '!' modifier is
> specified. The '+' modifier is being used to show additional detail of
> relations chosen to be listed at all, so it seemed like a bad idea to
> extend its meaning to also dictate whether partitions are to be listed.
> We have a separate 'S' modifier to ask to list system objects (which are,
> by default hidden), so it made sense to me to add yet another modifier
> (aforementioned '!') for partitions.
We have already made '+' signal "more detail, unspecified," for a lot
of different cases. If partitions are just "more detail" about a
table, which is the direction we've decided to go, it makes sense to
list them under the rubric of '+' rather than inventing yet another
hunk of syntax to psql's already confusing \ commands.
> > I'd like to add a flag of some kind to \d column output that marks a table
> > as having partitions, but I can't think of anything narrow enough and still
> > useful.
>
> Actually, if \d had shown RELKIND_PARTITIONED_TABLE tables as of Type
> "partitioned table", we wouldn't need a separate flag for marking a table
> as having partitions. But we've avoided using that term ("partitioned
> table") in the error messages and such, so wouldn't perhaps be a good idea
> to do that here. But I wonder if we (also) want to distinguish
> partitioned tables from regular tables? I understood that there is some
> desire for partitions be distinguished when they are listed in the output,
> either by default or by using a modifier.
+1 for showing that they're a different beast.
Best,
David.
--
David Fetter http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
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] New partitioning - some feedback
On Mon, Jul 10, 2017 at 05:33:34PM -0500, Robert Haas wrote: > On Mon, Jul 10, 2017 at 2:15 AM, Amit Langote > wrote: > > I posted a patch upthread which makes \d hide partitions > > (relispartition = true relations) and include them if the newly > > proposed '!' modifier is specified. The '+' modifier is being > > used to show additional detail of relations chosen to be listed at > > all, so it seemed like a bad idea to extend its meaning to also > > dictate whether partitions are to be listed. > > +1. That'd be a mess. With utmost respect, it's less messy than adding '!' to the already way too random and mysterious syntax of psql's \ commands. What should '\det!' mean? What about '\dT!'? > > Actually, if \d had shown RELKIND_PARTITIONED_TABLE tables as of > > Type "partitioned table", we wouldn't need a separate flag for > > marking a table as having partitions. > > I think that is false. Whether something is partitioned and whether > it is a partition are independent concerns. So whatever we land on needs to mention partition_of and has_partitions. Is that latter just its immediate partitions? Recursion all the way down? Somewhere in between? Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Arrays of domains
On Tue, Jul 11, 2017 at 12:44:33PM -0400, Tom Lane wrote: > Over in > https://www.postgresql.org/message-id/877ezgyn60@metapensiero.it > there's a gripe about array_agg() not working for a domain type. > It fails because we don't create an array type over a domain type, > so the parser can't identify a suitable output type for the polymorphic > aggregate. > > We could imagine tweaking the polymorphic-function resolution rules > so that a domain matched to ANYELEMENT is smashed to its base type, > allowing ANYARRAY to be resolved as the base type's array type. > While that would be a pretty localized fix, it seems like a kluge > to me. > > Probably a better answer is to start supporting arrays over domain > types. That was left unimplemented in the original domains patch, > but AFAICS not for any better reason than lack of round tuits. > I did find an argument here: > https://www.postgresql.org/message-id/3c98f7f6.29fe1...@redhat.com > that the SQL spec forbids domains over arrays, but that's the opposite > case (and a restriction we long since ignored, anyway). > > Can anyone think of a reason not to pursue that? +1 for pursuing it. When operations just compose, users get a more fun experience. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] idea: custom log_line_prefix components besides application_name
On Wed, Jul 12, 2017 at 07:38:56AM -0500, Robert Haas wrote: > On Tue, May 9, 2017 at 9:43 PM, Chapman Flack wrote: > > That's where the appident.cookie() function comes in. You just > > query it once at session establishment and remember the cookie. > > That allows your code to say: > > > > SET SESSION ON BEHALF OF 'joe user' BECAUSE I HAVE :cookie AND I SAY SO; > > > > and Mallory can't inject that because he doesn't have :cookie and the > > appident.cookie() function only succeeds the first time. > > I have for a long time been interested in having a protocol-level > method for setting the session identity. That is, instead of sending > "SET SESSION AUTHORIZATION 'rhaas'" wrapped in a Query message, you'd > send some new protocol message that we invent that nails down the > session authorization and doesn't allow it to be changed except by > another protocol message. I feel like the usefulness of this for > connection pooling software is pretty obvious: it's a lot easier for > the pooler to disallow a certain protocol message than a certain SQL > command. Neat idea. > But maybe we could generalize it a bit, so that it can work for any > GUC. For example, let the client send some new SetVariableViaProtocol > message with two arguments, a GUC name and a value. The server treats > this just like a SET command, except that once you've done this, the > variable can't later be changed from SQL. So now you don't even need > the cookie, and the client can't try to guess the cookie. You just > tell the server - via this protocol message - to nail down > session_authorization or application_name or appuser.thunk to some > value of your choice, and it's invulnerable to SQL injection > thereafter. Likely to SQL injection. I guess that would direct attackers to the protocol, which I suppose is already a harder target, assuming SSL or similar. > Whaddaya think? I thing I'm not alone in wanting some way to set parameters and hold them steady. I confess I'd been thinking more in terms of a DCL for these, but that makes it a lot less flexible than what you're proposing in the sense that it's set on connect rather than via GRANT and REVOKE. One thing I'm not seeing how to do via your proposal is to hold these things for local (not localhost) users. Is there some way to handle them, too, or would that be over-engineering this, given what a local user can already accomplish? Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] New partitioning - some feedback
On Fri, Jul 14, 2017 at 09:49:25PM -0500, Robert Haas wrote: > On Mon, Jul 10, 2017 at 5:46 PM, David Fetter wrote: > > With utmost respect, it's less messy than adding '!' to the already > > way too random and mysterious syntax of psql's \ commands. What > > should '\det!' mean? What about '\dT!'? > > Since \det lists foreign tables, \det! would list foreign tables even > if they are partitions. Plain \det would show only the ones that are > not partitions. > > \dT! wouldn't be meaningful, since \dT lists data types and data types > can't be partitions. If you're trying to conjure up a rule that every > \d command must accept the same set of modifiers, a quick > look at the output of \? and a little experimentation will quickly > show you that neither S nor + apply to all command types, so I see no > reason why that would need to be true for a new modifier either. > > TBH, I think we should just leave this well enough alone. We're > post-beta2 now, there's no clear consensus on what to do here, and > there will be very little opportunity for users to give us feedback if > we stick a change into an August beta3 before a September final > release. I think a new modifier would be too rushed at this stage, but there's no reason to throw out the progress on \d vs \dt. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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: Add --no-comments to skip COMMENTs with pg_dump
On Thu, Jun 01, 2017 at 10:05:09PM -0400, Tom Lane wrote: > Robert Haas writes: > > On Tue, May 30, 2017 at 8:55 PM, David G. Johnston > > wrote: > >>> Having --no-comments seems generally useful to me, in any case. > > >> It smacks of being excessive to me. > > > It sounds perfectly sensible to me. It's not exactly an elegant > > solution to the original problem, but it's a reasonable switch on > > its own merits. > > I dunno. What's the actual use-case, other than as a bad workaround > to a problem we should fix a different way? The one I run into frequently is in a proprietary fork, RDS Postgres. It'll happily dump out COMMENT ON EXTENSION plpgsq IS ... which is great as far as it goes, but errors out when you try to reload it. While bending over backwards to support proprietary forks strikes me as a terrible idea, I'd like to enable pg_dump to produce and consume ToCs just as pg_restore does with its -l/-L options. This would provide the finest possible grain. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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: Add --no-comments to skip COMMENTs with pg_dump
On Tue, Jul 18, 2017 at 08:38:25AM +0200, Michael Paquier wrote: > On Tue, Jul 18, 2017 at 3:45 AM, David Fetter wrote: > > The one I run into frequently is in a proprietary fork, RDS Postgres. > > It'll happily dump out COMMENT ON EXTENSION plpgsq IS ... > > which is great as far as it goes, but errors out when you try to > > reload it. > > > > While bending over backwards to support proprietary forks strikes me > > as a terrible idea, I'd like to enable pg_dump to produce and consume > > ToCs just as pg_restore does with its -l/-L options. This would > > provide the finest possible grain. > > Let's add as well a similar switch to pg_dumpall that gets pushed down > to all the created pg_dump commands. If this patch gets integrated > as-is this is going to be asked. And tests would be welcome. Excellent point about pg_dumpall. I'll see what I can draft up in the next day or two and report back. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] JSONB - JSONB operator feature request
On Tue, Jul 18, 2017 at 01:36:32PM +0200, david.tu...@linuxbox.cz wrote:
> Hi,
>
> some users and me used hstore - hstore for example storing only changed
> rows in trigger like:
>
> hstore(NEW) - hstore(OLD)
>
> There isn't same operator/function in JSON/JSONB. We can only remove keys
> from JSONB, but not equal key-value pairs. Is there any chance to have
> same feature with JSON/JSONB in postgres core?
What would - mean precisely for JSON[B]?
For example, what would you expect
SELECT '{"a": 1, "b": {"c": 2}}'::JSONB - '{"b": 1, "b": {"c": 3}}'::JSONB
to yield?
Best,
David.
--
David Fetter http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
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] JSONB - JSONB operator feature request
On Tue, Jul 18, 2017 at 01:36:32PM +0200, david.tu...@linuxbox.cz wrote:
> Hi,
>
> some users and me used hstore - hstore for example storing only changed
> rows in trigger like:
>
> hsore(NEW) - hstore(OLD)
>
> There isn't same operator/function in JSON/JSONB. We can only remove keys
> from JSONB, but not equal key-value pairs. Is there any chance to have
> same feature with JSON/JSONB in postgres core?
Here's one slightly modified from
http://coussej.github.io/2016/05/24/A-Minus-Operator-For-PostgreSQLs-JSONB/
CREATE OR REPLACE FUNCTION jsonb_minus ( arg1 jsonb, arg2 jsonb )
RETURNS jsonb
LANGUAGE sql
AS $$
SELECT
COALESCE(json_object_agg(
key,
CASE
-- if the value is an object and the value of the second argument is
-- not null, we do a recursion
WHEN jsonb_typeof(value) = 'object' AND arg2 -> key IS NOT NULL
THEN jsonb_minus(value, arg2 -> key)
-- for all the other types, we just return the value
ELSE value
END
), '{}')::jsonb
FROM
jsonb_each(arg1)
WHERE
arg1 -> key IS DISTINCT FROM arg2 -> key
$$;
CREATE OPERATOR - (
PROCEDURE = jsonb_minus,
LEFTARG = jsonb,
RIGHTARG = jsonb
);
I suspect that there's a faster way to do the jsonb_minus function
internally.
Best,
David.
--
David Fetter http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
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] Something for the TODO list: deprecating abstime and friends
On Wed, Jul 19, 2017 at 01:12:02PM -0400, Tom Lane wrote: > Robert Haas writes: > > On Mon, Jul 17, 2017 at 6:12 PM, Tom Lane wrote: > >> So, thinking about how that would actually work ... the thing to do in > >> order to preserve existing on-disk values is to alternate between signed > >> and unsigned interpretations of abstimes. That is, right now, abstime > >> is signed with origin 1970. The conversion I'm arguing we should make > >> real soon now is to unsigned with origin 1970. If the project lives > >> so long, in circa 70 years we'd switch it to signed with origin 2106. > >> Yadda yadda. > > > Doesn't this plan amount to breaking pg_upgrade compatibility and > > hoping that nobody notice? > > Well, what we'd need to do is document that the type is only meant to be > used to store dates within say +/- 30 years from current time. As long > as people adhere to that use-case, the proposal would work conveniently > long into the future ... > > > If we had two actually > > separate types and let people convert columns from the old type to the > > new type with just a validation scan, that would be engineering at the > > level of quality that I've come to associate with this project. > > ... whereas that would be seriously INconvenient. It's not just the > ALTER TABLE pushups, which presumably would be something you could do > and forget. It's that the new type name would be something you'd have > to change your applications to know about, and then you (or more likely > your successor) would have to do it over again decades later. > > I'd definitely be on board with just dropping the type altogether despite > Mark's concern. But I am not sure that the way you are proposing would > please anybody except pedants. +1 for just dropping the types, preferably modifying the contrib extensions that depend on it, less preferably, dropping those, too. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] JSONB - JSONB operator feature request
On Wed, Jul 19, 2017 at 06:17:35PM -0500, Merlin Moncure wrote:
> On Tue, Jul 18, 2017 at 12:49 PM, David Fetter wrote:
> > On Tue, Jul 18, 2017 at 01:36:32PM +0200, david.tu...@linuxbox.cz wrote:
> >> Hi,
> >>
> >> some users and me used hstore - hstore for example storing only changed
> >> rows in trigger like:
> >>
> >> hsore(NEW) - hstore(OLD)
> >>
> >> There isn't same operator/function in JSON/JSONB. We can only remove keys
> >> from JSONB, but not equal key-value pairs. Is there any chance to have
> >> same feature with JSON/JSONB in postgres core?
> >
> > Here's one slightly modified from
> > http://coussej.github.io/2016/05/24/A-Minus-Operator-For-PostgreSQLs-JSONB/
> >
> > CREATE OR REPLACE FUNCTION jsonb_minus ( arg1 jsonb, arg2 jsonb )
> > RETURNS jsonb
> > LANGUAGE sql
> > AS $$
> > SELECT
> > COALESCE(json_object_agg(
> > key,
> > CASE
> > -- if the value is an object and the value of the second
> > argument is
> > -- not null, we do a recursion
> > WHEN jsonb_typeof(value) = 'object' AND arg2 -> key IS NOT NULL
> > THEN jsonb_minus(value, arg2 -> key)
> > -- for all the other types, we just return the value
> > ELSE value
> > END
> > ), '{}')::jsonb
> > FROM
> > jsonb_each(arg1)
> > WHERE
> > arg1 -> key IS DISTINCT FROM arg2 -> key
> > $$;
> >
> > CREATE OPERATOR - (
> > PROCEDURE = jsonb_minus,
> > LEFTARG = jsonb,
> > RIGHTARG = jsonb
> > );
> >
> > I suspect that there's a faster way to do the jsonb_minus function
> > internally.
>
> yes, please! I also sorely miss the hstore 'slice' function which is
> very similar. The main remaining disadvantage with jsonb WRT to
> hstore is that you can't do simple retransformations that these
> operations allow for. Too often you end up doing multiple '->'
> operations against the same object followed by a rebundling which is a
> real performance killer.
If we can agree to a definition, we can make this go. My vague
memories from graph theory indicate that that "agree to a definition"
part is the real problem to be solved.
Best,
David.
--
David Fetter http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
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 Roman numeral conversion to to_number
On Thu, Aug 03, 2017 at 01:45:02PM -0400, Robert Haas wrote:
> On Thu, Aug 3, 2017 at 9:25 AM, Oliver Ford wrote:
> > Adds to the to_number() function the ability to convert Roman numerals
> > to a number. This feature is on the formatting.c TODO list. It is not
> > currently implemented in either Oracle, MSSQL or MySQL so gives
> > PostgreSQL an edge :-)
>
> I kind of put my head in my hands when I saw this. I'm not really
> sure it's worth complicating the code for something that has so little
> practical utility, but maybe other people will feel differently. I
> can't deny the profound advantages associated with having a leg up on
> Oracle.
>
> The error reporting is a little wonky, although maybe no wonkier than
> anything else about these conversion routines.
>
> rhaas=# select to_number('q', 'rn');
> ERROR: invalid character "q"
>
> (hmm, no position)
>
> rhaas=# select to_number('dd', 'rn');
> ERROR: invalid character "D" at position 1
>
> (now i get a position, but it's not really the right position; and the
> problem isn't really that the character is invalid but that you don't
> like me including it twice, and I said 'd' not 'D')
>
> rhaas=# select to_number('à', 'rn');
> ERROR: invalid character "?"
>
> (eh?)
>
> How much call is there for a format that can only represent values up to 3999?
There are ways to represent much larger numbers, possibly bigger than
INT_MAX. https://en.wikipedia.org/wiki/Roman_numerals#Large_numbers
https://en.wikipedia.org/wiki/Numerals_in_Unicode#Roman_numerals
As nearly as I can tell, this patch is late by 124 days.
Best,
David.
--
David Fetter http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
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] Re: [COMMITTERS] pgsql: Fix inadequacies in recently added wait events
On Wed, Aug 09, 2017 at 10:56:50AM -0400, Tom Lane wrote: > Robert Haas writes: > > On Wed, Aug 9, 2017 at 10:14 AM, Tom Lane wrote: > >> Thomas Munro writes: > >>> As for whether hypothetical check scripts would ever be run, I > >>> was thinking we should stick them under some make target that > >>> developers run all the time anyway -- perhaps "check". > >>> Shouldn't we catch simple mechanically detectable problems as > >>> early in the pipeline as possible? > > >> Adding overhead to every developer's every test cycle doesn't > >> sound like a win. > > > If it takes 100ms, nobody's gonna notice. > > I doubt running a perl script that analyzes the entire backend > source code is gonna take 100ms. What would be a reasonable maximum amount of time for such a check to take? Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] shared memory based stat collector (was: Sharing record typmods between backends)
On Sun, Aug 13, 2017 at 05:56:56PM -0700, Andres Freund wrote: > Hi, > > Since we're getting a bit into the weeds of a different topic, and since > I think it's an interesting feature, I'm detaching this into a separate > thread. > > On 2017-08-12 23:37:27 -0400, Tom Lane wrote: > > >> On 2017-08-12 22:52:57 -0400, Robert Haas wrote: > > >>> I think it'd be pretty interesting to look at replacing parts of the > > >>> stats collector machinery with something DHT-based. > > > On Sat, Aug 12, 2017 at 11:30 PM, Andres Freund > > > wrote: > > >> That seems to involve a lot more than this though, given that currently > > >> the stats collector data doesn't entirely have to be in memory. I've > > >> seen sites with a lot of databases with quite some per-database stats > > >> data. Don't think we can just require that to be in memory :( > > > > Robert Haas writes: > > > Hmm. I'm not sure it wouldn't end up being *less* memory. Don't we > > > end up caching 1 copy of it per backend, at least for the database to > > > which that backend is connected? Accessing a shared copy would avoid > > > that sort of thing. > > > > Yeah ... the collector itself has got all that in memory anyway. > > We do need to think about synchronization issues if we make that > > memory globally available, but I find it hard to see how that would > > lead to more memory consumption overall than what happens now. > > You both are obviously right. Another point of potential concern could > be that we'd pretyt much fully rely on dsm/dht's being available, for > the server to function correctly. Are we ok with that? Right now > postgres still works perfectly well, leaving parallelism aside, with > dynamic_shared_memory_type = none. > > > What are your thoughts about how to actually implement this? It seems > we'd have to do something like: > > 1) Keep the current per-backend & per-transaction state in each >backend. That allows both to throw away the information and avoids >increasing contention quite noticeably. > > 2) Some plain shared memory with metadata. A set of shared hashtables >for per database, per relation contents. > > 3) Individual database/relation entries are either individual atomics >(we don't rely on consistency anyway), or seqcount (like >st_changecount) based. > > 4) Instead of sending stats at transaction end, copy them into a >"pending" entry. Nontransactional contents can be moved to >the pending entry more frequently. > > 5) Occasionally, try to flush the pending array into the global hash. >The lookup in the table would be protected by something >LWLockConditionalAcquire() based, to avoid blocking - don't want to >introduce chokepoints due to commonly used tables and such. Updating >the actual stats can happen without the partition locks being held. > > I think there's two other relevant points here: > > a) It'd be quite useful to avoid needing a whole cluster's stats in >memory. Even if $subject would save memory, I'm hesitant committing >to something requiring all stats to be in memory forever. As a first >step it seems reasonable to e.g. not require state for all databases >to be in memory. The first time per-database stats are required, it >could be "paged in". Could even be more aggressive and do that on a >per-table level and just have smaller placeholder entries for >non-accessed tables, but that seems more work. > >On the other hand, autoavcuum is likely going to make that approach >useless anyway, given it's probably going to access otherwise unneded >stats regularly. > > b) I think our tendency to dump all stats whenever we crash isn't really >tenable, given how autovacuum etc are tied to them. We should think >about ways to avoid that if we're going to do a major rewrite of the >stats stuff, which this certainly sounds like. > > > If there weren't HS to worry about, these two points kinda sound like > the data should be persisted into an actual table, rather than some > weird other storage format. But HS seems to make that untenable. > > Greetings, > > Andres Freund As I recall, David Gould (hi!) had run across a case where there were thousand of tables and the stats file became a pretty serious bottleneck. There might even be a design or even code to address it. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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 support for tuple routing to foreign partitions
On Thu, Aug 17, 2017 at 05:27:05PM +0900, Etsuro Fujita wrote: > On 2017/07/11 6:56, Robert Haas wrote: > >On Thu, Jun 29, 2017 at 6:20 AM, Etsuro Fujita > > wrote: > >>So, I dropped the COPY part. > > > >Ouch. I think we should try to figure out how the COPY part will be > >handled before we commit to a design. > > I spent some time on this. To handle that, I'd like to propose doing > something similar to \copy (frontend copy): submit a COPY query "COPY ... > FROM STDIN" to the remote server and route data from a file to the remote > server. For that, I'd like to add new FDW APIs called during CopyFrom that > allow us to copy to foreign tables: > > * BeginForeignCopyIn: this would be called after creating a ResultRelInfo > for the target table (or each leaf partition of the target partitioned > table) if it's a foreign table, and perform any initialization needed before > the remote copy can start. In the postgres_fdw case, I think this function > would be a good place to send "COPY ... FROM STDIN" to the remote server. > > * ExecForeignCopyInOneRow: this would be called instead of heap_insert if > the target is a foreign table, and route the tuple read from the file by > NextCopyFrom to the remote server. In the postgres_fdw case, I think this > function would convert the tuple to text format for portability, and then > send the data to the remote server using PQputCopyData. > > * EndForeignCopyIn: this would be called at the bottom of CopyFrom, and > release resources such as connections to the remote server. In the > postgres_fdw case, this function would do PQputCopyEnd to terminate data > transfer. These primitives look good. I know it seems unlikely at first blush, but do we know of bulk load APIs for non-PostgreSQL data stores that this would be unable to serve? Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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 support for tuple routing to foreign partitions
On Fri, Aug 18, 2017 at 05:10:29PM +0900, Etsuro Fujita wrote: > On 2017/08/17 23:48, David Fetter wrote: > >On Thu, Aug 17, 2017 at 05:27:05PM +0900, Etsuro Fujita wrote: > >>On 2017/07/11 6:56, Robert Haas wrote: > >>>On Thu, Jun 29, 2017 at 6:20 AM, Etsuro Fujita > >>> wrote: > >>>>So, I dropped the COPY part. > >>> > >>>Ouch. I think we should try to figure out how the COPY part will be > >>>handled before we commit to a design. > >> > >>I spent some time on this. To handle that, I'd like to propose doing > >>something similar to \copy (frontend copy): submit a COPY query "COPY ... > >>FROM STDIN" to the remote server and route data from a file to the remote > >>server. For that, I'd like to add new FDW APIs called during CopyFrom that > >>allow us to copy to foreign tables: > >> > >>* BeginForeignCopyIn: this would be called after creating a ResultRelInfo > >>for the target table (or each leaf partition of the target partitioned > >>table) if it's a foreign table, and perform any initialization needed before > >>the remote copy can start. In the postgres_fdw case, I think this function > >>would be a good place to send "COPY ... FROM STDIN" to the remote server. > >> > >>* ExecForeignCopyInOneRow: this would be called instead of heap_insert if > >>the target is a foreign table, and route the tuple read from the file by > >>NextCopyFrom to the remote server. In the postgres_fdw case, I think this > >>function would convert the tuple to text format for portability, and then > >>send the data to the remote server using PQputCopyData. > >> > >>* EndForeignCopyIn: this would be called at the bottom of CopyFrom, and > >>release resources such as connections to the remote server. In the > >>postgres_fdw case, this function would do PQputCopyEnd to terminate data > >>transfer. > > > >These primitives look good. I know it seems unlikely at first > >blush, but do we know of bulk load APIs for non-PostgreSQL data > >stores that this would be unable to serve? > > Maybe I'm missing something, but I think these would allow the FDW > to do the remote copy the way it would like; in > ExecForeignCopyInOneRow, for example, the FDW could buffer tuples in > a buffer memory and transmit the buffered data to the remote server > if the data size exceeds a threshold. The naming is not so good? > Suggestions are welcome. The naming seems reasonable. I was trying to figure out whether this fits well enough with the bulk load APIs for databases other than PostgreSQL. I'm guessing it's "well enough" based on checking MySQL, Oracle, and MS SQL Server. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Cached plans and statement generalization
On Tue, Apr 25, 2017 at 06:11:09PM +0300, Konstantin Knizhnik wrote: > On 24.04.2017 21:43, Andres Freund wrote: > > Hi, > > > > On 2017-04-24 11:46:02 +0300, Konstantin Knizhnik wrote: > > > So what I am thinking now is implicit query caching. If the same query > > > with > > > different literal values is repeated many times, then we can try to > > > generalize this query and replace it with prepared query with > > > parameters. > > That's not actuall all that easy: > > - You pretty much do parse analysis to be able to do an accurate match. > >How much overhead is parse analysis vs. planning in your cases? > > - The invalidation infrastructure for this, if not tied to to fully > >parse-analyzed statements, is going to be hell. > > - Migrating to parameters can actually cause significant slowdowns, not > >nice if that happens implicitly. > > Well, first of all I want to share results I already get: pgbench with > default parameters, scale 10 and one connection: > > protocol > TPS > simple > 3492 > extended > 2927 > prepared > 6865 > simple + autoprepare > 6844 If this is string mashing on the unparsed query, as it appears to be, it's going to be a perennial source of security issues. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Cached plans and statement generalization
On Tue, Apr 25, 2017 at 11:35:21PM +0300, Konstantin Knizhnik wrote: > On 04/25/2017 07:54 PM, David Fetter wrote: > > On Tue, Apr 25, 2017 at 06:11:09PM +0300, Konstantin Knizhnik wrote: > > > On 24.04.2017 21:43, Andres Freund wrote: > > > > Hi, > > > > > > > > On 2017-04-24 11:46:02 +0300, Konstantin Knizhnik wrote: > > > > > So what I am thinking now is implicit query caching. If the same > > > > > query with > > > > > different literal values is repeated many times, then we can try to > > > > > generalize this query and replace it with prepared query with > > > > > parameters. > > > > That's not actuall all that easy: > > > > - You pretty much do parse analysis to be able to do an accurate match. > > > > How much overhead is parse analysis vs. planning in your cases? > > > > - The invalidation infrastructure for this, if not tied to to fully > > > > parse-analyzed statements, is going to be hell. > > > > - Migrating to parameters can actually cause significant slowdowns, not > > > > nice if that happens implicitly. > > > Well, first of all I want to share results I already get: pgbench with > > > default parameters, scale 10 and one connection: > > > > > > protocol > > > TPS > > > simple > > > 3492 > > > extended > > > 2927 > > > prepared > > > 6865 > > > simple + autoprepare > > > 6844 > > If this is string mashing on the unparsed query, as it appears to be, > > it's going to be a perennial source of security issues. > > Sorry, may be I missed something, but I can not understand how > security can be violated by extracting string literals from query. I > am just copying bytes from one buffer to another. I do not try to > somehow interpret this parameters. What I am doing is very similar > with standard prepared statements. And moreover query is parsed! > Only query which was already parsed and executed (but with different > values of parameters) can be autoprepared. I don't have an exploit yet. What concerns me is attackers' access to what is in essence the ability to poke at RULEs when they only have privileges to read. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Declarative partitioning - another take
On Thu, Apr 27, 2017 at 10:30:54AM +0900, Amit Langote wrote: > On 2017/04/27 1:52, Robert Haas wrote: > > On Tue, Apr 25, 2017 at 10:34 PM, Amit Langote > > wrote: > >> FWIW, I too prefer the latter, that is, fire only the parent's triggers. > >> In that case, applying only the patch 0001 will do. > > > > Do we need to update the documentation? > > Yes, I think we should. How about as in the attached? > > By the way, code changes I made in the attached are such that a subsequent > patch could implement firing statement-level triggers of all the tables in > a partition hierarchy, which it seems we don't want to do. Should then > the code be changed to not create ResultRelInfos of all the tables but > only the root table (the one mentioned in the command)? You will see that > the patch adds fields named es_nonleaf_result_relations and > es_num_nonleaf_result_relations, whereas just es_root_result_relation > would perhaps do, for example. Did I notice correctly that there's no way to handle transition tables for partitions in AFTER STATEMENT triggers? If not, I'm not suggesting that this be added at this late date, but we might want to document that. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Declarative partitioning - another take
On Fri, Apr 28, 2017 at 06:29:48PM +0900, Amit Langote wrote: > On 2017/04/28 7:36, David Fetter wrote: > > On Thu, Apr 27, 2017 at 10:30:54AM +0900, Amit Langote wrote: > >> On 2017/04/27 1:52, Robert Haas wrote: > >>> On Tue, Apr 25, 2017 at 10:34 PM, Amit Langote > >>> wrote: > >>>> FWIW, I too prefer the latter, that is, fire only the parent's triggers. > >>>> In that case, applying only the patch 0001 will do. > >>> > >>> Do we need to update the documentation? > >> > >> Yes, I think we should. How about as in the attached? > >> > >> By the way, code changes I made in the attached are such that a subsequent > >> patch could implement firing statement-level triggers of all the tables in > >> a partition hierarchy, which it seems we don't want to do. Should then > >> the code be changed to not create ResultRelInfos of all the tables but > >> only the root table (the one mentioned in the command)? You will see that > >> the patch adds fields named es_nonleaf_result_relations and > >> es_num_nonleaf_result_relations, whereas just es_root_result_relation > >> would perhaps do, for example. > > > > Did I notice correctly that there's no way to handle transition tables > > for partitions in AFTER STATEMENT triggers? > > Did you mean to ask about AFTER STATEMENT triggers defined on > "partitioned" tables? Specifying transition table for them is disallowed > at all. > > ERROR: "p" is a partitioned table > DETAIL: Triggers on partitioned tables cannot have transition tables. OK, I suppose. It wasn't clear from the documentation. > Triggers created on (leaf) partitions *do* allow specifying transition table. That includes the upcoming "default" tables, I presume. > Or are you asking something else altogether? I was just fuzzy on the interactions among these features. > > If not, I'm not suggesting that this be added at this late date, but > > we might want to document that. > > I don't see mentioned in the documentation that such triggers cannot be > defined on partitioned tables. Is that what you are saying should be > documented? Yes, but I bias toward documenting a lot, and this restriction could go away in some future version, which would make things more confusing in the long run. I'm picturing a conversation in 2020 that goes something like this: "On 10, you could have AFTER STATEMENT triggers on tables, foreigh tables, and leaf partition tables which referenced transition tables, but not on DEFAULT partitions. On 11, you could on DEFAULT partition tables. From 12 onward, you can have transition tables on any relation." Kevin? Thomas? Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] CTE inlining
On Sun, Apr 30, 2017 at 11:54:48PM +0200, Tomas Vondra wrote: > On 04/30/2017 06:28 AM, Tom Lane wrote: > > Craig Ringer writes: > > > - as you noted, it is hard to decide when it's worth inlining vs > > > materializing for CTE terms referenced more than once. > > > > [ raised eyebrow... ] Please explain why the answer isn't trivially > > "never". > > > > There's already a pretty large hill to climb here in the way of > > breaking peoples' expectations about CTEs being optimization > > fences. Breaking the documented semantics about CTEs being > > single-evaluation seems to me to be an absolute non-starter. > > > > I'm not sure that's a universal expectation, though. I know there > are people who actually do rely on that intentionally, no doubt > about that. And we'd nee to make it work for them. > > But I keep running into people who face serious performance issues > exactly because not realizing this, and using CTEs as named > subqueries. And when I tell them "optimization fence" they react > "Whaaat?" > > If I had to make up some numbers, I'd say the "What?" group is > about 10x the group of people who intentionally rely on CTEs being > optimization fences. I suspect you're off by at least a couple of orders of magnitude here, which make this even more important to deal with. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] CTE inlining
On Mon, May 01, 2017 at 09:22:42AM -0400, Andrew Dunstan wrote: > > So no more planner-affecting GUCs, please, particularly if we expect > > regular users to use them. > > +1 > > I still see users wanting to use the enable_foo settings in production. > > Having had years of telling users that CTEs are an optimization fence it > doesn't seem at all nice for us to turn around and change our mind about > that. I have relied on it in the past and I'm sure I'm very far from > alone in that. You are certainly not alone, but I believe that in this you're missing the vast majority (we hope) of PostgreSQL users. These are the users who have yet to adopt PostgreSQL, and have the quite reasonable expectation that ordinary-looking grammar *isn't* an optimization fence. > Maybe we could allow a "decorator" that would tell the planner the CTE > could be inlined? > > WITH INLINE mycte AS ( ...) +1 for a decorator, -1 for this one. We already have an explicit optimization fence with OFFSET 0, and I think making optimization fences explicit is how we should continue. I'd be more in favor of something along the lines of WITH FENCED/* Somewhat fuzzy. What fence? */ or WITH AT_MOST_ONCE /* Clearer, but not super precise */ or WITH UNIQUE_ATOMIC /* More descriptive, but not super clear without the docs in hand */ or something along that line. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] CTE inlining
On Tue, May 02, 2017 at 02:40:55PM +0200, Andreas Karlsson wrote: > On 05/02/2017 04:38 AM, Craig Ringer wrote: > > On 1 May 2017 at 22:26, Andreas Karlsson wrote: > > > I am not sure I like decorators since this means adding an ad hoc query > > > hint > > > directly into the SQL syntax which is something which I requires serious > > > consideration. > > > > And mangling the semantics of existing syntax doesn't? > > > > That's what we do right now so we can pretend we don't have query > > hints while still having query hints. > > I am in favor of removing the optimization fence from CTEs, and strongly > prefer no fence being the default behavior since SQL is a declarative > language and I think it is reasonable to assume that CTEs can be inlined. > But the question is how to best remove the fence while taking into account > that quite many use them as optimization fences today. > > I see some alternatives, none of them perfect. > > 1. Just remove the optimization fence and let people add OFFSET 0 to their > queries if they want an optimization fence. This lets us keep pretending > that we do not have query hints (and therefore do not have to formalize any > syntax for them) while still allowing people to add optimization fences. +1 I get that people with gigantic PostgreSQL installations with stringent performance requirements sometimes need to do odd things to squeeze out the last few percentage points of performance. As the people (well, at least the people close to the ground) at these organizations are fully aware, performance optimizations are extremely volatile with respect to new versions of software, whether it's PostgreSQL, Oracle, the Linux kernel, or what have you. They expect this, and they have processes in place to handle it. If they don't, it's pilot error. We should not be penalizing all our other users to maintain the fiction that people can treat performance optimizations as a "fire and forget" matter. > 2. Add a decorator for WITH (e.g. "WITH MATERIALIZED x (...") to add an > explicit optimization fence. This will for the first time add official > support for a query hint in the syntax which is a quite big precedent. Yep. It's one we should think very carefully before we introduce. > 3. Add a new GUC which can enable and disable the optimization fence. This > is a very clumsy tool, but maybe good enough for some users and some people > here in this thread have complained about our similar GUCs. Any GUC would be unable to distinguish one WITH clause from another. The hammer would then be guaranteed to be too big for precisely the cases where it's most needed. > 4. Add some new more generic query hinting facility. This is a lot > of work and something which would be very hard to get consensus for. Just the design of the thing would be the work of months at a minimum, assuming we got to some consensus at all. Maybe it's worth doing. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] CTE inlining
On Wed, May 03, 2017 at 11:26:27AM -0300, Claudio Freire wrote: > On Wed, May 3, 2017 at 2:19 AM, Craig Ringer wrote: > >> Or we will choose WITH MATERIALIZE, and then the users aware of > >> the fencing (and using the CTEs for that purpose) will have to > >> modify the queries. But does adding MATERIALIZE quality as major > >> query rewrite? > > > > Hardly. > > > >> Perhaps combining this with a GUC would be a solution. I mean, a > >> GUC specifying the default behavior, and then INLINE / > >> MATERIALIZE for individual CTEs in a query? > > > > It'd be nice if we could do that for a couple of releases as an > > interim measure, but people will then get locked into relying on > > it, and we'll never be able to remove it. > > The proposed guc seems like a good idea, without which ORMs that > support CTEs would be at a loss. Are you aware of such an ORM which both supports WITH and doesn't also closely track PostgreSQL development? I'm not. Even assuming that such a thing exists, it's not at all obvious to me that we should be stalling and/or putting in what will turn out to be misfeatures to accommodate it. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] transition table behavior with inheritance appears broken (was: Declarative partitioning - another take)
On Wed, May 03, 2017 at 11:47:04AM -0400, Robert Haas wrote: > On Tue, May 2, 2017 at 9:44 PM, Thomas Munro > wrote: > > I think that we should only capture transition tuples captured from > > the explicitly named relation, since we only fire AFTER STATEMENT > > triggers on that relation. I see no inconsistency with the policy of > > rejecting transition tables on partitioned tables (as I proposed and > > Kevin accepted[1]), because partitioned tables can't have any data so > > there would be no point. In contrast, every parent table in an > > inheritance hierarchy is also a regular table and can hold data, so I > > think we should allow transition tables on them, and capture > > transition tuples from that table only when you modify it directly. > > I suspect that most users would find it more useful to capture all of > the rows that the statement actually touched, regardless of whether > they hit the named table or an inheritance child. I just don't know > if it's practical to make that work. (And, of course, I don't know if > other people agree with my assessment of what is useful ... but > generally there seems to be support for making partitioned tables, at > least, look more like a single table that happens to have partitions > and less like a bunch of separate tables attached to each other with > duct tape.) +1 on the not-duct-tape view of partitioned tables. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] CTE inlining
On Wed, May 03, 2017 at 01:27:38PM -0300, Claudio Freire wrote: > On Wed, May 3, 2017 at 11:31 AM, David Fetter wrote: > > Are you aware of such an ORM which both supports WITH and doesn't > > also closely track PostgreSQL development? I'm not. > > > > Even assuming that such a thing exists, it's not at all obvious to > > me that we should be stalling and/or putting in what will turn out > > to be misfeatures to accommodate it. > > I know SQLAlchemy does support CTEs, and lags quite considerably in > its support of the latest syntactic elements. > > For instance, it took them 8 months to support the "skip locked" > option. That is pretty strictly their problem. > Not sure whether that qualifies as "closely tracking" postgres for > you. Clearly they do track it, but that doesn't mean they're fast or > as fast as one would like/need. We can NOT make their tardiness a driver of our development. > Sure, that might not be enough to warrant the GUC. I would think so, > those are my 2 cents. YMMV. When we add a "temporary" GUC, we're taking on a gigantic burden. Either we support it forever somehow, or we put it on a deprecation schedule immediately and expect to be answering questions about it for years after it's been removed. -1 for the GUC. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] renaming "transaction log"
On Wed, May 03, 2017 at 10:57:06AM -0300, Alvaro Herrera wrote: > Peter Eisentraut wrote: > > Most documentation and error messages still uses the term "transaction > > log" to refer to the write-ahead log. Here is a patch to rename that, > > which I think should be done, to match the xlog -> wal renaming in APIs. > > +1 for the idea. I suggest grepping for "transaction$" too -- there are > a few cases in SGML that have the phrase "transaction log" split across > lines. In general, you can use ag a.k.a. "The Silver Searcher" for phrase searches as follows: ag $(echo "the phrase to be searched" | sed -e 's/ /\\s/g') Thanks to Dagfinn Ilmari Mannsåker for the tip :) Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] renaming "transaction log"
On Wed, May 03, 2017 at 10:33:32AM -0700, David Fetter wrote: > On Wed, May 03, 2017 at 10:57:06AM -0300, Alvaro Herrera wrote: > > Peter Eisentraut wrote: > > > Most documentation and error messages still uses the term "transaction > > > log" to refer to the write-ahead log. Here is a patch to rename that, > > > which I think should be done, to match the xlog -> wal renaming in APIs. > > > > +1 for the idea. I suggest grepping for "transaction$" too -- there are > > a few cases in SGML that have the phrase "transaction log" split across > > lines. > > In general, you can use ag a.k.a. "The Silver Searcher" for phrase > searches as follows: > > ag $(echo "the phrase to be searched" | sed -e 's/ /\\s/g') Oops. That should read: ag $(echo "the phrase to be searched" | sed -e 's/ /\\s+/g') Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Google Summer Of Code 2017 & PostgreSQL
On Sat, May 06, 2017 at 07:54:51PM -0400, Stephen Frost wrote: > Greetings! > > I am very pleased to be able to announce that the PostgreSQL project has > been approved for 4 GSoC projects this year! Thanks for the hard work! Google's process has gotten a lot more rigorous over the years, and in under-appreciated consequence, people who would rather be doing other things on projects get to shoulder that burden. > The four projects are: > > - Add errors handling and parallel execution to COPY > > Student: Alexey Kondratov > Mentors: Anastasia Lubennikova, Alexander Korotkov, Alvaro Herrera > > - Eliminate O(N^2) scaling from rw-conflict tracking in serializable > transactions > > Student: Mengxing Liu > Mentors: Alvaro Herrera, Kevin Grittner > > - Explicitly support predicate locks in index AMs besides btree > > Student: Shubham Barai > Mentors: Andrey Borodin, Kevin Grittner > > - Foreign Keys for Array Elements > > Student: Mark Rofail > Mentors: Alexander Korotkov, Stephen Frost, Alvaro Herrera Good luck to all, and welcome to Alexey, Menxing, Shubham, and Mark! Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
