Thread

1. Sequence question.

   Anthony Best <abest@digitalflex.net> — 2003-12-17T01:12:47Z

   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.
   
   
   

2. Re: Sequence question.

   John Sidney-Woollett <johnsw@wardbrook.com> — 2003-12-17T07:57:24Z

   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
   >
   
   
   

3. Re: Sequence question

   John Sidney-Woollett <johnsw@wardbrook.com> — 2003-12-17T08:08:05Z

   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
   >
   
   
   

4. Replication

   John Sidney-Woollett <johnsw@wardbrook.com> — 2003-12-17T08:15:04Z

   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
   
   
   
   

5. Re: Replication

   Andrew Rawnsley <ronz@ravensfield.com> — 2003-12-17T14:22:24Z

   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
   
   
   

6. Re: Replication

   John Sidney-Woollett <johnsw@wardbrook.com> — 2003-12-18T08:16:59Z

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

7. Re: Replication

   Robert Treat <xzilla@users.sourceforge.net> — 2003-12-18T20:40:44Z

   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
   
   
   

8. Re: Replication

   Andrew Sullivan <andrew@libertyrms.info> — 2003-12-18T21:24:00Z

   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
   
   
   

9. Re: Replication

   John Sidney-Woollett <johnsw@wardbrook.com> — 2003-12-18T21:35:52Z

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

10. Re: Replication

    Joshua D. Drake <jd@commandprompt.com> — 2004-02-19T18:10:41Z

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

11. Postgresql 7.4.1 - Template database and user permission

    Froggy / Froggy Corp. <froggy@froggycorp.com> — 2004-04-19T16:52:26Z

    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,