|
Home > Archive > Slony1 PostgreSQL Replication > February 2006 > DDL replication ...
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
DDL replication ...
|
|
| Marc G. Fournier 2006-02-25, 9:50 am |
|
Is there any work being done on Slony-I replicating DDL's? And, of
course, setting up replication on any TABLES created in the process?
| |
| Andrew Sullivan 2006-02-25, 9:50 am |
| On Wed, Feb 22, 2006 at 12:48:22PM -0400, Marc G. Fournier wrote:
>
> Is there any work being done on Slony-I replicating DDL's? And, of
> course, setting up replication on any TABLES created in the process?
It's already in there. That's what execute is for.
A
--
Andrew Sullivan | ajs-oaT0K0jot5/q2IAV+ODieA@public.gmane.org
"The year's penultimate month" is not in truth a good way of saying
November.
--H.W. Fowler
| |
| Darcy Buskermolen 2006-02-25, 9:50 am |
| On Wednesday 22 February 2006 08:48, Marc G. Fournier wrote:
> Is there any work being done on Slony-I replicating DDL's? And, of
> course, setting up replication on any TABLES created in the process?
No there is not any work on this.. in order to support this it would require
hacks to the PG backend.. This could probably be done with Gavin's system
table trigger patches. The patches were never accepted for inclusion by
core.
> ____________________
____________________
_______
> Slony1-general mailing list
> Slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> http://gborg.postgresql.org/mailman.../slony1-general
--
Darcy Buskermolen
Wavefire Technologies Corp.
http://www.wavefire.com
ph: 250.717.0200
fx: 250.763.1759
| |
| Jim C. Nasby 2006-02-25, 9:50 am |
| On Wed, Feb 22, 2006 at 09:07:29AM -0800, Darcy Buskermolen wrote:
> On Wednesday 22 February 2006 08:48, Marc G. Fournier wrote:
>
> No there is not any work on this.. in order to support this it would require
> hacks to the PG backend.. This could probably be done with Gavin's system
> table trigger patches. The patches were never accepted for inclusion by
> core.
Any idea why? Being able to detect changes to tables certainly seems
like a good capability to have, beyond just Slony.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby-D/ iDPWeZeLdl57MIdRCFDg
@public.gmane.org
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
| |
| Darcy Buskermolen 2006-02-25, 9:50 am |
| On Wednesday 22 February 2006 11:40, Jim C. Nasby wrote:
> On Wed, Feb 22, 2006 at 09:07:29AM -0800, Darcy Buskermolen wrote:
>
> Any idea why? Being able to detect changes to tables certainly seems
> like a good capability to have, beyond just Slony.
Sorry, No off hand I can't remember why :/
--
Darcy Buskermolen
Wavefire Technologies Corp.
http://www.wavefire.com
ph: 250.717.0200
fx: 250.763.1759
| |
| Marc G. Fournier 2006-02-25, 9:50 am |
| On Wed, 22 Feb 2006, Jim C. Nasby wrote:
> On Wed, Feb 22, 2006 at 09:07:29AM -0800, Darcy Buskermolen wrote:
>
> Any idea why? Being able to detect changes to tables certainly seems
> like a good capability to have, beyond just Slony.
Just to jump in here, but I believe the issue is that you can't put a
trigger on a system table, which Gavin's patch apparently tries to address
.... if a trigger could be put onto something like pg_class, to detect a
table being added/removed, then the subsequent transmassion of an
appropriate CREATE command to the slave(s) wouldn't be too big of an issue
....
| |
| Rick Gigger 2006-02-25, 9:50 am |
|
On Feb 22, 2006, at 4:46 PM, Marc G. Fournier wrote:
> On Wed, 22 Feb 2006, Jim C. Nasby wrote:
>
>
> Just to jump in here, but I believe the issue is that you can't put a
> trigger on a system table, which Gavin's patch apparently tries to
> address
> ... if a trigger could be put onto something like pg_class, to
> detect a
> table being added/removed, then the subsequent transmassion of an
> appropriate CREATE command to the slave(s) wouldn't be too big of
> an issue
This seems like a good idea to me. Even if you don't want to put
forth effort to support DDL operations within an application it
protects against mistakes by the db admin. What if your dba forgets
to execute your ddl statements in slonk (or whatever it's called) and
accidentally runs them in psql. Now they don't get replicated. Now
you are replicating to a database with different table definitions.
What if the statements to be replicated are now invalid on the slave
but valid on the master. What happens then?
I agree that your admin should not be stupid and make a mistake like
that but there is no point in making it easier to shoot yourself in
the foot. It would be nice to be able to just update the master to
your hearts content have the slaves get updated as well.
What are the possible drawbacks to this? Why make the replication
harder to manager than it has to be? Does anyone know why the patch
was rejected?
Rick
| |
| Gavin Sherry 2006-02-25, 9:50 am |
| On Wed, 22 Feb 2006, Darcy Buskermolen wrote:
> On Wednesday 22 February 2006 08:48, Marc G. Fournier wrote:
>
> No there is not any work on this.. in order to support this it would require
> hacks to the PG backend.. This could probably be done with Gavin's system
> table trigger patches. The patches were never accepted for inclusion by
> core.
The issue, for me, is that there are two ways we could go about
implementing this and both have problems.
1) Create triggers on individual system tables.
If you wanted to know when a new table was created, when a table was
modified or dropped, you would create a trigger on pg_class, I suppose.
The problem, though, is that you might want to see what columns a new
table has but are they visible yet? It's a bit of a can of worms.
2) Create triggers on DDL
This seems to make more sense. You create a trigger on CREATE TABLE. I'm
not sure what you would pass the trigger in terms of data, but say you
managed to pass in some representation of the data surrounding the DDL
itself. There's a problem: what if people touch the system tables by hand?
There are other problems which plague both approaches: who has permission
to create such a trigger? What does CREATE TRIGGER ON CREATE DATABASE
mean? It's actually impossible to fire such triggers, it could be argued,
because they affect every database. This is also the case for CREATE
USER/ROLE, because it is a global operation.
It is far from a straight forward concept. The thing is though, the Slony
project is not the only project which wants such functionality: the JDBC
and other interface providers could greatly benefit because it would mean
that their client side metadata caching could be much more efficient.
Thoughts?
Gavin
| |
| Tim Allen 2006-02-25, 9:50 am |
| Gavin Sherry wrote:
>[...]
> There are other problems which plague both approaches: who has permission
> to create such a trigger? What does CREATE TRIGGER ON CREATE DATABASE
> mean? It's actually impossible to fire such triggers, it could be argued,
Even better, CREATE TRIGGER ON CREATE TRIGGER. You'd want to replicate
your triggers, right? ;-)
Tim
--
-----------------------------------------------
Tim Allen tim-PpQBR0/ s8wRtFtFawtkaSg@publ
ic.gmane.org
Proximity Pty Ltd http://www.proximity.com.au/
| |
| Christopher Kings-Lynne 2006-02-25, 9:50 am |
| > Even better, CREATE TRIGGER ON CREATE TRIGGER. You'd want to replicate
> your triggers, right? ;-)
Hell, what if you wanted your trigger to execute when someone creates a
trigger on create trigger?
CREATE TRIGGER ON CREATE TRIGGER ON CREATE TRIGGER
Chris
| |
| Jim C. Nasby 2006-02-25, 9:50 am |
| On Thu, Feb 23, 2006 at 03:02:14PM +1100, Gavin Sherry wrote:
> On Wed, 22 Feb 2006, Darcy Buskermolen wrote:
>
>
> The issue, for me, is that there are two ways we could go about
> implementing this and both have problems.
>
> 1) Create triggers on individual system tables.
>
> If you wanted to know when a new table was created, when a table was
> modified or dropped, you would create a trigger on pg_class, I suppose.
> The problem, though, is that you might want to see what columns a new
> table has but are they visible yet? It's a bit of a can of worms.
Visible?? You mean you're worried about about seeing stuff that hasn't
committed yet?
> 2) Create triggers on DDL
>
> This seems to make more sense. You create a trigger on CREATE TABLE. I'm
> not sure what you would pass the trigger in terms of data, but say you
> managed to pass in some representation of the data surrounding the DDL
> itself. There's a problem: what if people touch the system tables by hand?
Are there any operations (other than CREATE DATABASE) that don't touch a
system table?
> There are other problems which plague both approaches: who has permission
> to create such a trigger? What does CREATE TRIGGER ON CREATE DATABASE
> mean? It's actually impossible to fire such triggers, it could be argued,
> because they affect every database. This is also the case for CREATE
> USER/ROLE, because it is a global operation.
>
> It is far from a straight forward concept. The thing is though, the Slony
> project is not the only project which wants such functionality: the JDBC
> and other interface providers could greatly benefit because it would mean
> that their client side metadata caching could be much more efficient.
Absolutely. I think there's a ton of uses for this, including many
end-user applications (which is what slony is anyway...)
--
Jim C. Nasby, Sr. Engineering Consultant jnasby-D/ iDPWeZeLdl57MIdRCFDg
@public.gmane.org
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
| |
| Hannu Krosing 2006-02-25, 9:50 am |
| w5xoZWwga2VuYWwgcMOk
ZXZhbCwgTiwgMjAwNi0w
Mi0yMyBrZWxsIDE1OjAy
LCBraXJqdXRhcyBH
YXZpbiBTaGVycnk6Cj4g
Cj4gVGhlIGlzc3VlLCBm
b3IgbWUsIGlzIHRoYXQg
dGhlcmUgYXJlIHR3
byB3YXlzIHdlIGNvdWxk
IGdvIGFib3V0Cj4gaW1w
bGVtZW50aW5nIHRoaXMg
YW5kIGJvdGggaGF2
ZSBwcm9ibGVtcy4KPiAK
PiAxKSBDcmVhdGUgdHJp
Z2dlcnMgb24gaW5kaXZp
ZHVhbCBzeXN0ZW0g
dGFibGVzLgo+IAo+IElm
IHlvdSB3YW50ZWQgdG8g
a25vdyB3aGVuIGEgbmV3
IHRhYmxlIHdhcyBj
cmVhdGVkLCB3aGVuIGEg
dGFibGUgd2FzCj4gbW9k
aWZpZWQgb3IgZHJvcHBl
ZCwgeW91IHdvdWxk
IGNyZWF0ZSBhIHRyaWdn
ZXIgb24gcGdfY2xhc3Ms
IEkgc3VwcG9zZS4KPiBU
aGUgcHJvYmxlbSwg
dGhvdWdoLCBpcyB0aGF0
IHlvdSBtaWdodCB3YW50
IHRvIHNlZSB3aGF0IGNv
bHVtbnMgYSBuZXcK
PiB0YWJsZSBoYXMgYnV0
IGFyZSB0aGV5IHZpc2li
bGUgeWV0PyBJdCdzIGEg
Yml0IG9mIGEgY2Fu
IG9mIHdvcm1zLgoKSWYg
eW91IHdhbnQgdG8gc2Vl
IHRoZSBjb2x1bW5zLCB5
b3UgcHV0IHRyaWdn
ZXJzIG9uIHBnX2F0dHJp
YnV0ZSBhcwp3ZWxsLiBI
b3cgaXMgaXQgZGlmZmVy
ZW50IGZyb20gdHJp
Z2dlcnMgYW55IG90aGVy
IHBhcmVudC1jaGlsZCB0
YWJsZQpwYWlyID8KCkJl
aW5nIGFibGUgdG8g
cHV0IHRyaWdnZXJzIG9u
IHN5c3RlbSB0YWJsZXMg
dG8gcmVjb3JkIGNoYW5n
ZXMgdG9nZXRoZXIK
d2l0aCBPTiBDT01NSVQg
dHJpZ2dlciB0byBnYXRo
ZXIgYW5kIHByb2Nlc3Mg
dGhpcyByZWNvcmRl
ZCBkYXRhIHdvdWxkCmdp
dmUgeW91IDk5LjklIG9m
IG5lZWRlZCBmdW5jdGlv
bmFsaXR5LgoKQW5k
IGZvciBzbG9ueSB5b3Ug
d291bGQgZXZlbiBub3Qg
bmVlZCB0aGUgT04gQ09N
TUlUIHRyaWdnZXIs
IGFzIGFsbApyZWNvcmRl
ZCBkYXRhIGlzIHByb2Nl
c3NlZCBieSBhbm90aGVy
IHRocmVhZCBhbnl3
YXkuCgpUaGVyZSB3aWxs
IHByb2JhYmx5IGJlIHNv
bWUgdGhpbmdzIHRoYXQg
c2hvdWxkIG5vdCBi
ZSBkb25lIGluIHRyaWdn
ZXJzCm9uIHN5c3RlbSB0
YWJsZXMsIGJ1dCBqdXN0
IHJlY29yZGluZyBj
aGFuZ2VzIHRvIGFub3Ro
ZXIgdGFibGUgaXMKcHJv
YmFibHkgbm90IG9uZSBv
ZiB0aGVtLgoKPiAy
KSBDcmVhdGUgdHJpZ2dl
cnMgb24gRERMCgpUaGF0
IHdvdWxkIGJlIG1vcmUg
bGlrZWx5IENSRUFU
RSBSVUxFIG9uIERETC4g
SW4gb3RoZXIgcGxhY2Vz
IHdlIGNyZWF0ZQpSVUxF
UyBvbiBzdGF0ZW1l
bnRzIGFuZCBUUklHR0VS
UyBvbiByb3ctbGV2ZWwg
YWN0aW9ucy4KCj4gSXQg
aXMgZmFyIGZyb20g
YSBzdHJhaWdodCBmb3J3
YXJkIGNvbmNlcHQuIFRo
ZSB0aGluZyBpcyB0aG91
Z2gsIHRoZSBTbG9u
eQo+IHByb2plY3QgaXMg
bm90IHRoZSBvbmx5IHBy
b2plY3Qgd2hpY2ggd2Fu
dHMgc3VjaCBmdW5j
dGlvbmFsaXR5OiB0aGUg
SkRCQwo+IGFuZCBvdGhl
ciBpbnRlcmZhY2UgcHJv
dmlkZXJzIGNvdWxk
IGdyZWF0bHkgYmVuZWZp
dCBiZWNhdXNlIGl0IHdv
dWxkIG1lYW4KPiB0aGF0
IHRoZWlyIGNsaWVu
dCBzaWRlIG1ldGFkYXRh
IGNhY2hpbmcgY291bGQg
YmUgbXVjaCBtb3JlIGVm
ZmljaWVudC4KCkp1
c3QgYmVpbmcgYWJsZSB0
byBjcmVhdGUgQUZURVIg
dHJpZ2dlcnMgKG5vIG5l
ZWQgdG8gbXVjayB3
aXRoIERETApjb21tYW5k
cywganVzdCByZWNvcmQg
dGhlaXIgZWZmZWN0cykg
d291bGQgZ28gYSBs
b25nIHdheS4KCi0tLS0t
LS0tLS0tLS0KSGFubnUK
CgpfX19fX19fX19fX19f
X19fX19fX19fX19f
X19fX19fX19fX19fX19f
X19fX19fXwpTbG9ueTEt
Z2VuZXJhbCBtYWlsaW5n
IGxpc3QKU2xvbnkx
LWdlbmVyYWxAZ2Jvcmcu
cG9zdGdyZXNxbC5vcmcK
aHR0cDovL2dib3JnLnBv
c3RncmVzcWwub3Jn
L21haWxtYW4vbGlzdGlu
Zm8vc2xvbnkxLWdlbmVy
YWwK
| |
| Gavin Sherry 2006-02-25, 9:50 am |
| On Thu, 23 Feb 2006, Jim C. Nasby wrote:
> On Thu, Feb 23, 2006 at 03:02:14PM +1100, Gavin Sherry wrote:
>
> Visible?? You mean you're worried about about seeing stuff that hasn't
> committed yet?
I was implying that it was a timing problem. In the code we create the
pg_class entry and then populate pg_attribute from memory. We need to be
careful about where we invoke a trigger on pg_class to make sure all it's
ramifications are visible -- ie, that we've issued
CommandCounterIncrem
ent().
>
>
> Are there any operations (other than CREATE DATABASE) that don't touch a
> system table?
CREATE DATABASE does touch system catalogs. But it is the same catalog in
*all databases*. There might be triggers on pg_database in a number of
databases but how do we invoke them?
Gavin
| |
| Gavin Sherry 2006-02-25, 9:50 am |
| T24gVGh1LCAyMyBGZWIg
MjAwNiwgSGFubnUgS3Jv
c2luZyB3cm90ZToKCj4g
w5xoZWwga2VuYWwg
cMOkZXZhbCwgTiwgMjAw
Ni0wMi0yMyBrZWxsIDE1
OjAyLCBraXJqdXRhcyBH
YXZpbiBTaGVycnk6
Cj4gPgo+ID4gVGhlIGlz
c3VlLCBmb3IgbWUsIGlz
IHRoYXQgdGhlcmUgYXJl
IHR3byB3YXlzIHdl
IGNvdWxkIGdvIGFib3V0
Cj4gPiBpbXBsZW1lbnRp
bmcgdGhpcyBhbmQgYm90
aCBoYXZlIHByb2Js
ZW1zLgo+ID4KPiA+IDEp
IENyZWF0ZSB0cmlnZ2Vy
cyBvbiBpbmRpdmlkdWFs
IHN5c3RlbSB0YWJs
ZXMuCj4gPgo+ID4gSWYg
eW91IHdhbnRlZCB0byBr
bm93IHdoZW4gYSBuZXcg
dGFibGUgd2FzIGNy
ZWF0ZWQsIHdoZW4gYSB0
YWJsZSB3YXMKPiA+IG1v
ZGlmaWVkIG9yIGRyb3Bw
ZWQsIHlvdSB3b3Vs
ZCBjcmVhdGUgYSB0cmln
Z2VyIG9uIHBnX2NsYXNz
LCBJIHN1cHBvc2UuCj4g
PiBUaGUgcHJvYmxl
bSwgdGhvdWdoLCBpcyB0
aGF0IHlvdSBtaWdodCB3
YW50IHRvIHNlZSB3aGF0
IGNvbHVtbnMgYSBu
ZXcKPiA+IHRhYmxlIGhh
cyBidXQgYXJlIHRoZXkg
dmlzaWJsZSB5ZXQ/ IEl0J3MgYSBiaXQgb2Yg
YSBjYW4gb2Ygd29ybXMu
Cj4KPiBJZiB5b3Ugd2Fu
dCB0byBzZWUgdGhlIGNv
bHVtbnMsIHlvdSBw
dXQgdHJpZ2dlcnMgb24g
cGdfYXR0cmlidXRlIGFz
Cj4gd2VsbC4gSG93IGlz
IGl0IGRpZmZlcmVu
dCBmcm9tIHRyaWdnZXJz
IGFueSBvdGhlciBwYXJl
bnQtY2hpbGQgdGFibGUK
PiBwYWlyID8KClN1
cmUuIE15IHBvaW50IGlz
IHRoYXQgcGVvcGxlIG1p
Z2h0IG5vdCB3YW50IHRv
IHVuZGVyc3RhbmQg
dGhlIGludGVybmFscwpv
ZiB0aGUgc3lzdGVtIGNh
dGFsb2dzIHdoZW4gdGhl
eSBqdXN0IHdhbnQg
YSB0cmlnZ2VyIHdoaWNo
IHB1dHMgYW4gZW50cnkK
aW4gYSBsb2cgdGFibGUg
d2hlbiBhIG5ldyB0
YWJsZSBpcyBhZGRlZC4K
Cj4gPiAyKSBDcmVhdGUg
dHJpZ2dlcnMgb24gRERM
Cj4KPiBUaGF0IHdv
dWxkIGJlIG1vcmUgbGlr
ZWx5IENSRUFURSBSVUxF
IG9uIERETC4gSW4gb3Ro
ZXIgcGxhY2VzIHdl
IGNyZWF0ZQo+IFJVTEVT
IG9uIHN0YXRlbWVudHMg
YW5kIFRSSUdHRVJTIG9u
IHJvdy1sZXZlbCBh
Y3Rpb25zLgoKTm90IGEg
YmFkIGlkZWEuCgo+Cj4g
PiBJdCBpcyBmYXIgZnJv
bSBhIHN0cmFpZ2h0
IGZvcndhcmQgY29uY2Vw
dC4gVGhlIHRoaW5nIGlz
IHRob3VnaCwgdGhlIFNs
b255Cj4gPiBwcm9q
ZWN0IGlzIG5vdCB0aGUg
b25seSBwcm9qZWN0IHdo
aWNoIHdhbnRzIHN1Y2gg
ZnVuY3Rpb25hbGl0
eTogdGhlIEpEQkMKPiA+
IGFuZCBvdGhlciBpbnRl
cmZhY2UgcHJvdmlkZXJz
IGNvdWxkIGdyZWF0
bHkgYmVuZWZpdCBiZWNh
dXNlIGl0IHdvdWxkIG1l
YW4KPiA+IHRoYXQgdGhl
aXIgY2xpZW50IHNp
ZGUgbWV0YWRhdGEgY2Fj
aGluZyBjb3VsZCBiZSBt
dWNoIG1vcmUgZWZmaWNp
ZW50Lgo+Cj4gSnVz
dCBiZWluZyBhYmxlIHRv
IGNyZWF0ZSBBRlRFUiB0
cmlnZ2VycyAobm8gbmVl
ZCB0byBtdWNrIHdp
dGggRERMCj4gY29tbWFu
ZHMsIGp1c3QgcmVjb3Jk
IHRoZWlyIGVmZmVjdHMp
IHdvdWxkIGdvIGEg
bG9uZyB3YXkuCgpSaWdo
dC4gQnV0IGl0J3MgYSBt
YXR0ZXIgb2YgY29taW5n
IHVwIHdpdGggYSBz
b2x1dGlvbiB3aGljaCBt
YWtlcyBldmVyeW9uZQpo
YXBweSBhbmQgaXNuJ3Qg
YSBtYWludGVuYW5j
ZSBuaWdodG1hcmUuCgpH
YXZpbgpfX19fX19fX19f
X19fX19fX19fX19fX19f
X19fX19fX19fX19f
X19fX19fX19fXwpTbG9u
eTEtZ2VuZXJhbCBtYWls
aW5nIGxpc3QKU2xvbnkx
LWdlbmVyYWxAZ2Jv
cmcucG9zdGdyZXNxbC5v
cmcKaHR0cDovL2dib3Jn
LnBvc3RncmVzcWwub3Jn
L21haWxtYW4vbGlz
dGluZm8vc2xvbnkxLWdl
bmVyYWwK
| |
| Andreas Pflug 2006-02-25, 9:50 am |
| Gavin Sherry wrote:
>
>
>
> Not a bad idea.
This is probably a much better idea to catch DDL changes directly than
to reconstruct them from pg_catalog.* changes. When system table
definitions change, cross-version replication might get a pain. The less
slony needs to know about pgsql's internal handling, the better.
Regards,
Andreas
| |
| Andrew Sullivan 2006-02-25, 9:50 am |
| On Wed, Feb 22, 2006 at 05:02:38PM -0700, Rick Gigger wrote:
> you are replicating to a database with different table definitions.
> What if the statements to be replicated are now invalid on the slave
> but valid on the master. What happens then?
Things break really badly. You _must_ use execute.
> the foot. It would be nice to be able to just update the master to
> your hearts content have the slaves get updated as well.
Well, unless you _want_ the differences on the origin and replica.
Remember, that's also a feature. You could replicate to a target
that looks slightly different, so that some tables can show up on the
target but not others. It's important not to think of "master
database". I can see the argument per-table, but you need to be able
to turn it off.
A
--
Andrew Sullivan | ajs-oaT0K0jot5/q2IAV+ODieA@public.gmane.org
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
--Brad Holland
| |
| Andrew Sullivan 2006-02-25, 9:50 am |
| On Thu, Feb 23, 2006 at 08:00:58PM +1100, Gavin Sherry wrote:
>
> CREATE DATABASE does touch system catalogs. But it is the same catalog in
> *all databases*. There might be triggers on pg_database in a number of
> databases but how do we invoke them?
Indeed. And even if you solve that, what are the security
implications of it? It seems to me this is just a giant vector for
inter-database attacks.
A
--
Andrew Sullivan | ajs-oaT0K0jot5/q2IAV+ODieA@public.gmane.org
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun
| |
| David Fetter 2006-02-25, 9:50 am |
| On Thu, Feb 23, 2006 at 12:36:30AM -0600, Jim C. Nasby wrote:
> On Thu, Feb 23, 2006 at 03:02:14PM +1100, Gavin Sherry wrote:
>
> Visible?? You mean you're worried about about seeing stuff that
> hasn't committed yet?
Well, yes. I frequently will ensure that DDL changes happen
consistently by putting them inside a transaction, and this would be
on point. That, or I've completely misunderstood the issue at hand.
>
> Are there any operations (other than CREATE DATABASE) that don't
> touch a system table?
>
>
> Absolutely. I think there's a ton of uses for this, including many
> end-user applications (which is what slony is anyway...)
<aol>Me, too!</>
Cheers,
D
--
David Fetter david- iTBeR5ZXhegdnm+yROfE
0A@public.gmane.org http://fetter.org/
phone: +1 415 235 3778
Remember to vote!
| |
| Jim C. Nasby 2006-02-25, 9:50 am |
| On Thu, Feb 23, 2006 at 07:02:09AM -0500, Andrew Sullivan wrote:
> On Thu, Feb 23, 2006 at 08:00:58PM +1100, Gavin Sherry wrote:
>
> Indeed. And even if you solve that, what are the security
> implications of it? It seems to me this is just a giant vector for
> inter-database attacks.
Ok, so worst case we punt for now and don't support triggers on
pg_database. Triggers on some catalog tables/system events is far better
than triggers on none...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby-D/ iDPWeZeLdl57MIdRCFDg
@public.gmane.org
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
| |
| Jim C. Nasby 2006-02-25, 9:50 am |
| On Thu, Feb 23, 2006 at 09:35:50AM +0200, Hannu Krosing wrote:
> Being able to put triggers on system tables to record changes together
> with ON COMMIT trigger to gather and process this recorded data would
> give you 99.9% of needed functionality.
<snip>
> Just being able to create AFTER triggers (no need to muck with DDL
> commands, just record their effects) would go a long way.
Agreed. I'd much rather start with a small subset of possible
functionality and go from there rather than have nothing because we
can't come up with a fully-baked implementation.
So, what if the initial implimentation is limited to:
Per-row and per-statement AFTER triggers
Rules
Does that sound reasonable?
One thing about this that I don't like... it means we'd be pretty
formally exposing the catalogs as an API. Perhaps it would be better to
use either info_schema or newsysviews as an API, though I have no idea
how hard it would be to allow rules or triggers on a view. Of course
another possibility is to create an entirely different API.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby-D/ iDPWeZeLdl57MIdRCFDg
@public.gmane.org
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
| |
| Jan Wieck 2006-02-25, 9:50 am |
| On 2/23/2006 4:00 AM, Gavin Sherry wrote:
> On Thu, 23 Feb 2006, Jim C. Nasby wrote:
>
>
> I was implying that it was a timing problem. In the code we create the
> pg_class entry and then populate pg_attribute from memory. We need to be
> careful about where we invoke a trigger on pg_class to make sure all it's
> ramifications are visible -- ie, that we've issued
> CommandCounterIncrem
ent().
Plus having created things like the PK index for the table and other
constraints.
Which leads to yet another problem. What does Slony do if the admin
creates a table without a primary or even without any possile candidate
key? Don't replicate the table? Abort the CREATE TABLE?
And how exactly do I tell Slony (after it got all that super smart to
master DDL all by itself) that I want a different set of indexes on
"this particular replica", because that's my search engine server?
Jan
--
#===================
====================
====================
===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#===================
====================
=========== JanWieck- bwPqjjyvM7QAvxtiuMwx
3w@public.gmane.org #
| |
| Hannu Krosing 2006-02-25, 9:50 am |
| w5xoZWwga2VuYWwgcMOk
ZXZhbCwgUiwgMjAwNi0w
Mi0yNCBrZWxsIDExOjMw
LCBraXJqdXRhcyBK
YW4gV2llY2s6Cj4gT24g
Mi8yMy8yMDA2IDQ6MDAg
QU0sIEdhdmluIFNoZXJy
eSB3cm90ZToKPiA+
IEkgd2FzIGltcGx5aW5n
IHRoYXQgaXQgd2FzIGEg
dGltaW5nIHByb2JsZW0u
IEluIHRoZSBjb2Rl
IHdlIGNyZWF0ZSB0aGUK
PiA+IHBnX2NsYXNzIGVu
dHJ5IGFuZCB0aGVuIHBv
cHVsYXRlIHBnX2F0
dHJpYnV0ZSBmcm9tIG1l
bW9yeS4gV2UgbmVlZCB0
byBiZQo+ID4gY2FyZWZ1
bCBhYm91dCB3aGVy
ZSB3ZSBpbnZva2UgYSB0
cmlnZ2VyIG9uIHBnX2Ns
YXNzIHRvIG1ha2Ugc3Vy
ZSBhbGwgaXQncwo+
ID4gcmFtaWZpY2F0aW9u
cyBhcmUgdmlzaWJsZSAt
LSBpZSwgdGhhdCB3ZSd2
ZSBpc3N1ZWQKPiA+
IENvbW1hbmRDb3VudGVy
SW5jcmVtZW50KCkuCj4g
Cj4gUGx1cyBoYXZpbmcg
Y3JlYXRlZCB0aGlu
Z3MgbGlrZSB0aGUgUEsg
aW5kZXggZm9yIHRoZSB0
YWJsZSBhbmQgb3RoZXIg
Cj4gY29uc3RyYWlu
dHMuCj4gCj4gV2hpY2gg
bGVhZHMgdG8geWV0IGFu
b3RoZXIgcHJvYmxlbS4g
V2hhdCBkb2VzIFNs
b255IGRvIGlmIHRoZSBh
ZG1pbiAKPiBjcmVhdGVz
IGEgdGFibGUgd2l0aG91
dCBhIHByaW1hcnkg
b3IgZXZlbiB3aXRob3V0
IGFueSBwb3NzaWxlIGNh
bmRpZGF0ZSAKPiBrZXk/IERvbid0IHJlcGxp
Y2F0ZSB0aGUgdGFibGU/ IEFib3J0IHRoZSBDUkVB
VEUgVEFCTEU/ CgpBY3R1YWxseSBJIHdv
dWxk
IGxpa2Ugc2xvbnkgdG8g
YmUgYWJsZSB0byByZXBs
aWNhdGUgUEstbGVzcyB0
YWJsZXMgaW4KIklO
U0VSVCBPTkxZIi1tb2Rl
LCB0aGF0IGlzIGRpc2Fi
bGUgdXBkYXRlcyBhbmQg
ZGVsZXRlcywgYnV0
IGxvZyBpbnNlcnRzCmFu
ZCByZXBsYXkgdGhlc2Ug
b24gc2xhdmUuIE9mdGVu
IHRoZXJlIGlzIG5v
IGdvb2Qgd2F5IHRvIHB1
dCBhCihjYW5kaWRhdGUp
IHByaW1hcnkga2V5IG9u
IHNvbWUgbG9nL2Fy
Y2hpdmUgdGFibGVzLCBi
dXQgdGhleSB3b3VsZCBi
ZQpzdGlsbCBuZWVkZWQg
b24gdGhlIGRhdGF3
YXJlaG91c2Ugc2lkZS4K
Cj4gQW5kIGhvdyBleGFj
dGx5IGRvIEkgdGVsbCBT
bG9ueSAoYWZ0ZXIg
aXQgZ290IGFsbCB0aGF0
IHN1cGVyIHNtYXJ0IHRv
IAo+IG1hc3RlciBEREwg
YWxsIGJ5IGl0c2Vs
ZikgdGhhdCBJIHdhbnQg
YSBkaWZmZXJlbnQgc2V0
IG9mIGluZGV4ZXMgb24g
Cj4gInRoaXMgcGFy
dGljdWxhciByZXBsaWNh
IiwgYmVjYXVzZSB0aGF0
J3MgbXkgc2VhcmNoIGVu
Z2luZSBzZXJ2ZXI/
CgpJIGd1ZXNzIHRoYXQg
dGhlcmUgYXJlIHR3byAi
bW9kZXMiIGluIHdoaWNo
IHNsb255IGlzIHVz
ZWQgLSAKCmEpICJob3Qg
c3RhbmRieSIgLSBpLmUg
cmVwbGljYXRlIGV2ZXJ5
dGhpbmcgYXV0b21h
dGljYWxseSAoYW55d2F5
LCB0aGlzCmlzIHByb2Jh
Ymx5IGJldHRlciBzZXJ2
ZWQgYnkgV0FMLXNo
aXBwaW5nKQoKYikgImRh
dGEgd2FyZWhvdXNlIiAt
IG5vIGF1dG9tYXRpb24g
bmVlZGVkLCBvciBk
byBtaW5pbWFsIGF1dG9t
YXRpb24sCmxpa2Ugc2hp
cHBpbmcgb25seSB0YWJs
ZSArIHByaW1hcnkg
a2V5LiB1c3VhbGx5IHBl
b3BsZSBhdCBEVyBzaWRl
IHdpbGwKdGVsbCB5b3Ug
KHRoZSBhZG1pbikg
d2hlbiB0aGV5IG1pc3Mg
c29tZXRoaW5nIDopCgoK
LS0tLS0tLS0tLS0tLS0t
LS0KSGFubnUKCgpf
X19fX19fX19fX19fX19f
X19fX19fX19fX19fX19f
X19fX19fX19fX19fX19f
XwpTbG9ueTEtZ2Vu
ZXJhbCBtYWlsaW5nIGxp
c3QKU2xvbnkxLWdlbmVy
YWxAZ2JvcmcucG9zdGdy
ZXNxbC5vcmcKaHR0
cDovL2dib3JnLnBvc3Rn
cmVzcWwub3JnL21haWxt
YW4vbGlzdGluZm8vc2xv
bnkxLWdlbmVyYWwK
| |
| Rick Gigger 2006-02-25, 9:50 am |
| On Feb 24, 2006, at 9:30 AM, Jan Wieck wrote:
> On 2/23/2006 4:00 AM, Gavin Sherry wrote:
>
> Plus having created things like the PK index for the table and other
> constraints.
>
> Which leads to yet another problem. What does Slony do if the admin
> creates a table without a primary or even without any possile
> candidate
> key? Don't replicate the table? Abort the CREATE TABLE?
>
> And how exactly do I tell Slony (after it got all that super smart to
> master DDL all by itself) that I want a different set of indexes on
> "this particular replica", because that's my search engine server?
It seems to me that there are two cases to be handled:
1) Hot standby. Just duplicate the whole thing exactly
2) Anything else you might want to do.
Slony handles both of these cases wonderfully because it is designed
to be completely flexible which in my opinion is the way to go. At
the same time once a stable flexible solution is in place it makes
sense to me to automate certain common cases.
I see this as anagous to say autovacuum. It's nice to just turn it
on and let it take care of you. If you have tables though that are
really big or really small or really wide and are updated often then
just turning on autovacuum is not going to do it for you. You are
going to need to do some analysis and hand tune the per table
parameters to make sure everything is taken care of.
So why not have an option to just replicate an entire database. If
that option is selected then you just replicate every table. Put it
all in one set. If there is a primary key use it. If not then look
for a not null unique field. If that doesn't exist add a serial
column just as if the user had requested it explicitly in the slony
config.
Don't get me wrong I think that the most important thing is that
slony provides a way to do everything you might need to do. The
automation of common tasks has to come second to that. But slony is
now a stable reliable replication system. Why not add some extra
support for the case of "just give me an exact replica" and make it
totally turn key.
Yes this indeed will not work for all people and large or more
complex setups will require some hand tuning / configuring to get
good performance for what they want to do. If there are technical
issues that can't be overcome that is understandable but it sounds
like you are simply trying to maintain the flexibility of slony for
advanced configurations. I think it could be done such that advanced
configuration was still possible but a turnkey "just duplicate this
database to there" situations would be helpful for a lot of
installations that don't need that flexibility. I'm sure that there
are people out there that could use it but don't want to take the
time to set everything up. The don't NEED it but it would be nice to
have if it's not too much trouble. Getting those people to use it
would increase the installed base which means more users testing it
that could possibly be useful to the project (or postgres in general)
some day.
And it would give postgres the reputation of having an "it just
works" replication solution. Mysql has gotten this reputation by
implementing a half-baked kludged replication solution that appears
to "just work" but in reality has limitations and is probably not
very failsafe. I think that slony could be a solid flexible "done
right" replication solution that also can be set up in 15 minutes
just by installing it, turning it on and telling it what databases to
replicate.
Now that slonly is working and stable I think that it really could be
the best of both worlds. But I obviously had no hand in developing
it. So maybe there are technical hurdles that make this impossible.
But just like there was resistance to the windows port for so long I
think it is good that it was finally added. Maybe it's not the best
for big mission critical production sites. But there are many cases
where doing this could I think improve the adoption of postgres in
certain situations.
just my $0.02
Rick
| |
| Jim C. Nasby 2006-02-25, 9:50 am |
| On Fri, Feb 24, 2006 at 11:30:45AM -0500, Jan Wieck wrote:
> Which leads to yet another problem. What does Slony do if the admin
> creates a table without a primary or even without any possile candidate
> key? Don't replicate the table? Abort the CREATE TABLE?
Well, CREATE TABLE is something that doesn't make sense to replicate
automatically. What does make sense is having slony be able to push out
the definition for a table that was just added to a set, as well as
optionally picking up DDL changes made to the table down-the-road.
> And how exactly do I tell Slony (after it got all that super smart to
> master DDL all by itself) that I want a different set of indexes on
> "this particular replica", because that's my search engine server?
Make DDL replication optional.
BTW, just being able to push DDL out without all the locking that
execute_script requires would probably be a big win.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby-D/ iDPWeZeLdl57MIdRCFDg
@public.gmane.org
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
| |
| Andrew Sullivan 2006-02-28, 8:34 pm |
| On Fri, Feb 24, 2006 at 05:23:42PM -0600, Jim C. Nasby wrote:
> BTW, just being able to push DDL out without all the locking that
> execute_script requires would probably be a big win.
By what magic is that to happen? DDL on the database always requires
a certain amount of locking. Doing it across database back ends
without two phase commit is even tricker, because you'd _better_ be
sure that you don't send events to the other database before the
table is there or changed or whatever.
It is the importance of that synchronization that lead us to
suppose that execute script was enough. I'm trying to understand
what the problem is that one is trying to solve, that is not solved
now.
A
--
Andrew Sullivan | ajs-oaT0K0jot5/q2IAV+ODieA@public.gmane.org
When my information changes, I alter my conclusions. What do you do sir?
--attr. John Maynard Keynes
| |
| Jan Wieck 2006-02-28, 8:34 pm |
| On 2/27/2006 1:25 PM, Andrew Sullivan wrote:
> It is the importance of that synchronization that lead us to
> suppose that execute script was enough. I'm trying to understand
> what the problem is that one is trying to solve, that is not solved
> now.
This whole discussion is getting tiresome. It is a lot of handwaving
about how smart and autopiloted Slony should appear to inexperienced
users who "just want to replicate the entire database" without having a
clue what they are actually asking for (the users, not the people
arguing on this list).
I don't say that this is by itself a bad idea. But a statement like
"just create an exact copy"
should be backed by a 20-30 page technical concept describing how to
actually achieve this goal.
Jan
--
#===================
====================
====================
===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#===================
====================
=========== JanWieck- bwPqjjyvM7QAvxtiuMwx
3w@public.gmane.org #
|
|
|
|
|