Re: [PATCHES] Tablespace patch review

2004-06-19 Thread Dave Page



 -Original Message-
 From: [EMAIL PROTECTED] on behalf of Bruce Momjian
 Sent: Sat 6/19/2004 1:05 AM
 To: Andreas Pflug
 Cc: Tom Lane; Gavin Sherry; PostgreSQL-patches
 Subject: Re: [PATCHES] Tablespace patch review
 
 We can build a gui on top of the command-line tool, no?

No, we can't. Don't forget, everything we do in pgAdmin is via libpq.

Regards, Dave

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [PATCHES] Tablespace patch review

2004-06-19 Thread Andreas Pflug
Tom Lane wrote:
Andreas Pflug [EMAIL PROTECTED] writes:
 

Tom Lane wrote:
   

As for the authentication-is-expensive issue, what of it?  You *should*
have to authenticate yourself in order to look inside another person's
database.  The sort of cross-database inspection being proposed here
would be a big security hole in many people's view.
 

Accessing pg_class et al using the current sysuseid with acl checking 
should be ok and satisfy security demands, no?
   

No.  If the other user has you locked out from connecting to his
database at all, he's probably not going to feel that he should have to
disable your access to individual objects inside it.
 

Well he's using my tablespace, so I'd like to know at least the object name.
This has some connections to the discussions we periodically have about
preventing Joe User from looking at the system catalogs.  If we make any
changes in this area at all, I would expect them to be in the direction
of narrowing access, not widening it to include being able to see
other databases' catalogs.
 

Superuser/tablespace owner isn't quite Joe User, I believe.
Actually, there seem quite some other cross database/shared table issues 
(schema default tablespace, dropping user who owns objects) which make 
it desirable to have superuser readonly access to pg_catalog tables. 
Maybe a todo for 7.6...

Regards,
Andreas

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PATCHES] Tablespace patch review

2004-06-19 Thread Andreas Pflug
Dave Page wrote:
-Original Message-
From: [EMAIL PROTECTED] on behalf of Bruce Momjian
Sent: Sat 6/19/2004 1:05 AM
To: Andreas Pflug
Cc: Tom Lane; Gavin Sherry; PostgreSQL-patches
Subject: Re: [PATCHES] Tablespace patch review
We can build a gui on top of the command-line tool, no?
 

No, we can't. Don't forget, everything we do in pgAdmin is via libpq.
 

Yeah, gui on top of cmd line is nasty and a pain in regarding 
portability. It's not exactly challenging to implement it directly 
either, that's what I'll do in absence of a serverside solution. I'll 
redirect all pgadmin user speed complaints about this to Toms personal 
mailbox ;-)

Regards,
Andreas

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PATCHES] Tablespace patch review

2004-06-19 Thread Andreas Pflug
Bruce Momjian wrote:

I don't see why an admin tool can't connect to each database and get a
listing of what is in each tablespace.  I don't think connecting to 100
databases to get that information will be slow.
 

Well, whatever you call slow or not slow.
I checked it; connecting 10 databases, retrieving tablespace 
dependencies (pg_class union pg_schema) and closing takes about one 
second over an ssl connection, 0.2 seconds with non-ssl. This was a 
trusted connection, can't check what will happen with md5, krb or so.

Regards,
Andreas

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html


Re: [PATCHES] Tablespace patch review

