Обсуждение: A question about inheritance and sequence

Hello,
I have two databases, and I want the same structure, and if I change the structure of one database it changes the structure of the other. In documentation I saw that the syntax goes something like this: CREATE TABLE "User" () INHERITS database2."User"; But it's not working like this, and I couldn't find the example. Can you please write the correct example?

The other problem I have if with sequence. I have this table structure:
CREATE TABLE "Notes" (
    userkey character(40) NOT NULL,
    noteid SERIAL NOT NULL,
    note text,
    PRIMARY KEY (userkey, noteid),
    FOREIGN KEY (userkey) REFERENCES "User"(userkey) ON UPDATE CASCADE ON DELETE CASCADE
);

How can I make the noteid go from 1 to max for each user? Because if I add a row it goes +1 for all users? How would I do that the note id would go +1 for each userkey. Do I have to make nested queries and where would I do it?
Thank you for help.

Regards,

short answer is: do not use sequences for this. do not use sequences for any MEANINGFUL number.

to use sequences here, you would have to create separate sequence for each user, which is probably not practical.

use our own key-generating code, be it in the application or in trigger, you choose.

Triggers are generally more robust. Quick example:
NEW.noteid := SELECT 1+ COALESCE( (SELECT noteid FROM "Notes" WHERE userkey=NEW.userkey ORDER BY noteid DESC LIMIT 1), 0 );

Applications must take care of race conditions (like 2 clients trying to insert same noteid).


--
Filip Rembiałkowski
JID,mailto:filip.rembialkowski@gmail.com
http://filip.rembialkowski.net/

On Wed, Jun 24, 2009 at 01:43:20PM +0200, Marko Pahić wrote:
>    Hello,
>    I have two databases, and I want the same structure, and if I change the
>    structure of one database it changes the structure of the other. In
>    documentation I saw that the syntax goes something like this: CREATE TABLE
>    "User" () INHERITS database2."User"; But it's not working like this, and I
>    couldn't find the example. Can you please write the correct example?

If they're two separate databases, you can't have objects in one inherit from
objects in another. Two separate databases know nothing of each other. If, on
the other hand, you have separate schemas within the same database, objects in
one schema can inherit from objects in another. The partitioning documentation
shows an example of the basic syntax; if the tables involved are in different
schemas, simply write <schemaname>.<tablename> instead of just <tablename>

>    The other problem I have if with sequence. I have this table structure:
>    CREATE TABLE "Notes" (
>        userkey character(40) NOT NULL,
>        noteid SERIAL NOT NULL,
>        note text,
>        PRIMARY KEY (userkey, noteid),
>        FOREIGN KEY (userkey) REFERENCES "User"(userkey) ON UPDATE CASCADE ON
>    DELETE CASCADE
>    );
>
>    How can I make the noteid go from 1 to max for each user? Because if I add
>    a row it goes +1 for all users? How would I do that the note id would go
>    +1 for each userkey. Do I have to make nested queries and where would I do
>    it?

You could theoretically make a sequence for each user, but that would likely
be so painful to manage that you'd quickly give it up. Note, by the way, that
having a table name that you're required to double-quote all the time is also
often found to be a big pain. The other way to do it involves issuing a query
each time you add a new Note to find out what the user's last noteid was, and
add one to that. That sounds both slow and painful, though perhaps not as
painful as maintaining one sequence per user.

- Josh / eggyknap

--
Human beings make life so interesting.
Do you know that in a universe so full of wonder,
they have managed to invent boredom? Quite astonishing.