slight improvement:


select 'set add table (set id=1, origin=1, id='||(select count(*) from
information_schema.tables as t1 where t2.table_name >= t1.table_name and
table_schema in ('public') and table_type = 'BASE TABLE') || ', fully
qualified name = \'' || table_schema || '.' || table_name || '\');' from
information_schema.tables as t2 where table_schema in ('public') and
table_type = 'BASE TABLE' order by table_name;

On Fri, 2004-07-23 at 18:15 -0400, Christopher Browne wrote:
> Thomas F. O'Connell <tfo at sitening.com> writes:
> >> My intent is to throw another choice up against the wall, so we can
> >> see what's going to stick better.
> >
> > Sure. Makes sense. I guess I'm just curious as to how the development
> > process around slony is going to congeal.
> 
> If you contribute code, that will presumably influence how it develops.
> 
> Good questions can be a good contribution, but are certainly a lesser
> influence.
> 
> >>> For instance, why not create slonik commands like
> >>>
> >>> SET ADD UNIQUELY CONSTRAINED TABLES
> >>> SET ADD UNCONSTRAINED TABLES
> >>> SET ADD ALL TABLES
> >>> SET ADD ALL SEQUENCES
> >>
> >> Because there may be some table that SHOULDN'T be replicated.
> >>
> >> In our applications, for instance, there are all the tables that are
> >> "officially" part of the server application, and then there are some
> >> utility tables that the DBAs periodically create when looking to
> >> repair some problem that has occurred.
> >>
> >> If we said "replicate all tables," that would include cruddy ones that
> >> perhaps should be thrown away.
> >
> > Sure, but shouldn't that be up to the DBA to decide? I mean, including
> > a DELETE command in SQL sure allows you to do destructive things to
> > your data. Adding all tables
> 
> I kind of like having a "belt and suspenders" approach; the more
> opportunities we have to allow avoiding mistakes, the better.
> Promoting mistakes by automagically copying every cruddy table over
> is, in effect, a promotion of mistakes.
> 
> In a backup system, there typically need to be choices made as to what
> filesystems are to be backed up, possibly even what files.  This is a
> place where it seems to make sense to be even more precise about what
> is copied.
> 
> >> A better approach would be to request all tables in a particular
> >> namespace, as it is somewhat more likely that a namespace will be kept
> >> clean.
> >
> > I like this idea.
> >
> >>>> Stick those three things in a config file, and it's dead easy to
> >>>> generate a suitable "create set" that first adds PK candidates and
> >>>> then builds the set of all the tables.
> >
> > I forgot to ask this question earlier: Do you have an example of
> > this "dead easy" process? I'm currently writing a meta-slonik
> > utility for generation of my three lists (constrained tables,
> > unconstrained tables, and sequences), but there's no point in
> > reinventing the wheel if someone has already done this work.
> 
> If you want _all_ the tables in a particular namespace, then you might
> try something like:
> 
>  select table_schema || '.' || table_name 
>  from information_schema.tables
>  where table_schema in ('app1', 'app2', 'app3') and
>        table_type = 'BASE TABLE';
> 
> That grabs the fully-qualified table names for all tables in
> namespaces app1, app2, and app3.  It's easy to then cut'n'paste that
> into a configuration file.
> 
> >>> Again, my point would be why not (plan to) have slonik handle this
> >>> work for you?
> >>
> >> Because it may take human planning to get this to all take place at an
> >> appropriate time.
> >
> > I feel like the convenience would make up for it. I mean, I'm having
> > to do a lot of human planning right now to work around the lack of
> > automatic table specification procedures.
> 
> The problem is that my exclusions from "all the tables" may not
> involve the same policy as your exclusions.  I don't know your
> policies; you don't know mine; we don't have any basis to imagine that
> they are compatible.  And if they are not compatible, an policy that
> gets coded into Slony is liable to be unacceptable to some of us.
> 
> >> For instance, suppose the database is rather large, and you have to
> >> very carefully schedule when the "unique key" creation will take
> >> place on some critical table, because that will lead to an
> >> application outage.  In that case, having slonik "magically handle
> >> this" would be downright undesirable.
> >
> > Well, that's why I mentioned having the ability to add tables
> > according to whether or not they're constrained or not.
> 
> Again, that means having to encode some policy.  I can't imagine what
> policy to choose to try to do a Quick&Dirty encoding...
> 
> >>> I mean, what's the difference between a slonik command that grabs
> >>> all user relations and postgres itself in general. If a DBA trusts
> >>> the DBMS, then the DBA ought to be able to have some trust in the
> >>> extended development community that organizes around the DBMS.
> >>
> >> That only works if we're looking at:
> >>  - Development in the small, where everyone knows everyone, and
> >>  - Development under a tightly controlled set of DB management
> >>    doctrines.
> >
> > So is the doctrine that differentiates PostgreSQL the fact that it's
> > an implementation of SQL, which is an accepted standard?
> 
> This is all about policy.  The policies in a small organization will
> differ from those in a large one, for instance.
> 
> >> If there's any reason to need to be _careful_ about what data gets
> >> replicated, then magick becomes a major misfeature.
> >
> > Yeah, I guess the entire process is dependent on the trust of the
> > user community. I tend not to be too suspicious of magick when I
> > trust a development community and have been actively following the
> > development process. I know that not everyone in the postgres/slony
> > user community fits that category, though.
> >
> > To me the question boils down to simplicity/convenience vs. trust (in
> > both the software and my abilities as a DBA type). I have the trust,
> > but the features that would give me the simplicity/convenience I'd
> > like to have aren't there, yet.
> 
> But I don't know what policies are the ones to promote :-(.
> 
> > I think that eventually slony is going to need to include some
> > semi-automated way of helping the user select the relations to be
> > replicated. It sounds like the consensus is developing around a
> > broad set of user tools, some of which might end up perl-based, some
> > of which might end up web-based, and some of which might end up
> > GUI-based. Optimally, there would be some logical way in which each
> > of these solutions fit the general development structure and
> > strategy of slony, and that a robust, cohesive feature set is
> > available to all approaches (which I still think should include some
> > combination of slonik and/or slony configuration files).
> 
> It is also possible that some tools will configure things based on
> running the stored procedures that are the _real_ basis for it all.  I
> spent a considerable chunk of this week contending with generating
> Slonik scripts, and I'm not certain it's "growing on me" :-(.
> 
> I don't think anyone has final answers on any of this yet.
Anarchy may not be the best form of government, but it's better than no
government at all.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part
Url : http://gborg.postgresql.org/pipermail/slony1-general/attachments/20041230/88f77848/attachment.bin