2004-06-19 Thread Dave Page



 -Original Message-
 From: Andreas Pflug [mailto:[EMAIL PROTECTED]
 Sent: Sat 6/19/2004 6:40 PM
 To: Bruce Momjian
 Cc: Dave Page; Tom Lane; PostgreSQL-patches
 Subject: Re: [PATCHES] Tablespace patch review
  
 Well, whatever you call slow or not slow.
 I checked it; connecting 10 databases, retrieving tablespace 
 dependencies (pg_class union pg_schema) and closing takes about one 
 second over an ssl connection, 0.2 seconds with non-ssl. This was a 
 trusted connection, can't check what will happen with md5, krb or so.

Don't suppose you happened to try it on Win32 did you?

Regards, Dave




---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


Re: [PATCHES] Tablespace patch review

2004-06-19 Thread Andreas Pflug
Dave Page wrote:
 

-Original Message-
From: Andreas Pflug [mailto:[EMAIL PROTECTED]
Sent: Sat 6/19/2004 6:40 PM
To: Bruce Momjian
Cc: Dave Page; Tom Lane; PostgreSQL-patches
Subject: Re: [PATCHES] Tablespace patch review
Well, whatever you call slow or not slow.
I checked it; connecting 10 databases, retrieving tablespace 
dependencies (pg_class union pg_schema) and closing takes about one 
second over an ssl connection, 0.2 seconds with non-ssl. This was a 
trusted connection, can't check what will happen with md5, krb or so.
   

Don't suppose you happened to try it on Win32 did you?
 

This was from a win32 workstation (pgadmin3) to a Linux server.
Regards,
Andreas

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send unregister YourEmailAddressHere to [EMAIL PROTECTED])


Re: [PATCHES] Tablespace patch review

2004-06-19 Thread Bruce Momjian
Tom Lane wrote:
 Bruce Momjian [EMAIL PROTECTED] writes:
  Well, I didn't use tablespaces here so the pg_tablespaces directory is
  empty, so I can't think of what the tablespace is.
 
 You look in the pg_tablespace catalog for the row with that OID.
 
  Also, are we calling it pg_tablespaces (plural) rather than
  pg_tablespace?
 
 I didn't have any particular opinion about that till just now ...
 but now I see that it's a good idea for the pg_tablespaces directory
 (the one that holds all the symlinks) to have a different name from the
 pg_tablespace catalog, especially since the latter has a couple of rows
 that do not correspond to any entries in the former.

If you want something distinct, which I understand, perhaps pg_tblspc.

 I'm not wedded to pg_tablespaces as the name in particular, but
 it should not be pg_tablespace, or we'll suffer the same confusion
 over and over that you just did.

OK.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html


Re: [PATCHES] Tablespace patch review

2004-06-19 Thread Bruce Momjian
Andreas Pflug wrote:
 Bruce Momjian wrote:
 
 
 
 I don't see why an admin tool can't connect to each database and get a
 listing of what is in each tablespace.  I don't think connecting to 100
 databases to get that information will be slow.
 
   
 
 Well, whatever you call slow or not slow.
 I checked it; connecting 10 databases, retrieving tablespace 
 dependencies (pg_class union pg_schema) and closing takes about one 
 second over an ssl connection, 0.2 seconds with non-ssl. This was a 
 trusted connection, can't check what will happen with md5, krb or so.

Well, we could use something like dbsize to report how much disk space
is used by each database in the tablespace (it does an 'ls' in the
directory as a server-side function), and connect to the database to get
actual table names.

I can't think of any clean system that would allow access to the table
names in other databases without connecting to them.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html


Re: [PATCHES] Tablespace patch review

2004-06-18 Thread Tom Lane
Bruce Momjian [EMAIL PROTECTED] writes:
 OK, I have reviewed the patch.  I think Tom is doing the same, but I
 want to report the things I found.

I just came up for air after about two solid days of working on this
patch ... had not seen your message before committing it.  The good
news is that I think I did see all the stuff you found.

 What facility is there for moving objects between tablespaces?

None, as yet.

 Seems we should be consistent in having WIN32 defs or not.

Probably.  I removed #ifdefs whereever possible --- there are just a few
left in tablespace.c and dbcommands.c now.  I was contemplating
replacing HAVE_SYMLINKS with a HAVE_TABLESPACES flag, but with the
occurrences isolated to one file I'm not sure it's worth the trouble.

 Your code in tablespc.c calls realpath().  Do all OS's have that?

It doesn't anymore --- I was concerned about the portability question
too.  The only point of that code AFAICS was to prevent creation of
two pg_tablespace entries pointing at the same directory.  I felt that
the better way to handle this was to write a PG_VERSION file in the
tablespace directory during CREATE TABLESPACE.  A subsequent CREATE
TABLESPACE on the same directory will fail because the directory isn't
empty anymore.  And the version file might come in handy someday...

 Does all object creation code put a lock in the tablespace row to
 prevent DROP TABLESPACE from removing a tablespace in use?

I hacked up some logic to deal with this, based on taking out an
ExclusiveLock on pg_tablespace when adding a per-database subdirectory
to a tablespace directory or doing DROP TABLESPACE.  It works but it'd
be nice to reduce the strength of the lock ...

 There is interesting code that checks to see of the objects existing in
 a tablespace are about to be dropped by the transaction.

s/interesting/doesn't work/ ... I didn't commit this stuff.  Nor the
smgr changes either; those were far from ready for prime time.

 Are we ripping out our initlocation code at the same time?

Not done yet, but it's dead and should be removed as soon as a
decent respect for the deceased permits ;-)

 Where do we need to add mention of tablespaces in the main
 non-reference-page docs?  Clearly at least in the section on managing
 disk space.

