pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
possible bug?
7+ messages / 5 participants
[nested] [flat]

* possible bug?
@ 1999-10-25 01:22  Clayton Cottingham <drfrog@smartt.com>
  0 siblings, 4 replies; 7+ messages in thread

From: Clayton Cottingham @ 1999-10-25 01:22 UTC (permalink / raw)
  To: pgsql-sql

can anyone confirm this?

create index idx on table using btree(column  );
cluster idx on table;
drop index idx;

seems to drop all permissions and indexes
on the table involved

ive tested in 6.5 and 6.5.2





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] possible bug?
@ 1999-10-25 02:48  Mathijs Brands <mathijs@ilse.nl>
  parent: Clayton Cottingham <drfrog@smartt.com>
  3 siblings, 1 reply; 7+ messages in thread

From: Mathijs Brands @ 1999-10-25 02:48 UTC (permalink / raw)
  To: Clayton Cottingham <drfrog@smartt.com>; +Cc: pgsql-sql

On Mon, Oct 25, 1999 at 01:22:06AM +0000, Clayton Cottingham allegedly wrote:
> can anyone confirm this?
> 
> create index idx on table using btree(column  );
> cluster idx on table;
> drop index idx;
> 
> seems to drop all permissions and indexes
> on the table involved
> 
> ive tested in 6.5 and 6.5.2

This is correct. I've had this problem myself. When you check the source,
there is a comment about this, but nothing useful. I myself would
consider this a bug too, but I'm not sure about the developers.

Hmm, another useles email.

Mathijs



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] possible bug?
@ 1999-10-25 03:47  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Clayton Cottingham <drfrog@smartt.com>
  3 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 1999-10-25 03:47 UTC (permalink / raw)
  To: Clayton Cottingham <drfrog@smartt.com>; +Cc: pgsql-sql

Clayton Cottingham <drfrog@smartt.com> writes:
> can anyone confirm this?
> create index idx on table using btree(column  );
> cluster idx on table;
> drop index idx;
> seems to drop all permissions and indexes
> on the table involved

"cluster" deliberately drops all other indexes on the target table,
according to the source code.  (If this isn't mentioned in the
documentation, it should be.)

It looks like the implementation method is to build a whole new
table, destroy the old, and rename the new into place.  That probably
explains why the permissions get lost --- I suppose triggers &etc
are not carried over either, and heaven help you if you try to cluster
a member of an inheritance hierarchy...

I have no idea how hard it'd be to make a less destructive version
of "cluster".  Another item for the TODO list, I guess.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] possible bug?
@ 1999-10-25 08:52  Peter Eisentraut <e99re41@DoCS.UU.SE>
  parent: Clayton Cottingham <drfrog@smartt.com>
  3 siblings, 0 replies; 7+ messages in thread

From: Peter Eisentraut @ 1999-10-25 08:52 UTC (permalink / raw)
  To: Clayton Cottingham <drfrog@smartt.com>; +Cc: pgsql-sql

From the manual for CLUSTER:

"The table is actually copied to a temporary table in index order, then
renamed back to the original name. For this reason, all grant permissions
and other indexes are lost when clustering is performed."

So this is not a bug in the sense that no one knows about it, but it's an
unfortunate side-effect for sure.

	-Peter

On Mon, 25 Oct 1999, Clayton Cottingham wrote:

> can anyone confirm this?
> 
> create index idx on table using btree(column  );
> cluster idx on table;
> drop index idx;
> 
> seems to drop all permissions and indexes
> on the table involved
> 
> ive tested in 6.5 and 6.5.2
> 
> 
> 
> ************
> 
> 

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] possible bug?
@ 1999-10-26 04:53  Bruce Momjian <maillist@candle.pha.pa.us>
  parent: Clayton Cottingham <drfrog@smartt.com>
  3 siblings, 0 replies; 7+ messages in thread

From: Bruce Momjian @ 1999-10-26 04:53 UTC (permalink / raw)
  To: Clayton Cottingham <drfrog@smartt.com>; +Cc: pgsql-sql

> can anyone confirm this?
> 
> create index idx on table using btree(column  );
> cluster idx on table;
> drop index idx;
> 
> seems to drop all permissions and indexes
> on the table involved
> 

Yes.  See CLUSTER manual page.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] possible bug?
@ 1999-10-26 04:55  Bruce Momjian <maillist@candle.pha.pa.us>
  parent: Mathijs Brands <mathijs@ilse.nl>
  0 siblings, 0 replies; 7+ messages in thread

From: Bruce Momjian @ 1999-10-26 04:55 UTC (permalink / raw)
  To: Mathijs Brands <mathijs@ilse.nl>; +Cc: Clayton Cottingham <drfrog@smartt.com>; pgsql-sql

> On Mon, Oct 25, 1999 at 01:22:06AM +0000, Clayton Cottingham allegedly wrote:
> > can anyone confirm this?
> > 
> > create index idx on table using btree(column  );
> > cluster idx on table;
> > drop index idx;
> > 
> > seems to drop all permissions and indexes
> > on the table involved
> > 
> > ive tested in 6.5 and 6.5.2
> 
> This is correct. I've had this problem myself. When you check the source,
> there is a comment about this, but nothing useful. I myself would
> consider this a bug too, but I'm not sure about the developers.
> 

Man pages says:

       The table is actually copied to a temporary table in index
       order, then renamed back to the original  name.  For  this
       reason,  all  grant permissions and other indexes are lost
       when clustering is performed.



-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] possible bug?
@ 1999-10-26 04:58  Bruce Momjian <maillist@candle.pha.pa.us>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 7+ messages in thread

From: Bruce Momjian @ 1999-10-26 04:58 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Clayton Cottingham <drfrog@smartt.com>; pgsql-sql

> Clayton Cottingham <drfrog@smartt.com> writes:
> > can anyone confirm this?
> > create index idx on table using btree(column  );
> > cluster idx on table;
> > drop index idx;
> > seems to drop all permissions and indexes
> > on the table involved
> 
> "cluster" deliberately drops all other indexes on the target table,
> according to the source code.  (If this isn't mentioned in the
> documentation, it should be.)
> 
> It looks like the implementation method is to build a whole new
> table, destroy the old, and rename the new into place.  That probably
> explains why the permissions get lost --- I suppose triggers &etc
> are not carried over either, and heaven help you if you try to cluster
> a member of an inheritance hierarchy...
> 
> I have no idea how hard it'd be to make a less destructive version
> of "cluster".  Another item for the TODO list, I guess.
> 

Already on TODO list.  Text added to make it clearer.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~1999-10-26 04:58 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-10-25 01:22 possible bug? Clayton Cottingham <drfrog@smartt.com>
1999-10-25 02:48 ` Mathijs Brands <mathijs@ilse.nl>
1999-10-26 04:55   ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-10-25 03:47 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-10-26 04:58   ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-10-25 08:52 ` Peter Eisentraut <e99re41@DoCS.UU.SE>
1999-10-26 04:53 ` Bruce Momjian <maillist@candle.pha.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox