Mark Aufflick said:
> Doesn't that still serialize all updates requiring access to that
> sequence?
Sequences are used for inserts, not updates. Well, I haven't seen them
being used in updates anyway.
> If you used it within a transaction it would also have implications
> with other transactions using that "sequence" especially in a rollback
> situation.
Why? Yes, they would be serialized, but again, this will really only be
used for inserts into the same table, which are serialized by the database
anyway, as far as I know.
> In the context of AOLServer you could minimise the impact of these
> problems by initialising a totally seperate pool of db connections
That is not needed. Simply by first getting a new id in a different
transaction, pretty much all of the problems are gone. In Oracle and
postgres, getting a sequence value is serialized too; it has to be. My
solution would be less efficient if used within a transaction as the
updates to actual data tables would also stop other other transactions to
get a sequence. So just get an ID in a different transaction. Postgres
sequences don't roll back either when the transaction fails, even if used
in line:
postgres=# create sequence foo_id;
CREATE SEQUENCE
postgres=# create table foo (foo_id int primary key default
nextval('foo_id'), name varchar(10));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey"
for table "foo"
CREATE TABLE
postgres=# insert into foo (name) values ('bas');
INSERT 0 1
postgres=# insert into foo (name) values ('bas1234567890');
ERROR: value too long for type character varying(10)
postgres=# insert into foo (name) values ('bas1234567890');
ERROR: value too long for type character varying(10)
postgres=# insert into foo (name) values ('bas1234567890');
ERROR: value too long for type character varying(10)
postgres=# insert into foo (name) values ('bas1234567890');
ERROR: value too long for type character varying(10)
postgres=# insert into foo (name) values ('bas1234567890');
ERROR: value too long for type character varying(10)
postgres=# insert into foo (name) values ('bas1234567890');
ERROR: value too long for type character varying(10)
postgres=# select currval('foo_id');
currval
---------
7
I suspect Oracle (which I don't have handy right now) to be the same. So
there is no reason in this comparison to require my Sybase solution to
roll back the sequence. (which it would if used in the same tran as the
update, and some would like that behaviour as to not get gaps, but I
couldn't care less)
> I'd prefer my enterprise database not to decide that it would be a
> good idea to allow NULL = NULL to evaluate as true by default, but now
You mean you'd like it not acting like any other programming language out
there? :) Personally, I find the whole "is not null" in SQL annoying,
making me have to write extra code to deal with NULL comparisons.
I also would prefer my enterprise database to distinguish between an empty
string and NULL! (but I'd also like Sybase to allow NULL in a boolean
field to distinguish between an explicit false and a value left empty by
the user...)
All Systems have their merrits. As (commercial) enterprise database go, I
have _much_ better experiences with Sybase than Oracle for ease of
maintainance (best backups in the business), optimization (it just uses
indexes that make sense without needing hints, no matter how complex I
make the query), performance and realiability (don't get me started). Your
mileage may vary...
Cheers,
Bas.
--
AOLserver - http://www.aolserver.com/
To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]>
with the
body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject:
field of your email blank.
