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 <david(at)fetter(dot)org> 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] 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 <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> 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] 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 <david(at)fetter(dot)org> 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 <da...@fetter.org> 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 <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> 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 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 <david(at)fetter(dot)org> 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] generated columns
On Mon, Oct 02, 2017 at 02:30:38PM -0400, Tom Lane wrote: > Nico Williams <n...@cryptonector.com> writes: > > On Mon, Oct 02, 2017 at 12:50:14PM -0400, Adam Brusselback wrote: > >> So for me, i'd rather default to compute on read, as long storing the > >> pre-computed value is an option when necessary. > > > Sure, I agree. I was just wondering whether there might be any other > > difference besides performance characteristics. The answer to that is, > > I think, "no". > > What about non-immutable functions in the generation expression? Assuming they're permitted, which...well, I could make a case, they should be mutually exclusive with the cached option. I guess documenting the behavior in the manual would suffice, tempting as it would be to include a NOTICE when the table goes from having 0 or more generated columns all of which are immutable to having at least one that's not. Best, David. -- David Fetter <david(at)fetter(dot)org> 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] alter server for foreign table
On Fri, Sep 29, 2017 at 01:47:59PM -0400, Tom Lane wrote: > Konstantin Knizhnik <k.knizh...@postgrespro.ru> writes: > > According to Postgresql documentation it is not possible to alter server > > for foreign table: > > https://www.postgresql.org/docs/10/static/sql-alterforeigntable.html > > Hmm, we'd have to check if the table's options were legal for the > new FDW, but in principle we could support this, I suppose. > Not sure if it's useful enough to be worth the trouble. It would definitely be useful if it were available. Nodes are a good bit more fungible than they were even 5 years back. Best, David. -- David Fetter <david(at)fetter(dot)org> 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] Multicolumn hash indexes
On Tue, Sep 26, 2017 at 11:32:52PM -0400, Tom Lane wrote: > Robert Haas <robertmh...@gmail.com> writes: > > On Tue, Sep 26, 2017 at 7:18 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > >> It's not simple, particularly not if you wish that the index would support > >> queries specifying conditions for just a subset of the indexed columns > >> (an assumption that's buried pretty deeply in the planner, for one thing). > >> Then you couldn't compute the hash. > > > Whoa, that seems like moving the goalposts. > > No, that's merely stating where the goalposts stand, and where they have > stood for the past twenty-odd years. You might imagine they are somewhere > else, but you'd be mistaken. > > There is a facility in the planner to require a condition for the first > column of an index before considering an indexscan plan. We could perhaps > extend that to require a condition for each column of the index, though > I'm not sure how much work is involved directly in that. The bigger > picture here though is that it puts a premium on *not* throwing away > "unnecessary" qual conditions, which is directly antithetical to a bunch > of other planner goals. > > User: Why won't the planner use my multicolumn hash index? > I have query conditions constraining all the columns! > Us: Well, one of your conditions was discarded because it was > constant-true after constant simplification, or redundant with > a partition qual or CHECK constraint, or implied by an index > predicate, or treated as a join condition instead of a > restriction condition, or absorbed into an equivalence class > and then the planner chose to emit some other equivalence > condition instead, or possibly two or three other things. > User: WH! > > There are also some related problems concerning how to make index > entries for tuples that have some but not all of the indexed columns > being NULL. Maybe that goes away if you're willing to demand strict > equality constraints for all columns, but I'm not completely > convinced offhand. (See the amoptionalkey discussion in the index > AM API spec for some context.) > > I agree that doing a half-baked job of this is probably within > reach. I'm uncertain about what it would take to bake it fully. To stretch this analogy too far, what other things could be built out of the bread this bakes? I'm guessing that at least non-hash multicolumn indexes would benefit. Expressional indexes, maybe? Best, David. -- David Fetter <david(at)fetter(dot)org> 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] Overestimated filter cost and its mitigation
On Mon, Sep 11, 2017 at 04:43:46PM +0900, Yuto Hayamizu wrote: > Hi hackers, > > Currently, cost of a filter with multiple clauses is estimated by > summing up estimated cost of each clause. As long as a filter > consists of simple clauses and its cost is fairly small, it works > fine. However, when there exists some heavy clauses (like SubQuery or > user-defined functions) and most of tuples are filtered by other > simple clauses, total cost is likely to be overestimated. An attached > patch mitigates this overestimation by using selectivity estimation of > subsets of clauses in a filter. I've taken the liberty of adding this to the upcoming commitfest. Best, David. -- David Fetter <david(at)fetter(dot)org> 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 Sat, Sep 16, 2017 at 10:42:49PM +, Douglas Doole wrote: > Oliver, I took a look at your tests and they look thorough to me. > > One recommendation, instead of having 3999 separate selects to test every > legal roman numeral, why not just do something like this: > > do $$ > declare > i int; > rn text; > rn_val int; > begin > for i in 1..3999 loop > rn := trim(to_char(i, 'rn')); > rn_val := to_number(rn, 'rn'); > if (i <> rn_val) then > raise notice 'Mismatch: i=% rn=% rn_val=%', i, rn, rn_val; > end if; > end loop; > raise notice 'Tested roman numerals 1..3999'; > end; > $$; > > It's a lot easier to maintain than separate selects. Why not just one SELECT, as in: SELECT i, to_char(i, 'rn'), to_number(to_char(i, 'rn'), 'rn'); FROM generate_series(1,3999) i Best, David. -- David Fetter <david(at)fetter(dot)org> 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] hash partitioning
On Mon, Sep 11, 2017 at 07:43:29AM -0400, Robert Haas wrote: > On Mon, Sep 11, 2017 at 4:17 AM, Ashutosh Bapat > <ashutosh.ba...@enterprisedb.com> wrote: > >> Rebased 0002 against this commit & renamed to 0001, PFA. > > > > Given that we have default partition support now, I am wondering > > whether hash partitioned tables also should have default > > partitions. The way we have structured hash partitioning syntax, > > there can be "holes" in partitions. Default partition would help > > plug those holes. > > Yeah, I was thinking about that, too. On the one hand, it seems > like it's solving the problem the wrong way: if you've set up hash > partitioning properly, you shouldn't have any holes. Should we be pointing the gun away from people's feet by making hash partitions that cover the space automagically when the partitioning scheme[1] is specified? In other words, do we have a good reason to have only some of the hash partitions so defined by default? Best, David. [1] For now, that's just the modulus, but the PoC included specifying hashing functions, so I assume other ways to specify the partitioning scheme could eventually be proposed. -- David Fetter <david(at)fetter(dot)org> 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] generated columns
On Wed, Sep 13, 2017 at 10:09:37AM +0200, Andreas Karlsson wrote: > On 09/13/2017 04:04 AM, Simon Riggs wrote: > >On 31 August 2017 at 05:16, Peter Eisentraut > ><peter.eisentr...@2ndquadrant.com> wrote: > >>- index support (and related constraint support) > > > >Presumably you can't index a VIRTUAL column. Or at least I don't > >think its worth spending time trying to make it work. > > I think end users would be surprised if one can index STORED columns > and expressions but not VIRTUAL columns. So unless it is a huge > project I would say it is worth it. So long as the expression on the normal columns was immutable, it's fit for an expressional index, as is any immutable function composed with it. What am I missing? Best, David. -- David Fetter <david(at)fetter(dot)org> 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] CSV Logging questions
On Mon, Sep 04, 2017 at 05:27:40PM +0100, Greg Stark wrote: > I was just looking over the CSV logging code and have a few questions > about why things were done the way they were done. > > 1) Why do we gather a per-session log line number? Is it just to aid > people importing to avoid duplicate entries from partial files? Is > there some other purpose given that entries will already be sequential > in the csv file? > > 2) Why is the file error conditional on log_error_verbosity? Surely > the whole point of a structured log is that you can log everything and > choose what to display later -- i.e. why csv logging doesn't look at > log_line_prefix to determine which other bits to display. There's no > added cost to include this information unconditionally and they're far > from the largest piece of data being logged either. > > 3) Similarly I wonder if the statement should always be included even > with hide_stmt is set so that users can write sensible queries against > the data even if it means duplicating data. > > 4) Why the session start time? Is this just so that <process_id, > session_start_time> uniquely identiifes a session? Should we perhaps > generate a unique session identifier instead? > > The real reason I'm looking at this is because I'm looking at the > json_log plugin from Michael Paquier. It doesn't have the log line > numbers and I can't figure whether this is something it should have > because I can't quite figure out why they exist in CSV files. I think > there are a few other fields that have been added in Postgres but are > missing from the JSON log because of version skew. > > I'm wondering if we should abstract out the CSV format so instead of > using emit_log_hook you would add a new format and it would specify a > "add_log_attribute(key,val)" hook which would get called once per log > format so you could have as many log formats as you want and be sure > they would all have the same data. That would also mean that the > timestamps would be in sync and we could probably eliminate the > occurrences of the wrong format appearing in the wrong logs. +1 for making the emitters all work off the same source. Any idea how much work we're talking about to do these things? Best, David. -- David Fetter <david(at)fetter(dot)org> 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] GnuTLS support
On Fri, Sep 01, 2017 at 10:33:37PM +0200, Alvaro Herrera wrote: > Tom Lane wrote: > > Robert Haas <robertmh...@gmail.com> writes: > > > On Thu, Aug 31, 2017 at 1:52 PM, Andreas Karlsson <andr...@proxel.se> > > > wrote: > > > >> There are currently two failing SSL tests which at least to me seems more > > >> like they test specific OpenSSL behaviors rather than something which > > >> need > > >> to be true for all SSL libraries. > > > > > I don't know what we should do about these issues. > > > > Maybe the SSL test suite needs to be implementation-specific as well. > > If only two tests fail currently, I suggest that the thing to do is to > split it up in generic vs. library-specific test files. It should be > easy to do with the TAP framework -- just move the library-specific > tests to their own file and mark it as skipped at the start of the file > when a different library is detected. This seems like a much smarter and more reliable way to test. Best, David. -- David Fetter <david(at)fetter(dot)org> 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: Poor cost estimate with interaction between table correlation and partial indexes
On Sat, Aug 26, 2017 at 05:50:26PM -0700, Michael Malis wrote: > Do you think this is a reasonable approach? Should I start working > on a patch based on the solution I described or is there some other > approach I should look into? You'll get more traction with a proof-of-concept patch accompanying the plan than without. Don't bother with any level of care past proof-of-concept until you get positive feedback. Best, David. -- David Fetter <david(at)fetter(dot)org> 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 > >>><fujita.ets...@lab.ntt.co.jp> 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 <david(at)fetter(dot)org> 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 > ><fujita.ets...@lab.ntt.co.jp> 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 <david(at)fetter(dot)org> 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 <and...@anarazel.de> > > > 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 <robertmh...@gmail.com> 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 <david(at)fetter(dot)org> 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 <robertmh...@gmail.com> writes: > > On Wed, Aug 9, 2017 at 10:14 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > >> Thomas Munro <thomas.mu...@enterprisedb.com> 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 <david(at)fetter(dot)org> 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 <ojf...@gmail.com> 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 <david(at)fetter(dot)org> 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 <da...@fetter.org> 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 <david(at)fetter(dot)org> 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 <robertmh...@gmail.com> writes: > > On Mon, Jul 17, 2017 at 6:12 PM, Tom Lane <t...@sss.pgh.pa.us> 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 <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> 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 <da...@fetter.org> 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 <david(at)fetter(dot)org> 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 <robertmh...@gmail.com> writes: > > On Tue, May 30, 2017 at 8:55 PM, David G. Johnston > > <david.g.johns...@gmail.com> 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 <david(at)fetter(dot)org> 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 <da...@fetter.org> 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 <david(at)fetter(dot)org> 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 <c...@anastigmatix.net> 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 <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> 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 > <langote_amit...@lab.ntt.co.jp> 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 <david(at)fetter(dot)org> 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 <da...@fetter.org> 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 <david(at)fetter(dot)org> 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] 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 <da...@fetter.org> 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 <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> 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] 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 <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> 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 <da...@fetter.org> 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 <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> 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 > <k.knizh...@postgrespro.ru> 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 <david(at)fetter(dot)org> 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] WIP: Data at rest encryption
On Tue, Jun 13, 2017 at 10:28:14AM -0400, Peter Eisentraut wrote: > On 6/13/17 09:24, Stephen Frost wrote: > > but there are use-cases where it'd be really nice to be able to > > have PG doing the encryption instead of the filesystem because > > then you can do things like backup the database, copy it somewhere > > else directly, and then restore it using the regular PG > > mechanisms, as long as you have access to the key. That's not > > something you can directly do with filesystem-level encryption > > Interesting point. > > I wonder what the proper extent of "encryption at rest" should be. > If you encrypt just on a file or block level, then someone looking > at the data directory or a backup can still learn a number of things > about the number of tables, transaction rates, various configuration > settings, and so on. In the end, information leaks at a strictly positive baud rate because physics (cf. Claude Shannon, et al). Encryption at rest is one technique whereby people can slow this rate, but there's no such thing as getting it to zero. Let's not creep this feature in the ultimately futile attempt to do so. > In the scenario of a sensitive application hosted on a shared > SAN, I don't think that is good enough. > > Also, in the use case you describe, if you use pg_basebackup to make a > direct encrypted copy of a data directory, I think that would mean you'd > have to keep using the same key for all copies. Right. Best, David. -- David Fetter <david(at)fetter(dot)org> 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] Parallel Aggregation support for aggregate functions that use transitions not implemented for array_agg
On Tue, Jun 06, 2017 at 01:52:45PM -0400, Regina Obe wrote: > It seems CREATE AGGREGATE was expanded in 9.6 to support parallelization of > aggregate functions using transitions, with the addition of serialfunc and > deserialfunc to the aggregate definitions. > > https://www.postgresql.org/docs/10/static/sql-createaggregate.html > > I was looking at the PostgreSQL 10 source code for some example usages of > this and was hoping that array_agg and string_agg would support the feature. > At a cursory glance, it seems they do not use this. > Examples I see that do support it are the average and standard deviation > functions. > > Is there a reason for this or it just wasn't gotten to? I'd bet on lack of tuits. Anything with text has to deal with collation issues, etc., that may make this trickier than it first appears. Best, David. -- David Fetter <david(at)fetter(dot)org> 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] Hash Functions
On Tue, May 16, 2017 at 08:10:39AM -0700, Jeff Davis wrote: > On Mon, May 15, 2017 at 1:04 PM, David Fetter <da...@fetter.org> wrote: > > As the discussion has devolved here, it appears that there are, at > > least conceptually, two fundamentally different classes of partition: > > public, which is to say meaningful to DB clients, and "private", used > > for optimizations, but otherwise opaque to DB clients. > > > > Mashing those two cases together appears to cause more problems than > > it solves. > > I concur at this point. I originally thought hash functions might be > made portable, but I think Tom and Andres showed that to be too > problematic -- the issue with different encodings is the real killer. > > But I also believe hash partitioning is important and we shouldn't > give up on it yet. > > That means we need to have a concept of hash partitions that's > different from range/list partitioning. The terminology > "public"/"private" does not seem appropriate. Logical/physical or > external/internal might be better. I'm not attached to any particular terminology. > With hash partitioning: > * User only specifies number of partitions of the parent table; does > not specify individual partition properties (modulus, etc.) Maybe this is over-thinking it, but I'm picturing us ending up with something along the lines of: PARTITION BY INTERNAL(EXPRESSION) [ WITH ( [PARAMETERS] [, AS, APPROPRIATE] ) ] i.e. it's not clear that we should wire in "number of partitions" as a parameter. In a not that distant future, ANALYZE and similar could have a say in determining both the "how" and the "whether" of partitioning. > * Dump/reload goes through the parent table (though we may provide > options so pg_dump/restore can optimize this) Would it be simplest to default to routing through the immediate ancestor for now? It occurs to me that with the opaque partition system we're designing here, internal partitions would necessarily be leaves in the tree. > * We could provide syntax to adjust the number of partitions, which > would be expensive but still useful sometimes. Yep. I suspect that techniques for this are described in literature, and possibly even in code bases. Any pointers? > * All DDL should be on the parent table, including check constraints, > FKs, unique constraints, exclusion constraints, indexes, etc. Necessarily. > - Unique and exclusion constraints would only be permitted if the > keys are a superset of the partition keys. "Includes either all of the partition expression or none of it," maybe? > - FKs would only be permitted if the two table's partition schemes > match and the keys are members of the same hash opfamily (this could > be relaxed slightly, but it gets a little confusing if so) Relaxing sounds like a not-in-the-first-cut feature, and subtle. > * No attach/detach of partitions Since they're opaque, this is the only sane thing. > * All partitions have the same permissions Since they're opaque, this is the only sane thing. > * Individual partitions would only be individually-addressable for > maintenance (like reindex and vacuum), but not for arbitrary queries Since they're opaque, this is the only sane thing. > - perhaps also COPY for bulk loading/dumping, in case we get clients > smart enough to do their own hashing. This is appealing from a resource allocation point of view in the sense of deciding where the hash computing resources are spent. Do we want something like the NOT VALID/VALIDATE infrastructure to support it? > The only real downside is that it could surprise users -- why can I > add a CHECK constraint on my range-partitioned table but not the > hash-partitioned one? We should try to document this so users don't > find that out too far along. As long as they aren't surprised, I think > users will understand why these aren't quite the same concepts. +1 Best, David. -- David Fetter <david(at)fetter(dot)org> 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] Hash Functions
On Mon, May 15, 2017 at 03:26:02PM -0400, Robert Haas wrote: > On Sun, May 14, 2017 at 9:35 PM, Andres Freund <and...@anarazel.de> wrote: > > On 2017-05-14 21:22:58 -0400, Robert Haas wrote: > >> but wanting a CHECK constraint that applies to only one partition > >> seems pretty reasonable (e.g. CHECK that records for older years > >> are all in the 'inactive' state, or whatever). > > > > On a hash-partitioned table? > > No, probably not. But do we really want the rules for partitioned > tables to be different depending on the kind of partitioning in use? As the discussion has devolved here, it appears that there are, at least conceptually, two fundamentally different classes of partition: public, which is to say meaningful to DB clients, and "private", used for optimizations, but otherwise opaque to DB clients. Mashing those two cases together appears to cause more problems than it solves. Best, David. -- David Fetter <david(at)fetter(dot)org> 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] Hash Functions
On Mon, May 15, 2017 at 07:48:14AM -0700, Jeff Davis wrote: > This would mean we need to reload through the root as Andres and > others suggested, One refinement of this would be to traverse the partition tree, stopping at the first place where the next branch has hash partitions, or at any rate types which have no application-level significance, and load from there. This could allow for parallelizing where it's portable to do so. Level TablePartition Type Base table: Log (N/A) Next partition: Year(range) Next partition: Month (range) Next partition: Day (range) < Data gets loaded no lower than here Next partition: * (hash) That last, any below it, doesn't have a specific name because they're just an implementation detail, i.e. none has any application-level meaning. > and disable a lot of logical partitioning capabilities. I'd be a > little worried about what we do with attaching/detaching, though. Attaching and detaching partitions only makes sense for partitions whose partition keys have application-level meaning anyway. Does it make sense at this point to separate our partitions into two categories, those which have can significance to applications, and those which can't? Best, David. -- David Fetter <david(at)fetter(dot)org> 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] Hash Functions
On Fri, May 12, 2017 at 06:38:55PM -0400, Peter Eisentraut wrote: > On 5/12/17 18:13, Alvaro Herrera wrote: > > I think for logical replication the tuple should appear as being in the > > parent table, not the partition. No? > > Logical replication replicates base table to base table. How those > tables are tied together into a partitioned table or an inheritance tree > is up to the system catalogs on each side. This seems like a totally reasonable approach to pg_dump, especially in light of the fact that logical replication already (and quite reasonably) does it this way. Hard work has been done to make tuple-routing cheap, and this is one of the payoffs. Best, David. -- David Fetter <david(at)fetter(dot)org> 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] Addition of pg_dump --no-publications
On Fri, May 12, 2017 at 10:59:27AM +0900, Michael Paquier wrote: > On Thu, May 11, 2017 at 3:19 PM, Michael Paquier > <michael.paqu...@gmail.com> wrote: > > I imagine that pg_dump -s would be the basic operation that users > > would do first before creating a subcription on a secondary node, but > > what I find surprising is that publications are dumped by default. I > > don't find confusing that those are actually included by default to be > > consistent with the way subcriptions are handled, what I find > > confusing is that there are no options to not dump them, and no > > options to bypass their restore. > > > > So, any opinions about having pg_dump/pg_restore --no-publications? > > And that's really a boring patch, giving the attached. While it's consistent with surrounding code, I find the use of ints to express what is in essence a boolean condition puzzling. Any insights? Best, David. -- David Fetter <david(at)fetter(dot)org> 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 v2] Progress command to monitor progression of long running SQL queries
On Thu, May 11, 2017 at 05:24:16PM +0200, Remi Colinet wrote: > 2017-05-10 21:52 GMT+02:00 David Fetter <da...@fetter.org>: > > > On Wed, May 10, 2017 at 06:40:31PM +0200, Remi Colinet wrote: > > > Hello, > > > > > > This is version 2 of the new command name PROGRESS which I wrote in > > > order to monitor long running SQL queries in a Postgres backend > > > process. > > > > Perhaps I missed something important in the discussion, but was there > > a good reason that this isn't a function callable from SQL, i.e. not > > restricted to the psql client? Please don't top post. http://www.caliburn.nl/topposting.html > That's good point. > > I will probably convert the new command to a SQL function. Great! > I was fumbling between a table or a SQL function. Oracle uses > v$session_longops to track progression of long running SQL queries > which have run for more than 6 seconds. But a function is much > better to provide parameters such as the backend pid and a format > specification for the output. Once it's a function, it can very easily be called in a system (or user-defined) view. Best, David. -- David Fetter <david(at)fetter(dot)org> 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] [Proposal] Allow users to specify multiple tables in VACUUM commands
On Wed, May 10, 2017 at 08:10:48PM +, Bossart, Nathan wrote: > Hi Hackers, > > Currently, VACUUM commands allow you to specify one table or all of > the tables in the current database to vacuum. I’ve recently found > myself wishing I could specify multiple tables in a single VACUUM > statement. For example, this would be convenient when there are > several large tables in a database and only a few need cleanup for > XID purposes. Is this a feature that the community might be > interested in? I haven't read the implementation yet, but I've wanted this feature several times. Best, David. -- David Fetter <david(at)fetter(dot)org> 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 v2] Progress command to monitor progression of long running SQL queries
On Wed, May 10, 2017 at 06:40:31PM +0200, Remi Colinet wrote: > Hello, > > This is version 2 of the new command name PROGRESS which I wrote in > order to monitor long running SQL queries in a Postgres backend > process. Perhaps I missed something important in the discussion, but was there a good reason that this isn't a function callable from SQL, i.e. not restricted to the psql client? Best, David. -- David Fetter <david(at)fetter(dot)org> 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 Tue, May 09, 2017 at 12:48:01PM -0400, Tom Lane wrote: > David Fetter <da...@fetter.org> writes: > > On Fri, May 05, 2017 at 02:20:26PM -0400, Robert Haas wrote: > >> On Thu, May 4, 2017 at 10:59 AM, Chapman Flack <c...@anastigmatix.net> > >> wrote: > >>> invalid input syntax for integer: "21' && 1=2)) Uni/**/ON > >>> SEl/**/eCT 0x646665743166657274,0x646665743266657274, > >>> 0x646665743366657274 -- " > > >> Now that is choice. I wonder what specific database system that's > >> targeting... > > > It could well be targeting some class of pipeline to the database, > > too, for example one that removes comments and/or un-escapes. > > Yeah. It's a bit hard to see a database's parser treating "Uni/**/ON" > as UNION, but if some stack someplace had a keyword check ahead of > a comment-stripping step, maybe that could do something useful. Right. > > It occurs to me that psql's habit of stripping out everything on a > > line that follows a double dash might be vulnerable in this way, but > > I wouldn't see such vulnerabilities as super easy to exploit, as psql > > isn't usually exposed directly to input from the internet. > > I don't think that's a problem: while psql will remove "--" and everything > following it until newline, it won't remove the newline. So there's still > a token boundary there. If we tried to strip /*...*/ comments we'd have > to be more careful. We may still need to be careful. davidfetter@davidfetter=# SELECT 'foo'-- stuff goes here 'bar'; ?column? -- foobar (1 row) > As far as the actual thread topic goes, I tend to agree with > Robert's doubt that there's enough utility or consensus for this. I'm pretty sure we're going to need a logger with more structure than our default, especially as those logs get machine-parsed, and more importantly, machine-acted-upon. Best, David. -- David Fetter <david(at)fetter(dot)org> 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 Fri, May 05, 2017 at 02:20:26PM -0400, Robert Haas wrote: > On Thu, May 4, 2017 at 10:59 AM, Chapman Flack <c...@anastigmatix.net> wrote: > > invalid input syntax for integer: "21' && 1=2)) Uni/**/ON > > SEl/**/eCT 0x646665743166657274,0x646665743266657274, > > 0x646665743366657274 -- " > > Now that is choice. I wonder what specific database system that's > targeting... It could well be targeting some class of pipeline to the database, too, for example one that removes comments and/or un-escapes. It occurs to me that psql's habit of stripping out everything on a line that follows a double dash might be vulnerable in this way, but I wouldn't see such vulnerabilities as super easy to exploit, as psql isn't usually exposed directly to input from the internet. > > I just wonder if anybody thinks web apps, and therefore this > > scenario, are common enough these days to maybe justify one or two > > more GUCs with their own log_line_prefix escapes, such as > > app_client_addr or app_user. Naturally they would only be as > > reliable as the app setting them, and uninterpreted by PostgreSQL > > itself, and so functionally no different from the uninterpreted > > string already available as application_name. The benefit is > > perhaps to be clearer than just overloading application_name to > > carry two or three pieces of information (and perhaps privacy, if > > you care about app user identities and source IPs showing up in ps > > titles). > > > > Worth considering, or is application_name Good Enough? > > I mean, if there were a list of things that needed to propagated > that was (1) lengthy and (2) universally agreed, then we'd probably > want more than one field. But your list is pretty short, so I guess > I don't see why you can't just join them together with a punctuation > mark of your choice and call it good. > > I might be missing something, though. That there isn't universal agreement probably points to wanting an ability to place arbitrary fields in the logs, not just a log_line_prefix. This would be made a good bit simpler by structuring logs, by default, in some serialization a little easier to reason about (and among other things, parse correctly) than CSV. Best, David. -- David Fetter <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> 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 <da...@fetter.org> 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 <david(at)fetter(dot)org> 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 > <thomas.mu...@enterprisedb.com> 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 <david(at)fetter(dot)org> 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 <cr...@2ndquadrant.com> 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 <david(at)fetter(dot)org> 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 <andr...@proxel.se> 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 <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> 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 <craig.rin...@2ndquadrant.com> 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 <david(at)fetter(dot)org> 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 > >>> <langote_amit...@lab.ntt.co.jp> 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 <david(at)fetter(dot)org> 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 > > <langote_amit...@lab.ntt.co.jp> 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 <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> 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 <and...@anarazel.de> 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 <david(at)fetter(dot)org> 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 Fri, Mar 31, 2017 at 12:20:51PM -0500, Kevin Grittner wrote: > On Thu, Mar 30, 2017 at 11:51 AM, Kevin Grittner <kgri...@gmail.com> 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 <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> 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 <rahilasye...@gmail.com> 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 <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> 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 <t...@sss.pgh.pa.us> 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 <david(at)fetter(dot)org> 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 <br...@momjian.us> 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 <david(at)fetter(dot)org> 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 <robertmh...@gmail.com> writes: > >>> On Sun, Feb 19, 2017 at 3:31 AM, Tom Lane <t...@sss.pgh.pa.us> 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 <david(at)fetter(dot)org> 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 <sfr...@snowman.net> 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 <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> 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] UPDATE of partition key
On Thu, Feb 16, 2017 at 03:39:30PM +0530, Amit Khandekar wrote: > >>>> and then run ExecFindPartition() > >>>> again using the root. Will check. I am not sure right now how involved > >>>> that would turn out to be, but I think that logic would not change the > >>>> existing code, so in that sense it is not invasive. > >>> > >>> I couldn't understand why run ExecFindPartition() again on the root > >>> partitioned table, can you clarify? ISTM, we just want to tell the user > >>> in the HINT that trying the same update query with root partitioned table > >>> might work. I'm not sure if it would work instead to find some > >>> intermediate partitioned table (that is, between the root and the one that > >>> update query was tried with) to include in the HINT. > >> > >> What I had in mind was : Give that hint only if there *was* a > >> subpartition that could accommodate that row. And if found, we can > >> only include the subpartition name. > > > > Asking to try the update query with the root table sounds like a good > > enough hint. Trying to find the exact sub-partition (I assume you mean to > > imply sub-tree here) seems like an overkill, IMHO. > Yeah ... I was thinking , anyways it's an error condition, so why not > let the server spend a bit more CPU and get the right sub-partition > for the message. If we decide to write code to find the root > partition, then it's just a matter of another function > ExecFindPartition(). > > Also, I was thinking : give the hint *only* if we know there is a > right sub-partition. Otherwise, it might distract the user. If this is relatively straight-forward, it'd be great. More actionable knowledge is better. Thanks for taking this on. Best, David. -- David Fetter <david(at)fetter(dot)org> 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] UPDATE of partition key
On Wed, Feb 15, 2017 at 01:06:32PM +0530, Amit Khandekar wrote: > On 14 February 2017 at 22:24, David Fetter <da...@fetter.org> wrote: > > On Mon, Feb 13, 2017 at 05:31:56PM +0530, Amit Khandekar wrote: > >> Currently, an update of a partition key of a partition is not > >> allowed, since it requires to move the row(s) into the applicable > >> partition. > >> > >> Attached is a WIP patch (update-partition-key.patch) that removes > >> this restriction. When an UPDATE causes the row of a partition to > >> violate its partition constraint, then a partition is searched in > >> that subtree that can accommodate this row, and if found, the row > >> is deleted from the old partition and inserted in the new > >> partition. If not found, an error is reported. > > > > This is great! > > > > Would it be really invasive to HINT something when the subtree is > > a proper subtree? > > I am not quite sure I understood this question. Can you please > explain it a bit more ... Sorry. When an UPDATE can't happen, there are often ways to hint at what went wrong and how to correct it. Violating a uniqueness constraint would be one example. When an UPDATE can't happen and the depth of the subtree is a plausible candidate for what prevents it, there might be a way to say so. Let's imagine a table called log with partitions on "stamp" log_ and subpartitions, also on "stamp", log_MM. If you do something like UPDATE log_2017 SET "stamp"='2016-11-08 23:03:00' WHERE ... it's possible to know that it might have worked had the UPDATE taken place on log rather than on log_2017. Does that make sense, and if so, is it super invasive to HINT that? Best, David. -- David Fetter <david(at)fetter(dot)org> 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] CREATE TABLE with parallel workers, 10.0?
On Tue, Feb 14, 2017 at 09:24:47PM -0800, Joshua Chamberlain wrote: > Hello, > > (I'm posting to hackers since I got no response on the general > list.) > > I use Postgres + PostGIS quite heavily, and recently have been > taking full advantage of the new parallelism in 9.6. I'm now running > queries in a few hours that would otherwise take more than a day. > > However, parallelism is disabled for all queries that perform writes > (as documented). I would normally run "CREATE TABLE AS [some > super-expensive query]", but since that can't use parallelism I'm > using the \o option in psql, creating the table separately, and then > \copy-ing in the results. That works, but "CREATE TABLE AS" would > be more convenient. How about creating a temp view? CREATE TEMPORARY VIEW foo_tv AS [your gigantic query goes here]; CREATE TABLE foo (LIKE foo_tv); INSERT INTO foo SELECT * FROM foo_tv; > Are there plans in 10.0 to allow parallelism in queries that write, > or at least in "CREATE TABLE AS" queries? (Support in materialized > views would be great, too!) Patches are always welcome, and there's one more commitfest to go before 10. Best, David. -- David Fetter <david(at)fetter(dot)org> 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] UPDATE of partition key
On Mon, Feb 13, 2017 at 05:31:56PM +0530, Amit Khandekar wrote: > Currently, an update of a partition key of a partition is not > allowed, since it requires to move the row(s) into the applicable > partition. > > Attached is a WIP patch (update-partition-key.patch) that removes > this restriction. When an UPDATE causes the row of a partition to > violate its partition constraint, then a partition is searched in > that subtree that can accommodate this row, and if found, the row is > deleted from the old partition and inserted in the new partition. If > not found, an error is reported. This is great! Would it be really invasive to HINT something when the subtree is a proper subtree? Best, David. -- David Fetter <david(at)fetter(dot)org> 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 (v19)
On Sun, Feb 12, 2017 at 10:35:04AM +, Dean Rasheed wrote: > On 11 February 2017 at 01:17, Tomas Vondra <tomas.von...@2ndquadrant.com> > wrote: > > Thanks for the feedback, I'll fix this. I've allowed myself to be a bit > > sloppy because the number of attributes in the statistics is currently > > limited to 8, so the overflows are currently not an issue. But it doesn't > > hurt to make it future-proof, in case we change that mostly artificial limit > > sometime in the future. > > > > Ah right, so it can't overflow at present, but it's neater to have an > overflow-proof algorithm. > > Thinking about the exactness of the division steps is quite > interesting. Actually, the order of the multiplying factors doesn't > matter as long as the divisors are in increasing order. So in both my > proposal: > > result = 1 > for (i = 1; i <= k; i++) > result = (result * (n-k+i)) / i; > > and David's proposal, which is equivalent but has the multiplying > factors in the opposite order, equivalent to: > > result = 1 > for (i = 1; i <= k; i++) > result = (result * (n-i+1)) / i; > > the divisions are exact at each step. The first time through the loop > it divides by 1 which is trivially exact. The second time it divides > by 2, having multiplied by 2 consecutive factors, one of which is > therefore guaranteed to be divisible by 2. The third time it divides > by 3, having multiplied by 3 consecutive factors, one of which is > therefore guaranteed to be divisible by 3, and so on. Right. You know you can use integer division, which make sense as permutations of discrete sets are always integers. > My approach originally seemed more logical to me because of the way it > derives from the recurrence relation binomial(n, k) = binomial(n-1, > k-1) * n / k, but they both work fine as long as they have suitable > overflow checks. Right. We could even cache those checks (sorry) based on data type limits by architecture and OS if performance on those operations ever matters that much. > It's also interesting that descriptions of this algorithm tend to > talk about setting k to min(k, n-k) at the start as an optimisation > step, as I did in fact, whereas it's actually more than that -- it > helps prevent unnecessary intermediate overflows when k > n/2. Of > course, that's not a worry for the current use of this function, but > it's good to have a robust algorithm. Indeed. :) Best, David. -- David Fetter <david(at)fetter(dot)org> 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] One-shot expanded output in psql using \gx
On Thu, Feb 09, 2017 at 11:12:00AM +0100, Christoph Berg wrote: > Re: David Fetter 2017-02-08 <20170208151214.ga8...@fetter.org> > > Would you be open to saving the next person some work by doing > > something similar to how \d is done, namely looking for an 'x' > > modifier after g without regard to how far after? As of this writing, > > the \d version starts at line 398 in master. > > I think that ship has sailed, given there's already \gset. > Supporting \g[optionset] next to it *now*, given no one knows how > "optionset" is going to evolve seems like premature optimization. Good point. Thanks for looking it over. Best, David. -- David Fetter <david(at)fetter(dot)org> 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 on how to simplify comparing two sets
On Wed, Feb 08, 2017 at 11:22:56AM -0500, Tom Lane wrote: > Robert Haas <robertmh...@gmail.com> writes: > > On Wed, Feb 8, 2017 at 4:24 AM, Pantelis Theodosiou <yperc...@gmail.com> > > wrote: > >> I'm not advocating it but I don't see how introducing new SQL keywords > >> breaks backwards compatibility. > > > It does at least a little bit. > > Yes. I think a new set-operation keyword would inevitably have to > be fully reserved --- UNION, INTERSECT, and EXCEPT all are --- which > means that you'd break every application that has used that word as > a table, column, or function name. I've long wanted a SYMMETRIC DIFFERENCE join type, that being the only elementary set operation not included in join types, but nobody at the SQL standards committee seems to have cared enough to help. > Generally speaking, we try very darn hard not to introduce new > reserved words that are not called out as reserved in the SQL > standard. (And even for those, we've sometimes made the grammar > jump through hoops so as not to reserve a word that we didn't > reserve previously.) We just never know what new keywords the standards committee will dream up, or what silliness they'll introduce in the grammar :/ Best, David. -- David Fetter <david(at)fetter(dot)org> 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 (v19)
On Wed, Feb 08, 2017 at 03:23:25PM +, Dean Rasheed wrote:
> On 6 February 2017 at 21:26, Alvaro Herrera <alvhe...@2ndquadrant.com> wrote:
> > Tomas Vondra wrote:
> >> On 02/01/2017 11:52 PM, Alvaro Herrera wrote:
> >
> >> > Nearby, some auxiliary functions such as n_choose_k and
> >> > num_combinations are not documented. What it is that they do? I'd
> >> > move these at the end of the file, keeping the important entry points
> >> > at the top of the file.
> >>
> >> I'd say n-choose-k is pretty widely known term from combinatorics. The
> >> comment would essentially say just 'this is n-choose-k' which seems rather
> >> pointless. So as much as I dislike the self-documenting code, this actually
> >> seems like a good case of that.
> >
> > Actually, we do have such comments all over the place. I knew this as
> > "n sobre k", so the english name doesn't immediately ring a bell with me
> > until I look it up; I think the function comment could just say
> > "n_choose_k -- this function returns the binomial coefficient".
>
> One of the things you have to watch out for when writing code to
> compute binomial coefficients is integer overflow, since the numerator
> and denominator get large very quickly. For example, the current code
> will overflow for n=13, k=12, which really isn't that large.
>
> This can be avoided by computing the product in reverse and using a
> larger datatype like a 64-bit integer to store a single intermediate
> result. The point about multiplying the terms in reverse is that it
> guarantees that each intermediate result is an exact integer (a
> smaller binomial coefficient), so there is no need to track separate
> numerators and denominators, and you avoid huge intermediate
> factorials. Here's what that looks like in psuedo-code:
>
> binomial(int n, int k):
> # Save computational effort by using the symmetry of the binomial
> # coefficients
> k = min(k, n-k);
>
> # Compute the result using binomial(n, k) = binomial(n-1, k-1) * n / k,
> # starting from binomial(n-k, 0) = 1, and computing the sequence
> # binomial(n-k+1, 1), binomial(n-k+2, 2), ...
> #
> # Note that each intermediate result is an exact integer.
> int64 result = 1;
> for (int i = 1; i <= k; i++)
> {
> result = (result * (n-k+i)) / i;
> if (result > INT_MAX) Raise overflow error
> }
> return (int) result;
>
>
> Note also that I think num_combinations(n) is just an expensive way of
> calculating 2^n - n - 1.
Combinations are n!/(k! * (n-k)!), so computing those is more
along the lines of:
unsigned long long
choose(unsigned long long n, unsigned long long k) {
if (k > n) {
return 0;
}
unsigned long long r = 1;
for (unsigned long long d = 1; d <= k; ++d) {
r *= n--;
r /= d;
}
return r;
}
which greatly reduces the chance of overflow.
Best,
David.
--
David Fetter <david(at)fetter(dot)org> 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] One-shot expanded output in psql using \gx
On Wed, Feb 08, 2017 at 03:52:40PM +0100, Christoph Berg wrote:
> Re: David Fetter 2017-02-07 <20170207051659.gc3...@fetter.org>
> > On Mon, Feb 06, 2017 at 08:54:13PM +0100, Christoph Berg wrote:
> > > The majority of voices here was in favor of using \gx, so here is
> > > another version of the same patch which implements that.
> >
> > Patch is useful, and works as documented.
> >
> > Maybe it could get a test or two in src/test/regress/*/psql.*
>
> Good point. The new version tests \g and \gx with a new query, and
> re-running it on the last query buffer.
>
> ! /* \g [filename] -- send query, optionally with output to file/pipe */
> ! else if (strcmp(cmd, "g") == 0)
> {
> char *fname = psql_scan_slash_option(scan_state,
>
>OT_FILEPIPE, NULL, false);
> --- 910,920
> free(fname);
> }
>
> ! /*
> ! * \g [filename] -- send query, optionally with output to file/pipe
> ! * \gx [filename] -- same as \g, with expanded mode forced
> ! */
> ! else if (strcmp(cmd, "g") == 0 || strcmp(cmd, "gx") == 0)
> {
> char *fname = psql_scan_slash_option(scan_state,
>
>OT_FILEPIPE, NULL, false);
> *** exec_command(const char *cmd,
> *** 924,929
> --- 927,934
> pset.gfname = pg_strdup(fname);
> }
> free(fname);
> + if (strcmp(cmd, "gx") == 0)
> + pset.g_expanded = true;
> status = PSQL_CMD_SEND;
> }
Would you be open to saving the next person some work by doing
something similar to how \d is done, namely looking for an 'x'
modifier after g without regard to how far after? As of this writing,
the \d version starts at line 398 in master.
Best,
David.
--
David Fetter <david(at)fetter(dot)org> 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 on how to simplify comparing two sets
On Tue, Feb 07, 2017 at 09:10:17AM -0800, David Fetter wrote:
> On Tue, Feb 07, 2017 at 04:13:40PM +0100, Joel Jacobson wrote:
> > Hi hackers,
> >
> > Currently there is no simple way to check if two sets are equal.
>
> Assuming that a and b each has at least one NOT NULL column, is this
> simple enough? Based on nothing much, I'm assuming here that the IS
> NOT NULL test is faster than IS NULL, but you can flip that and change
> the array to {0} with identical effect.
>
> WITH t AS (
> SELECT a AS a, b AS b, (a IS NOT NULL)::int + (b IS NOT NULL)::int AS ind
> FROM a FULL JOIN b ON ...
> )
> SELECT array_agg(DISTINCT ind) = '{2}'
> FROM t;
You don't actually need a and b in the inner target list.
WITH t AS (
SELECT (a IS NOT NULL)::int + (b IS NOT NULL)::int AS ind
FROM a FULL JOIN b ON ...
)
SELECT array_agg(DISTINCT ind) = '{2}'
FROM t;
This could be shortened further to the following if we ever implement
DISTINCT for window functions, which might involve implementing
DISTINCT via hashing more generally, which means hashable
types...whee!
SELECT array_agg(DISTINCT (a IS NOT NULL)::int + (b IS NOT NULL)::int) OVER ()
= '{2}'
FROM a FULL JOIN b ON ...
Best,
David.
--
David Fetter <david(at)fetter(dot)org> 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 on how to simplify comparing two sets
On Tue, Feb 07, 2017 at 04:13:40PM +0100, Joel Jacobson wrote:
> Hi hackers,
>
> Currently there is no simple way to check if two sets are equal.
Assuming that a and b each has at least one NOT NULL column, is this
simple enough? Based on nothing much, I'm assuming here that the IS
NOT NULL test is faster than IS NULL, but you can flip that and change
the array to {0} with identical effect.
WITH t AS (
SELECT a AS a, b AS b, (a IS NOT NULL)::int + (b IS NOT NULL)::int AS ind
FROM a FULL JOIN b ON ...
)
SELECT array_agg(DISTINCT ind) = '{2}'
FROM t;
Best,
David.
--
David Fetter <david(at)fetter(dot)org> 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] One-shot expanded output in psql using \gx
On Mon, Feb 06, 2017 at 08:54:13PM +0100, Christoph Berg wrote: > The majority of voices here was in favor of using \gx, so here is > another version of the same patch which implements that. Patch is useful, and works as documented. Maybe it could get a test or two in src/test/regress/*/psql.* Best, David. -- David Fetter <david(at)fetter(dot)org> 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] 3D Z-curve spatial index
On Mon, Feb 06, 2017 at 11:04:12AM +0700, Boris Muratshin wrote: > The algorithm for 2D is described in articles (in Russian) > https://habrahabr.ru/post/319810/ and > https://habrahabr.ru/post/319096/ . > Goggle-translator generates (IMHO) suitable for understanding text. Welcome to the community! I apologize for not being more clear. Here, it is customary to send a patch rather than a web link when one has code one wishes to share. This is for several reasons: 1. To establish that you are warranting that you have the right to send the patch, i.e. that it is not legally encumbered in some way. 2. To ensure that the patch stays in the archives, as large web sites have gone away in the past, and will in the future. 3. Because this is how the current development process works. When I mentioned documentation, I was referring to the operation of the SQL-callable interface, assuming that there is one. When people apply the patch, they need to have some idea what it is supposed to do and how to make it do those things. Any difference between what it is supposed to do and what it actually does a bug, whether in the implementation, the documentation, or both. These things and many others are in the https://wiki.postgresql.org/wiki/Developer_FAQ Best, David. -- David Fetter <david(at)fetter(dot)org> 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] 3D Z-curve spatial index
On Sat, Feb 04, 2017 at 03:41:29PM +0700, Boris Muratshin wrote: > Hi hackers, > > The low-level implementation of 3D Z-curve index > (https://github.com/bmuratshin/zcurve/tree/master) > is getting close to GiST R-Tree performance at > significantly lesser number of pages read from disk. > > See attached figures, > times2 - average request execution time VS average points number in result > reads2 - average shared reads number VS average points number in result > > Feel free to connect with me if you have any questions. Please send the actual patch and any documentation you write that comes with it. Best, David. -- David Fetter <david(at)fetter(dot)org> 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, Feb 02, 2017 at 12:14:10PM -0500, Tom Lane wrote: > Alvaro Herrera <alvhe...@2ndquadrant.com> writes: > > Pavel Stehule wrote: > >> Better to enhance this feature step by step. > > > Agreed -- IMO this is a reasonable first step, except that I would > > rename the proposed extension so that it doesn't focus solely on > > the first step. > > Quite. The patch fails to make up its mind whether it's a trivial > example meant as a coding demonstration, or something that's going > to become actually useful. > > In the category of "actually useful", I would put checks like "are > there unqualified outer references in subqueries". That's something > we see complaints about at least once a month, I think, and it's the > type of error that even seasoned SQL authors can make easily. But > the current patch is not extensible in that direction (checking for > this in post_parse_analyze_hook seems quite impractical). > > Also, somebody who wants a check like that isn't necessarily going > to want "no WHERE clause" training wheels. So you're going to need > to think about facilities to enable or disable different checks. This is just the discussion I'd hoped for. I'll draft up a patch in the next day or two, reflecting what's gone so far. Best, David. -- David Fetter <david(at)fetter(dot)org> 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, Feb 02, 2017 at 10:34:43AM -0500, Bruce Momjian wrote: > On Thu, Feb 2, 2017 at 07:18:45AM -0800, David Fetter wrote: > > On Thu, Feb 02, 2017 at 03:16:29PM +, Bruce Momjian wrote: > > > I just don't see this patch going in. I think it needs are > > > larger approach to the problems it is trying to solve. I think > > > it then will be very useful. > > > > What problems that it's trying to solve are not addressed? > > Unjoined tables. Inconsistent alias references. I think there are > a bunch of things and if we can make a list and get a mode to warn > about all of them, it would be very useful. There is always a delicate balance between putting in all that's required for a minimum viable feature and actually getting something out there. As I see it, this feature's main benefit is that it prevents some very common and at the same time very damaging kinds of harm. It also, for now, serves a pedagogical purpose. It's relatively straight-forward for someone with little or no PostgreSQL experience to look at it and see what it does. I originally sent the feature to cover unsubtle types of blunders, I'd like to keep "unsubtle blunders" as the guiding principle here. I can see where an unintentional CROSS JOIN would qualify under that standard. I'm not sure I understand what you mean by inconsistent aliasing. Best, David. -- David Fetter <david(at)fetter(dot)org> 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
