Re: Postgres-R: primary key patches

Hi,

chris wrote:
> 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 rather large.
>
> - At THIS point, we decide to introduce replication, only to discover
> that there isn't a PRIMARY KEY on the table.

Yeah, that's the situation I had in mind as well.

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

You are assuming that one doesn't need to take an outage to start
replication in the first place. As Postgres-R comes with system catalog
changes, that's not the case. You will at least need to restart the
postmaster, without some sort of system catalog upgrading (which doesn't
currently exists) you even need a full dump/restore cycle.

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

Postgres-R is primarily being developed for *future* versions of
Postgres, I don't see any point in back porting something that is not
complete for the current version, yet.

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

Yup, that's the real issue here.

> The scenario I outline above hopefully answers this.

I see the problem of wanting to replicate tables which didn't have a
PRIMARY KEY before. But I still cannot see a use case for hiding indices
or keys.

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

Yeah, these issues certainly need to be addressed. I think the ability
to add a hidden column and a (visible!) primary key on that column
should help in that case.

Thinking about index creation time doesn't make sense, as long as we
still need a dump/restore cycle to setup replication. And even then,
that operational issue has nothing to do with the question of hiding the
newly generated index or not.

Regards

Markus