On Fri, 2006-10-06 at 18:06 -0400, Christopher Browne wrote:
> Jeff Davis <pgsql at j-davis.com> writes:
> > Now, you just replicate the one set of tables. It seems much more
> > maintainable also.
> 
> I think I like that idea.  It also has the merit of requiring rather
> fewer sequences :-).
> 
> Ah, yes.  Another thought...
> 
> Even if it turns out that having a lot of schemas and tables seems
> best, it would surely be nice if those tables pointed to some shared
> sequence in a central schema.  That would get rid of the "5000
> sequences" problem.  And it would also mean that transaction IDs and
> such (the things being populated using sequences) would be pretty
> disjoint even across the 5000-odd users.
> 

I like that idea. When you do that, do you normally use an INT8 just to
make sure? It seems like you'd burn through the values faster.

I think it would help because you're less likely to accidentally use a
valid reference. If you use a separate sequence for each table it seems
like the value 10 would be a valid reference to most tables, and you'd
be less likely to catch the mistake.

> A third thought [having too many of these today ;-)]...
> 
> This seems a lot like a case for stored procedures.  If there are a
> limited number of kinds of actions that can be performed, then setting
> up a small list of stored functions as an API would make the names of
> the tables totally irrelevant.
> 
> I'm working on an application that takes that approach: the
> application code (in C++, as it happens) never issues an INSERT,
> DELETE, UPDATE.  It instead calls stored functions which can hide a
> fair amount of the intelligence and business logic on the DB side.
> That does simplify the application a fair bit.

Interesting. I am a little wary of that, mostly because there isn't a
clear solution to the plpgsql saved plan invalidation yet. That's not an
inherent problem in your solution, I just think that if I used that many
functions I would lose track and get bitten by a cached plan. However,
it's a great design principle and I would like to try making an
application that follows that philosophy. Perl would work fine I'm sure.

Regards,
	Jeff Davis