Yeah.  The patch as committed covers the reference pages, but we
desperately need a higher-level discussion of tablespaces for the
administrator's guide.

regards, tom lane

---(end of broadcast)---
TIP 8: explain analyze is your friend


Re: [PATCHES] Tablespace patch review

2004-06-18 Thread Tom Lane
Bruce Momjian [EMAIL PROTECTED] writes:
 I have a few other questions:

 What is the procedure for moving tablespace directories?

There is none.

 However, pg_tablespace still has the old location.

Yup.  The *only* thing that pg_tablespace.spclocation is used for is
for pg_dumpall to dump appropriate CREATE TABLESPACE commands, so it's
not like you couldn't hack it after the fact.

 Do we need ALTER TABLESPACE to move tablespaces, and ALTER clauses to
 move objects to other tablespaces?  Are these TODO items for later?

TODO.  You sound like a man who's expecting a
several-generations-polished facility when we only just committed
the first version today.  I do not feel a need to have any of these
features in 7.5 ...

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PATCHES] Tablespace patch review

2004-06-18 Thread Gavin Sherry
On Fri, 18 Jun 2004, Tom Lane wrote:

 Bruce Momjian [EMAIL PROTECTED] writes:
  OK, I have reviewed the patch.  I think Tom is doing the same, but I
  want to report the things I found.

 I just came up for air after about two solid days of working on this
 patch ... had not seen your message before committing it.  The good
 news is that I think I did see all the stuff you found.

Awesome.


  What facility is there for moving objects between tablespaces?

 None, as yet.

  Seems we should be consistent in having WIN32 defs or not.

 Probably.  I removed #ifdefs whereever possible --- there are just a few
 left in tablespace.c and dbcommands.c now.  I was contemplating
 replacing HAVE_SYMLINKS with a HAVE_TABLESPACES flag, but with the
 occurrences isolated to one file I'm not sure it's worth the trouble.

  Your code in tablespc.c calls realpath().  Do all OS's have that?

 It doesn't anymore --- I was concerned about the portability question
 too.  The only point of that code AFAICS was to prevent creation of
 two pg_tablespace entries pointing at the same directory.  I felt that
 the better way to handle this was to write a PG_VERSION file in the
 tablespace directory during CREATE TABLESPACE.  A subsequent CREATE
 TABLESPACE on the same directory will fail because the directory isn't
 empty anymore.  And the version file might come in handy someday...

Yes. That's a better idea.

[snip]

  Where do we need to add mention of tablespaces in the main
  non-reference-page docs?  Clearly at least in the section on managing
  disk space.

 Yeah.  The patch as committed covers the reference pages, but we
 desperately need a higher-level discussion of tablespaces for the
 administrator's guide.

I'll look at this tomorrow.

Thanks for your assistance.


   regards, tom lane


Gavin

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PATCHES] Tablespace patch review

2004-06-18 Thread Bruce Momjian
Tom Lane wrote:
 Bruce Momjian [EMAIL PROTECTED] writes:
  I have a few other questions:
 
  What is the procedure for moving tablespace directories?
 
 There is none.
 
  However, pg_tablespace still has the old location.
 
 Yup.  The *only* thing that pg_tablespace.spclocation is used for is
 for pg_dumpall to dump appropriate CREATE TABLESPACE commands, so it's
 not like you couldn't hack it after the fact.
 
  Do we need ALTER TABLESPACE to move tablespaces, and ALTER clauses to
  move objects to other tablespaces?  Are these TODO items for later?
 
 TODO.  You sound like a man who's expecting a
 several-generations-polished facility when we only just committed
 the first version today.  I do not feel a need to have any of these
 features in 7.5 ...

