Re: [HACKERS] Rewriting backup.sgml (patch attached)

[Fabien COELHO]

Hello Joshua,

My 0.02 €:

Alright, per previous discussions I went through the backup.sgml page. I have 
gone thoroughly through:


sql dump
pg_dump
pg_restore
handling large databases

I removed file based backups


I strongly disagree:

The section is interesting at least for the arguments that explain why 
this approach has drawbacks. These caveats are important and should be 
preserved. Moreover people may be happy with filesystem snapshots, why 
not. Probably having a database running on top of such a journaled FS is 
not the best choice, but it may happen for good reasons.


The point of a documentation is not to hide options, but to allow the 
admin to do informed choices depending on her environment and constraints 
that you do not know about, and help her to implement the chosen option 
correctly and effectively. An option with drawbacks may be the good one.


I would rather emphasize the caveats, if this is the desired effect, say 
adding more "caution" or "warning" here and there, than removing this 
section altogether. Maybe move the troublesome option as the last one. But 
not remove it.



I do not want to progress any farther on this until I get some feedback.



  -  There are three fundamentally different approaches to backing up
  +  There are four different approaches to backing up
 PostgreSQL data:
 
  SQL dump
  -   File system level backup
  -   Continuous archiving
  +   PITR, Point in Time
   Recovery


I'm not sure that the "four" announced in from of a list of 2 items is 
right...


  -After restoring a backup, it is wise to run Docs are too often impersonal boring things, in competition with standards 
on that account. The occasional light tone and style, if it does not 
hinder clarity and comprehension, is not necessarily a bad thing.


Maybe a summary of backup approaches, maybe in the form of a table, could 
be a good thing at the end of the chapter introduction.


The introduction may emphasize the need to have tested backup plans.

You have added a warning about the fact that pg_dump does not save all the 
necessary data to restore the database. Good. I would suggest to go a step 
further and discuss that it needs to be considered at the beginning of the 
"SQL Dump" section, and maybe even in the chapter introduction.


Otherwise, the reorganized, detailed and explained options and practical 
advices around pg_dump looks good.


--
Fabien.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] jsonb concatenate operator's semantics seem questionable

[Peter Geoghegan]

On Tue, May 19, 2015 at 10:43 PM, Petr Jelinek  wrote:
> I am of strong opinion that concat should be shallow by default. Again it's
> how jquery works by default, it's how python's dict.update works and you can
> find this behavior in other languages as well when dealing with nested
> hashes. It's also how json would behave if you'd just did string
> concatenation (removing the outermost curly brackets) and parse it to json
> afterwards.

As I said, that argument might be a good one if you were able to
subscript jsonb and have the update affect one particular subdocument.
You're not, though -- updating jsonb usually requires you to write an
SQL expression that evaluates to the final jsonb document that you'd
like to update a record to contain.

> I think this whole discussion shows primarily that it's by far not
> universally agreed if concatenation of json should be shallow or deep by
> default and AFAICS this is true even in javascript world so we don't really
> have where to look for precedents.
>
> Given the above I would vote to just provide the function and leave out the
> || operator for now.

I've said my piece; I think it's a mistake to use an operator that has
a certain association, the association that the concatenate operate
got from hstore. || is the operator broadly useful for updates in
people's minds. I think this *positioning* of the operator is a
mistake. I'll leave it at that.

-- 
Peter Geoghegan


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Minor ON CONFLICT related fixes

[Peter Geoghegan]

On Tue, May 19, 2015 at 2:23 PM, Andres Freund  wrote:
> Pushed.

I eyeballed the commit, and realized that I made a trivial error. New
patch attached fixing that.

Sorry for not getting this fix completely right first time around.
Don't know how I missed it.
-- 
Peter Geoghegan
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 8cdef08..0585251 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -5500,7 +5500,7 @@ get_insert_query_def(Query *query, deparse_context *context)
 get_rule_expr(confl->arbiterWhere, context, false);
 			}
 		}
-		else
+		else if (confl->constraint != InvalidOid)
 		{
 			char   *constraint = get_constraint_name(confl->constraint);
 

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] jsonb concatenate operator's semantics seem questionable

[Petr Jelinek]

On 20/05/15 01:38, Jim Nasby wrote:

On 5/18/15 3:15 PM, Marko Tiikkaja wrote:

On 2015-05-18 22:10, Josh Berkus wrote:

On 05/18/2015 01:04 PM, Ryan Pedela wrote:

In the context of splitting shallow and deep merge into two
operators, I
think + is better for shallow and || better for deep. The reason for +
is because many programming languages have this behavior. If I see the
below code in language I have never used before:

objC = objA + objB

My default assumption is that + performs a shallow merge. Like I
said, I
would rather there just be one operator.


Thank you, that helps.  Anyone else?


If everyone thinks the operators mean different things, we could just
not add any operators and only provide functions instead.


My $0.02: I would expect || to be what I want to use to add something to
an existing JSON document, no matter what the path of what I'm adding
is. In other words, deep merge. I certainly wouldn't expect it to be
shallow.

If we get this wrong now, we'll be stuck with it forever. At a minimum I
think we should use anything other than || until we can figure this out.
That leaves || available for whichever case we decide on.



I am of strong opinion that concat should be shallow by default. Again 
it's how jquery works by default, it's how python's dict.update works 
and you can find this behavior in other languages as well when dealing 
with nested hashes. It's also how json would behave if you'd just did 
string concatenation (removing the outermost curly brackets) and parse 
it to json afterwards.


I think this whole discussion shows primarily that it's by far not 
universally agreed if concatenation of json should be shallow or deep by 
default and AFAICS this is true even in javascript world so we don't 
really have where to look for precedents.


Given the above I would vote to just provide the function and leave out 
the || operator for now.


--
 Petr Jelinek  http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Change pg_cancel_*() to ignore current backend

[Jim Nasby]

On 5/19/15 9:19 PM, Fabrízio de Royes Mello wrote:

We could add a second parameter to the current functions:
allow_own_pid DEFAULT false. To me that seems better than an
entirely separate set of functions.


+1 to add a second parameter to current functions.


Instead of allow_own_pid, I went with skip_own_pid. I have the function 
still returning true even when it skips it's own PID... that seems a bit 
weird, but I think it's better than returning false. Unless someone 
thinks it should return NULL, but I don't see that as any better either.

--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 89a609f..b405876 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -16508,7 +16508,7 @@ SELECT set_config('log_statement_stats', 'off', false);
  
   

-pg_cancel_backend(pid 
int)
+pg_cancel_backend(pid 
int, skip_my_pid boolean 
)
 
boolean
Cancel a backend's current query.  This is also allowed if the
@@ -16532,7 +16532,7 @@ SELECT set_config('log_statement_stats', 'off', false);
   
   

-pg_terminate_backend(pid 
int)
+pg_terminate_backend(pid 
int, skip_my_pid boolean 
)
 
boolean
Terminate a backend.  This is also allowed if the calling role
@@ -16562,6 +16562,10 @@ SELECT set_config('log_statement_stats', 'off', false);
 The role of an active backend can be found from the
 usename column of the
 pg_stat_activity view.
+
+   There is an optional second parameter of type boolean.  If
+   true (the default), pg_cancel_backend and
+   pg_terminate_backend will not signal the current backend.

 

diff --git a/src/backend/catalog/system_views.sql 
b/src/backend/catalog/system_views.sql
index 18921c4..a0cc975 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -869,6 +869,14 @@ COMMENT ON FUNCTION ts_debug(text) IS
 --
 
 CREATE OR REPLACE FUNCTION
+  pg_cancel_backend(pid int, skip_my_pid boolean DEFAULT true)
+  RETURNS boolean STRICT VOLATILE LANGUAGE internal AS 'pg_cancel_backend';
+
+CREATE OR REPLACE FUNCTION
+  pg_terminate_backend(pid int, skip_my_pid boolean DEFAULT true)
+  RETURNS boolean STRICT VOLATILE LANGUAGE internal AS 'pg_terminate_backend';
+
+CREATE OR REPLACE FUNCTION
   pg_start_backup(label text, fast boolean DEFAULT false)
   RETURNS pg_lsn STRICT VOLATILE LANGUAGE internal AS 'pg_start_backup';
 
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index 61d609f..dce8498 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -94,8 +94,12 @@ current_query(PG_FUNCTION_ARGS)
 #define SIGNAL_BACKEND_NOPERMISSION 2
 #define SIGNAL_BACKEND_NOSUPERUSER 3
 static int
-pg_signal_backend(int pid, int sig)
+pg_signal_backend(int pid, int sig, bool skip_own_pid)
 {
+   /* Skip our own pid unless we're told not to */
+   if (skip_own_pid && pid == MyProcPid)
+   return SIGNAL_BACKEND_SUCCESS;
+
PGPROC *proc = BackendPidGetProc(pid);
 
/*
@@ -158,7 +162,7 @@ pg_signal_backend(int pid, int sig)
 Datum
 pg_cancel_backend(PG_FUNCTION_ARGS)
 {
-   int r = pg_signal_backend(PG_GETARG_INT32(0), 
SIGINT);
+   int r = pg_signal_backend(PG_GETARG_INT32(0), 
SIGINT, PG_GETARG_BOOL(1));
 
if (r == SIGNAL_BACKEND_NOSUPERUSER)
ereport(ERROR,
@@ -182,7 +186,7 @@ pg_cancel_backend(PG_FUNCTION_ARGS)
 Datum
 pg_terminate_backend(PG_FUNCTION_ARGS)
 {
-   int r = pg_signal_backend(PG_GETARG_INT32(0), 
SIGTERM);
+   int r = pg_signal_backend(PG_GETARG_INT32(0), 
SIGTERM, PG_GETARG_BOOL(1));
 
if (r == SIGNAL_BACKEND_NOSUPERUSER)
ereport(ERROR,
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index b5b9345..475545b 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -3128,9 +3128,9 @@ DESCR("get OID of current session's temp schema, if any");
 DATA(insert OID = 2855 (  pg_is_other_temp_schema  PGNSP PGUID 12 1 0 0 0 
f f f f t f s 1 0 16 "26" _null_ _null_ _null_ _null_ _null_ 
pg_is_other_temp_schema _null_ _null_ _null_ ));
 DESCR("is schema another session's temp schema?");
 
-DATA(insert OID = 2171 ( pg_cancel_backend PGNSP PGUID 12 1 0 0 0 
f f f f t f v 1 0 16 "23" _null_ _null_ _null_ _null_ _null_ pg_cancel_backend 
_null_ _null_ _null_ ));
+DATA(insert OID = 2171 ( pg_cancel_backend PGNSP PGUID 12 1 0 0 0 
f f f f t f v 2 0 16 "23 16" _null_ _null_ _null_ _null_ _null_ 
pg_cancel_backend _null_ _null_ _null_ ));
 DESCR("cancel a server process' current query");
-DATA(insert OID = 2096 ( pg_terminate_backend  PGNSP PGUID 12 1 0 0 0 
f f f f t f v 1 0 16 "23" _nu

Re: [HACKERS] CTE optimization fence on the todo list?

[Chris Rogers]

I need this feature a lot.  Can anyone point me to a place in the code
where I can hack together a quick-and-dirty, compatibility-breaking
implementation?  Thanks!

On Sun, May 3, 2015 at 10:03 PM, Jim Nasby  wrote:

> On 5/3/15 11:59 AM, Andrew Dunstan wrote:
>
>>
>> On 05/03/2015 11:49 AM, Tom Lane wrote:
>>
>>> Andrew Dunstan  writes:
>>>
 On 05/01/2015 07:24 PM, Josh Berkus wrote:

> (A possible compromise position would be to offer a new GUC to
>> enable/disable the optimization globally; that would add only a
>> reasonably
>> small amount of control code, and people who were afraid of the change
>> breaking their apps would probably want a global disable anyway.)
>>
> This could be a very bad, almost impossible to catch, behaviour break.
 Even if we add the GUC, we're probably going to be imposing very
 significant code audit costs on some users.

>>> On what grounds do you claim it'd be a behavior break?  It's possible
>>> that the subquery flattening would result in less-desirable plans not
>>> more-desirable ones, but the results should still be correct.
>>>
>>
>> I meant w.r.t. performance. Sorry if that wasn't clear.
>>
>
> To put this in perspective... I've seen things like this take query
> runtime from minutes to multiple hours or worse; bad enough that "behavior
> break" becomes a valid description.
>
> We definitely need to highlight this in the release notes, and I think the
> GUC would be mandatory.
> --
> Jim Nasby, Data Architect, Blue Treble Consulting
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

[HACKERS] small typo

[Euler Taveira]

Hi,

Attached is a small typo.


-- 
   Euler Taveira   Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c
index 98016fc..761c277 100644
--- a/contrib/pg_buffercache/pg_buffercache_pages.c
+++ b/contrib/pg_buffercache/pg_buffercache_pages.c
@@ -142,7 +142,7 @@ pg_buffercache_pages(PG_FUNCTION_ARGS)
 			LWLockAcquire(BufMappingPartitionLockByIndex(i), LW_SHARED);
 
 		/*
-		 * Scan though all the buffers, saving the relevant fields in the
+		 * Scan through all the buffers, saving the relevant fields in the
 		 * fctx->record structure.
 		 */
 		for (i = 0; i < NBuffers; i++)

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Back-branch update releases planned for next week

[Tom Lane]

I wrote:
> In view of the multixactid wraparound issues that have gotten fixed over
> the last week or two, it's time to put out some minor releases.  After
> some discussion among core and the packagers list, we concluded that we
> should do it next week (before the Memorial Day holiday).  As per usual
> timing, we'll wrap tarballs Monday the 18th for public announcement
> Thursday the 21st.

We encountered some, er, unplanned difficulties, which forced a re-wrap
of the tarballs for 9.4.2 et al.  To allow the packagers their usual
amount of time to build packages, this week's releases will be announced
on Friday the 22nd, not Thursday as would be the normal schedule.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Change pg_cancel_*() to ignore current backend

[Fabrízio de Royes Mello]

Em terça-feira, 19 de maio de 2015, Jim Nasby 
escreveu:

> On 5/19/15 6:30 PM, David G. Johnston wrote:
>
>> On Tue, May 19, 2015 at 4:23 PM, Marko Tiikkaja > >wrote:
>>
>> On 2015-05-20 00:59, Jim Nasby wrote:
>>
>> I find it annoying to have to specifically exclude
>> pg_backend_pid() from
>> pg_stat_activity if I'm trying to kill a bunch of backends at
>> once, and
>> I can't think of any reason why you'd ever want to call a
>> pg_cancel_*
>> function with your own PID.
>>
>>
>> That's a rather easy way of testing that you're handling FATAL
>> errors correctly from a driver/whatever.
>>
>>
>> I'm having trouble thinking of a PC name for the function we create that
>> should do this; while changing the pg_cancel_* functions to operate more
>> safely.
>>
>
> We could add a second parameter to the current functions: allow_own_pid
> DEFAULT false. To me that seems better than an entirely separate set of
> functions.
>
>
+1 to add a second parameter to current functions.




-- 
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello

Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION

[Stephen Frost]

* Simon Riggs (si...@2ndquadrant.com) wrote:
> On 19 May 2015 at 16:49, Robert Haas  wrote:
> 
> > On Tue, May 19, 2015 at 3:00 PM, Simon Riggs 
> > wrote:
> > > As long as the cookie is randomly generated for each use, then I don't
> > see a
> > > practical problem with that approach.
> >
> > If the client sets the cookie via an SQL command, that command would
> > be written to the log, and displayed in pg_stat_activity.  A malicious
> > user might be able to get it from one of those places.
> >
> > A malicious user might also be able to just guess it.  I don't really
> > want to create a situation where any weakess in pgpool's random number
> > generation becomes a privilege-escalation attack.
> >
> > A protocol extension avoids all of that trouble, and can be target for
> > 9.6 just like any other approach we might come up with.  I actually
> > suspect the protocol extension will be FAR easier to fully secure, and
> > thus less work, not more.
> 
> That's a reasonable argument. So +1 to protocol from me.
> 
> To satisfy Tom, I think this would need to have two modes: one where the
> session can never be reset, for ultra security, and one where the session
> can be reset, which allows security and speed of pooling.

For my 2c, I continue to agree with a protocol-based approach, but I
don't think having two modes would actually satisfy concerns regarding
the security- we're still going to have to fix any issues which are
security related that come up from having the "session able to be reset"
mode.

That said, we know connection poolers are already using SET SESSION AUTH
(which is clearly far worse than what we're proposing to do here..) and
clearly we support SET ROLE, so any issues with those methods really
should be getting addressed anyway.  Perhaps we can continue to beg off
in the SET SESSION AUTH case by hiding behind "you're a superuser" or
"you're using it wrong" but that doesn't actually make anyone more
secure and we clearly need to address the SET ROLE case, as that is
absolutely expected to work correctly.

As for the discussion regarding having a connection pooler built-in-
that is absolutely something we need to do, in my view, because any
external connection pooler isn't going to offer the same set of
capabilities that core does and we continue to fight with the concerns
around changing the wireline protocol which hamstrings our progress in
this area.  That isn't to say it's all roses if we just built it in,
because clearly it's not and there's work to be done there, but a
connection pooler which is tied closely to core and which is upgraded
and deployed with it could be much more easily changed and improved.  On
the other hand, I'd really like to see improvement to our protocol too
and perhaps this is a way to get those, though it hasn't been happening
so far, unfortunately.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] upper planner path-ification

[Kyotaro HORIGUCHI]

At Tue, 19 May 2015 09:04:00 -0400, Robert Haas  wrote 
in 
> On Tue, May 19, 2015 at 7:19 AM, Andrew Gierth
>  wrote:
> >> "Tom" == Tom Lane  writes:
> >  Tom> Hm.  That's a hangover from when query_planner also gave back a
> >  Tom> Plan (singular) rather than a set of Paths.  I don't see any
> >  Tom> fundamental reason why we couldn't generalize it to be a list of
> >  Tom> potentially useful output orderings rather than just one.  But I'm
> >  Tom> a bit concerned about the ensuing growth in planning time; is it
> >  Tom> really all that useful?
> >
> > The planning time growth is a possible concern, yes. The potential gain
> > is eliminating one sort step, in the case when the input has a usable
> > sorted path but grouping_planner happens not to ask for it (when there's
> > more than just a single rollup, the code currently asks for one of the
> > sort orders pretty much arbitrarily since it has no real way to know
> > otherwise). Whether that would justify it... I don't know. Maybe that's
> > one to save for later to see if there's any feedback from actual use.
> 
> I kind of doubt that the growth in planning time would be anything too
> unreasonable.  We already consider multiple orderings for ordinary
> base relations, so it's not very obvious why consideration multiple
> orderings for subqueries would be any worse.  If we can arrange to
> throw away useless orderings early, as we do in other cases, then any
> extra paths we consider have a reasonable chance of being useful.

Though I don't think that the simple path-ification of what is
currently done make it grow in any degree, it could rapidly grow
if we unconditionally construct extra upper-paths using the
previously-abandoned extra paths or make them involved in join
considerations. But the growth in planning time could be kept
reasonable if we pay attention so that, as we have done so, the
additional optimization schemes to have simple and precise
bailing-out logic even if they require some complicated
calculation or yields more extra paths.

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Change pg_cancel_*() to ignore current backend

[Jim Nasby]

On 5/19/15 6:30 PM, David G. Johnston wrote:

On Tue, May 19, 2015 at 4:23 PM, Marko Tiikkaja mailto:ma...@joh.to>>wrote:

On 2015-05-20 00:59, Jim Nasby wrote:

I find it annoying to have to specifically exclude
pg_backend_pid() from
pg_stat_activity if I'm trying to kill a bunch of backends at
once, and
I can't think of any reason why you'd ever want to call a
pg_cancel_*
function with your own PID.


That's a rather easy way of testing that you're handling FATAL
errors correctly from a driver/whatever.


I'm having trouble thinking of a PC name for the function we create that
should do this; while changing the pg_cancel_* functions to operate more
safely.


We could add a second parameter to the current functions: allow_own_pid 
DEFAULT false. To me that seems better than an entirely separate set of 
functions.

--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] INSERT ... ON CONFLICT DO UPDATE with _any_ constraint

