Обсуждение: Using oid with RServ w/ Postgresql 7.2

Hey all,

So I have rserv set up, the problem we had originally was that when you
tell rserv what field to use to replicate you could only specify 1
field.

This doesn't exactly work for most of our tables as we use multi field
keys.

However, we tried it out using the "oid" field, and it seems to work.

My question would then be, are there any problems/reasons or hints with
using the oid field as the field that the rserv trigger is set on?

We will be using rserv in a production environment so I'm looking at
this as not just an academic solution but a real world one.

If anyone has any comments, thoughts or hints they would be appreciated.

Sincerely,

Will LaShell

Will LaShell <will@lashell.net> writes:
> My question would then be, are there any problems/reasons or hints with
> using the oid field as the field that the rserv trigger is set on?
> We will be using rserv in a production environment so I'm looking at
> this as not just an academic solution but a real world one.

This is risky for a long-lived database.  Things will work fine until
the OID counter wraps around (ie, more than 4 billion rows inserted
into your database).  After that you have a risk of OID collisions.

You can prevent the worst problems by installing a unique index on OID
on each replicated table; but then you may occasionally get unexpected
"duplicate key" errors.

My advice would be to add a serial8 column to each table and use that
as the replication primary key.

            regards, tom lane

On Thu, 2002-10-17 at 18:55, Tom Lane wrote:
> Will LaShell <will@lashell.net> writes:
> > My question would then be, are there any problems/reasons or hints with
> > using the oid field as the field that the rserv trigger is set on?
> > We will be using rserv in a production environment so I'm looking at
> > this as not just an academic solution but a real world one.
>
> This is risky for a long-lived database.  Things will work fine until
> the OID counter wraps around (ie, more than 4 billion rows inserted
> into your database).  After that you have a risk of OID collisions.
>
> You can prevent the worst problems by installing a unique index on OID
> on each replicated table; but then you may occasionally get unexpected
> "duplicate key" errors.
>
> My advice would be to add a serial8 column to each table and use that
> as the replication primary key.
>

Hrmm, yea, I guess I was hoping to avoid the problem of adding a column
to all of our tables that didn't really serve a purpose outside of being
a replication id.

Is rserv going to be moving into the core of Postgresql as the
replication system? If not, what type of replication is planned to be
done.  I know that you all are working on it and it is probably(?)  your
most requested feature next to point in time recovery.

Does anyone know why rserve doesn't support/use multi-field keys for the
replication id? Or the primary key if one is defined? I assume its for
ease of coding?

Sincerely,
Will LaShell

Will LaShell <will@lashell.net> writes:
> Does anyone know why rserve doesn't support/use multi-field keys for the
> replication id?

Well, the one in contrib/ is only a prototype.  The version that
PostgreSQL Inc sells commercially might have support for multi-field
keys by now.  You'd have to ask them though ...

            regards, tom lane

Hmmm,

you may consider contrib/dbmirror which provides replication
without having to add any new column. but you still need to have at
least one column as a "PRIMARY KEY".

I am trusting it in one of my production systems.

Regds
Mallah.



> On Thu, 2002-10-17 at 18:55, Tom Lane wrote:
>> Will LaShell <will@lashell.net> writes:
>> > My question would then be, are there any problems/reasons or hints with using the oid field
>> > as the field that the rserv trigger is set on? We will be using rserv in a production
>> > environment so I'm looking at this as not just an academic solution but a real world one.
>>
>> This is risky for a long-lived database.  Things will work fine until the OID counter wraps
>> around (ie, more than 4 billion rows inserted into your database).  After that you have a risk
>> of OID collisions.
>>
>> You can prevent the worst problems by installing a unique index on OID on each replicated
>> table; but then you may occasionally get unexpected "duplicate key" errors.
>>
>> My advice would be to add a serial8 column to each table and use that as the replication
>> primary key.
>>


> Hrmm, yea, I guess I was hoping to avoid the problem of adding a column to all of our tables
> that didn't really serve a purpose outside of being a replication id.
>
> Is rserv going to be moving into the core of Postgresql as the
> replication system? If not, what type of replication is planned to be done.  I know that you
> all are working on it and it is probably(?)  your most requested feature next to point in time
> recovery.
>
> Does anyone know why rserve doesn't support/use multi-field keys for the replication id? Or the
> primary key if one is defined? I assume its for ease of coding?
>
> Sincerely,
> Will LaShell



-----------------------------------------
Get your free web based email at trade-india.com.
   "India's Leading B2B eMarketplace.!"
http://www.trade-india.com/

On Thu, 2002-10-17 at 18:55, Tom Lane wrote:
> Will LaShell <will@lashell.net> writes:
> > My question would then be, are there any problems/reasons or hints with
> > using the oid field as the field that the rserv trigger is set on?
> > We will be using rserv in a production environment so I'm looking at
> > this as not just an academic solution but a real world one.
>
> This is risky for a long-lived database.  Things will work fine until
> the OID counter wraps around (ie, more than 4 billion rows inserted
> into your database).  After that you have a risk of OID collisions.
>
> You can prevent the worst problems by installing a unique index on OID
> on each replicated table; but then you may occasionally get unexpected
> "duplicate key" errors.
>
> My advice would be to add a serial8 column to each table and use that
> as the replication primary key.
>


Just out of curiousity, when OID wraparound happens, won't there be
bigger problems?

Will LaShell <will@lashell.net> writes:
> On Thu, 2002-10-17 at 18:55, Tom Lane wrote:
>> This is risky for a long-lived database.  Things will work fine until
>> the OID counter wraps around (ie, more than 4 billion rows inserted
>> into your database).  After that you have a risk of OID collisions.
>>
>> You can prevent the worst problems by installing a unique index on OID
>> on each replicated table; but then you may occasionally get unexpected
>> "duplicate key" errors.

> Just out of curiousity, when OID wraparound happens, won't there be
> bigger problems?

No, occasional "duplicate key" errors are about what you'd have to deal
with, assuming you have unique indexes on OID on every table where OID
matters to you.

You might also have some problems with pg_dump dumping things in a funny
order, because it relies on OID comparisons to try to guess the creation
order of objects.  We have plans to fix that in the future (maybe for
7.4), but for now it'd mean you'd need to manually edit the dump file
to fix the dump order.  (Or use the custom dump format and make use of
pg_restore's ability to restore things in a specified order.)

            regards, tom lane

On Fri, Oct 18, 2002 at 09:44:46AM -0400, Tom Lane wrote:
> Will LaShell <will@lashell.net> writes:
> > Does anyone know why rserve doesn't support/use multi-field keys for the
> > replication id?
>
> Well, the one in contrib/ is only a prototype.  The version that
> PostgreSQL Inc sells commercially might have support for multi-field
> keys by now.  You'd have to ask them though ...

It doesn't.  The best answer really is to use a sequence and a key
field just for this.  (In fact, the rserv key field _must_ be unique,
or the whole thing breaks horribly.  Don't ask me how I know ;-)

I understand that dbmirror uses the primary key on each table, but it
can't handle multi-column primary keys either, so it's no help.

For what it's worth, the additional overhead on our tables hasn't
been enough for me to regret the implementation of rserv.  We use the
commercial version, by the way.  The prototype version isn't terribly
fast.  Maybe dbmirror is a better free choice, but I don't know, as I
haven't tested it.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110