I just need to know what to add to the TODO list, and so we can answer
people who are going to ask for this functionality.  Added to TODO:

* Allow reporting of which objects are in which tablespaces
* Allow database recovery where tablespaces can't be created
o Add ALTER TABLESPACE to change location, name, owner
o Allow objects to be moved between tablespaces

I think this all the items still needed.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 8: explain analyze is your friend


Re: [PATCHES] Tablespace patch review

2004-06-18 Thread Bruce Momjian
Tom Lane wrote:
  Are we ripping out our initlocation code at the same time?
 
 Not done yet, but it's dead and should be removed as soon as a
 decent respect for the deceased permits ;-)

You want me to do the honors?

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PATCHES] Tablespace patch review

2004-06-18 Thread Tom Lane
Bruce Momjian [EMAIL PROTECTED] writes:
 Tom Lane wrote:
 Are we ripping out our initlocation code at the same time?
 
 Not done yet, but it's dead and should be removed as soon as a
 decent respect for the deceased permits ;-)

 You want me to do the honors?

Nah, I'll get it.  I want to do some other small cleanup on that patch,
too.  (But Gavin, you're on the hook for a rewrite of the admin guide
section about alternate locations into something about tablespaces...)

Somebody's got to fix oid2name and dbsize though.  Bruce, you want
to catch those?

regards, tom lane

---(end of broadcast)---
TIP 8: explain analyze is your friend


Re: [PATCHES] Tablespace patch review

2004-06-18 Thread Tom Lane
Bruce Momjian [EMAIL PROTECTED] writes:
 Tom Lane wrote:
 Somebody's got to fix oid2name and dbsize though.  Bruce, you want
 to catch those?

 Uh, how do they have to be fixed?  Isn't the relfilenode unchanged?  Do
 we just need to add tablespace lookups?

How useful will oid2name be if it doesn't understand about tablespaces?
I dunno how it ought to be changed, but surely it needs some thought.

dbsize doesn't even compile right now, because it's using
GetDatabasePath which now has another argument.  I did not patch it
because it needs more thought: should it report the total of all
tablespaces for the database, or should its API be extended so you
can ask about individual tablespaces, or what?  In any case it's
not a one-liner fix...

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html


Re: [PATCHES] Tablespace patch review

2004-06-18 Thread Gavin Sherry
On Fri, 18 Jun 2004, Tom Lane wrote:

 Bruce Momjian [EMAIL PROTECTED] writes:
  Tom Lane wrote:
  Are we ripping out our initlocation code at the same time?
 
  Not done yet, but it's dead and should be removed as soon as a
  decent respect for the deceased permits ;-)

  You want me to do the honors?

 Nah, I'll get it.  I want to do some other small cleanup on that patch,
 too.  (But Gavin, you're on the hook for a rewrite of the admin guide
 section about alternate locations into something about tablespaces...)

I can either replace the Alternative Locations section or make a higher
level reference to tablespaces under Server Administration in the main
index. What do people think?

Gavin

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])


Re: [PATCHES] Tablespace patch review

2004-06-18 Thread Gavin Sherry
On Fri, 18 Jun 2004, Bruce Momjian wrote:

[snip]

  TODO.  You sound like a man who's expecting a
  several-generations-polished facility when we only just committed
  the first version today.  I do not feel a need to have any of these
  features in 7.5 ...

 I just need to know what to add to the TODO list, and so we can answer
 people who are going to ask for this functionality.  Added to TODO:

   * Allow reporting of which objects are in which tablespaces

Do we need an information_schema.tablespaces view as well as an update to
information_schema.{tables|indexes|...} ?

Gavin

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html


Re: [PATCHES] Tablespace patch review

2004-06-18 Thread Andreas Pflug
Gavin Sherry wrote:
On Fri, 18 Jun 2004, Andreas Pflug wrote:
 

Gavin Sherry wrote:
   

On Fri, 18 Jun 2004, Bruce Momjian wrote:
[snip]

 

