Re: [HACKERS] PoC: Make it possible to disallow WHERE-less UPDATE and DELETE
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? 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] [COMMITTERS] pgsql: Make psql's \set display variables in alphabetical order.
On Wed, Feb 01, 2017 at 04:38:59PM -0500, Robert Haas wrote: > On Wed, Feb 1, 2017 at 1:08 PM, Andres Freund <and...@anarazel.de> wrote: > > On 2017-02-01 12:59:36 -0500, Tom Lane wrote: > >> David Fetter <da...@fetter.org> writes: > >> > On Wed, Feb 01, 2017 at 04:25:25PM +, Tom Lane wrote: > >> >> Make psql's \set display variables in alphabetical order. > >> > >> > This is a substantial usability improvement which nevertheless is hard > >> > to imagine changes things that scripts relied on. I think it's worth > >> > back-patching. > >> > >> I'm not that excited about it personally, but I agree it would be unlikely > >> to break anything. Other votes? > > > > -0.5 - I see very little reason to backpatch this over dozes of other > > changes. > > I'll vote -1. I don't think this is a bug fix. I withdraw my suggestion. 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] Re: [COMMITTERS] pgsql: Make psql's \set display variables in alphabetical order.
On Wed, Feb 01, 2017 at 04:25:25PM +, Tom Lane wrote: > Make psql's \set display variables in alphabetical order. This is a substantial usability improvement which nevertheless is hard to imagine changes things that scripts relied on. I think it's worth back-patching. 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 \G
On Mon, Jan 30, 2017 at 08:22:40AM -0700, David G. Johnston wrote: > On Mon, Jan 30, 2017 at 8:14 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > > Stephen Frost <sfr...@snowman.net> writes: > > > This particular bike-shedding really doesn't seem to be terribly useful > > > or sensible, to me. \gx isn't "consistent" or "descriptive", frankly. > > > > Why not? To me it reads as "\g with an x option". The "x" refers to > > the implied "\x", so it's not an arbitrary choice at all. > > > > The main problem I see with \G is that it's a dead end. If somebody > > comes along next year and says "I'd like a variant of \g with some other > > frammish", what will we do? There are no more case variants to use. > > > > In short, really the direction this ought to go in is \g[options] [file] > > which is perfectly consistent with precedents in psql such as \d. > > But there isn't any place where we've decided that upper case means > > a variant of a lower case command. > > > > +1 +1 from me, too, for what it's worth. If we're expanding the meanings of \g, let's do it with at least some eye to future expansions. 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] Superowners
On Fri, Jan 27, 2017 at 05:48:46PM -0500, Peter Eisentraut wrote: > On 1/26/17 1:25 PM, Simon Riggs wrote: > > That should include the ability to dump all objects, yet without > > any security details. And it should allow someone to setup logical > > replication easily, including both trigger based and new logical > > replication. And GRANT ON ALL should work. > > This basically sounds like a GRANT $privilege ON ALL $objecttype TO > $user. So you could have a user that can read everything, for > example. This would still be tremendously useful. So would a cogent, user-facing explanation of DCL in one obvious spot, which I guess I've signed up to do now that I'm complaining about its lack. 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] pg_ls_dir & friends still have a hard-coded superuser check
On Sun, Jan 29, 2017 at 05:52:51PM -0500, Robert Haas wrote: > On Sun, Jan 29, 2017 at 5:39 PM, David Fetter <da...@fetter.org> wrote: > > On Thu, Jan 26, 2017 at 08:50:27AM -0500, Robert Haas wrote: > >> On Wed, Jan 25, 2017 at 10:31 PM, Stephen Frost <sfr...@snowman.net> wrote: > >> > Frankly, I get quite tired of the argument essentially being made > >> > here that because pg_ls_dir() wouldn't grant someone superuser > >> > rights, that we should remove superuser checks from everything. > >> > As long as you are presenting it like that, I'm going to be quite > >> > dead-set against any of it. > >> 1. pg_ls_dir. I cannot see how this can ever be used to get > >> superuser privileges. > > > > With pilot error, all things are possible. A file name under $PGDATA > > could be the superuser password. > > Uh, true. The default value of application_name could be the > superuser password, too, but we still allow access to it by > unprivileged users. Of course. 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] pg_ls_dir & friends still have a hard-coded superuser check
On Thu, Jan 26, 2017 at 08:50:27AM -0500, Robert Haas wrote: > On Wed, Jan 25, 2017 at 10:31 PM, Stephen Frost <sfr...@snowman.net> wrote: > > Frankly, I get quite tired of the argument essentially being made > > here that because pg_ls_dir() wouldn't grant someone superuser > > rights, that we should remove superuser checks from everything. > > As long as you are presenting it like that, I'm going to be quite > > dead-set against any of it. > 1. pg_ls_dir. I cannot see how this can ever be used to get > superuser privileges. With pilot error, all things are possible. A file name under $PGDATA could be the superuser password. 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 as a set returning function
On Wed, Jan 25, 2017 at 08:51:38PM -0500, Corey Huinker wrote: > I've put in some more work on this patch, mostly just taking Alvaro's > suggestions, which resulted in big code savings. The patch applies atop master. The test (ls) which previously crashed the backend now doesn't, and works in a reasonable way. The description of the function still talks about its being a proof of concept. There are still neither regression tests nor SGML documentation. Are we at a point where we should add 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] One-shot expanded output in psql using \G
On Fri, Jan 27, 2017 at 02:27:37PM +0100, Christoph Berg wrote: > I frequently find myself in the situation that I want the "\x" > expanded output mode activated just for one query. There's little > wrong with typing "\x" and re-executing the query in that case, but > then I'm always annoyed that the expanded output is still active for > the next query after that. +1 Your situation is familiar to me, and likely common among heavy users of 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] COPY as a set returning function
On Wed, Jan 25, 2017 at 12:23:23PM -0500, Corey Huinker wrote: > > Feel free to mark it returned as feedback. The concept didn't > generate as much enthusiasm as I had hoped, so I think the right > thing to do now is scale it back to a patch that makes > CopyFromRawFields() externally visible so that extensions can use > them. You're getting enthusiasm in the form of these reviews and suggestions for improvement. That it doesn't always happen immediately is a byproduct of the scarcity of developer time and the sheer volume of things to which people need to pay attention. 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 as a set returning function
On Wed, Jan 25, 2017 at 06:16:16AM -0800, David Fetter wrote:
> On Mon, Oct 31, 2016 at 04:45:40PM -0400, Corey Huinker wrote:
> > Attached is a patch that implements copy_srf().
> >
> > The function signature reflects cstate more than it reflects the COPY
> > options (filename+is_program instead of FILENAME or PROGRAM, etc)
>
> The patch as it stands needs a rebase. I'll see what I can do today.
Please find attached a rebase, which fixes an OID collision that crept in.
- The patch builds against master and passes "make check".
- The patch does not contain user-visible documentation or examples.
- The patch does not contain tests.
I got the following when I did a brief test.
SELECT * FROM copy_srf(filename => 'ls', is_program => true) AS t(l text);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
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
diff --git a/src/backend/catalog/system_views.sql
b/src/backend/catalog/system_views.sql
index 4dfedf8..ae07cfb 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1065,6 +1065,21 @@ LANGUAGE INTERNAL
STRICT IMMUTABLE PARALLEL SAFE
AS 'jsonb_insert';
+CREATE OR REPLACE FUNCTION copy_srf(
+ IN filename text DEFAULT null,
+ IN is_program boolean DEFAULT false,
+ IN format text DEFAULT 'text',
+ IN delimiter text DEFAULT null,
+ IN null_string text DEFAULT E'\\N',
+ IN header boolean DEFAULT false,
+ IN quote text DEFAULT null,
+ IN escape text DEFAULT null,
+ IN encoding text DEFAULT null)
+RETURNS SETOF RECORD
+LANGUAGE INTERNAL
+VOLATILE ROWS 1000 COST 1000 CALLED ON NULL INPUT
+AS 'copy_srf';
+
-- The default permissions for functions mean that anyone can execute them.
-- A number of functions shouldn't be executable by just anyone, but rather
-- than use explicit 'superuser()' checks in those functions, we use the GRANT
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index f9362be..8e1bd39 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -30,6 +30,7 @@
#include "commands/defrem.h"
#include "commands/trigger.h"
#include "executor/executor.h"
+#include "funcapi.h"
#include "libpq/libpq.h"
#include "libpq/pqformat.h"
#include "mb/pg_wchar.h"
@@ -562,7 +563,6 @@ CopyGetData(CopyState cstate, void *databuf, int minread,
int maxread)
errmsg("could not read from
COPY file: %m")));
break;
case COPY_OLD_FE:
-
/*
* We cannot read more than minread bytes (which in
practice is 1)
* because old protocol doesn't have any clear way of
separating
@@ -4740,3 +4740,377 @@ CreateCopyDestReceiver(void)
return (DestReceiver *) self;
}
+
+Datum
+copy_srf(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ TupleDesc tupdesc;
+ Tuplestorestate *tupstore = NULL;
+ MemoryContext per_query_ctx;
+ MemoryContext oldcontext;
+ FmgrInfo*in_functions;
+ Oid *typioparams;
+ Oid in_func_oid;
+
+ CopyStateData copy_state;
+ int col;
+
+ Datum *values;
+ bool*nulls;
+
+ /* check to see if caller supports us returning a tuplestore */
+ if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+errmsg("set-valued function called in context
that cannot accept a set")));
+ }
+
+ if (!(rsinfo->allowedModes & SFRM_Materialize) || rsinfo->expectedDesc
== NULL)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+errmsg("materialize mode required, but it is
not allowed in this context")));
+ }
+
+ tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc);
+ values = (Datum *) palloc(tupdesc->natts * sizeof(Datum));
+ nulls = (bool *) palloc(tupdesc->natts * sizeof(bool));
+ in_functions = (FmgrInfo *) palloc(tupdesc->natts * sizeof(FmgrInfo));
+ typioparams = (Oid *) palloc(tupdesc->natts * sizeof(Oid));
Re: [HACKERS] COPY as a set returning function
On Mon, Oct 31, 2016 at 04:45:40PM -0400, Corey Huinker wrote: > Attached is a patch that implements copy_srf(). > > The function signature reflects cstate more than it reflects the COPY > options (filename+is_program instead of FILENAME or PROGRAM, etc) The patch as it stands needs a rebase. I'll see what I can do today. 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 as a set returning function
On Wed, Jan 25, 2017 at 02:37:57PM +0900, Michael Paquier wrote: > On Mon, Dec 5, 2016 at 2:10 PM, Haribabu Kommi <kommi.harib...@gmail.com> > wrote: > > On Tue, Nov 1, 2016 at 7:45 AM, Corey Huinker <corey.huin...@gmail.com> > > wrote: > >> > >> Attached is a patch that implements copy_srf(). > > > > Moved to next CF with "needs review" status. > > This patch is still waiting for review. David, are you planning to > look at it by the end of the CF? I'll be doing this today. 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] RustgreSQL
On Mon, Jan 09, 2017 at 12:51:43PM -0500, Robert Haas wrote: > On Sun, Jan 8, 2017 at 4:59 AM, Gavin Flower > <gavinflo...@archidevsys.co.nz> wrote: > >> Is this completely unrealistic or is it carved in stone PostgreSQL will > >> always be a C project forever and ever? > >> > > From my very limited understanding, PostgreSQL is more likely to be > > converted to C++! > > I'm tempted to snarkily reply that we should start by finishing the > conversion of PostgreSQL from LISP to C before we worry about > converting it to anything else. There are various code comments that > imply that it actually was LISP at one time and I can certainly > believe that given our incredibly wasteful use of linked lists in so > many places. gram.y asserts that this problem was fixed as far as the > grammar is concerned... > > *AUTHORDATEMAJOR EVENT > *Andrew Yu Sept, 1994 > POSTQUEL to SQL conversion > *Andrew Yu Oct, 1994 lispy > code conversion > > ...but I think it'd be fair to say that even there it was fixed only in part. David Gould (added to Cc:) mentioned that he had some ideas as to how to address 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] New CORRESPONDING clause design
On Tue, Jan 17, 2017 at 08:20:25AM -0600, Merlin Moncure wrote: > On Tue, Jan 17, 2017 at 12:37 AM, Surafel Temsgen <surafel3...@gmail.com> > wrote: > > I am new here and I really want to contribute, I have read same resource > > that help understanding database system and postgresql. I would like to > > start implementing sql syntax corresponding by clause because I believe > > implementing sql syntax gives an opportunity to familiarize many part of > > postgresql source code. Previous implementation is here and have an issue on > > explain query and break cases on unlabeled NULLs > > To repeat what a corresponding by clause means > > Corresponding clause either contains a BY(...) clause or not. If it > > doesn't have a BY(...) clause the usage is as follows. > > This is great stuff. Does the syntax only apply to UNION? I would > imagine it would also apply to INTERSECT/EXCEPT? What about UNION > ALL? My draft working standard from 2011 says in 7IWD-02-Foundation section 7.13 : a) If CORRESPONDING is specified, then: i) Within the columns of T1, equivalent s shall not be specified more than once and within the columns of T2, equivalent s shall not be specified more than once. ii) At least one column of T1 shall have a that is the of some column of T2. iii) Case: 1) If is not specified, then let SL be a of those s that are s of both T1 and T2 in the order that those s appear in T1. 2) If is specified, then let SL be a of those s explicitly appearing in the in the order that these s appear in the . Every in the shall be a of both T1 and T2. iv) The or is equivalent to: ( SELECT SL FROM TN1 ) OP ( SELECT SL FROM TN2 ) Earlier, it defines ( UNION | EXCEPT ) [ ALL | DISTINCT ] to have a meaning in this context, to wit: ::= | UNION [ ALL | DISTINCT ] [ ] | EXCEPT [ ALL | DISTINCT ] [ ] 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] Retiring from the Core Team
On Wed, Jan 11, 2017 at 04:29:19PM -0800, Josh Berkus wrote: > Hackers: > > You will have noticed that I haven't been very active for the past > year. My new work on Linux containers and Kubernetes has been even > more absorbing than I anticipated, and I just haven't had a lot of > time for PostgreSQL work. > > For that reason, as of today, I am stepping down from the PostgreSQL > Core Team. Thanks for all your hard work. It's not easy stepping into the (to hackers) un-sexiest role in the project, namely PR. I think I speak for all of us when I express our appreciation for your stepping up to this and staying at it, year after year. 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 Tue, Jan 10, 2017 at 08:31:47AM -0800, David Fetter wrote:
> On Mon, Jan 09, 2017 at 07:52:11PM -0300, Alvaro Herrera wrote:
> > David Fetter wrote:
> >
> > > + if (query->commandType == CMD_UPDATE || query->commandType ==
> > > CMD_DELETE)
> > > + {
> > > + /* Make sure there's something to look at. */
> > > + Assert(query->jointree != NULL);
> > > + if (query->jointree->quals == NULL)
> > > + ereport(ERROR,
> > > + (errcode(ERRCODE_SYNTAX_ERROR),
> > > + errmsg("%s requires a WHERE clause
> > > when the require_where hook is enabled.",
> > > + query->commandType ==
> > > CMD_UPDATE ? "UPDATE" : "DELETE"),
> > > + errhint("To %s all rows, use \"WHERE
> > > true\" or similar.",
> > > + query->commandType ==
> > > CMD_UPDATE ? "update" : "delete")));
> > > + }
> >
> > Per my earlier comment, I think this should use
> > ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED instead.
>
> Fixed.
>
> > I think this should say "the \"require_hook\" extension" rather than
> > use the term "hook".
>
> Fixed.
>
> > (There are two or three translatability rules violations in this
> > snippet,
>
> Based on the hints in the docs docs around translation, I've
> refactored this a bit.
>
> > but since this is an extension and those are not translatable, I
> > won't say elaborate further.)
>
> "Not translatable," or "not currently translated?"
Oops^2. Correct patch attached and sent to correct list. :P
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
commit 9e65a67434a553b717ba735472cf3108f4ee0e23
Author: David Fetter <da...@fetter.org>
Date: Thu Jul 21 23:34:21 2016 -0700
require_where: a contrib hook
This adds a process utility hook which makes simple UPDATE and DELETE
statements require a WHERE clause when loaded.
It is not intended to provide a general capability. Instead, its job is to
prevent common human errors made by people who only rarely use SQL. The
hook
is small enough to be usable as part of a short lesson on hooks.
diff --git a/contrib/Makefile b/contrib/Makefile
index 25263c0..4bd456f 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -40,6 +40,7 @@ SUBDIRS = \
pgstattuple \
pg_visibility \
postgres_fdw\
+ require_where \
seg \
spi \
tablefunc \
diff --git a/contrib/require_where/Makefile b/contrib/require_where/Makefile
new file mode 100644
index 000..933eb00
--- /dev/null
+++ b/contrib/require_where/Makefile
@@ -0,0 +1,19 @@
+# contrib/require_where/Makefile
+
+MODULE_big = require_where
+OBJS = require_where.o
+
+PGFILEDESC = 'require_where - require simple DELETEs and UPDATEs to have a
WHERE clause'
+
+REGRESS = require_where
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS = $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/require_where
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_builddir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/require_where/expected/require_where.out
b/contrib/require_where/expected/require_where.out
new file mode 100644
index 000..3fe28ec
--- /dev/null
+++ b/contrib/require_where/expected/require_where.out
@@ -0,0 +1,16 @@
+--
+-- Test require_where
+--
+\set echo all
+CREATE TABLE test_require_where(t TEXT);
+UPDATE test_require_where SET t=t; -- succeeds
+DELETE FROM test_require_where; -- succeeds
+LOAD 'require_where';
+UPDATE test_require_where SET t=t; -- fails
+ERROR: UPDATE requires a WHERE clause when the "require_where" extension is
loaded.
+HINT: To update all rows, use "WHERE true" or similar.
+UPDATE test_require_where SET t=t WHERE true; -- succeeds
+DELETE FROM test_require_where; -- fails
+ERROR: DELETE requires a WHERE clause when the "require_where" extension is
loaded.
+HINT: To delete all rows, use "WHERE true" or similar.
+DELETE FROM test_require_where WHERE true; -- succeeds
diff --git a/co
Re: [HACKERS] PoC: Make it possible to disallow WHERE-less UPDATE and DELETE
On Mon, Jan 09, 2017 at 07:52:11PM -0300, Alvaro Herrera wrote:
> David Fetter wrote:
>
> > + if (query->commandType == CMD_UPDATE || query->commandType ==
> > CMD_DELETE)
> > + {
> > + /* Make sure there's something to look at. */
> > + Assert(query->jointree != NULL);
> > + if (query->jointree->quals == NULL)
> > + ereport(ERROR,
> > + (errcode(ERRCODE_SYNTAX_ERROR),
> > +errmsg("%s requires a WHERE clause
> > when the require_where hook is enabled.",
> > +query->commandType ==
> > CMD_UPDATE ? "UPDATE" : "DELETE"),
> > +errhint("To %s all rows, use \"WHERE
> > true\" or similar.",
> > +query->commandType ==
> > CMD_UPDATE ? "update" : "delete")));
> > + }
>
> Per my earlier comment, I think this should use
> ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED instead.
Fixed.
> I think this should say "the \"require_hook\" extension" rather than
> use the term "hook".
Fixed.
> (There are two or three translatability rules violations in this
> snippet,
Based on the hints in the docs docs around translation, I've
refactored this a bit.
> but since this is an extension and those are not translatable, I
> won't say elaborate further.)
"Not translatable," or "not currently translated?"
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
commit 42f50cb8fa9848bbbc6776bcea03293a6b28b2d4
Author: Alvaro Herrera <alvhe...@alvh.no-ip.org>
Date: Tue Jan 10 11:41:13 2017 -0300
Fix overflow check in StringInfo; add missing casts
A few thinkos I introduced in fa2fa9955280. Also, amend a similarly
broken comment.
Report by Daniel Vérité.
Authors: Daniel Vérité, Álvaro Herrera
Discussion:
https://postgr.es/m/1706e85e-60d2-494e-8a64-9af1e1b21...@manitou-mail.org
diff --git a/src/backend/lib/stringinfo.c b/src/backend/lib/stringinfo.c
index bdc204e..3eee49b 100644
--- a/src/backend/lib/stringinfo.c
+++ b/src/backend/lib/stringinfo.c
@@ -313,19 +313,20 @@ enlargeStringInfo(StringInfo str, int needed)
* for efficiency, double the buffer size each time it overflows.
* Actually, we might need to more than double it if 'needed' is big...
*/
- newlen = 2 * str->maxlen;
- while (needed > newlen)
+ newlen = 2 * (Size) str->maxlen;
+ while ((Size) needed > newlen)
newlen = 2 * newlen;
/*
-* Clamp to the limit in case we went past it. Note we are assuming
here
-* that limit <= INT_MAX/2, else the above loop could overflow. We will
-* still have newlen >= needed.
+* Clamp to the limit in case we went past it. (We used to depend on
+* limit <= INT32_MAX/2, to avoid overflow in the loop above; we no
longer
+* depend on that, but if "needed" and str->maxlen ever become wider, we
+* will need similar caution here.) We will still have newlen >=
needed.
*/
if (newlen > limit)
newlen = limit;
- str->data = (char *) repalloc_huge(str->data, (Size) newlen);
+ str->data = (char *) repalloc_huge(str->data, newlen);
str->maxlen = newlen;
}
--
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 Mon, Jan 09, 2017 at 07:52:11PM -0300, Alvaro Herrera wrote:
> David Fetter wrote:
>
> > + if (query->commandType == CMD_UPDATE || query->commandType ==
> > CMD_DELETE)
> > + {
> > + /* Make sure there's something to look at. */
> > + Assert(query->jointree != NULL);
> > + if (query->jointree->quals == NULL)
> > + ereport(ERROR,
> > + (errcode(ERRCODE_SYNTAX_ERROR),
> > +errmsg("%s requires a WHERE clause
> > when the require_where hook is enabled.",
> > +query->commandType ==
> > CMD_UPDATE ? "UPDATE" : "DELETE"),
> > +errhint("To %s all rows, use \"WHERE
> > true\" or similar.",
> > +query->commandType ==
> > CMD_UPDATE ? "update" : "delete")));
> > + }
>
> Per my earlier comment, I think this should use
> ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED instead.
>
> I think this should say "the \"require_hook\" extension" rather than
> use the term "hook".
>
> (There are two or three translatability rules violations in this
> snippet, but since this is an extension and those are not translatable,
> I won't say elaborate further.)
I'm happy to fix it. Are the rules all in one spot I can refer to?
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 Sun, Jan 08, 2017 at 06:50:12PM -0600, Jim Nasby wrote:
> On 1/5/17 12:04 AM, David Fetter wrote:
> > +errmsg("UPDATE requires a WHERE clause
> > when require_where.delete is set to on"),
>
> ISTM that message is no longer true.
>
> The second if could also be an else if too.
I refactored this into one case and removed some fluff, some of which
was never needed, some of which is no longer.
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
commit 123f2c48e67da6a3693decfae2722fa1a686f48d
Author: David Fetter <da...@fetter.org>
Date: Thu Jul 21 23:34:21 2016 -0700
require_where: a contrib hook
This adds a process utility hook which makes simple UPDATE and DELETE
statements require a WHERE clause when loaded.
It is not intended to provide a general capability. Instead, its job is to
prevent common human errors made by people who only rarely use SQL. The
hook
is small enough to be usable as part of a short lesson on hooks.
diff --git a/contrib/Makefile b/contrib/Makefile
index 25263c0..4bd456f 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -40,6 +40,7 @@ SUBDIRS = \
pgstattuple \
pg_visibility \
postgres_fdw\
+ require_where \
seg \
spi \
tablefunc \
diff --git a/contrib/require_where/Makefile b/contrib/require_where/Makefile
new file mode 100644
index 000..933eb00
--- /dev/null
+++ b/contrib/require_where/Makefile
@@ -0,0 +1,19 @@
+# contrib/require_where/Makefile
+
+MODULE_big = require_where
+OBJS = require_where.o
+
+PGFILEDESC = 'require_where - require simple DELETEs and UPDATEs to have a
WHERE clause'
+
+REGRESS = require_where
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS = $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/require_where
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_builddir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/require_where/expected/require_where.out
b/contrib/require_where/expected/require_where.out
new file mode 100644
index 000..1884722
--- /dev/null
+++ b/contrib/require_where/expected/require_where.out
@@ -0,0 +1,16 @@
+--
+-- Test require_where
+--
+\set echo all
+CREATE TABLE test_require_where(t TEXT);
+UPDATE test_require_where SET t=t; -- succeeds
+DELETE FROM test_require_where; -- succeeds
+LOAD 'require_where';
+UPDATE test_require_where SET t=t; -- fails
+ERROR: UPDATE requires a WHERE clause when the require_where hook is enabled.
+HINT: To update all rows, use "WHERE true" or similar.
+UPDATE test_require_where SET t=t WHERE true; -- succeeds
+DELETE FROM test_require_where; -- fails
+ERROR: DELETE requires a WHERE clause when the require_where hook is enabled.
+HINT: To delete all rows, use "WHERE true" or similar.
+DELETE FROM test_require_where WHERE true; -- succeeds
diff --git a/contrib/require_where/require_where.c
b/contrib/require_where/require_where.c
new file mode 100644
index 000..9eae929
--- /dev/null
+++ b/contrib/require_where/require_where.c
@@ -0,0 +1,62 @@
+/*
+ * --
+ *
+ * require_where.c
+ *
+ * Copyright (C) 2017, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * contrib/require_where/require_where.c
+ *
+ * --
+ */
+#include "postgres.h"
+
+#include "fmgr.h"
+
+#include "parser/analyze.h"
+
+#include "utils/elog.h"
+
+PG_MODULE_MAGIC;
+
+void _PG_init(void);
+void _PG_fini(void);
+
+static post_parse_analyze_hook_type original_post_parse_analyze_hook =
NULL;
+
+/*
+ * This module makes simple UPDATE and DELETE statements require a WHERE clause
+ * and complains when this is not present.
+ */
+static void
+require_where_check(ParseState *pstate, Query *query)
+{
+
+ if (query->commandType == CMD_UPDATE || query->commandType ==
CMD_DELETE)
+ {
+ /* Make sure there's something to look at. */
+ Assert(query->jointree != NULL);
+ if (query->jointree->quals == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+errmsg("%s requires a WHERE clause
when the require_where hook is enabled.",
+query->commandTyp
Re: [HACKERS] PoC: Make it possible to disallow WHERE-less UPDATE and DELETE
On Wed, Jan 04, 2017 at 09:58:26PM -0800, David Fetter wrote: > On Sun, Jan 01, 2017 at 07:57:33PM +0900, Michael Paquier wrote: > > On Sun, Jan 1, 2017 at 12:34 PM, David Fetter <da...@fetter.org> wrote: > > > I've rolled your patches into this next one and clarified the commit > > > message, as there appears to have been some confusion about the scope. > > > > Is there actually a meaning to have two options? Couldn't we leave > > with just one? Actually, I'd just suggest to rip off the option and > > just to make the checks enabled when the library is loaded, to get a > > module as simple as passwordcheck. > > Done. > > > --- /dev/null > > +++ b/contrib/require_where/data/test_require_where.data > > @@ -0,0 +1,16 @@ > > +Four > > There is no need to load fake data as you need to just check the > > parsing of the query. So let's simplify the tests and remove that. > > Removed. > > > Except that the shape of the module is not that bad. The copyright > > notices need to be updated to 2017, and it would be nice to have some > > comments at the top of require_where.c to describe what it does. > > Please find attached the next version of the patch including Pavel's > suggestion that I provide some motivation for including those > Asserts. Here's one with the commit message included for easier review. 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 commit 6e28e474982a3da5095dab2bfc0d9a3241feda05 Author: David Fetter <da...@fetter.org> Date: Thu Jul 21 23:34:21 2016 -0700 require_where: a contrib hook This adds a process utility hook which makes simple UPDATE and DELETE statements require a WHERE clause when loaded. It is not intended to provide a general capability. Instead, its job is to prevent common human errors made by people who only rarely use SQL. The hook is small enough to be usable as part of a short lesson on hooks. diff --git a/contrib/Makefile b/contrib/Makefile index 25263c0..4bd456f 100644 --- a/contrib/Makefile +++ b/contrib/Makefile @@ -40,6 +40,7 @@ SUBDIRS = \ pgstattuple \ pg_visibility \ postgres_fdw\ + require_where \ seg \ spi \ tablefunc \ diff --git a/contrib/require_where/Makefile b/contrib/require_where/Makefile new file mode 100644 index 000..933eb00 --- /dev/null +++ b/contrib/require_where/Makefile @@ -0,0 +1,19 @@ +# contrib/require_where/Makefile + +MODULE_big = require_where +OBJS = require_where.o + +PGFILEDESC = 'require_where - require simple DELETEs and UPDATEs to have a WHERE clause' + +REGRESS = require_where + +ifdef USE_PGXS +PG_CONFIG = pg_config +PGXS = $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir = contrib/require_where +top_builddir = ../.. +include $(top_builddir)/src/Makefile.global +include $(top_builddir)/contrib/contrib-global.mk +endif diff --git a/contrib/require_where/expected/require_where.out b/contrib/require_where/expected/require_where.out new file mode 100644 index 000..a36dd1f --- /dev/null +++ b/contrib/require_where/expected/require_where.out @@ -0,0 +1,16 @@ +-- +-- Test require_where +-- +\set echo all +CREATE TABLE test_require_where(t TEXT); +UPDATE test_require_where SET t=t; -- succeeds +DELETE FROM test_require_where; -- succeeds +LOAD 'require_where'; +UPDATE test_require_where SET t=t; -- fails +ERROR: UPDATE requires a WHERE clause when require_where.delete is set to on +HINT: To update all rows, use "WHERE true" or similar. +UPDATE test_require_where SET t=t WHERE true; -- succeeds +DELETE FROM test_require_where; -- fails +ERROR: DELETE requires a WHERE clause when require_where.delete is set to on +HINT: To delete all rows, use "WHERE true" or similar. +DELETE FROM test_require_where WHERE true; -- succeeds diff --git a/contrib/require_where/require_where.c b/contrib/require_where/require_where.c new file mode 100644 index 000..09f2578 --- /dev/null +++ b/contrib/require_where/require_where.c @@ -0,0 +1,73 @@ +/* + * -- + * + * require_where.c + * + * Copyright (C) 2017, PostgreSQL Global Development Group + * + * IDENTIFICATION + * contrib/require_where/require_where.c + * + * -- + */ +#include "postgres.h" + +#include "fmgr.h" + +#include "parser/analyze.h" + +#include "utils/elog.h" +#inclu
Re: [HACKERS] PoC: Make it possible to disallow WHERE-less UPDATE and DELETE
On Sun, Jan 01, 2017 at 07:57:33PM +0900, Michael Paquier wrote:
> On Sun, Jan 1, 2017 at 12:34 PM, David Fetter <da...@fetter.org> wrote:
> > I've rolled your patches into this next one and clarified the commit
> > message, as there appears to have been some confusion about the scope.
>
> Is there actually a meaning to have two options? Couldn't we leave
> with just one? Actually, I'd just suggest to rip off the option and
> just to make the checks enabled when the library is loaded, to get a
> module as simple as passwordcheck.
Done.
> --- /dev/null
> +++ b/contrib/require_where/data/test_require_where.data
> @@ -0,0 +1,16 @@
> +Four
> There is no need to load fake data as you need to just check the
> parsing of the query. So let's simplify the tests and remove that.
Removed.
> Except that the shape of the module is not that bad. The copyright
> notices need to be updated to 2017, and it would be nice to have some
> comments at the top of require_where.c to describe what it does.
Please find attached the next version of the patch including Pavel's
suggestion that I provide some motivation for including those
Asserts.
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
diff --git a/contrib/Makefile b/contrib/Makefile
index 25263c0..4bd456f 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -40,6 +40,7 @@ SUBDIRS = \
pgstattuple \
pg_visibility \
postgres_fdw\
+ require_where \
seg \
spi \
tablefunc \
diff --git a/contrib/require_where/Makefile b/contrib/require_where/Makefile
new file mode 100644
index 000..933eb00
--- /dev/null
+++ b/contrib/require_where/Makefile
@@ -0,0 +1,19 @@
+# contrib/require_where/Makefile
+
+MODULE_big = require_where
+OBJS = require_where.o
+
+PGFILEDESC = 'require_where - require simple DELETEs and UPDATEs to have a
WHERE clause'
+
+REGRESS = require_where
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS = $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/require_where
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_builddir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/require_where/expected/require_where.out
b/contrib/require_where/expected/require_where.out
new file mode 100644
index 000..a36dd1f
--- /dev/null
+++ b/contrib/require_where/expected/require_where.out
@@ -0,0 +1,16 @@
+--
+-- Test require_where
+--
+\set echo all
+CREATE TABLE test_require_where(t TEXT);
+UPDATE test_require_where SET t=t; -- succeeds
+DELETE FROM test_require_where; -- succeeds
+LOAD 'require_where';
+UPDATE test_require_where SET t=t; -- fails
+ERROR: UPDATE requires a WHERE clause when require_where.delete is set to on
+HINT: To update all rows, use "WHERE true" or similar.
+UPDATE test_require_where SET t=t WHERE true; -- succeeds
+DELETE FROM test_require_where; -- fails
+ERROR: DELETE requires a WHERE clause when require_where.delete is set to on
+HINT: To delete all rows, use "WHERE true" or similar.
+DELETE FROM test_require_where WHERE true; -- succeeds
diff --git a/contrib/require_where/require_where.c
b/contrib/require_where/require_where.c
new file mode 100644
index 000..09f2578
--- /dev/null
+++ b/contrib/require_where/require_where.c
@@ -0,0 +1,73 @@
+/*
+ * --
+ *
+ * require_where.c
+ *
+ * Copyright (C) 2017, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * contrib/require_where/require_where.c
+ *
+ * --
+ */
+#include "postgres.h"
+
+#include "fmgr.h"
+
+#include "parser/analyze.h"
+
+#include "utils/elog.h"
+#include "utils/guc.h"
+
+PG_MODULE_MAGIC;
+
+void _PG_init(void);
+void _PG_fini(void);
+
+static post_parse_analyze_hook_type original_post_parse_analyze_hook =
NULL;
+
+/*
+ * This module makes simple UPDATE and DELETE statements require a WHERE clause
+ * and complains when this is not present.
+ */
+static void
+require_where_check(ParseState *pstate, Query *query)
+{
+
+ if (query->commandType == CMD_DELETE)
+ {
+ /* Make sure there's something to look at. */
+ Assert(query->jointree != NULL);
+ if (query->jointree->quals == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+errm
Re: [HACKERS] PoC: Make it possible to disallow WHERE-less UPDATE and DELETE
On Tue, Jan 03, 2017 at 11:59:19AM +0100, Pavel Stehule wrote: > Hi > I am sending review of this patch > > 1. there are not any problem with patching, compiling, doc > 2. the patch is simple, the documentation is good enough > 3. all regress tests passed without problems > > My questions: > > 1. is a data file really necessary? No. I'll remove it. > 2. There is not documented a assert "Assert(query->jointree != NULL)" > > It is valid, but should be documented why? Will 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] PoC: Make it possible to disallow WHERE-less UPDATE and DELETE
On Sun, Jan 01, 2017 at 07:57:33PM +0900, Michael Paquier wrote: > On Sun, Jan 1, 2017 at 12:34 PM, David Fetter <da...@fetter.org> wrote: > > I've rolled your patches into this next one and clarified the commit > > message, as there appears to have been some confusion about the scope. > > Not all the comments have been considered! > > Here are some example.. > > =# set require_where.delete to on; > SET > =# copy (delete from aa returning a) to stdout; > ERROR: 42601: DELETE requires a WHERE clause when > require_where.delete is set to on > HINT: To delete all rows, use "WHERE true" or similar. > LOCATION: require_where_check, require_where.c:42 > > COPY with DML returning rows are correctly restricted. > > Now if I look at WITH clauses... as no one this patch was aimed at protecting will do... I get that there's something about this feature that introduces some oddnesses. This stage of it is not intended to be some kind of a general guard against anything. It's intended to put some safety measures in place for an extremely restricted subset of SQL which has caused real damage in real systems. I'd love it if everyone who ever touched a production system was wearing the entire parser, planner, and executor in their heads, but that's not the situation I'm trying to help with here. > =# with delete_query as (delete from aa returning a) select * from > delete_query; > a > --- > (0 rows) > =# with update_query as (update aa set a = 3 returning a) select * > from update_query; > a > --- > (0 rows) > Oops. That's not cool. Those are protections for a later feature, if feasible. I'm happy to clarify the documentation and error messages as to the scope. > CTAS also perform no checks: Again, not in scope. > Is there actually a meaning to have two options? Couldn't we leave > with just one? Actually, I'd just suggest to rip off the option and > just to make the checks enabled when the library is loaded, to get a > module as simple as passwordcheck. Excellent suggestion. I'll see about that in the next couple of days. > +++ b/contrib/require_where/data/test_require_where.data > @@ -0,0 +1,16 @@ > +Four > There is no need to load fake data as you need to just check the > parsing of the query. So let's simplify the tests and remove that. OK > Except that the shape of the module is not that bad. The copyright > notices need to be updated to 2017, and it would be nice to have some > comments at the top of require_where.c to describe what it does. Will fix. 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 Fri, Sep 30, 2016 at 04:23:13PM +1300, Thomas Munro wrote: > On Thu, Sep 29, 2016 at 6:19 PM, David Fetter <da...@fetter.org> wrote: > > On Thu, Sep 29, 2016 at 11:12:11AM +1300, Thomas Munro wrote: > >> On Mon, Sep 26, 2016 at 5:11 PM, Thomas Munro > >> <thomas.mu...@enterprisedb.com> wrote: > >> > On Mon, Sep 26, 2016 at 1:18 PM, Thomas Munro > >> > <thomas.mu...@enterprisedb.com> wrote: > >> >> > >> >> On Mon, Sep 19, 2016 at 4:02 PM, David Fetter <da...@fetter.org> wrote: > >> >> > > >> >> > [training_wheels_004.patch] > >> >> > >> >> [review] > >> > >> Ping. > > > > Please find attached the next revision. > > I'm not sold on ERRCODE_SYNTAX_ERROR. There's nothing wrong with the > syntax, since parsing succeeded. It would be cool if we could use > ERRCODE_E_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED, though I'm not sure > what error class 38 really means. Does require_where's hook function > count as an 'external routine' and on that basis is it it allowed to > raise this error? Thoughts, anyone? > > I am still seeing the underscore problem when building the docs. > Please find attached fix-docs.patch which applies on top of > training_wheels_005.patch. It fixes that problem for me. > > The regression test fails. The expected error messages show the old > wording, so I think you forgot to add a file. Also, should > contrib/require_where/Makefile define REGRESS = require_where? That > would allow 'make check' from inside that directory, which is > convenient and matches other extensions. Please find attached > fix-regression-test.patch which also applies on top of > training_wheels_005.patch and fixes those things for me, and also adds > a couple of extra test cases. > > Robert Haas mentioned upthread that the authors section was too short, > and your follow-up v3 patch addressed that. Somehow two authors got > lost on their way to the v5 patch. Please find attached > fix-authors.patch which also applies on top of > training_wheels_005.patch to restore them. > > It would be really nice to be able to set this to 'Ready for > Committer' in this CF. Do you want to post a v6 patch or are you > happy for me to ask a committer to look at v5 + these three > corrections? Thanks! I've rolled your patches into this next one and clarified the commit message, as there appears to have been some confusion about the scope. 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 diff --git a/contrib/Makefile b/contrib/Makefile index 25263c0..4bd456f 100644 --- a/contrib/Makefile +++ b/contrib/Makefile @@ -40,6 +40,7 @@ SUBDIRS = \ pgstattuple \ pg_visibility \ postgres_fdw\ + require_where \ seg \ spi \ tablefunc \ diff --git a/contrib/require_where/Makefile b/contrib/require_where/Makefile new file mode 100644 index 000..9c41691 --- /dev/null +++ b/contrib/require_where/Makefile @@ -0,0 +1,19 @@ +# contrib/require_where/Makefile + +MODULE_big = require_where +OBJS = require_where.o + +PGFILEDESC = 'require_where - require DELETE and/or UPDATE to have a WHERE clause' + +REGRESS = require_where + +ifdef USE_PGXS +PG_CONFIG = pg_config +PGXS = $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir = contrib/require_where +top_builddir = ../.. +include $(top_builddir)/src/Makefile.global +include $(top_builddir)/contrib/contrib-global.mk +endif diff --git a/contrib/require_where/data/test_require_where.data b/contrib/require_where/data/test_require_where.data new file mode 100644 index 000..d4a29d8 --- /dev/null +++ b/contrib/require_where/data/test_require_where.data @@ -0,0 +1,16 @@ +Four +score +and +seven +years +ago +our +fathers +brought +forth +on +this +continent +a +new +nation diff --git a/contrib/require_where/expected/require_where.out b/contrib/require_where/expected/require_where.out new file mode 100644 index 000..adfd358 --- /dev/null +++ b/contrib/require_where/expected/require_where.out @@ -0,0 +1,22 @@ +-- +-- Test require_where +-- +\set echo all +LOAD 'require_where'; +CREATE TABLE test_require_where(t TEXT); +\copy test_require_where from 'data/test_require_where.data' +UPDATE test_require_where SET t=t; -- succeeds +SET require_where.update TO ON; +UPDATE test_require_where SET t=t; -- fails +ERROR: UPDATE requires a WHERE clause when require_where.delete is set to on +HINT: To update
Re: [HACKERS] [PATCH] Rename pg_switch_xlog to pg_switch_wal
On Fri, Dec 30, 2016 at 09:57:25AM -0500, Stephen Frost wrote: > Let's make this a clean break/change. +1 If there are known management gizmos to notify, it'd be nice to try to give them some sort of warning, but even for them, the release notes should spell it out clearly. That business where people would delete files with "log" in the path, not infrequently on a system running at capacity, isn't just theoretical. I've seen people lose data permanently that way, and I know I'm not the only one who's seen this in the real world. 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] Hooks
On Wed, Dec 28, 2016 at 12:19:11PM +0800, Craig Ringer wrote: > On 28 December 2016 at 12:15, Jim Nasby <jim.na...@bluetreble.com> wrote: > > > Can we reduce the scope of this to a manageable starting point? > > I'm guessing that all existing hooks share certain characteristics > > that it'd be pretty easy to detect. If you can detect the hook > > (which I guess means finding a static variable with hook in the > > name) then you can verify that there's an appropriate comment > > block. I'm guessing someone familiar with tools like doxygen could > > set that up without too much effort, and I'd be surprised if the > > community had a problem with it. > > Lets just make sure the comment blocks are nice and grep-able too. > > I think this is a great idea FWIW. Discovering the extension points > within Pg isn't easy. > > Callbacks aren't easy to find either. Should callbacks be another chapter in the docs? 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] Hooks
On Wed, Dec 28, 2016 at 03:07:52PM +0900, Michael Paquier wrote: > On Wed, Dec 28, 2016 at 2:14 PM, David Fetter <da...@fetter.org> wrote: > > Here's everything that matches ^\w+_hook$ that I've found so far in > > git master. There are very likely false positives in this list. > > > > [... long list of hooks ...] > > > > Some appear to be client-side, some server-side. I hope that no hook > > is named other than that way, and I'll do my best to check. > > This list includes some of the hooks of psql used to assign a pset > variable. You had better just list the backend-side ones, or if you > want just those in src/backend/. Those are the ones that matter to > extension and plugin developers. Thanks for the pointer! 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] Hooks
On Tue, Dec 27, 2016 at 10:15:55PM -0600, Jim Nasby wrote: > On 12/27/16 7:41 PM, David Fetter wrote: > > I see it as larger in scope than mine because it changes how we do > > things as a project. An example of the kind of thing that this raises > > is enforcement. Will something (or someone) check that new hooks have > > this? Will somebody check for comment skew when the APIs change? > > What happens when somebody forgets? > > Can we reduce the scope of this to a manageable starting point? That is what I'm trying to do. > I'm guessing that all existing hooks share certain characteristics > that it'd be pretty easy to detect. If you can detect the hook > (which I guess means finding a static variable with hook in the > name) then you can verify that there's an appropriate comment block. > I'm guessing someone familiar with tools like doxygen could set that > up without too much effort, and I'd be surprised if the community > had a problem with it. Sure, but that seems like an effort somewhat orthogonal to the one I proposed, which is to get some user-facing i.e. SGML docs up for the current hooks. Here's everything that matches ^\w+_hook$ that I've found so far in git master. There are very likely false positives in this list. ClientAuthentication_hook ExecutorCheckPerms_hook ExecutorEnd_hook ExecutorFinish_hook ExecutorRun_hook ExecutorStart_hook ExplainOneQuery_hook ProcessUtility_hook assign_hook autocommit_hook call_bool_check_hook call_enum_check_hook call_int_check_hook call_real_check_hook call_string_check_hook check_hook check_password_hook comp_keyword_case_hook create_upper_paths_hook echo_hidden_hook echo_hook emit_log_hook explain_get_index_name_hook fetch_count_hook fixed_paramref_hook fmgr_hook get_attavgwidth_hook get_index_stats_hook get_relation_info_hook get_relation_stats_hook histcontrol_hook join_search_hook needs_fmgr_hook next_client_auth_hook next_exec_check_perms_hook object_access_hook on_error_rollback_hook on_error_stop_hook original_client_auth_hook original_post_parse_analyze_hook p_coerce_param_hook p_paramref_hook p_post_columnref_hook p_pre_columnref_hook pgstat_beshutdown_hook planner_hook plpgsql_extra_checks_check_hook plpgsql_extra_errors_assign_hook plpgsql_extra_warnings_assign_hook post_parse_analyze_hook prev_post_parse_analyze_hook prompt1_hook prompt2_hook prompt3_hook quiet_hook sepgsql_audit_hook set_join_pathlist_hook set_rel_pathlist_hook shmem_startup_hook show_context_hook show_hook singleline_hook singlestep_hook variable_coerce_param_hook variable_paramref_hook verbosity_hook Some appear to be client-side, some server-side. I hope that no hook is named other than that way, and I'll do my best to check. 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] Hooks
On Wed, Dec 28, 2016 at 01:33:13AM +, Tsunakawa, Takayuki wrote: > From: David Fetter [mailto:da...@fetter.org] > > > How about putting a descriptive comment at the location where each > > > hook variable is defined, using some convention (e.g. like > > > Javadoc-style)? A separate document such as README and wiki can fail > > > to be updated. OTOH, if someone wants to add a new hook, we can > > > expect him to add appropriate comment by following existing hooks. > > > Using a fixed tag, e.g. "", would facilitate finding all hooks. > > > > I like this idea, but it's a much bigger one than mine because it's > > essentially inventing (or adopting, whatever we settle on) literate > > programming for the PostgreSQL project. > > > > https://en.wikipedia.org/wiki/Literate_programming > > I didn't intend to invent a new heavy rule or tool. I just meant comments > just like the existing function descriptions, something like > > /* > * Hook name: Authentication hook > * Description: ... > * Arguments: ... > * Return value: ... > * Note: ... > */ It's still new. For the record, I think it's an excellent idea. I see it as larger in scope than mine because it changes how we do things as a project. An example of the kind of thing that this raises is enforcement. Will something (or someone) check that new hooks have this? Will somebody check for comment skew when the APIs change? What happens when somebody forgets? > > At the moment, our practice is that (most--hooks being an > > exception) user-facing features must come with with user-facing > > docs which are written separately from the source code > > implementing them. > > OK. Anyway, if we can see in the PostgreSQL documentation what > hooks are available, it would be the best. I imagine you meant > adding a new chapter under the part "V. Server Programming". Exactly :) 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] Hooks
On Wed, Dec 28, 2016 at 12:47:10AM +, Tsunakawa, Takayuki wrote: > From: pgsql-hackers-ow...@postgresql.org > > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Jim Nasby > > AFAIK there's no way to get a list of hooks today, short of > > something like `git grep hook`. I think a simple list of what > > hooks we have, when they fire and where to find them in code would > > be sufficient. > > How about putting a descriptive comment at the location where each > hook variable is defined, using some convention (e.g. like > Javadoc-style)? A separate document such as README and wiki can > fail to be updated. OTOH, if someone wants to add a new hook, we > can expect him to add appropriate comment by following existing > hooks. Using a fixed tag, e.g. "", would facilitate finding > all hooks. I like this idea, but it's a much bigger one than mine because it's essentially inventing (or adopting, whatever we settle on) literate programming for the PostgreSQL project. https://en.wikipedia.org/wiki/Literate_programming In the realm of generated documentation, we do have a doxygen https://doxygen.postgresql.org/ for the project, but I haven't really found it helpful thus far. Let's take up literate programming in a separate thread. At the moment, our practice is that (most--hooks being an exception) user-facing features must come with with user-facing docs which are written separately from the source code implementing them. 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] Hooks
On Tue, Dec 27, 2016 at 01:32:46PM -0500, Tom Lane wrote: > David Fetter <da...@fetter.org> writes: > > One of our hidden treasures is the hook system, documented only in > > random presentations, if you can find them, and in the source code, if > > you know to look. > > > I'd like to document the hooks that we consider public APIs. > > The main reason we send people to the source code for that is that > it's often not very clear what the extent of a hook's API is. It would > not be terribly useful to document, say, planner_hook just by listing > its arguments and result type. Indeed it wouldn't, but there are hooks where the API is, at least to me, a little clearer, and I'll start with those. > To do anything useful with that hook requires a pretty extensive > understanding of what the planner does, and you're not going to get > that without a willingness to read source. I would assume willingness to read source for anything hook related. Documenting the hooks will help with places to look. > So I'm a bit suspicious of this project in the first place, but it's > hard to discuss which hooks should be documented when you haven't > defined what you mean by documentation. I haven't quite come up with that, but I'd pictured a part of the SGML docs that goes over, at a minimum, what all the hooks are and what they do, at least at the level of a sentence or paragraph's worth of description. > Anyway, there aren't any hooks that weren't put in with the expectation > of third-party code using them, so I'm not following your proposed > distinction between public and private hooks. That answers the question I had, which is essentially, "are there any private hooks?" and the answer is no. 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] Hooks
Folks, One of our hidden treasures is the hook system, documented only in random presentations, if you can find them, and in the source code, if you know to look. I'd like to document the hooks that we consider public APIs. To do this, I need to figure out whether there are hooks that we don't consider public APIs, ideally in some principled way. C doesn't have affordances built in for this, but maybe we've done something else to indicate which are implementation details and which are public APIs. Are there any hooks I should not document? If so, how will I tell in the future that a new hook shouldn't be documented? 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] pg_background contrib module proposal
On Wed, Dec 21, 2016 at 10:42:18AM -0500, Robert Haas wrote: > On Wed, Dec 21, 2016 at 10:29 AM, David Fetter <da...@fetter.org> wrote: > > On Wed, Dec 21, 2016 at 06:31:52PM +0800, Craig Ringer wrote: > >> On 21 December 2016 at 14:26, Andrew Borodin <boro...@octonica.com> wrote: > >> > >> > I'm not sure every platform supports microsecond sleeps > >> > >> Windows at least doesn't by default, unless that changed in Win2k12 > >> and Win8 with the same platform/kernel improvements that delivered > >> https://msdn.microsoft.com/en-us/library/hh706895(v=vs.85).aspx . I'm > >> not sure. On older systems sleeps are 1ms to 15ms. > > > > Apparently, as of 2011, there were ways to do this. It's not crystal > > clear to me just how reliable they are. > > > > http://stackoverflow.com/questions/9116618/cpp-windows-is-there-a-sleep-function-in-microseconds > > This whole subthread seems like a distraction to me. I find it hard > to believe that this test case would be stable enough to survive the > buildfarm where, don't forget, we have things like > CLOBBER_CACHE_ALWAYS machines where queries take 100x longer to run. > But even if it is, surely we can pick a less contrived test case. > So why worry about this? I wasn't super worried about the actual sleep times, but I was having trouble puzzling out what the test was actually doing, so I rewrote it with what I thought of as more clarity. 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] pg_background contrib module proposal
On Wed, Dec 21, 2016 at 06:31:52PM +0800, Craig Ringer wrote: > On 21 December 2016 at 14:26, Andrew Borodin <boro...@octonica.com> wrote: > > > I'm not sure every platform supports microsecond sleeps > > Windows at least doesn't by default, unless that changed in Win2k12 > and Win8 with the same platform/kernel improvements that delivered > https://msdn.microsoft.com/en-us/library/hh706895(v=vs.85).aspx . I'm > not sure. On older systems sleeps are 1ms to 15ms. Apparently, as of 2011, there were ways to do this. It's not crystal clear to me just how reliable they are. http://stackoverflow.com/questions/9116618/cpp-windows-is-there-a-sleep-function-in-microseconds 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] pg_background contrib module proposal
On Mon, Dec 19, 2016 at 09:30:32PM +0500, Andrew Borodin wrote: > 2016-12-19 4:21 GMT+05:00 David Fetter <da...@fetter.org>: > > Couldn't it sleep in increments smaller than a second? Like maybe > > milliseconds? Also, it's probably cleaner (or at least more > > comprehensible) to write something using format() and dollar quoting > > than the line with the double 's. > > Right. Here's version which waits for half a second. I do not see how > to path doubles via dollar sign, pg_background_launch() gets a string > as a parameter, it's not EXCEUTE USING. I see. I find the following a little easier to follow, and the sleeps still work even when very short. Best, David. CREATE TABLE input AS SELECT x, row_number() OVER (ORDER BY x) n FROM generate_series(0,.05,0.01) x ORDER BY x DESC; CREATE TABLE output(place int,value float); CREATE TABLE handles AS SELECT pg_background_launch( format($$ SELECT pg_sleep(%s); INSERT INTO output VALUES (%s, %s) $$, x, n, x ) ) h FROM input; --wait until everyone finishes SELECT * FROM handles JOIN LATERAL pg_background_result(handles.h) AS (x TEXT) ON (true); --output results SELECT * FROM output ORDER BY place; -- 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: pg_authid.rolpassword format (was Re: [HACKERS] Password identifiers, protocol aging and SCRAM protocol)
On Tue, Dec 20, 2016 at 06:14:40PM -0500, Stephen Frost wrote: > David, > > * David Fetter (da...@fetter.org) wrote: > > On Tue, Dec 20, 2016 at 08:34:19AM -0500, Stephen Frost wrote: > > > * Heikki Linnakangas (hlinn...@iki.fi) wrote: > > > > Even if you have a separate "verifier type" column, it's not fully > > > > normalized, because there's still a dependency between the > > > > verifier and verifier type columns. You will always need to look > > > > at the verifier type to make sense of the verifier itself. > > > > > > That's true- but you don't need to look at the verifier, or even > > > have *access* to the verifier, to look at the verifier type. > > > > Would a view that shows only what's to the left of the first semicolon > > suit this purpose? > > Obviously a (security barrier...) view or a (security definer) function > could be used, but I don't believe either is actually a good idea. Would you be so kind as to help me understand what's wrong with that idea? 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] Clarifying "server starting" messaging in pg_ctl start without --wait
On Tue, Dec 20, 2016 at 03:43:11PM -0500, Peter Eisentraut wrote: > On 12/20/16 3:31 PM, Ryan Murphy wrote: > > I'm concerned some new users may not understand this behavior of pg_ctl, > > so I wanted to suggest that we add some additional messaging after > > "server starting" - something like: > > > > $ pg_ctl -D datadir -l logfile start > > server starting > > (to wait for confirmation that server actually started, try pg_ctl again > > with --wait) > > Maybe the fix is to make --wait the default? +1 It's not super useful to have the prompt return while the server is still starting up. 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] pg_background contrib module proposal
On Tue, Dec 20, 2016 at 11:11:36AM +0530, amul sul wrote:
> On Tue, Dec 20, 2016 at 12:21 AM, David Fetter <da...@fetter.org> wrote:
> > On Thu, Nov 24, 2016 at 09:16:53AM +0530, amul sul wrote:
> >> Hi All,
> >>
> >> I would like to take over pg_background patch and repost for
> >> discussion and review.
> >
> > This looks great.
> >
> > Sadly, it now breaks initdb when applied atop
> > dd728826c538f000220af98de025c00114ad0631 with:
> >
> > performing post-bootstrap initialization ... TRAP:
> > FailedAssertion("!(const Node*)(rinfo))->type) == T_RestrictInfo))",
> > File: "costsize.c", Line: 660)
> > sh: line 1: 2840 Aborted (core dumped)
> > "/home/shackle/pggit/postgresql/tmp_install/home/shackle/10/bin/postgres"
> > --single -F -O -j -c search_path=pg_catalog -c exit_on_error=true template1
> > > /dev/null
> >
>
> I've complied binary with --enable-cassert flag and pg_background
> patch to the top of described commit as well as on latest HEAD, but I
> am not able to reproduce this crash, see this:
>
> [VM postgresql]$ /home/amul/Public/pg_inst/pg-master/bin/postgres
> --single -F -O -j -c search_path=pg_catalog -c exit_on_error=true
> template1
I haven't managed to reproduce it either. Sorry about the noise.
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: pg_authid.rolpassword format (was Re: [HACKERS] Password identifiers, protocol aging and SCRAM protocol)
On Tue, Dec 20, 2016 at 08:34:19AM -0500, Stephen Frost wrote: > Heikki, > > * Heikki Linnakangas (hlinn...@iki.fi) wrote: > > Even if you have a separate "verifier type" column, it's not fully > > normalized, because there's still a dependency between the > > verifier and verifier type columns. You will always need to look > > at the verifier type to make sense of the verifier itself. > > That's true- but you don't need to look at the verifier, or even > have *access* to the verifier, to look at the verifier type. Would a view that shows only what's to the left of the first semicolon suit this purpose? 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] pg_background contrib module proposal
On Thu, Nov 24, 2016 at 09:16:53AM +0530, amul sul wrote:
> Hi All,
>
> I would like to take over pg_background patch and repost for
> discussion and review.
This looks great.
Sadly, it now breaks initdb when applied atop
dd728826c538f000220af98de025c00114ad0631 with:
performing post-bootstrap initialization ... TRAP: FailedAssertion("!(const
Node*)(rinfo))->type) == T_RestrictInfo))", File: "costsize.c", Line: 660)
sh: line 1: 2840 Aborted (core dumped)
"/home/shackle/pggit/postgresql/tmp_install/home/shackle/10/bin/postgres"
--single -F -O -j -c search_path=pg_catalog -c exit_on_error=true template1 >
/dev/null
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] pg_background contrib module proposal
On Mon, Dec 12, 2016 at 10:17:24PM +0500, Andrew Borodin wrote: > Hi! > > Just in case you'd like to include sleepsort as a test, here it is > wrapped as a regression test(see attachment). But it has serious > downside: it runs no less than 5 seconds. Couldn't it sleep in increments smaller than a second? Like maybe milliseconds? Also, it's probably cleaner (or at least more comprehensible) to write something using format() and dollar quoting than the line with the double 's. 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] Speedup twophase transactions
On Sat, Dec 17, 2016 at 05:54:04PM -0500, Bruce Momjian wrote: > On Sun, Dec 18, 2016 at 07:41:50AM +0900, Michael Paquier wrote: > > On Sun, Dec 18, 2016 at 6:42 AM, Bruce Momjian <br...@momjian.us> wrote: > > > Uh, did you mean to attached patch here? > > > > Strange. I can confirm that I have received the patch as attached, but > > it is not on the archives. > > It must have been stripped by our email system. You were a direct > CC so you received it. I was neither, and I received it, so I don't thing PostgreSQL's email system stripped it. It's pretty mystifying, though. 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 vs. sql_inheritance
On Fri, Dec 16, 2016 at 11:05:21AM -0500, Robert Haas wrote: > On Thu, Dec 15, 2016 at 10:40 AM, Dmitry Ivanov <d.iva...@postgrespro.ru> > wrote: > > Hi everyone, > > > > Looks like "sql_inheritance" GUC is affecting partitioned tables: > > > > [breaks literally everything] > > > > I might be wrong, but IMO this should not happen. Queries involving update, > > delete etc on partitioned tables are basically broken. Moreover, there's no > > point in performing such operations on a parent table that's supposed to be > > empty at all times. > > An earlier version of Amit's patches tried to handle this by forcing > sql_inheritance on for partitioned tables, but it wasn't > well-implemented and I don't see the point anyway. Sure, turning > off sql_inheritance off for partitioned tables produces stupid > results. But turning off sql_inheritance for inheritance > hierarchies also produces stupid results. If we were going to do > anything about this, my vote would be to remove sql_inheritance. +1 It occurs to me this probably isn't the only GUC that's basically just a foot gun at this point. Is 10 a good time to sweep and clear them? 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] Temporal query processing with range types
On Wed, Dec 07, 2016 at 03:57:33PM +0100, Peter Moser wrote: > Am 05.12.2016 um 06:11 schrieb Haribabu Kommi: > > > > > > On Tue, Oct 25, 2016 at 8:44 PM, Peter Moser <pitiz...@gmail.com > > <mailto:pitiz...@gmail.com>> wrote: > > > > > > We decided to follow your recommendation and add the patch to the > > commitfest. > > > > > > Path is not applying properly to HEAD. > > Moved to next CF with "waiting on author" status. > > > > We updated our patch. We tested it with the latest > commit dfe530a09226a9de80f2b4c3d5f667bf51481c49. This looks neat, but it no longer applies to master. Is a rebase in the offing? 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] Transaction oddity with list partition of a list partition
On Thu, Dec 15, 2016 at 06:20:04PM +0900, Amit Langote wrote: > > Hi David, > > On 2016/12/15 18:09, David Fetter wrote: > > Per Thomas Munro, could it be that the CREATE ... PARTITION OF ... > > code fails to run CacheInvalidateRelcache on its parent(s)? > > Thomas's right. There is a patch posted for this issue [1]; I'm > sending an updated version of the patch later today in reply to [1]. > Meanwhile, could you try and see if the problem is fixed with the > attached patch. That fixed both cases. Thanks! 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] Transaction oddity with list partition of a list partition
On Thu, Dec 15, 2016 at 12:23:24AM -0800, David Fetter wrote:
> Folks,
>
> I'm having some trouble understanding what's going on here. When I \i
> the file in 55caaaeba877eac1feb6481fb413fa04ae9046ac without starting
> a transaction explicitly, it produces the expected results. When I \i
> it after a BEGIN, not so much.
I've managed to get a shorter repro for the issue:
BEGIN;
CREATE TABLE the_log (
ts TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
"user" TEXT NOT NULL DEFAULT current_user,
action TEXT NOT NULL,
table_schema TEXT NOT NULL,
table_name TEXT NOT NULL,
old_row JSONB,
new_row JSONB,
CHECK(
CASE action
WHEN 'INSERT' THEN old_row IS NULL AND new_row IS NOT NULL
WHEN 'UPDATE' THEN old_row IS NOT NULL AND new_row IS NOT NULL
ELSE /*DELETE, and maybe TRUNCATE, if that's supported by access to
old rows */
old_row IS NOT NULL AND new_row IS NULL
END
)
) PARTITION BY LIST(table_schema);
CREATE TABLE public_log
PARTITION OF the_log FOR VALUES IN ('public');
INSERT INTO the_log (action, table_schema, table_name, new_row)
VALUES ('INSERT','public','city','{"name": "Oakland", "population": 419267}');
leads to:
ERROR: no partition of relation "the_log" found for row
DETAIL: Failing row contains (2016-12-15 00:59:17.980094-08, shackle, INSERT,
public, city, null, {"name": "Oakland", "population": 419267}).
Per Thomas Munro, could it be that the CREATE ... PARTITION OF ... code
fails to run CacheInvalidateRelcache on its parent(s)?
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] Transaction oddity with list partition of a list partition
Folks,
I'm having some trouble understanding what's going on here. When I \i
the file in 55caaaeba877eac1feb6481fb413fa04ae9046ac without starting
a transaction explicitly, it produces the expected results. When I \i
it after a BEGIN, not so much.
What's going on?
Best,
David.
shackle@shackle=# BEGIN;
BEGIN
shackle@shackle=# \i ten_plus.sql
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE FUNCTION
CREATE TABLE
CREATE TRIGGER
psql:ten_plus.sql:66: ERROR: no partition of relation "the_log" found for row
DETAIL: Failing row contains (2016-12-15 00:17:46.579357-08, shackle, INSERT,
public, city, null, {"id": 1, "name": "Oakland", "population": 419267}).
CONTEXT: SQL statement "INSERT INTO the_log(
action,
table_schema,
table_name,
old_row,
new_row)
VALUES (
TG_OP,
TG_TABLE_SCHEMA,
TG_TABLE_NAME,
CASE TG_OP WHEN 'INSERT' THEN NULL ELSE row_to_json(OLD)::jsonb END,
CASE TG_OP WHEN 'DELETE' THEN NULL ELSE row_to_json(NEW)::jsonb END
)"
PL/pgSQL function log_change() line 3 at SQL statement
shackle@shackle=# ROLLBACK;
--
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
ten_plus.sql
Description: application/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] tzdata 2016j
On Tue, Dec 06, 2016 at 10:52:47AM -0500, Tom Lane wrote: > Vladimir Borodin <r...@simply.name> writes: > > Any chance to get tzdata 2016j in supported branches? > > When the next scheduled releases come around (February), we'll update > to whatever tzdata is current at that time. I'm guessing that request came through because Vladimir is actually affected by the change. Apparently, you can replace the tzdata file and restart the server per https://wiki.postgresql.org/wiki/FAQ#Will_PostgreSQL_handle_recent_daylight_saving_time_changes_in_various_countries.3F 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] Separate connection handling from backends
On Mon, Dec 05, 2016 at 01:48:03PM -0600, Jim Nasby wrote: > max_connections is a frequent point of contention between users and > developers. Users want to set it high so they don't have to deal with Yet > More Software (pgpool or pgBouncer); PG developers freak out because > backends are pretty heavyweight, there's some very hot code that's sensitive > to the size of ProcArray, lock contention, etc. > > One solution to this would be to segregate connection handling from actual > backends, somewhere along the lines of separating the main loop from the > switch() that handles libpq commands. Benefits: [interesting stuff elided] What do you see as the relationship between this proposal and the earlier one for admission control? https://www.postgresql.org/message-id/4b38c1c502250002d...@gw.wicourts.gov 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] PSQL commands: \quit_if, \quit_unless
On Tue, Nov 29, 2016 at 01:10:06PM +0100, Fabien COELHO wrote: > If there is a negative condition syntax, I would slightly prefer \ifnot to > \if_not or worse \unless. I would disaprove strongly of \unless because it > looses the clear symmetry with a closing \fi. I take it \sselnu is right out. 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] Tackling JsonPath support
On Sun, Nov 27, 2016 at 11:50:30AM -0500, Christian Convey wrote: > >From looking at other databases' docs, it seems like the behavior of > various JSON-related operators / functions are described partially in terms > of a "json path expression": > > * In Oracle, "JSON_TABLE", "JSON_exists_column", "JSON_value_column": [1] > * In MySQL: [2] > * In DB2: [3] > * In MS SQL Server: [4] > * (Whatever the Standards committee will end up producing.) There's another option we should also consider: jq <https://stedolan.github.io/jq/>. It's available under a PostgreSQL-compatible license, and has had a LOT of work put into correctness and performance. 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] ALTER DEFAULT PRIVILEGES with GRANT/REVOKE ON SCHEMAS
On Tue, Nov 22, 2016 at 08:59:09AM -0200, Matheus de Oliveira wrote:
> Hi all,
>
> I noticed that we have no option to set default privileges for newly
> created schemas, other than calling GRANT explicitly. At work I use ALTER
> DEFAULT PRIVILEGE (ADP) command extensively, as the developers are
> permitted to manage DDL on the databases, and all work fine except for when
> a new schema is created. So,I'd like to propose this very simple patch
> (attached) that adds the capability of using SCHEMAS, adding the following
> syntax to ADP:
>
> ALTER DEFAULT PRIVILEGES
> [ FOR { ROLE | USER } target_role [, ...] ]
> abbreviated_grant_or_revoke
>
> where abbreviated_grant_or_revoke is one of:
>
> GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] }
> ON SCHEMAS
> TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
>
> REVOKE [ GRANT OPTION FOR ]
> { USAGE | CREATE | ALL [ PRIVILEGES ] }
> ON SCHEMAS
> FROM { [ GROUP ] role_name | PUBLIC } [, ...]
> [ CASCADE | RESTRICT ]
I'd love to have this available.
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] pg_config --version
On Sun, Nov 27, 2016 at 09:12:47AM -0600, Jim Nasby wrote: > On 11/27/16 12:16 AM, Michael Paquier wrote: > > date: Thu, 2 Jul 2015 17:24:36 -0400 > > Make numeric form of PG version number readily available in Makefiles. > > If you don't want to wait for that, I wonder whether a back-patch to 9.2 with that would be accepted... > you can use [1] in shell I'd like to make as few assumptions about build environments as I can. I know for sure that people are building my things on platforms like Windows that I have no way to test, but don't wish to break. > or Make to accomplish something similar. Looks like there is a > dotted MAJORVERSION variable going back to at least 9.1, Good to know. I guess I can rely on there being a GNU make...at least for now. > so I could have used that instead of PGVERSION on line 51. > 1: https://github.com/decibel/pgxntool/blob/master/base.mk#L47-L56 Thanks for sharing :) 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] pg_config --version
On Sun, Nov 27, 2016 at 03:16:37PM +0900, Michael Paquier wrote: > On Sun, Nov 27, 2016 at 9:16 AM, David Fetter <da...@fetter.org> wrote: > > While updating some extensions, I noticed that pg_config --version > > produces output that's...maybe not quite as useful as it might be, at > > least to a machine, so I'd like to throw out some proposals to fix the > > situation. > > > > Add a --version-numeric option to pg_config > > > > or > > Replace the current --version option with its bare numeric version > > > > or > > Add another line of output to the current --version output, which > > would be the numeric version by itself > > > > What say? > > You may want to look at this thread that treats more or less the same topic: > https://www.postgresql.org/message-id/cab7npqtadajpx8ik4v3uyjbo2kmo8rhzqjkdsladdrannrg...@mail.gmail.com > And this has resulted in commit a5d489cc: > commit: a5d489ccb7e613c7ca3be6141092b8c1d2c13fa7 > author: Tom Lane <t...@sss.pgh.pa.us> > date: Thu, 2 Jul 2015 17:24:36 -0400 > Make numeric form of PG version number readily available in Makefiles. > > I would imagine that the common position has not changed much since, > and as Makefile.global.in provides this data... Doing more work in > pg_config is not really necessary for extensions. Thanks for the tip. :) Since at least two people ran across this independently, perhaps more documentation is in order. 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] pg_config --version
Folks, While updating some extensions, I noticed that pg_config --version produces output that's...maybe not quite as useful as it might be, at least to a machine, so I'd like to throw out some proposals to fix the situation. Add a --version-numeric option to pg_config or Replace the current --version option with its bare numeric version or Add another line of output to the current --version output, which would be the numeric version by itself I'm partial to the first because it's clear what's being asked for, the second because the product name does nothing for comprehension, and the third because it would be less strain on things that already parse the output. A somewhat larger project, not sure whether it's worth doing, would be to enable pg_config to print arbitrary combinations of the GUCs it could know about. What say? 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] Radix tree for character conversion
On Fri, Oct 28, 2016 at 09:18:08AM -0400, Robert Haas wrote: > On Thu, Oct 27, 2016 at 3:23 AM, Kyotaro HORIGUCHI > <horiguchi.kyot...@lab.ntt.co.jp> wrote: > > | COPYRIGHT AND PERMISSION NOTICE > > | > > | Copyright (c) 1991-2016 Unicode, Inc. All rights reserved. > > | Distributed under the Terms of Use in > > http://www.unicode.org/copyright.html. > > | > > | Permission is hereby granted, free of charge, to any person obtaining > > | a copy of the Unicode data files and any associated documentation > > | (the "Data Files") or Unicode software and any associated documentation > > | (the "Software") to deal in the Data Files or Software > > | without restriction, including without limitation the rights to use, > > | copy, modify, merge, publish, distribute, and/or sell copies of > > | the Data Files or Software, and to permit persons to whom the Data Files > > | or Software are furnished to do so, provided that either > > | (a) this copyright and permission notice appear with all copies > > | of the Data Files or Software, or > > | (b) this copyright and permission notice appear in associated > > | Documentation. > > > > Perhaps we can put the files into our repositoy by providing some > > notifications. > > Uggh, I don't much like advertising clauses. Your dislike is pretty common. Might it be worth reaching out to the Unicode consortium about this? They may well have added that as boilerplate without really considering the effects, and they even have a popup that specifically addresses licensing. http://www.unicode.org/reporting.html Best, David. -- David Fetter <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 of pg_xlog and pg_clog
On Thu, Oct 20, 2016 at 02:23:32PM -0400, Tom Lane wrote: > Robert Haas <robertmh...@gmail.com> writes: > > On Thu, Oct 20, 2016 at 2:09 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > >> We have the two precedents "pg_subtrans" and "pg_multixact", so > >> unless we want to get into renaming those too, I think "pg_trans" > >> and "pg_xact" are really the only options worth considering. > >> > >> Personally I'd go for "pg_trans", but it's only a weak preference. > > > Heaven forfend we actually use enough characters to make it > > self-documenting. > > $ ls $PGDATA > PG_VERSION pg_dynshmem/ pg_notify/ pg_stat_tmp/ > postgresql.auto.conf > base/ pg_hba.confpg_replslot/ pg_subtrans/ postgresql.conf > global/pg_ident.conf pg_serial/ pg_tblspc/postmaster.opts > pg_clog/ pg_logical/pg_snapshots/ pg_twophase/ postmaster.pid > pg_commit_ts/ pg_multixact/ pg_stat/ pg_wal/ > > I don't see one single one of those subdirectory names that I'd call > self-documenting. That's a problem we should do something about, even if we can't do it by renaming these all in one go. At the very least, we can do this for any new names. > Are you proposing we rename them all with carpal- > tunnel-syndrome-promoting names? Are you saying that people are getting carpal tunnel syndrome from hitting the tab key, which has been standard for completion in shells for decades? I'm pretty sure that doesn't actually happen. > There's certainly some case to be made for renaming at least one of > "pg_subtrans" and "pg_multixact" so that these three similarly-purposed > subdirectories can all have similar names. But I think on the whole > that's (a) fixing what ain't broken, and (b) making it even more unlikely > that we'll ever get to consensus on changing anything. We've managed to > agree that we need to change the names ending in "log"; let's do that > and be happy that we've removed one foot-gun from the system. Removing foot guns, un-sexy as it may be from a developer's perspective, is very useful work. Best, David. -- David Fetter <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 Fri, Sep 30, 2016 at 06:37:17PM +0200, Julien Rouhaud wrote: > On 30/09/2016 05:23, Thomas Munro wrote: > > > > It would be really nice to be able to set this to 'Ready for > > Committer' in this CF. Do you want to post a v6 patch or are you > > happy for me to ask a committer to look at v5 + these three > > corrections? > > I just looked at the patch, and noticed that only plain DELETE and > UPDATE commands are handled. Is it intended that writable CTE without > WHERE clauses are not detected by this extension? I personally think > that wCTE should be handled (everyone can forget a WHERE clause), but if > not it should at least be documented. You are correct in that it should work for every unqualified UPDATE or DELETE, not just some. Would you be so kind as to send along the tests cases you used so I can add them to the patch? Best, David. -- David Fetter <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, Sep 29, 2016 at 11:12:11AM +1300, Thomas Munro wrote:
> On Mon, Sep 26, 2016 at 5:11 PM, Thomas Munro
> <thomas.mu...@enterprisedb.com> wrote:
> > On Mon, Sep 26, 2016 at 1:18 PM, Thomas Munro
> > <thomas.mu...@enterprisedb.com> wrote:
> >>
> >> On Mon, Sep 19, 2016 at 4:02 PM, David Fetter <da...@fetter.org> wrote:
> >> >
> >> > [training_wheels_004.patch]
> >>
> >> [review]
>
> Ping.
Please find attached the next revision.
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
diff --git a/contrib/Makefile b/contrib/Makefile
index 25263c0..4bd456f 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -40,6 +40,7 @@ SUBDIRS = \
pgstattuple \
pg_visibility \
postgres_fdw\
+ require_where \
seg \
spi \
tablefunc \
diff --git a/contrib/require_where/Makefile b/contrib/require_where/Makefile
new file mode 100644
index 000..0cf3663
--- /dev/null
+++ b/contrib/require_where/Makefile
@@ -0,0 +1,17 @@
+# contrib/require_where/Makefile
+
+MODULE_big = require_where
+OBJS = require_where.o
+
+PGFILEDESC = 'require_where - require DELETE and/or UPDATE to have a WHERE
clause'
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS = $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/require_where
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_builddir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/require_where/data/test_require_where.data
b/contrib/require_where/data/test_require_where.data
new file mode 100644
index 000..d4a29d8
--- /dev/null
+++ b/contrib/require_where/data/test_require_where.data
@@ -0,0 +1,16 @@
+Four
+score
+and
+seven
+years
+ago
+our
+fathers
+brought
+forth
+on
+this
+continent
+a
+new
+nation
diff --git a/contrib/require_where/expected/require_where.out
b/contrib/require_where/expected/require_where.out
new file mode 100644
index 000..0876e13
--- /dev/null
+++ b/contrib/require_where/expected/require_where.out
@@ -0,0 +1,12 @@
+LOAD
+CREATE TABLE
+COPY 16
+UPDATE 16
+SET
+psql:sql/require_where.sql:17: ERROR: UPDATE requires a WHERE clause
+HINT: To update all rows, use "WHERE true" or similar.
+SET
+psql:sql/require_where.sql:21: ERROR: DELETE requires a WHERE clause
+HINT: To delete all rows, use "WHERE true" or similar.
+SET
+DELETE 16
diff --git a/contrib/require_where/require_where.c
b/contrib/require_where/require_where.c
new file mode 100644
index 000..27cbc25
--- /dev/null
+++ b/contrib/require_where/require_where.c
@@ -0,0 +1,92 @@
+/*
+ * --
+ *
+ * require_where.c
+ *
+ * Copyright (C) 2016, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * contrib/require_where/require_where.c
+ *
+ * --
+ */
+#include "postgres.h"
+
+#include "fmgr.h"
+
+#include "parser/analyze.h"
+
+#include "utils/elog.h"
+#include "utils/guc.h"
+
+PG_MODULE_MAGIC;
+
+void _PG_init(void);
+void _PG_fini(void);
+
+static post_parse_analyze_hook_type original_post_parse_analyze_hook =
NULL;
+static boolrequire_where_delete = false;
+static boolrequire_where_update = false;
+
+static void
+require_where_check(ParseState *pstate, Query *query)
+{
+
+ if (require_where_delete && query->commandType == CMD_DELETE)
+ {
+ Assert(query->jointree != NULL);
+ if (query->jointree->quals == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+errmsg("DELETE requires a WHERE clause
when require_where.delete is set to on"),
+errhint("To delete all rows, use
\"WHERE true\" or similar.")));
+ }
+
+ if (require_where_update && query->commandType == CMD_UPDATE)
+ {
+ Assert(query->jointree != NULL);
+ if (query->jointree->quals == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+errmsg("UPDATE requires a WHERE clause
when require_where.delete is set to on"),
+errhint("To update all
Re: [HACKERS] PoC: Make it possible to disallow WHERE-less UPDATE and DELETE
On Thu, Sep 29, 2016 at 11:12:11AM +1300, Thomas Munro wrote: > On Mon, Sep 26, 2016 at 5:11 PM, Thomas Munro > <thomas.mu...@enterprisedb.com> wrote: > > On Mon, Sep 26, 2016 at 1:18 PM, Thomas Munro > > <thomas.mu...@enterprisedb.com> wrote: > >> > >> On Mon, Sep 19, 2016 at 4:02 PM, David Fetter <da...@fetter.org> wrote: > >> > > >> > [training_wheels_004.patch] > >> > >> [review] > > Ping. I'll have another revision out as soon as I get some more test cases. Best, David. -- David Fetter <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 SQL counter statistics view (pg_stat_sql)
On Wed, Sep 21, 2016 at 02:05:24PM -0300, Alvaro Herrera wrote: > Another consideration is that the present patch lumps together all > ALTER cases in a single counter. This isn't great, but at the same > time we don't want to bloat the stat files by having hundreds of > counters per database, do we? I count 37 documented versions of ALTER as of git master. Is there some multiplier I'm missing? Best, David. -- David Fetter <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 SQL counter statistics view (pg_stat_sql)
On Wed, Sep 21, 2016 at 11:25:14AM -0400, Peter Eisentraut wrote: > On 9/14/16 4:01 PM, Robert Haas wrote: > > I think it is not a good idea to make the command names used here the > > plural forms of the command tags. Instead of "inserts", "updates", > > "imports", etc. just use "INSERT", "UPDATE", "IMPORT". That's simpler > > and less error prone - e.g. you won't end up with things like > > "refreshs", which is not a word. > > How about having the tag not be a column name but a row entry. So you'd > do something like > > SELECT * FROM pg_stat_sql WHERE tag = 'ALTER VIEW'; +1 for this. It's MUCH easier to deal with changes in row counts than changes in row type. Best, David. -- David Fetter <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 Fri, Sep 09, 2016 at 09:57:21AM -0400, Peter Eisentraut wrote: > Review of the patch in the commit fest: > > - The documentation is a bit incorrect about the ways to load this > module. shared_preload_libraries is not necessary. session_ and > local_ (with prep) should also work. Would you be so kind as to describe how you got local_preload_libraries to work? I'm stuck on getting Makefile to realize that the hook should be installed in $libdir/plugins rather than $libdir itself. Best, David. -- David Fetter <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 Mon, Sep 19, 2016 at 03:00:51PM -0400, Peter Eisentraut wrote: > On 9/19/16 12:02 AM, David Fetter wrote: > >> - The claim in the documentation that only superusers can do things > >> > with this module is not generally correct. > > I think that the claims are fixed. This is SUSET, at least in this > > patch, because anything short of that that changes query behavior > > seems incautious. > > Your last patch, which I looked at, had them as USERSET. I think that > is the right setting. Will work one up this evening that has that. Best, David. -- David Fetter <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 Fri, Sep 09, 2016 at 09:57:21AM -0400, Peter Eisentraut wrote:
> Review of the patch in the commit fest:
>
> - Various naming/spelling inconsistencies: In the source, the module
> is require_where, the documentation titles it require-where, the GUC
> parameters are requires_where.*, but incorrectly documented.
Fixed.
> - Unusual indentation in the Makefile
Fixed.
> - Needs tests
Still needs some fixing.
> - Not sure about errcode(ERRCODE_CARDINALITY_VIOLATION), which is
> documented in the code as "this means something returned the wrong
> number of rows". I think ERRCODE_SYNTAX_ERROR or something from
> nearby there would be better.
Changed to ERRCODE_SYNTAX_ERROR. CARDINALITY_VIOLATION was a bit too
cute.
> - errhint() string should end with a period.
Fixed.
> - The 7th argument of DefineCustomBoolVariable() is of type int, not
> bool, so passing false is somewhat wrong, even if it works.
Fixed.
> - There ought to be a _PG_fini() function that undoes what _PG_init()
> does.
Fixed.
> - The documentation should be expanded and clarified. Given that this
> is a "training wheels" module, we can be extra clear here. I would
> like to see some examples at least.
Working on this.
> - The documentation is a bit incorrect about the ways to load this
> module. shared_preload_libraries is not necessary. session_ and
> local_ (with prep) should also work.
I'm not 100% sure I understand what you want here. I did manage to
get the thing loaded without a restart via LOAD, but that's it so far.
Will continue to poke at it.
> - The claim in the documentation that only superusers can do things
> with this module is not generally correct.
I think that the claims are fixed. This is SUSET, at least in this
patch, because anything short of that that changes query behavior
seems incautious.
Best,
David.
--
David Fetter <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
diff --git a/contrib/Makefile b/contrib/Makefile
index 25263c0..4bd456f 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -40,6 +40,7 @@ SUBDIRS = \
pgstattuple \
pg_visibility \
postgres_fdw\
+ require_where \
seg \
spi \
tablefunc \
diff --git a/contrib/require_where/Makefile b/contrib/require_where/Makefile
new file mode 100644
index 000..0cf3663
--- /dev/null
+++ b/contrib/require_where/Makefile
@@ -0,0 +1,17 @@
+# contrib/require_where/Makefile
+
+MODULE_big = require_where
+OBJS = require_where.o
+
+PGFILEDESC = 'require_where - require DELETE and/or UPDATE to have a WHERE
clause'
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS = $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/require_where
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_builddir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/require_where/require_where.c
b/contrib/require_where/require_where.c
new file mode 100644
index 000..181b3bb
--- /dev/null
+++ b/contrib/require_where/require_where.c
@@ -0,0 +1,92 @@
+/*
+ * --
+ *
+ * require_where.c
+ *
+ * Copyright (C) 2016, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * contrib/require_where/require_where.c
+ *
+ * --
+ */
+#include "postgres.h"
+
+#include "fmgr.h"
+
+#include "parser/analyze.h"
+
+#include "utils/elog.h"
+#include "utils/guc.h"
+
+PG_MODULE_MAGIC;
+
+void _PG_init(void);
+void _PG_fini(void);
+
+static post_parse_analyze_hook_type original_post_parse_analyze_hook =
NULL;
+static boolrequire_where_delete = false;
+static boolrequire_where_update = false;
+
+static void
+require_where_check(ParseState *pstate, Query *query)
+{
+
+ if (require_where_delete && query->commandType == CMD_DELETE)
+ {
+ Assert(query->jointree != NULL);
+ if (query->jointree->quals == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+errmsg("DELETE requires a WHERE
clause"),
+errhint("To delete all rows, use
\"WHERE true\" or similar.")));
+ }
+
+ if (require_where_update && query->commandType == CMD_UPDATE)
+ {
Re: [HACKERS] Suggestions for first contribution?
On Mon, Sep 05, 2016 at 01:25:03PM -0400, Christian Convey wrote: > Hi guys, > > Can anyone suggest a project for my first PG contribution? How about adding PIVOT tables? MS SQL Server and Oracle both have them. If you're interested, I have some ideas about the UI parts and a few about the implementation. Best, David. -- David Fetter <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 some binaries
On Fri, Aug 26, 2016 at 04:33:47PM -0300, Euler Taveira wrote: > On 26-08-2016 14:03, David Fetter wrote: > > Would these make sense as pg_ctl options, or are you separating them > > out because they're not instance-wide? If separating them is > > important on those grounds, how about something like pg_db or > > pg_db_command? > > > It doesn't make sense because pg_ctl is server-side and pg_command would > be client-side. Perfect! > >> pg_oid2name: I don't have a strong opinion that it fits in pg_command; > > > > I vaguely knew that this existed, but I can't recall having heard of > > anybody actually using it. I suppose it's under pg_ctl if the split > > above between instance-wide and db-specific holds. > > > I don't use it for a long time. It also a client-side binary then better > place for it is pg_command. BTW, is anybody using it? If so, we could > add this functionality to psql and remove it. Sure. For server versions 10 or better, it could be a call to a new server-side function. Best, David. -- David Fetter <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 some binaries
On Fri, Aug 26, 2016 at 01:26:39PM -0300, Euler Taveira wrote: > Hi, > > I'm bringing this $subject into discussion again. Historically, we are > carrying binary names that have been confused newbies. createuser is the > worst name so for. Also, names like createdb, initdb, reindexdb, and > droplang does not suggest what product it is referring to. Adding a > prefix (pg_, pg, ...) would 'make things clear'. +1 for pg_ . We should have done this long ago, but this is better fixed than left broken. > If we have a consensus > about this change, I suggest renaming the following binaries: > > clusterdb > createdb > createlang > createuser > dropdb > droplang > dropuser > initdb > oid2name > reindexdb > vacuumdb > vacuumlo > > Another major change related to this topic is assemble functionalities > from binaries. We currently have 34 binaries (is that a lot for a single > software?). Also, some of them have the same principle: execute a > administrative or maintenance command. IMHO, from the list above, we > could reduce it to: > > pg_command: clusterdb, createdb, dropdb, createuser, dropuser, > createlang, droplang, reindexdb, vacuumdb, vacuumlo. It also has the > advantage to allow adding new administrative/maintenance commands to it > in the future; Would these make sense as pg_ctl options, or are you separating them out because they're not instance-wide? If separating them is important on those grounds, how about something like pg_db or pg_db_command? > pg_oid2name: I don't have a strong opinion that it fits in pg_command; I vaguely knew that this existed, but I can't recall having heard of anybody actually using it. I suppose it's under pg_ctl if the split above between instance-wide and db-specific holds. > initdb: we already have 'pg_ctl init' (since 9.0) and could remove initdb. > > Opinions? +1 for removing initdb. Best, David. -- David Fetter <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] New psql prompt substitution %r (m = master, r = replica)
On Thu, Aug 11, 2016 at 01:04:19PM +0300, Aleksander Alekseev wrote: > Hello. > > Suggested patch introduces an %r substitution in psql's prompt. This > substitution allows to display whether user is connected to master or > replica right in a prompt. This is a neat idea, but there are some issues. - There's a new GUC. This is probably not compelling enough a feature for that. - The check, if I understand correctly, is only done on connect, even though it could change during a session. How about something that: - Allowed setting a query to be executed after each command. - Put the results of that query into a psql variable. This differs from \gset in that it would be executed silently at the end of each command. What say? 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] Column COMMENTs in CREATE TABLE?
On Fri, Aug 05, 2016 at 10:14:21AM -0400, Peter Eisentraut wrote: > On 7/3/16 11:41 AM, Tom Lane wrote: > > I can see the reasoning for > > allowing COMMENT in a table column definition, but the argument for > > allowing it in simpler CREATEs seems tissue-thin: > > > > CREATE FUNCTION foo(int) RETURNS ... ; > > COMMENT ON FUNCTION foo(int) IS 'blah'; > > > > vs > > > > CREATE FUNCTION foo(int) RETURNS ... > > WITH (COMMENT 'blah'); > > > > Not much of a keystroke savings, nor is the comment noticeably > > "closer" to its object than before. > > I had actually been thinking about a similar proposal, but specifically > for CREATE FUNCTION. But the syntax would have to put it above the > function body, not below it. I think the CREATE FUNCTION syntax could > actually handle that. For what it's worth, I tend to put the function body last. That's just my taste, though. Would it be hard to keep the ability to permute the stuff after CREATE FUNCTION (args) RETURNS [SETOF] type as we have it now? 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 version numbering practices
On Mon, Aug 01, 2016 at 02:52:04PM -0400, Stephen Frost wrote: > * Tom Lane (t...@sss.pgh.pa.us) wrote: > > "David G. Johnston" <david.g.johns...@gmail.com> writes: > > > I suspect I'll end up using 10.x somewhat frequently though I'm mostly on > > > the lists. I suspect the choice will be dependent on context and channel. > > > > Hmm, that seems like a workable answer as well, and one that's traceable > > to our past habits. > > For my 2c, I'd kind of prefer v10, but I could live with 10.x. > > Not sure that I have any real reason for that preference other than > 'v10' is slightly shorter and seems more 'right', to me. 10 is even shorter, and when we get to 15, it seems like it'll be pretty silly still to be referring to the 9.x series. > In other words, "are you going to back-patch this to 10.x?" doesn't > seem quite right, whereas "are you going to back-patch this to v10?" > lines up correctly in my head, but I don't hold that distinction > very closely and either would work. What's wrong with, "Are you going to back-patch this to 10?" Bear in mind that this sentence first makes sense once we've got a new branch for 11, gets more likely as we have 12 and 13, then drops, after that, all the way to 0 when we hit 16, which by my calculation should be in the 2020s. Some of the people who will be our major contributors then are in high school now, and will just be puzzled and vaguely annoyed by references to the old system. Now, when we're changing the visible version number, seems like the time to break fully with the idea that our major version numbers have two parts. We'll still be referring, with decreasing frequency, to 9.6, 9.5, 9.4, etc., but there's good reason not to carry that idea forward now that we're no longer doing 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] New version numbering practices
On Mon, Aug 01, 2016 at 11:49:41AM -0400, Tom Lane wrote: > As Peter mentioned in > https://www.postgresql.org/message-id/ba76aeb0-2f84-d180-268f-ea0f5ace4...@2ndquadrant.com > the decision has been taken to simplify our user-facing version numbering > system to be a two-component number. Since there have been questions > about the details of that, I wanted to emphasize that we are not breaking > compatibility with code-facing version numbering. In particular, > PG_VERSION_NUM and related representations will look like 1000xx, 1100xx, > etc in future branches, as though the second component were zero in an > old-style version number. > > Somebody needs to come up with a patch implementing this changeover. > I will work on it if no one else feels motivated to (but I'd be just as > happy to let someone else do it). If we do not have such a patch ready > to go when the 9.6 branch is made on Aug 15, I will probably transiently > stamp HEAD as 9.7 rather than have a situation where "version 10" appears > in a three-part version number. (External code will need some cue as > to how to format displays from PG_VERSION_NUM, so we should have a hard > and fast rule that major >= 10 means new style.) > > Also, it strikes me that we need a new convention for how we talk about > release branches informally. Up to now, mentioning say "9.5" without > any further qualification in a PG-list message was usually sufficient > to indicate a branch number, but I do not think that will work so well > if one just writes "10". I'm tempted to start writing branch numbers > as something like "PG10" or "v10". Thoughts? I don't see 10 as ambiguous. It's clear what's being talked about, now that the decision has been made. Best, David. "This one goes up to 11." -- 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] "Strong sides of MySQL" talk from PgDay16Russia, translated
On Fri, Jul 29, 2016 at 09:20:12PM +0300, Nikolay Samokhvalov wrote:
> On Fri, Jul 29, 2016 at 5:28 PM, Jim Nasby <jim.na...@bluetreble.com> wrote:
> >
> > The way I sum up MySQL vs PG for people that ask is to recount how they
> > "fixed" the Feb. 31st bug when they released strict mode (something that
> > they actually called out in the release PR). With strict mode enabled, Feb.
> > 30th and 31st would give you an error. Feb 35th was still silently
> > converted to March whatever. *That was the MySQL mentality: data quality
> > doesn't matter compared to "ease of use".*
> >
> > They've done this throughout their history... when presented with a hard
> > problem, they skip around it or plaster over it, and then they promote that
> > their solution is the only right way to solve the problem. (Their docs
> > actually used to say that anything other that table-level locking was a bad
> > idea.)
>
>
> This is exactly what I mean saying MySQL speaks different language than I
> know, and that's why I simply cannot use it:
>
> (mysql 5.7.12)
>
> mysql> select cast('2016-99-99' as date);
> ++
> | cast('2016-99-99' as date) |
> ++
> | NULL |
> ++
> 1 row in set, 1 warning (0.00 sec)
>
>
> In Postgres:
>
> test=# select cast('2016-99-99' as date);
> ERROR: date/time field value out of range: "2016-99-99"
> LINE 1: select cast('2016-99-99' as date);
> ^
I expect this kind of blather from MySQL, but you've brought up
something that's been bothering me for awhile. PostgreSQL's response
should look more like this:
ERROR: month field value out of range: "2016-99-99"
LINE 1: select cast('2016-99-99' as date);
^
Any idea how much effort that would be?
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 Wed, Jul 27, 2016 at 02:59:17PM +0200, Vik Fearing wrote: > On 27/07/16 06:11, David Fetter wrote: > > On Wed, Jul 27, 2016 at 03:24:28AM +0200, Vik Fearing wrote: > >> On 27/07/16 03:15, Peter Eisentraut wrote: > >>> On 7/26/16 6:14 PM, Vik Fearing wrote: > >>>> As mentioned elsewhere in the thread, you can just do WHERE true > >>>> to get around it, so why on Earth have it PGC_SUSET? > >>> > >>> I'm not sure whether it's supposed to guard against typos and > >>> possibly buggy SQL string concatenation in application code. So > >>> it would help against accidental mistakes, whereas putting a WHERE > >>> TRUE in there would be an intentional override. > >> > >> If buggy SQL string concatenation in application code is your > >> argument, quite a lot of them add "WHERE true" so that they can just > >> append a bunch of "AND ..." clauses without worrying if it's the > >> first (or last, whatever), so I'm not sure this is protecting > >> anything. > > > > I am sure that I'm not the only one who's been asked for this feature > > because people other than me have piped up on this thread to that very > > effect. > > Sure. I'm just saying that I think it is poorly designed. I think it > would be far better to error out if the command affects x rows, or an > estimated y% of the table. What else would constitute a good design? I am a little wary of relying on estimates, at least those provided by EXPLAIN, because the row counts they produce can be off by several orders of magnitude. Are there more accurate ways to estimate? Would you want x and y to be parameters somewhere? > Doing that, and also allowing the user to turn it off, would solve the > problem as I understand your presentation of it. I made it PGC_USERSET in the third patch. > > I understand that there may well be lots of really meticulous people > > on this list, people who would never accidentally do an unqualified > > DELETE on a table in production, but I can't claim to be one of them > > because I have, and not just once. It's under once a decade, but even > > that's too many. > > That doesn't mean that requiring a WHERE clause -- without even looking > at what's in it -- is a good idea. > > Why not start by turning off autocommit, for example? Because that setting is client side, and even more vulnerable to not being turned on for everyone everywhere. > > I'm not proposing to make this feature default, or even available by > > default, but I am totally certain that this is the kind of feature > > people would really appreciate, even if it doesn't prevent every > > catastrophe. > > This kind of feature, why not. This feature, no. I would very much value your input into the design of the feature. 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] BRIN vs. HOT
On Thu, Jul 28, 2016 at 10:53:47AM -0400, Robert Haas wrote: > [1] I look forward to a future PostgreSQL conference in which the > struggle to pronounce "HMT" forms a recurring theme. For maximal confusion, this should really be called a Heap Often Tuple. 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] A Modest Upgrade Proposal
On Sun, Jul 17, 2016 at 02:55:20PM -0400, Jan Wieck wrote:
> On Sun, Jul 17, 2016 at 2:08 PM, Jim Nasby <jim.na...@bluetreble.com> wrote:
> > On 7/13/16 2:06 PM, Joshua D. Drake wrote:
> >> On 07/07/2016 01:01 PM, Robert Haas wrote:
> >> There was an unconference session on this topic at PGCon and quite a
> >>> number of people there stated that they found DDL to be an ease-of-use
> >>> feature and wanted to have it.
> >>
> >> Yeah, I haven't meet anyone yet that would like to have:
> >>
> >> select replicate_these_relations('['public']);
> >>
> >> vs:
> >>
> >> ALTER SCHEMA public ENABLE REPLICATION;
> >>
> >> (or something like that).
> >>
> >
> > I generally agree, but I think the more important question is "Why?". Is
> > it becouse DDL looks more like a sentence? Is it because arrays are a PITA?
> > Is it too hard to call functions?
>
> Once you get fine grained enough to support replicating different
> sets of possibly overlapping objects/namespaces to different groups
> of recipients, the DDL approach becomes just as convoluted as
> calling functions and nobody will memorize the entire syntax.
I don't see this as an actual problem. I've written parts of the
SELECT syntax, but I haven't memorized even all of that.
DDL doesn't promise to be more complicated or easier to get wrong than
function calls, as far as I can tell. The opposite could well be
true.
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 Wed, Jul 27, 2016 at 03:24:28AM +0200, Vik Fearing wrote: > On 27/07/16 03:15, Peter Eisentraut wrote: > > On 7/26/16 6:14 PM, Vik Fearing wrote: > >> As mentioned elsewhere in the thread, you can just do WHERE true > >> to get around it, so why on Earth have it PGC_SUSET? > > > > I'm not sure whether it's supposed to guard against typos and > > possibly buggy SQL string concatenation in application code. So > > it would help against accidental mistakes, whereas putting a WHERE > > TRUE in there would be an intentional override. > > If buggy SQL string concatenation in application code is your > argument, quite a lot of them add "WHERE true" so that they can just > append a bunch of "AND ..." clauses without worrying if it's the > first (or last, whatever), so I'm not sure this is protecting > anything. I am sure that I'm not the only one who's been asked for this feature because people other than me have piped up on this thread to that very effect. I understand that there may well be lots of really meticulous people on this list, people who would never accidentally do an unqualified DELETE on a table in production, but I can't claim to be one of them because I have, and not just once. It's under once a decade, but even that's too many. I'm not proposing to make this feature default, or even available by default, but I am totally certain that this is the kind of feature people would really appreciate, even if it doesn't prevent every catastrophe. 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 Tue, Jul 26, 2016 at 04:39:14PM -0400, Robert Haas wrote:
> On Mon, Jul 25, 2016 at 11:38 PM, David Fetter <da...@fetter.org> wrote:
> > On Mon, Jul 25, 2016 at 11:12:24PM -0400, Robert Haas wrote:
> >> On Fri, Jul 22, 2016 at 2:38 AM, David Fetter <da...@fetter.org> wrote:
> >> > I've renamed it to require_where and contrib-ified.
> >>
> >> I'm not sure that the Authors section is entirely complete.
> >
> > Does this suit?
>
> YFTATP.
Oops. I'd done it on the commitfest app, but not in the patch. I've
also made this PGC_USERSET.
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
diff --git a/contrib/Makefile b/contrib/Makefile
index 25263c0..4bd456f 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -40,6 +40,7 @@ SUBDIRS = \
pgstattuple \
pg_visibility \
postgres_fdw\
+ require_where \
seg \
spi \
tablefunc \
diff --git a/contrib/require_where/Makefile b/contrib/require_where/Makefile
new file mode 100644
index 000..731f9fb
--- /dev/null
+++ b/contrib/require_where/Makefile
@@ -0,0 +1,15 @@
+MODULE_big = require_where
+OBJS = require_where.o
+
+PGFILEDESC = 'require_where - require DELETE and/or UPDATE to have a WHERE
clause'
+
+ifdef USE_PGXS
+ PG_CONFIG = pg_config
+ PGXS = $(shell $(PG_CONFIG) --pgxs)
+ include $(PGXS)
+else
+ subdir = contrib/require_where
+ top_builddir = ../..
+ include $(top_builddir)/src/Makefile.global
+ include $(top_builddir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/require_where/require_where.c
b/contrib/require_where/require_where.c
new file mode 100644
index 000..556101a
--- /dev/null
+++ b/contrib/require_where/require_where.c
@@ -0,0 +1,81 @@
+/*
+ * --
+ *
+ * require_where.c
+ *
+ * Copyright (C) 2016, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * contrib/require_where/require_where.c
+ *
+ * --
+ */
+#include "postgres.h"
+
+#include "fmgr.h"
+
+#include "parser/analyze.h"
+
+#include "utils/elog.h"
+#include "utils/guc.h"
+
+PG_MODULE_MAGIC;
+
+void _PG_init(void);
+
+static post_parse_analyze_hook_type original_post_parse_analyze_hook = NULL;
+static bool delete_requires_where = false;
+static bool update_requires_where = false;
+
+static void
+requires_where_check(ParseState *pstate, Query *query)
+{
+
+ if (delete_requires_where && query->commandType == CMD_DELETE)
+ {
+ Assert(query->jointree != NULL);
+ if (query->jointree->quals == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_CARDINALITY_VIOLATION),
+errmsg("DELETE requires a WHERE
clause"),
+errhint("To delete all rows, use
\"WHERE true\" or similar")));
+ }
+
+ if (update_requires_where && query->commandType == CMD_UPDATE)
+ {
+ Assert(query->jointree != NULL);
+ if (query->jointree->quals == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_CARDINALITY_VIOLATION),
+errmsg("UPDATE requires a WHERE
clause"),
+errhint("To update all rows, use
\"WHERE true\" or similar")));
+ }
+
+ if (original_post_parse_analyze_hook != NULL)
+ (*original_post_parse_analyze_hook) (pstate, query);
+}
+
+void
+_PG_init(void)
+{
+ DefineCustomBoolVariable("requires_where.delete",
+ "Require every
DELETE statement to have a WHERE clause.",
+ NULL,
+
_requires_where,
+ false,
+ PGC_USERSET,
+ false,
+ NULL, NULL,
NULL);
+
+ DefineCustomBoolVariable("requires_where.update",
+
Re: [HACKERS] PoC: Make it possible to disallow WHERE-less UPDATE and DELETE
On Mon, Jul 25, 2016 at 11:12:24PM -0400, Robert Haas wrote: > On Fri, Jul 22, 2016 at 2:38 AM, David Fetter <da...@fetter.org> wrote: > > I've renamed it to require_where and contrib-ified. > > I'm not sure that the Authors section is entirely complete. Does this suit? 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] Temporal query processing with range types
On Fri, Jul 22, 2016 at 01:15:17PM +0200, Anton Dignös wrote: > Hi hackers, > > we are a group of researches that work on temporal databases. Our > main focus is the processing of data with time intervals, such as > the range types in PostgreSQL. Thanks for your hard work so far! [Explanation and examples elided] To what extent, if any, are you attempting to follow the SQL:2011 standard? http://cs.ulb.ac.be/public/_media/teaching/infoh415/tempfeaturessql2011.pdf 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, Jul 21, 2016 at 09:52:26AM -0700, David Fetter wrote:
> On Thu, Jul 21, 2016 at 12:46:29PM -0400, Robert Haas wrote:
> > On Thu, Jul 21, 2016 at 12:39 PM, David Fetter <da...@fetter.org> wrote:
> > > On Thu, Jul 21, 2016 at 06:20:37PM +0300, Teodor Sigaev wrote:
> > >> > Please find attached a patch which makes it possible to disallow
> > >> > UPDATEs and DELETEs which lack a WHERE clause. As this changes query
> > >> > behavior, I've made the new GUCs PGC_SUSET.
> > >> >
> > >> > What say?
> > >>
> > >> DELETE FROM tbl WHERE true; ?
> > >
> > > I specifically left this possible so the feature when turned on allows
> > > people to do updates with an always-true qualifier if that's what they
> > > actually mean to do.
> > >
> > > In case it wasn't clear, unqualified updates and deletes are permitted
> > > by default. This patch allows people to set it so they're disallowed.
> >
> > I join with others in thinking it's a reasonable contrib module. In
> > fact, I already wrote it for my 2015 PGCon tutorial. Well, the
> > "delete" part, anyway.
> >
> > https://github.com/robertmhaas/introduction-to-postgresql-hacking/compare/master...robertmhaas:delete_needs_where
>
> I'm happy to write the rest of this as a contrib module. I hope to
> get to that this evening.
I've renamed it to require_where and contrib-ified.
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
diff --git a/contrib/Makefile b/contrib/Makefile
index 25263c0..4bd456f 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -40,6 +40,7 @@ SUBDIRS = \
pgstattuple \
pg_visibility \
postgres_fdw\
+ require_where \
seg \
spi \
tablefunc \
diff --git a/contrib/require_where/Makefile b/contrib/require_where/Makefile
new file mode 100644
index 000..731f9fb
--- /dev/null
+++ b/contrib/require_where/Makefile
@@ -0,0 +1,15 @@
+MODULE_big = require_where
+OBJS = require_where.o
+
+PGFILEDESC = 'require_where - require DELETE and/or UPDATE to have a WHERE
clause'
+
+ifdef USE_PGXS
+ PG_CONFIG = pg_config
+ PGXS = $(shell $(PG_CONFIG) --pgxs)
+ include $(PGXS)
+else
+ subdir = contrib/require_where
+ top_builddir = ../..
+ include $(top_builddir)/src/Makefile.global
+ include $(top_builddir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/require_where/require_where.c
b/contrib/require_where/require_where.c
new file mode 100644
index 000..3f51492
--- /dev/null
+++ b/contrib/require_where/require_where.c
@@ -0,0 +1,81 @@
+/*
+ * --
+ *
+ * require_where.c
+ *
+ * Copyright (C) 2016, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * contrib/require_where/require_where.c
+ *
+ * --
+ */
+#include "postgres.h"
+
+#include "fmgr.h"
+
+#include "parser/analyze.h"
+
+#include "utils/elog.h"
+#include "utils/guc.h"
+
+PG_MODULE_MAGIC;
+
+void _PG_init(void);
+
+static post_parse_analyze_hook_type original_post_parse_analyze_hook = NULL;
+static bool delete_requires_where = false;
+static bool update_requires_where = false;
+
+static void
+requires_where_check(ParseState *pstate, Query *query)
+{
+
+ if (delete_requires_where && query->commandType == CMD_DELETE)
+ {
+ Assert(query->jointree != NULL);
+ if (query->jointree->quals == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_CARDINALITY_VIOLATION),
+errmsg("DELETE requires a WHERE
clause"),
+errhint("To delete all rows, use
\"WHERE true\" or similar")));
+ }
+
+ if (update_requires_where && query->commandType == CMD_UPDATE)
+ {
+ Assert(query->jointree != NULL);
+ if (query->jointree->quals == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_CARDINALITY_VIOLATION),
+errmsg("UPDATE requires a WHERE
clause"),
+errhint("To update all ro
Re: [HACKERS] PoC: Make it possible to disallow WHERE-less UPDATE and DELETE
On Thu, Jul 21, 2016 at 04:48:37PM -0500, Jim Nasby wrote: > On 7/21/16 11:46 AM, David Fetter wrote: > > > > Can't you implement this as a extension? > > Yes. In that case, I'd want to make it a contrib extension, as it is > > at least in theory attached to specific major versions of the backend. > > Howso? At least one of the structures it references isn't in a public API. 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, Jul 21, 2016 at 12:51:50PM -0400, Robert Haas wrote: > On Thu, Jul 21, 2016 at 12:49 PM, Abhijit Menon-Sen <a...@2ndquadrant.com> > wrote: > > At 2016-07-21 12:46:29 -0400, robertmh...@gmail.com wrote: > >> > >> I join with others in thinking it's a reasonable contrib module. > > > > I don't like the use of the term "empty" to describe an UPDATE or DELETE > > without a WHERE clause. > > /me scratches head. > > Who used that term? I did out of failure to imagine another short way to describe the situation as I was writing it up. I'd be delighted to change it to something else. Best, David. Oh, and the bike shed should definitely be puce with blaze orange polka dots. -- 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, Jul 21, 2016 at 12:46:29PM -0400, Robert Haas wrote: > On Thu, Jul 21, 2016 at 12:39 PM, David Fetter <da...@fetter.org> wrote: > > On Thu, Jul 21, 2016 at 06:20:37PM +0300, Teodor Sigaev wrote: > >> > Please find attached a patch which makes it possible to disallow > >> > UPDATEs and DELETEs which lack a WHERE clause. As this changes query > >> > behavior, I've made the new GUCs PGC_SUSET. > >> > > >> > What say? > >> > >> DELETE FROM tbl WHERE true; ? > > > > I specifically left this possible so the feature when turned on allows > > people to do updates with an always-true qualifier if that's what they > > actually mean to do. > > > > In case it wasn't clear, unqualified updates and deletes are permitted > > by default. This patch allows people to set it so they're disallowed. > > I join with others in thinking it's a reasonable contrib module. In > fact, I already wrote it for my 2015 PGCon tutorial. Well, the > "delete" part, anyway. > > https://github.com/robertmhaas/introduction-to-postgresql-hacking/compare/master...robertmhaas:delete_needs_where I'm happy to write the rest of this as a contrib module. I hope to get to that this evening. 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, Jul 21, 2016 at 09:21:55AM -0400, Jim Mlodgenski wrote: > On Thu, Jul 21, 2016 at 12:57 AM, David Fetter <da...@fetter.org> wrote: > > Please find attached a patch which makes it possible to disallow > > UPDATEs and DELETEs which lack a WHERE clause. As this changes > > query behavior, I've made the new GUCs PGC_SUSET. > > > > What say? > > > Can't you implement this as a extension? Yes. In that case, I'd want to make it a contrib extension, as it is at least in theory attached to specific major versions of the backend. Also, if it's not in contrib, we can basically forget about having most people even know about it, let alone get specific separate permission to use it in production. That's reality, much as I would like it not to be. > The SQL Firewall project is already doing some similar concepts by > catching prohibiting SQL and preventing it from executing. > https://github.com/uptimejp/sql_firewall That's very nice, but it illustrates my point perfectly. The extension is from a current respected and prolific contributor to the community, but I had no idea that it was there, and by dint of writing the PostgreSQL Weekly News, I keep closer tabs on external things PostgreSQL than easily 99.9% of people who deploy 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, Jul 21, 2016 at 06:20:37PM +0300, Teodor Sigaev wrote: > > Please find attached a patch which makes it possible to disallow > > UPDATEs and DELETEs which lack a WHERE clause. As this changes query > > behavior, I've made the new GUCs PGC_SUSET. > > > > What say? > > DELETE FROM tbl WHERE true; ? I specifically left this possible so the feature when turned on allows people to do updates with an always-true qualifier if that's what they actually mean to do. In case it wasn't clear, unqualified updates and deletes are permitted by default. This patch allows people to set it so they're disallowed. 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] PoC: Make it possible to disallow WHERE-less UPDATE and DELETE
Folks,
Please find attached a patch which makes it possible to disallow
UPDATEs and DELETEs which lack a WHERE clause. As this changes query
behavior, I've made the new GUCs PGC_SUSET.
What say?
Thanks to Gurjeet Singh for the idea and Andrew Gierth for the tips
implementing.
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
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index c9e0ec2..c01db8d 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -7019,6 +7019,32 @@ dynamic_library_path =
'C:\tools\postgresql;H:\my_project\lib;$libdir'
+
+ allow_empty_updates (boolean)
+
+ allow_empty_updates configuration
parameter
+
+
+
+
+Allow UPDATE statements that lack a WHERE clause.
+
+
+
+
+
+ allow_empty_deletes (boolean)
+
+ allow_empty_deletes configuration
parameter
+
+
+
+
+Allow DELETE statements that lack a WHERE clause.
+
+
+
+
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 29c8c4e..0c8786e 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -48,6 +48,9 @@
/* Hook for plugins to get control at end of parse analysis */
post_parse_analyze_hook_type post_parse_analyze_hook = NULL;
+bool allow_empty_deletes = true;
+bool allow_empty_updates = true;
+
static Query *transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt);
static Query *transformInsertStmt(ParseState *pstate, InsertStmt *stmt);
static List *transformInsertRow(ParseState *pstate, List *exprlist,
@@ -408,6 +411,12 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
qual = transformWhereClause(pstate, stmt->whereClause,
EXPR_KIND_WHERE, "WHERE");
+ /* Check for allow_empty_deletes */
+ if (!allow_empty_deletes && qual == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_STATEMENT_HAS_NO_WHERE),
+errmsg("DELETE without a WHERE clause is
disallowed")));
+
qry->returningList = transformReturningList(pstate,
stmt->returningList);
/* done building the range table and jointree */
@@ -2110,6 +2119,12 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
qual = transformWhereClause(pstate, stmt->whereClause,
EXPR_KIND_WHERE, "WHERE");
+ /* Check for allow_empty_updates */
+ if (!allow_empty_updates && qual == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_STATEMENT_HAS_NO_WHERE),
+errmsg("UPDATE without a WHERE clause is
disallowed")));
+
qry->returningList = transformReturningList(pstate,
stmt->returningList);
/*
diff --git a/src/backend/utils/errcodes.txt b/src/backend/utils/errcodes.txt
index be924d5..254cf10 100644
--- a/src/backend/utils/errcodes.txt
+++ b/src/backend/utils/errcodes.txt
@@ -388,6 +388,7 @@ Section: Class 54 - Program Limit Exceeded
# this is for wired-in limits, not resource exhaustion problems (class
borrowed from DB2)
54000EERRCODE_PROGRAM_LIMIT_EXCEEDED
program_limit_exceeded
54001EERRCODE_STATEMENT_TOO_COMPLEX
statement_too_complex
+54002EERRCODE_STATEMENT_HAS_NO_WHERE
statement_has_no_where
54011EERRCODE_TOO_MANY_COLUMNS
too_many_columns
54023EERRCODE_TOO_MANY_ARGUMENTS
too_many_arguments
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 6ac5184..fca6b71 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -47,6 +47,7 @@
#include "optimizer/geqo.h"
#include "optimizer/paths.h"
#include "optimizer/planmain.h"
+#include "parser/analyze.h"
#include "parser/parse_expr.h"
#include "parser/parse_type.h"
#include "parser/parser.h"
@@ -1653,6 +1654,26 @@ static struct config_bool ConfigureNamesBool[] =
NULL, NULL, NULL
},
+ {
+ {"allow_empty_deletes", PGC_SUSET, QUERY_TUNING,
+ gettext_noop("Allow DELETE without a WHERE clause"),
+ NULL
+ },
+ _empty_deletes,
+ true,
+
Re: [HACKERS] Password identifiers, protocol aging and SCRAM protocol
On Wed, Jul 20, 2016 at 02:12:57PM -0400, Alvaro Herrera wrote: > Michael Paquier wrote: > > On Wed, Jul 6, 2016 at 4:18 PM, Michael Paquier > > <michael.paqu...@gmail.com> wrote: > > > OK, after hacking that for a bit I have finished with option 2 and the > > > set of PG-like set of routines, the use of USE_SSL in the file > > > containing all the SHA functions of OpenBSD has proved to be really > > > ugly, but with a split things are really clear to the eye. The stuff I > > > got builds on OSX, Linux and MSVC. pgcrypto cannot link directly to > > > libpgcommon.a, so I am making it compile directly with the source > > > files, as it is doing on HEAD. > > > > Btw, attached is the patch I did for this part if there is any interest in > > it. > > After quickly eyeballing your patch, I agree with the decision of going > with (2), even if my gut initially told me that (1) would be better > because it'd require less makefile trickery. > > I'm surprised that you say pgcrypto cannot link libpgcommon directly. > Is there some insurmountable problem there? I notice your MSVC patch > uses libpgcommon while the Makefile symlinks the files. People have, in the past, expressed concerns about linking in pgcrypto. Apparently, in some countries, it's a legal problem. 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] Column COMMENTs in CREATE TABLE?
On Sat, Jul 02, 2016 at 01:06:49PM -0400, David G. Johnston wrote: > On Sat, Jul 2, 2016 at 12:55 PM, Marko Tiikkaja <ma...@joh.to> wrote: > > > > > What I would prefer is something like this: > > > > CREATE TABLE foo( > > f1 int NOT NULL COMMENT > > 'the first field', > > f2 int NOT NULL COMMENT > > 'the second field', > > ... > > ); > > > > which would ensure the comments are both next to the field definition > > they're documenting and that they make it all the way to the database. I > > looked into the biggest products, and MySQL supports this syntax. I > > couldn't find any similar syntax in any other product. > > > > > +1 for the idea - though restricting it to columns would not be ideal. +1 for adding it to all the CREATEs whose objects support COMMENT. Might something like CREATE ... [WITH (COMMENT $$Big honking comment here$$)] for the explicit CREATE cases and something like CREATE TABLE foo( id SERIAL PRIMARY KEY WITH (COMMENT 'Generated primary key, best find a natural one, too'), t TEXT NOT NULL WITH (COMMENT 'Really? A single-letter name?!?'), ... ) for cases where the CREATE isn't part of the syntax help alleviate the keyword issue? I suggested doing it this way because where there's one thing, in this case a COMMENT, it's reasonable to expect that there will be others and make that simpler to do. Best, David. -- David Fetter <da...@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 10.0
On Sat, Jun 18, 2016 at 05:48:30PM -0700, Josh Berkus wrote: > On 06/16/2016 11:01 PM, Craig Ringer wrote: > > > > I thought about raising this, but I think in the end it's replacing one > > confusing and weird versioning scheme for another confusing and weird > > versioning scheme. > > > > It does have the advantage that that compare a two-part major like > > 090401 vs 090402 won't be confused when they compare 100100 and 100200, > > since it'll be 11 and 12. So it's more backward-compatible. But > > ugly. > > Realistically, though, we're more likely to end up with 10.0.1 than > 10.1. I don't think we're anywhere near plumbing the depths of the > stuff which will break because folks are parsing our version numbers > with regexes. In more major software, this will break nagios > check_postgres. > > I'm not happy with it, but I believe that's where we'll end up. Pulling back a bit from this a bit, I am pretty sure that the fix for the new announced version numbers, so long as our new policy is clear and won't be changed again for the foreseeable future has the following characteristics: - Not a show-stopper, i.e. people will not drop support in their products for PostgreSQL over this. - A Matter of Programming that really is small. The difference between the old three-part/six-digit version encoding and the new two-part/four-digit version encoding is stark and simple, as is the code to handle it, even if it's regex. By the time we get to PostgreSQL 100.0, the first starship will already be back, and the 9.x PostgreSQLs will be in older to people using them then that the Zuse Z1 https://en.wikipedia.org/wiki/Konrad_Zuse is to us now. Just my $.02. Cheers, David. -- David Fetter <da...@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Negators for operators
Folks, While constructing a somewhat hairy query with HAVING in it, I noticed that a lot of operators don't have negators, which would have been convenient for the class of queries I was constructing. Further investigation showed that while 380 of the built-in operators had negators, 395 do not. For some fraction I'll investigate if warranted, a negator makes no sense. For the rest, I'd like to propose adding negator operators prefixed with '!', just as we have for the negators of regex-like things. What say? Cheers, David. -- David Fetter <da...@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] JSON[B] arrays are second-class citizens
On Tue, May 31, 2016 at 06:15:32PM -0400, David G. Johnston wrote:
> I stand corrected. I was thinking you could somehow craft unnest(' value here>') but there is no way to auto-convert to "anyarray"...
>
> > The json_array_elements family manages to do the right thing. Why
> > would it be harder to make sure UNNEST and ROWS FROM() do so?
> >
>
> I have apparently failed to understand your point. All I saw was that you
> wanted "unnest(jsonb)" to work in an identical fashion to
> "jsonb_array_elements(jsonb)". If there is some aspect beyond this being
> an aliasing situation then you have failed to communicate it such that I
> comprehended that fact.
>
Upon further investigation, I think UNNEST should Just Work™ which is
to say that it should unnest arrays into their top-level constituent
elements if the standard doesn't specify some other behavior.
Separately, I suppose, I think there needs to be an easy way to cast
the output of UNNEST. Lacking knowledge of the intricacies of
parsing, etc., I'd propose CAST(UNNEST(...) AS ...), or better yet,
UNNEST(...):: at least in the case without WITH ORDINALITY.
Further out in the future, at least so it seems to me, it would be
nice to have a feature where one could cast a column to an expanded
row type, e.g.:
SELECT my_jsonb::(i INT, t TEXT, p POINT), foo, bar
FROM ...
and get a result set with 5 columns in it.
Cheers,
David.
--
David Fetter <da...@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] JSON[B] arrays are second-class citizens
On Tue, May 31, 2016 at 06:20:26PM -0400, Tom Lane wrote:
> David Fetter <da...@fetter.org> writes:
> > On Tue, May 31, 2016 at 05:06:00PM -0400, David G. Johnston wrote:
> >> While likely not that common the introduction of an ambiguity makes
> >> raises the bar considerably.
>
> > What ambiguity?
>
> My first thought about it was that
>
> select unnest('{1,2,3}');
>
> would start failing. But it turns out it already does fail:
>
> ERROR: function unnest(unknown) is not unique
>
> You get that as a result of the recent introduction of unnest(tsvector),
> which we debated a few weeks ago and seem to have decided to leave as-is.
> But it failed before 9.6 too, with
>
> ERROR: could not determine polymorphic type because input has type "unknown"
>
> So at least in this particular case, adding unnest(jsonb) wouldn't be a
> problem from the standpoint of not being able to resolve calls that we
> could resolve before.
>
> Nonetheless, there *is* an ambiguity here, which is specific to json(b):
> what type of array are you expecting to get? The reason we have both
> json[b]_array_elements() and json[b]_array_elements_text() is that there
> are plausible use-cases for returning either json or plain text. It's not
> hard to imagine that somebody will want json[b]_array_elements_numeric()
> before long, too. If you want to have an unnest(jsonb) then you will need
> to make an arbitrary decision about which type it will return, and that
> doesn't seem like an especially great idea to me.
How about making casts work? UNNEST(jsonb)::NUMERIC or similar,
whatever won't make the parser barf.
> > UNNEST, and ROWS FROM have more capabilities including WITH ORDINALITY
> > than the json_array_elements-like functions do.
>
> AFAICT, this is nonsense. We did not tie WITH ORDINALITY to UNNEST;
> it works for any set-returning function.
Oops. My mistake. Sorry about the noise.
Cheers,
David.
--
David Fetter <da...@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] JSON[B] arrays are second-class citizens
On Tue, May 31, 2016 at 05:06:00PM -0400, David G. Johnston wrote:
> On Tue, May 31, 2016 at 4:34 PM, David Fetter <da...@fetter.org> wrote:
>
> > Folks,
> >
> > While querying some JSONB blobs at work in preparation for a massive
> > rework of the data infrastructure, I ran into things that really
> > puzzled me, to wit:
> >
> > SELECT * FROM unnest('["a","b","c"]'::jsonb);
> > ERROR: function unnest(jsonb) does not exist
> >
> > SELECT * FROM jsonb_array_elements('["a","b","c"]'::jsonb);
> > value
> > ───
> > "a"
> > "b"
> > "c"
> > (3 rows)
> I'd be inclined to -1 such a proposal. TIMTOWTDI is not a principle that
> we endeavor to emulate.
You cut out the part where I introduced the part that's not
equivalent, so "there is more than one way to do it" isn't on point
here. More on that specific issue below.
UNNEST, and ROWS FROM have more capabilities including WITH ORDINALITY
than the json_array_elements-like functions do. Is it really more
efficient to build and maintain those capabilities separately in the
JSON[B] set-returning functions, or to force our end users to use
atrocious hacks like putting
generate_series(1,jsonb_array_length(foo)) in the target list than
just to make UNNEST and ROWS FROM do the right thing?
> Having an overloaded form: <unnest(jsonb) : setof jsonb> is unappealing.
On what grounds?
> While likely not that common the introduction of an ambiguity makes
> raises the bar considerably.
What ambiguity?
SELECT jsonb_array_elements('{"a":2,"b":1}'::jsonb);
ERROR: cannot extract elements from an object
The json_array_elements family manages to do the right thing. Why
would it be harder to make sure UNNEST and ROWS FROM() do so?
> That said we do seem to be lacking any easy way to take a json array and
> attempt to convert it directly into a PostgreSQL array. Just a conversion
> is not always going to succeed though the capability seems worthwhile if as
> yet unasked for. The each->convert->array_agg pattern works but is likely
> inefficient for homogeneous json array cases.
To your earlier point about "there is more than one way to do it," we
have made no attempt to make some sort of language composed of
orthonormal vectors, and the SQL standard actually requires that we
not do so. For a trivial example, there's
SELECT * FROM foo
and
TABLE foo
which are equivalent and both spelled out in the standard.
Cheers,
David.
--
David Fetter <da...@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] JSON[B] arrays are second-class citizens
Folks,
While querying some JSONB blobs at work in preparation for a massive
rework of the data infrastructure, I ran into things that really
puzzled me, to wit:
SELECT * FROM unnest('["a","b","c"]'::jsonb);
ERROR: function unnest(jsonb) does not exist
SELECT * FROM jsonb_array_elements('["a","b","c"]'::jsonb);
value
───
"a"
"b"
"c"
(3 rows)
Similar things happen with the other functions matching
jsonb?_array_elements(_text)?
These functions correctly identify JSON[B] things which are not, at
their top level, arrays, and error out appropriately.
What this hints to me is that json_array_elements() and friends have
access to things that at least in theory UNNEST could have access to.
Is making those things accessible to UNNEST, etc., a reasonable
direction to go?
Another option I came up with is to make functions that match
jsonb?_array_elements(_text)?(_with_ordinality), but that seems
somewhat tedious and error-prone on the maintenance side.
What say?
Cheers,
David.
--
David Fetter <da...@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] PostgreSQL Weekly News Survey
Folks, In lieu of the weekly news this week, I'd like to as you to help improve the PostgreSQL Weekly News by participating in this survey. http://goo.gl/forms/Q6FPPwzPG3Zq66pC2 Thanks in advance, and please let me know if you have any trouble reaching or using the survey. Cheers, David. -- David Fetter <da...@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Allow COPY to use parameters
On Tue, May 24, 2016 at 02:16:40PM -0400, Tom Lane wrote:
> David Fetter <da...@fetter.org> writes:
> > Per discussion on IRC and some test code, COPY can't take parameters
> > in a PREPARE, which feature would make it even more useful.
>
> Uh, what?
>
> regression=# prepare foo as copy c from stdin;
> ERROR: syntax error at or near "copy"
> LINE 1: prepare foo as copy c from stdin;
>^
>
> Passing parameters into a utility statement of any stripe is a
> pretty considerable project, IIRC; the infrastructure isn't there.
Maybe it should be, at least for some of the utility statements.
Please find attached a patch which, according to Andrew Gierth, its
author, just barely qualifies as a PoC. Yes, it's had to break a
couple of messages in the regression tests.
Cheers,
David.
--
David Fetter <da...@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 3201476..66ae54e 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -279,12 +279,12 @@ static const char BinarySignature[11] =
"PGCOPY\n\377\r\n\0";
/* non-export function prototypes */
-static CopyState BeginCopy(bool is_from, Relation rel, Node *raw_query,
+static CopyState BeginCopy(bool is_from, Relation rel, Node *raw_query,
ParamListInfo params,
const char *queryString, const Oid queryRelId, List
*attnamelist,
List *options);
static void EndCopy(CopyState cstate);
static void ClosePipeToProgram(CopyState cstate);
-static CopyState BeginCopyTo(Relation rel, Node *query, const char
*queryString,
+static CopyState BeginCopyTo(Relation rel, Node *query, ParamListInfo params,
const char *queryString,
const Oid queryRelId, const char *filename, bool
is_program,
List *attnamelist, List *options);
static void EndCopyTo(CopyState cstate);
@@ -787,7 +787,7 @@ CopyLoadRawBuf(CopyState cstate)
* the table or the specifically requested columns.
*/
Oid
-DoCopy(const CopyStmt *stmt, const char *queryString, uint64 *processed)
+DoCopy(const CopyStmt *stmt, const char *queryString, ParamListInfo params,
uint64 *processed)
{
CopyState cstate;
boolis_from = stmt->is_from;
@@ -944,7 +944,7 @@ DoCopy(const CopyStmt *stmt, const char *queryString,
uint64 *processed)
}
else
{
- cstate = BeginCopyTo(rel, query, queryString, relid,
+ cstate = BeginCopyTo(rel, query, params, queryString, relid,
stmt->filename,
stmt->is_program,
stmt->attlist,
stmt->options);
*processed = DoCopyTo(cstate); /* copy from database to file */
@@ -1321,6 +1321,7 @@ static CopyState
BeginCopy(bool is_from,
Relation rel,
Node *raw_query,
+ ParamListInfo params,
const char *queryString,
const Oid queryRelId,
List *attnamelist,
@@ -1391,11 +1392,16 @@ BeginCopy(bool is_from,
* function and is executed repeatedly. (See also the same
hack in
* DECLARE CURSOR and PREPARE.) XXX FIXME someday.
*/
- rewritten = pg_analyze_and_rewrite((Node *)
copyObject(raw_query),
-
queryString, NULL, 0);
+ if (!IsA(raw_query,List))
+ {
+ rewritten = pg_analyze_and_rewrite((Node *)
copyObject(raw_query),
+
queryString, NULL, 0);
+ }
+ else
+ rewritten = (List *) raw_query;
/* check that we got back something we can work with */
- if (rewritten == NIL)
+ if (rewritten == NIL || linitial(rewritten) == NIL)
{
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -1453,7 +1459,7 @@ BeginCopy(bool is_from,
}
/* plan the query */
- plan = pg_plan_query(query, 0, NULL);
+ plan = pg_plan_query(query, 0, params);
/*
* With row level security and a user using "COPY relation TO",
we
@@ -1495,7 +1501,7 @@ BeginCopy(bool is_from,
cstate->queryDesc = CreateQueryDesc(plan, queryString,
[HACKERS] Allow COPY to use parameters
Folks, Per discussion on IRC and some test code, COPY can't take parameters in a PREPARE, which feature would make it even more useful. To make this work, we'd need to: - do parse analysis immediately - parameterize all the options This doesn't seem like a gigantic lift. What say? Cheers, David. -- David Fetter <da...@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Changed SRF in targetlist handling
On Mon, May 23, 2016 at 02:39:54PM -0500, Merlin Moncure wrote: > On Mon, May 23, 2016 at 2:13 PM, David Fetter <da...@fetter.org> wrote: > > On Mon, May 23, 2016 at 01:28:11PM -0500, Merlin Moncure wrote: > >> On Mon, May 23, 2016 at 12:10 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > >> > Andres Freund <and...@anarazel.de> writes: > >> >> discussing executor performance with a number of people at pgcon, > >> >> several hackers - me included - complained about the additional > >> >> complexity, both code and runtime, required to handle SRFs in the target > >> >> list. > >> > > >> > Yeah, this has been an annoyance for a long time. > >> > > >> >> One idea I circulated was to fix that by interjecting a special executor > >> >> node to process SRF containing targetlists (reusing Result possibly?). > >> >> That'd allow to remove the isDone argument from ExecEval*/ExecProject* > >> >> and get rid of ps_TupFromTlist which is fairly ugly. > >> > > >> > Would that not lead to, in effect, duplicating all of execQual.c? The > >> > new > >> > executor node would still have to be prepared to process all expression > >> > node types. > >> > > >> >> Robert suggested - IIRC mentioning previous on-list discussion - to > >> >> instead rewrite targetlist SRFs into lateral joins. My gut feeling is > >> >> that that'd be a larger undertaking, with significant semantics changes. > >> > > >> > Yes, this was discussed on-list awhile back (I see David found a > >> > reference > >> > already). I think it's feasible, although we'd first have to agree > >> > whether we want to remain bug-compatible with the old > >> > least-common-multiple-of-the-periods behavior. I would vote for not, > >> > but it's certainly a debatable thing. > >> > >> +1 on removing LCM. > > > > As a green field project, that would make total sense. As a thing > > decades in, it's not clear to me that that would break less stuff or > > break it worse than simply disallowing SRFs in the target list, which > > has been rejected on bugward-compatibility grounds. I suspect it > > would be even worse because disallowing SRFs in target lists would at > > least be obvious and localized when it broke code. > > If I'm reading this correctly, it sounds to me like you are making the > case that removing target list SRF completely would somehow cause less > breakage than say, rewriting it to a LATERAL based implementation for > example. Yes. Making SRFs in target lists throw an error is a thing that will be pretty straightforward to deal with in extant code bases, whatever size of pain in the neck it might be. The line of code that caused the error would be very clear, and the fix would be very obvious. Making their behavior different in some way that throws no warnings is guaranteed to cause subtle and hard to track bugs in extant code bases. We lost not a few existing users when we caused similar knock-ons in 8.3 by removing automated casts to text. I am no longer advocating for removing the functionality. I am just pointing out that the knock-on effects of changing the functionality may well cause more pain than the ones from removing it entirely. > With more than a little forbearance, let's just say I don't agree. If you'd be so kind as to explain your reasons, I think we'd all benefit. Cheers, David. -- David Fetter <da...@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
