Re: [HACKERS] MERGE SQL Statement for PG11

[Geoff Winkless]

On 6 November 2017 at 17:35, Simon Riggs  wrote:
> I read that step 3 in Approach2 is some kind of problem in MVCC
> semantics. My understanding is that SQL Standard allows us to define
> what the semantics of the statement are in relation to concurrency, so
> any semantic issue can be handled by defining it to work the way we
> want. The semantics are:
> a) when a unique index is available we avoid errors by using semantics
> of INSERT .. ON CONFLICT UPDATE.
> b) when a unique index is not available we use other semantics.

I'm obviously being obtuse.

If a unique index is not available, then surely there won't _be_ a
failure? The INSERT (or indeed UPDATE) that results in two similar
records will simply happen, and you will end up with two records the
same. That's OK, based on the semantics of MERGE, no? At the
transaction-start INSERT was the correct thing to do.

Geoff


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

Re: [HACKERS] MERGE SQL Statement for PG11

[Geoff Winkless]

Can I add my 2c worth, as someone without a horse in the race, as it
were, in the hope that telling me how I've got this wrong might
clarify the argument a bit (or at least you can all start shouting at
me rather than each other :) )

The point of merge is to allow you to choose to either INSERT or
UPDATE (or indeed DELETE) records based on existing state, yes? That
state is whatever the state of the system at the start of the
transaction?

If I understand correctly, the only time when this would be
problematic is if you try to insert a record into a table which would
not allow that INSERT because another transaction has performed an
INSERT by the time the COMMIT happens, and where that new record would
have changed the state of the MERGE clause, yes?

Isn't the only reason this would fail if there is a unique constraint
on that table?

Yes, you could end up INSERTing values from the merge when another
transaction has INSERTed another, but (again, unless I've
misunderstood) there's nothing in the spec that says that shouldn't
happen; meanwhile for those tables that do require unique values you
can use the UPSERT mechanism, no?

Geoff


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

Re: [HACKERS] Cursor With_Hold Performance Workarounds/Optimization

[Geoff Winkless]

On 19 October 2017 at 15:06, Leon Winter  wrote:

> The calculations inside the loop are written in some dynamic high-level
> language and cannot easily be translated into SQL.
>

​Can you not simply create a second connection to perform the updates?
​
Geoff

Re: [HACKERS] jsonb problematic operators

[Geoff Winkless]

On 16 December 2016 at 09:35, Craig Ringer  wrote:
> so it would be consistent with that to use ?? as a literal ? in the
> output query.
>
> This is also what PgJDBC does, per
> https://jdbc.postgresql.org/documentation/head/statement.html . So
> it's consistent .

"Me too".

To look at this from the other angle, is there a reason why the jsonb
indexes don't work with the jsonb_ functions but only with the
operators? Is this something that could be changed easily? It seems
like that would workaround this issue without requiring effort or
agreement from the PHP side.

Geoff


-- 
Sent 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 problematic operators

[Geoff Winkless]

On 9 Dec 2016 17:54, "Andres Freund"  wrote:

On 2016-12-09 12:17:32 -0500, Robert Haas wrote:
> As Geoff says, you don't have to use the operators; you could use the
> equivalent functions instead.  Every operator just gets turned into a
> function call internally, so this is always possible.

Well, except that only operators support indexing :(


Really? Seems like an odd design decision.

The only other simple suggestion then would be to use PDO named parameters
instead of positional ones. Much nicer syntax anyway, IMO.

Geoff

Re: [HACKERS] jsonb problematic operators

[Geoff Winkless]

On 9 December 2016 at 11:50, Jordan Gigov  wrote:
> There is this problem with the jsonb operators "? text" "?| text[]"
> and "?& text[]" that the question mark is typically used for prepared
> statement parameters in the most used abstraction APIs in Java and
> PHP.
>
> This really needs an alternative. Something like "HAS text", "HAS
> ANY(text[])" and "HAS ALL(text[])" same as regular array usage. It
> probably should be another word that has less chance of becoming a
> conflict with another operator in future SQL specifications, but
> that's for you to decide.

You mean something like the jsonb_ functions ?

\df jsonb*

> It's not a good idea to expect everyone else to make for workarounds
> for problems you choose to create.

I'd say it's not a good idea to come asking questions of a mailing
list with an attitude like that, but hey, it's nearly Holidays.

Geoff


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

Re: [HACKERS] [PATCH] Refactor "if(strspn(str, ...) == strlen(str)" code

[Geoff Winkless]

On 8 December 2016 at 15:54, Aleksander Alekseev
 wrote:
> Hi.
>
> I noticed that there is a lot of repeating code like this:
>
> ```
> if (strspn(str, " \t\n\r\f") == strlen(str))
> ```
>
> I personally don't find it particularly readable, not mentioning that
> traversing a string twice doesn't look as a good idea (you can check
> using objdump that latest GCC 6.2 doesn't optimize this code).

You could just change it to

if (str[strspn(str, " \t\n\r\f")] == '\0')

to mitigate calling strlen. It's safe to do so because strspn will
only return values from 0 to strlen(str).

Geoff


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

Re: [HACKERS] DISTINCT with btree skip scan

[Geoff Winkless]

On 23 November 2016 at 21:19, Thomas Munro
 wrote:
> Worth pursuing?  Does amskip suck?  Does anyone have better ideas,
> either for how to do the low level skip or the higher level Index Skip
> Scan, or perhaps a completely different way of looking at this?

I have no helpful suggestions with how to achieve it, but can I add a
voice of encouragement: there have been a good few occasions in the
past year (we moved from another db to PG) where the lack of skip
scans has bitten us; in that case it was using MIN() and GROUP BY,
where the grouping column was the first element in the compound index
and the aggregate column was the second: in the Other DB that sort of
query was extremely quick, not so much here.

I was also idly pondering (in one of those moments of conceited
self-delusion where I thought I might actually have enough spare time
to try to work on it myself) whether it would be possible to implement
that sort of skip with two indexes (so MIN(a) GROUP BY b with separate
indexes on (a) and (b) rather than a single index (a,b)); I never got
much further than idle musings though.

Geoff


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

Re: [HACKERS] pnstrdup considered armed and dangerous

[Geoff Winkless]

On 4 October 2016 at 14:12, Geoff Winkless <pgsqlad...@geoff.dj> wrote:
> Well I wouldn't say it's wrong, exactly. It might produce a segfault
> if relationName[NAMEDATALEN] is outside readable memory for the
> process, but otherwise it will behave as defined.

Finger slippage. Of course I meant

... if relationName[NAMEDATALEN-1] is outside...

Geoff


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

Re: [HACKERS] pnstrdup considered armed and dangerous

[Geoff Winkless]

On 3 October 2016 at 22:55, Andres Freund  wrote:
> A colleage of me just wrote innocent looking code like
> char *shardRelationName = pnstrdup(relationName, NAMEDATALEN);
> which is at the moment wrong if relationName isn't preallocated to
> NAMEDATALEN size.
[snip]
> isn't that a somewhat weird behaviour / implementation? Not really like
> strndup(), which one might believe to be analoguous...

Well I wouldn't say it's wrong, exactly. It might produce a segfault
if relationName[NAMEDATALEN] is outside readable memory for the
process, but otherwise it will behave as defined.

Geoff


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

Re: [HACKERS] Hash Indexes

[Geoff Winkless]

On 21 September 2016 at 13:29, Robert Haas  wrote:
> I'd be curious what benefits people expect to get.

An edge case I came across the other day was a unique index on a large
string: postgresql popped up and told me that I couldn't insert a
value into the field because the BTREE-index-based constraint wouldn't
support the size of string, and that I should use a HASH index
instead. Which, of course, I can't, because it's fairly clearly
deprecated in the documentation...


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

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

[Geoff Winkless]

On 30 July 2016 at 13:42, Tomas Vondra  wrote:
> I'd argue that if you mess with catalogs directly, you're on your own.

Interesting. What would you suggest people use instead?

Geoff


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

Re: [HACKERS] Implementing full UTF-8 support (aka supporting 0x00)

[Geoff Winkless]

On 3 August 2016 at 20:36, Álvaro Hernández Tortosa  wrote:
> Isn't the correct syntax something like:
>
> select E'\uc080', U&'\c080';
>
> ?
>
> It is a single character, 16 bit unicode sequence (see
> https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html).

No, what you've done there is created the three-byte utf8 sequence \xec8280

# select U&'\c080'::bytea;
  bytea
--
 \xec8280

It's not a UCS2 c080, it's utf8 c080.

Geoff


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

Re: [HACKERS] Implementing full UTF-8 support (aka supporting 0x00)

[Geoff Winkless]

On 3 August 2016 at 20:13, Álvaro Hernández Tortosa  wrote:
> Yet they are accepted by Postgres
> (like if Postgres would support Modified UTF-8 intentionally). The caracter
> in psql does not render as a nul but as this symbol: "삀".

Not accepted as valid utf8:

# select E'\xc0\x80';
ERROR:  invalid byte sequence for encoding "UTF8": 0xc0 0x80

You would need a "modified utf8" encoding, I think.

Geoff


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

Re: [HACKERS] Implementing full UTF-8 support (aka supporting 0x00)

[Geoff Winkless]

On 3 August 2016 at 15:54, Álvaro Hernández Tortosa  wrote:
> Given that 0x00 is a perfectly legal UTF-8 character, I conclude we're
> strictly non-compliant.

It's perhaps worth mentioning that 0x00 is valid ASCII too, and
PostgreSQL has never stored that either.

If you want to start quoting standards, there is in fact specific
mention in the ANSI spec of null terminators in passing strings to
host languages, so if postgresql stored NULs in that way we would end
up with parameters that we couldn't pass to UDFs in a
standards-compliant way.

Geoff


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

Re: [HACKERS] Why we lost Uber as a user

[Geoff Winkless]

On 3 August 2016 at 15:04, Kevin Grittner  wrote:
> My initial experience with PostgreSQL would have been entirely
> different had I not found the community lists and benefited from
> the assistance and collective wisdom found on them.

The top non-sponsored link on google for "postgres support" takes you
straight to a page with a link to the mailing lists. I'm not sure that
not being able to find them was a problem.

I can well imagine that uber wouldn't have wanted to publicise their
problems (and so wouldn't have used a mailing list anyway); obviously
I've no way of knowing if they contacted any of the support companies
in the professional services page - I assume that professional
courtesy (and/or NDAs!) would preclude anyone from posting such here
anyway.

The problem with the professional services page is that the list of
companies is very dry, but it might be difficult to improve: as a
community it might be considered unreasonable to promote one over the
other; however if I had to go searching for professional support (and
hadn't seen the level of interaction that some of those companies'
employees provide on the mailing lists) I would have no clear idea
where to start.

Perhaps listing those companies that provide employment for some of
the core developers at the top (and explaining so) might be
acceptable? (or maybe not just core? you get the idea though). Maybe a
separate section for support companies versus hosts?

Geoff


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

Re: [HACKERS] Why we lost Uber as a user

[Geoff Winkless]

On 2 August 2016 at 08:11, Alfred Perlstein <alf...@freebsd.org> wrote:
> On 7/2/16 4:39 AM, Geoff Winkless wrote:
> > I maintain that this is a nonsense argument. Especially since (as you 
> > pointed out and as I missed first time around) the bug actually occurred at 
> > different records on different slaves, so he invalidates his own point.

> Seriously?

No, I make a habit of spouting off random arguments to a list full of
people whose opinions I massively respect purely for kicks. What do
you think?

> There's a valid point here, you're sending over commands at the block level, 
> effectively "write to disk at this location" versus "update this record based 
> on PK", obviously this has some drawbacks that are reason for concern.

Writing values directly into file offsets is only problematic if
something else has failed that has caused the file to be an inexact
copy. If a different bug occurred that caused the primary key to be
corrupted on the slave (or indeed the master), PK-based updates would
exhibit similar propagation errors.

To reiterate my point, uber's described problem came about because of
a bug. Every software has bugs at some point in its life, to pretend
otherwise is simply naive. I'm not trying to excuse the bug, or to
belittle the impact that such a bug has on data integrity or on uber
or indeed on the reputation of PostgreSQL. While I'm prepared to
accept (because I have a job that requires I spend time on things
other than digging through obscure reddits and mailing lists to
understand more fully the exact cause) that in _this particular
instance_ the bug was propagated because of the replication mechanism
(although I'm still dubious about that, as per my comment above), that
does _not_ preclude other bugs propagating in a statement-based
replication. That's what I said is a nonsense argument, and no-one has
yet explained in what way that's incorrect.

Geoff


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

Re: [HACKERS] Why we lost Uber as a user

[Geoff Winkless]

On 28 Jul 2016 12:19, "Vitaly Burovoy" <vitaly.buro...@gmail.com> wrote:
>
> On 7/28/16, Geoff Winkless <pgsqlad...@geoff.dj> wrote:
> > On 27 July 2016 at 17:04, Bruce Momjian <br...@momjian.us> wrote:
> >
> >> Well, their big complaint about binary replication is that a bug can
> >> spread from a master to all slaves, which doesn't happen with statement
> >> level replication.
> >
> > ​
> > I'm not sure that that makes sense to me. If there's a database bug that
> > occurs when you run a statement on the master, it seems there's a decent
> > chance that that same bug is going to occur when you run the same
statement
> > on the slave.
> >
> > Obviously it depends on the type of bug and how identical the slave is,
but
> > statement-level replication certainly doesn't preclude such a bug from
> > propagating.
> >
> > ​Geoff
>
> Please, read the article first! The bug is about wrong visibility of
> tuples after applying WAL at slaves.
> For example, you can see two different records selecting from a table
> by a primary key (moreover, their PKs are the same, but other columns
> differ).

I read the article. It affected slaves as well as the master.

I quote:
"because of the way replication works, this issue has the potential to
spread into all of the databases in a replication hierarchy"

I maintain that this is a nonsense argument. Especially since (as you
pointed out and as I missed first time around) the bug actually occurred at
different records on different slaves, so he invalidates his own point.

Geoff

Re: [HACKERS] Why we lost Uber as a user

[Geoff Winkless]

On 27 July 2016 at 17:04, Bruce Momjian  wrote:

> Well, their big complaint about binary replication is that a bug can
> spread from a master to all slaves, which doesn't happen with statement
> level replication.


​
​I'm not sure that that makes sense to me. If there's a database bug that
occurs when you run a statement on the master, it seems there's a decent
chance that that same bug is going to occur when you run the same statement
on the slave.

Obviously it depends on the type of bug and how identical the slave is, but
statement-level replication certainly doesn't preclude such a bug from
propagating.​


​Geoff​

Re: [HACKERS] unexpected psql "feature"

[Geoff Winkless]

On 14 July 2016 at 00:12, Tom Lane  wrote:

> I wonder
> whether promoting \; to a recognized and documented behavior would
> allow us to get away with converting -c strings to normal parsing
> behavior, as was discussed and then rejected on compatibility grounds
> not too long ago.  People who need to keep the old behavior could do so
> by putting in backslashes.
>

Just so I'm clear: you're suggesting that
because people can modify their code to achieve the old behaviour it's no
longer breaking compatibility?

​That's an odd definition, IMO.

Geoff

Re: [HACKERS] Rename max_parallel_degree?

[Geoff Winkless]

On 25 April 2016 at 03:44, Robert Haas  wrote:
> On Sun, Apr 24, 2016 at 2:23 PM, Tom Lane  wrote:
>> FWIW, I agree with Bruce that using "degree" here is a poor choice.
>> It's an unnecessary dependence on technical terminology that many people
>> will not be familiar with.
>
> And many others will.  Some made-up term that is entirely
> PostgreSQL-specific is not going to be better.

Just to add my 2c, "degree" implies some sort of graded scale. So
setting it to (say) 10 would be "maximum", setting to 0 would be
"none" and setting it to anything in between would be relative to the
maximum.

eg in Vol26 "Encyclopedia of Computer Science and Technology" (the
first compsci reference that appeared for a google search) there are
three levels of granularity of degrees of parallelism.

https://books.google.co.uk/books?id=z4KECsT59NwC=PA41=PA41=degree+of+parallelism

Frankly it seems that the SQL crowd stole the computer science term
and applied it incorrectly.

Having a configuration option "_workers" makes much more sense to me.
It absolutely describes what it does without needing to refer to a
manual, and it removes ambiguity.

Geoff


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

Re: [HACKERS] Why the "UPDATE tab SET tab.col" is invalid?

[Geoff Winkless]

On 7 April 2016 at 16:45, Tom Lane  wrote:
> Don't know which version of the SQL spec you're looking at,

It was the draft 95 version, cos (being text file) it's easiest to
read :). I'll learn my lesson next time and expand the 2008 one.

> but SQL:2008 has
>
>  ::=
>   UPDATE  [ [ AS ]  ]
>   SET 
>   [ WHERE  ]
[snip]
> The reason why SQL doesn't allow an optional correlation name, and
> probably never will, is the same as the reason why we don't, and probably
> never will: it introduces an ambiguity as to whether you meant a dotted
> set-clause target name to be a reference to a field of a composite column
> or just a noise-word reference to the table's correlation name.

I stand (comprehensively :) ) corrected, thanks for clarifying.

Geoff


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

Re: [HACKERS] Why the "UPDATE tab SET tab.col" is invalid?

[Geoff Winkless]

On 7 April 2016 at 15:51, I wrote:
>  ::=
>   UPDATE 
> SET 
>   WHERE CURRENT OF 

I grabbed the wrong section of the doc; I should of course have pasted
the searched version:

 ::=
  UPDATE 
SET 
[ WHERE  ]

My point is still the same though :)