TODO.  You sound like a man who's expecting a
several-generations-polished facility when we only just committed
the first version today.  I do not feel a need to have any of these
features in 7.5 ...
 

I just need to know what to add to the TODO list, and so we can answer
people who are going to ask for this functionality.  Added to TODO:
* Allow reporting of which objects are in which tablespaces
   

Do we need an information_schema.tablespaces view as well as an update to
information_schema.{tables|indexes|...} ?
 

I checked this to implement it, and found it being less then trivial
when *all* objects of a tablespace should be displayed, not just the
ones in the current database.
   

I don't think we should try and show all objects for a tablespace in
information_schema.
Agreed, information_schema is database specific. I was thinking about a 
pg_tablespace_contents(..) function anyway.

Being able to list all objects in a tablespace, including which databases
they are in, is clearly useful, however (eg: hunting down use of a give
tablespace that you want dropped). Sounds like a script in contrib (or the
main source tree?) to me.
 

You're suggesting the dblink way using a shell script. Imagine 20, 200, 
... databases. This would be a costly thing (and has to be  implemented 
differently in win32).
I'd like to see an implementation that enables gui interfaces to show 
objects that depend on a tablespace, so you'd need to be aware of a user 
clicking on show what's in that tablespace and he probably wouldn't 
expect to wait an extended period of time for all databases to be 
scanned, or impose a 200-connection load on the server.

IMHO checking objects in a tablespace is a routine administrative task, 
so it should be supported natively by the server without need of 
contribs. And for win user acceptance, a command line tool won't be 
sufficient either.

Regards,
Andreas

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


Re: [PATCHES] Tablespace patch review

2004-06-18 Thread Gavin Sherry
On Sat, 19 Jun 2004, Andreas Pflug wrote:

[snip]

 I don't think we should try and show all objects for a tablespace in
 information_schema.
 
 Agreed, information_schema is database specific. I was thinking about a
 pg_tablespace_contents(..) function anyway.

 Being able to list all objects in a tablespace, including which databases
 they are in, is clearly useful, however (eg: hunting down use of a give
 tablespace that you want dropped). Sounds like a script in contrib (or the
 main source tree?) to me.
 
 
 You're suggesting the dblink way using a shell script. Imagine 20, 200,
 ... databases. This would be a costly thing (and has to be  implemented
 differently in win32).
 I'd like to see an implementation that enables gui interfaces to show
 objects that depend on a tablespace, so you'd need to be aware of a user
 clicking on show what's in that tablespace and he probably wouldn't
 expect to wait an extended period of time for all databases to be
 scanned, or impose a 200-connection load on the server.

I see this more as a script like Tom described in another email. We'd have
a list of tablespacecs and databases and scan each database (on connection
at a time) and get the information the user wants.

 IMHO checking objects in a tablespace is a routine administrative task,
 so it should be supported natively by the server without need of
 contribs. And for win user acceptance, a command line tool won't be
 sufficient either.

I would debate that.

Firstly, tablespaces aren't supported on windows yet. Secondly, I'd think
that Unix users would be fine with a command line tool, especially one
that can connect to a remote host.

For those not used to command line tools, I can imagine extensions to
pgadmin or phppgadmin.

Gavin

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PATCHES] Tablespace patch review

2004-06-18 Thread Andreas Pflug
Gavin Sherry wrote:
I would debate that.
Firstly, tablespaces aren't supported on windows yet.
Just a matter of time. And I'm talking of win32 workstations connecting 
to *ix servers too.

Secondly, I'd think
that Unix users would be fine with a command line tool, especially one
that can connect to a remote host.
For those not used to command line tools, I can imagine extensions to
pgadmin or phppgadmin.
 

:-) :-) :-)
Unfortunately, us admin tool programmers can't practice witchcraft, so 
we need a pgsql function for that...
Certainly, we could iterate all known databases making a one-time 
connection (if allowed to connect, what about template0?), retrieving 
tablespace dependencies, and close again. As debated above, that's quite 
costly, especially if more sophisticated authentication mechanisms are used.

Regards,
Andreas

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


Re: [PATCHES] Tablespace patch review

