Re: Replaceing records - Mailing list pgsql-general

    Fine says he seeing both sides and wanting to back both sides.

    REPLACE INTO throws away data with out the user knowing that they
have ditched the data. This means it has side effects. Standard
Programming Theory says that functions should not have unintended side
REPLACE INTO is in-fact a

DELETE followed by a INSERT

    Its also unclear what replace should do with missing fields
1. Fill them in with the defaults.
2. Leave them alone.

    If 1 its very dangerous and if 2 you should know what you are
changing anyway and the very least  it should return "UPDATE" or "INSERT"
depending on what "REPLACE" actually did!

    On the other hand Nesting are a good idea. There is one problem
however. When do you impose referential integrity for deferred checks. at
the last commit. or do you need a check references command.

    Its not the point that a UPDATE on most databases infers a COPY,
CHANGE COPY, DELETE steps so you can roll back if nessessary.

    Replace also needs to know the table stucture to work Update,
Insert and Delete don't they only need to check the constraints.

    As I'm sure I've said before SQL has huge holes and inconsistencies
and needs a complete re-write. its like VHS-Video not the best just whats
been sold to everyone.

Peter Childs


On 5 Sep 2003, Csaba Nagy wrote:

> [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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>