relation ### modified while in use

Alex Pilosov <alex@pilosoft.com> writes:

I'm having the error 'relation <number> modified while in use' fairly
often. It is the same relation that's always giving a problem.

Hmm, could we see the full schema dump for that relation?
(pg_dump -s -t tablename dbname will do)

If you are not actively modifying the schema, then in theory you should
not see this message, but...

regards, tom lane

I think this happens after I create/modify tables which reference this
table. This is spontaneous, and doesn't _always_ happen...

Anything I could do next time it craps up to help track the problem down?

-alex

----
CREATE TABLE "customers" (
"cust_id" int4 DEFAULT nextval('customers_cust_id_seq'::text) NOT
NULL,
"phone_npa" character(3) NOT NULL,
"phone_nxx" character(3) NOT NULL,
"phone_rest" character(4) NOT NULL,
"e_mail" character varying(30),
"daytime_npa" character(3),
"daytime_nxx" character(3),
"daytime_rest" character(4),
"is_business" bool DEFAULT 'f' NOT NULL,
PRIMARY KEY ("cust_id") );

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "customers" NOT
DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noaction_del" ('<unnamed>', 'cc_charges', 'customers',
'UNSPECIFIED', 'cust_id', 'cust_id');

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "customers" NOT
DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noaction_upd" ('<unnamed>', 'cc_charges', 'customers',
'UNSPECIFIED', 'cust_id', 'cust_id');

On Sun, 22 Oct 2000, Tom Lane wrote:

Alex Pilosov <alex@pilosoft.com> writes:

I think this happens after I create/modify tables which reference this
table. This is spontaneous, and doesn't _always_ happen...

Um. I was hoping it was something more easily fixable :-(. What's
causing the relcache to decide that the rel has been modified is the
addition or removal of foreign-key triggers on the rel. Which seems
legitimate. (It's barely possible that we could get away with allowing
triggers to be added or deleted mid-transaction, but that doesn't feel
right to me.)

There are two distinct known bugs that allow the error to be reported.
These have been discussed before, but to recap:

1. relcache will complain if the notification of cache invalidation
arrives after transaction start and before first use of the referenced
rel (when there was already a relcache entry left over from a prior
transaction). In this situation we should allow the change to occur
without complaint, ISTM. But the relcache doesn't currently have any
concept of first reference versus later references.

2. Even with #1 fixed, you could still get this error, because we are
way too willing to release locks on rels that have been referenced.
Therefore you can get this sequence:

Session 1 Session 2

begin;

select * from foo;
-- LockRelation(AccessShareLock);
-- UnLockRelation(AccessShareLock);

ALTER foo ADD CONSTRAINT;
-- LockRelation(AccessExclusiveLock);
-- lock released at commit

select * from foo;
-- LockRelation(AccessShareLock);
-- table schema update is detected, error must be reported

I think that we should hold at least AccessShareLock on any relation
that a transaction has touched, all the way to end of transaction.
This creates the potential for deadlocks that did not use to happen;
for example, if we have two transactions that concurrently both do

begin;
select * from foo; -- gets AccessShareLock
LOCK TABLE foo; -- gets AccessExclusiveLock
...
end;

this will work currently because the SELECT releases AccessShareLock
when done, but it will deadlock if SELECT does not release that lock.

That's annoying but I see no way around it, if we are to allow
concurrent transactions to do schema modifications of tables that other
transactions are using.

Comments anyone?

regards, tom lane

On Mon, 23 Oct 2000, Tom Lane wrote:

when done, but it will deadlock if SELECT does not release that lock.

That's annoying but I see no way around it, if we are to allow
concurrent transactions to do schema modifications of tables that other
transactions are using.

I might be in above my head, but maybe this is time for yet another type
of lock? "Do-not-modify-this-table-under-me" lock, which shall persist
until transaction commits, and will conflict only with alter table
lock/AccessExclusiveLock?

I realise we have already many lock types, but this seems to be proper
solution to me...

In related vein: Is there a way to see who (at least process id) is
holding locks on tables?

Alex Pilosov <alex@pilosoft.com> writes:

I might be in above my head, but maybe this is time for yet another type
of lock?

Wouldn't help --- it's still a deadlock.

regards, tom lane

On Mon, 23 Oct 2000, Alex Pilosov wrote:

On Mon, 23 Oct 2000, Tom Lane wrote:

when done, but it will deadlock if SELECT does not release that lock.

That's annoying but I see no way around it, if we are to allow
concurrent transactions to do schema modifications of tables that other
transactions are using.

I might be in above my head, but maybe this is time for yet another type
of lock? "Do-not-modify-this-table-under-me" lock, which shall persist
until transaction commits, and will conflict only with alter table
lock/AccessExclusiveLock?

I just realised that I _am_ in above my head, and the above makes no
sense, and is identical to holding AccessShareLock.

Sorry ;)

