Retrieving oid from INSERT

Hi all!

Is there any way to get the oid or any other row identifier
from a 
database insert with ns_db on Postgres?  Say for example I
have the 
following table:

CREATE TABLE test (
test_column int
) ;

And then I do an insert with aolserver, along the lines of:

ns_db dml $db "INSERT INTO test (test_column) SELECT 
COALESCE(MAX(test_column),0) + 1 FROM test"

Because I don't know the value of test_column that I just
entered, I 
don't have any way to continue working with that entry.  Is
there 
some trick to getting either the entry's oid or the value
of 
test_column back into the current TCL workspace?

Any suggestions would be appreciated.

-Scott


--
AOLserver - http://www.aolserver.com/

To Remove yourself from this list, simply send an email to
<listservlistserv.aol.com> with the
body of "SIGNOFF AOLSERVER" in the email
message. You can leave the Subject: field of your email
blank.

The way we do this in OpenACS is to have a primary key in
each table  
that is populated from a sequence.  So  in your code you get
the next  
number from the sequence, do the insert (storing the number
in the  
primary key column), and then you have that number already
in your  
possession to use to reference the row.

janine

On Mar 28, 2006, at 11:58 AM, William Scott Jordan wrote:

> Hi all!
>
> Is there any way to get the oid or any other row
identifier from a  
> database insert with ns_db on Postgres?  Say for
example I have the  
> following table:
>
> CREATE TABLE test (
> test_column int
> ) ;
>
> And then I do an insert with aolserver, along the lines
of:
>
> ns_db dml $db "INSERT INTO test (test_column)
SELECT COALESCE(MAX 
> (test_column),0) + 1 FROM test"
>
> Because I don't know the value of test_column that I
just entered,  
> I don't have any way to continue working with that
entry.  Is there  
> some trick to getting either the entry's oid or the
value of  
> test_column back into the current TCL workspace?
>
> Any suggestions would be appreciated.
>
> -Scott
>
>
> --
> AOLserver - http://www.aolserver.com/
>
> To Remove yourself from this list, simply send an email
to  
> <listservlistserv.aol.com> with the
> body of "SIGNOFF AOLSERVER" in the email
message. You can leave the  
> Subject: field of your email blank.


--
AOLserver - http://www.aolserver.com/

To Remove yourself from this list, simply send an email to
<listservlistserv.aol.com> with the
body of "SIGNOFF AOLSERVER" in the email
message. You can leave the Subject: field of your email
blank.

William Scott Jordan said:
> ns_db dml $db "INSERT INTO test (test_column)
SELECT
> COALESCE(MAX(test_column),0) + 1 FROM test"
Ehrm, that is a very, very bad way of creating keys. It is
no unlikely
that on a very loaded site two people might insert at the
same time and
get the same key!

The correct way of doing this is using a sequence:

create sequence test_id;

You can either first select an ID from this and use it
manually in your
query:

select nextval('test_id') as test_id;

Or use it in a default:

create table test (
   test_id primary key default nextval('test_id')
);

If using that method, you can use
"curval('test_id')" to get the last
assigned id and use it for further processing after you do
the insert.
curval gives back the last sequence handed out in the
current session, so
it is perfectly safe to use as long as you stick with the
same database
handle.

Cheers,
Bas.


--
AOLserver - http://www.aolserver.com/

To Remove yourself from this list, simply send an email to
<listservlistserv.aol.com> with the
body of "SIGNOFF AOLSERVER" in the email
message. You can leave the Subject: field of your email
blank.

How depresssing. I'm doing a lot of work with Sybase these
days.
That's right, the "enterprise" database. It
seems that sequences are
not "enterprise" enough and you end up seeing
developers resorting to
stuff like this instead.

Not that it's relevant in this case, but it's nice to
blowoff steam
occasionally - especially in an environment where people get
it!

On 3/29/06, Bas Scheffers <basscheffers.net> wrote:
> William Scott Jordan said:
> > ns_db dml $db "INSERT INTO test
(test_column) SELECT
> > COALESCE(MAX(test_column),0) + 1 FROM test"
> Ehrm, that is a very, very bad way of creating keys. It
is no unlikely
> that on a very loaded site two people might insert at
the same time and
> get the same key!
>
> The correct way of doing this is using a sequence:


