Re: [GENERAL] using ID as a key(REPOST) - Mailing list pgsql-general

> here somebody is challeging a very common practice (using sequence).
> seems that nobody really care! can some experts give us some authoritative
> analysis? seems a lot of people are more willing to tutor guys who
> did not do their homework than do real thinking, guess we all need
> a break ;-)

Since I'm not a database expert, I didn't want to argue with the
original assertion.  Plus I did not have the time to take a break.
And I still don't want to become falme bait, which this has the
potential to become.

But I will say that reviewing the postgresql message logs shows that a
GREAT deal of attention has been paid to making sequences work right.
Even putting race conditions aside, I trust PostgreSQL code more than
my own - they have certainly put more more thought into the issue than
I have.

> oracle, sql server (identity property, closer to pg's oid
> but more manipulatable) have sequence.
> however, since they are not sql92, so, it is a headache.
> I'm now porting linux/pg/apache/perl/cgi to NT/MSsql/iis/perl/cgi,
> this is a problem -- I'm now reading doc from M$ on how to
> migrate oracle to mssql to get the hint of how to migrate
> pg to mssql.

A pg_dump provides the complete sequence information - current value,
amount to increment by, everything.  No clue how to make mssql
understand it, since I have no interest in running mssql.

> however, "programmatically" is really ugly: you have to use
> flock, easy to be the bottleneck. -- not sure tho, any ideas?

This is why you need sequences - because in a client-server
environment, it is (near) impossible to do this with 100% reliablilty
unless you use expensive table locks.  Whereas sequences are FAST,
don't block table access, and are reliable.

If you do eventually decide to switch to a programatic interface, just
drop the insert default that calls the sequence generation.  Then you
are left with an int field that you can handle "programmatically".  Of
course you have to write that code then.  But why write it now, if
postgres works fine.

FWIW, I always use the PostgreSQL sequence to generate serial fields.
Sometimes I don't use them in conjuction with an automatic insert
default, but that was probably historical ignorance in most cases.  If
convenience matters most, do :

------------------------------------------------------------------------
test=> create table test (a serial, b int);
NOTICE:  CREATE TABLE will create implicit sequence 'test_a_seq' for SERIAL column 'test.a'
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'test_a_key' for table 'test'
CREATE
test=> \d test
Table    = test
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| a                                | int4 not null default nextval('" |     4 |
| b                                | int4                             |     4 |
+----------------------------------+----------------------------------+-------+
Index:    test_a_key

test=> insert into test (b) values (1);
INSERT 303036760 1
test=> insert into test (b) values (1);
INSERT 303036761 1
test=> select * from test;
a|b
-+-
1|1
2|1
(2 rows)

test=> select currval('"test_a_seq"');
currval
-------
      2
(1 row)

------------------------------------------------------------------------

Which is equivalent to:

------------------------------------------------------------------------
CREATE SEQUENCE "test_a_seq" start 2 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('"test_a_seq"');
CREATE TABLE "test" (
        "a" int4 DEFAULT nextval('"test_a_seq"') NOT NULL,
        "b" int4);
COPY "test" FROM stdin;
1       1
2       1
\.
CREATE UNIQUE INDEX "test_a_key" on "test" using btree ( "a" "int4_ops" );
------------------------------------------------------------------------

But if you are concerned about prgram portability, you may want

------------------------------------------------------------------------
test=> create table test (a int unique, b int);
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'test_a_key' for table 'test'
CREATE
test=> CREATE SEQUENCE "test_a_seq" start 2 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
CREATE
------------------------------------------------------------------------

then in your code do:

    select nextval('"test_a_seq"');

But frankly I see no reason to do this unless you have some oddly
cross-linked tables, because currval always gives you the value you
just inserted.  And if you do have cross-linked tables like that, you
probably have more serious choices to make in database design than
whether or not to use sequences.

Anyway, I've gone on much longer than I should.  Of course, this is
all just my opinion.  But I do feel that reviewing archive discussions
on sequences will show that they are an extremely valuable and well
thought-out tool,  Their use should not be casually dismissed.

--

Karl DeBisschop