Geoff


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

Re: [HACKERS] Why the "UPDATE tab SET tab.col" is invalid?

[Geoff Winkless]

On 7 April 2016 at 14:48, Merlin Moncure  wrote:
>
> On Thu, Apr 7, 2016 at 4:39 AM, postgres_sure  wrote:
> > Hi,
> >
> > I found "Do not include the table's name in the specification of a target
> > column
> >  — for example, UPDATE tab SET tab.col = 1 is invalid." in
> > the documentation.
> >
> > Some people usually like to update table by alias. They want to add this
> > feature.
>
> Is this syntax described in the SQL standard?

Given that the SQL standard has no concept of UPDATEs against a query
with multiple tables, and that's where this could bring the most
clarity, I'm not sure that it's reasonable to object on that basis.

As far as I can see the SQL standard doesn't let you alias tables in
an UPDATE (for the same reason, I imagine) but we allow _that_ even in
a single-table UPDATE.

 ::=
  UPDATE 
SET 
  WHERE CURRENT OF 


Geoff


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

Re: [HACKERS] Removing Functionally Dependent GROUP BY Columns

[Geoff Winkless]

On 14 January 2016 at 13:16, Julien Rouhaud  wrote:
> You're absolutely right, but in this case the comment is more like a
> reminder of a bigger comment few lines before that wasn't quoted in my mail

Fair enough, although I have two niggles with that:

a) the second comment could become physically separated from the first
by later additions of extra code, or by refactoring;
b) if you don't need the comment because the explanation for it is
local anyway and the comment tells you nothing that the code doesn't,
why have it at all?

> so I assume it's ok to keep it this way.

Of course it's ok to do whatever you decide is best: as I said
previously, I fully appreciate that I have no ownership over any of
the code.

Geoff


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

Re: [HACKERS] Removing Functionally Dependent GROUP BY Columns

[Geoff Winkless]

On 14 January 2016 at 11:19, Julien Rouhaud  wrote:
> +   /* don't try anything unless there's two Vars */
> +   if (varlist == NULL || list_length(varlist) < 2)
> +   continue;
>
> To be perfectly correct, the comment should say "at least two Vars".

Apologies for butting in and I appreciate I don't have any ownership
over this codebase or right to suggest any changes, but this just
caught my eye before I could hit "delete".

My mantra tends to be "why, not what" for inline comments; in this
case you can get the same information from the next line of code as
you get from the comment.

Perhaps something like

/* it's clearly impossible to remove duplicates if there are fewer
than two GROUPBY columns */

might be more helpful?

(also sorry if I've misunderstood what it _actually_ does, I just made
an assumption based on reading this thread!)

Geoff


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

Re: [HACKERS] Remaining 9.5 open items

[Geoff Winkless]

On 4 December 2015 at 15:50, Simon Riggs  wrote:

> Do we think they ever launched a Saturn V that didn't have some marginal
> flashing lights somewhere?
>

​Almost certainly. They had triple-redundant systems that were certified
for correctness. You don't knowingly send rockets into space with dubious
control systems.

I accept there are open items. I'd like a way to indicate to people they
> can start using it with a safety, apart from the listed caveats.
>

Just to add my .2c worth...

​T​
hat's what betas are for.

There's an implied open-source contract​

​that anyone who wants to use a feature in the next version will invest a
little of their time ​making sure that the feature works for them in the
beta before it gets released.

The developer side of that contract is that you fix the bugs people found
in the beta before release, because otherwise next time they won't bother.

And if you start pushing out full releases that you *know *introduce bugs
that weren't in the previous release, "with caveats" or not, you end up in
a situation where people won't upgrade until the second or third point
release.

Geoff

Re: [HACKERS] About BoringSSL, an OpenSSL fork

[Geoff Winkless]

On 26 October 2015 at 00:59, Michael Paquier 
wrote:

> https://boringssl.googlesource.com/boringssl/+/HEAD/PORTING.md
> Looking at the porting section many routines have changed compared to
> OpenSSL. I can't imagine this fork to become a complete replacement of
> OpenSSL, but it may be worth considering an integration in Postgres
> code depending on the features it will have (Curve25519,
> Ed25519 mentioned). Also since 9.4 the SSL code paths have been
> rearranged to allow more implementations to be done with other SSL
> libraries.
>

​​
​Quote:

​
Although BoringSSL is an open source project, it is not intended for
general use, as OpenSSL is. We don’t recommend that third parties depend
upon it. Doing so is likely to be frustrating because there are no
guarantees of API or ABI stability.​

​Sounds like a subscription to a world of pain.​

​Geoff​

Re: [HACKERS] [COMMITTERS] pgsql: Use gender-neutral language in documentation

[Geoff Winkless]

On 24 September 2015 at 11:33, Gavin Flower 
wrote:

> An example from a book on PostgreSQL server programming that I'm working
> through (Note that it is obviously awkward to write with gender pronouns
> when gender is irrelevant, note the "he she" in one place and "he/she" in
> another!):
>
>"If the user is a superuser, then they have permission to see the
>full query. If the user is a regular user, they will only see the
>full query for their queries."
>

Can I quietly suggest "​Users with superuser pemissions can always see the
full query​, while regular users will only see the full query for their own
queries."?

Geoff

Re: [HACKERS] [COMMITTERS] pgsql: Use gender-neutral language in documentation

[Geoff Winkless]

On 22 September 2015 at 21:22, David Steele  wrote:

> I think conversations like this are a part of why we have trouble
> attracting new contributors (of any gender) to the community.
>

​It's very clear that my use of the word (which I shan't make the mistake
of repeating!) is not acceptable to many on this list. I ​apologise
unreservedly to anyone I have unintentionally offended by the use of this
word, I shall not do so again; I would only say in my defence that I was
not intending to characterise all feminists in that way, rather to
distinguish the sort of feminists to whom the fact that a text refers to
"man"​ or "he" is offensive from the sort of feminists who I admire and
respect - those who fight for real equality, who use intelligent argument
and who have affected real change for good in my lifetime.

I can clearly see that my use of language has, ironically enough,
invalidated my argument somewhat. I have said my last on the subject​.

Geoff

Re: [HACKERS] [COMMITTERS] pgsql: Use gender-neutral language in documentation

[Geoff Winkless]

On 22 September 2015 at 09:28, Albe Laurenz  wrote:

> Peter Geoghegan wrote:
> > On Mon, Sep 21, 2015 at 9:32 PM, Erik Rijkers  wrote:
> >> I think this compulsive 'he'-avoiding is making the text worse.
> >>
> >>
> >> -  environment variable); any user can make such a change for his
> session.
> >> +  environment variable); any user can make such a change for their
> session.
> >
> > -1. It seems fine to me.
>
> (Disclaimer: I am not a native speaker.)
>
> Using the pronoun of the third person plural as a replacement for "his or
> her"
> has become widely used, at least in the U.S., and the OED condones that
> use:
> http://www.oxforddictionaries.com/definition/english/they
>
>
Without wanting to get into a grammar war, ​I'm not so sure I agree that it
"condones" it. Dictionaries reflect the current state of usage, they don't
act as arbiters of correctness. The abuse of "literally" as an emphasiser
(which usage is now listed in the OED) is a prime example.

As an Englishman ​I would prefer "his or her" over "their". Perhaps our
American cousins might disagree though.

WRT the second, it probably doesn't help that "might not be the same as the
database user that is to be connect as" is incorrect anyway - it should
perhaps be "that is to be connect*ed *as" (although I still find the
construction clumsy).

Geoff

Re: [HACKERS] [COMMITTERS] pgsql: Use gender-neutral language in documentation

[Geoff Winkless]

On 22 September 2015 at 14:09, Andrew Dunstan  wrote:

> You are fighting a losing battle. Think of they/them/their/theirs as being
> indefinitely gendered third person singular pronouns, as well as being
> third person plural pronouns. Yes it's a relatively new usage, but I don't
> think its at all unreasonable (speaking as someone who has been known to
> dislike some new usages and neologisms). It's not at all sloppy. On the
> contrary, it's quite deliberate. It's just not quite traditional.
>

​​It _is_ sloppy. It says "I can't be bothered to write a sentence that's
grammatically correct".
​

> You need to get over that.
>

I don't need to get over anything. If someone sends me a document that uses
"their" in a singular usage, I will think that person is lazy. That will
continue to be the case, whether people tell me that it's accepted usage or
not.

In much the same way, I know that I can safely discount the opinion of
anyone who uses "literally" to mean anything other than "literally"
 (
​similarly anyone who uses
"like" as a quotative)
​, even though both of those things are now in fairly common usage.​
​


> Your proposed style would make writing docs a lot harder,
>

I don't buy that at all. It takes a couple of seconds, if that, to come up
with something.


> forcing us to avoid use of the singular in cases where it is quite
> natural.
>

​Better than using the plural in the singular case.
​

> I'm strongly opposed to such a style rule.
>

​Meh. I don't really care how it's written, certainly not enough to make a
stand about it. I'd rather you guys concentrate on writing the brilliant
software than wasting time on stuff like this. I only replied because the
conversation popped up in my inbox and it seemed to be something on which
opinions were requested.

Geoff

Re: [HACKERS] [COMMITTERS] pgsql: Use gender-neutral language in documentation

[Geoff Winkless]

On 22 September 2015 at 10:52, Gavin Flower <gavinflo...@archidevsys.co.nz>
wrote:

> On 22/09/15 21:33, Geoff Winkless wrote:
>
>> ​​
>> Without wanting to get into a grammar war, ​I'm not so sure I agree that
>> it "condones" it. Dictionaries reflect the current state of usage, they
>> don't act as arbiters of correctness. The abuse of "literally" as an
>> emphasiser (which usage is now listed in the OED) is a prime example.
>>
>> I would prefer "his or her" over "their". Perhaps our American cousins
>> might disagree though.
>>
>> I prefer "their" rather than "his or her", it is less clumsy & there is
> no point in specifying gender unless it is relevant!
>

I agree in that I prefer "their" in informal speech; however in a formal
document I would find it sloppy.​ I don't think "his or her" is inherently
clumsy; m
aybe I'm just showing my age.​

Besides, some people are neither, or their biological gender is ambiguous -
> so a few people fit into neither the male nor the female category
> (depending on precise definitions, about 0.5%)!
>

My understanding is that most intersex (and certainly all trans) people
would identify with one or the other, and even those who don't select
exclusively identify with a mix of both (and would therefore still be
covered by "his or her", no?) although I don't pretend to be an expert.

Perhaps it would be easier to avoid the controversy by actually rewording
into the plural, where possible?

So

"any user can make such a change for his session."

becomes

"Users can make such a change for their individual sessions"

or similar?

Geoff

Re: [HACKERS] [COMMITTERS] pgsql: Use gender-neutral language in documentation

[Geoff Winkless]

Oh, good! We're actually going to have this argument? Even though I said I
don't care what you do?

On 22 September 2015 at 15:11, Andrew Dunstan <and...@dunslane.net> wrote:

> On 09/22/2015 09:25 AM, Geoff Winkless wrote:
>
>>  If someone sends me a document that uses "their" in a singular usage, I
>> will think that person is lazy. That will continue to be the case, whether
>> people tell me that it's accepted usage or not.
>
>
> You can think that if you like,


​Thanks!​ That's a great relief to me, as I'm sure you can imagine.


> but it's not even remotely true.


​You just stated that the reason you don't want to use the plural form I
suggested is because it's too hard/time-consuming. That does suggest you
accept that it's a valid solution but you're too lazy to use it.​


> It's a deliberate choice to use a new, perfectly reasonable and now widely
> accepted style of which you disapprove, but it's not lazy.
>

​That's your opinion; my opinion remains otherwise. It's not "perfectly
reasonable" to abuse the plural because some 1960s feminazis either
misunderstood or didn't like the fact that (because of history) in English
the gender-neutral singular happens to also be the male singular.

Happily for me, I can continue to write documents in a grammatically
correct way, and no-one will read them and think I'm a grammar-nazi (or
obstinate, or old-fashioned or whatever) because unless they're
specifically looking for it no-one will notice that I'm avoiding the
contentious usage altogether. On the other hand, there _will_ be a (perhaps
significant) proportion of people who read your documents and think that
you're incapable of writing a grammatically correct sentence.

Geoff

Re: [HACKERS] Precedence of standard comparison operators

[Geoff Winkless]

On 10 August 2015 at 16:31, Tom Lane t...@sss.pgh.pa.us wrote:

 Pavel Stehule pavel.steh...@gmail.com writes:
  On Sun, Aug 09, 2015 at 08:06:11PM -0400, Tom Lane wrote:
  So yeah, I do think that getting a syntax error if you don't use
  parentheses is the preferable behavior here.

  If we raise a syntax error, then there should be very informative
 message,

 Yeah, it would sure be nice to throw something better than syntax error.
 But I've looked at bison's facilities for that, and they're pretty bad.
 I'm not sure there's much we can do short of moving to some other parser
 generator tool, which would be a Large Undertaking ... and I don't know
 of any arguably-better tool anyway.


I would say that anyone who's tricksy enough to be using boolean logic in
the way you describe would be expected to have enough nouse about them to
either a) know what the precedences are or b) know that their lack of
knowledge means they should sprinkle their code with
​brackets
.​

Returning a syntax error for something that isn't actually an error in
syntax is a poor showing.
Are we to start
​expecting
syntax errors when people use comparison operators on
​NULLable​

​​
columns
​
without a COALESCE
​
because the comparison might do something they don't expect
​ if they haven't tested their code with NULL values
?

IMO using a = b  c as described is unnecessarily* horrid, *whichever* way
you mean it, and will only produce the sort of unreadability that generates
errors in the long run anyway (even if you understand it, chances are the
next poor sap reading your code won't) and deserves code that breaks,
especially if you're the sort of person who uses it without fully
understanding it.

(*Unnecessarily because there are clearer constructs - CASE springs to mind
- that do the same thing without the associated unreadability and/or
ambiguity)

Geoff

Re: [HACKERS] ON CONFLICT DO UPDATE using EXCLUDED.column gives an error about mismatched types

[Geoff Winkless]

On 4 August 2015 at 09:30, Amit Langote langote_amit...@lab.ntt.co.jp
wrote:

 On 2015-08-04 AM 02:57, Peter Geoghegan wrote:
  On Mon, Aug 3, 2015 at 8:53 AM, Geoff Winkless pgsqlad...@geoff.dj
 wrote:
  If I create a copy of the table using
 
  CREATE mytab (LIKE brokentab INCLUDING ALL);
  INSERT INTO mytab SELECT * FROM brokentab;
 
  Also, did you drop any columns from the original brokentab table
  where the bug can be reproduced?
 

 This seem to be the case. I could reproduce the reported problem:


​Although it seems Amit has defined the problem better than I could, so
this is a bit late to the party (!), yes, the table had been ALTERed after
it was created (looking back through the history, that modification
included at least one DROP COLUMN).

Thanks

Geoff​

[HACKERS] ON CONFLICT DO UPDATE using EXCLUDED.column gives an error about mismatched types

[Geoff Winkless]

Hi

We've come across a weirdness with ON CONFLICT, where UPSERTing a smallint
value produces an error:

db=# INSERT INTO brokentab(id, k1,k2,k3,k4,k5,k6,k7, smallval) VALUES
(5,0,0,0,1,0,1,0, 0) ON CONFLICT (id, k1,k2,k3,k4,k5,k6,k7) DO UPDATE SET
smallval=EXCLUDED.smallval;
ERROR:  attribute 29 has wrong type
DETAIL:  Table has type integer, but query expects smallint.

If you change the SET to smallval=0 the problem goes away, although using
SET smallval=CAST(EXCLUDED.smallval AS smallint) - or indeed AS int -
doesn't help at all.

If I create a copy of the table using

CREATE mytab (LIKE brokentab INCLUDING ALL);
INSERT INTO mytab SELECT * FROM brokentab;

the new table does not exhibit the same problem (so I'm assuming it's not
easily reproducible and giving you a creation script isn't going to help).

VACUUM FULL on the table makes no difference.

Is there anything you guys can suggest that I can do to help narrow down
the problem?

Linux Centos 6.5, kernel 2.6.32-431.el6.i686, pgsql alpha1, built from
source using gcc 4.4.7.

Thanks

Geoff

[HACKERS] Re: ON CONFLICT DO UPDATE using EXCLUDED.column gives an error about mismatched types

[Geoff Winkless]

On 3 August 2015 at 16:53, Geoff Winkless pgsqlad...@geoff.dj wrote:

 If I create a copy of the table using

 CREATE mytab (LIKE brokentab INCLUDING ALL);

 ​Of course I meant CREATE TABLE here... finger slippage :)​

[HACKERS] segfault in 9.5alpha - plpgsql function, implicit cast and IMMUTABLE cast function

[Geoff Winkless]

Hi all

While doing some testing of 9.5a one of my colleagues (not on list) found a
reproducible server segfault.

We've broken it down to a minimal script to reproduce below.

Reproduced on both machines on which we've installed 9.5 so far (both built
from source since we don't have any RHEL7 machines in development):

RHEL5.3 (Linux 2.6.18-128.el5 i386), gcc version 4.6.4
CentOS 6.5 (Linux 2.6.32-431.el6.i686), gcc version 4.4.7-4

Script for psql:

 cut ===

CREATE OR REPLACE FUNCTION to_date(integer) RETURNS date LANGUAGE sql
IMMUTABLE AS $$

SELECT $1::text::date

$$;


DROP CAST IF EXISTS (integer AS date);

CREATE CAST (integer AS date) WITH FUNCTION to_date(integer) AS IMPLICIT;



CREATE OR REPLACE FUNCTION newcrash(INTEGER) returns DATE LANGUAGE plpgsql
AS $$ BEGIN

RETURN $1;

END$$;


SELECT newcrash(20150202);

SELECT newcrash(20150203);


 cut ===



It doesn't crash the first time, but does consistently crash the second.
Given that if I remove IMMUTABLE from the function definition it doesn't
fail, it implies that there's a problem with the mechanism used to cache
function results - although the fact that the second function call doesn't
have to be the same value does suggest it's a problem with the code that
*searches* that result cache, rather than the section that retrieves it.


I tried cutting out the implicit CAST altogether and doing

RETURN to_date($1);


but this doesn't fail, which implies also that it's something related to
the implicit cast.


If I DECLARE a local DATE variable and SELECT INTO that (rather than just
using RETURN $1), it crashes at that point too.

Hope someone can get something useful from the above. Any questions, please
ask.


Geoff

Re: [HACKERS] [CORE] Restore-reliability mode

[Geoff Winkless]

Among several others, On 8 June 2015 at 13:59, David Gould da...@sonic.net
wrote:

 I think Alphas are valuable and useful and even more so if they have
 release
 notes. For example, some of my clients are capable of fetching sources and
 building from scratch and filing bug reports and are often interested in
 particular new features. They even have staging infrastructure that could
 test new postgres releases with real applications. But they don't do it.
 They also don't follow -hackers, they don't track git, and they don't have
 any easy way to tell if if the new feature they are interested in is
 actually complete and ready to test at any particular time. A lot of
 features are developed in multiple commits over a period of time and they
 see no point in testing until at least most of the feature is complete and
 expected to work. But it is not obvious from outside when that happens for
 any given feature. For my clients the value of Alpha releases would
 mainly be the release notes, or some other mark in the sand that says As
 of
 Alpha-3 feature X is included and expected to mostly work.


Wow! I never knew there were all these people out there who would be
rushing to help test if only the PG developers released alpha versions.
It's funny how they never used to do it when those alphas were done.

I say again: in my experience you don't get useful test reports from people
who aren't able or prepared to compile software; what you do get is lots of
unrelated and/or unhelpful noise in the mailing list. That may be harsh or
unfair or whatever, it's just my experience.

I guess the only thing we can do is see who's right. I'm simply trying to
point out that it's not the zero-cost exercise that everyone appears to
think that it is.

Geoff

Re: [HACKERS] [CORE] Restore-reliability mode

[Geoff Winkless]

On 8 June 2015 at 16:01, Robert Haas robertmh...@gmail.com wrote:

 On Mon, Jun 8, 2015 at 9:21 AM, Geoff Winkless pgsqlad...@geoff.dj
 wrote:
  Wow! I never knew there were all these people out there who would be
 rushing
  to help test if only the PG developers released alpha versions. It's
 funny
  how they never used to do it when those alphas were done.

 That's probably overplaying your hand a little bit (and it sounds a
 bit catty, too).


​I agree. The responses I had written yesterday but didn't send were much
worse.

Mainly because I think it's quite an attitude to take that open-source
developers should put extra time into building RPMs of development versions
rather than testers waiting 5 minutes while their machines compile.
Ohmygosh, you have to rpm install a bunch of -devel stuff? What a massive
hardship.

​
​
On 8 June 2015 at 16:06, Joshua D. Drake j...@commandprompt.com wrote:

 ​​
 The type of responses you are providing on this thread are not warranted.


I got people appearing completely insulted at my remarks and telling me
that if only they could run the alpha they would provide testing, so I
pointed out how easy it is to install the nightly from source and then they
tell me that actually compiling is far too difficult and complicated, and
that there are loads of clients who would run these nightlies if they had
RPMS...

If I truly believed that such an RPM would produce useful testing, I would
spend some of my own time building a setup to produce those RPMs myself and
post here publicising them, at which point we would have a huge number of
useful and productive test reports. Any one of the people telling me that
I'm wrong could easily do the same, but so far none has.

I'm not harping on because I want to make people feel bad, I'm harping on
because I don't want to see beta (and final) releases pushed back further
because of a bad compromise, and I believe that that will happen. I
apologise that I've clearly upset some people but they all have a very easy
route to prove me wrong, and I'll be happy to admit my error.
​
​Geoff​

Re: [HACKERS] [CORE] Restore-reliability mode

[Geoff Winkless]

On 8 June 2015 at 17:03, Claudio Freire klaussfre...@gmail.com wrote:

 It's not about the 5 minutes of compile time, it's about the signalling.

 Just *when* is git ready for testing? You don't know from the outside.

 I do lurk here a lot and still am unsure quite often.

 Even simply releasing an alpha *tarball* would be useful enough. What
 is needed is the signal to test, rather than a fully-built package.


​I can see that, and can absolutely get behind the idea of a nightly being
flagged as an alpha, since it should involve next to no developer time.

I may be overestimating the amount of time that goes towards producing a
release; the fact that the full-on alpha releases were stopped did imply to
me that it's not insignificant.

Geoff​

Re: [HACKERS] [CORE] Restore-reliability mode

[Geoff Winkless]

 ​
​
To play devil's advocate for a moment, is there anyone who would genuinely
be prepared to download and install an alpha release who would not already
have downloaded one of the nightlies? I only ask because I assume that
​releasing ​
an alpha is not zero-developer-cost and I don't believe
​that​
 there's a large ​
​
number of people who *would *be happy to install something that's described
as being buggy and subject to change but are put off by having to type
configure and make.
​

Further, it seems to me that the number of people who ​won't roll their own
who are useful as bug-finders is even smaller.

I get the feeling that the argument appears to be Bruce doesn't want to
release a beta, Simon wants to release something. Let's release an alpha
because it's sort-of half way in between as a consensus compromise (I'm
not deliberately picking on specific people, I'm aware you're not the only
two involved and arguing for either side, but you do seem to be fairly
polar opposite sides of the argument :) ); I don't really believe that
releasing an alpha moves anything further forward from a testing point of
view, and I'm fairly sure that it will have just as dele
terious effect on bugfixing as would a beta
​, with the added disadvantage of the extra developer cost.

​Geoff​

Re: [HACKERS] [CORE] Restore-reliability mode

[Geoff Winkless]

On 6 June 2015 at 13:41, Sehrope Sarkuni sehr...@jackdb.com wrote:

 On Sat, Jun 6, 2015 at 6:47 AM, Geoff Winkless pgsqlad...@geoff.dj
 wrote:
  To play devil's advocate for a moment, is there anyone who would
 genuinely be prepared to download
  and install an alpha release who would not already have downloaded one
 of the nightlies? I only ask
  because I assume that  releasing an alpha is not zero-developer-cost and
 I don't believe  that
  there's a large number of people who would be happy to install something
 that's described as being
  buggy and subject to change but are put off by having to type
 configure and make.

 I fit into that category and I would guess there would be others as
 well. Having system packages available via an apt-get install ...
 lowers the bar significantly to try things out.


​But it also lowers the bar to the extent that you get the people who won't
read the todo list and end up complaining about the things that everyone
already knows about​.


 It's much easier to work into dev/test setups if there are system
 packages as it's just a config change to an existing script. Building
 from source would require a whole new workflow that I don't have time
 to incorporate.


​Really? You genuinely don't have time to paste, say:

mkdir -p ~/src/pgdevel
cd ~/src/pgdevel
wget https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2
tar xjf postgresql-snapshot.tar.bz2
​mkdir bld
​
cd bld
../postgresql-9.5devel/configure $(pg_config --configure | sed -e
's/\(pg\|postgresql[-\/]\)\(doc-\)\?9\.[0-9]*\(dev\)\?/\1\29.5dev/g')
make wor
​ld​
​make check
make world-install
​​

​and yet you think you have enough time to provide more than a looks like
it's working report to the developers?​

(NB the sed for the pg_config line will probably need work, it looks like
it should work on the two types of system I have here but I have to admit I
changed the config line manually when I built it)



  Further, it seems to me that the number of people who won't roll their
 own who are useful as bug-finders is even smaller.

 That's probably true but they definitely won't find any bugs if they
 don't test at all.

 If it's possible to have automated packaging, even for just a subset
 of platforms, I think that'd be useful.


Well yes, automated packaging of the nightly build, that doesn't involve
the developers having to stop what they're doing to write official alpha
release docs or any of the other stuff that goes along with doing a
release, would be zero-impact on development (assuming the developers
didn't have to build or maintain the auto-packager) and therefore any
return (however small) would make it worthwhile.

Fancy building (and maintaining) the auto-packaging system, and managing a
mailing list for its users?

Geoff

Re: [HACKERS] Restore-reliability mode

[Geoff Winkless]

On 3 June 2015 at 14:50, Noah Misch n...@leadboat.com wrote:

 I
 ​ ​
 would define the subject matter as bug fixes, testing and review, not
 restructuring, testing and review.  Different code structures are
 clearest
 to different hackers.  Restructuring, on average, adds bugs even more
 quickly
 than feature development adds them.


​+1 to this. Rewriting or restructuring code because you don't trust it
(even though you have no reported real-world bugs)​ is a terrible idea.

Stopping all feature development to do it is even worse.

I know you're not talking about rewriting, but I think
http://www.joelonsoftware.com/articles/fog69.html is always worth a
re-read, if only because it's funny :)

I would always 100% support a decision to push back new releases because of
bugfixes for *known* issues, but if you think you *might *be able to find
bugs in code you don't like, you should do that on your own time. Iff you
find actual bugs, *then *you talk about halting new releases.

Geoff

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

[Geoff Winkless]

On 19 May 2015 at 21:57, Simon Riggs si...@2ndquadrant.com wrote:

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


​
CREATE TABLE mytable (
  c1 int NOT NULL,
  c2 int NOT NULL,
  PRIMARY KEY (c1),
  UNIQUE (c2)​

​);

INSERT INTO mytable (c1, c2) (10, 20);​
INSERT INTO mytable (c1, c2) (11, 21);
​INSERT INTO mytable (c1, c2) (10, 21) ON CONFLICT DO UPDATE .
​
Or did you mean from a coding point of view how it would be possible to
implement?

​Geoff​

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

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

[Geoff Winkless]

On 19 May 2015 at 21:12, Peter Geoghegan p...@heroku.com 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] INSERT ... ON CONFLICT DO UPDATE with _any_ constraint

[Geoff Winkless]

On 19 May 2015 at 20:11, Simon Riggs si...@2ndquadrant.com 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

[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

Re: [HACKERS] INSERT ... ON CONFLICT syntax issues

[Geoff Winkless]

On 7 May 2015 at 18:37, Andres Freund and...@anarazel.de wrote:

 I don't see a problem at all, with one exception: If we want the AS to
 be optional like in a bunch of other places, we have to either promote
 VALUES to a reserved keyword, only accept unreserved keywords, or play
 precedence games. I think it'd be perfectly fine to not make AS
 optional.


​
Although I've always used AS
​in all contexts ​
because I think the language is
​horribly ​
unclear without it, it seems obtuse to
​allow its absence
in the SQL-conforming parts of the language and not
​elsewhere
.
​
​Is anyone really using VALUES as a non-keyword? It's reserved in all the
SQL standards, which seems like storing up trouble for anyone using it
otherwise.

Geoff

Re: [HACKERS] INSERT ... ON CONFLICT UPDATE/IGNORE 4.0

[Geoff Winkless]

On 8 May 2015 at 16:03, Andres Freund and...@anarazel.de wrote:

 So I've committed the patch yesterday evening. I'm pretty sure there'll
 be some more minor things to change. But overall I feel good about the
 current state.

 It'd be quite helpful if others could read the docs, specifically for
 insert, and comment whether they're understandable. I've spent a fair
 amount of time trying to make them somewhat simpler, but I do think I
 only succeeded partially.  And there's also my own brand of english to
 consider ;)


​
Omitted only has one m.

There's an extra space in error . (See.

Otherwise it reads fine to me, although I've only skimmed it.

I may have misunderstood: there is only one ON CONFLICT action allowed? I
thought the previous version suggested multiple possible targets and
actions, this suggests that while there can be multiple targets the action
is always the same.

So I thought I could have

INSERT INTO distributors (did, dname)
  ON CONFLICT (did) DO UPDATE dname=target.dname
  ON CONFLICT (dname) DO NOTHING;

Did I misunderstand?

Finally there's no

INSERT INTO distributors (did, dname)
  SELECT did, dname FROM otherdists
ON CONFLICT (did) DO NOTHING;

example (or similar); do we think people will be smart enough to realise
that's possible without one?​


​Geoff​

Re: [HACKERS] INSERT ... ON CONFLICT UPDATE/IGNORE 4.0

[Geoff Winkless]

On 8 May 2015 at 16:51, Andres Freund and...@anarazel.de wrote:

 On 2015-05-08 16:36:07 +0100, Geoff Winkless wrote:
  I thought the previous version suggested multiple possible targets and
  actions, this suggests that while there can be multiple targets the
  action is always the same.

 I don't think any version of the patch included that functionality. I
 can see it being useful, but it'd make a bunch of things more
 complicated, so I doubt we'll get there for 9.5.


​I'm not particularly bothered by it - I only see it ever being useful on
the extreme edge case, and
I certainly wouldn't want
​it
to hold up the release - but it was the only reason I could see for
requiring a target_clause in the first place (I
​expect that's why I
misread the docs previously!). If you can't specify different actions based
on the target_clause, what's the point in forcing the user to enumerate it?

 example (or similar); do we think people will be smart enough to realise
  that's possible without one?​

 Hm. I'm tempted to say that the synopis makes that clear enough.


​Unless I'm missing it, it's really only in This happens on a row-by-row
basis and in the deterministic paragraph that you even mention
multi-line inserts in the ON CONFLICT section.​
​​

 I
 ​ ​
 personally never check such examples though, so maybe I'm the wrong
 person to judge.


​I'm afraid I'm the sort of person who goes straight to the examples :)

Maybe I'll just suggest then that there's a _potential for confusion_ if
you only give examples of the first kind - people might place some
inference on the fact that the examples only show single-row INSERTs.

Geoff

Re: [HACKERS] INSERT ... ON CONFLICT syntax issues

[Geoff Winkless]

On 6 May 2015 at 22:30, Heikki Linnakangas hlinn...@iki.fi wrote:

 On 05/07/2015 12:01 AM, Andres Freund wrote:

 On 2015-05-06 23:48:18 +0300, Heikki Linnakangas wrote:

 I'll see about fixing that. It's not just a matter of creating another
 alias
 for the same rel, I'm afraid: foo.t is supposed to refer to the tuple
 that
 we attempted to insert, like it does without the ON CONFLICT.


 I'm not sure what you mean here?


 Sorry, forget about that. I was confused and mixed up EXCLUDED and TARGET.
 Looks like they really aren't very good names :-).


​
Could
​
​
INSERT.column
​and ​
​
CONFLICT.column work?

So INSERT is the row that you were inserting, and CONFLICT is the row with
which it conflicted?
​
Geoff

Re: [HACKERS] INSERT ... ON CONFLICT syntax issues

[Geoff Winkless]

On 28 April 2015 at 15:46, Stephen Frost sfr...@snowman.net wrote:

 +1, NEW/OLD seem pretty natural and I'm not worried about what they look
 like in rules, and their usage in triggers matches up with what they'd
 mean here, I'd think.


Since I've stuck my head above the parapet once I figured I'd give m
y 2p's worth:
​IMHO ​
NEW/OLD doesn't fit at all.

In triggers you're applying it to something that (without the trigger)
would be the new or old version of a matching row
​, so it's completely intuitive​
; in this instance without the ON CONFLICT there would never be a
​​
new
​​
, because it would be
​a ​
failure
​​
.
​​

​MySQL uses VALUES(columnname) to reference the intended INSERT value (what
you might term NEW) and the target name to reference OLD. I understand
that people might think the bracketed syntax isn't very pleasant because
that looks like a function, but it seems more reasonable than NEW (can we
use VALUES.columname?); finally I don't see why we need an OLD (or
TARGET) at all - am I missing the point?

Geoff

Re: [HACKERS] INSERT ... ON CONFLICT syntax issues

[Geoff Winkless]

On 28 April 2015 at 15:57, I wrote:

 ​MySQL uses VALUES(columnname) to reference the intended INSERT value
 (what you might term NEW) and the target name to reference OLD. I
 understand that people might think the bracketed syntax isn't very pleasant
 because that looks like a function, but it seems more reasonable than NEW
 (can we use VALUES.columname?);


​
On balance I
​think I ​
don't like VALUES.column either
​, because although it looks all fine when you're doing a single INSERT ...
VALUES () it gets confusing if you're INSERTing from a SELECT.

​As you were. :(

Geoff

Re: [HACKERS] INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0

[Geoff Winkless]

On 23 April 2015 at 14:50, Andres Freund and...@anarazel.de wrote:

  ​Maybe I'm misreading it, but isn't index_predicate meant to be inside
 the
  brackets?
 
 
 http://postgres-benchmarks.s3-website-us-east-1.amazonaws.com/on-conflict-docs/sql-insert.html

 That has changed since.


​Oh, helpful. :)​

​I'll shut up. I have a feeling that my objection is really with the very
idea of unreserved keywords and I have a feeling that there will be rather
more people shouting me down if I go off on that particular rant; meanwhile
it's 20 years since I used yacc in earnest and it's too hazy to be able to
argue about what it is or isn't capable of.

When I set out I was really only hoping to express a preference as a user;
on balance I would really rather not have DO IGNORE, if it were possible to
avoid, because it's really ugly, but DO UPDATE/DO NOTHING I could just
about cope with (and means you don't need to add IGNORE as a keyword,
win!), although it still mildly pains me that there's an additional
unnecessary word.

But I certainly don't object enough to hold up you guys doing the actual
work for my benefit (among others, obviously!).

G

Re: [HACKERS] INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0

[Geoff Winkless]

Apologies for butting in but can I (as a user) express a preference as a
user against DO?

Firstly, it looks horrible. And what's to stop me having SELECT true AS
do in the where clause (as per your UPDATE objection)?

Shouldn't UPDATE be a reserved keyword anyway? AIUI ANSI suggests so.

http://developer.mimer.se/validator/sql-reserved-words.tml

I had always assumed it was; anyone who produced a query for me that
contained update in an unusual context would get slapped heavily.

Geoff

On 23 April 2015 at 11:54, Andres Freund and...@anarazel.de wrote:

 On 2015-04-22 16:40:07 -0700, Peter Geoghegan wrote:
  On Wed, Apr 22, 2015 at 3:23 PM, Peter Geoghegan p...@heroku.com wrote:
   * We need to sort out those issues with the grammar, since that only
   really applies to the inference specification. Maybe the WHERE clause
   that the inference specification accepts can be broken out. No ON
   CONFLICT UPDATE specific issues left there, AFAICT though.
 
  I pushed some code that deals with the predicate being within
 parenthesis:
 
 
 https://github.com/petergeoghegan/postgres/commit/358854645279523310f998dfc9cb3fe3e165ce1e

 And the way you've used nonassoc here doesn't look correct. You're
 hiding legitimate ambiguities in the grammar. UPDATE is a unreserved
 keyword, so for

 ... ON CONFLICT '(' index_params ')' where_clause OnConflictUpdateStmt

 it won't be able to discern whether an UPDATE in the WHERE clause is
 part of the where_clause or OnConflictUpdate.

 This is legal:
 SELECT * FROM (SELECT true as update) f WHERE update;
 i.e. 'update' can be the last part of a WHERE clause.

 Essentially what you're trying to do with the nonassic is hiding that
 UPDATE and IGNORE need to be reserved keywords with the syntax you're
 proposing. We can either make them reserved or change the syntax.

 One way to avoid making them reserved keywords - which would be somewhat
 painful - is to add a 'DO' before the IGNORE/UPDATE. I.e. something like

   ON CONFLICT opt_conflict_expr DO OnConflictUpdateStmt
 | ON CONFLICT opt_conflict_expr DO IGNORE

 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] INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0

[Geoff Winkless]

On 23 April 2015 at 13:51, Andres Freund and...@anarazel.de wrote:

 On April 23, 2015 3:34:07 PM GMT+03:00, Geoff Winkless 
 pgsqlad...@geoff.dj wrote:
 ​
 ​​
  And what's to stop me having SELECT true
 ​
 AS

do in the where clause (as per your UPDATE objection)?

 A syntax error. DO is a reserved keyword. Update is just unreserved (and
 thus can be used as a column label). Ignore is unreserved with the patch
 and was unreserved before.  We obviously can make both reserved, but of so
 we have to do it for real, not by hiding the conflicts


Sorry, I misunderstood: so it's not the fact that it can't be used as a
column label (because it can) but the fact that it can't then be referenced
within a WHERE clause without quoting
. Which is in itself utterly horrible, but that's a separate argument and I
can at least now understand your point.​

So I could end up with

INSERT INTO mytable (somevalue) VALUES (999) ON CONFLICT ('myindex') WHERE
update UPDATE update=1

but I would have to do

INSERT INTO mytable (somevalue) VALUES (999) ON CONFLICT ('myindex') WHERE
do UPDATE do=1

?

​
 Apologies for butting in but can I (as a user) express a preference as
 a
 ​
 user against DO?

 Sure. If you propose an alternative ;)


​Maybe I'm misreading it, but isn't index_predicate meant to be inside the
brackets?

http://postgres-benchmarks.s3-website-us-east-1.amazonaws.com/on-conflict-docs/sql-insert.html

certainly states that.

It's not one right now. And ignore isn't a keyword at all atm.


​As I said, it's my personal belief that anyone using keywords (of any
kind) unquoted deserves what they get, but I see your point.​


I think I object to the fact that you're talking about adding extra
syntactic sugar to work around a parser-implementation problem, not an
actual syntax problem (since UPDATE SET is unambiguous, isn't it?).

(Please don't top post)


Mea culpa. I blame google :)​

FWIW DO IGNORE just reads disgustingly. If you do finally go down the DO
route, perhaps DO NOTHING? :)

Geoff

Re: [HACKERS] gcc5: initdb produces gigabytes of _fsm files

[Geoff Winkless]

What does the ASM look like? It's a fairly quick way to tell whether the
fail is optimization or memory corruption.

Apologies if I'm explaining how to extract albumen to your elderly
relative...

On 12 February 2015 at 23:16, Tom Lane t...@sss.pgh.pa.us wrote:

 I wrote:
  Christoph Berg c...@df7cb.de writes:
  gcc5 is lurking in Debian experimental, and it's breaking initdb.

  Yeah, I just heard the same about Red Hat as well:
  https://bugzilla.redhat.com/show_bug.cgi?id=1190978
  Not clear if it's an outright compiler bug or they've just found some
  creative new way to make an optimization assumption we're violating.

 Apparently, it's the former.  See

 https://bugzilla.redhat.com/show_bug.cgi?id=1190978#c3

 I will be unamused if the gcc boys try to make an argument that they
 did some valid optimization here.

 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 {UPDATE | IGNORE} 2.0

[Geoff Winkless]

On 30 January 2015 at 21:58, Peter Geoghegan p...@heroku.com wrote:
 On Fri, Jan 30, 2015 at 6:59 AM, Geoff Winkless pgsqlad...@geoff.dj wrote:
 I suppose there's no reason why we couldn't use a no-op ON CONFLICT
 UPDATE anyway

 Right. IGNORE isn't really all that compelling for that reason. Note
 that this will still lock the unmodified row, though.

Mmmf. So I would have to make sure that my source tuples were unique
before doing the INSERT (otherwise the first ON CONFLICT UPDATE for a
tuple would block any other)? That's potentially very slow :(

When you say that you can't add exclusion constraints later, do you
mean from a coding point of view or just because people would get
confused whether exclusion constraints could be IGNOREd or not?

Geoff


-- 
Sent 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 {UPDATE | IGNORE} 2.0

[Geoff Winkless]

On 2 February 2015 at 14:32, Geoff Winkless pgsqlad...@geoff.dj wrote:
 Mmmf. So I would have to make sure that my source tuples were unique
 before doing the INSERT (otherwise the first ON CONFLICT UPDATE for a
 tuple would block any other)? That's potentially very slow :(

Replying to my own message, because it occurs to me I might be being
stupid (surely not :) )

When you say this will still lock the unmodified row did you mean
just that it's locked to _other_ processes until commit? That would be
much less impactful.

Geoff


-- 
Sent 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 {UPDATE | IGNORE} 2.0

[Geoff Winkless]

On Thu, Jan 29, 2015 at 11:38 PM, Peter Geoghegan pg(at)heroku(dot)com wrote:
 Simply removing IGNORE support until such time as we straighten
 that all out (9.6?) seems like the simplest solution. No need to block
 the progress of UPSERT, since exclusion constraint support was
 only ever going to be useful for the less compelling IGNORE variant.
 What do other people think? Do you agree with my view that we should
 shelve IGNORE support for now, Heikki?

I appreciate the work you're doing and (as a user rather than a
pg-hacker) don't want to butt in but if it would be possible to allow
support for IGNORE for unique but not exclusion constraints that would
be really helpful for my own use cases, where being able to insert
from a dataset into a table containing unique constraints without
having to first check the dataset for uniqueness (within both itself
and the target table) is very useful.

It's possible that I've misunderstood anyway and that you mean purely
that exclusion constraint IGNORE should be shelved until 9.6, in which
case I apologise.

Of course if there's no way to allow unique constraint IGNORE without
exclusion constraints then just ignore me; I (along I'm sure with all
the others who are following this conversation from afar) will be
incredibly grateful for the work you've done either way.

I suppose there's no reason why we couldn't use a no-op ON CONFLICT
UPDATE anyway, but that does seem rather messy and (I imagine) would
involve rather more work (unless the optimizer were able to optimize
away the update? I don't know enough to be able to say if it would).

Thanks

Geoff


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