Re: A question about inheritance and sequence

W dniu 24 czerwca 2009 13:43 użytkownik Marko Pahić <mpahic(at)gmail(dot)com>napisał:

> 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";

Where did you see such example?
databases in postgres are separate, self-contained data catalogs, not
visible one to another.

maybe you meant schemas, which are just namespaces inside one database.

To synchronize structure you will need something else.

>
> 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?

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(dot)rembialkowski(at)gmail(dot)com
http://filip.rembialkowski.net/