Thread: Table modifications with dependent views - best practices?
Ok, I've been using postgres for a-while now, and am curious how you guys handle this. What is the best way to make modifications to tables that have lots of dependent objects, like views? Do you just do the obvious drop...cascade and manually re-create your views? Do you keep your "create view" statements in a text file for this purpose or just pull them from a pg_dump file? Is there a better way to handle this sort of thing? I know on a properly designed database, changes to the schema are usually not necessary. However, during development, database tweaks (at least for me) are pretty common. Any thoughts?
On Thu, Apr 21, 2005 at 09:24:49PM -0500, John Browne wrote: > > Ok, I've been using postgres for a-while now, and am curious how you > guys handle this. What is the best way to make modifications to > tables that have lots of dependent objects, like views? Do you just > do the obvious drop...cascade and manually re-create your views? Do > you keep your "create view" statements in a text file for this purpose > or just pull them from a pg_dump file? Is there a better way to > handle this sort of thing? I put CREATE statements in a file so I can easily reload them. Sometimes I put views and functions in their own files so they can be reloaded separately, since dropping and recreating them is usually a trivial, non-destructive operation, unlike dropping and recreating tables. I usually put DDL statements in a transaction, for a couple of reasons: so that a mistake doesn't leave me with half-done work (any error will cause the entire transaction to roll back), and to make the changes atomic for the benefit of other transactions. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
> I usually put DDL statements in a transaction, for a couple of > reasons: so that a mistake doesn't leave me with half-done work > (any error will cause the entire transaction to roll back), and to > make the changes atomic for the benefit of other transactions. Can you do that in postgres? Will it really make the DDL atomic? I know that is oracle any DDL will implicityly commit any existing transaction, start another one, and commit again at the end of the statement. I think it is similar in SQL Server too. Can postgress really do DDL on multiple tables and indexes atomically?
On Fri, Apr 22, 2005 at 11:34:29AM +0100, David Roussel wrote: > > > I usually put DDL statements in a transaction, for a couple of > > reasons: so that a mistake doesn't leave me with half-done work > > (any error will cause the entire transaction to roll back), and to > > make the changes atomic for the benefit of other transactions. > > Can you do that in postgres? Will it really make the DDL atomic? Yes, although locking will probably prevent concurrent access and can cause deadlock. DDL statements like DROP, CREATE, and ALTER acquire an AccessExclusiveLock on the objects they're modifying, so the transaction doing the DDL will block until no other transactions hold locks on those objects, and other transactions' attempts to use those objects will block until the DDL transaction commits or rolls back. If the DDL transaction rolls back, then nobody else will ever have seen the changes; if it commits then the changes all become visible at the same time. Try it and see what happens. You might see blocking and you might be able to cause deadlock, but you shouldn't ever see some changes but not others. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Fri, Apr 22, 2005 at 11:34:29 +0100, David Roussel <pgsql-general@diroussel.xsmail.com> wrote: > > > I usually put DDL statements in a transaction, for a couple of > > reasons: so that a mistake doesn't leave me with half-done work > > (any error will cause the entire transaction to roll back), and to > > make the changes atomic for the benefit of other transactions. > > Can you do that in postgres? Will it really make the DDL atomic? I wasn't able to find where this is spelled out in the documentation, but I believe all DDL commands except DROP DATABASE can be rolled back now. For older versions of Postgres there were more DDL statements that were not transaction safe.
Bruno Wolff III <bruno@wolff.to> writes:
> I wasn't able to find where this is spelled out in the documentation,
> but I believe all DDL commands except DROP DATABASE can be rolled back now.
I don't think there's any all-in-one-place statement about it, but
anything that doesn't explicitly object to being put inside a
transaction block can be rolled back. Grepping for
PreventTransactionChain, I see that the current suspects are
CLUSTER (only the multi-table variants)
CREATE DATABASE
DROP DATABASE
REINDEX DATABASE
CREATE TABLESPACE
DROP TABLESPACE
VACUUM
regards, tom lane
Why would DDL statements in a transaction cause deadlocks? I understand the prevention of concurrent access, but I'm curious to know more about how deadlocks arise in this situation, as this is something I've seen in a production environment during transactional DDL traffic. Why would DDL statements be more likely to cause lock acquisition at cross purposes? A simple example would help me understand this. Thanks! -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Apr 22, 2005, at 6:11 AM, Michael Fuhr wrote: > On Fri, Apr 22, 2005 at 11:34:29AM +0100, David Roussel wrote: >> >>> I usually put DDL statements in a transaction, for a couple of >>> reasons: so that a mistake doesn't leave me with half-done work >>> (any error will cause the entire transaction to roll back), and to >>> make the changes atomic for the benefit of other transactions. >> >> Can you do that in postgres? Will it really make the DDL atomic? > > Yes, although locking will probably prevent concurrent access and > can cause deadlock. DDL statements like DROP, CREATE, and ALTER > acquire an AccessExclusiveLock on the objects they're modifying, > so the transaction doing the DDL will block until no other transactions > hold locks on those objects, and other transactions' attempts to > use those objects will block until the DDL transaction commits or > rolls back. If the DDL transaction rolls back, then nobody else > will ever have seen the changes; if it commits then the changes all > become visible at the same time. > > Try it and see what happens. You might see blocking and you might > be able to cause deadlock, but you shouldn't ever see some changes > but not others.
On Sat, Apr 23, 2005 at 10:36:00AM -0500, Thomas F.O'Connell wrote: > > Why would DDL statements in a transaction cause deadlocks? I understand > the prevention of concurrent access, but I'm curious to know more about > how deadlocks arise in this situation, as this is something I've seen > in a production environment during transactional DDL traffic. Why would > DDL statements be more likely to cause lock acquisition at cross > purposes? Locks are held until transaction end, as can be observed by querying pg_locks. DDL statements typically acquire an AccessExclusiveLock, which conflicts with all other lock types. With those in mind, consider the following example: Setup: CREATE TABLE foo (a integer); CREATE TABLE bar (x integer); Transactions: T1: BEGIN; T2: BEGIN; T1: SELECT * FROM foo; T2: ALTER TABLE bar ADD COLUMN y integer; T1 now has an AccessShareLock on foo, and T2 has an AccessExclusiveLock on bar. T1: SELECT * FROM bar; T2: ALTER TABLE foo ADD COLUMN b integer; T1's SELECT blocks because it wants a lock that conflicts with T2's lock on bar, and T2's ALTER blocks because it wants a lock that conflicts with T1's lock on foo. Both transactions are waiting for the other to release a lock, so we get deadlock. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
So I have a scenario in which account creation at the application layer generates a set of tables and indexes. The tables created have foreign keys to tables in use (and frequently read from and written to) by the rest of the application. Occasionally I was getting deadlocks, and this definitely explains why, if creating foreign keys requires an AccessExclusiveLock on the table to which the key refers. Ideally, this DDL work should occur in a transaction to avoid partial creation of the relevant objects, but it seems like it will always run the risk of generating deadlocks in a production environment. Blocking is less of an issue because the transaction shouldn't ever take terribly long, but deadlocks always strike me as a red flag, especially in a production application environment. Is there a best practice or suitable workaround for this sort of scenario? -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Apr 22, 2005, at 6:11 AM, Michael Fuhr wrote: > On Fri, Apr 22, 2005 at 11:34:29AM +0100, David Roussel wrote: >> >>> I usually put DDL statements in a transaction, for a couple of >>> reasons: so that a mistake doesn't leave me with half-done work >>> (any error will cause the entire transaction to roll back), and to >>> make the changes atomic for the benefit of other transactions. >> >> Can you do that in postgres? Will it really make the DDL atomic? > > Yes, although locking will probably prevent concurrent access and > can cause deadlock. DDL statements like DROP, CREATE, and ALTER > acquire an AccessExclusiveLock on the objects they're modifying, > so the transaction doing the DDL will block until no other transactions > hold locks on those objects, and other transactions' attempts to > use those objects will block until the DDL transaction commits or > rolls back. If the DDL transaction rolls back, then nobody else > will ever have seen the changes; if it commits then the changes all > become visible at the same time. > > Try it and see what happens. You might see blocking and you might > be able to cause deadlock, but you shouldn't ever see some changes > but not others.
> So I have a scenario in which account creation at the application > layer generates a set of tables and indexes. The tables created have > foreign keys to tables in use (and frequently read from and written > to) by the rest of the application. Occasionally I was getting > deadlocks, and this definitely explains why, if creating foreign keys > requires an AccessExclusiveLock on the table to which the key refers. > > Ideally, this DDL work should occur in a transaction to avoid partial > creation of the relevant objects, but it seems like it will always run > the risk of generating deadlocks in a production environment. Blocking > is less of an issue because the transaction shouldn't ever take > terribly long, but deadlocks always strike me as a red flag, > especially in a production application environment. > > Is there a best practice or suitable workaround for this sort of > scenario? > Deadlock only occurs when resources are acquired out of order by two or more concurrent processes. So you can avoid deadlock by acquiring locks in the same order. This of course is non-trivial, if the different processes are doing different tasks. One way is to acquire an exclusive lock at the very beginning of your transaction, say on the users table. Perhaps do this at the very begging, i.e. before the trigger fires. Of course whether this works depends on what you are deadlocking on. You probably need to analyse the lock cycles to determine which locks you need to acquire early. Another possibility would be to change the trigger to write a message to a queue table, and have another process service the queue and add the users. This way you are splitting the transaction in half, and thus releasing the locks obtained before the trigger fired. Of course you don't get this problem in oracle. I find its optimistic locks wonderful. David