How to insert on duplicate key?
Hi all,
I have a table like this,
CREATE TABLE mytable(
a varchar(40),
b text,
c text,
PRIMARY KEY (a, b)
);
What I want to do is:
insert a record into a table, and when the record already
exists(according to the primary key), update it.
I know that there is a ON DUPLICATE clause with MySQL, so I'm wondering is
there a quick and clean way to do this in PostgreSQL ?
I have googled and currently the only way I can find is do query first and
then update or insert.
Thanks a lot.
Regards,
On Dec 24, 2007, at 22:03 , fdu.xiaojf@gmail.com wrote:
I have googled and currently the only way I can find is do query
first and then update or insert.
Or alternatively, UPDATE and see if you've affected any rows. If not,
insert.
Michael Glaesemann
grzm seespotcode net
Michael Glaesemann wrote:
On Dec 24, 2007, at 22:03 , fdu.xiaojf@gmail.com wrote:
I have googled and currently the only way I can find is do query first
and then update or insert.Or alternatively, UPDATE and see if you've affected any rows. If not,
insert.Michael Glaesemann
grzm seespotcode net
Thanks for your quick reply!
Is the update and insert method quick?
I have tried the query and update/insert way, and it was very slow when more
than 1 million records have been inserted. (I have more than 20 million
records to insert.)
Thanks again!
Xiao Jianfeng
On Tue, 25 Dec 2007, fdu.xiaojf@gmail.com wrote:
insert a record into a table, and when the record already
exists(according to the primary key), update it.
There is an example that does exactly that, 37-1, in the documentation at
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html
It actually does the update first and only if that fails does the insert,
which avoids the whole duplicate key issue altogether.
I have tried the query and update/insert way, and it was very slow when
more than 1 million records have been inserted. (I have more than 20
million records to insert.)
This may be better because it isn't doing the query first. You may
discover that you need to aggressively run one of the VACUUM processes
(I'd guess regular and ANALYZE but not FULL) in order to keep performance
steady as the number of records grows. Anytime you update a row, that
becomes a dead row that's still taking up space, and if you do a lot of
those they get in the way of finding the rows that are still live. Take a
look at
http://www.postgresql.org/docs/current/interactive/routine-vacuuming.html
to get an idea of the process.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
On Tue, 25 Dec 2007, fdu.xiaojf@gmail.com wrote:
insert a record into a table, and when the record already
exists(according to the primary key), update it.
There is an example that does exactly that, 37-1, in the documentation at
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html
It actually does the update first and only if that fails does the insert,
which avoids the whole duplicate key issue altogether.
I have tried the query and update/insert way, and it was very slow when
more than 1 million records have been inserted. (I have more than 20
million records to insert.)
This may be better because it isn't doing the query first. You may
discover that you need to aggressively run one of the VACUUM processes
(I'd guess regular and ANALYZE but not FULL) in order to keep performance
steady as the number of records grows. Anytime you update a row, that
becomes a dead row that's still taking up space, and if you do a lot of
those they get in the way of finding the rows that are still live. Take a
look at
http://www.postgresql.org/docs/current/interactive/routine-vacuuming.html
to get an idea of the process.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On Dec 24, 2007, at 11:15 PM, Greg Smith wrote:
This may be better because it isn't doing the query first. You may
discover that you need to aggressively run one of the VACUUM
processes (I'd guess regular and ANALYZE but not FULL) in order to
keep performance steady as the number of records grows. Anytime you
update a row, that becomes a dead row that's still taking up space,
and if you do a lot of those they get in the way of finding the rows
that are still live. Take a look at http://www.postgresql.org/docs/current/interactive/routine-vacuuming.html
to get an idea of the process.
Whoa. I am going to have to dig into the implementation. What is
wrong with update in place, concurrency issues? The dead row
presumably is no longer indexed, right? Since it is known to be dead
is it automatically removed when there are no live transaction that
reference or may reference it and its data page space marked available
for new rows? If not, why not? I'm dredging my mind for stuff from
my RDBMS implementation grad course a very long time ago. I would
imagine that vacuuming often in a huge insert update would be a
pretty poor performer depending on implementation. How is this
implemented? Why would it be heavy IO if a list of pointers
effectively is being kept to the dead rows to simply be added to the
free list? What else is vacuum doing? Lookup implemented removal
from indices, something else?
- samantha
On Thursday 27 December 2007 12:23, Samantha Atkins wrote:
On Dec 24, 2007, at 11:15 PM, Greg Smith wrote:
This may be better because it isn't doing the query first. You may
discover that you need to aggressively run one of the VACUUM
processes (I'd guess regular and ANALYZE but not FULL) in order to
keep performance steady as the number of records grows. Anytime you
update a row, that becomes a dead row that's still taking up space,
and if you do a lot of those they get in the way of finding the rows
that are still live. Take a look at
http://www.postgresql.org/docs/current/interactive/routine-vacuuming.html
to get an idea of the process.Whoa. I am going to have to dig into the implementation. What is
wrong with update in place, concurrency issues? The dead row
presumably is no longer indexed, right?
At the time your transaction commits, it cannot update in place, since someone
else may be looking at the old version of the row in the middle of thier
transaction, so you need two copies. Even after updated you still need some
pointer in the index for the old version of the row, in case it its
referenced again.
Since it is known to be dead
is it automatically removed when there are no live transaction that
reference or may reference it and its data page space marked available
for new rows? If not, why not? I'm dredging my mind for stuff from
my RDBMS implementation grad course a very long time ago.
The problem is you have determined in your mind that a row is "known dead"
without explination of how that would actually be determined. A given
transaction doesn't have a way to determine if there are live transaction
looking at the row, that would require quite a bit of knowledge about what
else is occuring in the system to be able to determine that. That level of
knowledge/complexity is what vacuum takes care of.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
On 12/28/07, Robert Treat <xzilla@users.sourceforge.net> wrote:
On Thursday 27 December 2007 12:23, Samantha Atkins wrote:
Since it is known to be dead
is it automatically removed when there are no live transaction that
reference or may reference it and its data page space marked available
for new rows? If not, why not? I'm dredging my mind for stuff from
my RDBMS implementation grad course a very long time ago.
The problem is you have determined in your mind that a row is "known dead"
without explination of how that would actually be determined. A given
transaction doesn't have a way to determine if there are live transaction
looking at the row, that would require quite a bit of knowledge about what
else is occuring in the system to be able to determine that. That level of
knowledge/complexity is what vacuum takes care of.
If you're familiar with the intricacies of the garbage collection vs
realloc/free debate surrounding programming languages, especially in
multithreaded environments, this is basically the same thing applied
to disk storage. PostgreSQL implements garbage collection. Its methods
for doing it automatically are still being refined, hence the advice
on manual tuning for specific workloads.
On Fri, 28 Dec 2007, Robert Treat wrote:
A given transaction doesn't have a way to determine if there are live
transaction looking at the row, that would require quite a bit of
knowledge about what else is occuring in the system to be able to
determine that. That level of knowledge/complexity is what vacuum takes
care of.
One reason it doesn't happen automatically is that it would slow down the
client doing the update. Sorting through to figure out which rows can be
seen by which clients is better done later for a number of reasons; some
samples:
-It's more likely there won't be any transactions still referencing the
original row as time moves forward. If you do it the instant the row is
dead, odds are higher there's still somebody using the original one and
you can't prune it yet anyway.
-It's more efficicent to sort through a bunch of these in bulk than to
do them one at a time.
-You need to have a similar vacuum process happening regularly anyway to
analyze your tables and keep statistics about them up to date, so might as
well do both of those things at once.
The downside is that vacuum can have a relatively high impact on the
system, but the answer there is to do it more often so that any individual
vacuum is less difficult.
It'a also worth mentioning that some of this update row reuse happens more
automatically in V8.3 with a new feature called HOT, so in some cases this
particular issue has already has a workaround everyone can get in the near
future.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD