Re: Postgres-R: primary key patches - Mailing list pgsql-hackers

markus@bluegap.ch (Markus Wanner) writes:
> chris wrote:
>> I agree with you that tables are *supposed* to have primary keys;
>> that's proper design, and if tables are missing them, then something
>> is definitely broken.
>
> Ah, I see, so you are not concerned about tables with a PRIMARY KEY
> for which one wants another REPLICATION KEY, but only about tables
> without a PRIMARY KEY, for which one doesn't want a PRIMARY KEY in the
> first place.

"Doesn't want" is probably overstating the matter.

I'll describe a scenario to suggest where it might happen.

- A system is implemented, using the database, and, for some reason, no PRIMARY KEY is defined for a table.  Someone
forgot;it got misconfigured; a mistake was probably made.
 

- The system then goes into production, and runs for a while.  The table has data added to it, and starts to grow
ratherlarge.
 

- At THIS point, we decide to introduce replication, only to discover that there isn't a PRIMARY KEY on the table.

Ideally, we'd take an outage and add the primary key.  But suppose we
can't afford to do so?

The "add indexes concurrently" added in 8.3 (if memory serves) *would*
allow us to create a *candidate* primary key without forcing an
outage.

In theory, we'd like to have a true primary key.  Sometimes
operational issues get in the way.

> However, that's a general limitation of replication at tuple level:
> you need to be able to uniquely identify tuples. (Unlike replication
> on storage level, which can use the storage location for that).

No disagreement; yes, we certainly do need a way to uniquely identify
tuples, otherwise we can't replicate UPDATE or DELETE.

>> Sometimes, unfortunately, people make errors in design, and we wind up
>> needing to accomodate situations that are "less than perfect."
>>
>> The "happy happenstance" is that, in modern versions of PostgreSQL, a
>> unique index may be added in the background so that this may be
>> rectified without outage if you can live with a "candidate primary
>> key" rather than a true PRIMARY KEY.
>
> I cannot see any reason for not wanting a PRIMARY KEY, but wanting
> replication, and therefore a REPLICATION KEY.
>
> Or are you saying we should add a hidden REPLICATION KEY for people
> who are afraid of schema changes and dislike a visible primary key? 
> Would you want to hide the underlying index as well?

The scenario I outline above hopefully answers this.  It's not a
matter that I expect people to specifically desire not to have a
primary key.  Instead, I expect cases where mistakes compound with
operational issues to make them say "Ow - I can't do that now!"
-- 
select 'cbbrowne' || '@' || 'linuxfinances.info';
http://cbbrowne.com/info/lsf.html
Rules  of the  Evil Overlord  #145. "My  dungeon cell  decor  will not
feature exposed pipes.  While they add to the  gloomy atmosphere, they
are good  conductors of vibrations and  a lot of  prisoners know Morse
code." <http://www.eviloverlord.com/>