-alex

On Mon, 23 Oct 2000, Tom Lane wrote:

begin;
select * from foo; -- gets AccessShareLock
LOCK TABLE foo; -- gets AccessExclusiveLock
...
end;

this will work currently because the SELECT releases AccessShareLock
when done, but it will deadlock if SELECT does not release that lock.

Probably a silly question, but since this is the same transaction,
couldn't the lock be 'upgraded' without a problem?

Or postgres doesn't currently have idea of lock upgrades...?

-alex

Alex Pilosov <alex@pilosoft.com> writes:

On Mon, 23 Oct 2000, Tom Lane wrote:

begin;
select * from foo; -- gets AccessShareLock
LOCK TABLE foo; -- gets AccessExclusiveLock
...
end;

this will work currently because the SELECT releases AccessShareLock
when done, but it will deadlock if SELECT does not release that lock.

Probably a silly question, but since this is the same transaction,
couldn't the lock be 'upgraded' without a problem?

No, the problem happens when two transactions do the above at about
the same time. After the SELECTs, both transactions are holding
AccessShareLock, and both are waiting for the other to let go so's they
can get AccessExclusiveLock. AFAIK any concept of "lock upgrade" falls
afoul of this basic deadlock risk.

We do have a need to be careful that the system doesn't try to do
lock upgrades internally. For example, in
LOCK TABLE foo;
the parsing step had better not grab AccessShareLock on foo in
advance of the main execution step asking for AccessExclusiveLock.

regards, tom lane

Philip Warner <pjw@rhyme.com.au> writes:

At 01:01 23/10/00 -0400, Tom Lane wrote:

(It's barely possible that we could get away with allowing
triggers to be added or deleted mid-transaction, but that doesn't feel
right to me.)

A little OT, but the above is a useful feature for managing data; it's not
common, but the following sequence is essential to managing a database safely:

- Start TX
- Drop a few triggers, constraints etc
- Add/change data to fix erroneous/no longer accurate business rules
(audited, of course)
- Reapply the triggers, constraints
- Make sure it looks right
- Commit/Rollback based on the above check

There is nothing wrong with the above as long as you hold exclusive
lock on the tables being modified for the duration of the transaction.

The scenario I'm worried about is on the other side, ie, a transaction
that has already done some things to a table is notified of a change to
that table's triggers/constraints/etc being committed by another
transaction. Can it deal with that consistently? I don't think it can
in general. What I'm proposing is that once an xact has touched a
table, other xacts should not be able to apply schema updates to that
table until the first xact commits.

regards, tom lane

Tom Lane wrote:

Philip Warner <pjw@rhyme.com.au> writes:

At 01:01 23/10/00 -0400, Tom Lane wrote:

(It's barely possible that we could get away with allowing
triggers to be added or deleted mid-transaction, but that doesn't feel
right to me.)

A little OT, but the above is a useful feature for managing data; it's not
common, but the following sequence is essential to managing a database safely:

- Start TX
- Drop a few triggers, constraints etc
- Add/change data to fix erroneous/no longer accurate business rules
(audited, of course)
- Reapply the triggers, constraints
- Make sure it looks right
- Commit/Rollback based on the above check

There is nothing wrong with the above as long as you hold exclusive
lock on the tables being modified for the duration of the transaction.

The scenario I'm worried about is on the other side, ie, a transaction
that has already done some things to a table is notified of a change to
that table's triggers/constraints/etc being committed by another
transaction. Can it deal with that consistently? I don't think it can
in general. What I'm proposing is that once an xact has touched a
table, other xacts should not be able to apply schema updates to that
table until the first xact commits.

I agree with you.
I've wondered why AccessShareLock is a short term lock.

If we have a mechanism to acquire a share lock on a tuple,we
could use it for managing system info generally. However the
only allowed lock on a tuple is exclusive. Access(Share/Exclusive)
Lock on tables would give us a restricted solution about pg_class
tuples.

Thers'a possibility of deadlock in any case but there are few
cases when AccessExclusiveLock is really needed and we could
acquire an AccessExclusiveLock manually from the first if
necessary.

I'm not sure about the use of AccessShareLock in parse-analyze-
optimize phase however.

Regards.
Hiroshi Inoue

At 01:01 23/10/00 -0400, Tom Lane wrote:

(It's barely possible that we could get away with allowing
triggers to be added or deleted mid-transaction, but that doesn't feel
right to me.)

A little OT, but the above is a useful feature for managing data; it's not
common, but the following sequence is essential to managing a database safely:

- Start TX
- Drop a few triggers, constraints etc
- Add/change data to fix erroneous/no longer accurate business rules
(audited, of course)
- Reapply the triggers, constraints
- Make sure it looks right
- Commit/Rollback based on the above check

It is very undesirable to drop the triggers/constraints in a separate
transaction since a communications failure could leave them unapplied. At
least in one TX, the recovery process should back out the TX.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

At 01:37 23/10/00 -0400, Tom Lane wrote:

What I'm proposing is that once an xact has touched a
table, other xacts should not be able to apply schema updates to that
table until the first xact commits.

Totally agree. You may want to go further and say that metadata changes can
not be made while that *connection* exists: if the client has prepared a
query against a table will it cause a problem when the query is run?

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

What I'm proposing is that once an xact has touched a
table, other xacts should not be able to apply schema updates to that
table until the first xact commits.

No, this would mean too many locks, and would leave the dba with hardly a
chance to alter a table.

If I recall correctly the ANSI standard mandates that schema modifications
be seen immediately. Thus imho we need to refresh the relcache on first
access after modification. Thus two accesses to one table inside one tx
would be allowed to see two different versions (the exception beeing
serializable isolation level).

Imho we only need to lock out an alter table if a cursor is open on that table.

Andreas

Zeugswetter Andreas SB wrote:

What I'm proposing is that once an xact has touched a
table, other xacts should not be able to apply schema updates to that
table until the first xact commits.

No, this would mean too many locks, and would leave the dba with hardly a
chance to alter a table.

Are there many applications which have many SELECT statements(without
FOR UPDATE) in one tx ?
As for locks,weak locks doesn't pass intensive locks. Dba seems to be able
to alter a table at any time.

Regards.
Hiroshi Inoue

What I'm proposing is that once an xact has touched a
table, other xacts should not be able to apply schema updates to that
table until the first xact commits.

No, this would mean too many locks, and would leave the dba with hardly a
chance to alter a table.

Are there many applications which have many SELECT statements(without
FOR UPDATE) in one tx ?

Why not ?

As for locks,weak locks doesn't pass intensive locks. Dba
seems to be able to alter a table at any time.

Sorry, I don't understand this sentence. Tom suggested placing a shared lock on
any table that is accessed until end of tx. Noone can alter table until all users have
closed their txns and not accessed tables again. Remember that this would include
creating an index ...

