Maxim Sobolev wrote:
> Jan Janak wrote:
>> * No support for INSERT OR UPDATE (or REPLACE in MySQL). PostgreSQL does
>> not seem to support "insert or update" type of queries, so I will have
>> to re-implement it using transactions somehow. I am not yet sure
>> exactly how, so suggestions/patches/code are welcome.
>
> Well, I think you can simply run DELETE followed by INSERT within
> transaction block to emulate that. The outside world will not see any
> changes until transaction completes, which should provide the same
> functionality as "INSERT OR UPDATE", i.e.:
>
> BEGIN;
> DELETE FROM location WHERE ... LIMIT 1;
> INSERT INTO location ...;
> END;
>
> If INSERT fails there will be no changes to the database.
Unfortunately it is more complex than that. REPLACE only deletes an
old row if it stands in the way of the new row. In other words an old
row will only be deleted if the new insert generates a primary key or
unique index violation.
The code above would delete the old row even if it does not stand in
the way of the new insert. This could happen in acc or missed_calls
tables, for example, which has been designed without any such
constrains to make sure that inserts never fail.
Jan.
_______________________________________________
Serdev mailing list
[email protected]
http://lists.iptel.org/mailman/listinfo/serdev