2004-06-18 Thread Tom Lane
Andreas Pflug [EMAIL PROTECTED] writes:
 IMHO checking objects in a tablespace is a routine administrative task, 
 so it should be supported natively by the server without need of 
 contribs.

I strongly disagree.  Dropping a tablespace is not a routine activity,
and we don't have to have submillisecond response to operations that
are only needed when your first attempt to drop one fails.

As for the authentication-is-expensive issue, what of it?  You *should*
have to authenticate yourself in order to look inside another person's
database.  The sort of cross-database inspection being proposed here
would be a big security hole in many people's view.

 And for win user acceptance, a command line tool won't be 
 sufficient either.

This does not deserve a response.

regards, tom lane

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match


Re: [PATCHES] Tablespace patch review

2004-06-18 Thread Andreas Pflug
Tom Lane wrote:
Andreas Pflug [EMAIL PROTECTED] writes:
 

IMHO checking objects in a tablespace is a routine administrative task, 
so it should be supported natively by the server without need of 
contribs.
   

I strongly disagree.  Dropping a tablespace is not a routine activity,
 

Dropping certainly not. But inspecting? If implemented in a gui, it's 
just a click away.

As for the authentication-is-expensive issue, what of it?  You *should*
have to authenticate yourself in order to look inside another person's
database.  The sort of cross-database inspection being proposed here
would be a big security hole in many people's view.
 

Accessing pg_class et al using the current sysuseid with acl checking 
should be ok and satisfy security demands, no? Since it's the same 
cluster, we can be sure that it 's the same user in that cross db too. 
If the user has no access, the result won't have a meaning either.

The auth-is-expensive issue is about creating the db connection itself 
again and again, when we only want to change a database.

And for win user acceptance, a command line tool won't be 
sufficient either.
   

This does not deserve a response.
 

Well, that's not quite appropriate. A 'command line is enough for server 
maintenance' attitude won't attract win people; they're blind without a 
mouse.

Regards,
Andreas

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


Re: [PATCHES] Tablespace patch review

2004-06-18 Thread Tom Lane
Andreas Pflug [EMAIL PROTECTED] writes:
 Tom Lane wrote:
 As for the authentication-is-expensive issue, what of it?  You *should*
 have to authenticate yourself in order to look inside another person's
 database.  The sort of cross-database inspection being proposed here
 would be a big security hole in many people's view.
 
 Accessing pg_class et al using the current sysuseid with acl checking 
 should be ok and satisfy security demands, no?

No.  If the other user has you locked out from connecting to his
database at all, he's probably not going to feel that he should have to
disable your access to individual objects inside it.

This has some connections to the discussions we periodically have about
preventing Joe User from looking at the system catalogs.  If we make any
changes in this area at all, I would expect them to be in the direction
of narrowing access, not widening it to include being able to see
other databases' catalogs.

regards, tom lane

---(end of broadcast)---
TIP 8: explain analyze is your friend


Re: [PATCHES] Tablespace patch review

2004-06-18 Thread Bruce Momjian
Andreas Pflug wrote:
 And for win user acceptance, a command line tool won't be 
 sufficient either.
 
 
 
 This does not deserve a response.
   
 
 
 Well, that's not quite appropriate. A 'command line is enough for server 
 maintenance' attitude won't attract win people; they're blind without a 
 mouse.

We can build a gui on top of the command-line tool, no?

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PATCHES] Tablespace patch review

2004-06-18 Thread Tom Lane
Bruce Momjian [EMAIL PROTECTED] writes:
 Tom Lane wrote:
 How useful will oid2name be if it doesn't understand about tablespaces?
 I dunno how it ought to be changed, but surely it needs some thought.

 I assume we just need to add a tablespace display when run with no args,
 and a -s option to display _with_ -d to display only objects in that
 database.  We could go fancy and spin through all the databases and list
 the datbase name and objects in that tablespace.

I should think that the table-level display ought to show both the
relfilenode and tablespace OIDs for each table.

 Given the number of open items for 7.5, I am thinking of keeping this
 for post-feature freeze.  Both are contrib.

Right, I doubt Marc will object to fixing contrib stuff after feature
freeze ...

regards, tom lane

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [PATCHES] Tablespace patch review

