Re: [GENERAL] Grant on Database?
> Hey All,
>
> We have a need to grant privileges on entire databases to users and/or
> groups. It looks like GRANT just grants on tables and sequences, but I'd
> like to know if there's a more direct way to do it. What I'm doing now
> is getting a list of tables and sequences and calling grant for each one
> in turn. How am I getting this list (I'm user Perl, not psql)? With this
> query:
>
> SELECT relname
> FROM pg_class
> WHERE relkind IN ('r', 'S')
>AND relowner IN (
>SELECT usesysid
>FROM pg_user
>WHERE LOWER(usename) = 'myuser')
>
> Anyway, pointers to any shortcuts for this would be greatly appreciated.
First pointer, phpPgAdmin (http://www.greatbridge.org/project/phppgadmin)
has this built into it. It will automatically get the list of tables,
sequences and views and run a grant statment on them.
Second pointer. GRANT will take multiple "relations" seperated by commas:
GRANT ALL ON table1, table1, seq1, seq2, view1, view2 TO my_user;
-Dan
Re: [GENERAL] Weird indices
Joseph Shraibman <[EMAIL PROTECTED]> writes: > > Note that this all implies that when walking through the index to find > > heap tuples, you must check the current validity of each heap tuple. > > It is normal for an index tuple to point to a heap tuple which has > > been deleted. > > > > > > > > I'm talking about indices. The index should be updated to only point at > > > valid rows. > > > > When should the index be updated to only point at valid rows? That is > > only possible when a heap tuple is finally and completely removed. > > But currently that is only known at the time of a VACUUM. > > > You just said above 'It is normal for an index tuple to point to a heap > tuple which has been deleted.' I'm not sure what your point is here. I can see that there is an ambiguity in what I said. I said it is normal for an index tuple to point to a heap tuple which has been deleted. However, although the heap tuple has been deleted in present time, there may still be transactions which do not see that heap tuple as having been deleted. So the index tuple is still meaningful until all those transactions have completed. When all such transactions have completed is the case I meant when I described the heap tuple as finally and completely removed. > > Consider a transaction which sits around for a while and then aborts. > > At the moment that the transaction aborts, Postgres may become able to > > remove some heap tuples and some index tuples, and it might be invalid > > for Postgres to remove those tuples before the transaction aborts. > > > > But the transaction might never have looked at those tuples. So, > > given the Postgres data structures, the only way to keep an index > > fully up to date would be to effectively run a VACUUM over the entire > > database every time a transaction aborts. > > Why? There is a mechanism for keeping track of which heap tuples are > valid, why not index tuples? It is the nature of indices to be updated > on inserts, why not deletes? I would think that the advantage of being > able to use the index in the planner would outweigh the immediate cost > of doing the update. You're right. The mechanism used to preserve multiple versions of heap tuples could be extended to index tuples as well. Based on the heap tuple implementation, this would require adding two transaction ID's and a few flags to each index tuple. That's not insignificant. In a B-tree, right now, I think there are 8 bytes plus the key for each item in the tree. This would require adding another 10 bytes or so. That's a lot. Also, more work would be required for every update. Right now an update requires a B-tree insert for each index. With this change, every update would require an additional B-tree lookup and write for each index. That would require on average a bit less than one additional block write per index. That's a lot. In exchange, certain queries would become faster. Specifically, any query which only needed the information found in an index would become faster. Each such query would save on average a bit less than one additional block read per value found in the index. But since the indices would be less efficient, some of the advantage would be lost due to extra block reads of the index. What you are suggesting seems possible, but it does not seem to be obviously better. If you feel strongly about this, the most reasonable thing would be for you to implement it, and test the results. Since as far as I can see what you are suggesting is not clearly better, it's unlikely that anybody else is going to go to the considerable effort of implement it on your behalf. > > > But if the index isn't used by the planner then the point > > > is moot. > > > > As far as I know the index itself isn't used by the planner. > > > But could be. As I understand it the reason the index isn't used by the > planner is because the index could point at non-visible rows (row = heap > tuple). If the index could be used, many things now that are seq scans > could be converted to faster index scans. Some things could, sure. It's not obvious to me that many things could. The planner can't spend a lot of time looking at an index to decide whether or not to use it. If it's going to do that, it's better off to just decide to use the index in the first place. Index examination is not free. It requires disk reads just like everything else. > > I don't think there is any way to do that today. It would be possible > > to implement something along the lines I suggest above. I have no > > idea if the Postgres maintainers have any plans along these lines. > > > At the end of a transaction, when it sets the bit that this tuple isn't > valid, couldn't it at the same time also remove it if was no longer > visible to any transaction? It wouldn't remove the need for vacuum > because there may be another transaction that prevents it from being > removed right then and there. Yes, this could be done.
Re: [GENERAL] Bug in my ( newbie ) mind?
Dan Lyke <[EMAIL PROTECTED]> writes: > So one might think that, with appropriate casting, something more > like: >select (select phone_prefix.prefix order by random() limit 1) || ... > would be more likely to work (modulo some casting and such). Note this will not work in pre-7.1 releases --- 7.1 is the first that allows ORDER BY and LIMIT clauses in a sub-select. regards, tom lane
[GENERAL] Bug in my ( newbie ) mind?
Christopher Sawtell writes: > chris=# select phone_prefix.prefix order by random() limit 1 || '-' || > lpad((random()*1)::int, 4, '0')::text as "Phone Number"; > ERROR: parser: parse error at or near "||" This sure won't fix everything, but at the very least you need to parenthesize that first select clause inside another select. For instance, this works: select (select '123'::text) || (select '456'::text); So one might think that, with appropriate casting, something more like: select (select phone_prefix.prefix order by random() limit 1) || ... would be more likely to work (modulo some casting and such). Dan
Re: [GENERAL] Bug in my ( newbie ) mind?
On Wed, 21 Feb 2001, Christopher Sawtell wrote: > chris=# select phone_prefix.prefix order by random() limit 1 || '-' || > lpad((random()*1)::int, 4, '0')::text as "Phone Number"; All the things you are selecting need to come in the first part of the query. like, SELECT prefix || '-' || lpad((random()*1)::int, 4, '0')::text as "Phone Number" from phone_prefix order by random() limit 1; -- Tod McQuillin
[GENERAL] Bug in my ( newbie ) mind?
Greetings,
Please, what am I doing wrong?
chris=# \d phone_prefix
Table "phone_prefix"
-[ RECORD 1 ]--
Attribute | number
Type | integer
Modifier | not null default nextval('"phone_prefix_number_seq"'::text)
-[ RECORD 2 ]--
Attribute | prefix
Type | text
Modifier |
Index: phone_prefix_number_key
chris=# select phone_prefix.prefix order by random() limit 1;
prefix
384
(1 row)
Wonderful, works exactly as expected.
chris=# select lpad((random()*1)::int, 4, '0')::text as "Number";
Number
2958
(1 row)
ditto
But attempting to concatenate the two is a disaster.
chris=# select phone_prefix.prefix order by random() limit 1 || '-' ||
lpad((random()*1)::int, 4, '0')::text as "Phone Number";
ERROR: parser: parse error at or near "||"
chris=#
What am i doing wrong?
chris=# select version();
version
PostgreSQL 7.1beta3 on i586-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)
Thanks a 10^6
p.s. imho it would be a terrific help, especially for new-comers to SQL
like me, if the parser could be persuaded to utter just a tiny glimmer of
a hint as to what it thinks one's mistake is instead of the rather
enigmatic "ERROR: parser: parse error at or near".
Is it possible for mere mortals to help?
--
Sincerely etc.,
NAME Christopher Sawtell
CELL PHONE 021 257 4451
ICQ UIN45863470
EMAIL csawtell @ xtra . co . nz
CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz
-->> Please refrain from using HTML or WORD attachments in e-mails to me
<<--
Re: [GENERAL] Weird indices
Joseph Shraibman <[EMAIL PROTECTED]> writes: > Why? There is a mechanism for keeping track of which heap tuples are > valid, why not index tuples? It is the nature of indices to be updated > on inserts, why not deletes? An index is a hint: these tuples *might* be of interest to your transaction. It's OK for an index to point to some irrelevant tuples, but it's useless if it fails to point to all the possibly relevant tuples. Therefore, it's OK to insert an index entry at the earliest possible instant (as soon as a yet-uncommitted heap tuple is inserted); and contrariwise the index entry can't be deleted until the heap tuple can be proven to be no longer of interest to any still-alive transaction. Currently, proving that a heap tuple is globally no-longer-of-interest and removing it and its associated index tuples is the task of VACUUM. Intermediate state transitions (eg, this tuple has been deleted by a not-yet-committed transaction) are recorded in the heap tuple, but we don't try to look around and update all the associated index tuples at the same time. Maintaining that same state in all the index tuples would be expensive and would bloat the indexes. An index that's not a lot smaller than the associated heap is of little value, so extra bits in an index entry are to be feared. These are very fundamental system design decisions. If you'd like to show us the error of our ways, step right up to the plate and swing away; but unsubstantiated suggestions that these choices are wrong are not going to be taken with any seriousness. Postgres has come pretty far on the basis of these design choices. regards, tom lane
Re: [GENERAL] Weird indices
Ian Lance Taylor wrote: > > Joseph Shraibman <[EMAIL PROTECTED]> writes: > > > > > I understand that keeping different views for different open > > > > transactions can be difficult, but after a transaction that updates a > > > > row is over why isn't the row marked as 'universally visible' for all > > > > new transactions until another update occurs? > > > > > > It is. This mark is on the tuple in the heap. When a tuple is > > > current, and not locked for update, HEAP_XMAX_INVALID is set. After > > > the tuple is removed, HEAP_XMAX_COMMITTED is set. > > > > On the heap, but when is the index updated? Not until the next vacuum? > > The index is updated right away. Otherwise it could never be used, > since it would be inaccurate. I meant cleaned up. Which you answered: when vacuumed. > > Note that this all implies that when walking through the index to find > heap tuples, you must check the current validity of each heap tuple. > It is normal for an index tuple to point to a heap tuple which has > been deleted. > > > > I'm talking about indices. The index should be updated to only point at > > valid rows. > > When should the index be updated to only point at valid rows? That is > only possible when a heap tuple is finally and completely removed. > But currently that is only known at the time of a VACUUM. > You just said above 'It is normal for an index tuple to point to a heap tuple which has been deleted.' > Consider a transaction which sits around for a while and then aborts. > At the moment that the transaction aborts, Postgres may become able to > remove some heap tuples and some index tuples, and it might be invalid > for Postgres to remove those tuples before the transaction aborts. > > But the transaction might never have looked at those tuples. So, > given the Postgres data structures, the only way to keep an index > fully up to date would be to effectively run a VACUUM over the entire > database every time a transaction aborts. Why? There is a mechanism for keeping track of which heap tuples are valid, why not index tuples? It is the nature of indices to be updated on inserts, why not deletes? I would think that the advantage of being able to use the index in the planner would outweigh the immediate cost of doing the update. > > > But if the index isn't used by the planner then the point > > is moot. > > As far as I know the index itself isn't used by the planner. > But could be. As I understand it the reason the index isn't used by the planner is because the index could point at non-visible rows (row = heap tuple). If the index could be used, many things now that are seq scans could be converted to faster index scans. > I don't think there is any way to do that today. It would be possible > to implement something along the lines I suggest above. I have no > idea if the Postgres maintainers have any plans along these lines. > At the end of a transaction, when it sets the bit that this tuple isn't valid, couldn't it at the same time also remove it if was no longer visible to any transaction? It wouldn't remove the need for vacuum because there may be another transaction that prevents it from being removed right then and there. But for index tuples we could use your list system because (as I see it) the value of being able to use the index in the planner would outweigh the cost of the list system. > Ian -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
Re: [GENERAL] Weird indices
On Tue, Feb 20, 2001 at 05:02:22PM -0800, Stephan Szabo wrote: > > IIRC, There's something which is effectively : > estimated rows = * > I think fraction defaults to (is always?) 1/10 for the standard > index type. That's where the 50 comes from. And the frequency is > probably from the last vacuum analyze. Is there a way to change this fraction? We have a table with over 1 million rows and the statistics Postgres gathers are not particularly useful. There is not one (non-null) value that occurs significantly more often than other values but the distribution looks a lot like a 1/x curve I guess. The most common value occurs 5249 times but the average is only 95, so Postgres chooses seq scan almost always. We actually now set enable_seqscan=off in many areas of our code to speed it up to a useful rate. (This table also happens to have an (accedental) clustering on this column also). What is the reasoning behind estimating like that? Why not just the average or the average + 1 SD? Another idea, is there a use for making a "cohesiveness" index. ie. if you're looking X by looking up the index, on average, how many also matching tuples will be in the next 8k (or whatever size). Since these are likely to be in the cache the cost of retreival would be much lower. This would mean that an index on a clustered column would have a much lower estimated cost than an index on other columns. This would make clustering more useful. I think I'll stop rambling now... Martijn
[GENERAL] pg_shadow.passwd versus pg_hba.conf password passwd
Re-Sending due to rejection after subscribing, before confirming. Sorry if two make it through... Background: Trying to use a Cobalt box that has PostgreSQL pre-installed. I can change localhost "crypt" to "trust" in pg_hba.conf, but I don't really want to do that long-term. If I'm reading "man pg_passwd" correctly, I can create a standard Un*x passwd file and use that with "password" in pg_hba.conf However, the current installation seems to be using "crypt", with no passwd file, and with unencrypted passwords in the pg_shadow.passwd field -- Or, at least, as far as I can tell, since /etc/.meta.id has the same text as the admin's pg_shadow.passwd field. So, my question is, what is the "passwd" field in pg_shadow for?... Is that where an unencrypted password would be stored if I used "password" rather than "crypt"?... That seems the exact opposite of the reality on this box. Or can I get pg_hba.conf to just use that field somehow with "crypt"? If I *cannot* use pg_shadow.passwd for the encrypted password, and I use standard Un*x passwd file, does create_user know enough with -P to fill that in properly, or am I on my own?... How is Cobalt getting this to work with "localhost all crypt" in pg_hba.conf, but the password does not seem to be encrypted: /etc/.meta.id is plaintext of pg_shadow.passwd, and there is no obvious passwd file, so where's the crypt? I've installed PostgreSQL before, and all this stuff just worked somehow. :-^ I'm reading all the docs I can find, but interpreting them correctly is another matter :-) Please Cc: me, as I'm not really active on this list...
Re: [GENERAL] Weird indices
On Tue, 20 Feb 2001, Joseph Shraibman wrote: > Err I wan't complaing about count(*) per se, I was just using that as a > simple example of something that should be done with an index. Because > if the index doesn't have to worry about rows that aren't current then > you don't even have to go into the heap because the index alone should > have enough information to do that. If it doesn't have to worry about > rows that aren't visible. But the problem is how do you deal with concurrency? At any given point in time there are different sets of rows that are "current" for different transactions. They all need to be in the index so that index scans work for those transactions (unless you were to do something hacky to get around it) but not all of them are valid for each transaction, you still have to get that information somehow. You can keep the transaction information in the index but I know Tom's talked this idea down in the past (it's come up on hackers before), I don't really remember what the full arguments were both ways.
Re: [GENERAL] Weird indices
Stephan Szabo wrote: > > On Tue, 20 Feb 2001, Joseph Shraibman wrote: > > > Stephan Szabo wrote: > > > > > Where are you seeing something that says the estimator/planner using the > > > index to get an upper bound? The estimator shouldn't be asking either the > > > index or the heap for anything, it should be working entirely with the > > > statistics that were generated from vacuum. > > > > Index Scan using usertable_p_key on usertable (cost=0.00..25.68 rows=50 > > width=72) > > > > That rows=50, which is an overestimate by the way. > > That's because the estimate in this case was 50 and so it's estimating > that going through the index and checking the heap is faster than a > sequence scan. The *estimator* didn't use the index to figure that out, > it's just saying that the best plan to actually *run* the query uses > the index. > IIRC, There's something which is effectively : > estimated rows = * > I think fraction defaults to (is always?) 1/10 for the standard > index type. That's where the 50 comes from. And the frequency is > probably from the last vacuum analyze. Then it should do the same thing no matter what value I use, but when I do different searches in one case it estimates 50 when there are 16 and in the other it estimeates 502 where there are 502. -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
Re: [GENERAL] Weird indices
Joseph Shraibman <[EMAIL PROTECTED]> writes: > > > I understand that keeping different views for different open > > > transactions can be difficult, but after a transaction that updates a > > > row is over why isn't the row marked as 'universally visible' for all > > > new transactions until another update occurs? > > > > It is. This mark is on the tuple in the heap. When a tuple is > > current, and not locked for update, HEAP_XMAX_INVALID is set. After > > the tuple is removed, HEAP_XMAX_COMMITTED is set. > > On the heap, but when is the index updated? Not until the next vacuum? The index is updated right away. Otherwise it could never be used, since it would be inaccurate. When a new tuple is inserted in the heap, the index is updated to point to the new tuple. This involves inserting new tuples into the index. The old tuples in the index are left untouched, and continue to point to the old tuple in the heap. The old tuples in the index, and the heap, are removed by VACUUM. VACUUM walks through the index and checks the heap tuple corresponding to each index tuple. If the heap tuple is gone, or can no longer be seen by any transaction, then the index tuple can be removed. Note that this all implies that when walking through the index to find heap tuples, you must check the current validity of each heap tuple. It is normal for an index tuple to point to a heap tuple which has been deleted. > > > Maybe this is part of the whole 'vacuum later' vs. 'update now' > > > philosophy. If the point of vacuum later is to put off the performance > > > hit until later if it is causing these performance hits on queries > > > because index scans aren't being used then doesn't that mean 'update > > > now' is more likely to pay off in the short run? > > > > I don't follow. A simple VACUUM doesn't update the statistics. > > VACUUM ANALYZE has to do more work. > > I'm talking about indices. The index should be updated to only point at > valid rows. When should the index be updated to only point at valid rows? That is only possible when a heap tuple is finally and completely removed. But currently that is only known at the time of a VACUUM. Consider a transaction which sits around for a while and then aborts. At the moment that the transaction aborts, Postgres may become able to remove some heap tuples and some index tuples, and it might be invalid for Postgres to remove those tuples before the transaction aborts. But the transaction might never have looked at those tuples. So, given the Postgres data structures, the only way to keep an index fully up to date would be to effectively run a VACUUM over the entire database every time a transaction aborts. OK, that's not quite true. It would be possible to keep a list in shared memory of tuples which were recently dropped. Then as transactions dropped out, it would be possible to see which ones could be completely removed. This list of tuples would presumably include index tuples. > But if the index isn't used by the planner then the point > is moot. As far as I know the index itself isn't used by the planner. > > Are you suggesting that the statistics should be updated > > continuously? I guess that would be doable, but it would clearly > > slow down the database. For some applications, it would be an > > obviously bad idea. > > No, I'm suggesting that indices should be updated continuously so the > planner can use them without having a performance hit from checking if > tuples are valid or not. Well, as far as I know, the planner doesn't use the index. It only uses the statistics. > BTW is there any way to tell postgres to do an update at every commit > without waiting for a vacuum? I understand that the postgres core team > thinks it is a bad idea, but there are ways to (sort of) force using > index scans whent he planner doesn't want to, so is there something > similar to force incremental vacuuming at the end of each query? > > Java has this option: > -Xincgc enable incremental garbage collection > > that isn't recommended, but the java developers recognized that > sometimes a user might want it anyway for whatever reason. I don't think there is any way to do that today. It would be possible to implement something along the lines I suggest above. I have no idea if the Postgres maintainers have any plans along these lines. Ian
Re: [GENERAL] Weird indices
Joseph Shraibman <[EMAIL PROTECTED]> writes: > Then it should do the same thing no matter what value I use, but when I > do different searches in one case it estimates 50 when there are 16 and > in the other it estimeates 502 where there are 502. Well, it does know the difference between searching for the most common value and searching for other values, but whether that's relevant to your example is impossible to say with no details. regards, tom lane
Re: [GENERAL] Weird indices
Err I wan't complaing about count(*) per se, I was just using that as a simple example of something that should be done with an index. Because if the index doesn't have to worry about rows that aren't current then you don't even have to go into the heap because the index alone should have enough information to do that. If it doesn't have to worry about rows that aren't visible. Tom Lane wrote: > > Joseph Shraibman <[EMAIL PROTECTED]> writes: > > Maybe I'm not making myself understood. Another way of asking the same > > thing: > > Say there is a transaction that is looking at a non-current version of a > > row. 'non-current' could be the value it was at the start of the > > transaction (and was updated by another transaction) or was updated by > > this transaction but not committed yet. When this transaction is over > > is it really that hard to get rid of the refrence to the old version of > > the row? There should be a 1 bit field 'is old value and isn't being > > used by any transaction'. Is that really hard? > > Sure, it's easy to do that sort of bookkeeping ... on a per-row basis. > And we do. What's not so easy (an index helps not at all) is to > summarize N per-row status values into a single count(*) statistic that > you can maintain in a way significantly cheaper than just scanning the > rows when you need the count(*) value. Especially when the per-row > status values interact with the state values of the observing process > to determine what it should think count(*) really is. > > The issue is not really "could we make count(*) fast"? Yeah, we > probably could, if that were the only measure of performance we cared > about. The real issue is "can we do it at a price we're willing to pay, > considering the costs of slowdown of insert/update/delete operations, > extra storage space, and extra system complexity?" So far the answer's > been "no". > > You might want to look at the manual's discussion of MVCC and at the > Postgres internals talks that were given at OSDN (see slides at > http://www.postgresql.org/osdn/index.html) to learn more about how > things work. > Ugh, pdf format. I'll have to remember to look at them next time I'm on a windows box. > regards, tom lane -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
Re: [GENERAL] Weird indices
On Tue, 20 Feb 2001, Joseph Shraibman wrote: > > That's because the estimate in this case was 50 and so it's estimating > > that going through the index and checking the heap is faster than a > > sequence scan. The *estimator* didn't use the index to figure that out, > > it's just saying that the best plan to actually *run* the query uses > > the index. > > IIRC, There's something which is effectively : > > estimated rows = * > > I think fraction defaults to (is always?) 1/10 for the standard > > index type. That's where the 50 comes from. And the frequency is > > probably from the last vacuum analyze. > > Then it should do the same thing no matter what value I use, but when I > do different searches in one case it estimates 50 when there are 16 and > in the other it estimeates 502 where there are 502. It knows enough to do the special case where you are searching for the most common value. I'd guess that's what's happening on the 502. I think it stores the most common value and the fraction of rows that represents as of last vacuum analyze.
Re: [GENERAL] Weird indices
Ian Lance Taylor wrote: > > Joseph Shraibman <[EMAIL PROTECTED]> writes: > > A caveat on this reply: I've been studying the Postgres internals, but > I have not mastered them. > > > I understand that keeping different views for different open > > transactions can be difficult, but after a transaction that updates a > > row is over why isn't the row marked as 'universally visible' for all > > new transactions until another update occurs? > > It is. This mark is on the tuple in the heap. When a tuple is > current, and not locked for update, HEAP_XMAX_INVALID is set. After > the tuple is removed, HEAP_XMAX_COMMITTED is set. On the heap, but when is the index updated? Not until the next vacuum? > > > Maybe I'm not making myself understood. Another way of asking the same > > thing: > > Say there is a transaction that is looking at a non-current version of a > > row. 'non-current' could be the value it was at the start of the > > transaction (and was updated by another transaction) or was updated by > > this transaction but not committed yet. When this transaction is over > > is it really that hard to get rid of the refrence to the old version of > > the row? There should be a 1 bit field 'is old value and isn't being > > used by any transaction'. Is that really hard? > > There is a 1 bit field indicating that a tuple is an old value. > Postgres can also determine whether any transaction can see the > tuple. It does this by storing the transaction ID in the t_xmax > field. If all current transactions are newer than that transaction > ID, then that tuple is no longer visible to any transaction. > > In fact, I believe that is what the VACUUM command looks for. > > > Maybe this is part of the whole 'vacuum later' vs. 'update now' > > philosophy. If the point of vacuum later is to put off the performance > > hit until later if it is causing these performance hits on queries > > because index scans aren't being used then doesn't that mean 'update > > now' is more likely to pay off in the short run? > > I don't follow. A simple VACUUM doesn't update the statistics. > VACUUM ANALYZE has to do more work. I'm talking about indices. The index should be updated to only point at valid rows. But if the index isn't used by the planner then the point is moot. > > Are you suggesting that the statistics should be updated continuously? > I guess that would be doable, but it would clearly slow down the > database. For some applications, it would be an obviously bad idea. No, I'm suggesting that indices should be updated continuously so the planner can use them without having a performance hit from checking if tuples are valid or not. BTW is there any way to tell postgres to do an update at every commit without waiting for a vacuum? I understand that the postgres core team thinks it is a bad idea, but there are ways to (sort of) force using index scans whent he planner doesn't want to, so is there something similar to force incremental vacuuming at the end of each query? Java has this option: -Xincgc enable incremental garbage collection that isn't recommended, but the java developers recognized that sometimes a user might want it anyway for whatever reason. -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
Re: [GENERAL] Weird indices
Joseph Shraibman <[EMAIL PROTECTED]> writes: A caveat on this reply: I've been studying the Postgres internals, but I have not mastered them. > I understand that keeping different views for different open > transactions can be difficult, but after a transaction that updates a > row is over why isn't the row marked as 'universally visible' for all > new transactions until another update occurs? It is. This mark is on the tuple in the heap. When a tuple is current, and not locked for update, HEAP_XMAX_INVALID is set. After the tuple is removed, HEAP_XMAX_COMMITTED is set. > Maybe I'm not making myself understood. Another way of asking the same > thing: > Say there is a transaction that is looking at a non-current version of a > row. 'non-current' could be the value it was at the start of the > transaction (and was updated by another transaction) or was updated by > this transaction but not committed yet. When this transaction is over > is it really that hard to get rid of the refrence to the old version of > the row? There should be a 1 bit field 'is old value and isn't being > used by any transaction'. Is that really hard? There is a 1 bit field indicating that a tuple is an old value. Postgres can also determine whether any transaction can see the tuple. It does this by storing the transaction ID in the t_xmax field. If all current transactions are newer than that transaction ID, then that tuple is no longer visible to any transaction. In fact, I believe that is what the VACUUM command looks for. > Maybe this is part of the whole 'vacuum later' vs. 'update now' > philosophy. If the point of vacuum later is to put off the performance > hit until later if it is causing these performance hits on queries > because index scans aren't being used then doesn't that mean 'update > now' is more likely to pay off in the short run? I don't follow. A simple VACUUM doesn't update the statistics. VACUUM ANALYZE has to do more work. Are you suggesting that the statistics should be updated continuously? I guess that would be doable, but it would clearly slow down the database. For some applications, it would be an obviously bad idea. Ian
Re: [GENERAL] Weird indices
Joseph Shraibman <[EMAIL PROTECTED]> writes: > Maybe I'm not making myself understood. Another way of asking the same > thing: > Say there is a transaction that is looking at a non-current version of a > row. 'non-current' could be the value it was at the start of the > transaction (and was updated by another transaction) or was updated by > this transaction but not committed yet. When this transaction is over > is it really that hard to get rid of the refrence to the old version of > the row? There should be a 1 bit field 'is old value and isn't being > used by any transaction'. Is that really hard? Sure, it's easy to do that sort of bookkeeping ... on a per-row basis. And we do. What's not so easy (an index helps not at all) is to summarize N per-row status values into a single count(*) statistic that you can maintain in a way significantly cheaper than just scanning the rows when you need the count(*) value. Especially when the per-row status values interact with the state values of the observing process to determine what it should think count(*) really is. The issue is not really "could we make count(*) fast"? Yeah, we probably could, if that were the only measure of performance we cared about. The real issue is "can we do it at a price we're willing to pay, considering the costs of slowdown of insert/update/delete operations, extra storage space, and extra system complexity?" So far the answer's been "no". You might want to look at the manual's discussion of MVCC and at the Postgres internals talks that were given at OSDN (see slides at http://www.postgresql.org/osdn/index.html) to learn more about how things work. regards, tom lane
Re: [GENERAL] Re: A How-To: PostgreSQL from Tcl via ODBC
Tom Lane writes: > Re-run configure, and watch to make sure that it finds bison this time. > You'll need flex too, if you intend to build from CVS sources. And if you're going to use the ODBC drivers under Linux (or any other OS that links C "strings" into read only memory) you'll need pretty recent CVS sources. One of the bugs I had to track down even though my original CVS update was only a few weeks old. Dan
Re: [GENERAL] vacuum analyze again...
Bruce Momjian <[EMAIL PROTECTED]> writes: >> I find it hard to believe that VAC ANALYZE is all that much slower than >> plain VACUUM anyway; fixing the indexes is the slowest part of VACUUM in >> my experience. It would be useful to know exactly what the columns are >> in a table where VAC ANALYZE is considered unusably slow. > VACUUM ANALYZE does a huge number of adt/ function calls. It must be > those calls that make ANALYZE slower. People report ANALYZE is > certainly slower, and that is the only difference. That's why I'm asking what the data is. The function calls per se can't be that slow; I think there must be some datatype-specific issue. With TOAST in the mix, TOAST fetches could very well be an issue, but I didn't think 7.1 was being discussed ... regards, tom lane
Re: [GENERAL] vacuum analyze again...
> To get a partial VACUUM ANALYZE that was actually usefully faster than > the current code, I think you'd have to read just a few percent of the > blocks, which means much less than a few percent of the rows ... unless > maybe you picked selected blocks but then used all the rows in those > blocks ... but is that a random sample? It's debatable. > > I find it hard to believe that VAC ANALYZE is all that much slower than > plain VACUUM anyway; fixing the indexes is the slowest part of VACUUM in > my experience. It would be useful to know exactly what the columns are > in a table where VAC ANALYZE is considered unusably slow. VACUUM ANALYZE does a huge number of adt/ function calls. It must be those calls that make ANALYZE slower. People report ANALYZE is certainly slower, and that is the only difference. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] Re: Postgres slowdown on large table joins
On Mon, Feb 19, 2001 at 08:34:47PM -0600, Larry Rosenman wrote: > * Dave Edmondson <[EMAIL PROTECTED]> [010219 14:40]: > > > > yes. I ran VACUUM ANALYZE after creating the indicies. (Actually, I VACUUM > > > > the database twice a day.) The data table literally has 145972 rows, and > > > > 145971 will match conf_id 4... > > > > > > Hm. In that case the seqscan on data looks pretty reasonable ... not > > > sure if you can improve on this much, except by restructuring the tables. > > > How many rows does the query actually produce, anyway? It might be that > > > most of the time is going into sorting and delivering the result rows. > > > > All I'm really trying to get is the latest row with a conf_id of 4... I'm > > not sure if there's an easier way to do this, but it seems a bit ridiculous > > to read in almost 146000 rows to return 1. :( > > is there a timestamp or date/time tuple in the row? If so, index > THAT. > > LER actually, just did that yesterday... now that I finally understand incides. Thanks anyway. -- David Edmondson <[EMAIL PROTECTED]> GMU/FA d-(--) s+: a18>? C$ UB$ P+>+ L- E--- W++ N- o K-> w-- O? M-(--) V? PS+ PE+ Y? PGP t 5 X R+ tv-->! b DI+++ D+ G(--) e>* h!>+ r++ y+>++ ICQ: 79043921 AIM: AbsintheXL #music,#hellven on irc.esper.net
Re: [GENERAL] Re: A How-To: PostgreSQL from Tcl via ODBC
Bill Barnes <[EMAIL PROTECTED]> writes: > Thanks. That cleared the bison problem. > flex didn't work the same way though. Copied it also > to /home/billb/pgsql. Reported missing. Needs to go > someplace else? Hmm, should work the same: configure will find it if it's in your PATH. regards, tom lane
[GENERAL] strategies for keeping an audit trail of UPDATEs
Hello, In our app we must keep a trace of all changes (UPDATEs) done to an important_table, so that it's possible to get a snapshot of a given record at a given date. The implementation strategy we are thinking about: 1. create an important_table_archive which inherits from important_table, 2. create a trigger ON UPDATE of important_table which automatically creates a record in important_table_archive containing only the UPDATEd fields on the original record along with the modification date and author and the primary key, Is this a viable strategy for that kind of requirement? Is there a better, more orthodox one? Thanks in advance, -- PANOPE: Déjà même Hippolyte est tout prêt à partir ; Et l'on craint, s'il paraît dans ce nouvel orage, Qu'il n'entraîne après lui tout un peuple volage. (Phèdre, J-B Racine, acte 1, scène 4)
Re: [GENERAL] Re: A How-To: PostgreSQL from Tcl via ODBC
Thanks. That cleared the bison problem. flex didn't work the same way though. Copied it also to /home/billb/pgsql. Reported missing. Needs to go someplace else? TIA Bill --- Tom Lane <[EMAIL PROTECTED]> wrote: > Bill Barnes <[EMAIL PROTECTED]> writes: > > Ran into a hitch at 'make' which reported that > 'bison' > > was not installed. I'm running debian potato, so > used > > the apt-get install of bison. Bison is installed > in > > /usr/bin. I copied it to /home/billb/pgsql. > > > Still getting the 'bison missing' message. > > Re-run configure, and watch to make sure that it > finds bison this time. > You'll need flex too, if you intend to build from > CVS sources. > > regards, tom lane __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
Re: [GENERAL] vacuum analyze again...
Bruce Momjian <[EMAIL PROTECTED]> writes: >> How's reading a sufficiently large fraction of random rows going to be >> significantly faster than reading all rows? If you're just going to read >> the first n rows then that isn't really random, is it? > Ingres did this too, I thought. You could specify a certain number of > random rows, perhaps 10%. On a large table, that is often good enough > and much faster. Often 2% is enough. Peter's got a good point though. Even 2% is going to mean fetching most or all of the blocks in the table, for typical-size rows. Furthermore, fetching (say) every second or third block is likely to be actually slower than a straight sequential read, because you're now fighting the kernel's readahead policy instead of working with it. To get a partial VACUUM ANALYZE that was actually usefully faster than the current code, I think you'd have to read just a few percent of the blocks, which means much less than a few percent of the rows ... unless maybe you picked selected blocks but then used all the rows in those blocks ... but is that a random sample? It's debatable. I find it hard to believe that VAC ANALYZE is all that much slower than plain VACUUM anyway; fixing the indexes is the slowest part of VACUUM in my experience. It would be useful to know exactly what the columns are in a table where VAC ANALYZE is considered unusably slow. regards, tom lane
Re: [GENERAL] Problems when dumping a database
Tressens Lionel <[EMAIL PROTECTED]> writes: > My pgsql DBMS works great except that when I want to dump a database, > pg_dump says that database template1 doesn't exist (actually it does !) > and the dump is aborted... Curious. Can you connect to template1 by hand (eg "psql template1")? If not, try issuing "vacuum pg_database" (you can do this from any database not only template1) to see if it helps. If that doesn't fix it, we'll need to see the exact output from pg_dump, as well as what shows up in the postmaster log. regards, tom lane
Re: [GENERAL] vacuum analyze again...
Bruce Momjian <[EMAIL PROTECTED]> writes: > No, we have no ability to randomly pick rows to use for estimating > statistics. Should we have this ability? That would be really slick, especially given the fact that VACUUM runs much faster than VACUUM ANALYZE for a lot of PG users. I could change my daily maintenance scripts to do a VACUUM of everything, followed by a VACUUM ANALYZE of the small tables, followed by a VACUUM ANALYZE ESTIMATE (or whatever) of the large tables. Even cooler would be the ability to set a table size threshold, so that VACUUM ANALYZE would automatically choose the appropriate method based on the table size. Chris -- [EMAIL PROTECTED] - Chris JonesSRI International, Inc. www.sri.com
Re: [GENERAL] vacuum analyze again...
> Bruce Momjian writes: > > > No, we have no ability to randomly pick rows to use for estimating > > statistics. Should we have this ability? > > How's reading a sufficiently large fraction of random rows going to be > significantly faster than reading all rows? If you're just going to read > the first n rows then that isn't really random, is it? Ingres did this too, I thought. You could specify a certain number of random rows, perhaps 10%. On a large table, that is often good enough and much faster. Often 2% is enough. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[HACKERS] Re: [GENERAL] Re: A How-To: PostgreSQL from Tcl via ODBC
> Ran into a hitch at 'make' which reported that 'bison' > was not installed. I'm running debian potato, so used > the apt-get install of bison. Bison is installed in > /usr/bin. I copied it to /home/billb/pgsql. > > Still getting the 'bison missing' message. You need to remove config.cache before reconfiguring. Here's a hint for all who are getting PostgreSQL from CVS, are anyone else really: Run configure with --cache=/dev/null. There is never a reason why you would need that cache, and there is an infinite number of reasons why you don't want it. It's going to save you a lot of head aches. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [GENERAL] strategies for keeping an audit trail of UPDATEs
What you describe is what we do. Full history of all actions in the data tables are stored elsewhere via a trigger on INSERT, UPDATE / DELETE and a generic function written in C (to get the transaction ID they were a part of for postdated rollbacks or transactions where applicable -- unmodified since). -- Rod Taylor There are always four sides to every story: your side, their side, the truth, and what really happened. - Original Message - From: "Louis-David Mitterrand" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, February 20, 2001 12:27 PM Subject: [GENERAL] strategies for keeping an audit trail of UPDATEs > Hello, > > In our app we must keep a trace of all changes (UPDATEs) done to an > important_table, so that it's possible to get a snapshot of a given > record at a given date. > > The implementation strategy we are thinking about: > > 1. create an important_table_archive which inherits from > important_table, > > 2. create a trigger ON UPDATE of important_table which automatically > creates a record in important_table_archive containing only the UPDATEd > fields on the original record along with the modification date and > author and the primary key, > > Is this a viable strategy for that kind of requirement? Is there a > better, more orthodox one? > > Thanks in advance, > > -- > PANOPE: Déjà même Hippolyte est tout prêt à partir ; > Et l'on craint, s'il paraît dans ce nouvel orage, > Qu'il n'entraîne après lui tout un peuple volage. > (Phèdre, J-B Racine, acte 1, scène 4) >
[GENERAL] Problems when dumping a database
Hi all, My pgsql DBMS works great except that when I want to dump a database, pg_dump says that database template1 doesn't exist (actually it does !) and the dump is aborted... Any ideas ??? Thanks a lot Lionel
Re: [GENERAL] vacuum analyze again...
Bruce Momjian writes: > No, we have no ability to randomly pick rows to use for estimating > statistics. Should we have this ability? How's reading a sufficiently large fraction of random rows going to be significantly faster than reading all rows? If you're just going to read the first n rows then that isn't really random, is it? -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [GENERAL] Re: A How-To: PostgreSQL from Tcl via ODBC
Bill Barnes <[EMAIL PROTECTED]> writes: > Ran into a hitch at 'make' which reported that 'bison' > was not installed. I'm running debian potato, so used > the apt-get install of bison. Bison is installed in > /usr/bin. I copied it to /home/billb/pgsql. > Still getting the 'bison missing' message. Re-run configure, and watch to make sure that it finds bison this time. You'll need flex too, if you intend to build from CVS sources. regards, tom lane
Re: [GENERAL] vacuum analyze again...
> Bruce Momjian <[EMAIL PROTECTED]> writes: > > > No, we have no ability to randomly pick rows to use for estimating > > statistics. Should we have this ability? > > That would be really slick, especially given the fact that VACUUM runs > much faster than VACUUM ANALYZE for a lot of PG users. I could change > my daily maintenance scripts to do a VACUUM of everything, followed by > a VACUUM ANALYZE of the small tables, followed by a VACUUM ANALYZE > ESTIMATE (or whatever) of the large tables. > > Even cooler would be the ability to set a table size threshold, so > that VACUUM ANALYZE would automatically choose the appropriate method > based on the table size. Added to TODO: * Allow ANALYZE to process a certain random precentage of rows -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] number of pgsql childrens
From: "Emmanuel Pierre" <[EMAIL PROTECTED]> > on my DB server I ave 245 "idle" postmasters process for 130 httpd > persistant DB connections alive. Are you using persistant connections from Apache/PHP? If so, you are probably getting at least one connection per Apache child process. > can anyone give me a clue how to manage this number of unused/idles > children and how to have them quickerly deallocated ? If this is Apache, you'll need to make sure the Apache processes die off quicker. Perhaps look at MaxSpareServers. - Richard Huxton
Re: [GENERAL] Installing DBI client
If you are going to install DBD::Pg you need lib and include directories just to install the module On Tue, Feb 20, 2001 at 04:29:34PM +0100, Jose Manuel Lorenzo Lopez wrote: > Hello PG's, > > I have a question concerning the DBI module for postgresql. > > I want to use the DBI interface for accessing a remote postgresql DB. > There is no postgresql installed on the machine I want to use the DBI > (client), but of course on the DB machine. > > Which files am I supposed to copy onto the client machine from the > DB machine, to install and use the DBI interface on the client? > > Thanks a lot in advance for any suggestion! > > Best Regards / Un saludo / Mit freundlichen Grüßen / Cordiali Saluti > > José Manuel Lorenzo López > > -- > ** > ** José Manuel Lorenzo López** > ** ** > ** ICA Informationssysteme Consulting & Anwendungsgesellschaft mbH ** > ** Dept. SAP Basis R/3 VBue** > ** ** > ** e-mail to: [EMAIL PROTECTED]** > **
Re: [GENERAL] postgres load
From: "Emmanuel Pierre" <[EMAIL PROTECTED]> > I republish my question for I had no answer, and this is a serious > problem to me... I've used explain, vacuum, indexes... and so on, few > nested requests... Doesn't appear to be on the list. > > > I am running PGSql 7.0.3 over Linux 2/ELF with a ReiserFS > filesystem, > Bi-P3 800 and 2Gb of RAM. > > My database jump from 8 in load to 32 without any real reason > nor too > much requests. There _will_ be a reason - what is top telling you? > I already do vacuum even on the fly ifever that can decrease > load, but > nothing... Are you executing a particular query/set of queries when this happens? We'll need more information I'm afraid. - Richard Huxton
[GENERAL] -F and perl again
Hi,
in regards to my former question about using -F from perl, would the following
be the correct line to do it?
$dbh = DBI->connect("dbi:Pg:dbname=logs;options=-F");
--
Konstantin Agouros - NetAge Solutions, Dingolfinger Str. 6, 81673 Muenchen
Tel.: 089 666584-0, Fax: 089 666584-11, Email: [EMAIL PROTECTED]
--
Black holes are, where god divided by zero.
[GENERAL] unions on views (workaround?)
Hi, I am currently porting a database from MS Access to PostgreSQL. I have many views and occasionally some UNIONS among these VIEWS. I have to keep PostgreSQL 7.0.2 for the moment (that's what my ISP provides). So I need to know if anyone has any suggestions about how to simulate a union among two views with SQL (I don't want to put this inteligence into the application). TIA, Paulo Parola [EMAIL PROTECTED]
[GENERAL] Re: A How-To: PostgreSQL from Tcl via ODBC
Hooray! These instructions are just what an almost-novice needs. With the exception of changing the password to 'postgresql', the procedures started smoothly. Ran into a hitch at 'make' which reported that 'bison' was not installed. I'm running debian potato, so used the apt-get install of bison. Bison is installed in /usr/bin. I copied it to /home/billb/pgsql. Still getting the 'bison missing' message. Can anyone show me the error of my ways. TIA Bill --- Dan Lyke <[EMAIL PROTECTED]> wrote: > A friend asked me to figure out how to access > PostgreSQL from Tcl via > ODBC. For posterity, here's the step by step "how I > did it" that I > emailed to him. I don't know Tcl, this was just > about getting the > compile options correct and doing the proper > sysadminning to make > things work. > > Comments, suggestions and clarifications > appreciated, hopefully this > will save the next person going through the pain a > few steps: > > http://www.flutterby.com/archives/2001_Feb/19_PostgreSQLfromTclwithODBC.html __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
Re: [GENERAL] win2000: problems starting postmaster
I ran into the same issue over the weekend. If you look in the pgsql-ports email archives or the cygwin email archives you will see that this is a known problem with cygwin 1.1.8. (I believe it is fixed in current sources for cygwin). The workaround is to install cygwin 1.1.7. That solved the problem for me. thanks, --Barry Peep Krusberg wrote: > hello! > > That's my very first day with postgres, so please be kind... > > - cygwin installed OK > - it seems that postgres compiled & installed OK > - initdb created db structures > - ipc-daemon started OK > - but postmaster -i fails with messages: > > c:\cygwin\usr\local\pgsql\bin\postgres.exe: *** > recreate_mmaps_after_fork_failed > Startup failed - abort > NOTICE: IpcMemoryDetach: shmdt(0x0x71e): Invalid argument > NOTICE: IpcMemoryDetach: shmdt(0x0x70b): Invalid argument > NOTICE: IpcMemoryDetach: shmdt(0x0x71a): Invalid argument > > I'm able to run and use postgres in the backend mode. > > Any help appreciated. > > Peep
Re: Re[2]: [GENERAL] Weird indices
Jean-Christophe Boggio <[EMAIL PROTECTED]> writes: > JS> I mean the explain shows that getting the count(*) from the field that > JS> is indexed has to do a seq scan, presumably to determine if the rows are > JS> in fact valid. > count(*) means you want all the rows that have all the fields "not > null". Read carefully : ALL THE FIELDS. No, actually it just means "count the rows". count(f) for a field f (or more generally any expression f) counts the number of non-null values of f, but "*" just indicates count the rows. Nonetheless, it's not that easy to keep a running count(*) total for a table, even if we thought that select count(*) with no WHERE clause was a sufficiently critical operation to justify slowing down every other operation to keep the count(*) stats up to date. Think about committed vs not-committed transactions. In the worst case, each active transaction could have a different view of the table and thus a different idea of what count(*) should yield; and each transaction might have different pending updates that should affect the count(*) total when and if it commits. > ahem. One solution to the problem is known as "optimizer hints" in > Oracle : you specify directly in the query HOW the optimizer should > execute the query. It's very useful in various situations. I have > asked Tom many times if that exists in PostgreSQL but didn't get any > answer. I guess it's on a TODO list somewhere ;-) Not on mine ;-). I don't believe in the idea, first because it's not standard SQL, and second because I don't trust the user to know better than the system what the best plan is in a particular context. Hints that you put in last year may have been the right thing at the time (or not...) but they'll still be lying there forgotten in your code when the table contents and the Postgres implementation have changed beyond recognition. Yes, the optimizer needs work, and it'll get that work over time --- but a hint that's wrong is worse than no hint. I'd rather have Postgres blamed for performance problems of its own making than those of the user's making. regards, tom lane
Re: [GENERAL] How do I change data type from text to bool?
There currently is no simple SQL command that accomplishes this. It can be accomplished by creating an identical new table with the bool data type change and then running a "INSERT INTO new_table (SELECT * FROM old_table)". Then you can check your results, drop the old table, and rename the new one. Brent --- Donald Braman <[EMAIL PROTECTED]> wrote: > I have a table/class filled with records/instances in which I > accidentally > set the fields/attributes data type to text rather than > boolean. I now have > 75,000 records with 't' and 'f' So now I want to change the > attributes to > bool? I can't find anything on changing data types in the > integrated docs (I > searched them, didn't read all of them). Did I miss it? Is > there an easy way > to do this? -Don > __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
[GENERAL] number of pgsql childrens
on my DB server I ave 245 "idle" postmasters process for 130 httpd persistant DB connections alive. can anyone give me a clue how to manage this number of unused/idles children and how to have them quickerly deallocated ? -- EDENJOB / APR-Job Email: [EMAIL PROTECTED]Home:www.apr-job.com Phone: +33 1 47 81 02 41 Tatoo: +33 6 57 60 42 17 Fax: +33 1 41 92 91 54 eFAX: +44 0870-122-6748 ** This message and any attachments (the "message") are confidential and intended solely for the addressees. Any unauthorised use or dissemination is prohibited. E-mails are susceptible to alteration. Neither EDENJOB/APR-JOB or affiliates shall be liable for the message if altered, changed or falsified. **
[GENERAL] postgres load
I republish my question for I had no answer, and this is a serious problem to me... I've used explain, vacuum, indexes... and so on, few nested requests... I am running PGSql 7.0.3 over Linux 2/ELF with a ReiserFS filesystem, Bi-P3 800 and 2Gb of RAM. My database jump from 8 in load to 32 without any real reason nor too much requests. I already do vacuum even on the fly ifever that can decrease load, but nothing... I've done many indexed also... Can someone help me ? Emmanuel -- EDENJOB / APR-Job Email: [EMAIL PROTECTED]Home:www.apr-job.com Phone: +33 1 47 81 02 41 Tatoo: +33 6 57 60 42 17 Fax: +33 1 41 92 91 54 eFAX: +44 0870-122-6748 ** This message and any attachments (the "message") are confidential and intended solely for the addressees. Any unauthorised use or dissemination is prohibited. E-mails are susceptible to alteration. Neither EDENJOB/APR-JOB or affiliates shall be liable for the message if altered, changed or falsified. **
[GENERAL] Re: binding postmaster to *one* virtual IP address
On Tuesday 20 February 2001 13:38, Thierry Besancon wrote: > Hello > > I'd like to run postmaster on a workstation with several IP > addresses. What I'd like is to have it bind to one and only one of > those IP addresses. > maybe you can block incoming connections to pgport on the other ip adresses with ipchains. hope it helps -- gianpaolo racca [EMAIL PROTECTED] http://www.preciso.net
Re: [GENERAL] max / min explain
Yes there is. You can find it in the TODO list under Performance -> Indexes (http://www.postgresql.org/docs/todo.html). It isn't slated for the 7.1 release however. Brent --- adb <[EMAIL PROTECTED]> wrote: > I've noticed that select max(the_primary_key) from some_table > does a table scan. Is there any plan to implement max/min > calculations > using index lookups if the appropriate index exists? > > Thanks, > > Alex. > __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
Re: [GENERAL] Row ID and auto-increment?
You can create an auto incrementing field with SERIAL. Take a look at the FAQ (http://www.postgresql.org/docs/faq-english.html#4.16.1). Brent --- Raymond Chui <[EMAIL PROTECTED]> wrote: > If I create a table like > > create table tablename ( > aNuminteger not null, > namevarchar(10) > ); > > If I do select * from tablename; > > q1. Is there such thing rowid similar to Oracle in PostgreSQL? > q2. How do I make aNum auto increment by 1? Need to write > a trigger? how to write that? > I want to enforce column aNum 0,1,2,.n. > I want to prevent data entry people input 0,1,4,5,8,...n. > Thank you very much in advance! > > > > > --Raymond > > > begin:vcard > n:Chui;Raymond > tel;fax:(301)713-0963 > tel;work:(301)713-0624 Ext. 168 > x-mozilla-html:TRUE > url:http://members.xoom.com/rchui/ > org:NWS, NOAA > version:2.1 > email;internet:[EMAIL PROTECTED] > title:SA, DBA > note:ICQ #: 16722494 > adr;quoted-printable:;;NOAA, NWS, Office of Hydrology, > OH=0D=0A1325 East-West Highway, Room 8112;Silver > Spring;MD;20910-3283;U.S.A. > x-mozilla-cpt:;-6384 > fn:Raymond Chui > end:vcard > __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
Re: [GENERAL] vacuum analyze again...
[ Charset ISO-8859-1 unsupported, converting... ] > Hi, > > In Oracle, there are 2 ways to do the equivalent of vacuum analyze : > > * analyze table xx compute statitics > * analyze table xx estimate statistics > > In the second form, you can tell on what percentage of the file you > will do your stats. This is useful to make a quick analyze on huge tables > that have a homogenous dispersion. > > Is there a way to "estimate" the statistics that vacuum analyze will > use instead of "computing" them ? No, we have no ability to randomly pick rows to use for estimating statistics. Should we have this ability? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