[Peter Geoghegan]

On Tue, May 19, 2015 at 5:03 PM, Tom Lane  wrote:
> Peter Geoghegan  writes:
>> I think I agree with you, though: We should change things so that the
>> relcache gives indexes in something like the ordering that you
>> outline, rather than in the current arbitrary (though consistent) OID
>> order.
>
> I'm fairly sure that there are aspects of the code that rely on indexes
> being returned by RelationGetIndexList() in a stable order.  While I doubt
> that has to be exactly increasing-OID-order, I'm quite concerned about
> allowing the order to depend on mutable aspects of the indexes, like
> names.

I thought the importance of the ordering was just down to some AMs
(like hash) using heavyweight locks. This could cause unprincipled
deadlocks in the face of an inconsistent ordering. nbtree used to use
page-level heavyweight locks many years ago, too, so this used to be a
big, obvious requirement. Maybe there is another reason, but AFAICR
there are no hints of that from the relevant code, and I've looked
carefully.

If it was ever changed, I think it could be done in a way that didn't
add any problems, assuming I've accounted for all the ways in which
changing the ordering could be problematic.
-- 
Peter Geoghegan


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] INSERT ... ON CONFLICT DO UPDATE with _any_ constraint

[Tom Lane]

Peter Geoghegan  writes:
> I think I agree with you, though: We should change things so that the
> relcache gives indexes in something like the ordering that you
> outline, rather than in the current arbitrary (though consistent) OID
> order.

I'm fairly sure that there are aspects of the code that rely on indexes
being returned by RelationGetIndexList() in a stable order.  While I doubt
that has to be exactly increasing-OID-order, I'm quite concerned about
allowing the order to depend on mutable aspects of the indexes, like
names.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] INSERT ... ON CONFLICT DO UPDATE with _any_ constraint

[Peter Geoghegan]

On Tue, May 19, 2015 at 2:28 PM, Simon Riggs  wrote:
> On 19 May 2015 at 17:10, Peter Geoghegan  wrote:
>>
>> On Tue, May 19, 2015 at 1:57 PM, Simon Riggs 
>> wrote:
>> > We should allow DO UPDATE to exclude a constraint and apply a
>> > deterministic
>> > order to the constraints. 1. PK if it exists. 2. Replica Identity, when
>> > not
>> > PK, 3. UNIQUE constraints in name order, like triggers, so users can
>> > define
>> > a default evaluation order, just like they do with triggers.
>>
>> That seems like something way worse than just allowing it for all
>> constraints.
>
>
> I'm talking about the evaluation order; it would still match all
> constraints, otherwise they wouldn't be constraints.

But it doesn't match all constraints when a would-be conflict is
detected. IOW, we lock the row and go to UPDATE, and then the user is
on their own insofar as avoiding duplicate violations goes. What might
have happened in other unique indexes (had that original would-be dup
violation not occurred) is irrelevant (with the MySQL thing, say) --
you better just get it right, and know that if a dup violation occurs
it was the one you anticipated (e.g. because there is only one unique
index anyway). With Postgres, we want to make sure that the user has
put thought into the condition they take that update path on, and so
it is mandatory (it can infer multiple unique indexes, but only when
they're basically equivalent for this purpose).

I think I agree with you, though: We should change things so that the
relcache gives indexes in something like the ordering that you
outline, rather than in the current arbitrary (though consistent) OID
order. However, I think that this should be done to avoid unnecessary
index bloat (fail early), and I don't think it makes much sense to do
it on the grounds you outline. This is because you can still easily
take the alternative path for the wrong reason, causing subtle
"logical corruption". You can still not match all indexes because one
index had a would-be dup violation (and so, as I said, it doesn't
matter what would have happened with the other ones). Maybe you still
get a dup violation from the update, "saving" you, but who wants to
rely on that?

>> > 2) Compatibility with MySQL
>>
>> But what you describe isn't compatible with MySQL. It's totally novel.
>
>
> Upthread you said
>
> "It's trivial to modify Postgres to not require that a specific unique
> index be inferred, so that you can omit the inference specification
> for DO UPDATE just as you can for DO NOTHING. That would make it work
> in a similar way to MySQL"
>
> Similar is good and useful. Full compatibility is even better.

I actually do not feel strongly that it would be terrible to allow the
user to omit an inference clause for the DO UPDATE variant (on the
grounds of that being closer to MySQL). After all, we don't mandate
that the user specifies an explicit targetlist for INSERT, and that
seems like a footgun to me. If you want to make the case for doing
things that way, I probably will not oppose it. FWIW, I don't think
it's unreasonable to have a little discussion on fine points of
semantics like that post feature-freeze.

-- 
Peter Geoghegan


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] jsonb concatenate operator's semantics seem questionable

[Jim Nasby]

On 5/18/15 3:15 PM, Marko Tiikkaja wrote:

On 2015-05-18 22:10, Josh Berkus wrote:

On 05/18/2015 01:04 PM, Ryan Pedela wrote:

In the context of splitting shallow and deep merge into two operators, I
think + is better for shallow and || better for deep. The reason for +
is because many programming languages have this behavior. If I see the
below code in language I have never used before:

objC = objA + objB

My default assumption is that + performs a shallow merge. Like I said, I
would rather there just be one operator.


Thank you, that helps.  Anyone else?


If everyone thinks the operators mean different things, we could just
not add any operators and only provide functions instead.


My $0.02: I would expect || to be what I want to use to add something to 
an existing JSON document, no matter what the path of what I'm adding 
is. In other words, deep merge. I certainly wouldn't expect it to be 
shallow.


If we get this wrong now, we'll be stuck with it forever. At a minimum I 
think we should use anything other than || until we can figure this out. 
That leaves || available for whichever case we decide on.


BTW, if people are set on shallow merge being || then I'd suggest ||| as 
the deep merge operator.

--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Change pg_cancel_*() to ignore current backend

[David G. Johnston]

On Tue, May 19, 2015 at 4:23 PM, Marko Tiikkaja  wrote:

> On 2015-05-20 00:59, Jim Nasby wrote:
>
>> I find it annoying to have to specifically exclude pg_backend_pid() from
>> pg_stat_activity if I'm trying to kill a bunch of backends at once, and
>> I can't think of any reason why you'd ever want to call a pg_cancel_*
>> function with your own PID.
>>
>
> That's a rather easy way of testing that you're handling FATAL errors
> correctly from a driver/whatever.
>
>
I'm having trouble thinking of a PC name for the function we create that
should do this; while changing the pg_cancel_* functions to operate more
safely.

David J.​

Re: [HACKERS] Change pg_cancel_*() to ignore current backend

[Marko Tiikkaja]

On 2015-05-20 00:59, Jim Nasby wrote:

I find it annoying to have to specifically exclude pg_backend_pid() from
pg_stat_activity if I'm trying to kill a bunch of backends at once, and
I can't think of any reason why you'd ever want to call a pg_cancel_*
function with your own PID.


That's a rather easy way of testing that you're handling FATAL errors 
correctly from a driver/whatever.



.m


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[Jan de Visser]

On May 19, 2015 09:31:32 PM Greg Sabino Mullane wrote:
> Jan de Visser wrote:
> >> Well, one could argue that it *is* their problem, as they should be using
> >> the standard Postgres way for placeholders, which is $1, $2, $3...
> > 
> > Shirley you are joking: Many products use JDBC as an abstraction layer
> > facilitating (mostly) seamless switching between databases. I know the
> > product I worked on did. Are you advocating that every single statement
> > should use "SELECT * FROM foo WHERE bar = $1" on pg and "SELECT * FROM
> > foo WHERE bar = ?" on every other database?
> 
> I'm not joking, and don't call me Shirley. If you are running into
> situations where you have question mark operators in your queries, you have
> already lost the query abstraction battle. There will be no seamless
> switching if you are using jsonb, hstore, ltree, etc. My statement was more
> about pointing out that Postgres already offers a complete placeholder
> system, which drivers are free to implement if they want.

I must have misunderstood you Shirley Greg, because to me it 
parsed as if you were suggesting (paraphrasing) "ah forget about those pesky 
standardized drivers and their pesky syntax requirements. Just use ours like a 
big boy".

I understand that once you start using '?' as (part of) operator names in your 
queries you're not portable anymore. I just thought that your proposed 
solution was to throw all portability out the window. But I was probably 
(hopefully?) wrong.

jan



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Making the regression tests halt to attach a debugger

[Jim Nasby]

On 5/18/15 12:15 PM, Andrew Dunstan wrote:

On 05/18/2015 01:05 PM, Tom Lane wrote:

Meh.  You could also add "select pg_backend_pid()" or some such.
But really, the way I generally do this is to run gdb via a script
that auto-attaches to the right postgres process if at all possible.
Removes the whole problem.



This should go on the wiki.


https://wiki.postgresql.org/wiki/Gdblive_script; and linked from gdb 
section of Developer FAQ.

--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Bug in jsonb minus operator

[Andrew Dunstan]

On 05/18/2015 10:52 PM, Peter Geoghegan wrote:

On Mon, May 18, 2015 at 7:11 AM, Andrew Dunstan  wrote:

Here's an patch along those lines. It seems to do the trick, at least for
your test case, and it has the merit of being very small, so small I'd like
to backpatch it - accepting jbvBinary as is in pushJsonbValue seems like a
bug to me.

Isn't that for the benefit of raw scalar pseudo arrays? The existing
comments above pushJsonbValue() acknowledge such callers.



Umm, no, the raw scalar pseudo arrays are of type jbvArray, not 
jbvBinary. And they are pushed with WJB_BEGIN_ARRAY, not with WJB_ELEM 
or WJB_VALUE, as the comment notes. See this fragment of code from 
JsonbValueToJsonb:


scalarArray.type = jbvArray;
scalarArray.val.array.rawScalar = true;
scalarArray.val.array.nElems = 1;

pushJsonbValue(&pstate, WJB_BEGIN_ARRAY, &scalarArray);


I tested this by removing the assert test for jbvBinary in the WJB_ELEM 
and WJB_VALUE switch beranches and then running the regression suite. No 
assertion failure was triggered. While that's not guaranteed to be a 
perfect test, it doesn't seem like a bad one. Can you pose a counter 
example where this will break?





Why are you passing the skipNested variable to JsonbIteratorNext()
within jsonb_delete()? I'm not seeing a need for that.



If you don't the logic gets more complex, as you need to keep track of 
what level of the object you are at. The virtue of this change is that 
it will simplify a lot of such processing by removing the unnecessary 
restriction on passing jbvBinary values to pushJsonbValue().


If you have a better fix for the bug you complained about I'll be happy 
to take a look.


cheers

andrew






--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Change pg_cancel_*() to ignore current backend

[Jim Nasby]

I find it annoying to have to specifically exclude pg_backend_pid() from 
pg_stat_activity if I'm trying to kill a bunch of backends at once, and 
I can't think of any reason why you'd ever want to call a pg_cancel_* 
function with your own PID.


Any objections to modifying those functions so they do nothing when 
handed the PID of the current backend?

--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[David G. Johnston]

On Tue, May 19, 2015 at 2:34 PM, Bruno Harbulot  wrote:

>
> While I can imagine a Java PostgreSQL driver that would use the libpq
> syntax, I can't see it being able to have any useful sort of
> half-compatibility with JDBC, whether it mimics its interfaces or not. I'm
> not sure it would be very useful at all, considering how much the existing
> tooling the the Java world relies on JDBC.
>
>
​I won't claim to have studied this in great detail but there is a lot more
to the JDBC spec beyond the semantics of "PreparedStatement.parse(String)".
No need to throw out the baby with the bath water and reinvent ResultSet,
Connection and various other interfaces that are perfectly usable before
and after a suitable query has been fully parsed.

When I say "setInteger(1, new Integer(1000))" I don't care whether I had to
write "SELECT ? AS int_val" OR "SELECT $1 AS int_val"; though the later has
the nice property of providing corresponding numbers so that I would write
something like "SELECT $1 AS int_val, $1 AS int_val_2" and not be forced to
write "setInteger(2, new Integer(1000))" to pass in a value to the second -
but identical - parameter.  Maybe it violates the semantics defined by the
API - which I could be making too lightly of - but having the same
mechanics involved to solve the same problem - with only minor semantic
nuances to remember seems within the realm of reasonable.

David J.

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[Bruno Harbulot]

On Tue, May 19, 2015 at 9:50 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Tue, May 19, 2015 at 1:36 PM, Kevin Grittner  wrote:
>
> Gavin Flower  wrote:
>>
>> > I prefer the $1 approach, others can't use that, and there are
>> > situations where I could not either.
>> >
>> > So, how about defaulting to the '?' approach, but have a method
>> > to explicitly set the mode - to switch to using '$'?
>>
>> Are you suggesting that we implement something other than what is
>> described in these documents for prepared statement parameters?:
>>
>> http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html
>>
>>
>> http://download.oracle.com/otn-pub/jcp/jdbc-4_1-mrel-spec/jdbc4.1-fr-spec.pdf
>>
>> If so, I strongly oppose that.  If we are not going to deprecate
>> use of the question mark character for operators, we need some
>> nonstandard hack to our JDBC implementation, but an alternative
>> syntax for specifying PreparedStatement and CallableStatement
>> parameters seems entirely the wrong way to go.
>>
>
> ​I'll repeat my earlier comment that having a mode that allows for libpq
> syntax while still conforming to the JDBC class API would have value for
> those users willing to admit their application and code is not portable
> (and if they are using these operators it is not) and would rather conform
> as closely to native PostgreSQL language mechanics as possible.​
>
>
I don't think that approach is workable at all. JDBC isn't limited to a
number of classes and their methods, the documentation that surrounds it
obviously has an impact on how it was implemented internally and what users
should and shouldn't be allowed to expect when using these classes. While
there are tools that convert various parameter styles to ? (e.g. Groovy SQL
or Hibernate's named parameter) and a layer of conversion from $1 to ?
could exist, the bottleneck here will still be the JDBC layer itself, since
it's what sends the query to the database.

Users of question mark operators are already admitting their application
and code isn't portable (since they are specific to PostgreSQL and its
extensions). The problem has more to do with how the other tools around
handle these customisations. For example, it can be useful to have a model
based on Hibernate in Java and be able to use ? operators for specific
features. (Other tools like SQLAlchemy in Python also allow you to have
customisations specific to the RDMBS platform, while being able to use the
core features in a more platform-neutral way.)

It turns out that you can indeed use ? in JSONB with a custom Hibernate
query, you just need to double-escape it as follows: ? becomes ?? and has
to be escaped as \?\?, but \ has to be escaped itself...

