Обсуждение: problems with unique key on table with serial

Dear all,

I have the following problem.

I have a table (among others of course) that has the following structure:
    CREATE TABLE "tblserver" (
    "ID_Server" oid DEFAULT nextval('tblserver_id_server_seq'),
    "Svr_Name" varchar(150) NOT NULL,
    "IP_Address" inet NOT NULL DEFAULT '0.0.0.0',
    "Location" varchar(255),
    "Description" text,
    "C_Date" date,
    "C_Time" time,
    "C_User" varchar(42),
    "L_Date" date,
    "L_Time" time,
    "L_User" varchar(42),
    "C_Update" int4,
    "TimeStamp" timestamp,
    PRIMARY KEY("ID_Server")) \g

and a sequence for that (I create the sequence first and then the table)
    CREATE SEQUENCE tblserver_id_server_seq \g

than I have two functions
    CREATE FUNCTION fncdatetime_ains() returns opaque as
    'begin
        new.TimeStamp := now();
        new.C_Date := current_date;
        new.C_Time := current_time;
        new.C_User := getpgusername();
        return new;
    end;'
    language 'plpgsql' \g

    CREATE FUNCTION fncdatetime_aupd() returns opaque as
    'begin
        new.TimeStamp := now();
        new.L_Date := current_date;
        new.L_Time := current_time;
        new.L_User := getpgusername();
        return new;
    end;'
    language 'plpgsql' \g

and two triggers calling the functions:
    CREATE TRIGGER trgInsServer before insert on tblserver
    for each row execute procedure fncdatetime_ains() \g

    CREATE TRIGGER trgUpdServer before update on tblserver
    for each row execute procedure fncdatetime_aupd() \g

When I create the structure I do an insert into the table so that
I have a default-dataset. This is because I have some dependencies
on this table and I need kind of a blind dataset in it.

    INSERT INTO tblserver ("ID_Server", "Svr_Name", "IP_Address",
    "Location", "Description")
    VALUES (1, 'slughammer', '24.3.19.73', 'slugmania',
    'db-design by chris') \g

Now, when I try to insert a second dataset in the table from my
Web application I get the error-message:

Warning: PostgreSQL query failed: ERROR:
Cannot insert a duplicate key into unique index tblserver_pkey in
/sitebuilder/_inc/fnc_server_adm.php on line 178

What is that supposed to mean??? I thought When I have a sequence
pSQL determines the new PKey by itself on an insert?

Pleeease anyone give me a hint - just a small one ;-)

    chris

chris G�nther <guenther@uscreen.de> writes:
>     CREATE TABLE "tblserver" (
>     "ID_Server" oid DEFAULT nextval('tblserver_id_server_seq'),
>     ...

>     INSERT INTO tblserver ("ID_Server", "Svr_Name", "IP_Address",
>     "Location", "Description")
>     VALUES (1, 'slughammer', '24.3.19.73', 'slugmania',
>     'db-design by chris') \g

> Now, when I try to insert a second dataset in the table from my
> Web application I get the error-message:

> Warning: PostgreSQL query failed: ERROR:
> Cannot insert a duplicate key into unique index tblserver_pkey in
> /sitebuilder/_inc/fnc_server_adm.php on line 178

You inserted a row with an explicit specification of the ID_Server
column.  That's fine, but it didn't advance the sequence counter.
So your first try to insert something without a specified ID_Server
will compute the default column value, nextval('tblserver_id_server_seq'),
which is 1.  Presto, collision.  Subsequent tries should work though.

If you want to insert things with specified ID_Server values, it's up
to you to advance the sequence counter past those values (use setval()).
In this particular example, though, I don't see why you don't just leave
off the ID_Server value from the INSERT and let it assign 1 from the
sequence.

            regards, tom lane