Re: [HACKERS] Indices pinned in pageable RAM?
Marty Scholes <[EMAIL PROTECTED]> writes: > Has anyone seriously looked at how it would impact things to give the > DBA the option of storing certain indices in RAM instead of on disk? I'd classify it as an utter waste of time. If the index is being hit heavily, it will stay cached in RAM anyway. If it isn't, locking it in RAM would be a pessimization not an optimization, because the space would be better used for something else. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [pgsql-hackers-win32] [HACKERS] select like...not using index
"Merlin Moncure" <[EMAIL PROTECTED]> writes: > Here is what I think happened (this might be a bug, might not): Each > night I run initdb but I use a special postgresql.conf which is > optimized for quick data loading. This is copied over the default one > after the server is started. This contains the locale information which > is 'initialized by initdb'. These were still 'C' because this file was > generated before the default locale was changed. psql shows this > information when you ask it for the locale info even if it is > incorrect. I don't believe this for a minute. lc_ctype and lc_collate can *not* be set from postgresql.conf. Try it. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pgsql-server: Adjust our timezone library to use pg_time_t (typedef'd
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > Are the zic files something that should be updated for every minor > release, or only for every major release? AFAIK they don't change very often. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pgsql-server: Adjust our timezone library to use pg_time_t (typedef'd
I did not modify the format of the zic timezone database files, which means that for the moment the system will not know about daylight-savings periods outside the range 1901-2038. Given the way the files are set up, it's not a simple decision like 'widen to 64 bits'; we have to actually think about the range of years that need to be supported. We should probably inquire what the plans of the upstream zic people are before making any decisions of our own. Are the zic files something that should be updated for every minor release, or only for every major release? Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Default Locale in initdb
Christopher Kings-Lynne wrote: This has bitten me a couple times. In what version did it change? My feeling, and I'd like to see what everyone else thinks, is that if you do not specify a locale, you get "C." I think that initdb should default to something, and do the following: * Have an explicit warnign if no locale specified, and what it is defaulting to * Same for encoding. NO-ONE knows about the -E option when they first use postgres. Trust me on this. * Same for -W. NO-ONE knows this exists. Then they change their trusts to md5 and they can't login to their postgres account anymore. Of these, encoding can be overridden when you create a db, and the password issue can be recovered from very quickly. Only the lc-ctype and lc-collate settings are written in stone by initdb. So I think we can split up the cases. ISTM there's a good case for defaulting at least lc-collate and lc-ctype to "C" rather than whatever the environment says (the other locale settings can be reset in the config file anyway). cheers andrew ---(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: [HACKERS] Default Locale in initdb
This has bitten me a couple times. In what version did it change? My feeling, and I'd like to see what everyone else thinks, is that if you do not specify a locale, you get "C." I think that initdb should default to something, and do the following: * Have an explicit warnign if no locale specified, and what it is defaulting to * Same for encoding. NO-ONE knows about the -E option when they first use postgres. Trust me on this. * Same for -W. NO-ONE knows this exists. Then they change their trusts to md5 and they can't login to their postgres account anymore. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Default Locale in initdb
> Christopher Kings-Lynne wrote: >> > When this new behavior was introduced, and I migrated our databases to >> > the new PgSQL version (dump/restore), the locale of all my databases >> > were silently changed from C to US_en. This broke one application in a >> > very subtle way because of slightly different sort behavior in the >> > different locale. Tracking it down was quite tricky. >> > >> > PgSQL was just a little too helpful in this case. >> >> Seems pretty nasty thing to do. I would so vote for making -E and -W >> and --locate required flags to initdb. Oh the amount of time I've spent >> with people in IRC.. > > What about folks who don't use locales? This has bitten me a couple times. In what version did it change? My feeling, and I'd like to see what everyone else thinks, is that if you do not specify a locale, you get "C." That way things work as you'd expect in most cases. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Default Locale in initdb
Christopher Kings-Lynne wrote: > > When this new behavior was introduced, and I migrated our databases to > > the new PgSQL version (dump/restore), the locale of all my databases > > were silently changed from C to US_en. This broke one application in a > > very subtle way because of slightly different sort behavior in the > > different locale. Tracking it down was quite tricky. > > > > PgSQL was just a little too helpful in this case. > > Seems pretty nasty thing to do. I would so vote for making -E and -W > and --locate required flags to initdb. Oh the amount of time I've spent > with people in IRC.. What about folks who don't use locales? -- 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: [HACKERS] Default Locale in initdb
When this new behavior was introduced, and I migrated our databases to the new PgSQL version (dump/restore), the locale of all my databases were silently changed from C to US_en. This broke one application in a very subtle way because of slightly different sort behavior in the different locale. Tracking it down was quite tricky. PgSQL was just a little too helpful in this case. Seems pretty nasty thing to do. I would so vote for making -E and -W and --locate required flags to initdb. Oh the amount of time I've spent with people in IRC.. Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Transaction aborts on syntax error.
Hello,
I have a much clearer picture of the issue. So, does this mean that
with nested transactions, all statements will execute within a
mini-transaction, which may be executed within a branch of user
defined sub-transactions. Such that:
begin
...
...
begin
...
...
mini-transaction {syntax error}
...
commit
...
...
commit
-ESR-
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] process num.
On Wednesday 02 June 2004 02:01 pm, ivan wrote: (B> (B> where can i set min/max number of process which are waiting for (B> connections from clients ? (B> (B (BFirst off, you are on the wrong list. This is more appropriate for the admin (Bor preformance list. (B (BPostgreSQL forks a process for each connection. No processes are pre-forked. (BIt doesn't work like Apache. If you want a connection pool, there are (Bvarious applications out there, like pgpool. (B (B-- (BJonathan Gardner (B[EMAIL PROTECTED] (B (B---(end of broadcast)--- (BTIP 2: you can get off all lists at once with the unregister command (B(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Extended customizing, SQL functions,
> "pgsql" == pgsql <[EMAIL PROTECTED]> writes: pgsql> The have a database of information that is coming in at a pgsql> high speed regular basis. One bit of information is a pgsql> value. To get this value they must perform SELECT pgsql> sum(field) FROM table. Well, this simply does not pgsql> scale. They've used a trigger system with a small summary pgsql> table where they update, the number in the sumary pgsql> field. That works fine, except, that after a few thousand pgsql> updates, the SELECT time takes a while. Then they have to pgsql> vacuum constanty. It just seems like an ugly and wastefull pgsql> process. Sounds like something that TelegraphCQ can do well .. http://telegraph.cs.berkeley.edu -- Pip-pip Sailesh http://www.cs.berkeley.edu/~sailesh ---(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
[HACKERS] process num.
hi where can i set min/max number of process which are waiting for connections from clients ? bye ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] constraint upon view
On Monday 31 May 2004 06:38 am, Pierre Emmanuel Gros wrote: (B> Hi, (B> I would like to know if i can add constraint and typed column upon a (B> create view sentence. (B> something like create view toto (a INTEGER primary key , b VARCHAR) as (B> select . (B> If it is not possible , what to have to change in the backend sources to (B> obtain the result ??? (B> (B (BI don't think you want a view in this case. When you select against a view, (Bthe view is unrolled into the statement that forms it. Try a look at the (Bresults of "EXPLAIN ANALYZE" when you are selecting against a view. (B (B-- (BJonathan Gardner (B[EMAIL PROTECTED] (B (B---(end of broadcast)--- (BTIP 9: the planner will ignore your desire to choose an index scan if your (B joining column's datatypes do not match
[HACKERS] Indices pinned in pageable RAM?
Has anyone seriously looked at how it would impact things to give the DBA the option of storing certain indices in RAM instead of on disk? Queries (both select and insert/update) against heavily indexed tables do most of the reads and writes to the index trees and relatively little reading and writing to the actual table data. The index is completely redundant (read: rebuildable during restart) and its rebuild "should" be very parallelizable. Without the WAL and disk synchronization munching, even a single-threaded rebuild during restart should be fairly quick. This would allow the index to stay in RAM. giving it a higher priority than all other disk objects in the OS cache. Putting it in pageable RAM would allow the OS to reclaim core if it absolutely had to. Am I way off here? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] constraint upon view
Hi, I would like to know if i can add constraint and typed column upon a create view sentence. something like create view toto (a INTEGER primary key , b VARCHAR) as select . If it is not possible , what to have to change in the backend sources to obtain the result ??? Thank you Pierre ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Converting postgresql.conf parameters to kilobytes
In article <[EMAIL PROTECTED]>, Tom Lane <[EMAIL PROTECTED]> writes: > Shridhar Daithankar <[EMAIL PROTECTED]> writes: >> I was toying around with idea of converting all the memory related >> parameters in postgresql.conf to kilobytes for simplicity and >> uniformity. > Why is that a good idea? Two reasons: 1. Some values are in KB, some in 8 KB 2. I find it easier to calculate in KB I'd like to see the following: * If the value is purely numeric, treat it as before (to ensure compatibility with older versions) * If the value is numeric with a prefix of [KMG], interpret it as KB, MB, or GB, respectively ---(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: [HACKERS] Default Locale in initdb
Paul Ramsey wrote: Just because it is not new does not mean that it is good. Sure. I've been caught by it too. Once. :-) When this new behavior was introduced, and I migrated our databases to the new PgSQL version (dump/restore), the locale of all my databases were silently changed from C to US_en. This broke one application in a very subtle way because of slightly different sort behavior in the different locale. Tracking it down was quite tricky. PgSQL was just a little too helpful in this case. It doesn't happen silently - initdb tells you what it is doing. Ignoring the current environment and using a default value of "C" would be a very simple change to make, if that's what people want. cheers andrew Andrew Dunstan wrote: [EMAIL PROTECTED] wrote: Is it me or has the default locale of created databases change at some point? Currently, on Linux, if one does not specify a locale, the locale is taken from the system environment and it is not "C." While I can both sides of a discussion, I think that choosing a "locale" without one being specified is a bad idea, even if it is the locale of the machine. The reason why it is a bad idea is that certain features of the database which only work correctly with a locale of "C" will not work by default. This is not new behaviour. (Why are you the only person who posts here who is nameless?) cheers andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Default Locale in initdb
On Wed, 2 Jun 2004 [EMAIL PROTECTED] wrote: > Is it me or has the default locale of created databases change at some point? > > Currently, on Linux, if one does not specify a locale, the locale is taken > from the system environment and it is not "C." > > While I can both sides of a discussion, I think that choosing a "locale" > without one being specified is a bad idea, even if it is the locale of the > machine. The reason why it is a bad idea is that certain features of the > database which only work correctly with a locale of "C" will not work by > default. The same is true with not taking the locale. Other unix applications will sort "correctly" without additional work, but PostgreSQL will not. The LIKE optimization can be "fixed" in recent versions by adding an index and leaving the locale, but getting correct sorting is going to require a reinitdb. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Default Locale in initdb
Just because it is not new does not mean that it is good. When this new behavior was introduced, and I migrated our databases to the new PgSQL version (dump/restore), the locale of all my databases were silently changed from C to US_en. This broke one application in a very subtle way because of slightly different sort behavior in the different locale. Tracking it down was quite tricky. PgSQL was just a little too helpful in this case. Andrew Dunstan wrote: [EMAIL PROTECTED] wrote: Is it me or has the default locale of created databases change at some point? Currently, on Linux, if one does not specify a locale, the locale is taken from the system environment and it is not "C." While I can both sides of a discussion, I think that choosing a "locale" without one being specified is a bad idea, even if it is the locale of the machine. The reason why it is a bad idea is that certain features of the database which only work correctly with a locale of "C" will not work by default. This is not new behaviour. (Why are you the only person who posts here who is nameless?) cheers andrew -- __ / | Paul Ramsey | Refractions Research | Email: [EMAIL PROTECTED] | Phone: (250) 885-0632 \_ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Default Locale in initdb
[EMAIL PROTECTED] wrote: Is it me or has the default locale of created databases change at some point? Currently, on Linux, if one does not specify a locale, the locale is taken from the system environment and it is not "C." While I can both sides of a discussion, I think that choosing a "locale" without one being specified is a bad idea, even if it is the locale of the machine. The reason why it is a bad idea is that certain features of the database which only work correctly with a locale of "C" will not work by default. This is not new behaviour. (Why are you the only person who posts here who is nameless?) cheers andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested transactions and tuple header info
Alvaro Herrera wrote: > On Wed, Jun 02, 2004 at 12:23:37PM -0400, Bruce Momjian wrote: > > Tom Lane wrote: > > > > If we go with a global CID counter then we don't have to add that step. > > > > Seems Alvaro is already using a global counter. > > I think I stated already that I'm in fact using it. Not sure why it > didn't show up in the list. Yes, I saw it. I wrote "seems" when I should have wrote it as a fact. -- 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
[HACKERS] Default Locale in initdb
Is it me or has the default locale of created databases change at some point? Currently, on Linux, if one does not specify a locale, the locale is taken from the system environment and it is not "C." While I can both sides of a discussion, I think that choosing a "locale" without one being specified is a bad idea, even if it is the locale of the machine. The reason why it is a bad idea is that certain features of the database which only work correctly with a locale of "C" will not work by default. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Nested transactions and tuple header info
On Wed, Jun 02, 2004 at 12:23:37PM -0400, Bruce Momjian wrote: > Tom Lane wrote: > > If we go with a global CID counter then we don't have to add that step. > > Seems Alvaro is already using a global counter. I think I stated already that I'm in fact using it. Not sure why it didn't show up in the list. -- Alvaro Herrera () "¿Que diferencia tiene para los muertos, los huérfanos, y aquellos que han perdido su hogar, si la loca destrucción ha sido realizada bajo el nombre del totalitarismo o del santo nombre de la libertad y la democracia?" (Gandhi) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested transactions and tuple header info
On Wed, Jun 02, 2004 at 11:12:31AM -0400, Tom Lane wrote: > A global CID counter would also simplify other visibility tests. Alvaro > hasn't said anything about how he's doing visibility checks across > different subxacts of the same main xact, but without global CID there > would need to be some pretty ugly checks to determine whether a subxact > happened before or after the CID cutoff your outer xact is interested > in. Yes, I'm using a global CID counter. -- Alvaro Herrera () "La vida es para el que se aventura" ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested transactions and tuple header info
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > I don't understand why a single counter is needed for phantom xids. We > > keep the cmin/cmax on the tuple already, and our own backend can look up > > the xmin/xmax that goes with the phantom. > > Oh, so you're thinking of an internal table that provides a mapping back > to the replaced xmin? Ugh. Perhaps it could be made to work, but it's > a lot of mechanism, and it will slow down visibility checks (since > AFAICS you'd have to check every subxid against the phantoms table). My idea was to have a tuple bit indicating the xid is a phantom. > If we go with a global CID counter then we don't have to add that step. Seems Alvaro is already using a global counter. > A global CID counter would also simplify other visibility tests. Alvaro > hasn't said anything about how he's doing visibility checks across > different subxacts of the same main xact, but without global CID there > would need to be some pretty ugly checks to determine whether a subxact > happened before or after the CID cutoff your outer xact is interested > in. If a global counter will reduce the number of phantom checks, then good. However, I assume Alvaro has to access the creation/expire xid to determine if the subtransaction committed, so I didn't think a global counter would help reduce the number of 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 8: explain analyze is your friend
Re: [HACKERS] ACLs versus ALTER OWNER
On Wed, Jun 02, 2004 at 10:54:36PM +0800, Christopher Kings-Lynne wrote: > >Well, the spec doesn't have create permissions per se, but they do have > >a "usage" right on domains, and they specify that revoking that results > >in dropping objects: > > > > 7) For every abandoned domain descriptor DO, let S1.DN be the > > of DO. The following is > >effectively executed without further Access Rule checking: > > > > DROP DOMAIN S1.DN CASCADE > > Hmmm. Seems pretty harsh. But barring us implementing that (I don't > see it happening for 7.5), just had an idea :) > > How about pg_dumpall dumps all users as superusers, and then changes > them back to what they're supposed to be at the bottom of the script :) Huh, how about a GUC var, say "creating_user", which would make objects created by the superuser as created by whoever is mentioned there? The dump connects only as superuser and changes creating_user as needed. Not a pretty idea, but weren't you looking for kludges? :-) -- Alvaro Herrera () "La persona que no quería pecar / estaba obligada a sentarse en duras y empinadas sillas/ desprovistas, por cierto de blandos atenuantes" (Patricio Vogel) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested transactions and tuple header info
Bruce Momjian <[EMAIL PROTECTED]> writes: > I don't understand why a single counter is needed for phantom xids. We > keep the cmin/cmax on the tuple already, and our own backend can look up > the xmin/xmax that goes with the phantom. Oh, so you're thinking of an internal table that provides a mapping back to the replaced xmin? Ugh. Perhaps it could be made to work, but it's a lot of mechanism, and it will slow down visibility checks (since AFAICS you'd have to check every subxid against the phantoms table). If we go with a global CID counter then we don't have to add that step. A global CID counter would also simplify other visibility tests. Alvaro hasn't said anything about how he's doing visibility checks across different subxacts of the same main xact, but without global CID there would need to be some pretty ugly checks to determine whether a subxact happened before or after the CID cutoff your outer xact is interested in. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] ACLs versus ALTER OWNER
How about pg_dumpall dumps all users as superusers, and then changes them back to what they're supposed to be at the bottom of the script :) Leaves you in kind of a dangerous state if the script doesn't complete, doesn't it? If your script doesn't complete, it can leave you in all sorts of bad states, but I guess this is a reasonably bad one. Someone else suggested having pg_dump dump all objects without ownership (so, on restore, they'd all initially be owned by the user running the script, hopefully a superuser) and then doing ALTER OWNERs and GRANTs at the bottom. This seems a little cleaner to me, though it's got the problem that somebody would have to go off and implement the remaining ALTER OWNER commands. I guess that's me... I'll have a crack at it, but don't let that stop anyone from piping up and helping me :) Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Converting postgresql.conf parameters to kilobytes
On Wed, 02 Jun 2004 11:05:43 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: > Shridhar Daithankar <[EMAIL PROTECTED]> writes: > >> I remain unalterably opposed to the notion of measuring > >shared_buffers> in KB, but if you think you can get such a thing in > >over my objections, > > > Are you OK with MBs? I am fine with anything. > > No, I'm not. shared_buffers should be measured in buffers (ie, > pages). Anything else is obscurantism. Not to mention highly likely > to confuse people who are used to how it's been set in the past. This may be an unreasonable suggestion, but how about allowing both? I've seen several configuration systems do the following: shared_buffers = 1 ( shared_buffers in pages ) shared_buffers = 100M ( 100 MBs of shared_buffers ) shared_buffers = 2048K ( 2MBs of shared_buffers ) Using something like this would leave the old functionality in tact, allow users to use what they like, and shouldn't introduce that much complexity into the code. - Frank Wiles <[EMAIL PROTECTED]> http://frank.wiles.org - ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] ACLs versus ALTER OWNER
Someone else suggested having pg_dump dump all objects without ownership (so, on restore, they'd all initially be owned by the user running the script, hopefully a superuser) and then doing ALTER OWNERs and GRANTs at the bottom. Actually, this would probably only be reasonable if you fixed the ACLs after an ALTER OWNER, like you proposed earlier. Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Converting postgresql.conf parameters to kilobytes
Frank Wiles <[EMAIL PROTECTED]> writes: > This may be an unreasonable suggestion, but how about allowing both? > I've seen several configuration systems do the following: > shared_buffers = 1 ( shared_buffers in pages ) > shared_buffers = 100M ( 100 MBs of shared_buffers ) > shared_buffers = 2048K ( 2MBs of shared_buffers ) I could live with that. I'm not sure how painful it would be to wedge into GUC though; and I have a feeling that it is exactly *not* what Shridhar would think is simpler than the present behavior ;-) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ACLs versus ALTER OWNER
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: >> Someone else suggested having pg_dump dump all objects without ownership >> (so, on restore, they'd all initially be owned by the user running the >> script, hopefully a superuser) and then doing ALTER OWNERs and GRANTs at >> the bottom. > Actually, this would probably only be reasonable if you fixed the ACLs > after an ALTER OWNER, like you proposed earlier. I was envisioning pg_dump not issuing any GRANTs until after the ALTER OWNER steps, so it really wouldn't matter whether ALTER OWNER did anything to the ACL list; it'd still be NULL at that point anyway. (I do, however, have every intention of fixing ALTER OWNER that way before 7.5 freeze.) BTW, is pg_dump careful about the order in which it issues GRANTs? Specifically, what about being sure that chains of GRANT OPTIONs are re-granted in a legal sequence? I don't recall any smarts in the code about that... 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: [HACKERS] Converting postgresql.conf parameters to kilobytes
On Wednesday 02 June 2004 20:59, Tom Lane wrote: > Frank Wiles <[EMAIL PROTECTED]> writes: > > This may be an unreasonable suggestion, but how about allowing both? > > I've seen several configuration systems do the following: > > > > shared_buffers = 1 ( shared_buffers in pages ) > > shared_buffers = 100M ( 100 MBs of shared_buffers ) > > shared_buffers = 2048K ( 2MBs of shared_buffers ) > > I could live with that. I'm not sure how painful it would be to wedge > into GUC though; and I have a feeling that it is exactly *not* what > Shridhar would think is simpler than the present behavior ;-) Usability POV, I could live with that. That would mean converting the GUC from int to string though. Shridhar ---(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: [HACKERS] Converting postgresql.conf parameters to kilobytes
On Wednesday 02 June 2004 20:16, Tom Lane wrote: > Shridhar Daithankar <[EMAIL PROTECTED]> writes: > > Any updates/opinions? Should we convert assign hooks to perform actual > > assignment and custom validation instead of just custom validation? It is > > clear from README that it is for validation purposes only.. > > As it should be. Assign hooks have no business altering the > user-supplied value. OK > I remain unalterably opposed to the notion of measuring shared_buffers > in KB, but if you think you can get such a thing in over my objections, Are you OK with MBs? I am fine with anything. > the way to do it is to decouple the GUC parameter from NBuffers. The > GUC setting is whatever it is; you can reject the value if it's too > far out of range, but you do not editorialize upon it. What you do is > compute the derived value for NBuffers and assign that in the assign > hook. That means removing NBuffers from declaration for config structure and substituting a dummy variable for it? If you think this is good and acceptable enough, I will proceed making changes that way. Shall I take this as guideline for other parameters as well? Shridhar ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Nested transactions and tuple header info
Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > On Wed, Jun 02, 2004 at 09:52:28AM -0400, Tom Lane wrote: > >> AFAICS your proposal does not support this. The two cursors' snapshots > >> will differ only in the recorded current-cid for the outer transaction. > >> If the subtrans has overwritten xmin/cmin, there is no way to make that > >> decision correctly. > > > Why would it overwrite cmin? Only a new xmin is needed (and cmax and > > xmax, but the cursors don't care about those) > > If you overwrite xmin and not cmin, you've created a nonsensical > situation. How do you distinguish this tuple from tuples created by the > subxact itself? More generally, cmin is only meaningful in combination > with a particular transaction ID; you can't just arbitrarily replace > xmin without changing cmin. > > I've been trying to think of ways to solve these problems by having a > main xact and all its subxacts share a common CID sequence (ie, a > subxact would have its own xid but would not start CID over at one). > If you assume that, then Bruce's idea may indeed work, since you would > never replace xmin in a way that would shift the interpretation of cmin > into a different CID sequence. But I suspect there is a simpler way to > solve it given that constraint. I thought about using a global command counter. The problem there is that there is no way to control the visibility of tuples by other transactions on commit except going back end fixing up tuples, which is unacceptable. By creating phantoms, we can decide if an specific xmin/xmax pair should be appear as committed and set it accordingly on commit. -- 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: [HACKERS] Converting postgresql.conf parameters to kilobytes
Bruce Momjian <[EMAIL PROTECTED]> writes: > Also, I it seems postgres --describe-config isn't working. It outputs > nothing here. Yeah, same here. I'll take a look --- I may have side-swiped that during recent hacking in main.c. 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: [HACKERS] Nested transactions and tuple header info
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> I've been trying to think of ways to solve these problems by having a > >> main xact and all its subxacts share a common CID sequence (ie, a > >> subxact would have its own xid but would not start CID over at one). > >> If you assume that, then Bruce's idea may indeed work, since you would > >> never replace xmin in a way that would shift the interpretation of cmin > >> into a different CID sequence. But I suspect there is a simpler way to > >> solve it given that constraint. > > > I thought about using a global command counter. The problem there is > > that there is no way to control the visibility of tuples by other > > transactions on commit except going back end fixing up tuples, which is > > unacceptable. > > No, I said own xid --- so the "phantom xid" part is still there. But > your idea definitely does *not* work unless you use a single CID > sequence for the whole main xact; and I'm still wondering if there's > not a simpler implementation possible given that assumption. I don't understand why a single counter is needed for phantom xids. We keep the cmin/cmax on the tuple already, and our own backend can look up the xmin/xmax that goes with the phantom. -- 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: [HACKERS] ACLs versus ALTER OWNER
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > How about pg_dumpall dumps all users as superusers, and then changes > them back to what they're supposed to be at the bottom of the script :) Leaves you in kind of a dangerous state if the script doesn't complete, doesn't it? Someone else suggested having pg_dump dump all objects without ownership (so, on restore, they'd all initially be owned by the user running the script, hopefully a superuser) and then doing ALTER OWNERs and GRANTs at the bottom. This seems a little cleaner to me, though it's got the problem that somebody would have to go off and implement the remaining ALTER OWNER commands. 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: [HACKERS] Converting postgresql.conf parameters to kilobytes
Shridhar Daithankar <[EMAIL PROTECTED]> writes: >> I remain unalterably opposed to the notion of measuring shared_buffers >> in KB, but if you think you can get such a thing in over my objections, > Are you OK with MBs? I am fine with anything. No, I'm not. shared_buffers should be measured in buffers (ie, pages). Anything else is obscurantism. Not to mention highly likely to confuse people who are used to how it's been set in the past. 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: [HACKERS] Converting postgresql.conf parameters to kilobytes
Shridhar Daithankar <[EMAIL PROTECTED]> writes: > Any updates/opinions? Should we convert assign hooks to perform actual > assignment and custom validation instead of just custom validation? It is > clear from README that it is for validation purposes only.. As it should be. Assign hooks have no business altering the user-supplied value. We do have provisions for letting string assign hooks do that, but the intended use of this was just for trivial display adjustments like case-normalizing time zone names. I remain unalterably opposed to the notion of measuring shared_buffers in KB, but if you think you can get such a thing in over my objections, the way to do it is to decouple the GUC parameter from NBuffers. The GUC setting is whatever it is; you can reject the value if it's too far out of range, but you do not editorialize upon it. What you do is compute the derived value for NBuffers and assign that in the assign hook. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] ACLs versus ALTER OWNER
Well, the spec doesn't have create permissions per se, but they do have a "usage" right on domains, and they specify that revoking that results in dropping objects: 7) For every abandoned domain descriptor DO, let S1.DN be the of DO. The following is effectively executed without further Access Rule checking: DROP DOMAIN S1.DN CASCADE Hmmm. Seems pretty harsh. But barring us implementing that (I don't see it happening for 7.5), just had an idea :) How about pg_dumpall dumps all users as superusers, and then changes them back to what they're supposed to be at the bottom of the script :) Easy :) Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested transactions and tuple header info
Bruce Momjian <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> I've been trying to think of ways to solve these problems by having a >> main xact and all its subxacts share a common CID sequence (ie, a >> subxact would have its own xid but would not start CID over at one). >> If you assume that, then Bruce's idea may indeed work, since you would >> never replace xmin in a way that would shift the interpretation of cmin >> into a different CID sequence. But I suspect there is a simpler way to >> solve it given that constraint. > I thought about using a global command counter. The problem there is > that there is no way to control the visibility of tuples by other > transactions on commit except going back end fixing up tuples, which is > unacceptable. No, I said own xid --- so the "phantom xid" part is still there. But your idea definitely does *not* work unless you use a single CID sequence for the whole main xact; and I'm still wondering if there's not a simpler implementation possible given that assumption. 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: [HACKERS] ACLs versus ALTER OWNER
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: >> The problem here is not with pg_dump; the problem is that dropping >> privileges doesn't cascade to dropping objects that are dependent on >> those privileges. AFAICS the SQL spec requires us to be able to do >> the latter. > The spec really requires that?? So basically we have RESTRICT and > CASCADE on REVOKE? Well, the spec doesn't have create permissions per se, but they do have a "usage" right on domains, and they specify that revoking that results in dropping objects: 7) For every abandoned domain descriptor DO, let S1.DN be the of DO. The following is effectively executed without further Access Rule checking: DROP DOMAIN S1.DN CASCADE Similarly, revoking access to tables etc. results in physical changes to views that reference those tables. So I think the idea is pretty clear. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested transactions and tuple header info
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> This is exactly the same argument as not being able to overwrite cmin. > > > Basically the phantom xid's are a shorthand for saying the tuple was > > created by xid1 and deleted by xid2, both part of the same main > > transaction. > > > A cursor looking at the rows has to recognize the xid is a phantom (via > > pg_subtrans) and look up the creation xid. > > You still don't see the point. Consider > > BEGIN; > DECLARE CURSOR c1 FOR SELECT * FROM a ...; > INSERT INTO a VALUES(...); -- call this row x > DECLARE CURSOR c2 FOR SELECT * FROM a ...; > BEGIN; > DELETE FROM a WHERE ...;-- assume this deletes row x > ROLLBACK; > FETCH FROM c1; -- must NOT see row x > FETCH FROM c2; -- must see row x > > AFAICS your proposal does not support this. The two cursors' snapshots > will differ only in the recorded current-cid for the outer transaction. > If the subtrans has overwritten xmin/cmin, there is no way to make that > decision correctly. I do not overwrite cmin or cmax. If xid=1 creates a row: xmin=1 cmin=1 and xid=2 goes to expire it, we get: xmin=3 (phantom for xmin=1, xmax=2) cmin=1 cmax=1 and we set a phantom bit on the tuple. When we see it later and need to know the xmin or xmax, we look it up in local memory. (Maybe we don't even need a hash, just a List because I can't imagine more than a few of these phantoms being used.) Other backends see the tuple with a xmin as "in progress" so they don't need to look any further. Then, on commit, we decide if a tuple created by xid=1 and expired by xid=2 should appear created or not, and mark the phantom commit status accordingly. -- 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: [HACKERS] sync vs. fsync question
On 5/31/2004 9:45 PM, Christopher Kings-Lynne wrote: Hi, I had this question posed to me on IRC and I didn't know the answer. If all that is needed to ensure integrity is that the WAL is fsynced, what is wrong with just going: wal_sync_method = fsync fsync = false The assumption that WAL is all that is needed to ensure integrity is wrong in the first place, unless you are going to keep the WAL forever and never recycle the segments. What you're effectively asking for is not to checkpoint any more. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] ACLs versus ALTER OWNER
The problem here is not with pg_dump; the problem is that dropping privileges doesn't cascade to dropping objects that are dependent on those privileges. AFAICS the SQL spec requires us to be able to do the latter. The spec really requires that?? So basically we have RESTRICT and CASCADE on REVOKE? That seems pretty odd to me. What's so wrong about allowing someone to create tables for a while and then revoking their permission to do it from now on?? That's exactly what we do for databases at the moment, we have an 'OWNER' clause. And that's how I coded tablespaces to be dumped as well. Either way, our concept of a superuser surely isn't in the spec, so can we at least fix that problem? ie. we dump lanugages as default session_authorization and then ALTER LANGUAGE it to change it to the correct user? Same for CREATE OPERATOR CLASS and ALTER OP CLASS, and CREATE CAST commands for binary-compatible casts. (I do note that neither of those ALTER forms allows changing owner and there is no ALTER CAST at all - we'd need to add them). If we're gonna invest work on fixing this, we ought to do what the spec tells us to, not invent warts on the security model. Sure. Let's be honest though and admit that there are a lot of broken dumps out there at the moment. For me, I have to change all my users to superusers before dumping, then change them all back after a restore. This is because we did a security crackdown and tightened up on everyone's privileges... Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Nested transactions and tuple header info
Alvaro Herrera <[EMAIL PROTECTED]> writes: > On Wed, Jun 02, 2004 at 09:52:28AM -0400, Tom Lane wrote: >> AFAICS your proposal does not support this. The two cursors' snapshots >> will differ only in the recorded current-cid for the outer transaction. >> If the subtrans has overwritten xmin/cmin, there is no way to make that >> decision correctly. > Why would it overwrite cmin? Only a new xmin is needed (and cmax and > xmax, but the cursors don't care about those) If you overwrite xmin and not cmin, you've created a nonsensical situation. How do you distinguish this tuple from tuples created by the subxact itself? More generally, cmin is only meaningful in combination with a particular transaction ID; you can't just arbitrarily replace xmin without changing cmin. I've been trying to think of ways to solve these problems by having a main xact and all its subxacts share a common CID sequence (ie, a subxact would have its own xid but would not start CID over at one). If you assume that, then Bruce's idea may indeed work, since you would never replace xmin in a way that would shift the interpretation of cmin into a different CID sequence. But I suspect there is a simpler way to solve it given that constraint. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested transactions and tuple header info
On Wed, Jun 02, 2004 at 09:52:28AM -0400, Tom Lane wrote: > BEGIN; > DECLARE CURSOR c1 FOR SELECT * FROM a ...; > INSERT INTO a VALUES(...); -- call this row x > DECLARE CURSOR c2 FOR SELECT * FROM a ...; > BEGIN; > DELETE FROM a WHERE ...;-- assume this deletes row x > ROLLBACK; > FETCH FROM c1; -- must NOT see row x > FETCH FROM c2; -- must see row x > > AFAICS your proposal does not support this. The two cursors' snapshots > will differ only in the recorded current-cid for the outer transaction. > If the subtrans has overwritten xmin/cmin, there is no way to make that > decision correctly. Why would it overwrite cmin? Only a new xmin is needed (and cmax and xmax, but the cursors don't care about those) -- Alvaro Herrera () "La fuerza no está en los medios físicos sino que reside en una voluntad indomable" (Gandhi) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested transactions and tuple header info
Bruce Momjian <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> This is exactly the same argument as not being able to overwrite cmin. > Basically the phantom xid's are a shorthand for saying the tuple was > created by xid1 and deleted by xid2, both part of the same main > transaction. > A cursor looking at the rows has to recognize the xid is a phantom (via > pg_subtrans) and look up the creation xid. You still don't see the point. Consider BEGIN; DECLARE CURSOR c1 FOR SELECT * FROM a ...; INSERT INTO a VALUES(...); -- call this row x DECLARE CURSOR c2 FOR SELECT * FROM a ...; BEGIN; DELETE FROM a WHERE ...;-- assume this deletes row x ROLLBACK; FETCH FROM c1; -- must NOT see row x FETCH FROM c2; -- must see row x AFAICS your proposal does not support this. The two cursors' snapshots will differ only in the recorded current-cid for the outer transaction. If the subtrans has overwritten xmin/cmin, there is no way to make that decision correctly. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Converting postgresql.conf parameters to kilobytes
Hi,
Any updates/opinions? Should we convert assign hooks to perform actual
assignment and custom validation instead of just custom validation? It is
clear from README that it is for validation purposes only..
Or Shall i look for some place else to perform conversion?
Shridhar
On Tuesday 01 June 2004 18:01, Shridhar Daithankar wrote:
> On Tuesday 01 June 2004 14:12, Shridhar Daithankar wrote:
> > Actually I need to find out few more things about it. It is not as simple
> > as adding a assign_hook. When I tried to initdb with changes, it demanded
> > 64MB of shared buffers which I (now) think that somewhere NBuffers are
> > used before postgresql.conf is parsed. So 8192*8000=64MB. But this is
> > just guesswork. Haven't looked in it there.
>
> Found it. Following is the code that is causing problem.
>
> guc.c:2998
> ---
> if (conf->assign_hook)
> if (!(*conf->assign_hook) (newval, changeVal,
> source))
> {
> ereport(elevel,
>
> (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
>errmsg("invalid value
> for parameter \"%s\": %d",
> name,
> newval)));
> return false;
> }
>
> if (changeVal || makeDefault)
> {
> if (changeVal)
> {
> *conf->variable = newval;
> conf->gen.source = source;
> }
> ---
>
> So even if assign_hook is executed, the value of variable is overwritten in
> next step which nullifies any factoring/change in value done in assign
> hook.
>
> I find this as a convention at many other place at guc.c. Call assign_hook
> and the overwrite the value. So is assign_hook called only to validate the
> value? How do I modify the value of the variable without getting specific?
>
> I tried
>
> if (changeVal && !(conf->assign_hook))
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] ACLs versus ALTER OWNER
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > I think we need a proper 'effective user' facility. > At the moment, there's breakage if a super user creates a language, then > drops their superuser privs, then the dump cannot be restored. The problem here is not with pg_dump; the problem is that dropping privileges doesn't cascade to dropping objects that are dependent on those privileges. AFAICS the SQL spec requires us to be able to do the latter. If we're gonna invest work on fixing this, we ought to do what the spec tells us to, not invent warts on the security model. Tossing in expedient concepts like "effective user" is a great recipe for creating unfixable security holes. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ACLs versus ALTER OWNER
Fabien COELHO <[EMAIL PROTECTED]> writes:
> Due to how ACL are defined in SQL, I restate my suggestion that the super
> user should be able to change ANY right, including the GRANTOR field,
I'm unconvinced of this: that philosophy soon leads you into allowing
the superuser to create self-inconsistent sets of rights, such as rights
that flow from "nowhere" (i.e., are not traceable through an unbroken
chain to the original owner's grant options). The changes we have been
making recently are specifically designed to prevent such situations,
and I don't really wish to backtrack.
It's worth pointing out also that the superuser can always brute-force
things:
UPDATE pg_class SET relacl = '{ ... anything ...}' WHERE ...
and so we don't really need to provide escape hatches in GRANT/REVOKE
that are only useful to superusers. I think our concern with
GRANT/REVOKE should be to provide a self-consistent set of operations.
We're about there AFAICT with respect to GRANT/REVOKE themselves, but
ALTER OWNER as currently defined breaks 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: [pgsql-hackers-win32] [HACKERS] select like...not using index
> Hmmm, snipped from your reply was the explain plan from the query where it > was clear you were using two different character data types: bpchat and > text. That, alone, may have been a problem. > Looking at your defaults, did you do: > initdb --locale=C somepath I reran initdb --locale=C yesterday and that fixed the problem. Since I am doing the nightly win32 builds I run initdb each night around 1am and I missed the locale warning. I had a feeling it was something like this. The part I don't understand is why psql was saying the locale (show lc_ctype) was 'C' when pg_controldata was not. This, along with recent code revisions tricked me for a while (not to mention the default locale being changed). Here is what I think happened (this might be a bug, might not): Each night I run initdb but I use a special postgresql.conf which is optimized for quick data loading. This is copied over the default one after the server is started. This contains the locale information which is 'initialized by initdb'. These were still 'C' because this file was generated before the default locale was changed. psql shows this information when you ask it for the locale info even if it is incorrect. The real settings are of course built into the database itself. This stuff is all new to me, I've never really had to deal with locales before. > Personally, I think, if I do not specify a locale, I don't want a specific > locale. Period. I haven't been paying too close attention to the hackers > list to say when this happened, but it bit me a couple times. I now accept this as dogma :) Merlin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] ACLs versus ALTER OWNER
On Wed, 2004-06-02 at 18:44, Christopher Kings-Lynne wrote: > > REVOKE ALL ON TABLE foo FROM GRANTOR [USER] alice; > > > > The super user must really be a *super* user. > > I think we need a proper 'effective user' facility. > > At the moment, there's breakage if a super user creates a language, then > drops their superuser privs, then the dump cannot be restored. > > All other failure cases also exist. eg if a gumby user creates a table > in a schema, then has his permission to create tables in that schema > revoked. The dump will be broken. > > The solution seems to me that we need to have an 'effective_user' SET > option so that the superuser doing the restore can still create tables > owned by the gumby, even though the gumby does not have privileges to do > so. If I remember correctly, we already have this option. ALTER table OWNER to newowner; Perhaps pg_dump should just include; ALTER relation OWNER to originalowner; at the end of the dump, instead of connecting as the owner to restore it. > > Chris > > > ---(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 Regards, John ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] query INSERT OR REPLACE
In SQLite or MySQL there is a statement INSERT OR REPLACE , is something like this in postgres , or could be ? No, there isn't and there currently isn't anyone working on adding it. Chris ---(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
[HACKERS] query INSERT OR REPLACE
Hi In SQLite or MySQL there is a statement INSERT OR REPLACE , is something like this in postgres , or could be ? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] ACLs versus ALTER OWNER
REVOKE ALL ON TABLE foo FROM GRANTOR [USER] alice; The super user must really be a *super* user. I think we need a proper 'effective user' facility. At the moment, there's breakage if a super user creates a language, then drops their superuser privs, then the dump cannot be restored. All other failure cases also exist. eg if a gumby user creates a table in a schema, then has his permission to create tables in that schema revoked. The dump will be broken. The solution seems to me that we need to have an 'effective_user' SET option so that the superuser doing the restore can still create tables owned by the gumby, even though the gumby does not have privileges to do so. Chris ---(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: [HACKERS] ACLs versus ALTER OWNER
Dear Tom, > [...] > Even more interesting, the superuser can't fix it either, Due to how ACL are defined in SQL, I restate my suggestion that the super user should be able to change ANY right, including the GRANTOR field, with an appropriate syntax, something like: REVOKE ALL ON TABLE foo FROM GRANTOR [USER] alice; The super user must really be a *super* user. > ISTM that reasonable behavior for ALTER OWNER would include doing > surgery on the object's ACL to replace references to the old owner by > references to the new owner. [...] I'm about so submit a fix for "create database" so that ownership and acl would be fixed wrt to the owner of the database. This patch will include a function to switch grantor rights that might be of interest for the above purpose, as it may save you little time. I'll try to send the patch submission this week-end. > I think there are corner cases where the merging might produce > unintuitive results, but it couldn't be as spectacularly bad as > doing nothing is. I agree that these is work to do in the ACL area... As an additionnal suggestion, I noticed in my tests that nothing is really tested in the regression tests. It would be useful to have tests cases of acl with accesses allowed or forbidden, maybe with a systematic and exhaustive approach... It takes time to do that, but I think it would be useful so as to measure what is needed. Have a nice day, -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