SQLQuery query = session
.createSQLQuery("SELECT
CAST((CAST('{\"key1\":123,\"key2\":\"Hello\"}' AS jsonb) \\?\\? CAST(? AS
text)) AS BOOLEAN)");
query.setString(0, "key1");

Again, this may have to do with the fact that these tools may have a
legitimate expectation that ? should be reserved for parameters, partly
because it seems to be very common in practice, but more importantly if the
SQL specification itself says it's what ? is for.

While I can imagine a Java PostgreSQL driver that would use the libpq
syntax, I can't see it being able to have any useful sort of
half-compatibility with JDBC, whether it mimics its interfaces or not. I'm
not sure it would be very useful at all, considering how much the existing
tooling the the Java world relies on JDBC.

This problem is also broader than JDBC: on top of the languages and
libraries already mentioned, it may affect ODBC, as Dave Cramer has just
said (I haven't tried).



Best wishes,

Bruno.

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


Jan de Visser wrote:
>> Well, one could argue that it *is* their problem, as they should be using
>> the standard Postgres way for placeholders, which is $1, $2, $3...

> Shirley you are joking: Many products use JDBC as an abstraction layer 
> facilitating (mostly) seamless switching between databases. I know the 
> product 
> I worked on did. Are you advocating that every single statement should use 
> "SELECT * FROM foo WHERE bar = $1" on pg and "SELECT * FROM foo WHERE bar = 
> ?" 
> on every other database?

I'm not joking, and don't call me Shirley. If you are running into situations 
where you have question mark operators in your queries, you have already lost 
the query abstraction battle. There will be no seamless switching if you 
are using jsonb, hstore, ltree, etc. My statement was more about pointing out 
that Postgres already offers a complete placeholder system, which drivers 
are free to implement if they want.

> A database is only as valuable as the the part of the outside world it can 
> interact with. Large parts of the data-consuming world are developed in java 
> using JDBC. If your opinion is that JDBC developers should adapt themselves 
> to 
> pg then you instantaneously diminish the value of pg.

Well, they will have to adapt to one way or another: using ?? or \? is doing 
so, and the other solution (Postgres adapting itself to the driver by 
deprecating the ? operator) is not realistically likely to happen.

- -- 
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201505191718
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAlVbq4AACgkQvJuQZxSWSsgrXgCaA6MTvbDeg2aMf+/HFnxutrqH
P1sAoLZB1w5+UXHMxXqW/Ex0q7GwoFds
=IOpS
-END PGP SIGNATURE-




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Rewriting backup.sgml (patch attached)

[Joshua D. Drake]

Hello,

Alright, per previous discussions I went through the backup.sgml page. I 
have gone thoroughly through:


sql dump
pg_dump
pg_restore
handling large databases

I removed file based backups

I didn't really touch the red headed step child that is pg_dumpall 
(although a word smithed it a little).


I tried to remove some of the conversationalist tone. This is technical 
documentation not a story. I also removed as many extra words as 
reasonable and added specific examples.


Now, I know some of you will say things like... why did you add -C to 
the pg_dump line. The reason is simple, without it as new person is 
going to get an error on restore. Our documentation should be explicit 
not implicit.


I do not want to progress any farther on this until I get some feedback.

Sincerely,

JD
--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.
diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml
index def43a2..cdc288b 100644
--- a/doc/src/sgml/backup.sgml
+++ b/doc/src/sgml/backup.sgml
@@ -6,19 +6,17 @@
  backup
 
  
-  As with everything that contains valuable data, PostgreSQL
-  databases should be backed up regularly. While the procedure is
-  essentially simple, it is important to have a clear understanding of
+  PostgreSQL databases should be backed up regularly. 
+  The procedure is simple but it is important to have a clear understanding of
   the underlying techniques and assumptions.
  
 
  
-  There are three fundamentally different approaches to backing up
+  There are four different approaches to backing up
   PostgreSQL data:
   
SQL dump
-   File system level backup
-   Continuous archiving
+   PITR, Point in Time Recovery
   
   Each has its own strengths and weaknesses; each is discussed in turn
   in the following sections.
@@ -28,141 +26,176 @@
   SQL Dump
 
   
-   The idea behind this dump method is to generate a file with SQL
-   commands that, when fed back to the server, will recreate the
-   database in the same state as it was at the time of the dump.
-   PostgreSQL provides the utility program
-for this purpose. The basic usage of this
-   command is:
+  PostgreSQL provides the program  for
+   generating a backup file with SQL commands that, when fed back to the server, 
+   will recreate the database in the same state as it was at the time of the dump.  
+   The basic usage of  is:
 
-pg_dump dbname > outfile
+pg_dump -C -F p -f outfile dbname
 
-   As you see, pg_dump writes its result to the
-   standard output. We will see below how this can be useful.
-   While the above command creates a text file, pg_dump
-   can create files in other formats that allow for parallism and more
-   fine-grained control of object restoration.
+
+   The use of -C ensures that the dump file will
+   contain the requisite  command within the dump file. The use of
+   -Fp ensures that you are using the plain
+   text format and the use of -f allows you
+   to specify the name of the file the dump will be written to. It is also possible
+   for pg_dump to create files in other formats that allow for parallelism 
+   and fine-grained control of object backup or restoration. For more details on all options 
+   available to pg_dump please refer to the  reference page. 
   
 
   
-   pg_dump is a regular PostgreSQL
-   client application (albeit a particularly clever one). This means
-   that you can perform this backup procedure from any remote host that has
-   access to the database. But remember that pg_dump
-   does not operate with special permissions. In particular, it must
-   have read access to all tables that you want to back up, so in order
-   to back up the entire database you almost always have to run it as a
-   database superuser.  (If you do not have sufficient privileges to back up
-   the entire database, you can still back up portions of the database to which
-   you do have access using options such as
-   -n schema
-   or -t table.)
+   The pg_dump application requires read access to all objects within the 
+   database that it will be operating with. This generally requires database 
+   super-user access. It is possible for any database user to use 
+   pg_dump to backup the objects that they own regardless of super-user access. This
+   can be achieved using options such as -n schema
+   or -t table. 
   
 
+
   
-   To specify which database server pg_dump should
-   contact, use the command line options -h
-   host and -p port. The
-   default host is the local host or whatever your
-   PGHOST environment variable specifies. Similarly,
-   the default port is indicated by the PGPORT
-   environment variable or, failing that, by the compiled-in default.
-   (Conveniently, the server will normally have the same compiled-in
-   default

Re: [HACKERS] INSERT ... ON CONFLICT DO UPDATE with _any_ constraint

[Simon Riggs]

On 19 May 2015 at 17:10, Peter Geoghegan  wrote:

> On Tue, May 19, 2015 at 1:57 PM, Simon Riggs 
> wrote:
> > We should allow DO UPDATE to exclude a constraint and apply a
> deterministic
> > order to the constraints. 1. PK if it exists. 2. Replica Identity, when
> not
> > PK, 3. UNIQUE constraints in name order, like triggers, so users can
> define
> > a default evaluation order, just like they do with triggers.
>
> That seems like something way worse than just allowing it for all
> constraints.
>

I'm talking about the evaluation order; it would still match all
constraints, otherwise they wouldn't be constraints.

> 2) Compatibility with MySQL
>
> But what you describe isn't compatible with MySQL. It's totally novel.
>

Upthread you said

"It's trivial to modify Postgres to not require that a specific unique
index be inferred, so that you can omit the inference specification
for DO UPDATE just as you can for DO NOTHING. That would make it work
in a similar way to MySQL"

Similar is good and useful. Full compatibility is even better.

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] Minor ON CONFLICT related fixes

[Andres Freund]

On 2015-05-18 19:09:27 -0700, Peter Geoghegan wrote:
> On Mon, May 18, 2015 at 2:09 PM, Peter Geoghegan  wrote:
> > You pointed out that the reason for this trivial bug on Jabber, but
> > here's the obvious fix, including an EXPLAIN regression test.
> 
> Also, I attach a patch adding ruleutils.c deparsing support for INSERT
> statement target aliases. This was previously overlooked.

Pushed.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] INSERT ... ON CONFLICT DO UPDATE with _any_ constraint

[Peter Geoghegan]

On Tue, May 19, 2015 at 1:57 PM, Simon Riggs  wrote:
> We should allow DO UPDATE to exclude a constraint and apply a deterministic
> order to the constraints. 1. PK if it exists. 2. Replica Identity, when not
> PK, 3. UNIQUE constraints in name order, like triggers, so users can define
> a default evaluation order, just like they do with triggers.

That seems like something way worse than just allowing it for all constraints.

>>> I have a hard time imagining why you'd ever not want to be explicit
>>> about what to take the alternative path on for the DO UPDATE variant.
>>>
>>> What do you have in mind?
>>
>>
>> If I'm being honest, my main driver is laziness :) I don't mind specifying
>> the constraint if I can understand why it's required, but otherwise it just
>> seems like I need to do more typing for no reason. Especially when there's
>> only one unique constraint on a table.
>
>
> 1) Ease of use - Unique constraints don't change very often. This saves time
> for the common case where they stay the same. It also saves time if they do
> change, because you avoid having to completely recode your app AND make that
> happen at exactly the same time you apply the change of unique constraint.

I don't see how it's possible to change unique constraints in a way
that breaks the inference specification without that actually being
desirable -- naming the constraint by name is an escape hatch that is
generally discouraged. That's the whole point of inference. I put an
awful lot of work into making unique index inference as forgiving as
possible. For example, it doesn't care what order attributes appear
in, or if they appear redundantly, or if an ON CONFLICT unique index
predicate is more selective than any available index that is otherwise
satisfied (there is a call to predicate_implied_by()).

> 2) Compatibility with MySQL

But what you describe isn't compatible with MySQL. It's totally novel.

-- 
Peter Geoghegan


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION

[Simon Riggs]

On 19 May 2015 at 16:49, Robert Haas  wrote:

> On Tue, May 19, 2015 at 3:00 PM, Simon Riggs 
> wrote:
> > As long as the cookie is randomly generated for each use, then I don't
> see a
> > practical problem with that approach.
>
> If the client sets the cookie via an SQL command, that command would
> be written to the log, and displayed in pg_stat_activity.  A malicious
> user might be able to get it from one of those places.
>
> A malicious user might also be able to just guess it.  I don't really
> want to create a situation where any weakess in pgpool's random number
> generation becomes a privilege-escalation attack.
>
> A protocol extension avoids all of that trouble, and can be target for
> 9.6 just like any other approach we might come up with.  I actually
> suspect the protocol extension will be FAR easier to fully secure, and
> thus less work, not more.


That's a reasonable argument. So +1 to protocol from me.

To satisfy Tom, I think this would need to have two modes: one where the
session can never be reset, for ultra security, and one where the session
can be reset, which allows security and speed of pooling.

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] INSERT ... ON CONFLICT DO UPDATE with _any_ constraint

[Simon Riggs]

On 19 May 2015 at 16:36, Geoff Winkless  wrote:

> On 19 May 2015 at 21:12, Peter Geoghegan  wrote:
>
>> It's trivial to modify Postgres to not require that a specific unique
>> index be inferred, so that you can omit the inference specification
>> for DO UPDATE just as you can for DO NOTHING. That would make it work
>> in a similar way to MySQL; whatever actually conflict was detected
>> would be assumed to be cause to take the alternative update path.
>>
>
> ​Except that would break the deterministic behaviour, surely? Because if
> you only updated one row based on which constraint matched first, the row
> that was updated would depend on the order in which the constraints were
> evaluated, yes?
>

It would depend upon the evaluation order, but that would not break
determinism unless you allowed a random evaluation order.

Omitting the clause for DO NOTHING yet requiring it for DO UPDATE doesn't
make sense.

We should allow DO UPDATE to exclude a constraint and apply a deterministic
order to the constraints. 1. PK if it exists. 2. Replica Identity, when not
PK, 3. UNIQUE constraints in name order, like triggers, so users can define
a default evaluation order, just like they do with triggers.



> I was expecting that matching two constraints would end up UPDATEing two
> separate rows.
>

It's not clear to me how a single INSERT could cause two or more UPDATEs.

I have a hard time imagining why you'd ever not want to be explicit
>> about what to take the alternative path on for the DO UPDATE variant.
>>
>> What do you have in mind?
>
>
> If I'm being honest, my main driver is laziness :) I don't mind specifying
> the constraint if I can understand why it's required, but otherwise it just
> seems like I need to do more typing for no reason. Especially when there's
> only one unique constraint on a table.
>

1) Ease of use - Unique constraints don't change very often. This saves
time for the common case where they stay the same. It also saves time if
they do change, because you avoid having to completely recode your app AND
make that happen at exactly the same time you apply the change of unique
constraint.

2) Compatibility with MySQL

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] a few thoughts on the schedule

[Robert Haas]

On Tue, May 19, 2015 at 1:35 PM, Andres Freund  wrote:
>> The vary earliest time frame that would make sense to me is to branch
>> July 1st and start a CF on July 15th.
>
> I'm wondering why the CF has to start after branching? Or is that just
> two independent dates? The first week or so of the first CF won't have
> much stuff ready for commit.

Well, if there is something ready to commit, I'd like to be able to commit it.

>> Personally, given where we're at right now, I don't think an early
>> fall release of 9.5 is going to be remotely practical.
>
> Why? To me the last few beta periods were pretty drawn out affairs,
> without much happening. Yes, there was the jsonb stuff in 9.4 delaying
> the release, but that wasn't waiting for work, but a decision.  But most
> of the time everyone was developing their stuff for the next cycle,
> waiting for beta testers to come forward with bugs. Not very much of
> that happened.
>
> I think a shorter schedule might actually help us to both, get the open
> issues closed sooner, and get more actual testing. Most people seem to
> work with a "Oh, there's time left, I can do that later" attitude.

There's something to that theory.  I'm just worried all of those last
minute commits are hiding a bunch of bugs.

> I think part of that is saying "no" more efficiently, upfront. Which is
> why I really want the triage step.
> a) It's much better for the project to not have several "junior" reviewers
>first spend time on a patch, then have a small flamefest, and then
>have somebody "senior" reject a patch in its entirety. That's a waste
>of everyone's effort and frustrating.
> b) It's not that bad to hear a "no" as a new contributor soon after
>submission. It's something entirely different to go through a long
>bikeshedding, several revisions of reworking, just to be told in the
>end that it was a bad idea from the get go.

I agree this would help.  Figuring out how to do it in a reasonable
way would help a lot.  If we could get say 4 committers to go through
at the start of each CommitFest and each comment very briefly on 25%
of the patches each (yes, no, or maybe, and a bit of justification), I
bet that would streamline things considerably.  If we could get each
committer to go through 50% of the patches and do this, then each
patch would get a quick opinion from two committers right at the
outset.  That would be even nicer.

>> Unless talented reviewers can get such job offers, we are going to
>> continue to have trouble making ends meet.
>
> Hasn't every talented reviewer gotten job offers shortly afterwards in
> the last few years?  The ones that accept don't necessarily work that
> much in the community, but several seem to. And I think in the case of
> several people the reason they don't, is less the company, but that it's
> emotionally draining.

Agreed, lots of people get job offers.  But somehow we're still short
of reviewers, so something's not working the way it needs to.  Maybe
that's for the reason you postulate, or maybe it's for the reason I
postulate, or maybe there is some other reason.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[Dave Cramer]

On 19 May 2015 at 16:36, Kevin Grittner  wrote:

> Gavin Flower  wrote:
>
> > I prefer the $1 approach, others can't use that, and there are
> > situations where I could not either.
> >
> > So, how about defaulting to the '?' approach, but have a method
> > to explicitly set the mode - to switch to using '$'?
>
> Are you suggesting that we implement something other than what is
> described in these documents for prepared statement parameters?:
>
> http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html
>
>
> http://download.oracle.com/otn-pub/jcp/jdbc-4_1-mrel-spec/jdbc4.1-fr-spec.pdf
>
> If so, I strongly oppose that.  If we are not going to deprecate
> use of the question mark character for operators, we need some
> nonstandard hack to our JDBC implementation, but an alternative
> syntax for specifying PreparedStatement and CallableStatement
> parameters seems entirely the wrong way to go.
>
>

> The issue here is what to do about the difficulties in using JDBC
> prepared statements in combination with the PostgreSQL extension of
> operator names containing question marks.  Using a double question
> mark is not horrible as a solution.


Actually the issue is what to do about a number of connectors which use a
fairly standard '?' as a placeholder.
Notably absent from the discussion is ODBC upon which JDBC was modelled and
probably predates any use of ? as an operator

It may not be what we would
> have arrived at had the discussion taken place on the pgsql-jdbc
> list rather than underneath a github pull request, but we can
> only move forward from where we are.
>
> possibly, however all of the current JDBC maintainers opined and reached
an agreement on this.


> Out of curiosity, how long has the ?? solution been implemented in
> a driver jar file available as a public download?


 At least since February of this year

What are the
> guidelines for what discussion belongs on the pgsql-jdbc list and
> what discussion belongs on github?  Is someone interested in
> participating in the discussions leading to decisions about our
> JDBC connector expected to follow both?
>
> Currently pull requests are the easiest to deal with so most discussion is
on github.

I guess updating the JDBC web page would be in order.



Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[David G. Johnston]

On Tue, May 19, 2015 at 1:36 PM, Kevin Grittner  wrote:

> Gavin Flower  wrote:
>
> > I prefer the $1 approach, others can't use that, and there are
> > situations where I could not either.
> >
> > So, how about defaulting to the '?' approach, but have a method
> > to explicitly set the mode - to switch to using '$'?
>
> Are you suggesting that we implement something other than what is
> described in these documents for prepared statement parameters?:
>
> http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html
>
>
> http://download.oracle.com/otn-pub/jcp/jdbc-4_1-mrel-spec/jdbc4.1-fr-spec.pdf
>
> If so, I strongly oppose that.  If we are not going to deprecate
> use of the question mark character for operators, we need some
> nonstandard hack to our JDBC implementation, but an alternative
> syntax for specifying PreparedStatement and CallableStatement
> parameters seems entirely the wrong way to go.
>

​I'll repeat my earlier comment that having a mode that allows for libpq
syntax while still conforming to the JDBC class API would have value for
those users willing to admit their application and code is not portable
(and if they are using these operators it is not) and would rather conform
as closely to native PostgreSQL language mechanics as possible.​

​That said I would not argue that the current official driver needs to be
so modified.​


> The issue here is what to do about the difficulties in using JDBC
> prepared statements in combination with the PostgreSQL extension of
> operator names containing question marks.  Using a double question
> mark is not horrible as a solution.  It may not be what we would
> have arrived at had the discussion taken place on the pgsql-jdbc
> list rather than underneath a github pull request, but we can
> only move forward from where we are.
>
> Out of curiosity, how long has the ?? solution been implemented in
> a driver jar file available as a public download?


​Less than 6 months...discussion started a few months prior to that.


> What are the
> guidelines for what discussion belongs on the pgsql-jdbc list and
> what discussion belongs on github?  Is someone interested in
> participating in the discussions leading to decisions about our
> JDBC connector expected to follow both?
>
>
As things stand now - it seems that way.  There are no guidelines that I
can tell but I'd likely consider pgsql-jdbc the equivalent of -general and
GitHub looks like -hackers.  Neither is particularly high volume.

​David J.

Re: [HACKERS] INSERT ... ON CONFLICT DO UPDATE with _any_ constraint

[Peter Geoghegan]

On Tue, May 19, 2015 at 1:36 PM, Geoff Winkless  wrote:
> On 19 May 2015 at 21:12, Peter Geoghegan  wrote:
>>
>> It's trivial to modify Postgres to not require that a specific unique
>> index be inferred, so that you can omit the inference specification
>> for DO UPDATE just as you can for DO NOTHING. That would make it work
>> in a similar way to MySQL; whatever actually conflict was detected
>> would be assumed to be cause to take the alternative update path.
>
>
> Except that would break the deterministic behaviour, surely? Because if you
> only updated one row based on which constraint matched first, the row that
> was updated would depend on the order in which the constraints were
> evaluated, yes? I was expecting that matching two constraints would end up
> UPDATEing two separate rows.

Well, it would be deterministic to the extent that the indexes would
be evaluated in OID order. But yes, the first would-be duplicate
violation would make the update path be taken once and only once for
the row proposed for insertion -- at that point, you've given up on
insertion (unless there is a row locking conflict). Just like MySQL, I
believe.

How can you find a would-be violation without inserting? How can you
insert without also violating the other thing? It's far messier than
it first appears.

>> I have a hard time imagining why you'd ever not want to be explicit
>> about what to take the alternative path on for the DO UPDATE variant.
>>
>> What do you have in mind?
>
>
> If I'm being honest, my main driver is laziness :) I don't mind specifying
> the constraint if I can understand why it's required, but otherwise it just
> seems like I need to do more typing for no reason. Especially when there's
> only one unique constraint on a table.

Well, I don't have zero sympathy for that, but I'm pretty sure that
that's what other people wanted. If I'm being honest, I don't actually
remember how true that was.

-- 
Peter Geoghegan


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION

[Robert Haas]

On Tue, May 19, 2015 at 3:00 PM, Simon Riggs  wrote:
> As long as the cookie is randomly generated for each use, then I don't see a
> practical problem with that approach.

If the client sets the cookie via an SQL command, that command would
be written to the log, and displayed in pg_stat_activity.  A malicious
user might be able to get it from one of those places.

A malicious user might also be able to just guess it.  I don't really
want to create a situation where any weakess in pgpool's random number
generation becomes a privilege-escalation attack.

A protocol extension avoids all of that trouble, and can be target for
9.6 just like any other approach we might come up with.  I actually
suspect the protocol extension will be FAR easier to fully secure, and
thus less work, not more.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION

[Robert Haas]

On Tue, May 19, 2015 at 2:46 PM, Andres Freund  wrote:
> On 2015-05-19 14:41:06 -0400, Robert Haas wrote:
>> On Tue, May 19, 2015 at 12:29 PM, Andres Freund  wrote:
>> > On 2015-05-19 10:53:10 -0400, Robert Haas wrote:
>> >> That seems like a kludge to me.  If the cookie leaks out somhow, which
>> >> it will, then it'll be insecure.  I think the way to do this is with a
>> >> protocol extension that poolers can enable on request.  Then they can
>> >> just refuse to forward any "reset authorization" packets they get from
>> >> their client.  There's no backward-compatibility break because the
>> >> pooler can know, from the server version, whether the server is new
>> >> enough to support the new protocol messages.
>> >
>> > That sounds like a worse approach to me. Don't you just need to hide the
>> > session authorization bit in a function serverside to circumvent that?
>>
>> I'm apparently confused.  There's nothing you can do to maintain
>> security against someone who can load C code into the server.  I must
>> be misunderstanding you.
>
> It very well might be me that's confused. But what's stopping a user
> from doing a "RESET SESSION AUTHORIZATION;" in a DO block or something?
> I guess you are intending that a RESET SESSION AUTHORIZATION is only
> allowed on a protocol level when the protocol extension is in use?

Yes, something like that.  I'm not sure if we'd want to reuse the
existing SESSION AUTHORIZATION concept or create something new, but
either way the idea would be that the pooler would send a
PoolerSetAuthorization message which could only be undone by another
such message.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] INSERT ... ON CONFLICT DO UPDATE with _any_ constraint

[Geoff Winkless]

On 19 May 2015 at 21:12, Peter Geoghegan  wrote:

> It's trivial to modify Postgres to not require that a specific unique
> index be inferred, so that you can omit the inference specification
> for DO UPDATE just as you can for DO NOTHING. That would make it work
> in a similar way to MySQL; whatever actually conflict was detected
> would be assumed to be cause to take the alternative update path.
>

​Except that would break the deterministic behaviour, surely? Because if
you only updated one row based on which constraint matched first, the row
that was updated would depend on the order in which the constraints were
evaluated, yes? I was expecting that matching two constraints would end up
UPDATEing two separate rows.

I have a hard time imagining why you'd ever not want to be explicit
> about what to take the alternative path on for the DO UPDATE variant.
>
> What do you have in mind?


If I'm being honest, my main driver is laziness :) I don't mind specifying
the constraint if I can understand why it's required, but otherwise it just
seems like I need to do more typing for no reason. Especially when there's
only one unique constraint on a table.

Geoff

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[Kevin Grittner]

Gavin Flower  wrote:

> I prefer the $1 approach, others can't use that, and there are
> situations where I could not either.
>
> So, how about defaulting to the '?' approach, but have a method
> to explicitly set the mode - to switch to using '$'?

Are you suggesting that we implement something other than what is
described in these documents for prepared statement parameters?:

http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html

http://download.oracle.com/otn-pub/jcp/jdbc-4_1-mrel-spec/jdbc4.1-fr-spec.pdf

If so, I strongly oppose that.  If we are not going to deprecate
use of the question mark character for operators, we need some
nonstandard hack to our JDBC implementation, but an alternative
syntax for specifying PreparedStatement and CallableStatement
parameters seems entirely the wrong way to go.

The issue here is what to do about the difficulties in using JDBC
prepared statements in combination with the PostgreSQL extension of
operator names containing question marks.  Using a double question
mark is not horrible as a solution.  It may not be what we would
have arrived at had the discussion taken place on the pgsql-jdbc
list rather than underneath a github pull request, but we can
only move forward from where we are.

Out of curiosity, how long has the ?? solution been implemented in
a driver jar file available as a public download?  What are the
guidelines for what discussion belongs on the pgsql-jdbc list and
what discussion belongs on github?  Is someone interested in
participating in the discussions leading to decisions about our
JDBC connector expected to follow both?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION

[José Luis Tallón]

On 05/19/2015 09:00 PM, Simon Riggs wrote:

[snip]


I think the idea of having SET SESSION AUTH pass a cookie, and
only let
RESET SESSION AUTH work when the same cookie is supplied, is pretty
reasonable.


As long as the cookie is randomly generated for each use, then I don't 
see a practical problem with that approach.


Protocol level solution means we have to wait 1.5 years before anybody 
can begin using that. I'm also dubious that a small hole in the 
protocol arrangements could slam that door shut because we couldn't 
easily backpatch.


Having an in-core pooler would be just wonderful because then we could 
more easily trust it and we wouldn't need to worry.


Ufff Please don't do that.
Postgres is "just" a database. And a very good one at that. Let us keep 
it that way and not try to re-implement everything within it --- We're 
not "the big red company" after all :)


There are places where a pooler is badly needed and others where it 
is just overkill and counterproductive.
Plus, scalability models / usage patterns are not nearly the same (nor 
even compatible sometimes!) between databases and poolers.



There exist perfectly good solutions already (and they can certainly be 
improved), such as PgBouncer (or even PgPool-II) or others can be adopted.



Just my .02€


/ J.L.

Re: [HACKERS] Per row status during INSERT .. ON CONFLICT UPDATE?

[Peter Geoghegan]

On Tue, May 19, 2015 at 1:20 PM, Peter Geoghegan  wrote:
> On Tue, May 19, 2015 at 1:07 PM, Robins Tharakan  wrote:
>> My use-case is to create an extra row for all UPDATEd rows (only), which is
>> implemented in MSSQL by enveloping the MERGE with an INSERT (MERGE ...
>> OUTPUT $action) WHERE $action = 'UPDATE'.
>
> That could make sense. You can achieve something similar with per-row
> triggers, perhaps.

BTW, be prepared to deal with the updated row (*any* row version) not
being visible to your MVCC snapshot with that pattern in Postgres (at
READ COMMITTED level). It probably won't matter, but it could.

-- 
Peter Geoghegan


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Per row status during INSERT .. ON CONFLICT UPDATE?

[Peter Geoghegan]

On Tue, May 19, 2015 at 1:07 PM, Robins Tharakan  wrote:
> My use-case is to create an extra row for all UPDATEd rows (only), which is
> implemented in MSSQL by enveloping the MERGE with an INSERT (MERGE ...
> OUTPUT $action) WHERE $action = 'UPDATE'.

That could make sense. You can achieve something similar with per-row
triggers, perhaps.

> Am still to test, but looks like Thom's reply earlier could take care of my
> use-case, so we may need more people requesting this magic field, with a
> valid use-case.

I'm not opposed to it, but it's not a personal priority to implement
this. I don't think it's great practice to use the hidden fields like
that. I can't see anything other than a dedicated expression serving
this purpose, if there is ever a documented solution.

-- 
Peter Geoghegan


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] INSERT ... ON CONFLICT DO UPDATE with _any_ constraint

[Peter Geoghegan]

On Tue, May 19, 2015 at 12:57 PM, Geoff Winkless  wrote:
> Well http://www.postgresql.org/docs/devel/static/sql-insert.html explains
> that a conflict_target clause is required but doesn't explain why.

Yes, for ON CONFLICT DO UPDATE, it is mandatory.

> It _does_ make clear that multiple UPDATEs to the same row are not allowed,
> but that in itself doesn't automatically restrict the use of multiple
> constraint targets; I could easily INSERT a set of values that would trigger
> that failure with just one constraint target.

True.

> http://www.postgresql.org/docs/devel/static/sql-insert.html talks about how
> MySQL's ON DUPLICATE can only act against the first matching row where
> multiple constraints match against multiple rows. I suppose if that were the
> case here (ie the first excluding row would stop other rows firing against
> the UPDATE) would break the deterministic feature, but it's not clear if
> that's true or not. I don't see why multiple target rows couldn't be updated
> based on multiple constraints, that would not in-and-of-itself break
> determinism.
>
> If I'm missing the obvious, accept my apologies.

It's trivial to modify Postgres to not require that a specific unique
index be inferred, so that you can omit the inference specification
for DO UPDATE just as you can for DO NOTHING. That would make it work
in a similar way to MySQL; whatever actually conflict was detected
would be assumed to be cause to take the alternative update path.

The only reason I can see for wanting to do this is where you're
running a migration or something, and two unique indexes are
equivalent anyway. Like maybe you have a partial index and a
non-partial index, and you're just about to drop one of them. But the
inference specification will do the right thing here anyway --
multiple unique indexes can be inferred for edge cases like this.

I have a hard time imagining why you'd ever not want to be explicit
about what to take the alternative path on for the DO UPDATE variant.
Unless perhaps you have a different UPDATE targetlist and so on
corresponding to that case, which is currently not possible -- but
then what if multiple constraints have would-be violations at the same
time? It gets awfully messy very quickly.

What do you have in mind?

-- 
Peter Geoghegan


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Per row status during INSERT .. ON CONFLICT UPDATE?

[Robins Tharakan]

On 19 May 2015 at 23:24, Peter Geoghegan  wrote:

> That's certainly something we talked about. It could probably be done
> with some kind of magical expression. I have to wonder how many of the
> people that are sure that they need this really do, though. Is it
> really natural to care about this distinction with idiomatic usage?
>

Thanks everyone for responding promptly.

​Not sure if I can be authoritative for many, but for me, the need emanates
from having to move an ETL off MSSQL Server, which supports OUTPUT $action
(similar to RETURNING * in Postgres​) where $action is the per-row status
(INSERT / UPDATE).

My use-case is to create an extra row for all UPDATEd rows (only), which is
implemented in MSSQL by enveloping the MERGE with an INSERT (MERGE ...
OUTPUT $action) WHERE $action = 'UPDATE'.

Am still to test, but looks like Thom's reply earlier could take care of my
use-case, so we may need more people requesting this magic field, with a
valid use-case.

--
Robins Tharakan

Re: [HACKERS] INSERT ... ON CONFLICT DO UPDATE with _any_ constraint

[Geoff Winkless]

On 19 May 2015 at 20:11, Simon Riggs  wrote:

> I'm sure we'll be asked these questions many times.
>
> Can you comment on whether the docs are sufficiently detailed to explain
> this answer?
>
​
Well http://www.postgresql.org/docs/devel/static/sql-insert.html explains
that a conflict_target clause is required but doesn't explain why.

It _does_ make clear that multiple UPDATEs to the same row are not allowed,
but that in itself doesn't automatically restrict the use of multiple
constraint targets; I could easily INSERT a set of values that would
trigger that failure with just one constraint target.

http://www.postgresql.org/docs/devel/static/sql-insert.html talks about how
MySQL's ON DUPLICATE can only act against the first matching row where
multiple constraints match against multiple rows. I suppose if that were
the case here (ie the first excluding row would stop other rows firing
against the UPDATE) would break the deterministic feature, but it's not
clear if that's true or not. I don't see why multiple target rows couldn't
be updated based on multiple constraints, that would not in-and-of-itself
break determinism.

If I'm missing the obvious, accept my apologies.

Geoff

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[Gavin Flower]

On 20/05/15 07:37, Jan de Visser wrote:

On May 19, 2015 07:04:56 PM Greg Sabino Mullane wrote:

Bruno Harbulot asked for a devil's advocate by saying:

My main point was that this is not specific to JDBC. Considering that even
PostgreSQL's own ECPG is affected, the issue goes probably deeper than it
seems. I'm just not convinced that passing the problem onto connectors,
libraries and ultimately application developers is the right thing to do
here.

Well, one could argue that it *is* their problem, as they should be using
the standard Postgres way for placeholders, which is $1, $2, $3...

Shirley you are joking: Many products use JDBC as an abstraction layer
facilitating (mostly) seamless switching between databases. I know the product
I worked on did. Are you advocating that every single statement should use
"SELECT * FROM foo WHERE bar = $1" on pg and "SELECT * FROM foo WHERE bar = ?"
on every other database?

A database is only as valuable as the the part of the outside world it can
interact with. Large parts of the data-consuming world are developed in java
using JDBC. If your opinion is that JDBC developers should adapt themselves to
pg then you instantaneously diminish the value of pg.

jan



I prefer the $1 approach, others can't use that, and there are 
situations where I could not either.


So, how about defaulting to the '?' approach, but have a method to 
explicitly set the mode - to switch to using '$'?




Cheers,
Gavin


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[Jan de Visser]

On May 19, 2015 07:04:56 PM Greg Sabino Mullane wrote:
> Bruno Harbulot asked for a devil's advocate by saying:
> > My main point was that this is not specific to JDBC. Considering that even
> > PostgreSQL's own ECPG is affected, the issue goes probably deeper than it
> > seems. I'm just not convinced that passing the problem onto connectors,
> > libraries and ultimately application developers is the right thing to do
> > here.
> 
> Well, one could argue that it *is* their problem, as they should be using
> the standard Postgres way for placeholders, which is $1, $2, $3...

Shirley you are joking: Many products use JDBC as an abstraction layer 
facilitating (mostly) seamless switching between databases. I know the product 
I worked on did. Are you advocating that every single statement should use 
"SELECT * FROM foo WHERE bar = $1" on pg and "SELECT * FROM foo WHERE bar = ?" 
on every other database?

A database is only as valuable as the the part of the outside world it can 
interact with. Large parts of the data-consuming world are developed in java 
using JDBC. If your opinion is that JDBC developers should adapt themselves to 
pg then you instantaneously diminish the value of pg.

jan



-- 
Sent 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 few thoughts on the schedule

[Simon Riggs]

On 18 May 2015 at 23:34, Robert Haas  wrote:

> On May 18, 2015, at 10:41 PM, Andres Freund  wrote:
> >> On 2015-05-19 11:34:49 +0900, Michael Paquier wrote:
> >> +1 for moving it at least 1 month.
> >
> > 2015-06-15 also collides with pgcon, which probably isn't the best
> > idea. I do think we should try hard doing a triage at the start of a CF
> > and not many with experience in the project are going to have time
> > around then.
> >
> > So, to where do we move it? We probably need to schedule at least the
> > first CF now. Just to 2015-07-15? That'd leave us enough room to
> > schedule the rest at pgcon.
>
> Honestly, that seems awful soon.  I would have thought maybe August 15th.
>

We have this discussion every year, but I would like to skip that.

+1 to 2015-07-15 and then the same schedule as this release. Constant fine
tuning the dates doesn't really help, it just creates the impression that
discussion might make the dates flexible which works against us, even
though I might otherwise agree with them.

That allows us to release in Sept, without conflicting with CFs.

I suggest we go Beta1 on June 1, so we can discuss any problems arising in
person in Ottawa. It's quicker than normal, but if we've lost a month or
two we should just skip the usual "open items" chase, which can be done in
parallel with users finding and reporting real bugs.

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[Bruno Harbulot]

On Tue, May 19, 2015 at 8:04 PM, Greg Sabino Mullane 
wrote:

>
> -BEGIN PGP SIGNED MESSAGE-
> Hash: RIPEMD160
>
> Bruno Harbulot asked for a devil's advocate by saying:
> > My main point was that this is not specific to JDBC. Considering that
> even
> > PostgreSQL's own ECPG is affected, the issue goes probably deeper than it
> > seems. I'm just not convinced that passing the problem onto connectors,
> > libraries and ultimately application developers is the right thing to do
> > here.
>
> Well, one could argue that it *is* their problem, as they should be using
> the standard Postgres way for placeholders, which is $1, $2, $3...
>


As I was saying in another message on this thread a few hours ago, it
appears that ? is reserved for placeholders for Dynamic SQL according to
the SQL specifications, and that would be exactly what ECPG is using as far
as I understand.



