pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedautomatic update or insert
6+ messages / 6 participants
[nested] [flat]
* automatic update or insert
@ 2005-10-20 06:36 tobbe <tobbe@tripnet.se>
2005-10-25 08:07 ` Re: automatic update or insert PFC <lists@boutiquenumerique.com>
2005-10-25 14:38 ` Re: automatic update or insert Daryl Richter <daryl@brandywine.com>
2005-10-25 15:26 ` Re: automatic update or insert codeWarrior <gpatnude@hotmail.com>
2005-10-26 07:53 ` Re: automatic update or insert Patrick JACQUOT <patrick.jacquot@anpe.fr>
0 siblings, 4 replies; 6+ messages in thread
From: tobbe @ 2005-10-20 06:36 UTC (permalink / raw)
To: pgsql-sql
Hi.
I have a little problem.
In a system of mine i need to insert records into table [tbStat], and
if the records exist i need to update them instead and increase a
column [cQuantity] for every update.
I.e. the first insert sets cQuantity to 1, and for every other run
cQuantity is increased.
Currently i have implemented this as a stored procedure in the plpgsql
language. This means that in my stored procedure i first do a select to
find out if the row exists or not, then i do a insert or update
depending if the row existed.
Unfortunately, stored procedures seems awfully slow. And i need the
application to go faster.
One solution could be to implement the stored procedure in my program
instead. I think that this will be atleast 50% faster than my stored
procedure, so that would be ok.
However, this has made me thinking. Couldn't this be done directly in
SQL?
Brgds Robert
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: automatic update or insert
2005-10-20 06:36 automatic update or insert tobbe <tobbe@tripnet.se>
@ 2005-10-25 08:07 ` PFC <lists@boutiquenumerique.com>
3 siblings, 0 replies; 6+ messages in thread
From: PFC @ 2005-10-25 08:07 UTC (permalink / raw)
To: tobbe <tobbe@tripnet.se>; pgsql-sql
> In a system of mine i need to insert records into table [tbStat], and
> if the records exist i need to update them instead and increase a
> column [cQuantity] for every update.
>
> I.e. the first insert sets cQuantity to 1, and for every other run
> cQuantity is increased.
>
> Currently i have implemented this as a stored procedure in the plpgsql
> language. This means that in my stored procedure i first do a select to
> find out if the row exists or not, then i do a insert or update
> depending if the row existed.
There are two ways you can do this :
* If you will have more updates than inserts (ie. more items with a
quantity >1 than 1) :
UPDATE
If the update updated no rows, then INSERT
* If you have more inserts than updates (ie. more items with quantity 1
than >1) :
INSERT
if it fails due to violating the unique constraint, then UPDATE
None of these involve a SELECT. The first one is very cheap if you end up
doing more updates than inserts, because it just does the update.
You will of course need a UNIQUE index to identify your rows, and prevent
insertion of duplicates. I suppose you have this already.
There is a subtility in the second form : the INSERT will fail on
duplicate key, so you have to either rollback the transaction if you send
the queries raw from your app, or catch the exception in your plpgsql
function.
Also a race condition might exist if someone deletes a row in-between, or
the first procedure is executed twice at the same time by different
threads. Be prepared to retry your transaction.
Something like the ON DUPLICATE KEY UPDATE in MySQL would be nice to have.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: automatic update or insert
2005-10-20 06:36 automatic update or insert tobbe <tobbe@tripnet.se>
@ 2005-10-25 14:38 ` Daryl Richter <daryl@brandywine.com>
3 siblings, 0 replies; 6+ messages in thread
From: Daryl Richter @ 2005-10-25 14:38 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
tobbe wrote:
> Hi.
>
> I have a little problem.
>
> In a system of mine i need to insert records into table [tbStat], and
> if the records exist i need to update them instead and increase a
> column [cQuantity] for every update.
>
> I.e. the first insert sets cQuantity to 1, and for every other run
> cQuantity is increased.
>
Regardless of how you implemented it, this seems unwise. You can never
know, nor validate, that this quantity is definitely correct. Why can't
you just insert another row and then count them?
If this is a transient value you might be ok, but I generally wouldn't
put it in a DB in that case anyway...
> Currently i have implemented this as a stored procedure in the plpgsql
> language. This means that in my stored procedure i first do a select to
> find out if the row exists or not, then i do a insert or update
> depending if the row existed.
>
> Unfortunately, stored procedures seems awfully slow. And i need the
> application to go faster.
>
> One solution could be to implement the stored procedure in my program
> instead. I think that this will be atleast 50% faster than my stored
> procedure, so that would be ok.
>
> However, this has made me thinking. Couldn't this be done directly in
> SQL?
>
>
> Brgds Robert
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
--
Daryl Richter
Platform Author & Director of Technology
(( Brandywine Asset Management )
( "Expanding the Science of Global Investing" )
( http://www.brandywine.com ))
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: automatic update or insert
2005-10-20 06:36 automatic update or insert tobbe <tobbe@tripnet.se>
@ 2005-10-25 15:26 ` codeWarrior <gpatnude@hotmail.com>
3 siblings, 0 replies; 6+ messages in thread
From: codeWarrior @ 2005-10-25 15:26 UTC (permalink / raw)
To: pgsql-sql
The following trigger procedure works for me.... you'd need to adjust this
to manipulate YOUR table schema:
DROP FUNCTION dmc_comp_plan_duplicates() CASCADE;
CREATE OR REPLACE FUNCTION dmc_comp_plan_duplicates() RETURNS "trigger" AS
$BODY$
DECLARE did integer;
BEGIN
SELECT COALESCE(id, 0) AS id FROM dmc_compensation_plan_quota
WHERE dmc_compensation_plan = NEW.dmc_compensation_plan
AND dmc_quota_item = NEW.dmc_quota_item
INTO did;
RAISE NOTICE 'DID: %', did;
IF ((did = 0) OR (did IS NULL)) THEN
RAISE NOTICE 'INSERT: DID: %', did;
-- INSERT INTO dmc_compensation_plan_quota (dmc_compensation_plan,
dmc_quota_item) VALUES (NEW.dmc_compensation_plan, NEW.dmc_quota_item);
RETURN NEW;
ELSE
RAISE WARNING 'UPDATE: DID: %', did;
UPDATE dmc_compensation_plan_quota SET active_flag = TRUE WHERE id = did;
RETURN NULL;
END IF;
-- DEFAULT = DO NOTHING...
RETURN NULL;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION dmc_comp_plan_duplicates() OWNER TO datamosaics;
CREATE TRIGGER dmc_comp_plan_duplicates BEFORE INSERT ON
dmc_compensation_plan_quota FOR EACH ROW EXECUTE PROCEDURE
dmc_comp_plan_duplicates();
"tobbe" <tobbe@tripnet.se> wrote in message
news:1129790184.351579.302550@g47g2000cwa.googlegroups.com...
> Hi.
>
> I have a little problem.
>
> In a system of mine i need to insert records into table [tbStat], and
> if the records exist i need to update them instead and increase a
> column [cQuantity] for every update.
>
> I.e. the first insert sets cQuantity to 1, and for every other run
> cQuantity is increased.
>
> Currently i have implemented this as a stored procedure in the plpgsql
> language. This means that in my stored procedure i first do a select to
> find out if the row exists or not, then i do a insert or update
> depending if the row existed.
>
> Unfortunately, stored procedures seems awfully slow. And i need the
> application to go faster.
>
> One solution could be to implement the stored procedure in my program
> instead. I think that this will be atleast 50% faster than my stored
> procedure, so that would be ok.
>
> However, this has made me thinking. Couldn't this be done directly in
> SQL?
>
>
> Brgds Robert
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: automatic update or insert
2005-10-20 06:36 automatic update or insert tobbe <tobbe@tripnet.se>
@ 2005-10-26 07:53 ` Patrick JACQUOT <patrick.jacquot@anpe.fr>
3 siblings, 0 replies; 6+ messages in thread
From: Patrick JACQUOT @ 2005-10-26 07:53 UTC (permalink / raw)
To: tobbe <tobbe@tripnet.se>; +Cc: pgsql-sql
tobbe wrote:
>Hi.
>
>I have a little problem.
>
>In a system of mine i need to insert records into table [tbStat], and
>if the records exist i need to update them instead and increase a
>column [cQuantity] for every update.
>
>I.e. the first insert sets cQuantity to 1, and for every other run
>cQuantity is increased.
>
>Currently i have implemented this as a stored procedure in the plpgsql
>language. This means that in my stored procedure i first do a select to
>find out if the row exists or not, then i do a insert or update
>depending if the row existed.
>
>Unfortunately, stored procedures seems awfully slow. And i need the
>application to go faster.
>
>One solution could be to implement the stored procedure in my program
>instead. I think that this will be atleast 50% faster than my stored
>procedure, so that would be ok.
>
>However, this has made me thinking. Couldn't this be done directly in
>SQL?
>
>
>Brgds Robert
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
>
Maybe would it be better to insert always, and to use grouping and
summation when using the table. That would enable you to preserve the
history of events.
That's how I almost always work
hth
P.Jacquot
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: automatic update or insert
@ 2005-10-25 02:43 Stewart Ben (RBAU/EQS4) * <Ben.Stewart@au.bosch.com>
0 siblings, 0 replies; 6+ messages in thread
From: Stewart Ben (RBAU/EQS4) * @ 2005-10-25 02:43 UTC (permalink / raw)
To: pgsql-sql
> Currently i have implemented this as a stored procedure in the plpgsql
> language. This means that in my stored procedure i first do a
> select to
> find out if the row exists or not, then i do a insert or update
> depending if the row existed.
>
> Unfortunately, stored procedures seems awfully slow. And i need the
> application to go faster.
Do you have indexes on the columns being looked up? Make sure you create
the index then ANALYZE.
> One solution could be to implement the stored procedure in my program
> instead. I think that this will be atleast 50% faster than my stored
> procedure, so that would be ok.
>
> However, this has made me thinking. Couldn't this be done directly in
> SQL?
You could probably implement this as a trigger on the table for INSERT.
Have a squiz through the documentation on triggers.
Best regards,
Ben Stewart
--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Services, Student Software Engineer (RBAU/EQS4)
Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA
mailto:ben.stewart@au.bosch.com
http://www.bosch.com.au/
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-10-26 07:53 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-20 06:36 automatic update or insert tobbe <tobbe@tripnet.se>
2005-10-25 08:07 ` PFC <lists@boutiquenumerique.com>
2005-10-25 14:38 ` Daryl Richter <daryl@brandywine.com>
2005-10-25 15:26 ` codeWarrior <gpatnude@hotmail.com>
2005-10-26 07:53 ` Patrick JACQUOT <patrick.jacquot@anpe.fr>
2005-10-25 02:43 Re: automatic update or insert Stewart Ben (RBAU/EQS4) * <Ben.Stewart@au.bosch.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox