SEO

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?

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

> 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?

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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/

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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/

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

> 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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend