On 8/23/2004 2:20 PM, Darren wrote:

> Thanks. I won't do that then. 
> On the subject of adding primary keys to tables do you or anyone else see any 
> issues with doing something like this:
> 
> Create a bigint field in a table.
> Assign the value of the oid in each row to the new key field - UPDATE 
> table_name SET keyfield = oid;
> Then make the new field the primary key for the table.
> 
> Any traps for the unwary there? Or should I just use the table add key command 
> for slonik?

Add a bigint field to the table. Create a unique index on field. Set the 
default value to be nextval of some (also replicated) sequence. UPDATE 
tab SET field = nextval() WHERE field IS NULL;. Set field to be NOT 
NULL. Use that as the key for Slony.

OID's are not guaranteed to be unique.


Jan

> 
> Thanks.
> Darren
> 
> 
> On Friday 20 August 2004 05:10, Jan Wieck wrote:
>> On 8/19/2004 11:47 PM, Darren wrote:
>> > Hello,
>> >
>> > I ran across something today when trying to use oids as primary keys. The
>> > slon daemon will create SQL like this this:
>> >  UPDATE table_name SET field = value WHERE ;
>> >
>> > with no clause for the where. Of course this fails.
>> > Is this just something that shouldn't be done? Or might slony be able to
>> > make use of tables that use the oid as a primary key? I ask mainly
>> > because using the oid is an easy way to retrofit a primary key onto
>> > existing tables in a clean manner.
>>
>> Using OID's as primary keys is neither clean, nor reliable. Do not get
>> fooled by the fact that most of the system catalogs use OID's for their
>> primary key. If you use OID's for your data primary keys, you not only
>> start running through them at a higher rate, you also force yourself
>> into a situation where you cannot cleanup your system even with a dump
>> and restore in the case where your OID counter rolls over. And believe
>> me, a rollover of the OID counter isn't funny.
>>
>> A lot of special logic would be necessary to support system attributes
>> as row identifiers in slony. I don't have any plans to do so.
>>
>>
>> Jan


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck at Yahoo.com #