>
> > Recommending that all drivers implement \? as a semi-standard workaround
> is
> > actually a much more difficult problem than it seems: it requires
> following
> > the development of each project, making the case to each community
> > (assuming they're all open source), and reasonable in-depth knowledge of
> > their respective implementation, also assuming that \? won't cause
> further
> > problems there (of course, all that is easier if you're already working
> on
> > that particular project).
>
> That's actually where we are right now. And it's not really our job to
> make the case to each community - it is the responsibility of each project
> to solve the problem, presumably because of pressure from their users.
>

... except if those communities made the assumption that ? was indeed
reserved for placeholders according to the SQL specifications. (I might
have misinterpreted where that part of the spec is applicable, since I
can't claim I've absorbed the entire set of documents.)


 > Even according to what you're saying this issue has required a first
> > workaround back in 2008, and another one earlier this year, probably due
> to
> > concerns that weren't spotted when implementing the first workaround
> (this
> > also presumably requires users to run a fairly recent version of this
> > connector now).
>
> True enough regarding the two changes. But the system worked well, in that
> someone had a problem, raised a bug, and it got fixed. I'm not sure I see
> the point about requiring recent versions of the connector - that's true
> for lots of bug fixes and features. This one at least is fairly optional
> with many existing workarounds (e.g. use $1, quote things in a different
> way).
>

This model of development also requires the users to be able to upgrade
their connectors to a recent release, which may also affect other
dependencies (depending on the complexity of the overall system).


Best wishes,

Bruno.

Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION

[Petr Jelinek]

On 19/05/15 20:46, Andres Freund wrote:

On 2015-05-19 14:41:06 -0400, Robert Haas wrote:

On Tue, May 19, 2015 at 12:29 PM, Andres Freund  wrote:

On 2015-05-19 10:53:10 -0400, Robert Haas wrote:

That seems like a kludge to me.  If the cookie leaks out somhow, which
it will, then it'll be insecure.  I think the way to do this is with a
protocol extension that poolers can enable on request.  Then they can
just refuse to forward any "reset authorization" packets they get from
their client.  There's no backward-compatibility break because the
pooler can know, from the server version, whether the server is new
enough to support the new protocol messages.


That sounds like a worse approach to me. Don't you just need to hide the
session authorization bit in a function serverside to circumvent that?


I'm apparently confused.  There's nothing you can do to maintain
security against someone who can load C code into the server.  I must
be misunderstanding you.


It very well might be me that's confused. But what's stopping a user
from doing a "RESET SESSION AUTHORIZATION;" in a DO block or something?
I guess you are intending that a RESET SESSION AUTHORIZATION is only
allowed on a protocol level when the protocol extension is in use?



If I understand Robert correctly, he was talking about setting and 
resetting this on protocol level (with the assistance of pooler) so 
there is no way to circumvent that from SQL no matter how you mask the 
command. I think that idea is quite sound.


--
 Petr Jelinek  http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


> I did find some alternatives discussed a couple of years back, like
> {postgres qm} and ; the later simply being to allow the
> operator to be quoted inside "operator()"

Yes, we (DBD::Pg) looked at using at some of the JDBC-ish alternatives 
like the (very verbose) vendor escape clauses, but settled on the simplicity of 
a single backslash in the end. See part of the discussion here:

http://www.nntp.perl.org/group/perl.dbi.users/2014/12/msg37057.html

- -- 
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201505191520
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAlVbjQQACgkQvJuQZxSWSsgYhACfUfztfxZBQEwESqRYkfRco29M
pAUAoO9qA5IWN96UXsh9iASspiEYfAfF
=k8Gl
-END PGP SIGNATURE-




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[Dave Cramer]

On 19 May 2015 at 15:02, Tom Lane  wrote:

> "Greg Sabino Mullane"  writes:
> > Dave Cramer opined:
> >> It would seem that choosing ? for operators was ill advised; I'm not
> >> convinced that deprecating them is a bad idea. If we start now, in 5
> years
> >> they should be all but gone
>
> > Ha ha ha ha ha! That's a good one. We still have clients on Postgres 7!
> > Five years is way too short to replace something that major.
>
Yeah, that's a big problem for this line of thought.  Even if we had
> consensus today, the first release that would actually contain alternative
> operators would be 9.6, more than a year out (since 9.5 is past feature
> freeze now).  It would take several years after that before there would be
> any prospect of removing the old ones, and several years more before PG
> versions containing the old operators were out of support.
>
> Now there are different ways you could look at this.  From the perspective
> of a particular end user, you could imagine instituting a shop policy of
> not using the operators containing '?' as soon as you had a release where
> there were alternatives.  So in that context you might have a fix
> available as soon as 9.6 came out.  But from the perspective of a driver
> author who has to support queries written by other people, the problem
> would not be gone for at least ten years more.  Changing the driver's
> behavior sounds like a more practical solution.
>
>
The current JDBC driver doesn't really support anything beyond 8.4 except
for CRUD operations.

We are also are no longer supporting JVM's older than 1.6 in the current
driver.
People who insist on staying on old code get what they get. I don't see a
problem with saying after a certain date we just don't support it in the
current code.

After all I have heard rumblings about deprecating V2 protocol ?

FWIW, I was content to leave this alone. JDBC has a workable solution.
However I've not seen a good argument for continuing to use the ? operator
as it's conflicts with many clients and is apparently not in the SQL
standard.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


>

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[Bruno Harbulot]

On Tue, May 19, 2015 at 7:51 PM, Greg Sabino Mullane 
wrote:

>
> -BEGIN PGP SIGNED MESSAGE-
> Hash: RIPEMD160
>
>
> Dave Cramer opined:
> > It would seem that choosing ? for operators was ill advised; I'm not
> > convinced that deprecating them is a bad idea. If we start now, in 5
> years
> > they should be all but gone
>
> Ha ha ha ha ha! That's a good one. We still have clients on Postgres 7!
> Five years is way too short to replace something that major.
>

I think deprecation doesn't necessarily imply removal. It seems that the
two operators could exist together by creating a second operator with the
same characteristics as suggested by Frank Heikens in this post:
http://stackoverflow.com/questions/27573778/postgresql-jsonb-and-jdbc/27580137#27580137

This would also make it easier to backport these operators into existing
installations (even on 9.4), thereby making the transition easier.

I don't know enough about PostgreSQL's implementation, but I presume this
is effectively just giving an alias for the same operation, and hopefully,
the query engine could benefit from indices created using either notations
interchangeably. (This is probably the most important feature when changing
one notation for another.)


In addition, the argument regarding the time it can take users to upgrade
works both ways. If I understood correctly from your message yesterday,
you've only implemented the latest workaround using \? in DBD::Pg quite
recently, which would equally require users to be able to upgrade to a more
recent version of DBD::Pg (or PHP/PDO where the workaround doesn't seem to
be implemented at all yet). Admittedly, I guess it might often be easier to
upgrade the client side than the database server, but I'm not sure that is
always the case (some frontends can potentially be awkward to update,
whereas a database upgrade can be smoother... It varies...).


Best wishes,

Bruno.

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[Andrew Dunstan]

On 05/19/2015 02:22 PM, Tom Lane wrote:

Mike Blackwell  writes:

See for example
http://docs.oracle.com/cd/B19306_01/text.102/b14218/cqoper.htm#i997330,
Table 3-1, third row, showing the precedence of '?'.  Further down the
page, under "Fuzzy" see "Backward Compatibility Syntax".

If I'm reading that right, that isn't a SQL-level operator but an operator
in their text search query language, which would only appear in SQL
queries within string literals (compare tsquery's query operators in PG).
So it wouldn't be a hazard for ?-substitution, as long as the substituter
was bright enough to not change string literals.





Yeah. What would be nice would be to have a functional notation 
corresponding to the operators, so you would be able to write


   something."?>"(a,b)

and it would mean exactly the same thing, including indexability, as

   a ?> b

I presume that wouldn't give the drivers a headache.

cheers

andrew


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] INSERT ... ON CONFLICT DO UPDATE with _any_ constraint

[Simon Riggs]

On 19 May 2015 at 11:49, Geoff Winkless  wrote:

> On 19 May 2015 at 16:32, I wrote:
>
>> In the event that the INSERT triggers a constraint that the UPDATE fails
>> to resolve, it will still fail in exactly the same way that running the ON
>> CONFLICT on a specific constraint would fail, so it's not like you gain any
>> extra value from specifying the constraint, is it?
>>
>
> ​I don't know why I wrote this paragraph, it's just the product of me
> thinking of something else at the same time:
>  UPDATE obviously doesn't resolve a conflict as such.
>
>
> Thinking about it more, I suppose if multiple constraints end up
> triggering for the same INSERT, it would require UPDATEs of multiple rows.
> Is that the issue?
>

I'm sure we'll be asked these questions many times.

Can you comment on whether the docs are sufficiently detailed to explain
this answer?

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160

Bruno Harbulot asked for a devil's advocate by saying:
> My main point was that this is not specific to JDBC. Considering that even
> PostgreSQL's own ECPG is affected, the issue goes probably deeper than it
> seems. I'm just not convinced that passing the problem onto connectors,
> libraries and ultimately application developers is the right thing to do
> here.

Well, one could argue that it *is* their problem, as they should be using 
the standard Postgres way for placeholders, which is $1, $2, $3...

> Recommending that all drivers implement \? as a semi-standard workaround is
> actually a much more difficult problem than it seems: it requires following
> the development of each project, making the case to each community
> (assuming they're all open source), and reasonable in-depth knowledge of
> their respective implementation, also assuming that \? won't cause further
> problems there (of course, all that is easier if you're already working on
> that particular project).

That's actually where we are right now. And it's not really our job to 
make the case to each community - it is the responsibility of each project 
to solve the problem, presumably because of pressure from their users.
The "\?" would only be a recommendation (but a pretty good one - the Perl 
folk talked a good bit about the best solution and researched if \? had 
the potential to cause any other problems).

> Even according to what you're saying this issue has required a first
> workaround back in 2008, and another one earlier this year, probably due to
> concerns that weren't spotted when implementing the first workaround (this
> also presumably requires users to run a fairly recent version of this
> connector now).

True enough regarding the two changes. But the system worked well, in that 
someone had a problem, raised a bug, and it got fixed. I'm not sure I see 
the point about requiring recent versions of the connector - that's true 
for lots of bug fixes and features. This one at least is fairly optional 
with many existing workarounds (e.g. use $1, quote things in a different way).

> (It looks like PHP/PDO is another one to add to the list:
> https://bugs.php.net/bug.php?id=62493, it's been open for almost 3 years.)

Some days I almost feel sorry for PHP.

- -- 
Greg Sabino Mullane g...@turnstep.com
PGP Key: 0x14964AC8 201505191503
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAlVbiRUACgkQvJuQZxSWSsjHYgCg1GfnDrdefkoedpigxYiuBMFX
794An3XWPXA0dVRk0JI6/Ik5Jb7SOLO7
=Amfe
-END PGP SIGNATURE-




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[Tom Lane]

"Greg Sabino Mullane"  writes:
> Dave Cramer opined:
>> It would seem that choosing ? for operators was ill advised; I'm not
>> convinced that deprecating them is a bad idea. If we start now, in 5 years
>> they should be all but gone

> Ha ha ha ha ha! That's a good one. We still have clients on Postgres 7!
> Five years is way too short to replace something that major.

Yeah, that's a big problem for this line of thought.  Even if we had
consensus today, the first release that would actually contain alternative
operators would be 9.6, more than a year out (since 9.5 is past feature
freeze now).  It would take several years after that before there would be
any prospect of removing the old ones, and several years more before PG
versions containing the old operators were out of support.

Now there are different ways you could look at this.  From the perspective
of a particular end user, you could imagine instituting a shop policy of
not using the operators containing '?' as soon as you had a release where
there were alternatives.  So in that context you might have a fix
available as soon as 9.6 came out.  But from the perspective of a driver
author who has to support queries written by other people, the problem
would not be gone for at least ten years more.  Changing the driver's
behavior sounds like a more practical solution.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION

[Simon Riggs]

On 18 May 2015 at 12:33, Alvaro Herrera  wrote:

> Bruce Momjian wrote:
> > On Sun, May 17, 2015 at 09:31:47PM +0200, José Luis Tallón wrote:
> > > On 05/17/2015 07:39 PM, Tom Lane wrote:
> > > >=?windows-1252?Q?Jos=E9_Luis_Tall=F3n?= 
> writes:
> > > >>On the other hand, ISTM that what we all intend to achieve is some
> > > >>Postgres equivalent of the SUID bit... so why not just do something
> > > >>equivalent?
> > > >>---
> > > >>  LOGIN-- as user with the appropriate role membership /
> privilege?
> > > >>  ...
> > > >>  SET ROLE / SET SESSION AUTHORIZATION WITH COOKIE / IMPERSONATE
> > > >>  ... do whatever ...-- unprivileged user can NOT do the
> > > >>"impersonate" thing
> > > >>  DISCARD ALL-- implicitly restore previous authz
> > > >>---
> > > >Oh?  What stops the unprivileged user from doing DISCARD ALL?
> > >
> > > Indeed. The pooler would need to block this.
> > > Or we would need to invent another (this time, privileged) verb in
> > > order to restore authz.
> >
> > What if you put the SQL in a function then call the function?  I don't
> > see how the pooler could block this.
>
> I think the idea of having SET SESSION AUTH pass a cookie, and only let
> RESET SESSION AUTH work when the same cookie is supplied, is pretty
> reasonable.
>

As long as the cookie is randomly generated for each use, then I don't see
a practical problem with that approach.

Protocol level solution means we have to wait 1.5 years before anybody can
begin using that. I'm also dubious that a small hole in the protocol
arrangements could slam that door shut because we couldn't easily backpatch.

Having an in-core pooler would be just wonderful because then we could more
easily trust it and we wouldn't need to worry.

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[Mike Blackwell]

​Ah.  I see.  Thanks for the clarification.​

__
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
mike.blackw...@rrd.com
http://www.rrdonnelley.com



* *

On Tue, May 19, 2015 at 1:44 PM, Bruno Harbulot  wrote:

>
>
> On Tue, May 19, 2015 at 7:22 PM, Tom Lane  wrote:
>
>> Mike Blackwell  writes:
>> > See for example
>> > http://docs.oracle.com/cd/B19306_01/text.102/b14218/cqoper.htm#i997330,
>> > Table 3-1, third row, showing the precedence of '?'.  Further down the
>> > page, under "Fuzzy" see "Backward Compatibility Syntax".
>>
>> If I'm reading that right, that isn't a SQL-level operator but an operator
>> in their text search query language, which would only appear in SQL
>> queries within string literals (compare tsquery's query operators in PG).
>> So it wouldn't be a hazard for ?-substitution, as long as the substituter
>> was bright enough to not change string literals.
>>
>> regards, tom lane
>>
>
> That's how I read it too. I've tried this little test:
> http://sqlfiddle.com/#!4/7436b/4/0
>
> CREATE TABLE test_table (
>   id INTEGER PRIMARY KEY,
>   name VARCHAR(100)
> );
>
> INSERT INTO test_table (id, name) VALUES (1, 'Nicole');
> INSERT INTO test_table (id, name) VALUES (2, 'Nicholas');
> INSERT INTO test_table (id, name) VALUES (3, 'Robert');
> INSERT INTO test_table (id, name) VALUES (4, 'Michael');
> INSERT INTO test_table (id, name) VALUES (5, 'Nicola');
>
> CREATE INDEX idx_test_table_name ON test_table(name) INDEXTYPE IS
> CTXSYS.CONTEXT;
>
> SELECT * FROM test_table WHERE CONTAINS(name, '?Nicolas', 1) > 0;
>
>
> Fuzzy matching works indeed, but the question mark is part of the literal
> (similarly to % when using LIKE).
>
> Best wishes,
>
> Bruno.
>
>

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


Dave Cramer opined:
> It would seem that choosing ? for operators was ill advised; I'm not
> convinced that deprecating them is a bad idea. If we start now, in 5 years
> they should be all but gone

Ha ha ha ha ha! That's a good one. We still have clients on Postgres 7!
Five years is way too short to replace something that major.

> Agreed a patch would be the first place to start

That, or just running the idea up the flagpole on -general. I'm a fairly 
strong -1 at the moment, but will listen to arguments.

- -- 
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201505191448
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAlVbhfcACgkQvJuQZxSWSsguxwCgrMPQKa9tTfMcuv4jRnqM6mNg
OvAAmgLNpa2vsiv+bkd7p4bUEx7op9ax
=sr9j
-END PGP SIGNATURE-




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION

[Andres Freund]

On 2015-05-19 14:41:06 -0400, Robert Haas wrote:
> On Tue, May 19, 2015 at 12:29 PM, Andres Freund  wrote:
> > On 2015-05-19 10:53:10 -0400, Robert Haas wrote:
> >> That seems like a kludge to me.  If the cookie leaks out somhow, which
> >> it will, then it'll be insecure.  I think the way to do this is with a
> >> protocol extension that poolers can enable on request.  Then they can
> >> just refuse to forward any "reset authorization" packets they get from
> >> their client.  There's no backward-compatibility break because the
> >> pooler can know, from the server version, whether the server is new
> >> enough to support the new protocol messages.
> >
> > That sounds like a worse approach to me. Don't you just need to hide the
> > session authorization bit in a function serverside to circumvent that?
> 
> I'm apparently confused.  There's nothing you can do to maintain
> security against someone who can load C code into the server.  I must
> be misunderstanding you.

It very well might be me that's confused. But what's stopping a user
from doing a "RESET SESSION AUTHORIZATION;" in a DO block or something?
I guess you are intending that a RESET SESSION AUTHORIZATION is only
allowed on a protocol level when the protocol extension is in use?

Greetings,

Andres Freund


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[Bruno Harbulot]

On Tue, May 19, 2015 at 7:22 PM, Tom Lane  wrote:

> Mike Blackwell  writes:
> > See for example
> > http://docs.oracle.com/cd/B19306_01/text.102/b14218/cqoper.htm#i997330,
> > Table 3-1, third row, showing the precedence of '?'.  Further down the
> > page, under "Fuzzy" see "Backward Compatibility Syntax".
>
> If I'm reading that right, that isn't a SQL-level operator but an operator
> in their text search query language, which would only appear in SQL
> queries within string literals (compare tsquery's query operators in PG).
> So it wouldn't be a hazard for ?-substitution, as long as the substituter
> was bright enough to not change string literals.
>
> regards, tom lane
>

That's how I read it too. I've tried this little test:
http://sqlfiddle.com/#!4/7436b/4/0

CREATE TABLE test_table (
  id INTEGER PRIMARY KEY,
  name VARCHAR(100)
);

INSERT INTO test_table (id, name) VALUES (1, 'Nicole');
INSERT INTO test_table (id, name) VALUES (2, 'Nicholas');
INSERT INTO test_table (id, name) VALUES (3, 'Robert');
INSERT INTO test_table (id, name) VALUES (4, 'Michael');
INSERT INTO test_table (id, name) VALUES (5, 'Nicola');

CREATE INDEX idx_test_table_name ON test_table(name) INDEXTYPE IS
CTXSYS.CONTEXT;

SELECT * FROM test_table WHERE CONTAINS(name, '?Nicolas', 1) > 0;


Fuzzy matching works indeed, but the question mark is part of the literal
(similarly to % when using LIKE).

Best wishes,

Bruno.

Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION

[Robert Haas]

On Tue, May 19, 2015 at 12:29 PM, Andres Freund  wrote:
> On 2015-05-19 10:53:10 -0400, Robert Haas wrote:
>> That seems like a kludge to me.  If the cookie leaks out somhow, which
>> it will, then it'll be insecure.  I think the way to do this is with a
>> protocol extension that poolers can enable on request.  Then they can
>> just refuse to forward any "reset authorization" packets they get from
>> their client.  There's no backward-compatibility break because the
>> pooler can know, from the server version, whether the server is new
>> enough to support the new protocol messages.
>
> That sounds like a worse approach to me. Don't you just need to hide the
> session authorization bit in a function serverside to circumvent that?

I'm apparently confused.  There's nothing you can do to maintain
security against someone who can load C code into the server.  I must
be misunderstanding you.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent 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 few thoughts on the schedule

[Joshua D. Drake]

On 05/19/2015 11:02 AM, Peter Geoghegan wrote:


Hasn't every talented reviewer gotten job offers shortly afterwards in
the last few years?  The ones that accept don't necessarily work that
much in the community, but several seem to. And I think in the case of
several people the reason they don't, is less the company, but that it's
emotionally draining.


I think that's very true, and often unacknowledged. Reviewing other
people's work can be very difficult. I do not enjoy conflict with
other people on this mailing list one bit, and that's getting harder
to deal with on a personal level over time, not easier.


Although I certainly understand your sentiment. It isn't personal. It is 
professional. If people are taking personally (and I certainly have), 
they need to step the heck back, take a breath and ask themselves what 
their problem is.


If they won't, then someone in the community needs to step up and help 
them do that. The more we remove the ID, the more productive we will become.


Sincerely,


JD



--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


--
Sent 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 few thoughts on the schedule

[Joshua D. Drake]

On 05/19/2015 10:44 AM, Andres Freund wrote:


I don't know what the solution is but I know I like the idea of a tree
freeze except for bug fixes for at least 3 weeks but I would be jumping for
joy if we froze the tree except for bug fixes for 6 or 12 weeks.


We've done that for pretty much every release so far?



That isn't really my experience at least from perception and I will be 
honest and I haven't followed the releases for 9.4 and 9.3 that much but 
it used to be:


Branch Tree
Accept patches for new tree and closed tree (bug fixes)

What I am suggesting is that we don't accept ANY patches that are not 
directly related to the closed tree that is being prepped for release.


I am not suggesting a shutdown of collaboration or discussion. I am 
trying (and perhaps failing) to find a way to steer everyone in a single 
direction for this release:


Our focus is the quality of 9.5, nothing else.




I don't care about 9.6 at this point.


But you don't develop things for it, so you're in a very different
position. It takes a *lot* of time to come up with a serious proposal


I would argue I develop a lot more than you consider. I have to deal 
with the end result that -hackers create on a much wider scale (as do 
most other consultants) than most do.



for a new feature, and then lots more time to come up with a reasonable
patch. To get a serious feature into 9.6 you pretty much have to already
have started by now.


Then extend the development time. Instead of 12-15 months, let's make it 
18-21 or 21-24 months or again, move to a staggered model (like Ubuntu).





We move so fast anyway, most people I know haven't even migrated to
9.4.x and even more are happily plugging away on 9.2.


I don't think that's really related to moving fast. It's just that
existing systems don't necessarily need to move - after all they could
put the system into production at their respective version.  That's
different to when you consider adopting/extending postgres for a new use
case/product.  And there people quit regularly lament a couple problems
in postgres. Say if we, and there's been serious talk about that,
addressed vacuuming being so painful, that'd certainly increase adoption
in the mid term.


This is true but doesn't negate my argument, it enforces it. Most people 
aren't going to be anywhere near disappointed if we slow down and focus 
on quality versus innovativeness.


Note: I am not saying we don't try to release quality software, of 
course we do.


JD

--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[Tom Lane]

Mike Blackwell  writes:
> See for example
> http://docs.oracle.com/cd/B19306_01/text.102/b14218/cqoper.htm#i997330,
> Table 3-1, third row, showing the precedence of '?'.  Further down the
> page, under "Fuzzy" see "Backward Compatibility Syntax".

If I'm reading that right, that isn't a SQL-level operator but an operator
in their text search query language, which would only appear in SQL
queries within string literals (compare tsquery's query operators in PG).
So it wouldn't be a hazard for ?-substitution, as long as the substituter
was bright enough to not change string literals.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[Mike Blackwell]

See for example
http://docs.oracle.com/cd/B19306_01/text.102/b14218/cqoper.htm#i997330,
Table 3-1, third row, showing the precedence of '?'.  Further down the
page, under "Fuzzy" see "Backward Compatibility Syntax".


__
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
mike.blackw...@rrd.com
http://www.rrdonnelley.com



* *

On Tue, May 19, 2015 at 12:45 PM, Bruno Harbulot <
br...@distributedmatter.net> wrote:

>
>
> On Tue, May 19, 2015 at 6:15 PM, Mike Blackwell 
> wrote:
>
>> A Google search suggests Oracle 9.x supports a unary '?' operator (fuzzy
>> match), so the use of '?' in an operator name is not without precedent.
>>
>>
> Interesting. Do you have any specific link? I'm probably not using the
> right Google search, but the nearest reference I've found is for Oracle 10,
> and it seems to use the tilde (~) operator for fuzzy matching:
> http://www.oracle.com/technetwork/search/oses/overview/new-query-features-in-10-1-8-2-1-132287.pdf
>
> Best wishes,
>
> Bruno.
>

Re: [HACKERS] a few thoughts on the schedule

[Peter Geoghegan]

On Tue, May 19, 2015 at 10:35 AM, Andres Freund  wrote:
> I'm not sure. ISTM that a painfull couple hours every now and then are
> much less bad than the continuous CF we had lately. I personally also
> find it frustrating to go through the CF and see a good portion of
> things that I never can see going anywhere, but that still suck up
> resources.
>
> I'd actually be willing to do triage every now and then; but I don't
> think it should always be the same person. For one it does come with
> power, for another it's nice to now always be the person having to tell
> people that their stuff isn't relevant/good/whatever enough. It's also
> not good to needlessly build up SPOFs.

It's pretty hard to tell someone that they're working on something
that doesn't matter to us. That's why it happens comparatively rarely.
If I told some new contributor that their patch was not worth our
time, I'd fully expect some other experienced hacker to show up 5
minutes later and tell me I'm wrong, unless perhaps the idea was
shockingly bad, which is rare.

>> Unless talented reviewers can get such job offers, we are going to
>> continue to have trouble making ends meet.
>
> Hasn't every talented reviewer gotten job offers shortly afterwards in
> the last few years?  The ones that accept don't necessarily work that
> much in the community, but several seem to. And I think in the case of
> several people the reason they don't, is less the company, but that it's
> emotionally draining.

I think that's very true, and often unacknowledged. Reviewing other
people's work can be very difficult. I do not enjoy conflict with
other people on this mailing list one bit, and that's getting harder
to deal with on a personal level over time, not easier.

-- 
Peter Geoghegan


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Per row status during INSERT .. ON CONFLICT UPDATE?

[Peter Geoghegan]

On Tue, May 19, 2015 at 10:49 AM, Andres Freund  wrote:
>> The RETURNING clause just allows us to return columns, but am unable to
>> find a way to know 'what' happened to a given row.
>
> There previously has been discussion about extending RETURNING to allow
> to return the before/after row. But to me that's a mostly independent
> feature to ON CONFLICT.

That's certainly something we talked about. It could probably be done
with some kind of magical expression. I have to wonder how many of the
people that are sure that they need this really do, though. Is it
really natural to care about this distinction with idiomatic usage?

-- 
Peter Geoghegan


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Per row status during INSERT .. ON CONFLICT UPDATE?

[Andres Freund]

On 2015-05-19 17:53:09 +0530, Robins Tharakan wrote:
> Is there a way to know which rows were INSERTed and UPDATEd when doing a
> INSERT ... ON CONFLICT UPDATE? Probably via pseudo column indicating INSERT
> / UPDATE ?

No, not really.

> The RETURNING clause just allows us to return columns, but am unable to
> find a way to know 'what' happened to a given row.

There previously has been discussion about extending RETURNING to allow
to return the before/after row. But to me that's a mostly independent
feature to ON CONFLICT.

Greetings,

Andres Freund


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[Bruno Harbulot]

On Tue, May 19, 2015 at 6:15 PM, Mike Blackwell 
wrote:

> A Google search suggests Oracle 9.x supports a unary '?' operator (fuzzy
> match), so the use of '?' in an operator name is not without precedent.
>
>
Interesting. Do you have any specific link? I'm probably not using the
right Google search, but the nearest reference I've found is for Oracle 10,
and it seems to use the tilde (~) operator for fuzzy matching:
http://www.oracle.com/technetwork/search/oses/overview/new-query-features-in-10-1-8-2-1-132287.pdf

Best wishes,

Bruno.

Re: [HACKERS] a few thoughts on the schedule

[Andres Freund]

On 2015-05-19 09:43:54 -0700, Joshua D. Drake wrote:
> 
> On 05/18/2015 08:52 PM, Andres Freund wrote:
> 
> >Maybe we should forget them and just have monthly 'judgefests' where
> >some poor sod summarizes the current state and direction, and we then
> >collaboratively discuss whether we see things going anywhere and if not,
> >what would need to happen that they do.  And have a policy that "older"
> >patches should be preferred over newer ones; but at the same time cull
> >patches continually sitting at the tail end as 'not interesting'.
> >
> 
> I don't think this will be a productive solution. I would argue that any
> solution we come up with, somebody is going to think they got the short end
> of the stick. There will be someone that thinks it is inefficient, that it
> doesn't suit their needs or that it doesn't work in their paradigm. That is
> why we don't have a proper issue/bug tracker. That is why we are constantly
> "inventing here" instead of relying on the work of others (when it comes to
> this particular problem).

What does that have to do with the suggestion above? That seems entirely
unrelated to changing CFs to a different format.

> I don't know what the solution is but I know I like the idea of a tree
> freeze except for bug fixes for at least 3 weeks but I would be jumping for
> joy if we froze the tree except for bug fixes for 6 or 12 weeks.

We've done that for pretty much every release so far?


> I don't care about 9.6 at this point.

But you don't develop things for it, so you're in a very different
position. It takes a *lot* of time to come up with a serious proposal
for a new feature, and then lots more time to come up with a reasonable
patch. To get a serious feature into 9.6 you pretty much have to already
have started by now.

> We move so fast anyway, most people I know haven't even migrated to
> 9.4.x and even more are happily plugging away on 9.2.

I don't think that's really related to moving fast. It's just that
existing systems don't necessarily need to move - after all they could
put the system into production at their respective version.  That's
different to when you consider adopting/extending postgres for a new use
case/product.  And there people quit regularly lament a couple problems
in postgres. Say if we, and there's been serious talk about that,
addressed vacuuming being so painful, that'd certainly increase adoption
in the mid term.

Greetings,

Andres Freund


-- 
Sent 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 few thoughts on the schedule

[Andres Freund]

On 2015-05-19 10:25:49 -0400, Robert Haas wrote:
> On Mon, May 18, 2015 at 11:52 PM, Andres Freund  wrote:
> > I personally think the late close of the 9.4 cycle has alone thrings far
> > enough off track that we can't fairly evaluate a 5 CF schedule.
> 
> Oh, I agree with that.

Ah, ok.

> The vary earliest time frame that would make sense to me is to branch
> July 1st and start a CF on July 15th.

I'm wondering why the CF has to start after branching? Or is that just
two independent dates? The first week or so of the first CF won't have
much stuff ready for commit.

> Personally, given where we're at right now, I don't think an early
> fall release of 9.5 is going to be remotely practical.

Why? To me the last few beta periods were pretty drawn out affairs,
without much happening. Yes, there was the jsonb stuff in 9.4 delaying
the release, but that wasn't waiting for work, but a decision.  But most
of the time everyone was developing their stuff for the next cycle,
waiting for beta testers to come forward with bugs. Not very much of
that happened.

I think a shorter schedule might actually help us to both, get the open
issues closed sooner, and get more actual testing. Most people seem to
work with a "Oh, there's time left, I can do that later" attitude.

I mean if there'd actually be lots of people busy testing, sure, a long
beta makes sense for postgres (complex, contains critical data). But I
don't think that's happening.

> - Do 4 CommitFests as we have for past releases.  We could do July
> 15th, September 15th, November 15th, and January 15th; or we could do
> August 1st, October 1st, December 1st, and February 1st; or we could
> do August 15th, October 15th, December 15th, and February 15th.
> Probably, that last one isn't so good: starting on December 15th is
> going to suck.

I tend to agree that Dec 15 is a bad idea.

> > Maybe we should forget them and just have monthly 'judgefests' where
> > some poor sod summarizes the current state and direction, and we then
> > collaboratively discuss whether we see things going anywhere and if not,
> > what would need to happen that they do.  And have a policy that "older"
> > patches should be preferred over newer ones; but at the same time cull
> > patches continually sitting at the tail end as 'not interesting'.
> 
> I think we need to start by understanding that we need the
> contribution experience to be good for both patch authors and also for
> reviewers (including reviewers who are commiters).   We very much need
> to give new contributors a good experience of submitting patches and
> getting useful feedback and getting stuff committed.  I think it's
> clear that we could do a much better job at that, and the project
> would benefit enormously.

Agreed.  I think right now to succeed in the project you need to be
extraordinarily stubborn or patient. Which in turn comes with its own
set of problems in the long term, besides lower participation. The set
of qualities needed to succeed aren't the same that I see needed in the
project.

> However, doing a better job means spending more time on it, and we
> can't just demand that senior reviewers or contributors spend more
> time on it.  I mean, we can, I guess, but it will only breed
> frustration and resentment.  I'm not sure what the solution is here,
> but if it boils down to telling people who have put a lot of effort
> into the project over a long period of time that they are not doing
> enough, I'm here to say that won't work.

Agreed, that we can't just demand it. But I think without changing
anything the situation will just get worse and worse, because there'll
be few new senior people.

I think part of that is saying "no" more efficiently, upfront. Which is
why I really want the triage step.
a) It's much better for the project to not have several "junior" reviewers
   first spend time on a patch, then have a small flamefest, and then
   have somebody "senior" reject a patch in its entirety. That's a waste
   of everyone's effort and frustrating.
b) It's not that bad to hear a "no" as a new contributor soon after
   submission. It's something entirely different to go through a long
   bikeshedding, several revisions of reworking, just to be told in the
   end that it was a bad idea from the get go.

> So one problem that comes up in the context of your proposal is that
> it's likely to be hard to find the "poor sod" whose existence you
> hypothecate.  Maybe there is someone who will do that once or twice,
> but I think it'll be hard to keep that position filled over the long
> term.

I'm not sure. ISTM that a painfull couple hours every now and then are
much less bad than the continuous CF we had lately. I personally also
find it frustrating to go through the CF and see a good portion of
things that I never can see going anywhere, but that still suck up
resources.

I'd actually be willing to do triage every now and then; but I don't
think it should always be the same person. For one it does come

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[Dave Cramer]

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 19 May 2015 at 13:15, Mike Blackwell  wrote:

> A Google search suggests Oracle 9.x supports a unary '?' operator (fuzzy
> match), so the use of '?' in an operator name is not without precedent.
>

Interesting argument. There is considerable precedent where we take the
position that just because xyz supports it we don't.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[Mike Blackwell]

A Google search suggests Oracle 9.x supports a unary '?' operator (fuzzy
match), so the use of '?' in an operator name is not without precedent.


__
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
mike.blackw...@rrd.com
http://www.rrdonnelley.com



* *

On Tue, May 19, 2015 at 10:03 AM, Bruno Harbulot <
br...@distributedmatter.net> wrote:

>
>
> On Tue, May 19, 2015 at 3:23 PM, Kevin Grittner  wrote:
>
>> David G. Johnston  wrote:
>> > On Mon, May 18, 2015 at 3:31 PM, Bruno Harbulot <
>> br...@distributedmatter.net>wrote:
>>
>> >> In the discussion on the OpenJDK JDBC list two years ago
>> >> (
>> http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/50.html
>> ),
>> >> Lance Andersen said "There is nothing in the SQL standard that
>> >> would support the use of an '?' as anything but a parameter
>> >> marker.".
>>
>> > ​"​CREATE OPERATOR is a PostgreSQL extension. There are no
>> > provisions for user-defined operators in the SQL standard."
>>
>> Exactly.  The standard specifies the characters to use for the
>> predicates that it defines, and provides no mechanism for adding
>> additional predicates; but who in the world would want to exclude
>> all extensions to the standard?
>>
>
> I was certainly not suggesting custom operators should be excluded. I was
> suggesting using something that was actually not incompatible with the SQL
> standards (and, even with standards aside, the expectations implementors
> have regarding the question mark, since it affects other tools too).
>
>
>
>> > ​And by extension if indeed the standard does require the use of
>> > "?" for parameters we are in violation there because the backend
>> > protocol deals with $# placeholders and not "?"​
>>
>> We're talking about a different specification that has question
>> marks as parameter placeholders.  That's in the Java Database
>> Connector (JDBC) specification.  (It is apparently also specified
>> in other documents, although I'm not familiar enough with those to
>> comment.)  Note that it would create all sorts of pain if both the
>> SQL statements and a connector issuing them used the same
>> convention for substituting parameters; it is a *good* thing that
>> plpgsql and SQL function definitions use a different convention
>> than JDBC!
>>
>
> Actually, we were not just talking about JDBC. I don't know the
> specifications in details, but the SQL:201x (preliminary) documents linked
> from
> https://wiki.postgresql.org/wiki/Developer_FAQ#Where_can_I_get_a_copy_of_the_SQL_standards.3F
> seem to have some information. The Foundation document (Section 4.25
> Dynamic SQL concepts) says that dynamic parameters are represented by a
> question mark.
>
> In addition, the BNF grammar available at
> http://www.savage.net.au/SQL/sql-2003-2.bnf.html#dynamic%20parameter%20specification
> also says:
> ::=   
>
> I'm not familiar enough with these documents to know whether I'm missing
> some context, but it would seem that the question mark is a reserved
> character, beyond the scope of JDBC (at the very least, it seems
> incompatible with Dynamic SQL and its implementation in ECPG).
>
> Best wishes,
>
> Bruno.
>
>

Re: [HACKERS] a few thoughts on the schedule

[Joshua D. Drake]

On 05/18/2015 08:52 PM, Andres Freund wrote:


Maybe we should forget them and just have monthly 'judgefests' where
some poor sod summarizes the current state and direction, and we then
collaboratively discuss whether we see things going anywhere and if not,
what would need to happen that they do.  And have a policy that "older"
patches should be preferred over newer ones; but at the same time cull
patches continually sitting at the tail end as 'not interesting'.



I don't think this will be a productive solution. I would argue that any 
solution we come up with, somebody is going to think they got the short 
end of the stick. There will be someone that thinks it is inefficient, 
that it doesn't suit their needs or that it doesn't work in their 
paradigm. That is why we don't have a proper issue/bug tracker. That is 
why we are constantly "inventing here" instead of relying on the work of 
others (when it comes to this particular problem).


I don't know what the solution is but I know I like the idea of a tree 
freeze except for bug fixes for at least 3 weeks but I would be jumping 
for joy if we froze the tree except for bug fixes for 6 or 12 weeks.


I don't care about 9.6 at this point. We move so fast anyway, most 
people I know haven't even migrated to 9.4.x and even more are happily 
plugging away on 9.2.


Consider that yes, we have a ton of people that migrated to 9.4 but 
those generally aren't people running the 24x7 enterprise class database.


It will not hurt us, and will only help us to slow down for this 
release. If 9.6 gets pushed until Winter 2017, so what.


Let's release Alpha1, start promoting the heck out of it amongst the 
community and early adopting (for NON PRODUCTION) developers. Let's make 
it easy as snot dripping from a toddlers nose to submit bug reports. 
Let's verify those things and let's produce the most solid, reliable and 
bug free PostgreSQL version, ever.


The summer is nigh and it is going to be slow going anyway.


JD



--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION

[Andres Freund]

On 2015-05-19 10:53:10 -0400, Robert Haas wrote:
> That seems like a kludge to me.  If the cookie leaks out somhow, which
> it will, then it'll be insecure.  I think the way to do this is with a
> protocol extension that poolers can enable on request.  Then they can
> just refuse to forward any "reset authorization" packets they get from
> their client.  There's no backward-compatibility break because the
> pooler can know, from the server version, whether the server is new
> enough to support the new protocol messages.

That sounds like a worse approach to me. Don't you just need to hide the
session authorization bit in a function serverside to circumvent that?

Greetings,

Andres Freund


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] errmsg() clobbers errno

[Tom Lane]

John Gorman  writes:
> While debugging an extension I discovered that the errmsg()
> function zeros out errno.

So might a lot of other functions used in an ereport's arguments.

> This is annoying because if the process of assembling a meaningful
> error message happens to call errmsg() before calling strerror()
> we lose the strerror information.

This is why you should use %m and not strerror(errno).  The infrastructure
for %m is set up so that errno is captured before evaluating any of the
ereport's arguments.

> I am attaching a patch to preserve errno across errmsg() calls.

This is pretty useless, unfortunately, because there are just too
many ways to bite yourself on the rear if you reference errno inside
the arguments of an ereport (or any other complicated nest of function
calls).

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Wrong Assert in PageIndexMultiDelete?

[Heikki Linnakangas]

On 05/19/2015 07:15 PM, Tom Lane wrote:

Anastasia Lubennikova  writes:

I am trying to create new index access method.
And I found strange Assert in PageIndexMultiDelete
 function.



Assert
(nitems
< MaxIndexTuplesPerPage

);



Is '<' sign is correct? I thougt it should be '<='.
Is it a bug or just my misunderstanding?


Hm, I think it's a bug.  It's probably not very significant because
MaxIndexTuplesPerPage is an overestimate (it doesn't account for index
special space), but it's wrong AFAICS.

That Assert hasn't been there very long, either --- seems to have been
added in 877b0887.  Heikki, did you have some specific reason for writing
it like that?


No, it looks like a bug to me as well. Will fix...

- Heikki



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Wrong Assert in PageIndexMultiDelete?

[Tom Lane]

Anastasia Lubennikova  writes:
> I am trying to create new index access method.
> And I found strange Assert in PageIndexMultiDelete
>  function.

> Assert
> (nitems
> < MaxIndexTuplesPerPage
> 
> );

> Is '<' sign is correct? I thougt it should be '<='.
> Is it a bug or just my misunderstanding?

Hm, I think it's a bug.  It's probably not very significant because
MaxIndexTuplesPerPage is an overestimate (it doesn't account for index
special space), but it's wrong AFAICS.

That Assert hasn't been there very long, either --- seems to have been
added in 877b0887.  Heikki, did you have some specific reason for writing
it like that?

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] errmsg() clobbers errno

[John Gorman]

Hi All

While debugging an extension I discovered that the errmsg()
function zeros out errno.

This is annoying because if the process of assembling a meaningful
error message happens to call errmsg() before calling strerror()
we lose the strerror information. This is exactly the time when we
want to preserve any available error state.

I am attaching a patch to preserve errno across errmsg() calls.

Does this seem like a good idea?

Best, John


errmsg-errno-v1.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] INSERT ... ON CONFLICT DO UPDATE with _any_ constraint

