Re: [GENERAL] Revisited: Transactions, insert unique.

2000-04-28 Thread Bruce Momjian
[Charset iso-8859-1 unsupported, filtering to ASCII...] > On Thu, 27 Apr 2000, Bruce Momjian wrote: > > > Are you asking for sub-second timeout values? If so, we could add that. > > I already have a millisecond resolution for deadlock timeouts implemented. > Is that okay? Sure, that should wor

Re: [GENERAL] Revisited: Transactions, insert unique.

2000-04-27 Thread Ed Loehr
Joachim Achtzehnter wrote: > > ...It is NOT required that the outcome be > equivalent to the result that would be observed by running the > transactions in a particular order, such as in the order they were > actually started. The outcome is only required to be equivalent to some > (arbitrary) or

Re: [GENERAL] Revisited: Transactions, insert unique.

2000-04-27 Thread Ed Loehr
[EMAIL PROTECTED] wrote: > > ...the snapshot is taken from the first DML statement... That explains it. I missed that in the docs, and was mislead by the SERIALIZABLE doc sections. Regards, Ed Loehr

Re: [GENERAL] Revisited: Transactions, insert unique.

2000-04-27 Thread Bruce Momjian
> Ok so I'm biased to how MySQL does it (it's simple and has a good chance of > working well). Yes it shifts a lot to the application. But if people have > to do things like do their multiple select for updates in the right order > (to prevent deadlocks), they might as well start using something l

RE: [GENERAL] Revisited: Transactions, insert unique.

2000-04-27 Thread Hiroshi Inoue
> -Original Message- > From: Peter Eisentraut [mailto:[EMAIL PROTECTED]] > Sent: Thursday, April 27, 2000 4:46 PM > To: Hiroshi Inoue > > On Thu, 27 Apr 2000, Hiroshi Inoue wrote: > > > PostgreSQL's SERIALIZABLE isolation level would allow both inserts. > > READ COMMITED isolation level wo

RE: [GENERAL] Revisited: Transactions, insert unique.

2000-04-27 Thread Peter Eisentraut
On Thu, 27 Apr 2000, Hiroshi Inoue wrote: > PostgreSQL's SERIALIZABLE isolation level would allow both inserts. > READ COMMITED isolation level wouldn't allow A's inserts. > > As I mentioned in another posting,PostgreSQL's SERIALIZABLE > isolation level isn't completely serializable and it's sam

Re: [GENERAL] Revisited: Transactions, insert unique.

2000-04-27 Thread Joachim Achtzehnter
In a message to and Hiroshi Inoue pgsql-general, Ed Loehr wrote: > > -- Within transaction A -- > BEGIN; > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; > > -- Within transaction B -- > BEGIN; > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; > INSER

Re: [GENERAL] Revisited: Transactions, insert unique.

2000-04-26 Thread Ed Loehr
Hiroshi Inoue wrote: > > You should call SET TRANSACTION immediately after BEGIN. > Note that SET TRANSACTION .. is per transaction command. > > PostgreSQL's SERIALIZABLE isolation level would allow both inserts. > READ COMMITED isolation level wouldn't allow A's inserts. Even if I call SET aft

Re: [GENERAL] Revisited: Transactions, insert unique.

2000-04-26 Thread Lincoln Yeoh
At 11:59 AM 26-04-2000 -0500, Ed Loehr wrote: >Joachim Achtzehnter wrote: >> >> In a message to Ed Loehr and pgsql-general, Lincoln Yeoh wrote: >> > Then you do a commit on both, and you end up with two rows. >> >> This is dissapointing indeed! What this means is that Postgresql >> transactions

Re: [GENERAL] Revisited: Transactions, insert unique.

