Tom Lane wrote:
> Hiroshi Inoue <[EMAIL PROTECTED]> writes:
> > RelationCacheInvalidate() is called from ResetSystemCaches()
> > and calles RelationFlushRelation() for all relation descriptors
> > except some nailed system relations.
> > I'm wondering why nailed relations could be exceptions.
> >
Hiroshi Inoue <[EMAIL PROTECTED]> writes:
> RelationCacheInvalidate() is called from ResetSystemCaches()
> and calles RelationFlushRelation() for all relation descriptors
> except some nailed system relations.
> I'm wondering why nailed relations could be exceptions.
> Conversely why must Relation
Hi
RelationCacheInvalidate() is called from ResetSystemCaches()
and calles RelationFlushRelation() for all relation descriptors
except some nailed system relations.
I'm wondering why nailed relations could be exceptions.
Conversely why must RelationCacheInvalidate() call
RelationFlushRelation() fo
> > The problem at hand is that
> > a plan may be invalidated before it is even finished building. Do you
> > expect the parse-rewrite-plan-execute pipeline to be prepared to back up
> > and restart if we notice a relation schema change report halfway down the
> > process?
Yes, during the proce
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]]
>
> "Hiroshi Inoue" <[EMAIL PROTECTED]> writes:
> > Doesn't current heap_open() have a flaw that even the first
> > use of a relation in a transaction may cause an error
> > "relation ### modified while in use" ?
>
> Sure,
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]]
>
> > BTW,I sometimes see
> > ERROR: SearchSysCache: recursive use of cache 10(16)
> > under small MAXNUMMESSAGES environment.
> > I'm not sure about the cause but suspicious if sufficiently
> > many system relations are
On Fri, 3 Nov 2000, Hiroshi Inoue wrote:
> PL/pgSQL already prepares a plan at the first execution
> time and executes the plan repeatedly after that.
> We would have general PREPARE/EXECUTE feature in the
> near fututre. IMHO another mechanism to detect plan invali
> dation is needed.
Excellent
"Hiroshi Inoue" <[EMAIL PROTECTED]> writes:
> Doesn't current heap_open() have a flaw that even the first
> use of a relation in a transaction may cause an error
> "relation ### modified while in use" ?
Sure, that was the starting point of the discussion.
>> because that will open us up to fail
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]]
>
> Hiroshi Inoue <[EMAIL PROTECTED]> writes:
> > Do we have a conclusion about this thread ?
> > If no,how about changing heap_open(r) so that they allocate
> > Relation descriptors after acquiring a lock on the table ?
> >
Hiroshi Inoue <[EMAIL PROTECTED]> writes:
> Do we have a conclusion about this thread ?
> If no,how about changing heap_open(r) so that they allocate
> Relation descriptors after acquiring a lock on the table ?
> We would use LockRelation() no longer.
That won't do by itself, because that will op
Tom Lane wrote:
> Alex Pilosov <[EMAIL PROTECTED]> 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 dec
At 09:36 25/10/00 +0200, Zeugswetter Andreas SB wrote:
>
>> I have not followed the entire thread, but if you are in a serializable OR
>> repeatable-read transaction,
>
>Serializable and repeatable read are the same thing, different wording.
Not last time I looked. RR ensures that rows you have s
> I have not followed the entire thread, but if you are in a serializable OR
> repeatable-read transaction,
Serializable and repeatable read are the same thing, different wording.
> I would think that read-only statements will
> need to keep some kind of lock on the rows they read (or the tabl
At 18:31 24/10/00 +0900, Hiroshi Inoue wrote:
>
>
>Zeugswetter Andreas SB wrote:
>
>> > > > Are there many applications which have many SELECT statements(without
>> > > > FOR UPDATE) in one tx ?
>> > >
>> > > Why not ?
>> > >
>> > It seems to me that multiple SELECT statements in a tx has little
>
Vadim Mikheev wrote:
> > > > In my understanding,locking levels you provided contains
> > > > an implicit share/exclusive lock on the corrsponding
> > > > pg_class tuple i.e. AccessExclusive Lock acquires an
> > > > exclusive lock on the corresping pg_class tuple and
> > > > other locks acquire
> > > > > Are there many applications which have many SELECT statements(without
> > > > > FOR UPDATE) in one tx ?
> > > >
> > > > Why not ?
> > > >
> > > It seems to me that multiple SELECT statements in a tx has little
> > > meaning unless the tx is executed in SERIALIZABLE isolation level.
> >
>
> > > In my understanding,locking levels you provided contains
> > > an implicit share/exclusive lock on the corrsponding
> > > pg_class tuple i.e. AccessExclusive Lock acquires an
> > > exclusive lock on the corresping pg_class tuple and
> > > other locks acquire a share lock, Is it right ?
> >
>
Zeugswetter Andreas SB wrote:
[snip]
>
> Also the result would be, that the first readonly statements are allowed to
> see schema changes, but selects after the first DML would not :-(
>
Does it mean that even read-only statements aren't allowed
to release locks after other DMLs ?
Regards.
Hi
> More of that - while one xaction will wait to alter a table no new xaction
> will be allowed to access this table too.
Yes, I forgot, that placing an exclusive lock will make later shared lock
requests wait.
Andreas
> > Also the result would be, that the first readonly statements are allowed to
> > see schema changes, but selects after the first DML would not :-(
>
> Does it mean that even read-only statements aren't allowed
> to release locks after other DMLs ?
That is, what Tom is suggesting, but not afte
Zeugswetter Andreas SB wrote:
> > > > Are there many applications which have many SELECT statements(without
> > > > FOR UPDATE) in one tx ?
> > >
> > > Why not ?
> > >
> > It seems to me that multiple SELECT statements in a tx has little
> > meaning unless the tx is executed in SERIALIZABLE isol
> > > Are there many applications which have many SELECT statements(without
> > > FOR UPDATE) in one tx ?
> >
> > Why not ?
> >
> It seems to me that multiple SELECT statements in a tx has little
> meaning unless the tx is executed in SERIALIZABLE isolation level.
E.g. a table is accessed multipl
> Zeugswetter Andreas SB <[EMAIL PROTECTED]> writes:
> > Yes, and holding a row exclusive lock must imho at least
> grab a shared
> > table lock
>
> As indeed it does. Our disagreement seems to be just on the point of
> whether it's safe to allow a read-only transaction to release its
> Acces
"Mikheev, Vadim" wrote:
> > > > 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
> > > sc
> > > 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.
> >
>
Vadim Mikheev wrote:
> > > 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
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 hardl
Philip Warner wrote:
> At 15:29 23/10/00 +0900, Hiroshi Inoue wrote:
> >
> >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
Hiroshi Inoue <[EMAIL PROTECTED]> 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/pla
At 10:10 23/10/00 -0400, Tom Lane wrote:
>
>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 ...;
>
>
> > 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 the
Philip Warner <[EMAIL PROTECTED]> writes:
> In this case, wouldn't the answer depend on the isolation level of session
> 1? For serializable TX, then constraint would not apply; 'read committed'
> would mean the constraint was visible on the second insert and at the commit.
The important issue he
Philip Warner <[EMAIL PROTECTED]> writes:
> Only slightly; one interpretation of a table lock is that it locks all of
> the data in the table; and a lock on the pg_class row locks the metadata. I
> must admit that I am having a little difficulty thinking of a case where
> the distinction would be
Zeugswetter Andreas SB <[EMAIL PROTECTED]> writes:
> Yes, and holding a row exclusive lock must imho at least grab a shared
> table lock
As indeed it does. Our disagreement seems to be just on the point of
whether it's safe to allow a read-only transaction to release its
AccessShareLock locks p
> > 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 s
> > You were talking about the "select only" case (and no for update eighter).
> > I think that select statements need a shared lock for the duration of their
> > execution only.
>
> You seem to think that locks on individual tuples conflict with
> table-wide locks.
Yes, very much so. Any oth
At 10:45 23/10/00 -0400, Tom Lane wrote:
>Philip Warner <[EMAIL PROTECTED]> writes:
>> Don't we have this ability? What about taking a RowShare lock on the
>> pg_class tuple whenever you read from the table; then requiring schema
>> updates take a RowExclusive lock on the pg_class tuple?
>
>How is
Philip Warner <[EMAIL PROTECTED]> writes:
> Don't we have this ability? What about taking a RowShare lock on the
> pg_class tuple whenever you read from the table; then requiring schema
> updates take a RowExclusive lock on the pg_class tuple?
How is that different from taking locks on the table
Zeugswetter Andreas SB <[EMAIL PROTECTED]> writes:
> No, the above is not a valid example, because Session 2 won't
> get the exclusive lock until Session 1 commits, since Session 1 already
> holds a lock on foo (for the inserted row).
> You were talking about the "select only" case (and no for
Zeugswetter Andreas SB <[EMAIL PROTECTED]> 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
At 15:29 23/10/00 +0900, Hiroshi Inoue wrote:
>
>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
> 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-
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 alte
> > > 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.
> >
>
>
> 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 ANS
Tom Lane wrote:
> Philip Warner <[EMAIL PROTECTED]> 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
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 whi
Philip Warner <[EMAIL PROTECTED]> 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 managi
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 sequen
Alex Pilosov <[EMAIL PROTECTED]> 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,
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 S
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 tha
Alex Pilosov <[EMAIL PROTECTED]> 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, 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
Alex Pilosov <[EMAIL PROTECTED]> 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 m
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_cus
Alex Pilosov <[EMAIL PROTECTED]> writes:
> I'm having the error 'relation 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 mod
I'm having the error 'relation modified while in use' fairly
often. It is the same relation that's always giving a problem. Usually
after all currently-running backends die away with that error, error
disappears. If I shutdown, ipcclean, start up postgres, it also
disappears.
What causes this?
58 matches
Mail list logo