[Geoff Winkless]

On 19 May 2015 at 16:32, I wrote:

> In the event that the INSERT triggers a constraint that the UPDATE fails
> to resolve, it will still fail in exactly the same way that running the ON
> CONFLICT on a specific constraint would fail, so it's not like you gain any
> extra value from specifying the constraint, is it?
>

​I don't know why I wrote this paragraph, it's just the product of me
thinking of something else at the same time:
 UPDATE obviously doesn't resolve a conflict as such.


Thinking about it more, I suppose if multiple constraints end up triggering
for the same INSERT, it would require UPDATEs of multiple rows. Is that the
issue?

Geoff

[HACKERS] INSERT ... ON CONFLICT DO UPDATE with _any_ constraint

[Geoff Winkless]

I finally got around to running some UPSERT tests on the development build,
which is very exciting for me :)

I'm not sure if I missed the point with this (probably...): I'm unclear on
the reason why DO UPDATE requires explicitly specifying the constraint
while DO NOTHING does not.

If it's a feature of the locking implementation (or something) that for "DO
UPDATE" only one index can be used, then so be it.

However if it would be possible to allow any conflict to run the UPDATE
clause (in the same way as any conflict triggers DO NOTHING in the
alternate form) I would personally find that very pleasant.

You could even then arbitrate on conflicts in the UPDATE clause, if you had
to, using (say)

