Sequence question.

[Anthony Best]

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 ma*******@postgresql.org

[John Sidney-Woollett]

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 ma*******@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[John Sidney-Woollett]

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 ma*******@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[John Sidney-Woollett]

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

[Andrew Rawnsley]

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.
[phone]
www.ravensfield.com
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[John Sidney-Woollett]

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.
[phone]
www.ravensfield.com

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Robert Treat]

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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Andrew Sullivan]

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
<an****@libertyrms.info> M2P 2A8
[phone] x110
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[John Sidney-Woollett]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Joshua D. Drake]

>

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.
[phone]
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.
[phone] - [e-mail] - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
---------------------------(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

[Froggy / Froggy Corp.]

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,

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend