Thread: Sequence question.
I'm working on an idea that uses sequences. I'm going to create a table like this: id serial, sequence int, keyword varchar(32), text text for every keyword there will be a uniq sequence for it eg: id, sequence, keyword 1, 1, foo, ver1 2, 1, bar, bar ver1 3, 2, foo, ver2 4, 2, bar, bar ver2 etc... I could have one sequence for all keyword which would be 1,3, etc... I would be prefer to have them in sequence. I'm sure someone has ran into this before, any ideas? Anthony.
How about using two tables; one to hold the keyword and its (last allocated) sequence value, and the second to store your data as below. create table Keyword ( keyword varchar(32), sequence integer, primary key(keyword) ) create table Data ( id serial, sequence int, keyword varchar(32), text text ) Add a trigger to the Data table for Insert so that it joins to the (parent) keyword table and increments the keyword.sequence value, and places that into the Data.sequence value. You will get 'holes' in the keyword sequencing when you delete data from the Data table. If that's a problem then you will need an alternative design. Hope that helps. John Sidney-Woollett Anthony Best said: > I'm working on an idea that uses sequences. > > I'm going to create a table like this: > > id serial, > sequence int, > keyword varchar(32), > text text > > for every keyword there will be a uniq sequence for it eg: > > id, sequence, keyword > 1, 1, foo, ver1 > 2, 1, bar, bar ver1 > 3, 2, foo, ver2 > 4, 2, bar, bar ver2 > etc... > > I could have one sequence for all keyword which would be 1,3, etc... I > would be prefer to have them in sequence. I'm sure someone has ran into > this before, any ideas? > > Anthony. > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
Sorry I should have added that the trigger needs to create a new keyword record if the join in the trigger fails to locate the keyword in the keyword table. Hopefully you can create the trigger yourself. The keyword table is effectively a distinct list of all keywords inserted into the data table with the associated last allocated sequence number for the keyword. John John Sidney-Woollett said: > How about using two tables; one to hold the keyword and its (last > allocated) sequence value, and the second to store your data as below. > > create table Keyword ( > keyword varchar(32), > sequence integer, > primary key(keyword) > ) > > create table Data ( > id serial, > sequence int, > keyword varchar(32), > text text > ) > > Add a trigger to the Data table for Insert so that it joins to the > (parent) keyword table and increments the keyword.sequence value, and > places that into the Data.sequence value. > > You will get 'holes' in the keyword sequencing when you delete data from > the Data table. If that's a problem then you will need an alternative > design. > > Hope that helps. > > John Sidney-Woollett > > Anthony Best said: >> I'm working on an idea that uses sequences. >> >> I'm going to create a table like this: >> >> id serial, >> sequence int, >> keyword varchar(32), >> text text >> >> for every keyword there will be a uniq sequence for it eg: >> >> id, sequence, keyword >> 1, 1, foo, ver1 >> 2, 1, bar, bar ver1 >> 3, 2, foo, ver2 >> 4, 2, bar, bar ver2 >> etc... >> >> I could have one sequence for all keyword which would be 1,3, etc... I >> would be prefer to have them in sequence. I'm sure someone has ran into >> this before, any ideas? >> >> Anthony. >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend >
Apologies if this is a double post - I'm sure I sent a similar e-mail to the list, but it seems to have disappeared in the ether. Id there a definitive list of what replication options are currently available for 7.4 (and 7.5), and what their relative strengths and weaknesses are; ease of use, configuration, cost, support etc I need to start tackling replication of our DB in the next few weeks, and wondered what the options are. I've seen RServer and Mammoth Replication - these look good (on paper) but both appear to involve a license fee (which is more than I have to spend right now). Is pgReplication ready for 7.4 yet? And are there any other *free* options? If not maybe I'll have to find the money for a commercial solution. I'd appreciate any feedback from anyone who has a working replication solution. Thanks John Sidney-Woollett
On Dec 17, 2003, at 3:15 AM, John Sidney-Woollett wrote: > Apologies if this is a double post - I'm sure I sent a similar e-mail > to > the list, but it seems to have disappeared in the ether. > > Id there a definitive list of what replication options are currently > available for 7.4 (and 7.5), and what their relative strengths and > weaknesses are; ease of use, configuration, cost, support etc > > I need to start tackling replication of our DB in the next few weeks, > and > wondered what the options are. > > I've seen RServer and Mammoth Replication - these look good (on paper) > but > both appear to involve a license fee (which is more than I have to > spend > right now). There's 2 versions of eRServer - v1.2 is free on gborg and Postgresql, Inc. has another that you get with a support contract. I've patched eRServer pretty heavily to fix some bugs and add some usability. The eRServer list has some discussion of these. Jan Weick is in the early stages of developing a new replication system that looks to be pretty nice. He also has a project set up on gborg. In addition, RServ (what eRServer was based on) is still going, along with dbmirror (which is in contrib in the main distribution). Mammoth is something you would have to ask Joshua Drake about. Don't know anything about it. > > Is pgReplication ready for 7.4 yet? And are there any other *free* > options? If not maybe I'll have to find the money for a commercial > solution. Doesn't look like based on the web site at gborg. > > I'd appreciate any feedback from anyone who has a working replication > solution. > So far eRServer is working ok for me. If you use the unpatched version on gborg, be sure to read the erserver list archives, as the current tarball has a problem or two. If you want to work with my fork let me know. I like to think it works better, but I'm hardly unbiased... > Thanks > > John Sidney-Woollett > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if > your > joining column's datatypes do not match > -------------------- Andrew Rawnsley President The Ravensfield Digital Resource Group, Ltd. (740) 587-0114 www.ravensfield.com
Andrew Thanks for the info. Can you detail more fully the changes you made to eRServer - v1.2, and will your patched version work with 7.4? John Andrew Rawnsley said: > > On Dec 17, 2003, at 3:15 AM, John Sidney-Woollett wrote: > >> Apologies if this is a double post - I'm sure I sent a similar e-mail >> to >> the list, but it seems to have disappeared in the ether. >> >> Id there a definitive list of what replication options are currently >> available for 7.4 (and 7.5), and what their relative strengths and >> weaknesses are; ease of use, configuration, cost, support etc >> >> I need to start tackling replication of our DB in the next few weeks, >> and >> wondered what the options are. >> >> I've seen RServer and Mammoth Replication - these look good (on paper) >> but >> both appear to involve a license fee (which is more than I have to >> spend >> right now). > > There's 2 versions of eRServer - v1.2 is free on gborg and Postgresql, > Inc. has another > that you get with a support contract. > > I've patched eRServer pretty heavily to fix some bugs and add some > usability. The > eRServer list has some discussion of these. > > Jan Weick is in the early stages of developing a new replication system > that > looks to be pretty nice. He also has a project set up on gborg. > > In addition, RServ (what eRServer was based on) is still going, along > with dbmirror > (which is in contrib in the main distribution). > > Mammoth is something you would have to ask Joshua Drake about. Don't > know anything about it. > >> >> Is pgReplication ready for 7.4 yet? And are there any other *free* >> options? If not maybe I'll have to find the money for a commercial >> solution. > > Doesn't look like based on the web site at gborg. > >> >> I'd appreciate any feedback from anyone who has a working replication >> solution. >> > > So far eRServer is working ok for me. If you use the unpatched version > on gborg, be sure to > read the erserver list archives, as the current tarball has a problem > or two. If you want to > work with my fork let me know. I like to think it works better, but I'm > hardly unbiased... > >> Thanks >> >> John Sidney-Woollett >> >> >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 9: the planner will ignore your desire to choose an index scan if >> your >> joining column's datatypes do not match >> > -------------------- > > Andrew Rawnsley > President > The Ravensfield Digital Resource Group, Ltd. > (740) 587-0114 > www.ravensfield.com > >
Check out the pg_replicator project on gborg, IIRC it has links to some
of the replication solutions.... USOGRES comes to mind...
BTW - I'f you do give each one a twirl, we'd love to hear about your
experiences.
Robert Treat
On Wed, 2003-12-17 at 03:15, John Sidney-Woollett wrote:
> Apologies if this is a double post - I'm sure I sent a similar e-mail to
> the list, but it seems to have disappeared in the ether.
>
> Id there a definitive list of what replication options are currently
> available for 7.4 (and 7.5), and what their relative strengths and
> weaknesses are; ease of use, configuration, cost, support etc
>
> I need to start tackling replication of our DB in the next few weeks, and
> wondered what the options are.
>
> I've seen RServer and Mammoth Replication - these look good (on paper) but
> both appear to involve a license fee (which is more than I have to spend
> right now).
>
> Is pgReplication ready for 7.4 yet? And are there any other *free*
> options? If not maybe I'll have to find the money for a commercial
> solution.
>
> I'd appreciate any feedback from anyone who has a working replication
> solution.
>
> Thanks
>
> John Sidney-Woollett
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
On Thu, Dec 18, 2003 at 03:40:44PM -0500, Robert Treat wrote:
> >
> > I've seen RServer and Mammoth Replication - these look good (on paper) but
> > both appear to involve a license fee (which is more than I have to spend
> > right now).
For the record, there is an open source version of erserver. It's
available on gborg.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Afilias Canada Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
Robert
Thanks for the info.
I'll keep the list posted with feedback after I decide which product to go
with.
John
Robert Treat said:
> Check out the pg_replicator project on gborg, IIRC it has links to some
> of the replication solutions.... USOGRES comes to mind...
>
> BTW - I'f you do give each one a twirl, we'd love to hear about your
> experiences.
>
> Robert Treat
>
> On Wed, 2003-12-17 at 03:15, John Sidney-Woollett wrote:
>> Apologies if this is a double post - I'm sure I sent a similar e-mail to
>> the list, but it seems to have disappeared in the ether.
>>
>> Id there a definitive list of what replication options are currently
>> available for 7.4 (and 7.5), and what their relative strengths and
>> weaknesses are; ease of use, configuration, cost, support etc
>>
>> I need to start tackling replication of our DB in the next few weeks,
>> and
>> wondered what the options are.
>>
>> I've seen RServer and Mammoth Replication - these look good (on paper)
>> but
>> both appear to involve a license fee (which is more than I have to spend
>> right now).
>>
>> Is pgReplication ready for 7.4 yet? And are there any other *free*
>> options? If not maybe I'll have to find the money for a commercial
>> solution.
>>
>> I'd appreciate any feedback from anyone who has a working replication
>> solution.
>>
>> Thanks
>>
>> John Sidney-Woollett
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: the planner will ignore your desire to choose an index scan if
>> your
>> joining column's datatypes do not match
>
> --
> Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
>
>
> > Mammoth is something you would have to ask Joshua Drake about. Don't > know anything about it. Just FYI: There is very detailed information about Mammoth Replicator located here: http://www.commandprompt.com/entry.lxp?lxpe=304 Sincerely, Joshua D. Drake > >> >> Is pgReplication ready for 7.4 yet? And are there any other *free* >> options? If not maybe I'll have to find the money for a commercial >> solution. > > > Doesn't look like based on the web site at gborg. > >> >> I'd appreciate any feedback from anyone who has a working replication >> solution. >> > > So far eRServer is working ok for me. If you use the unpatched version > on gborg, be sure to > read the erserver list archives, as the current tarball has a problem or > two. If you want to > work with my fork let me know. I like to think it works better, but I'm > hardly unbiased... > >> Thanks >> >> John Sidney-Woollett >> >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 9: the planner will ignore your desire to choose an index scan if >> your >> joining column's datatypes do not match >> > -------------------- > > Andrew Rawnsley > President > The Ravensfield Digital Resource Group, Ltd. > (740) 587-0114 > www.ravensfield.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
Attachment
Hello,
I'am using postgresql to make different web site with differente
database for each, but with same table/link/...
So, i change from 7.2 to 7.4 and try to use template function to create
each new DB more easier (with user posgres):
CREATE DATABASE myowntemplate TEMPLATE = template1;
After that, i create the differentes tables.
I create my user and create the db for each user :
CREATE DATABASE user_db OWNER = user TEMPLATE = myowntemplate;
Update data, and try it.
But i got a msg in the postgresql.log that my user have not the
permission to access the tables :
ERROR: permission denied for relation table1
ERROR: permission denied for relation table2
[...]
So maybe i made a mistake, by i thought by making "user" the owner of
"user_db", he will gain each priviliges on "myowndb".
Thanx for answers,
regards,