2004-06-18 Thread Bruce Momjian
Tom Lane wrote:
 Bruce Momjian [EMAIL PROTECTED] writes:
  Tom Lane wrote:
  How useful will oid2name be if it doesn't understand about tablespaces?
  I dunno how it ought to be changed, but surely it needs some thought.
 
  I assume we just need to add a tablespace display when run with no args,
  and a -s option to display _with_ -d to display only objects in that
  database.  We could go fancy and spin through all the databases and list
  the datbase name and objects in that tablespace.
 
 I should think that the table-level display ought to show both the
 relfilenode and tablespace OIDs for each table.

This is the existing display:

(3) aspg oid2name -d test
All tables from database test:
-
17147  = sql_features
17152  = sql_implementation_info
17157  = sql_languages
17162  = sql_packages
17167  = sql_sizing
17172  = sql_sizing_profiles
17220  = x

For objects in the default tablespace, they don't show a tablespace oid,
right?  Where do we put it?  A column that will be empty if they don't
use tablespaces?

  Given the number of open items for 7.5, I am thinking of keeping this
  for post-feature freeze.  Both are contrib.
 
 Right, I doubt Marc will object to fixing contrib stuff after feature
 freeze ...

Also, remember I am only online fulltime for another two days, then I am
leaving for Europe, return on July 3, after feature freeze.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match


Re: [PATCHES] Tablespace patch review

2004-06-18 Thread Christopher Kings-Lynne
Are we ripping out our initlocation code at the same time?
Not done yet, but it's dead and should be removed as soon as a
decent respect for the deceased permits ;-)

You want me to do the honors?
What about people upgrading from 7.4 databases that used database locations?
Chris
---(end of broadcast)---
TIP 8: explain analyze is your friend


Re: [PATCHES] Tablespace patch review

2004-06-18 Thread Tom Lane
Christopher Kings-Lynne [EMAIL PROTECTED] writes:
 What about people upgrading from 7.4 databases that used database locations?

They'll get a nice warning:

regression=# create database foo location 'bar';
WARNING:  LOCATION is not supported anymore
HINT:  Consider using tablespaces instead.
CREATE DATABASE

and everything will go into the default tablespace.  I don't really
see how to do much better than that ...

regards, tom lane

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match


Re: [PATCHES] Tablespace patch review

2004-06-18 Thread Tom Lane
Bruce Momjian [EMAIL PROTECTED] writes:
 Tom Lane wrote:
 I should think that the table-level display ought to show both the
 relfilenode and tablespace OIDs for each table.

 For objects in the default tablespace, they don't show a tablespace oid,
 right?  Where do we put it?  A column that will be empty if they don't
 use tablespaces?

pg_class will show a zero for objects in the default tablespace, but
I think oid2name should pull the actual tablespace ID from
pg_database.dattablespace and show that.  The convention about zero
is just to make life simple for CREATE DATABASE --- users of oid2name
should not have to think about it.

regards, tom lane

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match


Re: [PATCHES] Tablespace patch review

2004-06-18 Thread Bruce Momjian
Tom Lane wrote:
 Bruce Momjian [EMAIL PROTECTED] writes:
  Tom Lane wrote:
  I should think that the table-level display ought to show both the
  relfilenode and tablespace OIDs for each table.
 
  For objects in the default tablespace, they don't show a tablespace oid,
  right?  Where do we put it?  A column that will be empty if they don't
  use tablespaces?
 
 pg_class will show a zero for objects in the default tablespace, but
 I think oid2name should pull the actual tablespace ID from
 pg_database.dattablespace and show that.  The convention about zero
 is just to make life simple for CREATE DATABASE --- users of oid2name
 should not have to think about it.

Well, I didn't use tablespaces here so the pg_tablespaces directory is
empty, so I can't think of what the tablespace is.  Is it the database
oid?   Also, are we calling it pg_tablespaces (plural) rather than
pg_tablespace?

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [PATCHES] Tablespace patch review

2004-06-18 Thread Tom Lane
Bruce Momjian [EMAIL PROTECTED] writes:
 Well, I didn't use tablespaces here so the pg_tablespaces directory is
 empty, so I can't think of what the tablespace is.