--
AOLserver - http://www.aolserver.com/

To Remove yourself from this list, simply send an email to
<listservlistserv.aol.com> with the
body of "SIGNOFF AOLSERVER" in the email
message. You can leave the Subject: field of your email
blank.

People that think this is a good solution aren't
"enterprise" developers
anyway! 

Though I sympathize with your pain, Sybase has identity
columns, which
work quite well.

The other option is to simply have a table to like (sequence
varchar, id
int) and then:

update sequences set id = id + 1 where sequence =
'mytable';
-- that also locked the row for the next updater, so now it
is perfectly
safe to do:
select id from sequences where sequence = 'mytable'

All you need to do is spent half an hour at the start of a
project setting
up this, a stored proc and a Tcl procedure named
"get_next_id" and forget
about it from then on...

Other than this quirk, I have found Sybase to be much more
"enterprise"
than other database I have worked with.

Cheers,
Bas.

Mark Aufflick said:
> How depresssing. I'm doing a lot of work with Sybase
these days.
> That's right, the "enterprise" database.
It seems that sequences are
> not "enterprise" enough and you end up
seeing developers resorting to
> stuff like this instead.
>
> Not that it's relevant in this case, but it's nice to
blowoff steam
> occasionally - especially in an environment where
people get it!
>
> On 3/29/06, Bas Scheffers <basscheffers.net> wrote:
> > William Scott Jordan said:
> > > ns_db dml $db "INSERT INTO test
(test_column) SELECT
> > > COALESCE(MAX(test_column),0) + 1 FROM
test"
> > Ehrm, that is a very, very bad way of creating
keys. It is no unlikely
> > that on a very loaded site two people might insert
at the same time and
> > get the same key!
> >
> > The correct way of doing this is using a sequence:
>
>
> --
> AOLserver - http://www.aolserver.com/
>
> To Remove yourself from this list, simply send an email
to
> <listservlistserv.aol.com> with the
> body of "SIGNOFF AOLSERVER" in the email
message. You can leave the
> Subject: field of your email blank.
>


--
AOLserver - http://www.aolserver.com/

To Remove yourself from this list, simply send an email to
<listservlistserv.aol.com> with the
body of "SIGNOFF AOLSERVER" in the email
message. You can leave the Subject: field of your email
blank.

Doesn't that still serialize all updates requiring access
to that sequence?

If you used it within a transaction it would also have
implications
with other transactions using that "sequence"
especially in a rollback
situation.

In the context of AOLServer you could minimise the impact of
these
problems by initialising a totally seperate pool of db
connections
just for this "sequence" manipulation proc. This
could decrease
performance though - especially if there is network latency
between
aolserver and the database.

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
I'm getting off topic ;)


--
AOLserver - http://www.aolserver.com/

To Remove yourself from this list, simply send an email to
<listservlistserv.aol.com> with the
body of "SIGNOFF AOLSERVER" in the email
message. You can leave the Subject: field of your email
blank.

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
<listservlistserv.aol.com> with the
body of "SIGNOFF AOLSERVER" in the email
message. You can leave the Subject: field of your email
blank.

On Tue, Apr 04, 2006 at 03:02:21PM +1000, Mark Aufflick
wrote:
> Doesn't that still serialize all updates requiring
access to that sequence?

No.  Sequences in an RDBMS are designed to scale gracefully
under
heavy concurrent load.  (This is basic stuff, grab any good
intro. book on databases and read up on it.)

They basically do this by guaranteeing ONLY that the integer
you get
from the sequence is unique and generally increasing over
time -
that's it.  The sequence can skip numbers, and the sequence
integers
are NOT necessarily in any particular order by either commit
time or
sequence request time.

> If you used it within a transaction it would also have
implications
> with other transactions using that
"sequence" especially in a rollback
> situation.

No, it doesn't, not in any database with real built-in
sequence
support, like Oracle or PostgreSQL.  If you rollback a
transaction
which used a sequence, that sequence integer is simply
discarded,
never to be used again.

Of course, if you are implementing sequences yourself with a
helper
table, then yes, you get to worry about all the above
issues.  (They
are only hard to solve *IF* you also need concurrency.) 
This is why
it's nice to have real sequences in the RDBMS...

> In the context of AOLServer you could minimise the
impact of these
> problems by initialising a totally seperate pool of db
connections
> just for this "sequence" manipulation proc.
This could decrease

AFAICT, you're speculating about solving a problem that
never actually
exists.

There are real RDBMSs, like MS SQL Server, which don't
support the
sort of sequences I described above, but those all tend to
have some
different but similar mechanism intended to solve the same
problems.
I think SQL Server and Sybase use auto-incrementing column
values, for
example.

I bet SQLite, for instance, doesn't provide any such
sequence-like
support at all, so you'd emulate it yourself by simply
taking an
exclusive lock on a my_sequence table or whatever.  But,
SQLite is a
lightweight embedded database designed for low or no
concurrency, so
by definition, you don't have a concurrency problem anyway!
 (SQLite
takes an exlusive lock on the whole database for every
single write
transaction, so if you DO have a concurrency problem, it's
unlikely to
be solely because of your ad-hoc table-based sequence
support!)

But actually I'm wrong, SQLite does support
auto-incrementing columns,
and provides a helper function to return the the integer key
that just
got automatically generated by your insert:

  http://www.sqlite.o
rg/faq.html#q1

And of course, serializing all writes (or even all
transactions
period) works just fine if your load is moderate and your
application
is carefully designed to insure that ALL your transactions
are very
fast.  That seems to be exactly the approach that D. Richard
Hipp,
creator of SQLite, takes in building his own SQLite-backed
websites.

-- 
Andrew Piskorski <atppiskorski.com>
http://www.piskorski.com/


--
AOLserver - http://www.aolserver.com/

To Remove yourself from this list, simply send an email to
<listservlistserv.aol.com> with the
body of "SIGNOFF AOLSERVER" in the email
message. You can leave the Subject: field of your email
blank.

On 2006.04.04, Bas Scheffers <basSCHEFFERS.NET> wrote:
> 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...

I just have to ask: what version of Sybase and Oracle are
you comparing?
Oracle 7 to Sybase ASE 11?  Since Oracle 8.0 and 8i, in my
experience,
Oracle has always been better, more reliable, etc. than
Sybase ASE 10
and 11.  Oracle 10g today ... there's no comparison --
Sybase is closer
to MySQL/PostgreSQL than it is to Oracle, in terms of
capability and
stability.

Of course, I find that it always ends up being a people
problem: if you
have DBAs that don't know what they're doing or don't do
their jobs
properly, you can end up with an Oracle instance that's
broken and a
Sybase setup that works ... which leaves developers to learn
that
"Sybase is better than Oracle" ...

The lesson here: Regardless of software, if your people
can't make it
work, it won't satisfy you.

But, I still assert that, given equally competent people,
modern Oracle
is better than modern Sybase.  Hands down.

-- Dossy

-- 
Dossy Shiobara              | dossypanoptic.com | http://dossy.org/
Panoptic Computer Network   | http://panoptic.com/
  "He realized the fastest way to change is to laugh
at your own
    folly -- then you can let go and quickly move on."
(p. 70)


--
AOLserver - http://www.aolserver.com/

To Remove yourself from this list, simply send an email to
<listservlistserv.aol.com> with the
body of "SIGNOFF AOLSERVER" in the email
message. You can leave the Subject: field of your email
blank.

Andrew Piskorski said:
> No.  Sequences in an RDBMS are designed to scale
gracefully under
> heavy concurrent load.  (This is basic stuff, grab any
good
I think Mark was talking about my Sybase solution (sequence
table) for if
you don't want to use "identity" columns.

> I think SQL Server and Sybase use auto-incrementing
column values, for
> example.
Yeah they do. They have a bit of a bad rep, though. Do a
google on "sybase
identity gap" to see what I mean. That and they
require extra work if you
want to transfer a table from one DB to another, but not
much.

> And of course, serializing all writes (or even all
transactions
> period) works just fine if your load is moderate and
your application
The load can even be pretty high. Unless you are doing an
amazing amount
of inserts, it's not going to matter, especially if you
first get the
sequence in a different transaction.

Cheers,
Bas.


--
AOLserver - http://www.aolserver.com/

To Remove yourself from this list, simply send an email to
<listservlistserv.aol.com> with the
body of "SIGNOFF AOLSERVER" in the email
message. You can leave the Subject: field of your email
blank.