On Wed, May 20, 2015 at 11:26 AM, Andres Freund wrote:
> Even if maybe not directly under the guise of exclusion constraints
> themselves, but I do think it's an interesting way to more easily allow
> to implement unique constraints on !amcanunique type indexes. Or, more
> interestingly, for uniq
On 2015-05-20 12:07:56 -0700, Peter Geoghegan wrote:
> You're talking about exclusion constraints as an implementation detail
> of something interesting, which I had not considered.
I did mention those two usecases a bunch of times... ;)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@pos
On 2015-05-20 11:24:06 -0700, Peter Geoghegan wrote:
> On Wed, May 20, 2015 at 10:37 AM, Andres Freund wrote:
> > But you *can* use a exclusion constraint for DO NOTHING. Just not (yet)
> > for DO UPDATE.
>
> FWIW, I don't think exclusion constraint DO UPDATE support is ever
> going to be useful.
On Wed, May 20, 2015 at 10:37 AM, Andres Freund wrote:
> But you *can* use a exclusion constraint for DO NOTHING. Just not (yet)
> for DO UPDATE.
FWIW, I don't think exclusion constraint DO UPDATE support is ever
going to be useful.
--
Peter Geoghegan
--
Sent via pgsql-hackers mailing list
Andres Freund writes:
> On 2015-05-20 13:31:57 -0400, Tom Lane wrote:
>> If you can't use an exclusion constraint to support the command,
>> then the error message shouldn't be worded like that.
> But you *can* use a exclusion constraint for DO NOTHING. Just not (yet)
> for DO UPDATE.
Hm. Maybe
On 2015-05-20 13:31:57 -0400, Tom Lane wrote:
> Sure, but on what basis does it decide that there's a conflict?
>
> If you can't use an exclusion constraint to support the command,
> then the error message shouldn't be worded like that.
But you *can* use a exclusion constraint for DO NOTHING. Jus
Andres Freund writes:
> On 2015-05-20 18:09:05 +0100, Thom Brown wrote:
This implies that an exclusion constraint is valid in the statement,
which contradicts the docs. Which one is correct?
>>> ON CONFLICT can be used for ... DO NOTHING as well.
>> Yes, but still confusing when not u
On 2015-05-20 18:09:05 +0100, Thom Brown wrote:
> On 20 May 2015 at 17:54, Andres Freund wrote:
> > On 2015-05-20 17:44:05 +0100, Thom Brown wrote:
> >> The docs say "Note that exclusion constraints are not supported with
> >> ON CONFLICT DO UPDATE."
> >>
> >> But I get the following error message
On 20 May 2015 at 17:54, Andres Freund wrote:
> On 2015-05-20 17:44:05 +0100, Thom Brown wrote:
>> On 8 May 2015 at 16:03, Andres Freund 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
On 2015-05-20 17:44:05 +0100, Thom Brown wrote:
> On 8 May 2015 at 16:03, Andres Freund 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
On 8 May 2015 at 16:03, Andres Freund 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 c
Andres Freund writes:
> prairiedog, without CCA, failed as well
> http://pgbuildfarm.org/cgi-bin/show_log.pl?nm=prairiedog&dt=2015-05-08%2019%3A55%3A11
> different test, but again directly after index creation. So I hope it's
> indeed the indcheckxmin thing.
Oh, interesting. That definitely sugg
On 2015-05-08 22:29:47 +0200, Andres Freund wrote:
> On 2015-05-08 15:22:09 -0400, Tom Lane wrote:
> > I'm back to suspecting that the indcheckxmin issue is the true cause of
> > the buildfarm failure
> > though we lack an explanation why Andres failed to reproduce it ...
>
> My laptop is probabl
On 2015-05-08 15:22:09 -0400, Tom Lane wrote:
> I'm back to suspecting that the indcheckxmin issue is the true cause of
> the buildfarm failure
Me too.
> though we lack an explanation why Andres failed to reproduce it ...
My laptop is probably a good bit faster than jaguarundi, particularly in
a
Stephen Frost writes:
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
>> Actually, looking closer, the quoted code is simply not broken without
>> RELCACHE_FORCE_RELEASE: without that, neither heap_close nor index_close
>> will do anything that could cause a cache flush. So while it's certainly
>> good
* Tom Lane (t...@sss.pgh.pa.us) wrote:
> I wrote:
> > Peter Geoghegan writes:
> >> On Fri, May 8, 2015 at 11:59 AM, Tom Lane wrote:
> >>> Ooops. But shouldn't that have failed 100% of the time in a CCA build?
> >>> Or is the candidates list fairly noncritical?
>
> >> The candidates list is abso
I wrote:
> Peter Geoghegan writes:
>> On Fri, May 8, 2015 at 11:59 AM, Tom Lane wrote:
>>> Ooops. But shouldn't that have failed 100% of the time in a CCA build?
>>> Or is the candidates list fairly noncritical?
>> The candidates list is absolutely critical.
> Oh, I was confusing CCA with RELC
* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Peter Geoghegan writes:
> > On Fri, May 8, 2015 at 11:59 AM, Tom Lane wrote:
> >> Ooops. But shouldn't that have failed 100% of the time in a CCA build?
> >> Or is the candidates list fairly noncritical?
>
> > The candidates list is absolutely critical.
On 2015-05-08 14:59:22 -0400, Tom Lane wrote:
> Andres Freund writes:
> > I think Peter (on IM) just found a more likely explanation than mine.
> > index_close(idxRel, NoLock);
> > heap_close(relation, NoLock);
> > candidates = lappend_oi
Peter Geoghegan writes:
> On Fri, May 8, 2015 at 11:59 AM, Tom Lane wrote:
>> Ooops. But shouldn't that have failed 100% of the time in a CCA build?
>> Or is the candidates list fairly noncritical?
> The candidates list is absolutely critical.
Oh, I was confusing CCA with RELCACHE_FORCE_RELEAS
* Peter Geoghegan (p...@heroku.com) wrote:
> On Fri, May 8, 2015 at 12:00 PM, Peter Geoghegan wrote:
> > On Fri, May 8, 2015 at 11:59 AM, Tom Lane wrote:
> >> Ooops. But shouldn't that have failed 100% of the time in a CCA build?
> >> Or is the candidates list fairly noncritical?
> >
> > The can
On Fri, May 8, 2015 at 12:00 PM, Peter Geoghegan wrote:
> On Fri, May 8, 2015 at 11:59 AM, Tom Lane wrote:
>> Ooops. But shouldn't that have failed 100% of the time in a CCA build?
>> Or is the candidates list fairly noncritical?
>
> The candidates list is absolutely critical.
However, the prob
On Fri, May 8, 2015 at 11:59 AM, Tom Lane wrote:
> Ooops. But shouldn't that have failed 100% of the time in a CCA build?
> Or is the candidates list fairly noncritical?
The candidates list is absolutely critical.
--
Peter Geoghegan
--
Sent via pgsql-hackers mailing list (pgsql-hackers@post
Andres Freund writes:
> I think Peter (on IM) just found a more likely explanation than mine.
> index_close(idxRel, NoLock);
> heap_close(relation, NoLock);
> candidates = lappend_oid(candidates,
> idxForm->indexrelid);
> ...
> Yes
On Fri, May 8, 2015 at 11:35 AM, Andres Freund wrote:
> I think Peter (on IM) just found a more likely explanation than mine.
>
> index_close(idxRel, NoLock);
> heap_close(relation, NoLock);
> candidates = lappend_oid(candidat
On 2015-05-08 14:30:46 -0400, Tom Lane wrote:
> Maybe just hold an open transaction in another session while you do what
> the regression test does? I think this is probably not a matter of CCA
> per se but just timing. It's unfortunate that the test in question is
> run serially without other tr
Andres Freund writes:
> On 2015-05-08 11:10:00 -0700, Peter Geoghegan wrote:
>> +1. I knew we should have done this before commit.
> Hrmpf.
> I couldn't hit the problem with CCA unfortunately, even after a bunch of
> tries; quite possibly it's too fast on my laptop.
Maybe just hold an open tran
On 2015-05-08 11:10:00 -0700, Peter Geoghegan wrote:
> +1. I knew we should have done this before commit.
Hrmpf.
I couldn't hit the problem with CCA unfortunately, even after a bunch of
tries; quite possibly it's too fast on my laptop. So I'll just have
remove the check and we'll see whether it
On Fri, May 8, 2015 at 11:06 AM, Andres Freund wrote:
> On 2015-05-08 20:37:15 +0300, Heikki Linnakangas wrote:
>> Why does INSERT ON CONFLICT pay attention to indcheckxmin? Uniqueness check
>> only cares about the most recent committed version of the tuple, and the
>> index good for that use imme
On 2015-05-08 20:37:15 +0300, Heikki Linnakangas wrote:
> Why does INSERT ON CONFLICT pay attention to indcheckxmin? Uniqueness check
> only cares about the most recent committed version of the tuple, and the
> index good for that use immediately. If there was a problem there, the
> uniqueness chec
On 2015-05-08 19:32:02 +0200, Andres Freund wrote:
> If the failure is indeed caused by checkxmin (trying to reproduce
> right now), we can just remove the updates in that subsection of the
> tests. They're not relevant.
Hm. Or easier and uglier, replace the CREATE INDEX statements with
CREATE IN
On 05/08/2015 08:25 PM, Tom Lane wrote:
Andres Freund writes:
On 2015-05-08 12:32:10 -0400, Tom Lane wrote:
Looks like there's a CLOBBER_CACHE_ALWAYS issue ...
http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=jaguarundi&dt=2015-05-08%2011%3A52%3A00
Currently index inferrence ignores i
On 2015-05-08 13:25:22 -0400, Tom Lane wrote:
> Andres Freund writes:
> > On 2015-05-08 12:32:10 -0400, Tom Lane wrote:
> >> Looks like there's a CLOBBER_CACHE_ALWAYS issue ...
> >> http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=jaguarundi&dt=2015-05-08%2011%3A52%3A00
>
> > Currently inde
Andres Freund writes:
> On 2015-05-08 12:32:10 -0400, Tom Lane wrote:
>> Looks like there's a CLOBBER_CACHE_ALWAYS issue ...
>> http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=jaguarundi&dt=2015-05-08%2011%3A52%3A00
> Currently index inferrence ignores indexes that aren't yet valid
> accor
On 2015-05-08 12:32:10 -0400, Tom Lane wrote:
> Andres Freund writes:
> > 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.
>
> Looks like there's a CLOBBER_CACHE_ALWAYS issue ...
>
Andres Freund writes:
> 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.
Looks like there's a CLOBBER_CACHE_ALWAYS issue ...
http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=jagu
On 8 May 2015 at 16:51, Andres Freund 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 an
On 2015-05-08 16:36:07 +0100, Geoff Winkless wrote:
> 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.
Thanks, I'll push fixes for those.
> I may have misunderstood: there is only one ON CONFLICT action
> all
On 8 May 2015 at 16:03, Andres Freund 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
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
On 05/06/2015 09:51 PM, Heikki Linnakangas wrote:
So, yes, DO NOTHING does very little - and that is its appeal.
Supporting this behavior does not short change those who actually care
about the existing tuple sticking around for the duration of their
transaction - they have a way of doing that. I
On 2015-05-06 22:51:43 +0300, Heikki Linnakangas wrote:
> Yeah, I agree that DO NOTHING should not lock the rows. It might make sense
> to have a DO LOCK variant, which locks the rows, although I don't
> immediately see what the use case would be.
If you want to do something more complicated with
On 05/06/2015 10:47 PM, Peter Geoghegan wrote:
On Wed, May 6, 2015 at 8:20 AM, Andres Freund wrote:
On 2015-05-05 15:00:56 -0700, Peter Geoghegan wrote:
Locking the row is not "nothing", though. If you want to lock the row,
use an UPSERT with a tautologically false WHERE clause (like "WHERE
fa
On Wed, May 6, 2015 at 8:20 AM, Andres Freund wrote:
> On 2015-05-05 15:00:56 -0700, Peter Geoghegan wrote:
>> Locking the row is not "nothing", though. If you want to lock the row,
>> use an UPSERT with a tautologically false WHERE clause (like "WHERE
>> false").
>
> That's not the same. For one
On Tue, May 5, 2015 at 10:31 AM, Andres Freund wrote:
> Another thing I'm wondering about is dealing with deferrable
> constraints/deferred indexes.
>
> a) Why does ExecCheckIndexConstraints() check for indisimmediate for
>*all* indexes and not just when it's an arbiter index? That seems
>
On 2015-05-05 15:00:56 -0700, Peter Geoghegan wrote:
> Locking the row is not "nothing", though. If you want to lock the row,
> use an UPSERT with a tautologically false WHERE clause (like "WHERE
> false").
That's not the same. For one it "breaks" RETURNING which is a death
knell, for another it's
On Tue, May 5, 2015 at 8:40 AM, Andres Freund wrote:
> One additional thing I'm wondering about is the following: Right now
> INSERT ... ON CONFLICT NOTHING does not acquire a row level lock on the
> 'target' tuple. Are we really ok with that? Because in this form ON
> CONFLICT NOTHING really does
Hi,
On 2015-04-26 18:02:06 -0700, Peter Geoghegan wrote:
> Remaining challenges
> =
One additional thing I'm wondering about is the following: Right now
INSERT ... ON CONFLICT NOTHING does not acquire a row level lock on the
'target' tuple. Are we really ok with that? Because in t
On 2015-04-26 18:02:06 -0700, Peter Geoghegan wrote:
> Remaining challenges
> =
Another thing I'm wondering about is dealing with deferrable
constraints/deferred indexes.
a) Why does ExecCheckIndexConstraints() check for indisimmediate for
*all* indexes and not just when it's a
On Mon, May 4, 2015 at 9:00 PM, Andres Freund wrote:
> I think it's pretty clear that we'll have to require that.
Okay, then. I'll push out revised testing of column-level privileges
later. (Andres rebased, and we're now pushing code to:
https://github.com/petergeoghegan/postgres/commits/insert_c
On 2015-05-04 19:13:27 -0700, Peter Geoghegan wrote:
> A question has come up about RTEs, column-level privileges and BEFORE
> triggers. This commit message gives a summary:
>
> https://github.com/petergeoghegan/postgres/commit/87b9f27055e81d1396db3d10a5e9d01c52603783
>
> I'm pretty sure that I'l
On Fri, May 1, 2015 at 7:49 AM, Andres Freund wrote:
>> seems weird for both the BEFORE INSERT and BEFORE UPDATE triggers to
>> get a crack at the same tuple, so your way might be better after all.
>> But on the other hand, the BEFORE INSERT trigger might have had side
>> effects, so we can't just
On Fri, May 1, 2015 at 7:47 AM, Heikki Linnakangas wrote:
> Hmm, so it was stuck for half an hour at that point? Why do you think it was
> a livelock?
>
>> This is the same server that I shared credentials with you for. Feel
>> free to ssh in and investigate it yourself.
>
>
> I logged in, but the
* Robert Haas (robertmh...@gmail.com) wrote:
> OK. In that case, I'm a lot less sure what the right decision is. It
> seems weird for both the BEFORE INSERT and BEFORE UPDATE triggers to
> get a crack at the same tuple, so your way might be better after all.
> But on the other hand, the BEFORE IN
On Fri, May 1, 2015 at 10:49 AM, Andres Freund wrote:
>> One idea is to decide that an INSERT with an ON CONFLICT UPDATE
>> handler is still an INSERT. Period. So the INSERT triggers run, the
>> UPDATE triggers don't, and that's it.
>
> I think that'd be much worse.
OK. Well, in that case, I g
On 2015-05-01 10:39:35 -0400, Robert Haas wrote:
> On Fri, May 1, 2015 at 10:24 AM, Andres Freund wrote:
> > The BEFORE UPDATE would catch things in this case.
>
> OK. In that case, I'm a lot less sure what the right decision is. It
> seems weird for both the BEFORE INSERT and BEFORE UPDATE tri
On 04/30/2015 11:09 PM, Peter Geoghegan wrote:
I've been unable to reproduce the unprincipled deadlock using the same
test case as before. However, the exclusion constraint code now
livelocks. Here is example output from a stress-testing session:
...
[Fri May 1 04:45:35 2015] normal exit at 14
On Fri, May 1, 2015 at 10:24 AM, Andres Freund wrote:
>> > Well, it's a BEFORE INSERT trigger, not a BEFORE UPDATE, that's why I'm
>> > not so sure that argument applies.
>>
>> Would the BEFORE UPDATE trigger even fire in this case?
>
> BEFORE UPDATE triggers fire for INSERT ... ON CONFLICT UPDATE
On 2015-05-01 10:21:27 -0400, Robert Haas wrote:
> On Fri, May 1, 2015 at 10:10 AM, Andres Freund wrote:
> > On 2015-05-01 10:06:42 -0400, Robert Haas wrote:
> >> On Fri, May 1, 2015 at 9:58 AM, Andres Freund wrote:
> >> > would you rather have EXCLUDED.data refer to the tuple version from
> >> >
On Fri, May 1, 2015 at 10:10 AM, Andres Freund wrote:
> On 2015-05-01 10:06:42 -0400, Robert Haas wrote:
>> On Fri, May 1, 2015 at 9:58 AM, Andres Freund wrote:
>> > would you rather have EXCLUDED.data refer to the tuple version from
>> > VALUES (or a SELECT or ...) or to version from the BEFORE
On 01/05/15 16:10, Andres Freund wrote:
On 2015-05-01 10:06:42 -0400, Robert Haas wrote:
On Fri, May 1, 2015 at 9:58 AM, Andres Freund wrote:
would you rather have EXCLUDED.data refer to the tuple version from
VALUES (or a SELECT or ...) or to version from the BEFORE trigger?
I think it woul
On 2015-05-01 10:06:42 -0400, Robert Haas wrote:
> On Fri, May 1, 2015 at 9:58 AM, Andres Freund wrote:
> > would you rather have EXCLUDED.data refer to the tuple version from
> > VALUES (or a SELECT or ...) or to version from the BEFORE trigger?
>
> I think it would be completely shocking if it
On Fri, May 1, 2015 at 9:58 AM, Andres Freund wrote:
> Right now this, besides cleanup, docs and syntax leaves only one real
> issue I know of. Which is the question what EXCLUDED actually refers to.
>
> Consider a table
> blarg(key int primary key, data text); with a BEFORE INSERT
> trigger that
On 2015-04-26 18:02:06 -0700, Peter Geoghegan wrote:
> Remaining challenges
> =
So I did the executor changes I'd mentioned downthread, and Peter agreed
that it'd quite workable.
Right now this, besides cleanup, docs and syntax leaves only one real
issue I know of. Which is the qu
On Thu, Apr 30, 2015 at 7:00 PM, Heikki Linnakangas wrote:
> To fix that, we need to fix the "livelock insurance" check so that A does
> not wait for B here. Because B is not a speculative insertion, A should
> cancel its speculative insertion and retry instead. (I pushed the one-line
> fix for th
On 04/27/2015 11:02 PM, Peter Geoghegan wrote:
On Mon, Apr 27, 2015 at 8:31 PM, Heikki Linnakangas wrote:
I thought we had an ironclad scheme to prevent deadlocks like this, so I'd
like to understand why that happens.
Okay. I think I know how it happens (I was always skeptical of the
idea th
On Tue, Apr 28, 2015 at 3:38 AM, Andres Freund wrote:
> The more I look at approach taken in the executor, the less I like it.
> I think the fundamental structural problem is that you've chosen to
> represent the ON CONFLICT UPDATE part as fully separate plan tree node;
> planned nearly like a nor
On 2015-04-27 23:52:58 +0200, Andres Freund wrote:
> On 2015-04-27 16:28:49 +0200, Andres Freund wrote:
> > On 2015-04-26 18:02:06 -0700, Peter Geoghegan wrote:
> > > * So far, there has been a lack of scrutiny about what the patch does
> > > in the rewriter (in particular, to support the EXCLUDED.
On Mon, Apr 27, 2015 at 6:43 AM, Andres Freund wrote:
> Could you please add the tests for the logical decoding code you added?
> I presume you have some already/
Most of the tests I used for logical decoding were stress tests (i.e.
prominently involved my favorite tool, jjanes_upsert). There is
On Mon, Apr 27, 2015 at 8:31 PM, Heikki Linnakangas wrote:
> I thought we had an ironclad scheme to prevent deadlocks like this, so I'd
> like to understand why that happens.
Okay. I think I know how it happens (I was always skeptical of the
idea that this would be 100% reliable), but I'll be ab
On 04/27/2015 07:02 PM, Peter Geoghegan wrote:
So, this can still happen, but is now happening less often than
before, I believe. On a 16 core server, with continual 128 client
jjanes_upsert exclusion constraint only runs, with fsync=off, I
started at this time:
2015-04-27 21:22:28 UTC [ 0 ]: LO
On Mon, Apr 27, 2015 at 7:02 PM, Peter Geoghegan wrote:
> Given that exclusion constraints can only be used with IGNORE, and
> given that this is so hard to recreate, I'm inclined to conclude that
> it's acceptable. It's certainly way better than risking livelocks by
> not having "deadlock insuran
On Sun, Apr 26, 2015 at 6:02 PM, Peter Geoghegan wrote:
> * I privately pointed out to Heikki what I'd said publicly about 6
> weeks ago: that there is still a *very* small chance of exclusion
> constraints exhibiting "unprincipled deadlocks" (he missed it at the
> time). I think that this risk is
On Mon, Apr 27, 2015 at 2:52 PM, Andres Freund wrote:
> So, I'm looking. And I've a few questions:
> * Why do we need to spread knowledge about speculative inserts that wide?
> It's now in 1) Query, 2) ParseState 3) ModifyTable 4) InsertStmt. That
> seems a bit wide - and as far as I see not r
On 2015-04-27 16:28:49 +0200, Andres Freund wrote:
> On 2015-04-26 18:02:06 -0700, Peter Geoghegan wrote:
> > * So far, there has been a lack of scrutiny about what the patch does
> > in the rewriter (in particular, to support the EXCLUDED.* pseudo-alias
> > expression) and optimizer (the whole con
On 2015-04-26 18:02:06 -0700, Peter Geoghegan wrote:
> * So far, there has been a lack of scrutiny about what the patch does
> in the rewriter (in particular, to support the EXCLUDED.* pseudo-alias
> expression) and optimizer (the whole concept of an "auxiliary"
> query/plan that share a target RTE
On 2015-04-26 18:02:06 -0700, Peter Geoghegan wrote:
> It's make-or-break time for this patch. Please help me get it over the
> line in time.
Could you please add the tests for the logical decoding code you added?
I presume you have some already/
> Heikki is in Northern California this week, and
On Sun, Apr 26, 2015 at 6:02 PM, Peter Geoghegan wrote:
> Remaining challenges
> =
I may have forgotten one: Andres asked me to make logical decoding
discriminate against speculative confirmation records/changes, as
opposed to merely looking for the absence of a super-deletion. We
78 matches
Mail list logo