2000-04-25 Thread Joachim Achtzehnter
In a message to Ed Loehr and pgsql-general, Lincoln Yeoh wrote: > > Transaction A > begin; > insert into test (a,b) select 4,'four' from test > where not exists (select * from test where a=4); > > Transaction B > begin; > insert into test (a,b) select 4,'four' from test > where not exists (select

Re: [GENERAL] Revisited: Transactions, insert unique.

2000-04-25 Thread Joachim Achtzehnter
Today, in a message to pgsql-general, Ross J. Reedstrom wrote: > > On Mon, Apr 24, 2000 at 09:23:48PM -0700, Joachim Achtzehnter wrote: > > > > I suspect, though, that to support statement-level aborts > > more is needed than an overhaul of the error system. Statement > > level aborts are essenti

Re: [GENERAL] Revisited: Transactions, insert unique.

2000-04-25 Thread Lincoln Yeoh
At 10:58 AM 24-04-2000 -0500, Ed Loehr wrote: >Good point. And you can combine the check David suggests with the insert >statement, e.g., > > INSERT INTO mytable (id, ...) > SELECT 7, ... > FROM mytable > WHERE NOT EXISTS (SELECT * FROM mytable WHER

Re: [GENERAL] Revisited: Transactions, insert unique.

2000-04-25 Thread Ross J. Reedstrom
On Mon, Apr 24, 2000 at 09:23:48PM -0700, Joachim Achtzehnter wrote: > > This is good to hear. I suspect, though, that to support statement-level > aborts more is needed than an overhaul of the error system. Statement > level aborts are essentially a form of nested transaction, i.e. the > ability

Re: [GENERAL] Revisited: Transactions, insert unique.

2000-04-25 Thread Lincoln Yeoh
At 10:17 PM 24-04-2000 -0400, Mike Mascari wrote: >Lincoln Yeoh wrote: >PostgreSQL implements SELECT...FOR UPDATE to allow for the >sequence you'be described: > >Session 1: > >BEGIN; >SELECT webuser FROM webusers WHERE webuser = 'webuser1'; > >Session 2: > >BEGIN; >UPDATE webusers SET webuser = 'w

Re: [GENERAL] Revisited: Transactions, insert unique.

2000-04-24 Thread Joachim Achtzehnter
Today, in a message to pgsql-general, David Boerwinkle wrote: > > it seems like this is something that ought to be handled > programmatically. That is, query the table to see if the row exists, > then decide what you are going to do (insert or update) based on the > results of your query. It ce

Re: [GENERAL] Revisited: Transactions, insert unique.

2000-04-24 Thread Haroldo Stenger
Ed Loehr wrote: > > Lincoln Yeoh wrote: > > > > Hi, > > > > Previously I wanted to ensure that I am inserting something unique into a > > table, the answer was to create a unique index on the relevant columns. > > > > But what if I don't want to get an error which would force a rollback? Say > >

Re: [GENERAL] Revisited: Transactions, insert unique.

2000-04-24 Thread Ed Loehr
ginal Message- > From: Lincoln Yeoh <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] <[EMAIL PROTECTED]> > Date: Monday, April 24, 2000 1:13 AM > Subject: [GENERAL] Revisited: Transactions, insert unique. > > >Hi, > > > >Previously I wanted to ensure

Re: [GENERAL] Revisited: Transactions, insert unique.

2000-04-24 Thread davidb
ery. Am I completely missing the point? David Boerwinkle -Original Message- From: Lincoln Yeoh <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] <[EMAIL PROTECTED]> Date: Monday, April 24, 2000 1:13 AM Subject: [GENERAL] Revisited: Transactions, insert unique. >Hi, > >Previous

Re: [GENERAL] Revisited: Transactions, insert unique.

2000-04-24 Thread Ed Loehr
Lincoln Yeoh wrote: > > Hi, > > Previously I wanted to ensure that I am inserting something unique into a > table, the answer was to create a unique index on the relevant columns. > > But what if I don't want to get an error which would force a rollback? Say > I want to insert something if it d

[GENERAL] Revisited: Transactions, insert unique.

2000-04-23 Thread Lincoln Yeoh
Hi, Previously I wanted to ensure that I am inserting something unique into a table, the answer was to create a unique index on the relevant columns. But what if I don't want to get an error which would force a rollback? Say I want to insert something if it doesn't already exist, but update it