At 06:29 AM 10/30/2005 -0800, David Fetter wrote:
>On Fri, Oct 28, 2005 at 09:57:03PM -0400, blackwater dev wrote:
> > In MySQL, I can use the replace statement which either updates the
> > data there or inserts it. Is there a comporable syntax to use in
> > postgreSQL?
>
>Not really, but here's an example which doesn't have the brokenness of
>MySQL's REPLACE INTO and doesn't have the race conditions that some
>others' proposals have.
>
>http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
Erm, doesn't it have the same race conditions? It's just fine as long as
you have an appropriate uniqueness constraint.
If you have the appropriate uniqueness constraint, you'll be fine whatever
you do as long as you're not doing something too stupid.
If it is possible to lock on something that already exists or has "yet to
exist" then maybe you can do such an insert/update.
e.g. "SELECT .... FOR INSERT WHERE field1=x"
How about customizable user locking? e.g. lock string "tablename field1=x"
Anyway, I used to lock tables before doing selects before inserts/updates.
Link.