INSERT INTO mytable ...
ON CONFLICT DO UPDATE SET
  col1=CASE
WHEN mytable.uniquefield1=excluded.uniquefield1 THEN targettedvalue1
ELSE mytable.col1
  END,
  col2=CASE
WHEN mytable.uniquefield2=excluded.uniquefield2 THEN targettedvalue2
  ELSE mytable.col2
  END;

Not exactly pretty but workable.

I just find it slightly upsetting that for (what I would expect is) the
majority use case (when the INSERT would only ever trigger one unique
constraint) one must still define the unique fields.

In the event that the INSERT triggers a constraint that the UPDATE fails to
resolve, it will still fail in exactly the same way that running the ON
CONFLICT on a specific constraint would fail, so it's not like you gain any
extra value from specifying the constraint, is it?

As I said, I probably missed the point.

Geoff

[HACKERS] Wrong Assert in PageIndexMultiDelete?

[Anastasia Lubennikova]

Hi, hackers!

I am trying to create new index access method.
And I found strange Assert in PageIndexMultiDelete
 function.

Assert
(nitems
< MaxIndexTuplesPerPage

);

Is '<' sign is correct? I thougt it should be '<='.
Is it a bug or just my misunderstanding?

-- 
Best regards,
Lubennikova Anastasia

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[Bruno Harbulot]

On Tue, May 19, 2015 at 3:23 PM, Kevin Grittner  wrote:

> David G. Johnston  wrote:
> > On Mon, May 18, 2015 at 3:31 PM, Bruno Harbulot <
> br...@distributedmatter.net>wrote:
>
> >> In the discussion on the OpenJDK JDBC list two years ago
> >> (
> http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/50.html
> ),
> >> Lance Andersen said "There is nothing in the SQL standard that
> >> would support the use of an '?' as anything but a parameter
> >> marker.".
>
> > ​"​CREATE OPERATOR is a PostgreSQL extension. There are no
> > provisions for user-defined operators in the SQL standard."
>
> Exactly.  The standard specifies the characters to use for the
> predicates that it defines, and provides no mechanism for adding
> additional predicates; but who in the world would want to exclude
> all extensions to the standard?
>

I was certainly not suggesting custom operators should be excluded. I was
suggesting using something that was actually not incompatible with the SQL
standards (and, even with standards aside, the expectations implementors
have regarding the question mark, since it affects other tools too).



> > ​And by extension if indeed the standard does require the use of
> > "?" for parameters we are in violation there because the backend
> > protocol deals with $# placeholders and not "?"​
>
> We're talking about a different specification that has question
> marks as parameter placeholders.  That's in the Java Database
> Connector (JDBC) specification.  (It is apparently also specified
> in other documents, although I'm not familiar enough with those to
> comment.)  Note that it would create all sorts of pain if both the
> SQL statements and a connector issuing them used the same
> convention for substituting parameters; it is a *good* thing that
> plpgsql and SQL function definitions use a different convention
> than JDBC!
>

Actually, we were not just talking about JDBC. I don't know the
specifications in details, but the SQL:201x (preliminary) documents linked
from
https://wiki.postgresql.org/wiki/Developer_FAQ#Where_can_I_get_a_copy_of_the_SQL_standards.3F
seem to have some information. The Foundation document (Section 4.25
Dynamic SQL concepts) says that dynamic parameters are represented by a
question mark.

In addition, the BNF grammar available at
http://www.savage.net.au/SQL/sql-2003-2.bnf.html#dynamic%20parameter%20specification
also says:
::=   

I'm not familiar enough with these documents to know whether I'm missing
some context, but it would seem that the question mark is a reserved
character, beyond the scope of JDBC (at the very least, it seems
incompatible with Dynamic SQL and its implementation in ECPG).

Best wishes,

Bruno.

Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION

[Robert Haas]

On Mon, May 18, 2015 at 12:33 PM, Alvaro Herrera
 wrote:
> Bruce Momjian wrote:
>> On Sun, May 17, 2015 at 09:31:47PM +0200, José Luis Tallón wrote:
>> > On 05/17/2015 07:39 PM, Tom Lane wrote:
>> > >=?windows-1252?Q?Jos=E9_Luis_Tall=F3n?=  
>> > >writes:
>> > >>On the other hand, ISTM that what we all intend to achieve is some
>> > >>Postgres equivalent of the SUID bit... so why not just do something
>> > >>equivalent?
>> > >>---
>> > >>  LOGIN-- as user with the appropriate role membership / 
>> > >> privilege?
>> > >>  ...
>> > >>  SET ROLE / SET SESSION AUTHORIZATION WITH COOKIE / IMPERSONATE
>> > >>  ... do whatever ...-- unprivileged user can NOT do the
>> > >>"impersonate" thing
>> > >>  DISCARD ALL-- implicitly restore previous authz
>> > >>---
>> > >Oh?  What stops the unprivileged user from doing DISCARD ALL?
>> >
>> > Indeed. The pooler would need to block this.
>> > Or we would need to invent another (this time, privileged) verb in
>> > order to restore authz.
>>
>> What if you put the SQL in a function then call the function?  I don't
>> see how the pooler could block this.
>
> I think the idea of having SET SESSION AUTH pass a cookie, and only let
> RESET SESSION AUTH work when the same cookie is supplied, is pretty
> reasonable.

That seems like a kludge to me.  If the cookie leaks out somhow, which
it will, then it'll be insecure.  I think the way to do this is with a
protocol extension that poolers can enable on request.  Then they can
just refuse to forward any "reset authorization" packets they get from
their client.  There's no backward-compatibility break because the
pooler can know, from the server version, whether the server is new
enough to support the new protocol messages.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[Dave Cramer]

On 19 May 2015 at 10:23, Kevin Grittner  wrote:

> David G. Johnston  wrote:
> > On Mon, May 18, 2015 at 3:31 PM, Bruno Harbulot <
> br...@distributedmatter.net>wrote:
>
> >> In the discussion on the OpenJDK JDBC list two years ago
> >> (
> http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/50.html
> ),
> >> Lance Andersen said "There is nothing in the SQL standard that
> >> would support the use of an '?' as anything but a parameter
> >> marker.".
>
> > ​"​CREATE OPERATOR is a PostgreSQL extension. There are no
> > provisions for user-defined operators in the SQL standard."
>
> Exactly.  The standard specifies the characters to use for the
> predicates that it defines, and provides no mechanism for adding
> additional predicates; but who in the world would want to exclude
> all extensions to the standard?
>
> > ​And by extension if indeed the standard does require the use of
> > "?" for parameters we are in violation there because the backend
> > protocol deals with $# placeholders and not "?"​
>
> We're talking about a different specification that has question
> marks as parameter placeholders.  That's in the Java Database
> Connector (JDBC) specification.  (It is apparently also specified
> in other documents, although I'm not familiar enough with those to
> comment.)  Note that it would create all sorts of pain if both the
> SQL statements and a connector issuing them used the same
> convention for substituting parameters; it is a *good* thing that
> plpgsql and SQL function definitions use a different convention
> than JDBC!
>
> The JDBC spec provides for escapes using curly braces (including
> product-specific escapes); it seems like a big mistake for us to
> have chosen a completely different mechanism for escaping the
> question mark character in a SQL statement.  Perhaps the least
> painful path would be to add support for {?} as the escape for a
> question mark, and a connection option to supplement that with
> support for the legacy \? escape.  I would bet a lot of money that
> even with an "if" test for that option, the curly brace escape
> would be faster than what's there now (when the option was not
> set).  Some operators would look a little funny in Java string
> literals, but that's not so bad.
>

Perhaps reviewing https://github.com/pgjdbc/pgjdbc/pull/187 might help
understand why we chose ??

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

Re: [HACKERS] Run pgindent now?

[Tom Lane]

Robert Haas  writes:
> On Mon, May 18, 2015 at 6:53 PM, Tom Lane  wrote:
>> Would it alleviate your concern any if we eased into this, like say only
>> apply the back-branch pgindent run to 9.5 and later branches?  Then at
>> least I could foresee the end of that particular annoyance.

> If we do this only beginning with 9.5, and if we can make the output
> 100% consistent across branches, and if we run it before EVERY minor
> release so that people don't see unrelated diffs between consecutive
> tarballs, then it would address my concerns.

To do it before every minor release would require re-indenting HEAD
as well (since the whole point is to keep HEAD and the back branches
consistent).  I think we'd get too much push-back from developers
whose pending patches got broken.  We can get away with reindenting
HEAD between development cycles, but probably not more often than that.

I'm not particularly concerned by the tarball-diff argument: running
diff with --ignore-spaces should mask most of the changes.  Moreover,
assuming the code was properly indented at x.y.0 release time, any
changes applied by pgindent would only be within subsequent back-patches,
which hopefully are a very small part of the code.  (Perhaps it would be
useful to do a trial indent on some old branch right now, just to see how
large the diffs are; then we'd have some actual facts in this argument...)

And lastly, committers who are bothered by the prospect of such changes
could take the time to reindent their back-patched changes before
committing in the first place.  (FWIW, I usually do, and it's not hard
except in files that have been heavily mangled in HEAD.)

> I wish that pgident could be made more automated, like by having it
> fully built into the tree so that you can type 'make indent', or by
> having a daemon that would automatically pgindent the main tree
> periodically (say, once a month, or when more than X number of
> lines/files have changed, whichever comes first).  I still find it
> quite a hassle to set up and run.

It is a pain.  I have a shell script that fetches the typedef list
automatically, which helps.  The main problem with a "make indent" target
is that only in Bruce's annual runs do we really want to let it loose on
the whole tree.  In manual fixups, I only point it at the files I've
edited (and then, often, I have to remove some diffs in unrelated parts
of those files).  I wish that could be a bit easier, though I'm not sure
how.

regards, tom lane


-- 
Sent 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 few thoughts on the schedule

[Robert Haas]

On Mon, May 18, 2015 at 11:52 PM, Andres Freund  wrote:
>> > [first 9.6 CF around 2015-07-15]
>
>> Honestly, that seems awful soon.  I would have thought maybe August 15th.
>
> Maybe we should just rename it to 9.6-1 for now? And then look how
> things look around pgcon?

I'd rather agree on a date.  People need to plan their schedules.

>> I am inclined to think the 5-CommitFest thing we did this time did not
>> work out. It might've been fine if feature freeze had been a month
>> earlier, but by freezing in May we've pretty clearly stolen at least a
>> month, if not two, from the next cycle.
>
> I personally think the late close of the 9.4 cycle has alone thrings far
> enough off track that we can't fairly evaluate a 5 CF schedule.

Oh, I agree with that.  I'm certainly not saying we shouldn't do it
again.  But I don't see a practical way to do 5 CFs again for 9.6 and
also release it in September of 2016.  I don't think it would be a
good idea to open the tree for 9.6 development in three weeks, or even
in time for a July 1st CommitFest.  The vary earliest time frame that
would make sense to me is to branch July 1st and start a CF on July
15th.  If we schedule four more CommitFests after that at two month
intervals, they would start on September 15th, November 15th, January
15th, and March 15th, putting us a month behind where we were this
time.  That's not going to work.

So I think the options are:

- Do 4 CommitFests as we have for past releases.  We could do July
15th, September 15th, November 15th, and January 15th; or we could do
August 1st, October 1st, December 1st, and February 1st; or we could
do August 15th, October 15th, December 15th, and February 15th.
Probably, that last one isn't so good: starting on December 15th is
going to suck.

- Do 5 or more CommitFests and accept that the release cycle is going
to be more than a year.  Personally, given where we're at right now, I
don't think an early fall release of 9.5 is going to be remotely
practical.  I think we're going to end up releasing in late fall or
early in the new year.  So I'd be completely fine with a schedule that
aims for 9.6 to get released around March-May of 2017, so the last
CommitFest would start in August or September of 2016.  I expect that
to be unpopular, which is fine, but then I think we have to limit
ourselves to 4 CFs this time through.

> Personally I'm coming more and more to the conclusion that CFs just
> don't work [anymore]. I think the *tracking* itself is rather important
> and has a worthwhile role. But it seems to me that what CFs have lately
> essentially ended up being, is closer to a cycle long review queue than
> anything else.

I mostly agree with that.

> ISTM that the CF scheduling right now does more harm than good.
> * They seem to frustrate a lot of the people doing a lot of
>   reviews.
> * Evidently they don't very well prevent individual patches from
>   just slipping through and through.
> * They lead to completely uninteresting patches being reviewed before
> others.
> * The contribution experience is still pretty painful and takes ages

Those are legitimate issues.

> Maybe we should forget them and just have monthly 'judgefests' where
> some poor sod summarizes the current state and direction, and we then
> collaboratively discuss whether we see things going anywhere and if not,
> what would need to happen that they do.  And have a policy that "older"
> patches should be preferred over newer ones; but at the same time cull
> patches continually sitting at the tail end as 'not interesting'.

I think we need to start by understanding that we need the
contribution experience to be good for both patch authors and also for
reviewers (including reviewers who are commiters).   We very much need
to give new contributors a good experience of submitting patches and
getting useful feedback and getting stuff committed.  I think it's
clear that we could do a much better job at that, and the project
would benefit enormously.  However, doing a better job means spending
more time on it, and we can't just demand that senior reviewers or
contributors spend more time on it.  I mean, we can, I guess, but it
will only breed frustration and resentment.  I'm not sure what the
solution is here, but if it boils down to telling people who have put
a lot of effort into the project over a long period of time that they
are not doing enough, I'm here to say that won't work.

So one problem that comes up in the context of your proposal is that
it's likely to be hard to find the "poor sod" whose existence you
hypothecate.  Maybe there is someone who will do that once or twice,
but I think it'll be hard to keep that position filled over the long
term.

Unfortunately, I don't have a lot of good ideas here.  I know that I
spend as much time reviewing other people's patches as I can manage to
find in my schedule, and I know a lot of people would probably like to
see me do more of that.  I'm sure there are also some people who w

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[Kevin Grittner]

David G. Johnston  wrote:
> On Mon, May 18, 2015 at 3:31 PM, Bruno Harbulot 
> wrote:

>> In the discussion on the OpenJDK JDBC list two years ago
>> ( 
>> http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/50.html
>>  ),
>> Lance Andersen said "There is nothing in the SQL standard that
>> would support the use of an '?' as anything but a parameter
>> marker.".

> ​"​CREATE OPERATOR is a PostgreSQL extension. There are no
> provisions for user-defined operators in the SQL standard."

Exactly.  The standard specifies the characters to use for the
predicates that it defines, and provides no mechanism for adding
additional predicates; but who in the world would want to exclude
all extensions to the standard?

> ​And by extension if indeed the standard does require the use of
> "?" for parameters we are in violation there because the backend
> protocol deals with $# placeholders and not "?"​

We're talking about a different specification that has question
marks as parameter placeholders.  That's in the Java Database
Connector (JDBC) specification.  (It is apparently also specified
in other documents, although I'm not familiar enough with those to
comment.)  Note that it would create all sorts of pain if both the
SQL statements and a connector issuing them used the same
convention for substituting parameters; it is a *good* thing that
plpgsql and SQL function definitions use a different convention
than JDBC!

The JDBC spec provides for escapes using curly braces (including
product-specific escapes); it seems like a big mistake for us to
have chosen a completely different mechanism for escaping the
question mark character in a SQL statement.  Perhaps the least
painful path would be to add support for {?} as the escape for a
question mark, and a connection option to supplement that with
support for the legacy \? escape.  I would bet a lot of money that
even with an "if" test for that option, the curly brace escape
would be faster than what's there now (when the option was not
set).  Some operators would look a little funny in Java string
literals, but that's not so bad.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Run pgindent now?

[Tom Lane]

Andrew Dunstan  writes:
> Tom, if you want  to get dromedary reporting on all branches, just 
> remove the "branches => [ 'HEAD' ]," from the config.

dromedary is a pretty slow machine, so I'm going to pass on that unless
there's a good reason to think it would find typedefs your machines don't.
I rather doubt that --- our use of platform-dependent typedefs is fairly
small and stable, so it seems like checking HEAD should be sufficient.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

[Dave Cramer]

On 18 May 2015 at 18:49, David G. Johnston 
wrote:

> On Mon, May 18, 2015 at 3:31 PM, Bruno Harbulot <
> br...@distributedmatter.net> wrote:
>
>> On Sun, May 17, 2015 at 5:15 PM, Greg Sabino Mullane 
>> wrote:
>>
>>
>>>
>>> > In that case my vote is new operators. This has been a sore point for
>>> the
>>> > JDBC driver
>>>
>>> Um, no, new operators is a bad idea. Question marks are used by hstore,
>>> json, geometry, and who knows what else. I think the onus is solely on
>>> JDBC to solve this problem. DBD::Pg solved it in 2008 with
>>> the pg_placeholder_dollaronly solution, and earlier this year by allowing
>>> backslashes before the question mark (because other parts of the stack
>>> were
>>> not able to smoothly implement pg_placeholder_dollaronly.) I recommend
>>> all drivers implement \? as a semi-standard workaround.
>>>
>>> See also:
>>> http://blog.endpoint.com/2015/01/dbdpg-escaping-placeholders-with.html
>>>
>>>
>> I'm not sure the onus is solely on JDBC. Using question marks in
>> operators clearly has required a number of connectors to implement their
>> own workarounds, in different ways. This also seems to affect some
>> libraries and frameworks that depend on those connectors (and for which the
>> workarounds may even be more convoluted).
>>
>> My main point was that this is not specific to JDBC. Considering that
>> even PostgreSQL's own ECPG is affected, the issue goes probably deeper than
>> it seems. I'm just not convinced that passing the problem onto connectors,
>> libraries and ultimately application developers is the right thing to do
>> here.
>>
>> In the discussion on the OpenJDK JDBC list two years ago (
>> http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/50.html
>> ), Lance Andersen said "There is nothing in the SQL standard that would
>> support the use of an '?' as anything but a parameter marker.". It might be
>> worth finding out whether this is indeed the case according to the SQL
>> specifications (I'm afraid I'm not familiar with these specifications to do
>> it myself).
>>
>
> ​"​CREATE OPERATOR is a PostgreSQL extension. There are no provisions for
> user-defined operators in the SQL standard."
>
> http://www.postgresql.org/docs/9.4/interactive/sql-createoperator.html
>
> ​And by extension if indeed the standard does require the use of "?" for
> parameters we are in violation there because the backend protocol deals
> with $# placeholders and not "?"​
>
> ​I too do not know enough here.
>
> Note that it would not be enough to change the existing operators - any
> use of "?" would have to be forbidden including those created by users.​
>
> The first step on this path would be for someone to propose a patch adding
> alternative operators for every existing operator that uses "?".  If this
> idea is to move forward at all that patch would have to be accepted.  Such
> a patch is likely to see considerable bike-shedding.  We then at least
> provide an official way to avoid "?" operators that shops can make use of
> at their discretion.  Removing the existing operators or forbidding custom
> operators is a separate discussion.
>
> David J.​
>
>
It would seem that choosing ? for operators was ill advised; I'm not
convinced that deprecating them is a bad idea. If we start now, in 5 years
they should be all but gone

Agreed a patch would be the first place to start

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

Re: [HACKERS] Patch for bug #12845 (GB18030 encoding)

[Arjen Nienhuis]

>> That's fine when not every code point is used, but it's different for
>> GB18030 where almost all code points are used. Using a plain array
>> saves space and saves a binary search.
>
> Well, it doesn't save any space: if we get rid of the additional linear
> ranges in the lookup table, what remains is 30733 entries requiring about
> 256K, same as (or a bit less than) what you suggest.

We could do both. What about something like this:

static unsigned int utf32_to_gb18030_from_0x0001[1105] = {
/* 0x0 */ 0x1, 0x2, 0x3, 0x4, 0x5, 0x6, 0x7, 0x8,
...
static unsigned int utf32_to_gb18030_from_0x2010[1587] = {
/* 0x0 */ 0xa95c, 0x8136a532, 0x8136a533, 0xa843, 0xa1aa, 0xa844,
0xa1ac, 0x8136a534,
...
static unsigned int utf32_to_gb18030_from_0x2E81[28965] = {
/* 0x0 */ 0xfe50, 0x8138fd39, 0x8138fe30, 0xfe54, 0x8138fe31,
0x8138fe32, 0x8138fe33, 0xfe57,
...
static unsigned int utf32_to_gb18030_from_0xE000[2149] = {
/* 0x0 */ 0xaaa1, 0xaaa2, 0xaaa3, 0xaaa4, 0xaaa5, 0xaaa6, 0xaaa7, 0xaaa8,
...
static unsigned int utf32_to_gb18030_from_0xF92C[254] = {
/* 0x0 */ 0xfd9c, 0x84308535, 0x84308536, 0x84308537, 0x84308538,
0x84308539, 0x84308630, 0x84308631,
...
static unsigned int utf32_to_gb18030_from_0xFE30[464] = {
/* 0x0 */ 0xa955, 0xa6f2, 0x84318538, 0xa6f4, 0xa6f5, 0xa6e0, 0xa6e1, 0xa6f0,
...

static uint32
conv_utf8_to_18030(uint32 code)
{
uint32  ucs = utf8word_to_unicode(code);

#define conv_lin(minunicode, maxunicode, mincode) \
if (ucs >= minunicode && ucs <= maxunicode) \
return gb_unlinear(ucs - minunicode + gb_linear(mincode))

#define conv_array(minunicode, maxunicode) \
if (ucs >= minunicode && ucs <= maxunicode) \
return utf32_to_gb18030_from_##minunicode[ucs - minunicode];

conv_array(0x0001, 0x0452);
conv_lin(0x0452, 0x200F, 0x8130D330);
conv_array(0x2010, 0x2643);
conv_lin(0x2643, 0x2E80, 0x8137A839);
conv_array(0x2E81, 0x9FA6);
conv_lin(0x9FA6, 0xD7FF, 0x82358F33);
conv_array(0xE000, 0xE865);
conv_lin(0xE865, 0xF92B, 0x8336D030);
conv_array(0xF92C, 0xFA2A);
conv_lin(0xFA2A, 0xFE2F, 0x84309C38);
conv_array(0xFE30, 0x1);
conv_lin(0x1, 0x10, 0x90308130);
/* No mapping exists */
return 0;
}

>
> The point about possibly being able to do this with a simple lookup table
> instead of binary search is valid, but I still say it's a mistake to
> suppose that we should consider that only for GB18030.  With the reduced
> table size, the GB18030 conversion tables are not all that far out of line
> with the other Far Eastern conversions:
>
> $ size utf8*.so | sort -n
>textdata bss dec hex filename
>1880 512  162408 968 utf8_and_ascii.so
>2394 528  162938 b7a utf8_and_iso8859_1.so
>6674 512  1672021c22 utf8_and_cyrillic.so
>   24318 904  16   252386296 utf8_and_win.so
>   28750 968  16   297347426 utf8_and_iso8859.so
>  121110 512  16  121638   1db26 utf8_and_euc_cn.so
>  123458 512  16  123986   1e452 utf8_and_sjis.so
>  133606 512  16  134134   20bf6 utf8_and_euc_kr.so
>  185014 512  16  185542   2d4c6 utf8_and_sjis2004.so
>  185522 512  16  186050   2d6c2 utf8_and_euc2004.so
>  212950 512  16  213478   341e6 utf8_and_euc_jp.so
>  221394 512  16  221922   362e2 utf8_and_big5.so
>  274772 512  16  275300   43364 utf8_and_johab.so
>  26 512  16  278304   43f20 utf8_and_uhc.so
>  332262 512  16  332790   513f6 utf8_and_euc_tw.so
>  350640 512  16  351168   55bc0 utf8_and_gbk.so
>  496680 512  16  497208   79638 utf8_and_gb18030.so
>
> If we were to get excited about reducing the conversion time for GB18030,
> it would clearly make sense to use similar infrastructure for GBK, and
> perhaps the EUC encodings too.

I'll check them as well. If they have linear ranges it should work.

>
> However, I'm not that excited about changing it.  We have not heard field
> complaints about these converters being too slow.  What's more, there
> doesn't seem to be any practical way to apply the same idea to the other
> conversion direction, which means if you do feel there's a speed problem
> this would only halfway fix it.

It does work if you linearlize it first. That's why we need to convert
to utf32 first as well. That's a form of linearization.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Run pgindent now?

[Robert Haas]

On Mon, May 18, 2015 at 6:53 PM, Tom Lane  wrote:
>> I am personally not excited about that.  I would rather leave the
>> back-branches alone.
>
> It would be awfully nice though if we didn't have to deal with random
> cross-branch indenting differences.  I've lost, maybe not years off my
> life, but certainly weeks of not-very-pleasant make-work because of that.
> I'm surprised you've not had the same experience.

Well, there are a couple of things that worry me:

- People rely on us to ship, in minor releases, only critical security
and stability fixes.  Re-indenting the code is neither, and people may
not appreciate needless whitespace differences being shipped in the
next branch.  Anyone who diffs that tarball against the previous one
is going to see a bunch of stuff in there that may make them nervous.

- If pgindent doesn't handle every branch in exactly the same way,
it's possible that this change could exacerbate differences instead of
reducing them.  I actually think this is quite a likely outcome.

I personally have not found back-patching to have been significantly
complicated by whitespace differences.  There are certainly code
differences that can make it quite miserable in some cases, but I
cannot recall a case where there was an issue of this time due to
erratic indenting in one branch that had meanwhile been fixed in
another branch.  I accept that your experience may be different, of
course.

> Would it alleviate your concern any if we eased into this, like say only
> apply the back-branch pgindent run to 9.5 and later branches?  Then at
> least I could foresee the end of that particular annoyance.

If we do this only beginning with 9.5, and if we can make the output
100% consistent across branches, and if we run it before EVERY minor
release so that people don't see unrelated diffs between consecutive
tarballs, then it would address my concerns.

I wish that pgident could be made more automated, like by having it
fully built into the tree so that you can type 'make indent', or by
having a daemon that would automatically pgindent the main tree
periodically (say, once a month, or when more than X number of
lines/files have changed, whichever comes first).  I still find it
quite a hassle to set up and run.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Per row status during INSERT .. ON CONFLICT UPDATE?

[Marko Tiikkaja]

On 5/19/15 3:04 PM, Thom Brown wrote:

If you want the delta, you'll have to resort to a CTE:

e.g.

# WITH newvals AS (
 INSERT INTO test (name, age) VALUES ('James', 45)
ON CONFLICT (name)
DO UPDATE SET age = EXCLUDED.age
RETURNING *)
SELECT n.name, o.age as "old.age", n.age as "new.age"
FROM test o RIGHT JOIN newvals n on o.name = n.name;

  name  | old.age | new.age
---+-+-
  James |  44 |  45
(1 row)


Also note that the old value is not the actual value right before the 
update, but one according to a snapshot taken at the beginning of the 
query.  So if you instead did SET age = age + 1, you could see an old 
value of 44 and a new value of 46 (or any similarly weird combination of 
values).



.m


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Run pgindent now?

[Andrew Dunstan]

On 05/18/2015 08:06 PM, Andrew Dunstan wrote:


On 05/18/2015 07:04 PM, Bruce Momjian wrote:

On Mon, May 18, 2015 at 06:53:00PM -0400, Tom Lane wrote:

Robert Haas  writes:
On Mon, May 18, 2015 at 12:10 PM, Bruce Momjian  
wrote:

There was talk last time of pgindent-ing head and all back branches,
because a patch applied to head and back branches was historically 
only
pgindented in head, meaning that any future patches in that area 
could

not be easily backpatched.

Do we want to do this?

I am personally not excited about that.  I would rather leave the
back-branches alone.

It would be awfully nice though if we didn't have to deal with random
cross-branch indenting differences.  I've lost, maybe not years off my
life, but certainly weeks of not-very-pleasant make-work because of 
that.

I'm surprised you've not had the same experience.

If people were good about pgindenting patches meant to be back-patched
*before* they committed, it would not be such an issue, but they're not
very good about that.

I couldn't figure out why we were getting that code drift, but now that
Tom has identified why it happens, it seems good that we fix it.
Would it alleviate your concern any if we eased into this, like say 
only

apply the back-branch pgindent run to 9.5 and later branches? Then at
least I could foresee the end of that particular annoyance.

(BTW, one practical issue is where would we get typedef lists relevant
to the back branches.  I'm not sure if the buildfarm infrastructure is
capable of collecting branch-specific data, or if we'd need to rather
than just using a union of all branches' typedefs.)

Uh, I just happen to commit the typedef list file used for the pgindent
run in src/tools/pgindent/typedefs.list, per branch, so we would just
use the same file.  If typedefs were added in a backbranch (unlikely),
we probably wouldn't want to use them anyway.





The buildfarm animals are perfectly capable of finding typedefs for 
each branch. They haven't been because the default configuration is 
only to collect them for HEAD.


Changing this is easy, especially since I control five of the six 
members currently reporting typedefs successfully, and Tom controls 
the other one.


I've currently set two of them to do run typedefs for all live branches.

The other thing is that the server script that amalgamates them only 
looks at HEAD. That will need to change.


We would probably want an amalgamated list, because there could have 
been symbols on old branches that were deleted in later branches. With 
luck the presence of false positives wouldn't matter. It usually 
doesn't seem to.







OK, if you look at 
 you will be 
able to see the state of things. It's not even remotely pretty, and I am 
going to fix that, but it works.


As you will be able to see, a number of buildfarm members are reporting 
on typedefs on all the live branches. You can get the list for each 
branch by hitting the appropriate link (essentially 
'/cgi-bin/typedefs.pl?branch=$branch'). If you ask for 'ALL' as the 
branch it gives you the amalgamated list over all branches. If you don't 
specify a branch at all, it gives you HEAD (which is buildfarm spelling 
for master), since that's what it did previously. I can change the 
default to ALL if that's what people want.


Tom, if you want  to get dromedary reporting on all branches, just 
remove the "branches => [ 'HEAD' ]," from the config.


Enjoy.

cheers

andrew


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Per row status during INSERT .. ON CONFLICT UPDATE?

[Thom Brown]

On 19 May 2015 at 13:23, Robins Tharakan  wrote:
> Hi,
>
> Is there a way to know which rows were INSERTed and UPDATEd when doing a
> INSERT ... ON CONFLICT UPDATE? Probably via pseudo column indicating INSERT
> / UPDATE ?
>
> The RETURNING clause just allows us to return columns, but am unable to find
> a way to know 'what' happened to a given row.
>
> Any pointers would be helpful.
> Couldn't find anything related in 9.5devel docs either.

I don't think there's anything that tells you directly in the results
whether an INSERT or an UPDATE was performed.  But you could use a
hack which is to return the xmax in the output, and if that's 0, it
INSERTed.  If it's greater than 0, it UPDATEd:

e.g.

# INSERT INTO test (name, age) values ('Jack', 44) ON CONFLICT (name)
DO UPDATE SET age = EXCLUDED.age RETURNING xmax, *;
 xmax | id | name | age
--++--+-
0 | 70 | Jack |  44
(1 row)


# INSERT INTO test (name, age) values ('Jack', 44) ON CONFLICT (name)
DO UPDATE SET age = EXCLUDED.age RETURNING xmax, *;
  xmax   | id | name | age
-++--+-
 1097247 | 70 | Jack |  44
(1 row)



If you want the delta, you'll have to resort to a CTE:

e.g.

# WITH newvals AS (
INSERT INTO test (name, age) VALUES ('James', 45)
   ON CONFLICT (name)
   DO UPDATE SET age = EXCLUDED.age
   RETURNING *)
SELECT n.name, o.age as "old.age", n.age as "new.age"
FROM test o RIGHT JOIN newvals n on o.name = n.name;

 name  | old.age | new.age
---+-+-
 James |  44 |  45
(1 row)


Regards

Thom


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] upper planner path-ification

[Robert Haas]

On Tue, May 19, 2015 at 7:19 AM, Andrew Gierth
 wrote:
>> "Tom" == Tom Lane  writes:
>  Tom> Hm.  That's a hangover from when query_planner also gave back a
>  Tom> Plan (singular) rather than a set of Paths.  I don't see any
>  Tom> fundamental reason why we couldn't generalize it to be a list of
>  Tom> potentially useful output orderings rather than just one.  But I'm
>  Tom> a bit concerned about the ensuing growth in planning time; is it
>  Tom> really all that useful?
>
> The planning time growth is a possible concern, yes. The potential gain
> is eliminating one sort step, in the case when the input has a usable
> sorted path but grouping_planner happens not to ask for it (when there's
> more than just a single rollup, the code currently asks for one of the
> sort orders pretty much arbitrarily since it has no real way to know
> otherwise). Whether that would justify it... I don't know. Maybe that's
> one to save for later to see if there's any feedback from actual use.

I kind of doubt that the growth in planning time would be anything too
unreasonable.  We already consider multiple orderings for ordinary
base relations, so it's not very obvious why consideration multiple
orderings for subqueries would be any worse.  If we can arrange to
throw away useless orderings early, as we do in other cases, then any
extra paths we consider have a reasonable chance of being useful.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Parallel Seq Scan

[Amit Kapila]

On Mon, May 11, 2015 at 3:00 AM, Robert Haas  wrote:
>
>
> I think it might be better to try to solve this problem in a more
> localized way.  Can we arrange for planstate->instrumentation to point
> directory into the DSM, instead of copying the data over later?

Yes, we can do that but I am not sure we can do that for pgBufferUsage
which is a separate information we need to pass back to master backend.
One way could be to change pgBufferUsage to a pointer and then allocate
the memory for same at backend startup time and for parallel workers, it
should point to DSM.  Do you see any simple way to handle it?

Another way could be that master backend waits for parallel workers to
finish before collecting the instrumentation information and buffer usage
stats.  It seems to me that we need this information (stats) after execution
in master backend is over, so I think we can safely assume that it is okay
to finish the execution of parallel workers if they are not already finished
the execution.


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

[HACKERS] Per row status during INSERT .. ON CONFLICT UPDATE?

[Robins Tharakan]

Hi,

Is there a way to know which rows were INSERTed and UPDATEd when doing a
INSERT ... ON CONFLICT UPDATE? Probably via pseudo column indicating INSERT
/ UPDATE ?

The RETURNING clause just allows us to return columns, but am unable to
find a way to know 'what' happened to a given row.

​Any pointers would be helpful.
Couldn't find anything related in 9.5devel docs either.​

--
thanks
​
Robins