Обсуждение: Database Features Questions,

I've never used PostgreSQL before, and I have some questions I couldn't
locate the answers to in any of the postgresql manuals I've found.
Forgive me if they've been asked a dozen times before.

Can I create nested transactions?
  EG: Can I create transaction1
      do some work
        create transaction2
        do some work
        commit/or/rollback transaction2
      do some work
      commit/or/rollback transaction1

If not, how can I simulate this?

Can I create transactions which other connections can access to?
      Server 1 starts Transaction named "WebRequest-1234"
      Server 1 does work in transaction "WebRequest-1234"
      Server 2 does work in transaction "WebRequest-1234"
      Server 2 signals completion to Server 1
      Server 1 commits/or/rollsback transaction "WebRequest-1234"

Another example of this would be a multithreaded database application
trying to achieve parallelism in reporting inside the context of the
uncommitted changes in a transaction:

      Thread 1 starts Transaction "Transaction-1234"
      Thread 1 does some work
      Thread 1 dispatches Threads 2,3,4
      Thread 2 does some heavy reporting inside context of
Transaction-1234
      Thread 3 does some heavy reporting inside context of
Transaction-1234
      Thread 4 does some heavy reporting inside context of
Transaction-1234
      Thread 1 waits for threads to complete
      Thread 1 commits/rollsback Transaction-1234

In this case they could should share a single DBConnection, but that
would not allow any parallelism in the DB.

If this is not possible, how I can simulate this?


I see that to create unique identifiers for a column, I can use the OID
value or a sequence.  If I use a sequence, is there any way of having
the sequence value returned after an insert, instead of the OID?  Or for
every insert I do, do I need to do two requests -- One to insert, and
one to do a lookup by OID to fetch the sequence number of the row I just
inserted?

Semirelated question, what is the maximum bound of the OID value?  If I
fetch this value using JDBC, what type does it come back as?  And what
happens when that overflows?

Another question is, if postgre does not support these features, does
Interbase, Oracle or another DB support these types of features?

Thanks,
-Joe

On Wed, 20 Sep 2000 13:37:32 -0400, Joe Kislo wrote:
>
> I see that to create unique identifiers for a column, I can use the OID
> value or a sequence.  If I use a sequence, is there any way of having
> the sequence value returned after an insert, instead of the OID?  Or for
> every insert I do, do I need to do two requests -- One to insert, and
> one to do a lookup by OID to fetch the sequence number of the row I just
> inserted?
>

you should not use the OID value for application level work.  for one thing,
it's not portable, and if you rebuild the database it'll change.  you should
consider it a strictly internal value.

anyway, that's what sequences are for (as you recognize).  they're portable,
and under your control.  you can create one like so:

    CREATE SEQUENCE tablename_colname_seq;
    CREATE TABLE tablename
        (colname INT4 DEFAULT nextval('tablename_colname_seq');
    CREATE UNIQUE INDEX tablename_colname_key on tablename (colname);

in the midst of a transaction you can get the current value of the sequence
by calling the funciton 'currval':

    SELECT currval('tablename_colname_seq');

This is the most portable way of accomplishing what you're after.

Postgres also offers a special data type called 'SERIAL':

    CREATE TABLE tablename (colname SERIAL);

is the equivalent of the above 3 create statements.

There's more info on this under 'Datatypes' in the User section of the
Postgres docs (from which i copied this example) that come with the distribution.

there are also additional sequence functions that could be
of use like nextval(), etc.  there's info on them in the docs
to.

regards
--e--

On Wed, 20 Sep 2000 17:54:40 -0400, Joe Kislo wrote:

> > you should not use the OID value for application level work.  for one thing,
> > it's not portable, and if you rebuild the database it'll change.  you should
> > consider it a strictly internal value.
>
>     Hmm, I think you missed what my ultimate goal of using it was...
>

well, you said:
"I see that to create unique identifiers for a column, I can use the OID
value or a sequence. ..."

to which i would say: OID's aren't created in the user's space:  they're a global
value for all databases (they also don't uniquely identify a column, but a row in
a given table, in a given database in an installation of postgres).
if you're looking to create unique identifiers for a given application,
you want to use sequences and not rely on OID's.

but, if i'm missing something i apologize in advance.


> > anyway, that's what sequences are for (as you recognize).  they're portable,
> > and under your control.  you can create one like so:
> >
> >         CREATE SEQUENCE tablename_colname_seq;
> >         CREATE TABLE tablename
> >             (colname INT4 DEFAULT nextval('tablename_colname_seq');
> >         CREATE UNIQUE INDEX tablename_colname_key on tablename (colname);
> >
> > in the midst of a transaction you can get the current value of the sequence
> > by calling the funciton 'currval':
> >
> >         SELECT currval('tablename_colname_seq');
> >
> > This is the most portable way of accomplishing what you're after.
> >
> > Postgres also offers a special data type called 'SERIAL':
> >
> >         CREATE TABLE tablename (colname SERIAL);
> >
> > is the equivalent of the above 3 create statements.
>
>     What I'm trying to do is insert a record, one with a SERIAL column, and
> after that insert know what the setting to that serial value was.
>
> So:
>
> CREATE Table whatever (whateverID SERIAL),
>                        (otherdata int);
> insert into whatever (otherdata) values (1);
>
> This insert function, in postgresql, returns the OID value as a result.
> Unfortunately I want the -serial- number back, not the OID.  Does this
> mean I must initiate another request to the database server to get the
> serialID that was issued to my insert function?
>

yes, the insert statement returns the OID (along with the command, and the
number of rows affected), but only in psql.  other interfaces are not guaranteed
to return it (because it's postgres specific).  for example, if you do an
insert via perl's DBD::Pg, all you get back into the application is the number of rows
affected.


> As to your suggestion to use:
> select currval("whatever_seq");
>
> Would that work if I was in autocommit mode?  Wouldn't that create a

yes that's what you need to do.  and no, it won't work in autocommit.
see below . . .


> race condition if other threads were inserting rows into table
> "whatever", and updating the sequence number? Or does "currval" always
> return the LAST sequence number used by -my- connection?
>

it won't create a race condition, because postgres doesn't lock a row in
the database from reading when writing to it (unless specified to).  currval
will return the last sequence number used by your connection only when you're
using transactions (i.e. autocommit set to true) and you haven't committed
or rolled back.


> Is there anyway to get the serial# to be returned instead of the OID, or
> do I have to send a second query to track the serial numbers on my
> inserts?
>

so, to finally answer your question ;-) . . .

you would get the value of the serial column by calling the function currval
(which is simply a sql query on a system table, which maintains info about each
individual sequence by name) on the sequence for the SERIAL column in your table.
the naming conventiion is (i believe) "tablename_colname_seq."

if you need the value for a second insert (like for a table that has a FK
reference to the one you just inserted to) you could do this:

begin;
insert into foo (A,B) values (B);  /* where A is a serial column */
insert into bar (C,D) values ( currval('foo_A_seq'), 'yuck');  /* where C is a FK ref to foo */
commit;

if you simply need to get the "number" from that column you'd:

begin;
insert into foo (A,B) values (B);  /* where A is a serial column */
select currval('foo_A_seq');
commit;

note that the transaction is key, without which there's no guarantee that
some other statement will affect the value of the sequence.

HTH
--e--






> Thanks,
> -Joe
>