Hi again --

I hate to be a bore about the execute script problem I raised a few
days ago, but I would dearly love to get clear about it, and any
clarification, confirmation or rebuttal would be helpful, so please
forgive the repost. 

To recap what I concluded, but which I hoped to be shown was mistaken:
the problem is a fairly general one, and should arise whenever you
need to alter a table upon which a view depends in such a way that the
view needs to be redefined.  What I thought was the ideal way, namely,
to drop the view, alter the table, and recreate the view in one
transaction, does not work with execute script, as the test case below
demonstrates.  It is necessary to run execute script twice, recreating
the view in a second pass; the view in question won't exist for some
period of time, so some downtime is necessary.

Is this right?  If so, is it unavoidable?

Cheers,

js

On Thu, Jun 23, 2005 at 12:42:03AM -0400, Jacob Smullyan wrote:
> As I should have done at first, I've prepared a simple test case which
> shows my execute script failure with all the irrelevant cruft removed:
> 
>   http://www.smullyan.org/smulloni/executescript2.tar.gz
> 
> The test creates a table with 1 column, and a (vacuous) view which
> just does a select from the table.  The script passed to execute
> script drops the view, alters the table, and recreates the view,
> referencing the new column.  The create view statement fails (claiming
> the column does not exist).
> 
> I also include an alter table alter column statement affecting the new
> column before the create view statement; interestingly, this succeeds
> (that is, if you comment out the create view).  I'm curious why this
> is.
> 
> I can successfully run the script in two passes -- one that drops the
> view and alters the table, and another than recreates the view.  Of
> course, this commits the db before the view that is dropped is
> recreated, which isn't too happy a solution; not only because of the
> implied downtime, but because complex ddl would have to be reordered
> and divided up (according to not particularly obvious criteria) into
> slony-digestible portions, which would be a PITR (which in this case
> means: Pain In The Rump).
> 
> I hope, therefore, that you'll classify this as a bug and not as a
> feature or fact of life!
> 
> Cheers,
> 
> js
> 
> -- 
> Jacob Smullyan



> _______________________________________________
> Slony1-general mailing list
> Slony1-general at gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/slony1-general


-- 
Jacob Smullyan
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: not available
Url : http://gborg.postgresql.org/pipermail/slony1-general/attachments/20050628/e7353627/attachment.bin