Emmanuel Cecchet m...@frogthinker.org writes:
Tom Lane wrote:
AFAIK that doesn't really have anything to do with the temp-ness of the
table; it'd be the same with a regular table. The problem is you have
an in-doubt tuple in pg_class for pg_temp_NNN.foo, and you are trying
to create another
Tom Lane wrote:
Emmanuel Cecchet m...@frogthinker.org writes:
Take PG 8.3.0 and try:
BEGIN;
CREATE TEMP TABLE foo (x int) ON COMMIT DROP;
PREPARE TRANSACTION 't1';
[BEGIN;] -- doesn't really matter if you start a new transaction or not
CREATE TEMP TABLE foo (x int); -- blocks until t1
Tom Lane wrote:
True, but the problem is that the tuple might still be live to (some
snapshots in) that transaction, so we can't inject a duplicate tuple
without risking confusing it. In this particular case that isn't an
issue because the transaction is done executing, but the tqual.c
rules
Emmanuel Cecchet m...@frogthinker.org writes:
Tom Lane wrote:
True, but the problem is that the tuple might still be live to (some
snapshots in) that transaction, so we can't inject a duplicate tuple
without risking confusing it. In this particular case that isn't an
issue because the
Emmanuel Cecchet wrote:
Tom Lane wrote:
True, but the problem is that the tuple might still be live to (some
snapshots in) that transaction, so we can't inject a duplicate tuple
without risking confusing it. In this particular case that isn't an
issue because the transaction is done
Tom Lane wrote:
Emmanuel Cecchet m...@frogthinker.org writes:
Tom Lane wrote:
True, but the problem is that the tuple might still be live to (some
snapshots in) that transaction, so we can't inject a duplicate tuple
without risking confusing it. In this particular case that isn't an
Emmanuel Cecchet m...@frogthinker.org writes:
But when the transaction prepares, we know that. What would prevent us
to do at prepare time the same cleanup that commit does?
The entire point of PREPARE is that it's *not* committed yet.
regards, tom lane
--
Sent via
Tom Lane wrote:
Emmanuel Cecchet m...@frogthinker.org writes:
But when the transaction prepares, we know that. What would prevent us
to do at prepare time the same cleanup that commit does?
The entire point of PREPARE is that it's *not* committed yet.
Agreed but all objects that
Hi,
As we discussed during PGCon, we are using temp tables in 2PC
transactions. The temp tables are dropped before PREPARE (or have an ON
COMMIT DROP option) and never cross transaction boundaries.
In 8.3.1, a patch was introduced to disallow temp tables in 2PC
transactions and we tried to
Emmanuel Cecchet m...@frogthinker.org writes:
Take PG 8.3.0 and try:
BEGIN;
CREATE TEMP TABLE foo (x int) ON COMMIT DROP;
PREPARE TRANSACTION 't1';
[BEGIN;] -- doesn't really matter if you start a new transaction or not
CREATE TEMP TABLE foo (x int); -- blocks until t1 commits
I have been
Are there some sort of functions that I could use to know which process
has an exclusive lock on relations, pages, tuples, transactions,
etc... ?
Best regards,
Alfranio Junior
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
On Sat, 11 Feb 2006, Alfranio Correia Junior wrote:
Are there some sort of functions that I could use to know which process
has an exclusive lock on relations, pages, tuples, transactions,
etc... ?
SELECT * FROM pg_locks;
- Heikki
---(end of
I mean, any C function.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
On 2005-05-30, Neil Conway [EMAIL PROTECTED] wrote:
On Wed, 2005-03-23 at 10:04 -0500, Tom Lane wrote:
I think last night's discussion makes it crystal-clear why I felt that
this hasn't been sufficiently thought through. Please revert until the
discussion comes to a conclusion.
Are there
On Wed, 2005-03-23 at 10:04 -0500, Tom Lane wrote:
I think last night's discussion makes it crystal-clear why I felt that
this hasn't been sufficiently thought through. Please revert until the
discussion comes to a conclusion.
Are there any remaining objections to reapplying this patch?
The
Neil Conway [EMAIL PROTECTED] writes:
Are there any remaining objections to reapplying this patch?
The original commit message is here:
http://archives.postgresql.org/pgsql-committers/2005-03/msg00316.php
The archives of the -hackers thread are here:
Alvaro Herrera wrote:
On Wed, Mar 23, 2005 at 10:42:01AM +0800, Christopher Kings-Lynne wrote:
If you want to be my friend forever, then fix CLUSTER so that it uses
sharerowexclusive as well :D
I don't think it's as easy as that, because you have to move tuples
around in the cluster
It would keep the old table around while building the new, then grab
an exclusive lock to swap the two.
Lock upgrading is right out.
It would need a whole of new family of intent locks, with different rules.
Andreas
---(end of
Neil Conway [EMAIL PROTECTED] writes:
Tom Lane wrote:
It isn't 100% MVCC, I agree. But it works because system catalog
lookups are SnapshotNow, and so when another session comes and wants to
look at the table it will see the committed new version of the pg_class
row pointing at the new
Neil Conway [EMAIL PROTECTED] writes:
Tom Lane wrote:
I don't think this has been adequately thought through at all ... but
at least make it ExclusiveLock. What is the use-case for allowing
SELECT FOR UPDATE in parallel with this?
Ok, patch applied -- I adjusted it to use ExclusiveLock, and
Neil Conway [EMAIL PROTECTED] writes:
Tom Lane wrote:
It isn't 100% MVCC, I agree. But it works because system catalog
lookups are SnapshotNow, and so when another session comes and wants to
look at the table it will see the committed new version of the pg_class
row pointing at the new
Tom Lane wrote:
I think last night's discussion makes it crystal-clear why I felt that
this hasn't been sufficiently thought through. Please revert until the
discussion comes to a conclusion.
I applied the patch because I don't think it is very closely related to
the discussion. But if you'd
Tom Lane wrote:
I agree that we aren't MVCC with respect to DDL operations (and for this
purpose CLUSTER is DDL). Trying to become so would open a can of worms
far larger than it's worth, though, IMHO.
I think if we can come up with a reasonable way to handle all the
consequences, it's worth
Neil Conway wrote:
AndrewSN pointed out on IRC that ALTER TABLE ... ADD FOREIGN KEY and
CREATE TRIGGER both acquire AccessExclusiveLocks on the table they are
adding triggers to (the PK table, in the case of ALTER TABLE). Is this
necessary? I don't see why we can't allow SELECT queries on the
If you want to be my friend forever, then fix CLUSTER so that it uses
sharerowexclusive as well :D
Chris
Neil Conway wrote:
Neil Conway wrote:
AndrewSN pointed out on IRC that ALTER TABLE ... ADD FOREIGN KEY and
CREATE TRIGGER both acquire AccessExclusiveLocks on the table they are
adding
On Wed, 23 Mar 2005 12:40 pm, Christopher Kings-Lynne wrote:
If you want to be my friend forever, then fix CLUSTER so that it uses
sharerowexclusive as well :D
I don't think it's as easy as that, because you have to move tuples
around in the cluster operation. Same sort of issue as vacuum
If you want to be my friend forever, then fix CLUSTER so that it uses
sharerowexclusive as well :D
I don't think it's as easy as that, because you have to move tuples
around in the cluster operation. Same sort of issue as vacuum full I would
suggest.
Cluster doesn't move rows...
I didn't say it
On Wed, Mar 23, 2005 at 10:42:01AM +0800, Christopher Kings-Lynne wrote:
If you want to be my friend forever, then fix CLUSTER so that it uses
sharerowexclusive as well :D
I don't think it's as easy as that, because you have to move tuples
around in the cluster operation. Same sort of
Christopher Kings-Lynne wrote:
If you want to be my friend forever, then fix CLUSTER so that it uses
sharerowexclusive as well :D
Hmm, this might be possible as well. During a CLUSTER, we currently
- lock the heap relation with AccessExclusiveLock
- lock the index we're clustering on with
Huh, cluster already does that.
It does and it doesn't. Something like the first thing it does is muck
with the old table's filenode IIRC, meaning that immediately the old
table will no longer work.
Chris
---(end of broadcast)---
TIP 8: explain
Neil Conway wrote:
So I think it should be possible to lock both the heap relation and the
index with ExclusiveLock, which would allow SELECTs on them. This would
apply to both the single relation and multiple relation variants of
CLUSTER (since we do each individual clustering in its own
Bruce Momjian wrote:
Certainly we need to upgrade to an exclusive table lock to replace the
heap table.
Well, we will be holding an ExclusiveLock on the heap relation
regardless. We replace the heap table by swapping its relfilenode, so
ISTM we needn't hold an AccessExclusiveLock.
Do we want to
Neil Conway [EMAIL PROTECTED] writes:
AndrewSN pointed out on IRC that ALTER TABLE ... ADD FOREIGN KEY and
CREATE TRIGGER both acquire AccessExclusiveLocks on the table they are
adding triggers to (the PK table, in the case of ALTER TABLE). Is this
necessary? I don't see why we can't allow
Neil Conway [EMAIL PROTECTED] writes:
/*
! * Grab an exclusive lock on the pk table, so that someone doesn't
! * delete rows out from under us. (Although a lesser lock would do for
! * that purpose, we'll need exclusive lock anyway to add triggers to
! * the pk
Christopher Kings-Lynne [EMAIL PROTECTED] writes:
It would keep the old table around while building the new, then grab
an exclusive lock to swap the two.
Lock upgrading is right out.
regards, tom lane
---(end of
Neil Conway wrote:
... except that when we rebuild the relation's indexes, we acquire an
AccessExclusiveLock on the index. This would introduce the risk of
deadlock. It seems necessary to acquire an AccessExclusiveLock when
rebuilding shared indexes, since we do the index build in-place, but I
Neil Conway [EMAIL PROTECTED] writes:
Well, we will be holding an ExclusiveLock on the heap relation
regardless. We replace the heap table by swapping its relfilenode, so
ISTM we needn't hold an AccessExclusiveLock.
Utterly wrong. When you commit you will physically drop the old table.
If
Tom Lane wrote:
Utterly wrong. When you commit you will physically drop the old table.
If there is a SELECT running against the old table it will be quite
unhappy after that.
How can we drop the file at commit, given that a serializable
transaction's snapshot should still be able to see old
Neil Conway wrote:
Tom Lane wrote:
Utterly wrong. When you commit you will physically drop the old table.
If there is a SELECT running against the old table it will be quite
unhappy after that.
How can we drop the file at commit, given that a serializable
transaction's snapshot should
Neil Conway [EMAIL PROTECTED] writes:
How can we drop the file at commit, given that a serializable
transaction's snapshot should still be able to see old relfilenode's
content?
It isn't 100% MVCC, I agree. But it works because system catalog
lookups are SnapshotNow, and so when another
Neil Conway [EMAIL PROTECTED] writes:
- if we make these changes, we will need some way to delete a
no-longer-visible relfilenode.
This is presuming that we abandon the notion that system catalog
access use SnapshotNow. Which opens the question of what they should
use instead ... to which
Tom Lane wrote:
It isn't 100% MVCC, I agree. But it works because system catalog
lookups are SnapshotNow, and so when another session comes and wants to
look at the table it will see the committed new version of the pg_class
row pointing at the new relfilenode file.
If by works, you mean provides
Tom Lane wrote:
This is presuming that we abandon the notion that system catalog
access use SnapshotNow. Which opens the question of what they should
use instead ... to which transaction snapshot isn't the answer,
because we have to be able to do system catalog accesses before
we've set the
Tom Lane wrote:
I don't think this has been adequately thought through at all ... but
at least make it ExclusiveLock. What is the use-case for allowing
SELECT FOR UPDATE in parallel with this?
Ok, patch applied -- I adjusted it to use ExclusiveLock, and fleshed out
some of the comments.
-Neil
AndrewSN pointed out on IRC that ALTER TABLE ... ADD FOREIGN KEY and
CREATE TRIGGER both acquire AccessExclusiveLocks on the table they are
adding triggers to (the PK table, in the case of ALTER TABLE). Is this
necessary? I don't see why we can't allow SELECT queries on the table to
proceed
Neil Conway wrote:
AndrewSN pointed out on IRC that ALTER TABLE ... ADD FOREIGN KEY and
CREATE TRIGGER both acquire AccessExclusiveLocks on the table they are
adding triggers to (the PK table, in the case of ALTER TABLE). Is this
necessary? I don't see why we can't allow SELECT queries on the
46 matches
Mail list logo