Maxim Sobolev wrote:
> Jan Janak wrote:
>> 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.
>
> Well I think the whole idea "delete an old row if it stands in the way
> based on index" seems to be very dangerous. What if the new row "stands
> in the way" of several rows (tables can have multiple indexes and/or
> indexes that involve multiple fields)? Should all of them be deleted?
> IMHO the code should explicitly specify which fields should match in
> order to warrant replacing old record with the new one, in which case
> the DELETE/INSERT bundle should work just fine.
Yes, this is how REPLACE works. It guarantees that the new data will be
written into the database by removing all rows that stand in its way.
There seems to be a new construct called MERGE in one of the newer
revisions of the SQL standard but PostgreSQL does not support it yet.
I found two workarounds that people seem to use, one of them uses
transactions with savepoints:
BEGIN;
-- other operations
SAVEPOINT sp1;
INSERT INTO wines VALUES('Chateau Lafite 2003', '24');
-- Assume the above fails because of a unique key violation,
-- so now we issue these commands:
ROLLBACK TO sp1;
UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite
2003';
-- continue with other operations, and eventually
COMMIT;
And another, more complex one, implemented in pl/sql.
If they don't work then I will use the delete and insert approach for
usrloc and treat other modules such as acc_db separately (modifying
them to do pure insert).
Jan.
_______________________________________________
Serdev mailing list
[email protected]
http://lists.iptel.org/mailman/listinfo/serdev
