Re: [HACKERS] Table rewrites vs. pending AFTER triggers
Hi Gokul, If you are saying that DDL should be auto-commit, yes, this really does limit some use cases. Transactional DDL is quite helpful for SQL generators, which need to avoid leaving schema half-changed if the application crashes or there¹s a problem with the database that causes a command to fail. SLONY is an example of such a generator where transactional DDL would be helpful though I don¹t know for a fact that SLONY uses it. We have used it in the past for building queues in SQL, which required multiple schema changes for a single queue. In sum, it¹s much easier to implement such tools if you can do a set of schema changes atomically. There are no doubt other use cases as well. Cheers, Robert On 1/2/08 11:04 PM, Gokulakannan Somasundaram [EMAIL PROTECTED] wrote: Is there why we allow DDLs inside a transaction and allow it to be rolled back? If we commit the previous transaction, as soon as we encounter a DDL, and commit the DDL too (without waiting for commit) will it be affecting some use cases? I actually mean to say that DDLs can be declared as self-committing. That would get rid of these exceptions. Am i missing something? Thanks, Gokul. On Jan 3, 2008 12:02 AM, Andrew Dunstan [EMAIL PROTECTED] wrote: Simon Riggs wrote: On Tue, 2008-01-01 at 16:09 -0500, Tom Lane wrote: Paranoia would suggest forbidding *any* form of ALTER TABLE when there are pending trigger events, but maybe that's unnecessarily strong. That works for me. Such a combination makes no sense, so banning it is the right thing to do. +1. Doesn't make much sense to me either. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org http://archives.postgresql.org -- Robert Hodges, CTO, Continuent, Inc. Email: [EMAIL PROTECTED] Mobile: +1-510-501-3728 Skype: hodgesrm -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Table rewrites vs. pending AFTER triggers
On Sun, Jun 01, 2008 at 11:51:29PM -0700, Robert Hodges wrote: If you are saying that DDL should be auto-commit, yes, this really does limit some use cases. I agree. Transactional DDL is a big feature I'd hate to see go away. Oracle DBAs I know look with envy on this feature of Postgres. with the database that causes a command to fail. SLONY is an example of such a generator where transactional DDL would be helpful though I don¹t know for a fact that SLONY uses it. It sort of does, in that all work under Slony is performed in a transaction. But Slony attempts to isolate the DDL in a sync at just the right point, and not mix schema and data changes in syncs. Still, one of the important best practices in preparing your DDL changing scripts for Slony is to try running that script inside a transaction (and then rolling back) on every node, to ensure that it will in fact work on every node. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Table rewrites vs. pending AFTER triggers
Hi, I'm with David on this one. Transactional DDL also turns out to be incredibly helpful for tools that generate and load DDL to extend the database, for example triggers and control tables to implement reliable messaging. You can put the setup in a single transaction, which vastly simplifies tool implementation. We have an application at Continuent that depends on exactly this behavior. I was investigating PostgreSQL semantics just last week and was delighted to find they appear to be exactly right. Oracle on the other hand is going to be a pain... Cheers, Robert Hodges On 1/3/08 12:11 AM, David Fetter [EMAIL PROTECTED] wrote: On Thu, Jan 03, 2008 at 01:08:47PM +0530, Gokulakannan Somasundaram wrote: On Jan 3, 2008 12:44 PM, Tom Lane [EMAIL PROTECTED] wrote: Gokulakannan Somasundaram [EMAIL PROTECTED] writes: I actually mean to say that DDLs can be declared as self-committing. Egad, an Oracle lover in our midst. :). True, its an impact of working more with Oracle. I made the suggestion here, because it might reduce some if conditions. Most of us think that roll-back-able DDL is one of the best features of Postgres, and certainly one of our best selling points vis-a-vis Oracle. Don't expect us to give it up. Can you please explain, any specific use-case where DDLs are necessary within a transaction? Let's imagine that you want to do a DDL change to a production database. You've tested the change script on a test database, but you want to be sure you get *exactly* from the place you were to the place you want to be. With transactional DDL, you know absolutely for sure that you've done either the whole change or none of it, i.e. not half-way in between :) Cheers, David (a giant fan of transactional DDL) - Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Table rewrites vs. pending AFTER triggers
On Thu, Jan 03, 2008 at 01:08:47PM +0530, Gokulakannan Somasundaram wrote: On Jan 3, 2008 12:44 PM, Tom Lane [EMAIL PROTECTED] wrote: Gokulakannan Somasundaram [EMAIL PROTECTED] writes: I actually mean to say that DDLs can be declared as self-committing. Egad, an Oracle lover in our midst. :). True, its an impact of working more with Oracle. I made the suggestion here, because it might reduce some if conditions. Most of us think that roll-back-able DDL is one of the best features of Postgres, and certainly one of our best selling points vis-a-vis Oracle. Don't expect us to give it up. Can you please explain, any specific use-case where DDLs are necessary within a transaction? Let's imagine that you want to do a DDL change to a production database. You've tested the change script on a test database, but you want to be sure you get *exactly* from the place you were to the place you want to be. With transactional DDL, you know absolutely for sure that you've done either the whole change or none of it, i.e. not half-way in between :) Cheers, David (a giant fan of transactional DDL) -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Table rewrites vs. pending AFTER triggers
On Jan 3, 2008 3:53 PM, Martijn van Oosterhout [EMAIL PROTECTED] wrote: On Thu, Jan 03, 2008 at 01:08:47PM +0530, Gokulakannan Somasundaram wrote: Can you please explain, any specific use-case where DDLs are necessary within a transaction? I don't think they are ever necessary, they're just very very nice. For example: - You want a new column to appear populated on a table atomically. You do a BEGIN; add column; update set column=foo; add foreign key; COMMIT - Installation of external modules can be done atomically, so you don't end up with half installed contrib modules. - Principle of Least Surprise. Automatic commit for any reason seems wrong. - Temporarily disabling triggers/indexes/constraints, if the system aborts/crashes, the triggers are reinstated automatically. - Just general niceity of being able to test schema changes without immediatly changing the system. There are many more... Thanks.. it looks like a good feature... Gokul.
Re: [HACKERS] Table rewrites vs. pending AFTER triggers
Gokulakannan Somasundaram wrote: Can you please explain, any specific use-case where DDLs are necessary within a transaction? SQL-Scripts that create database objects and should either succeed or have no effect. Yours, Laurenz Albe ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Table rewrites vs. pending AFTER triggers
Simon Riggs wrote: On Tue, 2008-01-01 at 16:09 -0500, Tom Lane wrote: Paranoia would suggest forbidding *any* form of ALTER TABLE when there are pending trigger events, but maybe that's unnecessarily strong. That works for me. Such a combination makes no sense, so banning it is the right thing to do. I disagree. This is an implementation limitation, so it makes sense to try to restrict the user as least as possible. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Table rewrites vs. pending AFTER triggers
Alvaro Herrera [EMAIL PROTECTED] writes: On Tue, 2008-01-01 at 16:09 -0500, Tom Lane wrote: Paranoia would suggest forbidding *any* form of ALTER TABLE when there are pending trigger events, but maybe that's unnecessarily strong. I disagree. This is an implementation limitation, so it makes sense to try to restrict the user as least as possible. There's a tradeoff here between security, flexibility, and the amount of work we want to put into it. At the moment it's not clear to me that it's worth spending the amount of work that would be needed to determine which forms of ALTER TABLE are safe in this connection. If you're feeling hot about it, feel free to do the legwork. (A precedent is that all forms of ALTER TABLE take exclusive lock, which is more or less the same thing for the cross-backend case. There's been occasional discussion of whether some forms could take lesser locks, but never enough interest to make it happen.) regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Table rewrites vs. pending AFTER triggers
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: On Tue, 2008-01-01 at 16:09 -0500, Tom Lane wrote: Paranoia would suggest forbidding *any* form of ALTER TABLE when there are pending trigger events, but maybe that's unnecessarily strong. I disagree. This is an implementation limitation, so it makes sense to try to restrict the user as least as possible. There's a tradeoff here between security, flexibility, and the amount of work we want to put into it. At the moment it's not clear to me that it's worth spending the amount of work that would be needed to determine which forms of ALTER TABLE are safe in this connection. If you're feeling hot about it, feel free to do the legwork. (A precedent is that all forms of ALTER TABLE take exclusive lock, which is more or less the same thing for the cross-backend case. There's been occasional discussion of whether some forms could take lesser locks, but never enough interest to make it happen.) I'd still like to see a sane use case. The other thing being traded off is possibly simplicity. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Table rewrites vs. pending AFTER triggers
On Tue, 2008-01-01 at 16:09 -0500, Tom Lane wrote: Paranoia would suggest forbidding *any* form of ALTER TABLE when there are pending trigger events, but maybe that's unnecessarily strong. That works for me. Such a combination makes no sense, so banning it is the right thing to do. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Table rewrites vs. pending AFTER triggers
Simon Riggs wrote: On Tue, 2008-01-01 at 16:09 -0500, Tom Lane wrote: Paranoia would suggest forbidding *any* form of ALTER TABLE when there are pending trigger events, but maybe that's unnecessarily strong. That works for me. Such a combination makes no sense, so banning it is the right thing to do. +1. Doesn't make much sense to me either. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Table rewrites vs. pending AFTER triggers
Is there why we allow DDLs inside a transaction and allow it to be rolled back? If we commit the previous transaction, as soon as we encounter a DDL, and commit the DDL too (without waiting for commit) will it be affecting some use cases? I actually mean to say that DDLs can be declared as self-committing. That would get rid of these exceptions. Am i missing something? Thanks, Gokul. On Jan 3, 2008 12:02 AM, Andrew Dunstan [EMAIL PROTECTED] wrote: Simon Riggs wrote: On Tue, 2008-01-01 at 16:09 -0500, Tom Lane wrote: Paranoia would suggest forbidding *any* form of ALTER TABLE when there are pending trigger events, but maybe that's unnecessarily strong. That works for me. Such a combination makes no sense, so banning it is the right thing to do. +1. Doesn't make much sense to me either. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Table rewrites vs. pending AFTER triggers
Gokulakannan Somasundaram [EMAIL PROTECTED] writes: I actually mean to say that DDLs can be declared as self-committing. Egad, an Oracle lover in our midst. Most of us think that roll-back-able DDL is one of the best features of Postgres, and certainly one of our best selling points vis-a-vis Oracle. Don't expect us to give it up. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Table rewrites vs. pending AFTER triggers
On Jan 3, 2008 12:44 PM, Tom Lane [EMAIL PROTECTED] wrote: Gokulakannan Somasundaram [EMAIL PROTECTED] writes: I actually mean to say that DDLs can be declared as self-committing. Egad, an Oracle lover in our midst. :). True, its an impact of working more with Oracle. I made the suggestion here, because it might reduce some if conditions. Most of us think that roll-back-able DDL is one of the best features of Postgres, and certainly one of our best selling points vis-a-vis Oracle. Don't expect us to give it up. Can you please explain, any specific use-case where DDLs are necessary within a transaction? -- Thanks, Gokul.
