On 03/19/2015 09:57 AM, Dave Cramer wrote:
>
> On 19 March 2015 at 09:51, Steve Singer <ssin...@afilias.info
> <mailto:ssin...@afilias.info>> wrote:
>
> On 03/19/2015 06:32 AM, Dave Cramer wrote:
>
> Actually I think it has been fixed in bug 349 (Thanks Chris)
> However I'd
> like to integrate schema changes into something like flyway.
> https://github.com/flyway/
>
> Not sure how this best plays out yet.
>
>
>
> I think it would be great to see slony support in some schema
> management tools such as flyway,alembic or liquibase
>
> I see two approaches worth considering
>
> 1) Have the schema managment tool Call the slonik executable and
> pass in a slonik script.
>
>
> While I see the merit in this from your POV it is rather "blocky" and
> sort of breaks the spirit of flyway (the only one I have seriously
> looked at)
>
>
> 2) Call the SQL stored procedures directly. This would involve calling
> _slonyschema.ddlCapture for each SQL statement. Then calling
> ddlScript_complete()
>
>
> Yes, this is where I was headed. Unfortunately flyway appears to call
> their hooks as a transaction. I have asked them to provide a hook
> without a transaction.
>
What do you mean 'as a transaction' I think you could do those commands
as part of larger transaction.
The EXECUTE SCRIPT cases are an easy example (in slony 2.2+) because
they don't generate any events or involve waiting for events. I think
you could call those functions as part of a larger migration operation.
A much harder case is 'create a table, and add it to my existing
replication set'.
The slonik for this would look something like
create set(id=1234, origin=1);
set add table(set id=1234, fully qualified name='public.some_new_table');
subscribe set(set id=1234, provider=1,receiver=2);
--repeat the subscribe set for all nodes that get the set
merge set(id=1,add id=1234, origin=1);
Slonik will issue many different stored function calls, commit
transactions , wait for things to replicate and then move onto the next
command. Your not going to be able to do something like above as part
of a single-uncommitted transaction. Even if we had a stable API the
burden of calling those functions , in the right order , at the right
time, against the correct node isn't trivial (slonik.c is over 6000
lines of code)
In the past I've thought about creating something like a pl/slonik.
Maybe where the admin conn_info strings are available somewhere on each
database server then you do something in SQL like
DO $$
create set(id=1234, origin=1);
set add table(set id=1234,
fully qualified name='public.some_new_table');
subscribe set(set id=1234, provider=1,receiver=2);
--repeat the subscribe set for all nodes that get the set
merge set(id=1,add id=1234, origin=1);
$$ language plslonik;
This still couldn't be transactional, the pl/slonik interperter would
then need to open up libpq connections to both the local database and
all the remote databases using the admin conninfo connection data to
actually issue the commands slonik issues today.
>
> I would recommend against trying to duplicate the functions in
> ddlScript capture or ddlScript_complete. I'll describe what they do
> in 2.2
> for interest sake but I don't see any reason why you would want to
> re-invent this code.
>
> Agreed.
>
> The advantage of just using option 1 is that we (the slony
> developers) try to keep the slonik command syntax somewhat stable
> between releases but we reserve the right to change the API of the
> stored functions as we desire. We don't consider the stored
> functions a stable API.
> The pg-admin folks use the stored functions directly and keeping
> pg-admin working with the various slony versions and API changes
> in something they need to keep on-top of. You would have to take on
> a similar responsibility.
>
>
> Any reason why you couldn't have a stable API here. Somewhat higher
> level than the current functions which would allow you to still do
> whatever you wanted below this layer ?
>
>
> If you to teach your schema management tool to do more than just DDL
> but also do things like add new tables to a
> replication set then the job of doing this directly with the stored
> procedures becomes larger. Slonik often does more than just call
> the stored functions.
>
>
> One step at a time ;)
>
> Dave
>
>
>
> Here are the major steps the ddlScript_capture stored function
> performs when submitting DDL to run on all nodes
>
> * Captures the values of any replicated sequences
> * Inserts the ddl into the sl_log_script table
> * Executes the DDL submitted
>
> The ddlScript_complete call will then
> * Add a row to sl_log_script indicating the DDL is complete
> * Call the updateRelName functions to check for any table renames
> * Call repair_log_triggers to fix the kkvv encoding for the
> trigger arguments
>
>
>
>
> Dave Cramer
>
> On 19 March 2015 at 05:47, Glyn Astill <glynast...@yahoo.co.uk
> <mailto:glynast...@yahoo.co.uk>
> <mailto:glynast...@yahoo.co.uk
> <mailto:glynast...@yahoo.co.uk>__>> wrote:
>
> > From: Dave Cramer <davecra...@gmail.com
> <mailto:davecra...@gmail.com>
> <mailto:davecra...@gmail.com <mailto:davecra...@gmail.com>>>
> >To: slony <slony1-general@lists.slony.__info
> <mailto:slony1-general@lists.slony.info>
> <mailto:slony1-general@lists.__slony.info
> <mailto:slony1-general@lists.slony.info>>>
> >Sent: Wednesday, 18 March 2015, 13:50
> >Subject: [Slony1-general] replicating execute script
> >
> >
> >
> >Due to the usage of capital letters in the slony cluster
> execute script fails.
> >
> >
> >I am looking to replicate execute script for DDL changes.
> From what I can see execute script takes a lock out on sl_lock
> before executing the script, and releases it at the end.
> >
> >
> >What else am I missing ?
> >
>
>
> Well it looks like ddlscript_complete calls updateRelname() and
> repair_log_triggers(true), so I guess that's part of it.
>
>
> Wouldn't it be better to try and patch execute script to
> handle your
> case sensitivity issue?
>
>
>
>
> _________________________________________________
> Slony1-general mailing list
> Slony1-general@lists.slony.__info
> <mailto:Slony1-general@lists.slony.info>
> http://lists.slony.info/__mailman/listinfo/slony1-__general
> <http://lists.slony.info/mailman/listinfo/slony1-general>
>
>
>
_______________________________________________
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general
