Re: Replaceing records - Mailing list pgsql-general

[rant mode]
I have to answer this: I'm not trying to use a non-standard feature, I
try to solve a problem. Namely to be able to try to insert and on
failure continue the transaction. This is by no means a non-standard
feature.
AFAIKT the standard says nothing about rolling back automatically a
transaction on error, it just says that YOU should be able to roll it
back or commit it, and then all or nothing of the changes should be
executed.
The application design can be "fixed", but that means ugly workarounds.
In my case a simple fix would be to always insert all the possible
records before any update would happen, but that would bloat the table
10-fold - I think you agree this is unacceptable.
Please understand me: I'm not after pissing off the postgres developers
by telling Postgres is not up to it, I try to insist that nested
transactions are a very important feature, which can solve lots of
problems which apparently might have nothing to do with nested
transactions.

Cheers,
Csaba.


On Fri, 2003-09-05 at 04:38, Jan Wieck wrote:
> Whatever you guy's try or suggest, it's doomed to suffer.
>
> The whole problem stems from using a non-standard feature. And in my
> opinion MySQL's "REPLACE INTO" is less a feature or extension to the
> standard than more another stupid and lesser thought through addition of
> apparently speed gaining crap at the cost of proper design.
>
> One possible reason why this sort of "feature" was left out of the SQL
> standard could be that the source of an ID, that is supposed to be
> unique in the end, should by default ensure it's uniqueness. Defining a
> column UNIQUE is a last line of defense, and aborted actions because of
> constraint violation should be the exception, not the normal mode of
> operation. If it's the DB to ensure uniqueness, it has to generate the
> ID and one can use a sequence. If it's the application to generate it,
> the application should know if this is an INSERT or an UPDATE.
>
> Wherever one is using this "REPLACE INTO" language violation, the client
> application or even something in front of it is generating ID's but it's
> not sure if it is sending down a new or existing one. The real question
> is "why is this piece of garbage unable to tell the ID is newly created
> or has to exist already?"
>
> I don't think there should be a way to subsitute this. Fix the
> application design instead.
>
>
> Jan