On Thu, Aug 25, 2016 at 12:59 PM, Peter Geoghegan wrote:
> Maybe we should change the ordering of those IndexInfo structs to
> something more suitable, but it must be immutable (it cannot hinge
> upon the details of one particular DML statement).
I meant that it must be stable (not immutable), in
On Thu, Aug 25, 2016 at 12:16 PM, Tom Lane wrote:
> I'm not following. The only insertions happening in this test case
> are coming from various clients doing the same INSERT ON CONFLICT UPDATE.
> If one of them has successfully inserted "42" into the arbiter index,
> how is it that other ones ar
On 08/25/2016 10:08 PM, Peter Geoghegan wrote:
On Thu, Aug 25, 2016 at 11:49 AM, Tom Lane wrote:
I think the point is that given the way he's set up the test case,
there should be no duplicate violation in the plain unique index
unless there is one in the arbiter index. So assuming that INSERT
Peter Geoghegan writes:
> (I don't think that it matters which order anything is tested
> in, though, because not finding a dup value in the arbiter index does
> not guarantee that there won't be one in the other index. There is no
> locking when no conflict is initially found, and so no guarantee
On Thu, Aug 25, 2016 at 11:49 AM, Tom Lane wrote:
> I think the point is that given the way he's set up the test case,
> there should be no duplicate violation in the plain unique index
> unless there is one in the arbiter index. So assuming that INSERT
> tests the arbiter indexes first, there sh
Peter Geoghegan writes:
> On Thu, Aug 25, 2016 at 7:12 AM, Ivan Frolkov wrote:
>> So, if we have primary key and unique constraint on a table then upsert will
>> not work as would expected.
> Why is this unexpected?
> You only take the alternative path (UPDATE) in the event of a would-be
> dupl
On Thu, Aug 25, 2016 at 7:12 AM, Ivan Frolkov wrote:
> So, if we have primary key and unique constraint on a table then upsert will
> not work as would expected.
Why is this unexpected?
You only take the alternative path (UPDATE) in the event of a would-be
duplicate violation. You can't upsert w
Suppose we have some table
create table cnt(
usr_id int primary key,
usr_doc_ref text not null,
cnt int,
sum int
);
And going to run some insert on conflict update on it (pgbench script):
\setrandom id 1 50
insert into cnt as c(usr_id,usr_doc_ref, cnt) values(:id, '#'||:id, 1) on
con
Peter Geoghegan writes:
> On Wed, Jul 13, 2016 at 2:49 AM, Bjørnar Ness wrote:
>> But with upsert/do nothing, this will not work as "needed".
>>
>> Would it be possible to introduce a "ON CONFLICT SELECT" argument:
>>
>> with _foo as (
>> insert into foo(i) values(1)
>> on conflict select retu
On Wed, Jul 13, 2016 at 2:49 AM, Bjørnar Ness wrote:
> But with upsert/do nothing, this will not work as "needed".
>
> Would it be possible to introduce a "ON CONFLICT SELECT" argument:
>
> with _foo as (
> insert into foo(i) values(1)
> on conflict select returning id
> ) insert into bar(foo_
The new upsert feature is a great addition, but in some cases is not
as usable as
I and seems lots of others would like it to be, take an example with
circular references:
create table foo (
id serial references bar(foo_id) on delete cascade,
i int
);
create table bar (
foo_id integer refer
On 2015-06-25 AM 09:51, Amit Langote wrote:
>
> Peter,
>
> On 2015-06-25 AM 02:35, Peter Geoghegan wrote:
>>
>> Inheritance with triggers is a leaky abstraction, so this kind of
>> thing is always awkward. Still, UPSERT has full support for
>> *inheritance* -- that just doesn't help in this case.
On 24 June 2015 at 15:05, Fujii Masao wrote:
> How should we treat this problem for 9.5? If we want to fix this problem
> completely, probably we would need to make constraint_exclusion work with
> even UPSERT. Which sounds difficult to do at least for 9.5. Any other idea?
> Or we should just tr
On 6/24/15 1:03 PM, Peter Geoghegan wrote:
On Wed, Jun 24, 2015 at 11:02 AM, Peter Geoghegan wrote:
I think that the real way to fix this is, as you say, to make it so
that it isn't necessary in general to write trigger functions like
this to make inheritance work.
Excuse me -- I mean to make
Peter,
On 2015-06-25 AM 02:35, Peter Geoghegan wrote:
>
> Inheritance with triggers is a leaky abstraction, so this kind of
> thing is always awkward. Still, UPSERT has full support for
> *inheritance* -- that just doesn't help in this case.
>
Could you clarify as to what UPSERT's support for
On Wed, Jun 24, 2015 at 11:02 AM, Peter Geoghegan wrote:
> I think that the real way to fix this is, as you say, to make it so
> that it isn't necessary in general to write trigger functions like
> this to make inheritance work.
Excuse me -- I mean to make *partitioning* work.
--
Peter Geoghega
On Wed, Jun 24, 2015 at 7:05 AM, Fujii Masao wrote:
> How should we treat this problem for 9.5? If we want to fix this problem
> completely, probably we would need to make constraint_exclusion work with
> even UPSERT. Which sounds difficult to do at least for 9.5. Any other idea?
> Or we should ju
On Wed, Jun 24, 2015 at 7:38 AM, Robert Haas wrote:
> Is the root of the problem that the trigger is called for an INSERT ..
> ON CONFLICT statement but it turns that into a plain INSERT?
>
> Is there any way of writing a partitioning trigger that doesn't have
> that defect?
We did discuss whethe
On 2015-06-24 10:38:38 -0400, Robert Haas wrote:
> On Wed, Jun 24, 2015 at 10:29 AM, Andres Freund wrote:
> > On 2015-06-24 23:05:45 +0900, Fujii Masao wrote:
> >> INSERT ON CONFLICT DO UPDATE doesn't seem to work on the current
> >> partitioning
> >> mechanism. For example, in the following SQL
On Wed, Jun 24, 2015 at 10:29 AM, Andres Freund wrote:
> On 2015-06-24 23:05:45 +0900, Fujii Masao wrote:
>> INSERT ON CONFLICT DO UPDATE doesn't seem to work on the current partitioning
>> mechanism. For example, in the following SQL commands, the last UPSERT
>> command
>> would fail with an err
Hi,
On 2015-06-24 23:05:45 +0900, Fujii Masao wrote:
> INSERT ON CONFLICT DO UPDATE doesn't seem to work on the current partitioning
> mechanism. For example, in the following SQL commands, the last UPSERT command
> would fail with an error. The error message is
I think that's pretty much inevita
Hi,
INSERT ON CONFLICT DO UPDATE doesn't seem to work on the current partitioning
mechanism. For example, in the following SQL commands, the last UPSERT command
would fail with an error. The error message is
ERROR: duplicate key value violates unique constraint "hoge_20150601_pkey"
DETAI
On Sun, Oct 19, 2014 at 2:52 PM, Greg Stark wrote:
> Well OLD and NEW are also not joins yet we expose them this way. It
> always seemed like a hack to me but better one hack than two different
> inconsistent hacks, no?
In my opinion, no. Those hacks do not appear in the parse analysis of
ordinar
On Thu, Oct 16, 2014 at 8:00 PM, Peter Geoghegan wrote:
> Basically, it's difficult to make this work for technical reasons
> precisely because what I have here isn't join-like. Can I easily
> disallow "OLD.*" in a RETURNING clause (recall that we only project
> inserted tuples, as always)? Even i
On Thu, Oct 16, 2014 at 11:39 AM, Robert Haas wrote:
> On Thu, Oct 16, 2014 at 2:01 PM, Peter Geoghegan wrote:
>> I want to retain CONFLICTING(), although I'm thinking about changing
>> the spelling to EXCLUDED(). While CONFLICTING() is more or less a new
>> and unprecedented style of expression,
On Thu, Oct 16, 2014 at 2:01 PM, Peter Geoghegan wrote:
> I want to retain CONFLICTING(), although I'm thinking about changing
> the spelling to EXCLUDED(). While CONFLICTING() is more or less a new
> and unprecedented style of expression, and in general that's something
> to be skeptical of, I th
On Thu, Oct 16, 2014 at 11:01 AM, Peter Geoghegan wrote:
> It is? In any case, I'm working on a revision with this syntax:
By the way, in my next revision, barring any objections, the ON
CONFLICT (column/expression) syntax is mandatory in the case of ON
CONFLICT UPDATE, and optional in the case o
On Thu, Oct 16, 2014 at 6:48 AM, Robert Haas wrote:
> If that seems too complicated, leave it out for v1: just insist that
> there must be at least one unique non-partial index on the relevant
> set of columns.
That's what I'll do.
> There seems to be some confusion here. This part was about th
On Mon, Oct 13, 2014 at 2:02 PM, Peter Geoghegan wrote:
>> If the user issues INSERT .. ON DUPLICATE (a) UPDATE, we'll fire
>> before-insert triggers and then inspect the tuple to be inserted. If
>> b is neither 1 nor 2, then we'll fail with an error saying that we
>> can't support ON DUPLICATE w
On Mon, Oct 13, 2014 at 7:46 AM, Robert Haas wrote:
> Come on. You've had the syntax that way for a while, multiple people
> have objected to it, and it didn't get changed. When people renewed
> their objections, you said that they were not having a "realistic
> conversation". I'm tired of gett
On Fri, Oct 10, 2014 at 4:41 PM, Peter Geoghegan wrote:
> On Fri, Oct 10, 2014 at 12:09 PM, Robert Haas wrote:
>> I think what's realistic here is that the patch isn't going to get
>> committed the way you have it today, because nobody else likes that
>> design. That may be harsh, but I think it
On 2014-10-12 13:40, Marko Tiikkaja wrote:
> On 10/12/14, 2:36 PM, Matthew Woodcraft wrote:
>> On 2014-10-10 19:44, Kevin Grittner wrote:
>>> To restate: to do so is conflating the logical definition of the
>>> database with a particular implementation detail. As just one
>>> reason that is a bad
On 10/12/14, 2:36 PM, Matthew Woodcraft wrote:
On 2014-10-10 19:44, Kevin Grittner wrote:
To restate: to do so is conflating the logical definition of the
database with a particular implementation detail. As just one
reason that is a bad idea: we can look up unique indexes on the
specified colu
On 2014-10-10 19:44, Kevin Grittner wrote:
> Peter Geoghegan wrote:
>> People keep remarking that they don't like that you can (optionally)
>> name a unique index explicitly,
[...]
> To restate: to do so is conflating the logical definition of the
> database with a particular implementation de
On Fri, Oct 10, 2014 at 2:16 PM, Kevin Grittner wrote:
>> Would you be okay with this never working with partial unique
>> indexes? That gives me something to work with.
>
> That seems like the only sensible course, to me.
Okay, great. If that's the consensus, then that's all I need to know.
>>
On Fri, Oct 10, 2014 at 2:17 PM, Jim Nasby wrote:
> That said, since the use case for UPSERT is different than both INSERT and
> UPDATE maybe it would be a good idea to have a separate trigger for them
> anyway.
-1. I think that having a separate UPSERT trigger is a bad idea. I'll
need to change
Peter Geoghegan wrote:
> On Fri, Oct 10, 2014 at 12:09 PM, Robert Haas wrote:
>> I think what's realistic here is that the patch isn't going to get
>> committed the way you have it today, because nobody else likes that
>> design. That may be harsh, but I think it's accurate.
>
> I do think that'
On 10/9/14, 6:59 PM, Gavin Flower wrote:
On 10/10/14 12:38, Jim Nasby wrote:
On 10/8/14, 5:51 PM, Peter Geoghegan wrote:
On Wed, Oct 8, 2014 at 2:01 PM, Kevin Grittner wrote:
>Although the last go-around does suggest that there is at least one
>point of difference on the semantics. You seem t
On Fri, Oct 10, 2014 at 12:09 PM, Robert Haas wrote:
> I think what's realistic here is that the patch isn't going to get
> committed the way you have it today, because nobody else likes that
> design. That may be harsh, but I think it's accurate.
I do think that's harsh, because it's unnecessar
On Fri, Oct 10, 2014 at 12:04 PM, Kevin Grittner wrote:
> Peter Geoghegan wrote:
>
>> There is no danger of UPDATE before row-level triggers firing
>> without then updating (unless the xact aborts, but you know what
>> I mean).
>
> Well, let's make sure I do know what you mean. If a BEFORE UPDAT
On Fri, Oct 10, 2014 at 11:55 AM, Kevin Grittner wrote:
> I haven't read the patch, but the discussion has made that clear,
> yes. Try to take agreement on a point gracefully, will ya? ;-)
Heh, sorry. I did literally mean what I said - it wasn't 100% clear to
me that you got that. It's safest t
Peter Geoghegan wrote:
> On Fri, Oct 10, 2014 at 11:44 AM, Kevin Grittner wrote:
>> [discussion on committers rejecting the notion of a syntax
>> involving specification of an index name]
> as I've said many times, there are problems with naming
> columns directly that need to be addressed. T
On Fri, Oct 10, 2014 at 2:29 PM, Peter Geoghegan wrote:
> People keep remarking that they don't like that you can (optionally)
> name a unique index explicitly, and I keep telling them why I've done
> it that way [1]. There is a trade-off here. I am willing to go another
> way in that trade-off, b
Peter Geoghegan wrote:
> There is no danger of UPDATE before row-level triggers firing
> without then updating (unless the xact aborts, but you know what
> I mean).
Well, let's make sure I do know what you mean. If a BEFORE UPDATE
... FOR EACH ROW trigger returns NULL, the UPDATE will be skippe
Peter Geoghegan wrote:
> On Fri, Oct 10, 2014 at 11:30 AM, Kevin Grittner wrote:
>> That seems a lot cleaner than the proposal on the Wiki page. If we
>> go that route, it makes sense to fire the BEFORE INSERT triggers
>> before attempting the insert and then fire BEFORE UPDATE triggers
>> befo
On Fri, Oct 10, 2014 at 11:44 AM, Kevin Grittner wrote:
> We've all read that, and your repeated arguments for that point of
> view. We disagree and have said why. What in that is not a
> realistic conversation?
Because, as I've said many times, there are problems with naming
columns directly t
On Fri, Oct 10, 2014 at 11:38 AM, Peter Geoghegan wrote:
>> That seems a lot cleaner than the proposal on the Wiki page. If we
>> go that route, it makes sense to fire the BEFORE INSERT triggers
>> before attempting the insert and then fire BEFORE UPDATE triggers
>> before attempting the UPDATE.
Peter Geoghegan wrote:
> On Fri, Oct 10, 2014 at 11:05 AM, Robert Haas wrote:
>> I actually like this syntax reasonably well in some ways, but I don't
>> like that we're mentioning the index name, and the CONFLICTING()
>> notation is decidedly odd.
>
> People keep remarking that they don't like
On Fri, Oct 10, 2014 at 11:30 AM, Kevin Grittner wrote:
> That seems a lot cleaner than the proposal on the Wiki page. If we
> go that route, it makes sense to fire the BEFORE INSERT triggers
> before attempting the insert and then fire BEFORE UPDATE triggers
> before attempting the UPDATE. If e
Robert Haas wrote:
> Anything we do about triggers will by definition be novel. Right
> now, we have INSERT, UPDATE, and DELETE. If we add a new
> operation, whether it's called UPSERT or MERGE or FROB, [ ... ]
If we call it MERGE, then we had better follow the rules the
standard lays out for
On Fri, Oct 10, 2014 at 11:05 AM, Robert Haas wrote:
> Anything we do about triggers will by definition be novel. Right now,
> we have INSERT, UPDATE, and DELETE. If we add a new operation,
> whether it's called UPSERT or MERGE or FROB, or if we add a flag to
> insert that makes it possibly do s
On Wed, Oct 8, 2014 at 5:01 PM, Kevin Grittner wrote:
> Peter Geoghegan wrote:
>> On Mon, Oct 6, 2014 at 8:35 AM, Robert Haas wrote:
>>> I think the problem is that it's not possible to respect the "usual
>>> guarantees" even at READ COMMITTED level when performing an INSERT OR
>>> UPDATE operat
On 10/10/14 12:38, Jim Nasby wrote:
On 10/8/14, 5:51 PM, Peter Geoghegan wrote:
On Wed, Oct 8, 2014 at 2:01 PM, Kevin Grittner
wrote:
>Although the last go-around does suggest that there is at least one
>point of difference on the semantics. You seem to want to fire the
>BEFORE INSERT trigger
On 10/8/14, 5:51 PM, Peter Geoghegan wrote:
On Wed, Oct 8, 2014 at 2:01 PM, Kevin Grittner wrote:
>Although the last go-around does suggest that there is at least one
>point of difference on the semantics. You seem to want to fire the
>BEFORE INSERT triggers before determining whether this wil
On Wed, Oct 8, 2014 at 7:04 PM, Marti Raudsepp wrote:
> Oops, I missed "for each row" here.
Note that I have an open item for what to do about statement level
triggers here: https://wiki.postgresql.org/wiki/UPSERT
I'm not satisfied with the current behavior. It needs more thought.
But I thin
On Thu, Oct 9, 2014 at 4:56 AM, Marti Raudsepp wrote:
> create trigger ev1 before insert on evt_type execute procedure ins();
> create trigger ev2 before update on evt_type execute procedure upd();
> create trigger ev3 after insert on evt_type execute procedure ins();
> create trigger ev4 after up
On Thu, Oct 9, 2014 at 4:25 AM, Peter Geoghegan wrote:
> On Wed, Oct 8, 2014 at 6:12 PM, Marti Raudsepp wrote:
>> Skipping
>> BEFORE UPDATE entirely seems to violate POLA.
> Good thing that the patch doesn't do that, then. I clearly documented
> this in a few places, including:
> http://postgres
On Wed, Oct 8, 2014 at 6:12 PM, Marti Raudsepp wrote:
> Oh, one more consideration: I believe you will run into the same issue
> if you want to implement BEFORE UPDATE triggers in any form. Skipping
> BEFORE UPDATE entirely seems to violate POLA.
Good thing that the patch doesn't do that, then. I
On Thu, Oct 9, 2014 at 3:47 AM, Peter Geoghegan wrote:
> On Wed, Oct 8, 2014 at 5:37 PM, Marti Raudsepp wrote:
>> Only in case the trigger changes *key* columns necessary for atomicity
>> (i.e. from the WITHIN index). Other columns are fair game. The
>> restriction seems justifiable to me: it's u
On Wed, Oct 8, 2014 at 5:37 PM, Marti Raudsepp wrote:
> Only in case the trigger changes *key* columns necessary for atomicity
> (i.e. from the WITHIN index). Other columns are fair game. The
> restriction seems justifiable to me: it's unreasonable to be atomic
> with respect to values that change
On Thu, Oct 9, 2014 at 2:46 AM, Peter Geoghegan wrote:
>> Indeed, the current behavior breaks even the canonical "keep track of
>> how many posts are in a thread" trigger example
> use an AFTER trigger for this kind of thing, and all of these
> issues go away.
In the latest patches from CommitFes
On Wed, Oct 8, 2014 at 4:30 PM, Marti Raudsepp wrote:
> On Thu, Oct 9, 2014 at 1:51 AM, Peter Geoghegan wrote:
>> On Wed, Oct 8, 2014 at 2:01 PM, Kevin Grittner wrote:
>>> Although the last go-around does suggest that there is at least one
>>> point of difference on the semantics. You seem to w
On Thu, Oct 9, 2014 at 1:51 AM, Peter Geoghegan wrote:
> On Wed, Oct 8, 2014 at 2:01 PM, Kevin Grittner wrote:
>> Although the last go-around does suggest that there is at least one
>> point of difference on the semantics. You seem to want to fire the
>> BEFORE INSERT triggers before determining
On Wed, Oct 8, 2014 at 2:01 PM, Kevin Grittner wrote:
> Although the last go-around does suggest that there is at least one
> point of difference on the semantics. You seem to want to fire the
> BEFORE INSERT triggers before determining whether this will be an
> INSERT or an UPDATE. That seems l
Peter Geoghegan wrote:
> On Mon, Oct 6, 2014 at 8:35 AM, Robert Haas wrote:
>> I think the problem is that it's not possible to respect the "usual
>> guarantees" even at READ COMMITTED level when performing an INSERT OR
>> UPDATE operation (however spelled). You may find that there's a tuple
>>
On Mon, Oct 6, 2014 at 8:35 AM, Robert Haas wrote:
> I think the problem is that it's not possible to respect the "usual
> guarantees" even at READ COMMITTED level when performing an INSERT OR
> UPDATE operation (however spelled). You may find that there's a tuple
> with the same PK which is comm
On Mon, Oct 6, 2014 at 8:35 AM, Robert Haas wrote:
> I think the problem is that it's not possible to respect the "usual
> guarantees" even at READ COMMITTED level when performing an INSERT OR
> UPDATE operation (however spelled).
That's definitely the main problem. Also, there could be garden
va
On Fri, Oct 3, 2014 at 4:16 PM, Kevin Grittner wrote:
>> ... the SQL standard does not require that MERGE be atomic in the
>> sense of atomically providing either an INSERT or UPDATE, ...
>
> My understanding is that the standard logically requires (without
> concern for implementation details) th
On Fri, Oct 3, 2014 at 3:42 PM, Peter Geoghegan wrote:
>> We routinely pick very
>> different plans based on the presence or absence of an index, and
>> we use special snapshots in the course of executing many DML
>> statements (if FK triggers are fired)
Apart from FK snapshots, we also use dirty
On Fri, Oct 3, 2014 at 3:42 PM, Peter Geoghegan wrote:
> Yes, it is. I am opposed to using the MERGE syntax for this *because*
> MERGE is useful (independently useful, when done properly), not
> because it is not useful.
As I've mentioned, there is also the practical argument: No one else
does t
On Fri, Oct 3, 2014 at 2:44 PM, Kevin Grittner wrote:
> I've never seen "atomic" used to mean "you never get an error"
> before.
> When you are saying "atomic" you mean something quite different.
Perhaps I should have been more careful on that point. Just to be
crystal clear: I don't intend that
Peter Geoghegan wrote:
> On Fri, Oct 3, 2014 at 1:16 PM, Kevin Grittner wrote:
>> I'm having trouble parsing what this statement means:
>>
>>> ... the SQL standard does not require that MERGE be atomic in
>>> the sense of atomically providing either an INSERT or UPDATE, ...
> ... always getting
On Fri, Oct 3, 2014 at 1:16 PM, Kevin Grittner wrote:
> I have two questions I hope you can clarify. I'm having trouble
> parsing what this statement means:
>
>> ... the SQL standard does not require that MERGE be atomic in the
>> sense of atomically providing either an INSERT or UPDATE, ...
>
>
Peter Geoghegan wrote:
> The page is: https://wiki.postgresql.org/wiki/UPSERT
Thanks!
I have two questions I hope you can clarify. I'm having trouble
parsing what this statement means:
> ... the SQL standard does not require that MERGE be atomic in the
> sense of atomically providing eithe
On Thu, Oct 2, 2014 at 12:54 AM, Peter Geoghegan wrote:
> I've started off by adding varied examples of the use of the existing
> proposed syntax. I'll expand on this soon.
I spent some time today expanding on the details, and commenting on
the issues around the custom syntax (exactly what it doe
On Thu, Oct 2, 2014 at 12:07 AM, Heikki Linnakangas
wrote:
> Could you write down all of the discussed syntaxes, using a similar notation
> we use in the manual, with examples on how to use them? And some examples on
> what is possible with some syntaxes, and not with others? That would make it
>
On 10/02/2014 02:52 AM, Peter Geoghegan wrote:
Having been surprisingly successful at advancing our understanding of
arguments for and against various approaches to "value locking", I
decided to try the same thing out elsewhere. I have created a
general-purpose UPSERT wiki page.
The page is: htt
On 10/02/2014 07:52 AM, Peter Geoghegan wrote:
> Having been surprisingly successful at advancing our understanding of
> arguments for and against various approaches to "value locking", I
> decided to try the same thing out elsewhere. I have created a
> general-purpose UPSERT wiki page.
>
> The pa
Having been surprisingly successful at advancing our understanding of
arguments for and against various approaches to "value locking", I
decided to try the same thing out elsewhere. I have created a
general-purpose UPSERT wiki page.
The page is: https://wiki.postgresql.org/wiki/UPSERT
Right now,
On Sun, Mar 04, 2007 at 02:55:47PM +0200, Hannu Krosing wrote:
> Is'nt the standard way of doing it thus:
>
> UPDATE
> IF NOT FOUND THEN
> INSERT
> IF DUPLICATE KEY THEN
> UPDATE
> END IF
> END IF
>
> At least this is how UPSERT is usually done in plpgsql
Well, you need to loop, becau
Bruno Wolff III wrote:
On Sun, Mar 04, 2007 at 14:55:47 +0200,
Hannu Krosing <[EMAIL PROTECTED]> wrote:
UPDATE
IF NOT FOUND THEN
INSERT
IF DUPLICATE KEY THEN
UPDATE
END IF
END IF
I believe it is possible for the above to fail. For example another
transaction could create a matching
Ühel kenal päeval, P, 2007-03-04 kell 07:46, kirjutas Bruno Wolff III:
> On Sun, Mar 04, 2007 at 14:55:47 +0200,
> Hannu Krosing <[EMAIL PROTECTED]> wrote:
> >
> > UPDATE
> > IF NOT FOUND THEN
> > INSERT
> > IF DUPLICATE KEY THEN
> > UPDATE
> > END IF
> > END IF
>
> I believe it is p
On Sun, Mar 04, 2007 at 14:55:47 +0200,
Hannu Krosing <[EMAIL PROTECTED]> wrote:
>
> UPDATE
> IF NOT FOUND THEN
> INSERT
> IF DUPLICATE KEY THEN
> UPDATE
> END IF
> END IF
I believe it is possible for the above to fail. For example another
transaction could create a matching record b
Ühel kenal päeval, R, 2007-03-02 kell 10:13, kirjutas Tom Lane:
> Andrew Dunstan <[EMAIL PROTECTED]> writes:
> > My instinct would be to follow your first strategy, i.e. detect which
> > path is needed rather than try one and then if it fails do the other.
>
> The very first thing you need to thi
Couple notes:
(1) Upsert is not just a desire of MySQL users. I just spec'd a major
proprietary-database replacement project at a fortune 500 where they want an
Upsert and are unhappy that PostgreSQL doesn't have it. Unfortunately, they
don't want to spring for development funds :-(
(2) Doin
Tom Lane wrote:
Bricklen Anderson <[EMAIL PROTECTED]> writes:
http://archives.postgresql.org/pgsql-hackers/2004-05/thrd5.php#00497
There is a thread there entitled "Adding MERGE to the TODO list"
The more interesting discussion is the one that got it taken off TODO again,
from Nov 2005. Try t
On Fri, 2007-03-02 at 13:19 -0500, Tom Lane wrote:
> "Simon Riggs" <[EMAIL PROTECTED]> writes:
> > Seems like we should try to locate a row first, then INSERT if we cannot
> > find one. That's slower on INSERT but more balanced overall
>
> Except it still has the race condition.
I'm not saying it
Bricklen Anderson <[EMAIL PROTECTED]> writes:
> http://archives.postgresql.org/pgsql-hackers/2004-05/thrd5.php#00497
> There is a thread there entitled "Adding MERGE to the TODO list"
The more interesting discussion is the one that got it taken off TODO again,
from Nov 2005. Try these threads:
ht
Simon Riggs wrote:
I'm a bit surprised the TODO didn't mention the MERGE statement, which
is the SQL:2003 syntax for specifying this as an atomic statement.
http://archives.postgresql.org/pgsql-hackers/2004-05/thrd5.php#00497
There is a thread there entitled "Adding MERGE to the TODO list"
--
"Simon Riggs" <[EMAIL PROTECTED]> writes:
> Seems like we should try to locate a row first, then INSERT if we cannot
> find one. That's slower on INSERT but more balanced overall
Except it still has the race condition.
> I'm a bit surprised the TODO didn't mention the MERGE statement, which
> is
On Fri, 2007-03-02 at 15:41 +, Heikki Linnakangas wrote:
> Tom Lane wrote:
> > Andrew Dunstan <[EMAIL PROTECTED]> writes:
> >> My instinct would be to follow your first strategy, i.e. detect which
> >> path is needed rather than try one and then if it fails do the other.
> >
> > The very firs
Gregory Stark wrote:
"Florian G. Pflug" <[EMAIL PROTECTED]> writes:
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
This allow to make an insert, and if the key is already there to modify the
value depending on the current one.
May this could be generalized to a generic
Tom Lane wrote:
Andrew Dunstan <[EMAIL PROTECTED]> writes:
My instinct would be to follow your first strategy, i.e. detect which
path is needed rather than try one and then if it fails do the other.
The very first thing you need to think about is how to solve the race
condition problem, ie, tw
Andrew Dunstan <[EMAIL PROTECTED]> writes:
> My instinct would be to follow your first strategy, i.e. detect which
> path is needed rather than try one and then if it fails do the other.
The very first thing you need to think about is how to solve the race
condition problem, ie, two backends conc
"Florian G. Pflug" <[EMAIL PROTECTED]> writes:
>>> INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
>>> This allow to make an insert, and if the key is already there to modify the
>>> value depending on the current one.
>
> May this could be generalized to a generic " on do
Andrew Dunstan wrote:
Jonathan Scher wrote:
Hello,
I'd like to work on TODO item:
> Add REPLACE or UPSERT command that does UPDATE, or on failure, INSERT
could you please tell me if I'm going in the right way?
There are some different syntaxes possible, but MySQL has an
interesting one here:
Jonathan Scher wrote:
Hello,
I'd like to work on TODO item:
> Add REPLACE or UPSERT command that does UPDATE, or on failure, INSERT
could you please tell me if I'm going in the right way?
There are some different syntaxes possible, but MySQL has an
interesting one here:
http://dev.mysql.com/
Hello,
I'd like to work on TODO item:
> Add REPLACE or UPSERT command that does UPDATE, or on failure, INSERT
could you please tell me if I'm going in the right way?
There are some different syntaxes possible, but MySQL has an interesting
one here:
http://dev.mysql.com/doc/refman/5.0/en/inser
98 matches
Mail list logo