You look in the pg_tablespace catalog for the row with that OID.

 Also, are we calling it pg_tablespaces (plural) rather than
 pg_tablespace?

I didn't have any particular opinion about that till just now ...
but now I see that it's a good idea for the pg_tablespaces directory
(the one that holds all the symlinks) to have a different name from the
pg_tablespace catalog, especially since the latter has a couple of rows
that do not correspond to any entries in the former.

I'm not wedded to pg_tablespaces as the name in particular, but
it should not be pg_tablespace, or we'll suffer the same confusion
over and over that you just did.

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html


Re: [PATCHES] Tablespace patch review

2004-06-17 Thread Bruce Momjian
Bruce Momjian wrote:
 Gavin Sherry wrote:
  Attached is an updated patch, fixing a compile error which my compiler
  didn't seem to detect/suffer from and incorporating fixes to problems
  raised by Neil.
  
  Thanks,
  
  Gavin
 
 OK, I have reviewed the patch.  I think Tom is doing the same, but I
 want to report the things I found.

I have a few other questions:

What is the procedure for moving tablespace directories?  I assume with
the postmaster down the directory can be moved and the symlink changed. 
However, pg_tablespace still has the old location.  Should we use lstat
so pg_tablespace gets updated automatically or as part of pg_dump, or
throw a server message if the symlink doesn't match pg_tablespace.  We
need to add instructions that pg_tablespace needs to be updated if the
symlink is changed.  What bothers me is that someone updating just the
symlink might run fine but would not be able to restore a dump to the
same machine.

And about restore, particularly to another machine, what do we do if the
tablespace can't be created in the location specified in the dump?  The
tablespace creation fails, and all objects specified in that tablespace
also fail?  Seems bad, particularly if you are restoring after a
hardware failure.  Do we need a GUC that says if the tablespace doesn't
exist, create the object in the default location?   Do we need a
pg_dump option that ignores tablespaces completely for portability and
for restoring to another server?

Is pg_dump smart enough not to emit the tablespace if the object would
already be created in the right tablespace, perhaps because of its
schema?  The new tablespace clause adds a non-standard clause to CREATE
TABLE, something we were hoping to avoid, but I doubt it is possible.

Do we need ALTER TABLESPACE to move tablespaces, and ALTER clauses to
move objects to other tablespaces?  Are these TODO items for later?

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


Re: [PATCHES] Tablespace patch review

2004-06-17 Thread Christopher Kings-Lynne
I wrote the pg_dump bits, so I guess I can answer these...
And about restore, particularly to another machine, what do we do if the
tablespace can't be created in the location specified in the dump?  The
tablespace creation fails, and all objects specified in that tablespace
also fail?  Seems bad, particularly if you are restoring after a
hardware failure.  Do we need a GUC that says if the tablespace doesn't
exist, create the object in the default location?   Do we need a
pg_dump option that ignores tablespaces completely for portability and
for restoring to another server?
Wll.  There's a lot of stuff that can already fail in a restore - 
you always must watch your restore output to ensure things have worked. 
 When I restore and it can't create a tsearch function because I forgot 
to install the tsearch.so, it errors and then keeps going with tables 
and triggers and all sorts of stuff failing to restore because they 
depended on that function.  I don't see the failure to create a 
tablespace as being any different.

Is pg_dump smart enough not to emit the tablespace if the object would
already be created in the right tablespace, perhaps because of its
schema?  
Yes it is, I was very careful about that.  If you never use a tablespace 
in your life, you will never see tablespace commands in your dumps.  The 
changes to pg_dump work just fine against a pre-7.5 backend and again no 
tablespace commands will be output.

The new tablespace clause adds a non-standard clause to CREATE
TABLE, something we were hoping to avoid, but I doubt it is possible.
Well, if you don't use them, you won't see them, so I don't have a 
problem with that :)

Do we need ALTER TABLESPACE to move tablespaces, and ALTER clauses to
move objects to other tablespaces?  Are these TODO items for later?
Would be nice :)  I'm also in favour of ADD [PRIMARY KEY | UNIQUE 
](blah) TABLESPACE asdf which isn't in the current patch.

Chris
---(end of broadcast)---
TIP 8: explain analyze is your friend