Andreas

in general. What I'm proposing is that once an xact has touched a
table, other xacts should not be able to apply schema updates to that
table until the first xact commits.

I agree with you.

I don't know. We discussed this issue just after 6.5 and decided to
allow concurrent schema modifications.
Oracle has disctionary locks but run each DDL statement in separate
xaction, so - no deadlock condition here. OTOH, I wouldn't worry
about deadlock - one just had to follow common anti-deadlock rules.

I've wondered why AccessShareLock is a short term lock.

MUST BE. AccessShare-/Exclusive-Locks are *data* locks.
If one want to protect schema then new schema share/excl locks
must be inroduced. There is no conflict between data and
schema locks - they are orthogonal.

We use AccessShare-/Exclusive-Locks for schema because of...
we allow concurrent schema modifications and no true schema
locks were required.

If we have a mechanism to acquire a share lock on a tuple,we
could use it for managing system info generally. However the
only allowed lock on a tuple is exclusive. Access(Share/Exclusive)

Actually, just look at lock.h:LTAG structure - lock manager supports
locking of "some objects" inside tables:

typedef struct LTAG
{
Oid relId;
Oid dbId;
union
{
BlockNumber blkno;
Transaction xid;
} objId;
...
- we could add oid to union above and lock tables by acquiring lock
on pg_class with objId.oid = table' oid. Same way we could lock indices
and whatever we want... if we want -:)

Lock on tables would give us a restricted solution about pg_class
tuples.

Thers'a possibility of deadlock in any case but there are few
cases when AccessExclusiveLock is really needed and we could
acquire an AccessExclusiveLock manually from the first if
necessary.

I'm not sure about the use of AccessShareLock in parse-analyze-
optimize phase however.

There is notion about breakable (parser) locks in Oracle documentation -:)

Vadim

As for locks,weak locks doesn't pass intensive locks. Dba
seems to be able to alter a table at any time.

Sorry, I don't understand this sentence. Tom suggested placing a shared

lock on

any table that is accessed until end of tx. Noone can alter table until

all users have

closed their txns and not accessed tables again.

More of that - while one xaction will wait to alter a table no new xaction
will be
allowed to access this table too.

Remember that this would include creating an index ...

I don't think so. Index creation requires
1. share lock on schema
2. share lock on data

Vadim

Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:

As for locks,weak locks doesn't pass intensive locks. Dba
seems to be able to alter a table at any time.

Sorry, I don't understand this sentence. Tom suggested placing a
shared lock on any table that is accessed until end of tx. Noone can
alter table until all users have closed their txns and not accessed
tables again.

Until existing xacts using that table have closed, yes. But I believe
the lock manager has some precedence rules that will allow the pending
request for AccessExclusiveLock to take precedence over new requests
for lesser locks. So you're only held off for a long time if you have
long-running xacts that use the target table.

I consider that behavior *far* safer than allowing schema changes to
be seen mid-transaction. Consider the following example:

Session 1 Session 2

begin;

INSERT INTO foo ...;

ALTER foo ADD constraint;

INSERT INTO foo ...;

end;

Which, if any, of session 1's insertions will be subject to the
constraint? What are the odds that the dba will like the result?

With my proposal, session 2's ALTER would wait for session 1 to commit,
and then the ALTER's own scan to verify the constraint will check all
the rows added by session 1.

Under your proposal, I think the rows inserted at the beginning of
session 1's xact would be committed without having been checked.

regards, tom lane

Hiroshi Inoue <Inoue@tpf.co.jp> writes:

I'm not sure about the use of AccessShareLock in parse-analyze-
optimize phase however.

That's something we'll have to clean up while fixing this. Currently
the system may acquire and release AccessShareLock multiple times while
parsing/rewriting/planning. We need to make sure that an appropriate
lock is grabbed at *first* use and then held.

Should save a few cycles as well as being more correct ...

regards, tom lane