Re: [PATCHES] Tablespace patch review
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
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] Nested transactions
Alvaro Herrera <[EMAIL PROTECTED]> writes: > I don't think we should explicitly forbid it. I think it should be > forbidden to close the outermost transaction inside a function (else the > function would not be able to terminate correctly), but for levels > before that one it'd be OK. More specifically, a function cannot close any xact that was open when it was called. It can only close xacts that it started (or equivalently, roll back to savepoints that it established). Otherwise the behavior is nonsensical. Rollback of an outer transaction would mean making like the function call never even happened, so the function certainly couldn't expect to keep control. 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] Nested transactions
Bruce Momjian <[EMAIL PROTECTED]> writes: > Did I make a mistake by promoting subtransactions rather than > savepoints? No. We can implement savepoints on top of subtransactions, but not vice versa. AFAICS the savepoint syntax is just a shorthand for a constrained form of subtransaction --- essentially one where you can't explicitly commit a subtransaction, it's implicitly committed when you commit the parent. I don't see anything but syntactic sugar here ... 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
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
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
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
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...) > > 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? -- 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
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
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
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
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.
There seem to be several ways to implement:
(1) dblink like access iterating all databases. This seems
extraordinarily expensive
(2) low level scanning tablespace location, then try to translate found
oids to names withoug SPI. Probably not desirable.
(3) extending parser/executor to allow a table specification of the form
database.namespace.tablename. This has been requested several times
before, but was rejected, with the advise to use dblink.
(4) Copy the contents of the desired cross-db table to a temporary table
(generate new oid in local db, copy cross-db table file to new oid file,
insert pg_class). A typically weird Andreas' approach :-)
Regards,
Andreas
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [PATCHES] Nested transactions
The other thing that I have been meaning to say in this thread is that I don't like using COMMIT to mean subtransaction commit (vs. introducing a new command for it) because of the following situation. Lets say that I have a java method that takes a jdbc connection and this code starts a transaction and does some work then issues a commit to ensure the changes have been committed to the database, and then does some other work outside the database based on the fact that the commit was sucessfull and it therefore knows the data is saved to disk (i.e. send out an email notification, or any number of other non-database tasks). Now lets suppose that someone calls this method with a database connection that already has a transaction started, so that this method really is beginning and working with a sub-transaction. Now when it commits it doesn't know if the changes will ever get to disk since its commit could be rolled back later. So this code gets broken. I like the functionality of nested transactions, I just think that there needs to be different commands other than BEGIN/COMMIT to work with them. So that there is no possiblity for misunderstanding what COMMIT really means. thanks, --Barry Alvaro Herrera wrote: On Wed, Jun 16, 2004 at 11:45:36PM +0100, Simon Riggs wrote: The patch looks impressively technical, but overall I'm not exactly sure what it does...I guess I'm just not clear why I would want it, except as the main technical pre-work to later syntax changes. I'm sure some short explanations would clear that up for me very quickly... :) Right. I have never intended to be implementing a known SQL standard feature. What I'm doing is allowing the whole backend to go back to a know state after an error is encountered. With this in place, implementing SAVEPOINTs the way SQL expects them to work appears to be a very trivial exercise. Perhaps what I've just asked about is trivial icing on the cake you've just baked, I think this phrase very precisely describes it. At least, that's what I expect. You may not see it, but a savepoint is just the start of a nested transaction in disguise. Consider: begin; insert into foo values (1); savepoint dammit; insert into foo values (2); select foo; -- fails rollback to dammit; insert into foo values (3); commit; You expect the transaction to finish with tuples 1 and 3 in table foo, right? Well, this is exactly the same as begin; insert into foo values (1); begin; -- dammit insert into foo values (2); select foo; -- fails, goes to aborted state rollback; insert into foo values (3); commit; So all that's needed for the former to work is to be able to define a "name" for a transaction (using a cute syntax) and being able to rollback to it. Definitely trivial, after all the work I have put into making the latter work. In extant releases you can only do this: begin; insert into foo values (1); insert into foo values (2); select foo; -- oops, can't go back! rollback; begin; insert into foo values (1); insert into foo values (3); commit; You are forced to send all the commands before the aborting one to the server again. And there's no way to "undo" a command in the transaction, short of aborting it completely. I don't know what Oracle or other DBMSs expect in this area. Anyone care to give me a few pointers? If I'm missing something, I want to know as soon as possible. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] Tablespace patch review
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. That, as you point out, is hard and possibly gets
users into some messy situations.
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.
Gavin
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] Tablespace patch review
Gavin Sherry <[EMAIL PROTECTED]> writes:
> On Fri, 18 Jun 2004, Bruce Momjian wrote:
>> * 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|...} ?
That would depend on your theology about whether implementation-specific
additions to information_schema are a good idea or not. I'd lean
against doing this myself. ISTM the entire point of information_schema
is to be standard, and additions are, well, not. Worse, they might
conflict with future extensions to the standard.
I don't actually see a way *inside the database* to implement Bruce's
TODO item; there's no way for a backend to look at the catalogs of other
databases. We could look to see which databases had nonempty
subdirectories of a tablespace, and report those databases by name, but
explaining exactly what's inside those subdirectories is a lot harder.
It might be better to think about supporting this need with oid2name
or some similar client-level tool. It's easy to see how oid2name
might be extended to produce an output like
Tablespace t1
Database d1
Table x
Table y
Database d2
etc etc
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] Tablespace patch review
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
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] Nested transactions
Barry Lind <[EMAIL PROTECTED]> writes: > I like the functionality of nested transactions, I just think that there > needs to be different commands other than BEGIN/COMMIT to work with > them. So that there is no possiblity for misunderstanding what COMMIT > really means. There's something to be said for that view. Another thing in its favor is that if we choose names like SUBBEGIN and SUBCOMMIT, then we get rid of the syntax conflict with plpgsql's BEGIN/END. A function cannot legally issue a true COMMIT, as it has to be inside an outer transaction --- so it only needs to be able to say SUBBEGIN and SUBCOMMIT. I'm not at all wedded to those particular names, of course. Just thinking that it'd simplify life if they were spelled differently than BEGIN and END. regards, tom lane ---(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
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
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] Nested transactions
Barry Lind wrote: The other thing that I have been meaning to say in this thread is that I don't like using COMMIT to mean subtransaction commit (vs. introducing a new command for it) because of the following situation. Lets say that I have a java method that takes a jdbc connection and this code starts a transaction and does some work then issues a commit to ensure the changes have been committed to the database, and then does some other work outside the database based on the fact that the commit was sucessfull and it therefore knows the data is saved to disk (i.e. send out an email notification, or any number of other non-database tasks). Now lets suppose that someone calls this method with a database connection that already has a transaction started, so that this method really is beginning and working with a sub-transaction. Now when it commits it doesn't know if the changes will ever get to disk since its commit could be rolled back later. So this code gets broken. Note that there is no standard way in JDBC to enter a subtransaction unless you issue the BEGIN explicitly or invoke the savepoint API. There are lots of ways to confuse the driver's transaction handling already if you issue arbitary transaction control instructions (althugh I'm working on making the driver recover better -- the v3 transaction status indicator in ReadyForQuery helps). And, well, how do you expect the code to ever not be broken? If commit() really does commit the whole transaction, the caller code that expects to still be in a transaction is going to be unhappy (if it doesn't expect to still be in a transaction, why is it opening a subtransaction at all?). The callee should be using JTA's registerSynchronization() to get callbacks after transaction commit, or using 2PC (yes I know it's not supported yet), depending on the guarantees it needs. Earlier Alvaro was looking at ways to provide the transaction nesting level via the client protocol; I suggested doing it as a parameter (so you get ParameterStatus on nesting change) but I'm not sure what happened with it after that. Assuming something does get done here, the driver can track where it is in subtransactions quite easily, and so if you want Connection.commit() to really mean "commit this transaction and all subtransactions" even in the face of the user messing around with BEGIN themselves, we can do that even if multiple COMMITs are needed -- we just look at the current nesting level to work out how many to issue. This behaviour (of commit()/rollback()) actually makes sense as things like connection pools will expect Connection.commit() or Connection.rollback to produce a reasonably "vanilla" connection state, and transaction monitors are likely to want those methods to affect the entire transaction too. I like the functionality of nested transactions, I just think that there needs to be different commands other than BEGIN/COMMIT to work with them. So that there is no possiblity for misunderstanding what COMMIT really means. "BEGIN NESTED WORK" / "COMMIT NESTED WORK" / "END NESTED WORK" or something? And make plain BEGIN inside a transaction a warning (as it currently is) and plain COMMIT/END inside a subtransaction an error? (or should they affect all subtransactions?) I can see this having some value for dealing with existing applications that issue redundant BEGIN/COMMIT/ROLLBACK statements (and get warnings, but ignore them). -O ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Tablespace patch review
Tom Lane wrote:
> Gavin Sherry <[EMAIL PROTECTED]> writes:
> > On Fri, 18 Jun 2004, Bruce Momjian wrote:
> >> * Allow reporting of which objects are in which tablespaces
This would have to be an external tool that connects to each database.
>
> > Do we need an information_schema.tablespaces view as well as an update to
> > information_schema.{tables|indexes|...} ?
>
> That would depend on your theology about whether implementation-specific
> additions to information_schema are a good idea or not. I'd lean
> against doing this myself. ISTM the entire point of information_schema
> is to be standard, and additions are, well, not. Worse, they might
> conflict with future extensions to the standard.
>
> I don't actually see a way *inside the database* to implement Bruce's
> TODO item; there's no way for a backend to look at the catalogs of other
> databases. We could look to see which databases had nonempty
> subdirectories of a tablespace, and report those databases by name, but
> explaining exactly what's inside those subdirectories is a lot harder.
The best we could do here is to just report the database name and the
relfilenode.
>
> It might be better to think about supporting this need with oid2name
> or some similar client-level tool. It's easy to see how oid2name
> might be extended to produce an output like
>
> Tablespace t1
> Database d1
> Table x
> Table y
> Database d2
> etc etc
Yep, that's what I was thinking --- an external tool.
--
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
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
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
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
Tom Lane wrote: > 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. Well, I figure we would copy the database capability we have for tablespaces. If you call oid2name with no args, you get: All databases: - 17219 = test 1 = template1 17218 = template0 If we specify just the database name we get: (2) aspg oid2name -d template1 All tables from database "template1": - 17147 = sql_features 17152 = sql_implementation_info 17157 = sql_languages 17162 = sql_packages 17167 = sql_sizing 17172 = sql_sizing_profiles 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. > 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... For dbsize, I assume we have to follow the symlinks. We would have to spin through all the tablespaces looking for directories with the database oid. Given the number of open items for 7.5, I am thinking of keeping this for post-feature freeze. Both are contrib. -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Tablespace patch review
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
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] stderr & win32 admin check
Magnus, where are we on this refactoring process. --- Magnus Hagander wrote: > >> * Created function write_stderr(const char *fmt, ...), used > >before elog > >> can be used. This function will write to stderr on unix and on win32 > >> fconsole. It will write to the eventlog on win32 when running as a > >> service. > >> * Changed all (most? I think I got all) fprintf(stderr,...) > >to use this > >> function instead. That way, we gain the ability to put all the other > >> preivously-stderr-messages to the eventlog as well. > > > >I'm not sure this is a good idea. The remaining uses of stderr were > >that way for a reason, not because someone had forgot to change them > >into elog calls. It would be a lot less invasive to just move the > >privilege check as you originally intended. > > > I figured as long as nothing "dangerous" (e.g. using memory allocations > etc) is done in the function, it should be just as safe as fprintf. On > Unix, it does nothing more than a simple fprintf anyway (one call > deeper). The only difference in practice is that we can put them in the > eventlog on win32 (again, only using calls that are safe in this > context). If we do it the other way, we are going to lose these other > messages when running as a service on win32 (since we specifically are > not using ereport(), per what you say above). > > Also, this would remove the check so you could do initdb and other > operations that are blocked today (that don't go through postmaster.c) > when being root, I assumed that was not good either... > > //Magnus > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > -- 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] [HACKERS] serverlog function (log_destination file)
Were are we on this?
---
Andreas Pflug wrote:
> Tom Lane wrote:
>
> >
> >This has got portability issues (fopen("ab"))
> >
> My doc says b is ignored on ansi systems, and recommends using it. Do
> you have other experiences?
>
> > and I don't care for its
> >use of malloc in preference to palloc either.
> >
> Do we already have an applicable memory context in the postmaster at
> that early stage of initialization?
>
> >Also, pg_logfile() will dump core if LogFileName returns null.
> >
> >
> How that?
>
> char *filename=LogFileName();
> if (filename)
> {
>...
> free(filename);
> }
>
> >The bigger issue though is whether this is useful at all, if you cannot
> >solve the file rotation issue (and I don't think you can). As
> >implemented, the secondary log file cannot be truncated without
> >restarting the postmaster. I think that reduces it from a possibly
> >useful feature to a useless toy.
> >
> This patch isn't trying to be better on logfile handling than the
> default stderr redirection behavior, besides being able to access it
> through the postmaster. Seems you insist to name this a toy, many users
> don't.
>
> > (The fact that pg_logfile_length
> >returns int and not something wider is pretty silly in this connection.)
> >
> >
> 2GB logfile seems pretty big...
>
> Regards,
> Andreas
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faqs/FAQ.html
>
--
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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Tablespace patch review
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] win32 libpq ENABLE_THREAD_SAFETY
Patch applied. Thanks. I updated the comments at the top of win32.mak
too to document this new option.
I also made some changes so our Win32 native port can use threads too.
I need to modify the configure tests but for now the attached applied
patch will set up the groundwork for it. Mingw does support threads,
right?
---
Andreas Pflug wrote:
> So here's the updated ENABLE_THREAD_SAFETY patch for win32, to be
> compiled under VC5/6/7 (tested with VC6).
>
> nmake -f win32.mak [DEBUG=1] [USE_SSL=1] [ENABLE_THREAD_SAFETY=1]
>
> are supported.
>
> Regards,
> Andreas
>
> /*-
> *
> * pthread-win32.c
> *partial pthread implementation for win32
> *
> * Copyright (c) 2004, PostgreSQL Global Development Group
> * IDENTIFICATION
> * $PostgreSQL: $
> *
> *-
> */
>
>
> #include "windows.h"
> #include "pthread.h"
>
> HANDLE pthread_self()
> {
>return GetCurrentThread();
> }
>
> void pthread_setspecific(pthread_key_t key, void *val)
> {
> }
>
> void *pthread_getspecific(pthread_key_t key)
> {
> return NULL;
> }
>
> void pthread_mutex_init(pthread_mutex_t *mp, void *attr)
> {
>*mp = CreateMutex(0, 0, 0);
> }
>
> void pthread_mutex_lock(pthread_mutex_t *mp)
> {
>WaitForSingleObject(*mp, INFINITE);
> }
>
> void pthread_mutex_unlock(pthread_mutex_t *mp)
> {
>ReleaseMutex(*mp);
> }
> #ifndef __PTHREAD_H
> #define __PTHREAD_H
>
> typedef ULONG pthread_key_t;
> typedef HANDLE pthread_mutex_t;
> typedef int pthread_once_t;
>
> HANDLE pthread_self();
>
> void pthread_setspecific(pthread_key_t, void*);
> void* pthread_getspecific(pthread_key_t);
>
> void pthread_mutex_init(pthread_mutex_t *, void *attr);
> void pthread_mutex_lock(pthread_mutex_t*); // blocking
> void pthread_mutex_unlock(pthread_mutex_t*);
>
> #endif
> Index: interfaces/libpq/fe-connect.c
> ===
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/libpq/fe-connect.c,v
> retrieving revision 1.274
> diff -u -r1.274 fe-connect.c
> --- interfaces/libpq/fe-connect.c 10 Jun 2004 22:26:24 - 1.274
> +++ interfaces/libpq/fe-connect.c 13 Jun 2004 19:13:58 -
> @@ -882,11 +882,13 @@
> const char *node = NULL;
> int ret;
> #ifdef ENABLE_THREAD_SAFETY
> +#ifndef WIN32
> static pthread_once_t check_sigpipe_once = PTHREAD_ONCE_INIT;
>
> /* Check only on first connection request */
> pthread_once(&check_sigpipe_once, check_sigpipe_handler);
> #endif
> +#endif
>
> if (!conn)
> return 0;
> @@ -3183,11 +3185,19 @@
> }
>
> static pgthreadlock_t default_threadlock;
> +
> static void
> default_threadlock(int acquire)
> {
> #ifdef ENABLE_THREAD_SAFETY
> +#ifndef WIN32
> static pthread_mutex_t singlethread_lock = PTHREAD_MUTEX_INITIALIZER;
> +#else
> + static pthread_mutex_t singlethread_lock;
> +static long mutex_initialized = 0;
> +if (!InterlockedExchange(&mutex_initialized, 1L))
> +pthread_mutex_init(&singlethread_lock, NULL);
> +#endif
> if (acquire)
> pthread_mutex_lock(&singlethread_lock);
> else
> Index: interfaces/libpq/fe-secure.c
> ===
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/libpq/fe-secure.c,v
> retrieving revision 1.41
> diff -u -r1.41 fe-secure.c
> --- interfaces/libpq/fe-secure.c 3 Jun 2004 00:13:19 - 1.41
> +++ interfaces/libpq/fe-secure.c 13 Jun 2004 19:14:00 -
> @@ -864,8 +864,14 @@
> init_ssl_system(PGconn *conn)
> {
> #ifdef ENABLE_THREAD_SAFETY
> -static pthread_mutex_t init_mutex = PTHREAD_MUTEX_INITIALIZER;
> -
> +#ifndef WIN32
> +static pthread_mutex_t init_mutex = PTHREAD_MUTEX_INITIALIZER;
> +#else
> +static pthread_mutex_t init_mutex;
> +static long mutex_initialized = 0L;
> +if (!InterlockedExchange(&mutex_initialized, 1L))
> +pthread_mutex_init(&init_mutex, NULL);
> +#endif
> pthread_mutex_lock(&init_mutex);
>
> if (pq_initssllib && pq_lockarray == NULL) {
> @@ -1171,6 +1177,7 @@
>
>
> #ifdef ENABLE_THREAD_SAFETY
> +#ifndef WIN32
> /*
> * Check SIGPIPE handler and perhaps install our own.
> */
> @@ -1210,6 +1217,7 @@
> if (!PQinSend())
> exit(128 + SIGPIPE);/* typical return value for SIG_DFL */
> }
> +#endif
> #endif
>
> /*
> Index: interfaces/libpq/win32.mak
> ===
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/libpq/win32.mak,v
> retrieving revision 1.24
> diff -u -r1.24 win32.mak
> --- interfaces/libpq/win32.mak4 Jun 2004 13:30:04 - 1.24
> +++ inter
Re: [PATCHES] Tablespace patch review
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
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] eventlog fix
OK, I have created the following diff to document the pgevent installation instructions. However, you didn't send me a Makefile for creating the DLL, and I am not sure how to do that. Would you send me a `Makefile for src/bin/pgevent? Thanks. --- Laurent Ballester wrote: > Hello, > > > > > We don't use this hungarian notation anywhere ... it certainly looks > > ugly. > > > > Sorry, I modify pgevent.c again and suppress all hungarian variables naming. > > I add a diff -c for src/bin/Makefile, necessary to compile pgevent for > windows platform. > > > regards > > Laurent Ballester [ Attachment, skipping... ] [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings -- 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 Index: doc/src/sgml/installation.sgml === RCS file: /cvsroot/pgsql-server/doc/src/sgml/installation.sgml,v retrieving revision 1.202 diff -c -c -r1.202 installation.sgml *** doc/src/sgml/installation.sgml 23 May 2004 15:13:43 - 1.202 --- doc/src/sgml/installation.sgml 19 Jun 2004 05:12:09 - *** *** 1073,1078 --- 1073,1091 +Registering eventlog on Windows: + + To register a Windows eventlog + library with the operating system, issue this command after installation: + + regsvr32 pgsql_library_directory/pgevent.dll + + This creates registry entries used by the event viewer. + + + + Uninstallation: To undo the installation use the command gmake Index: src/bin/Makefile === RCS file: /cvsroot/pgsql-server/src/bin/Makefile,v retrieving revision 1.44 diff -c -c -r1.44 Makefile *** src/bin/Makefile18 Jun 2004 21:24:05 - 1.44 --- src/bin/Makefile19 Jun 2004 05:12:11 - *** *** 15,20 --- 15,23 DIRS := initdb ipcclean pg_ctl pg_dump \ psql scripts pg_config pg_controldata pg_resetxlog + ifeq ($(PORTNAME), win32) + DIRS+=pgevent + endif all install installdirs uninstall depend distprep: @for dir in $(DIRS); do $(MAKE) -C $$dir $@ || exit; done ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] Tablespace patch review
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
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
