Must be owner to truncate?

Stephen Frost <sfrost@snowman.net> writes:

The current permissions checks for truncate seem to be excessive. It
requires that you're the owner of the relation instead of requiring
that you have delete permissions on the relation. It was pointed out
that truncate doesn't call triggers but it seems like that would be
something easy enough to check for.

There are other reasons for restricting it:
* truncate takes a much stronger lock than a plain delete does.
* truncate is not MVCC-safe.

I don't really agree with the viewpoint that truncate is just a quick
DELETE, and so I do not agree that DELETE permissions should be enough
to let you do a TRUNCATE.

regards, tom lane

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Stephen Frost <sfrost@snowman.net> writes:

The current permissions checks for truncate seem to be excessive. It
requires that you're the owner of the relation instead of requiring
that you have delete permissions on the relation. It was pointed out
that truncate doesn't call triggers but it seems like that would be
something easy enough to check for.

There are other reasons for restricting it:
* truncate takes a much stronger lock than a plain delete does.

What permissions are required to lock a table? With just select,
insert, update and delete on a table I can LOCK TABLE it, which acquires
an ACCESS EXCLUSIVE on it and will therefore hold off anyone else from
using the table till the end of my transaction anyway. So I don't see
this as being a reason to disallow non-owners use of truncate.

* truncate is not MVCC-safe.

Erm, that's why it gets a stronger lock, so I don't really see what
this has to do with it.

I don't really agree with the viewpoint that truncate is just a quick
DELETE, and so I do not agree that DELETE permissions should be enough
to let you do a TRUNCATE.

Truncate is exactly a quick DELETE, in fact, DELETE could stand to learn
some thing from truncate to make it suck a little less to
'delete from x;' when x is a reasonably large table. This probably
wouldn't actually be all that difficult to do if there's a way to keep
the old file around until all the transactions using it have completed
that's not too expensive, etc.

Thanks,

Stephen

On 2005-07-07, Stephen Frost <sfrost@snowman.net> wrote:

* truncate is not MVCC-safe.

Erm, that's why it gets a stronger lock, so I don't really see what
this has to do with it.

It's not MVCC-safe even with the AccessExclusive lock; it damages snapshots
that were taken before the truncate operation but which don't have a lock
on the table yet. The only reason it doesn't break pg_dump is that the
first thing that pg_dump does is to take AccessShare locks on every table
that it's going to dump.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

On Thu, Jul 07, 2005 at 01:48:59PM -0400, Tom Lane wrote:

Stephen Frost <sfrost@snowman.net> writes:

The current permissions checks for truncate seem to be excessive. It
requires that you're the owner of the relation instead of requiring
that you have delete permissions on the relation. It was pointed out
that truncate doesn't call triggers but it seems like that would be
something easy enough to check for.

There are other reasons for restricting it:
* truncate takes a much stronger lock than a plain delete does.
* truncate is not MVCC-safe.

I don't really agree with the viewpoint that truncate is just a quick
DELETE, and so I do not agree that DELETE permissions should be enough
to let you do a TRUNCATE.

What about adding a truncate permission? I would find it useful, as it
seems would others.
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

* Jim C. Nasby (decibel@decibel.org) wrote:

On Thu, Jul 07, 2005 at 01:48:59PM -0400, Tom Lane wrote:

I don't really agree with the viewpoint that truncate is just a quick
DELETE, and so I do not agree that DELETE permissions should be enough
to let you do a TRUNCATE.

What about adding a truncate permission? I would find it useful, as it
seems would others.

That would be acceptable for me as well. I'd prefer it just work off
delete, but as long as I can grant truncate to someone w/o giving them
ownership rights on the table I'd be happy.

Thanks,

Stephen

* Andrew - Supernews (andrew+nonews@supernews.com) wrote:

On 2005-07-07, Stephen Frost <sfrost@snowman.net> wrote:

* truncate is not MVCC-safe.

Erm, that's why it gets a stronger lock, so I don't really see what
this has to do with it.

It's not MVCC-safe even with the AccessExclusive lock; it damages snapshots
that were taken before the truncate operation but which don't have a lock
on the table yet. The only reason it doesn't break pg_dump is that the
first thing that pg_dump does is to take AccessShare locks on every table
that it's going to dump.

This seems like something which should probably be fixed, but which is
probably too late to fix for 8.1. Of course, if we could fix this then
it seems like it would be possible for us to just change 'delete from x'
to behave as truncate does now given appropriate conditions. I'm not as
familiar with that area as others are; is this a very difficult thing to
do? If not then I may take a look at it, it'd be a very nice
improvement.

Thanks,

Stephen

The current permissions checks for truncate seem to be excessive. It
requires that you're the owner of the relation instead of requiring
that you have delete permissions on the relation. It was pointed out
that truncate doesn't call triggers but it seems like that would be
something easy enough to check for. My thinking is to replace the
existing ownercheck with:

Must have delete permissions on the relation
If the relation has triggers:
Check that the caller is the owner of the relation, if so, then
issue a NOTICE that those triggers won't be called and perform the
truncate.
If not the owner, then error out saying there are ON DELETE triggers
and that you're not the owner.

I'm strongly in favour of this patch. I am currently in this situation:

1. Web db user runs as non-superuser, non-owner.
2. I have a table of a tens of thousands of rows that I must delete
entirely and rebuild every day at least (pg_trgm word list)
3. It just gets slow over time, even with autovac.
4. I can't vacuum it as i'm not the owner, and I cannot truncate it either.
5. Table has no triggers or FK's whatsoever.

So, stephen frost's suggestion would be fantastic.

Chris

There are other reasons for restricting it:
* truncate takes a much stronger lock than a plain delete does.
* truncate is not MVCC-safe.

I don't really agree with the viewpoint that truncate is just a quick
DELETE, and so I do not agree that DELETE permissions should be enough
to let you do a TRUNCATE.

Ah. I didn't realise that 2nd point. I don't care so much about the
stronger lock in my application.

Chris

Stephen Frost <sfrost@snowman.net> writes:

* Andrew - Supernews (andrew+nonews@supernews.com) wrote:

It's not MVCC-safe even with the AccessExclusive lock;

This seems like something which should probably be fixed,

You've missed the point entirely: this *cannot* be fixed, at least not
without giving up the performance advantages that make TRUNCATE
interesting.

regards, tom lane

* Christopher Kings-Lynne (chriskl@familyhealth.com.au) wrote:

I'm strongly in favour of this patch. I am currently in this situation:

1. Web db user runs as non-superuser, non-owner.
2. I have a table of a tens of thousands of rows that I must delete
entirely and rebuild every day at least (pg_trgm word list)
3. It just gets slow over time, even with autovac.
4. I can't vacuum it as i'm not the owner, and I cannot truncate it either.
5. Table has no triggers or FK's whatsoever.

So, stephen frost's suggestion would be fantastic.

This is a very similar situation to what I'm in, which is why I was
asking for the change. :)

Thanks,

Stephen

* Christopher Kings-Lynne (chriskl@familyhealth.com.au) wrote:

There are other reasons for restricting it:
* truncate takes a much stronger lock than a plain delete does.
* truncate is not MVCC-safe.

I don't really agree with the viewpoint that truncate is just a quick
DELETE, and so I do not agree that DELETE permissions should be enough
to let you do a TRUNCATE.

Ah. I didn't realise that 2nd point. I don't care so much about the
stronger lock in my application.

Does truncate not being MVCC-safe cause problems in your situation? It
certainly doesn't in mine and I expect the same is true for alot of
others in the same situation.

Thanks,

Stephen

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Stephen Frost <sfrost@snowman.net> writes:

* Andrew - Supernews (andrew+nonews@supernews.com) wrote:

It's not MVCC-safe even with the AccessExclusive lock;

This seems like something which should probably be fixed,

You've missed the point entirely: this *cannot* be fixed, at least not
without giving up the performance advantages that make TRUNCATE
interesting.

Alright, can we give that rather significant performance advantage to
non-owners in some way then? Perhaps as an extra grant right?

This is along the lines of what I was thinking, though I do see that it
gets more complicated when dealing with transactions which started
before the one committing the truncate (Not a problem in my case, but
would have to be dealt with to be MVCC-safe):

TRUNCATE is fast because it knows that it's delete'ing everything and
so it just creates a new (empty) file and deletes the old file. DELETE
goes through the entire file marking each record for deletion and then
the system has to wait around for the vacuum'er to come through and
clean up the file. New transactions using that file have to scan past
all of the deleted tuples until they get vacuumed though. My thinking
is along these lines:

delete from x;/truncate x;
--> Creates a new, empty, file and makes it the 'current' file
--> Marks the old file for deletion, but it is kept around for any
transactions which were started before the truncate;
--> New transactions use the empty file
--> Once all transactions using the old file have completed, the old
file can be deleted.
--> Old transactions which insert rows would need to use the new file
or scan the old file for rows which they added, I suppose.

Thanks,

Stephen

On Thu, 7 Jul 2005, Stephen Frost wrote:

* Andrew - Supernews (andrew+nonews@supernews.com) wrote:

On 2005-07-07, Stephen Frost <sfrost@snowman.net> wrote:

* truncate is not MVCC-safe.

Erm, that's why it gets a stronger lock, so I don't really see what
this has to do with it.

It's not MVCC-safe even with the AccessExclusive lock; it damages snapshots
that were taken before the truncate operation but which don't have a lock
on the table yet. The only reason it doesn't break pg_dump is that the
first thing that pg_dump does is to take AccessShare locks on every table
that it's going to dump.

This seems like something which should probably be fixed, but which is
probably too late to fix for 8.1. Of course, if we could fix this then
it seems like it would be possible for us to just change 'delete from x'
to behave as truncate does now given appropriate conditions. I'm not as

Doesn't the lock difference between delete and truncate mean that suddenly
deletes on x may or may not block concurrent selects to x (depending on
whether it's a full table delete and whether x has delete triggers)? Or
are you thinking that after making it MVCC safe the lock could be
lessened?

With the current truncate lock, it seems bad to me for users who want to
do:
begin;
delete from x;
-- do inserts and other stuff to the now empty x

while still allowing access to x. Especially if whether or not you have
access depends on whether there are delete triggers on x.

Does truncate not being MVCC-safe cause problems in your situation? It
certainly doesn't in mine and I expect the same is true for alot of
others in the same situation.

Well, it is done inside a transaction, plus has concurrent use...

Chris

Stephen Frost wrote:

delete from x;/truncate x;
--> Creates a new, empty, file and makes it the 'current' file
--> Marks the old file for deletion, but it is kept around for any
transactions which were started before the truncate;
--> New transactions use the empty file
--> Once all transactions using the old file have completed, the old
file can be deleted.
--> Old transactions which insert rows would need to use the new file
or scan the old file for rows which they added, I suppose.

And when the transaction that issued the TRUNCATE aborts after step 3,
but newer transactions commit?

Mike Mascari

* Mike Mascari (mascarm@mascari.com) wrote:

Stephen Frost wrote:

delete from x;/truncate x;
--> Creates a new, empty, file and makes it the 'current' file
--> Marks the old file for deletion, but it is kept around for any
transactions which were started before the truncate;
--> New transactions use the empty file
--> Once all transactions using the old file have completed, the old
file can be deleted.
--> Old transactions which insert rows would need to use the new file
or scan the old file for rows which they added, I suppose.

And when the transaction that issued the TRUNCATE aborts after step 3,
but newer transactions commit?

The newer transactions would have to check for that situation. It's not
completely thought through, but at the same time I don't necessairly
think it's something that would be completely impossible to do and still
retain most of the performance benefits, at least in the most common
case.

Thanks,

Stephen

Stephen Frost <sfrost@snowman.net> writes:

* Mike Mascari (mascarm@mascari.com) wrote:

And when the transaction that issued the TRUNCATE aborts after step 3,
but newer transactions commit?

The newer transactions would have to check for that situation.

How would they do that? They might be long gone by the time the
truncating transaction rolls back.

It might be possible to do something that preserves full MVCC-ness for
concurrent readers, but I don't believe there is any choice but to lock
out concurrent writers until the truncate commits. If you try to allow
that, there's no way to keep straight whose change goes into which file.

regards, tom lane

On L, 2005-07-09 at 09:47 -0400, Mike Mascari wrote:

Stephen Frost wrote:

delete from x;/truncate x;
--> Creates a new, empty, file and makes it the 'current' file
--> Marks the old file for deletion, but it is kept around for any
transactions which were started before the truncate;
--> New transactions use the empty file
--> Once all transactions using the old file have completed, the old
file can be deleted.
--> Old transactions which insert rows would need to use the new file
or scan the old file for rows which they added, I suppose.

And when the transaction that issued the TRUNCATE aborts after step 3,
but newer transactions commit?

should be the same as when newer transactions had used a file after a
DELETE ; had been issued.

Could the new file not be made to cover the next available 1GB of file
space, that is a new physical file ?

This could made using of same kind of machinery my proposal for
concurrent index does (i.e. locks that forbid putting new tuples in
certain tuple ranges)

Then, if the truncating transaction commits, the N first pgysical 1GB
files are removed, and just the remaining ones are used. if it aborts,
the first files stay, and we just have some tuples placed sparcely
starting at the next 1GB boundary.

--
Hannu Krosing <hannu@skype.net>

On Sat, Jul 09, 2005 at 11:43:52PM +0300, Hannu Krosing wrote:

Could the new file not be made to cover the next available 1GB of file
space, that is a new physical file ?

This could made using of same kind of machinery my proposal for
concurrent index does (i.e. locks that forbid putting new tuples in
certain tuple ranges)

I think your proposals are too "handwavy", but there is a similar
mechanism outlined for on-line index reorganizarion, whereby new tuples
can be inserted concurrently with the reorganization, being stored on a
"spill area". See

@inproceedings{DBLP:conf/sigmod/ZouS96,
author = {C. Zou and B. Salzberg},
editor = {H. V. Jagadish and Inderpal Singh Mumick},
title = {On-line Reorganization of Sparsely-populated B+trees},
booktitle = {Proceedings of the 1996 ACM SIGMOD International Conference on
Management of Data, Montreal, Quebec, Canada, June 4-6, 1996},
publisher = {ACM Press},
year = {1996},
pages = {115-124},
bibsource = {DBLP, \url{http://dblp.uni-trier.de}}
}

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Para tener m�s hay que desear menos"