Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE} 2.0

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

Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE} 2.0

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

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

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

Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE} 2.0

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

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

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,

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

​ ​ 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

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

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

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

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

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

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

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

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

Re: [HACKERS] Restore-reliability mode

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

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

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

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

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

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

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 :)​

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

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

Re: [HACKERS] Precedence of standard comparison operators

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

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

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

Re: [HACKERS] About BoringSSL, an OpenSSL fork

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 >

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

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

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

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!)

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

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

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

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

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

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 c

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

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 "the

Re: [HACKERS] Remaining 9.5 open items

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

Re: [HACKERS] Removing Functionally Dependent GROUP BY Columns

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

Re: [HACKERS] Removing Functionally Dependent GROUP BY Columns

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

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

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

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

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 ]

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

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

Re: [HACKERS] Rename max_parallel_degree?

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

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

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

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

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 abou

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

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

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

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

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

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

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

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 >

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

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

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

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

Re: [HACKERS] unexpected psql "feature"

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

Re: [HACKERS] pnstrdup considered armed and dangerous

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

Re: [HACKERS] pnstrdup considered armed and dangerous

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] >

Re: [HACKERS] Hash Indexes

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

Re: [HACKERS] DISTINCT with btree skip scan

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

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

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 >

Re: [HACKERS] jsonb problematic operators

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

Re: [HACKERS] jsonb problematic operators

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".

Re: [HACKERS] jsonb problematic operators

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

Re: [HACKERS] MERGE SQL Statement for PG11

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

Re: [HACKERS] MERGE SQL Statement for PG11

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

Re: [HACKERS] Cursor With_Hold Performance Workarounds/Optimization

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