Re: [HACKERS] [GENERAL] Concurrency problem building indexes
Wes [EMAIL PROTECTED] writes: Where do you want me to send it to? pgsql-bugs would be appropriate. Of course, since your hardware is different, it may not show up since it appears to be a timing thing.. I'm on a PB G4 1Ghz. My G4 is in the shop at the moment, but Apple promised it back by Friday. I kinda doubt it's *that* platform specific though. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Google SoC--Idea Request
Jim C. Nasby wrote: On Mon, Apr 24, 2006 at 11:05:18PM -0400, Tom Lane wrote: Jonah H. Harris [EMAIL PROTECTED] writes: While the student could do some benchmarking on relatively new hardware and make suggestions, I agree with Tom. Having to keep support for older platforms doesn't leave much flexibility to change the defaults. Another point here is that the defaults *are* reasonable for development and for small installations; the people who are complaining are the ones who expect to run terabyte databases without any tuning. (I exaggerate perhaps, but the point is valid.) We've talked more than once about offering multiple alternative starting-point postgresql.conf files to give people an idea of what to do for small/medium/large installations. MySQL have done that for years and it doesn't seem that users are unable to cope with the concept. But doing this is (a) mostly a matter of testing and documenting, not coding and (b) probably too small for a SoC project anyway. My recollection was that there was opposition to offering multiple config files, but that there was a proposal to make initdb smarter about picking configuration values. Personally, I agree that multiple config files would be fine. Or a really fancy solution would be feeding a config option to initdb and have it generate an appropriate postgresql.conf. We have already done some initdb tuning improvements for 8.2 - shared buffers now tops out at 4000 instead of 1000 and initdb now sets max_fsm_pages at a more realistic level. (top is 200,000 instead of previously hardcoded 20,000). I would have liked to increase max_connections too, but that would have caused problems on OSX, apparently. See previous discussion. Personally I would much rather see a tuning advisor tool in more general use than just provide small/medium/large config setting files. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Google SoC--Idea Request
Jonah H. Harris [EMAIL PROTECTED] writes: On 4/25/06, Andrew Dunstan [EMAIL PROTECTED] wrote: Personally I would much rather see a tuning advisor tool in more general use than just provide small/medium/large config setting files. True dat. One thing that has to be figured out before we can go far with this is the whole question of how much smarts initdb really ought to have. Since a lot of packagers think that initdb should be run non-interactively behind the scenes, the obvious solution of give initdb a --small/--medium/--large parameter does not work all that nicely. But on the other hand we can't just tell people to drop in replacement config files when the one in place contains initdb-created specifics, such as locale settings. Now that there's a provision for include directives in postgresql.conf, one way to address this would be to split the config info into multiple physical files, some containing purely performance-related settings while others consider functionality. But that seems more like a wart than a solution to me. I feel that we've pushed performance-tuning logic into initdb that probably ought not be there, and we ought to factor it out again. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Google SoC--Idea Request
Maybe you can develop a graphic interface just like Fedora Core setup interface which can choose packages installing, then the user can choose config file and then have a little change in parameters. - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Jonah H. Harris [EMAIL PROTECTED] Cc: Andrew Dunstan [EMAIL PROTECTED]; Jim C. Nasby [EMAIL PROTECTED]; John DeSoi [EMAIL PROTECTED]; Pgsql Hackers pgsql-hackers@postgresql.org Sent: Tuesday, April 25, 2006 2:16 PM Subject: Re: [HACKERS] Google SoC--Idea Request Jonah H. Harris [EMAIL PROTECTED] writes: On 4/25/06, Andrew Dunstan [EMAIL PROTECTED] wrote: Personally I would much rather see a tuning advisor tool in more general use than just provide small/medium/large config setting files. True dat. One thing that has to be figured out before we can go far with this is the whole question of how much smarts initdb really ought to have. Since a lot of packagers think that initdb should be run non-interactively behind the scenes, the obvious solution of give initdb a --small/--medium/--large parameter does not work all that nicely. But on the other hand we can't just tell people to drop in replacement config files when the one in place contains initdb-created specifics, such as locale settings. Now that there's a provision for include directives in postgresql.conf, one way to address this would be to split the config info into multiple physical files, some containing purely performance-related settings while others consider functionality. But that seems more like a wart than a solution to me. I feel that we've pushed performance-tuning logic into initdb that probably ought not be there, and we ought to factor it out again. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] Concurrency problem building indexes
On 4/25/06 1:01 AM, Tom Lane [EMAIL PROTECTED] wrote: Where do you want me to send it to? pgsql-bugs would be appropriate. It's not that small that I would want to post it to a list. Also, I think I've sanitized the data, but I wouldn't want to post it on a public list. Or are you just looking for the index build script? Of course, since your hardware is different, it may not show up since it appears to be a timing thing.. I'm on a PB G4 1Ghz. My G4 is in the shop at the moment, but Apple promised it back by Friday. I kinda doubt it's *that* platform specific though. You never know... CPU speed, hard drive speed (slow), etc. Wes ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] Concurrency problem building indexes
Wes [EMAIL PROTECTED] writes: On 4/25/06 1:01 AM, Tom Lane [EMAIL PROTECTED] wrote: Where do you want me to send it to? pgsql-bugs would be appropriate. It's not that small that I would want to post it to a list. Also, I think I've sanitized the data, but I wouldn't want to post it on a public list. So invent some made-up data. I'd be seriously surprised if this behavior has anything to do with the precise data being indexed. Experiment around till you've got something you don't mind posting that exhibits the behavior you see. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] TODO Item: ACL_CONNECT
Hi
On Mon, 2006-04-24 at 23:07 -0400, Alvaro Herrera wrote:
Gevik Babakhani wrote:
If one is going to revoke the last ACL_CONNECT, a warning is going to
issued then that part of the REVOKE gets canceled.
Humm, no, the WARNING is issued but the REVOKE is executed anyway.
I have tested this by applying the patch-0.4.diff of a new src tree.
[EMAIL PROTECTED] ~]$ createdb
CREATE DATABASE
[EMAIL PROTECTED] ~]$ psql
Welcome to psql 8.2devel, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
gevik=# revoke connection on database gevik from gevik;
REVOKE
gevik=# revoke connection on database gevik from public;
WARNING: The revoke statement or at least one part of it cannot be
completed on database gevik
DETAIL: At least one database connection privilege should be granted
for this database
REVOKE
gevik=# select datname,datacl from pg_catalog.pg_database;
datname | datacl
---+
postgres |
gevik | {=Tc/gevik,gevik=CT/gevik}
template1 | {=c/gevik,gevik=CTc/gevik}
template0 | {=c/gevik,gevik=CTc/gevik}
(4 rows)
gevik=#
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] TODO Item: ACL_CONNECT
On Mon, 2006-04-24 at 23:16 -0400, Tom Lane wrote: Why are we debating this? It won't get accepted anyway, because the whole thing is silly. Show me one other object type that we issue such warnings for, or anyone else who has even suggested that we should. So, I am very much confused. What do I do now. Do you mean the whole thing won't get accepted and I should stop developing the TODO item? or just strip the warning part. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Concurrency problem building indexes
[ shrug... ] Worksforme. There is a short interval at the end of the first CREATE INDEX on the table where the problem would happen if another CREATE INDEX tries to modify the pg_class row before the first one's committed. I did a pg_dumpall and removed the index creation commands. The first time I run the index build, I usually get at least one occurrence. I think that narrows it down nicely. You create the table, load rows, then without analyze create the indexes, thus pg_class is not up to date, and the update needs to be done. My answer to this would be to (have an option to) ommit this relpages and reltuples update. It is imho not the task of create index to update statistics in the first place. I have been burnt by that behavior when creating indexes on empty tables in Informix and never liked it (iirc pg has a workaround for empty tables though). Wes, you could most likely solve your immediate problem if you did an analyze before creating the indexes. Andreas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Checking assumptions
On Mon, Apr 24, 2006 at 11:11:59PM -0400, Bruce Momjian wrote: Are we OK with the Coverity reports now? Well, you can see for yourself: http://scan.coverity.com/ We're down from the near-300 to just 60. They've unfixed the ereport() issue but it was fixed for two days which allowed me to isolate then and mark the false positives. More than 50% of those remaining are in the ECPG code (primarily memory-leaks in error conditions which may or may not be real). The remaining are in the src/bin directory, where the issues are not that important. The only one remaining in the backend I consider important was the one relating to the failure to allocate a shared hash [1] which I posted earlier. We're now into the hard-slog part. For example, the fix to ecpg/ecpglib/execute.c yesterday fixes the old problems but creates new ones (nval leaked on last iteration of loop). I'm still trying to find a way to export info on the memory leaks so other people can look at them. Have a nice day, [1] http://archives.postgresql.org/pgsql-hackers/2006-04/msg00732.php -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
[HACKERS] Summary of coverity bugs
I managed to export some data out into a sort of useful format, primarily the memory leaks. It not as nice as having it with the source, but it should allow people to find the problems (or confirm that they're not problems at all). The line references are all to HEAD. I wanted to make a link to the cvsweb interface but my XSLT skills wern't up to that apparently. http://svana.org/kleptog/temp/pgsql-bin.xml http://svana.org/kleptog/temp/pgsql-ecpg.xml Your browser has to understand XML/XSLT which should be most recent browsers but I'm not totally sure. The first file is the stuff in the src/bin directory, the second in the src/interfaces/ecpg directory. It's a little hard to follow, but it can be done... Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Summary of coverity bugs
I wanted to make a link to the cvsweb interface but my XSLT skills wern't up to that apparently. The following XSL fragment will do the trick for the cvsweb link: trth align=leftFile/thtd xsl:element name=a xsl:attribute name=hrefhttp://developer.postgresql.org/cvsweb.cgi/xsl:value-of select=file//xsl:attribute xsl:value-of select=file/ /xsl:element /td/tr Cheers, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Google SoC--Idea Request
Personally I would much rather see a tuning advisor tool in more general use than just provide small/medium/large config setting files. True dat. Maybe the SoC project here is just such a tuning advisor tool? Something that can run pgbench repeatedly, try different settings, and compare results. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Google SoC--Idea Request
On 4/25/06, Bort, Paul [EMAIL PROTECTED] wrote: Maybe the SoC project here is just such a tuning advisor tool? Something that can run pgbench repeatedly, try different settings, and compare results. IIRC, that already exists. I think it was called pg_autotune or something similar. -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 1: 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] Protocol Message Graph
On Sun, 2006-04-23 at 18:43 -0400, Alvaro Herrera wrote: Agent M wrote: I have created a directed graph using graphviz that shows the message flow/event stream. Perhaps this will be helpful to someone. Of course, corrections are also welcome. Thats a good start. Maybe it would make more sense to have a graph of states, and the edges would indicate what messages are sent to change from one state to another. Yes, that would be better. It would be good to do the same for LWLock states, to help check for deadlock possibilities. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] TODO Item: ACL_CONNECT
Gevik Babakhani wrote: On Mon, 2006-04-24 at 23:16 -0400, Tom Lane wrote: Why are we debating this? It won't get accepted anyway, because the whole thing is silly. Show me one other object type that we issue such warnings for, or anyone else who has even suggested that we should. No other object type has the ability to require you to stop the server and start a standalone backend to fix the mistake, which is what makes this thing unique. So, I am very much confused. What do I do now. Do you mean the whole thing won't get accepted and I should stop developing the TODO item? or just strip the warning part. Tom is referring to the WARNING. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 1: 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] TODO Item: ACL_CONNECT
Gevik Babakhani wrote: Hi On Mon, 2006-04-24 at 23:07 -0400, Alvaro Herrera wrote: Gevik Babakhani wrote: If one is going to revoke the last ACL_CONNECT, a warning is going to issued then that part of the REVOKE gets canceled. Humm, no, the WARNING is issued but the REVOKE is executed anyway. I have tested this by applying the patch-0.4.diff of a new src tree. My point is that the behavior you describe is broken. The warning should be issued but the command should be executed anyway. That way the user executing it knows that he has locked everyone out of the database (but he can lock everyone out of the database if he wants to.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] TODO Item: ACL_CONNECT
* Alvaro Herrera ([EMAIL PROTECTED]) wrote: Gevik Babakhani wrote: On Mon, 2006-04-24 at 23:16 -0400, Tom Lane wrote: Why are we debating this? It won't get accepted anyway, because the whole thing is silly. Show me one other object type that we issue such warnings for, or anyone else who has even suggested that we should. No other object type has the ability to require you to stop the server and start a standalone backend to fix the mistake, which is what makes this thing unique. Eh? Isn't that the case if you manage to remove the superuser bit from everyone? Yet it's allowed, I'm not even sure there's a warning.. In any case, what we do there can serve as precedent. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] TODO Item: ACL_CONNECT
Stephen Frost wrote: * Alvaro Herrera ([EMAIL PROTECTED]) wrote: Gevik Babakhani wrote: On Mon, 2006-04-24 at 23:16 -0400, Tom Lane wrote: Why are we debating this? It won't get accepted anyway, because the whole thing is silly. Show me one other object type that we issue such warnings for, or anyone else who has even suggested that we should. No other object type has the ability to require you to stop the server and start a standalone backend to fix the mistake, which is what makes this thing unique. Eh? Isn't that the case if you manage to remove the superuser bit from everyone? Yet it's allowed, I'm not even sure there's a warning.. In any case, what we do there can serve as precedent. Hmm, true. Maybe we could raise a warning in that case as well :-) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Summary of coverity bugs
On Tue, Apr 25, 2006 at 02:09:26PM +0200, Csaba Nagy wrote: I wanted to make a link to the cvsweb interface but my XSLT skills wern't up to that apparently. The following XSL fragment will do the trick for the cvsweb link: Thanks, done. If I could select the revision by date, that'd be cool, but appears not to be possible. -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Google SoC--Idea Request
Tom Lane wrote: Jonah H. Harris [EMAIL PROTECTED] writes: On 4/25/06, Andrew Dunstan [EMAIL PROTECTED] wrote: Personally I would much rather see a tuning advisor tool in more general use than just provide small/medium/large config setting files. True dat. One thing that has to be figured out before we can go far with this is the whole question of how much smarts initdb really ought to have. Since a lot of packagers think that initdb should be run non-interactively behind the scenes, the obvious solution of give initdb a --small/--medium/--large parameter does not work all that nicely. But on the other hand we can't just tell people to drop in replacement config files when the one in place contains initdb-created specifics, such as locale settings. Now that there's a provision for include directives in postgresql.conf, one way to address this would be to split the config info into multiple physical files, some containing purely performance-related settings while others consider functionality. But that seems more like a wart than a solution to me. I feel that we've pushed performance-tuning logic into initdb that probably ought not be there, and we ought to factor it out again. Sounds good. I don't care what we do for 8.2, but we should do something. Or am I going to have to bring out my dancing elephant again? :-) http://www.janetskiles.com/ART/greeting/greet-ani/dancing-elephant.jpg -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] Concurrency problem building indexes
On 4/25/06 2:18 AM, Tom Lane [EMAIL PROTECTED] wrote: So invent some made-up data. I'd be seriously surprised if this behavior has anything to do with the precise data being indexed. Experiment around till you've got something you don't mind posting that exhibits the behavior you see. My initial attempts last night at duplicating it with a small result set were not successful. I'll see what I can do. On 4/25/06 3:25 AM, Zeugswetter Andreas DCP SD [EMAIL PROTECTED] wrote: Wes, you could most likely solve your immediate problem if you did an analyze before creating the indexes. I can try that. Is that going to be a reasonable thing to do when there's 100 million rows per table? I obviously want to minimize the number of sequential passes through the database. Wes ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Implementing RESET CONNECTION ...
OK, what would people like done with this patch? Our TODO list has: * -Add RESET CONNECTION command to reset all session state This would include resetting of all variables (RESET ALL), dropping of temporary tables, removing any NOTIFYs, cursors, open transactions, prepared queries, currval()s, etc. This could be used for connection pooling. We could also change RESET ALL to have this functionality. The difficult of this features is allowing RESET ALL to not affect changes made by the interface driver for its internal use. One idea is for this to be a protocol-only feature. Another approach is to notify the protocol when a RESET CONNECTION command is used. This patch does everything except reset currval(), but the big missing item is that it doesn't handle the protocol issues outlined in the TODO item. However, there also has been very little discussion on exactly how the protocol stuff would work. Should we add it for 8.2 and see if we get any problem reports? --- I have backed out the patch until there is more discussion. I now see that the CC list had an incorrect entry for the patches list, so I am unsure if others have seen this patch thoroughly. --- Hans-J?rgen Sch?nig wrote: We have implemented a patch which can be used by connection pools for instance. RESECT CONNECTION cleans up a backend so that it can be reused. Temp tables, LISTEN / NOTIFY stuff, WITH HOLD cursors, open transactions, prepared statements and GUCs are cleaned up. I hope we have not missed important per-backend information. test=# BEGIN; BEGIN test=# RESET CONNECTION; RESET test=# COMMIT; WARNING: there is no transaction in progress COMMIT test=# PREPARE myplan(int, int) AS SELECT $1 + $2; PREPARE test=# RESET CONNECTION; RESET test=# EXECUTE myplan(1, 2); ERROR: prepared statement myplan does not exist test=# test=# DECLARE mycur CURSOR WITH HOLD FOR SELECT relname FROM pg_class; DECLARE CURSOR test=# FETCH NEXT FROM mycur; relname - views (1 row) test=# RESET CONNECTION; RESET test=# FETCH NEXT FROM mycur; ERROR: cursor mycur does not exist test=# CREATE TEMP TABLE mytmp (id int4); CREATE TABLE test=# RESET CONNECTION; RESET test=# INSERT INTO mytmp VALUES (10); ERROR: relation mytmp does not exist All regression tests passed. It would be nice if we had this in 8.1. Best regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/660/816 40 77 www.cybertec.at, www.postgresql.at -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Implementing RESET CONNECTION ...
To complete the connection pooling for multiple users, it would be great to have a protocol level option to change roles semi-permanently (to reduce permissions). RESET SESSION AUTHORIZATION would then bounce back to that (new, set) role until another protocol-level role rollback. This would allow completely reusable connections per database while maintaining a real sandbox for each connection. On Tue, April 25, 2006 10:19 am, Bruce Momjian wrote: OK, what would people like done with this patch? Our TODO list has: * -Add RESET CONNECTION command to reset all session state This would include resetting of all variables (RESET ALL), dropping of temporary tables, removing any NOTIFYs, cursors, open transactions, prepared queries, currval()s, etc. This could be used for connection pooling. We could also change RESET ALL to have this functionality. The difficult of this features is allowing RESET ALL to not affect changes made by the interface driver for its internal use. One idea is for this to be a protocol-only feature. Another approach is to notify the protocol when a RESET CONNECTION command is used. This patch does everything except reset currval(), but the big missing item is that it doesn't handle the protocol issues outlined in the TODO item. However, there also has been very little discussion on exactly how the protocol stuff would work. Should we add it for 8.2 and see if we get any problem reports? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [GENERAL] Concurrency problem building indexes
Wes, you could most likely solve your immediate problem if you did an analyze before creating the indexes. I can try that. Is that going to be a reasonable thing to do when there's 100 million rows per table? I obviously want to minimize the number of sequential passes through the database. No, I think it would only help if it gets the exact tuple count. For large tables it only gets an exact count with a full scan (use vacuum instead of analyze). Then again, when the table is large, the different create indexes should finish at sufficiently different times, so an analyze might be sufficient to fix the problem for small tables. (analyze is fast for large tables since it only does a sample) Andreas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] Concurrency problem building indexes
Zeugswetter Andreas DCP SD [EMAIL PROTECTED] writes: My answer to this would be to (have an option to) ommit this relpages and reltuples update. Wouldn't help, unless you want to get rid of relhasindex too. Wes, you could most likely solve your immediate problem if you did an analyze before creating the indexes. No, because that would install an approximate tuple count that the first CREATE INDEX would (most likely) still want to replace. The best I can gather from Wes' mail is that he's somehow getting a higher-than-chance probability that the first two CREATE INDEX commands finish at almost exactly the same time, and thus neither one of them sees the pg_class row as already updated with the correct values. I can't think of any effect in the PG code that would make CREATE INDEX commands tend to converge rather than diverge, so maybe there is some external effect here. Wes, is your machine a dual processor by any chance? Which OS X version exactly? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] FOR UPDATE lock problem ?
Hi all,
I had a lock problem on my database.
When I use a select for update request whitch uses an index, the
locking system is inconsistant.
Take this example:
test=# \d users
Table public.users
Column | Type |Modifiers
-+-+-
id_user | integer | not null default
nextval('users_id_user_seq'::regclass)
name| text|
Indexes:
users_pkey PRIMARY KEY, btree (id_user)
test=# \d sessions
Table public.sessions
Column |Type |Modifiers
+-+---
id_session | integer | not null default
nextval('sessions_id_session_seq'::regclass)
id_user| integer |
from_date | timestamp without time zone | default now()
to_date| timestamp without time zone |
Indexes:
sessions_pkey PRIMARY KEY, btree (id_session)
idx_session_null btree (id_session) WHERE to_date IS NULL
Foreign-key constraints:
sessions_id_user_fkey FOREIGN KEY (id_user) REFERENCES
users(id_user)
test =# INSERT INTO users (name) values ('bob');
test =# INSERT INTO users (name) values ('brad');
test =# INSERT INTO Sessions (id_user,to_date) select id_user,now() from
users;
x 200 times (for example)
test =# INSERT INTO Sessions (id_user) select id_user from users;
test =# ANALYSE Sessions;
test=# explain select s.id_session from users u, sessions s where
to_date IS NULL and u.id_user = s.id_user;
QUERY PLAN
-
Nested Loop (cost=0.00..6.85 rows=1 width=4)
- Index Scan using idx_session_null on sessions s (cost=0.00..1.01
rows=1 width=8)
- Index Scan using users_pkey on users u (cost=0.00..5.82 rows=1
width=4)
Index Cond: (u.id_user = outer.id_user)
(4 rows)
Then the problem with two backends:
bk1:
test=# begin;
test=# select s.id_session from users u, sessions s
where to_date IS NULL and u.id_user = s.id_user for update;
id_session
403
404
(2 rows)
bk2:
test=# begin;
test=# select s.id_session from users u, sessions s
where to_date IS NULL and u.id_user = s.id_user for update;
= ... Waiting
bk1:
test=# UPDATE sessions set to_date = now() where to_date is null;
UPDATE 2
test=# commit;
Then finaly on bk2:
id_session
403
404
(2 rows)
= But the rows were updated by the other backend so to_date field is
not null for these tuples...However these tuples are in the result
produced by the backend #2...
If I remove the idx_session_null index the problem disappears.
--
REYNAUD Jean-Samuel [EMAIL PROTECTED]
Elma
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Google SoC--Idea Request
On Tue, Apr 25, 2006 at 08:39:57AM -0400, Jonah H. Harris wrote: On 4/25/06, Bort, Paul [EMAIL PROTECTED] wrote: Maybe the SoC project here is just such a tuning advisor tool? Something that can run pgbench repeatedly, try different settings, and compare results. IIRC, that already exists. I think it was called pg_autotune or something similar. Last time I tried autotune I couldn't get it to work on FreeBSD, and it tuned a minimum of parameters. For example, it didn't touch checkpoint_segments, which is pretty essential to tune on a higher-end server. Not saying it wouldn't be a good place to start, but I also don't think it's a replacement for a built-in tuning tool. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] TODO Item: ACL_CONNECT
On Tue, 2006-04-25 at 08:53 -0400, Alvaro Herrera wrote: Gevik Babakhani wrote: My point is that the behavior you describe is broken. The warning should be issued but the command should be executed anyway. That way the user executing it knows that he has locked everyone out of the database (but he can lock everyone out of the database if he wants to.) This is no problem :) I will remove the cancellation part and change the warning. Could someone please provide a suitable warning message :) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] TODO Item: ACL_CONNECT
Alvaro Herrera [EMAIL PROTECTED] writes: No other object type has the ability to require you to stop the server and start a standalone backend to fix the mistake, which is what makes this thing unique. This one doesn't either. I already pointed out two reasons why not: 1. you can connect to a different database (eg template1 or postgres) and fix the problem from there. 2. the restriction won't be enforced against superusers anyway. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Implementing RESET CONNECTION ...
Bruce Momjian pgman@candle.pha.pa.us writes: Should we add it for 8.2 and see if we get any problem reports? No. I don't believe this can work without a far more invasive patch than this is. To point out just one problem, what of cached plans in plpgsql functions? Those can't be carried across a genuine connection reset (permissions and search path are two reasons why not). And the protocol issues are not something you can just ignore, because the command does break reasonable driver-level expectations. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] Concurrency problem building indexes
On 4/25/06 9:58 AM, Tom Lane [EMAIL PROTECTED] wrote: I can't think of any effect in the PG code that would make CREATE INDEX commands tend to converge rather than diverge, so maybe there is some external effect here. Wes, is your machine a dual processor by any chance? Which OS X version exactly? No, I'm running on a single processor powerbook. I currently am running OS X 10.4.5 on this system, 1GB memory. A while back, I saw a posting (I think from Tom) to the effect of index creation converging due to disk caching. It was along the lines of the slower index would be reading from disk data cached by the first index creation's read. When the faster creation went out to read from disk, the one reading from memory could catch up. Possible? Below is the (mostly psql) output from the creation script. Timing is enabled. It's a bit difficult to follow because of the multiprocessing. The index build script is perl, using popen to talk to psql. $|=1 is set on the command pipe to flush each command to the process. I can provide that if you want. If needed I could rewrite it using DBI to get easier to read logging. This is 6 tables, and I get three errors. The header table has 3 indexes, and the detail table has 4. Wes In buildIndex type=header date=20050904 which=1 939: header date- The number is the PID of the child process 939: header msgid \timing Timing is on. create index header_i_date_20050904 on header_20050904 (mdate) TABLESPACE indexes; header 1 forked 939 In buildIndex type=header date=20050904 which=2 942: header originator \timing Timing is on. create index header_i_originator_20050904 on header_20050904 (originator) TABLESPACE indexes; header 2 forked 942 Waiting for index builds to complete CREATE INDEX Time: 79.463 ms \q ERROR: tuple concurrently updated create index header_i_msgid_20050904 on header_20050904 (messageid) TABLESPACE indexes; 942 completed. CREATE INDEX Time: 7.125 ms \q 939 completed. header 1 forked 946 header 2 forked 947 Waiting for index builds to complete In buildIndex type=header date=20050905 which=1 In buildIndex type=header date=20050905 which=2 946: header date 946: header msgid 947: header originator \timing Timing is on. create index header_i_originator_20050905 on header_20050905 (originator) TABLESPACE indexes; \timing Timing is on. create index header_i_date_20050905 on header_20050905 (mdate) TABLESPACE indexes; CREATE INDEX Time: 444.957 ms create index header_i_msgid_20050905 on header_20050905 (messageid) TABLESPACE indexes; CREATE INDEX Time: 569.063 ms \q 947 completed. CREATE INDEX Time: 293.467 ms \q 946 completed. header 1 forked 953 In buildIndex type=header date=20050906 which=1 In buildIndex type=header date=20050906 which=2 header 2 forked 954 Waiting for index builds to complete 953: header date 953: header msgid 954: header originator \timing Timing is on. create index header_i_originator_20050906 on header_20050906 (originator) TABLESPACE indexes; \timing Timing is on. create index header_i_date_20050906 on header_20050906 (mdate) TABLESPACE indexes; CREATE INDEX Time: 1048.094 ms create index header_i_msgid_20050906 on header_20050906 (messageid) TABLESPACE indexes; ERROR: tuple concurrently updated \q 954 completed. CREATE INDEX Time: 566.794 ms \q 953 completed. In buildIndex type=detail date=20050904 which=1 960: detail originator 960: detail date \timing Timing is on. create index detail_i_originator_20050904 on detail_20050904 (originator) TABLESPACE indexes; detail 1 forked 960 In buildIndex type=detail date=20050904 which=2 963: detail recipient 963: detail msgid \timing Timing is on. create index detail_i_recipient_20050904 on detail_20050904 (recipient) TABLESPACE indexes; CREATE INDEX Time: 11.679 ms create index detail_i_msgid_20050904 on detail_20050904 (messageid) TABLESPACE indexes; CREATE INDEX Time: 4.952 ms \q detail 2 forked 963 Waiting for index builds to complete 963 completed. CREATE INDEX Time: 123.106 ms create index detail_i_date_20050904 on detail_20050904 (mdate) TABLESPACE indexes; CREATE INDEX Time: 5.724 ms \q 960 completed. In buildIndex type=detail date=20050905 which=1 967: detail originator 967: detail date \timing Timing is on. create index detail_i_originator_20050905 on detail_20050905 (originator) TABLESPACE indexes; detail 1 forked 967 In buildIndex type=detail date=20050905 which=2 971: detail recipient 971: detail msgid \timing Timing is on. create index detail_i_recipient_20050905 on detail_20050905 (recipient) TABLESPACE indexes; detail 2 forked 971 Waiting for index builds to complete CREATE INDEX Time: 1223.597 ms create index detail_i_date_20050905 on detail_20050905 (mdate) TABLESPACE indexes; ERROR: tuple concurrently updated create index detail_i_msgid_20050905 on detail_20050905 (messageid) TABLESPACE indexes; CREATE INDEX Time: 629.056
Re: [HACKERS] FOR UPDATE lock problem ?
REYNAUD Jean-Samuel [EMAIL PROTECTED] writes: test=# explain select s.id_session from users u, sessions s where to_date IS NULL and u.id_user = s.id_user; QUERY PLAN - Nested Loop (cost=0.00..6.85 rows=1 width=4) - Index Scan using idx_session_null on sessions s (cost=0.00..1.01 rows=1 width=8) - Index Scan using users_pkey on users u (cost=0.00..5.82 rows=1 width=4) Index Cond: (u.id_user = outer.id_user) (4 rows) If I remove the idx_session_null index the problem disappears. Interesting example. The planner is assuming that it need not explicitly check the to_date IS NULL condition as a plan filter condition since it is using a partial index, but apparently in the case of SELECT FOR UPDATE queries we need to check anyway so that EvalPlanQual will work properly. Or maybe partial-index predicates ought to be added to the EvalPlanQual mechanism. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] Concurrency problem building indexes
Wes [EMAIL PROTECTED] writes: A while back, I saw a posting (I think from Tom) to the effect of index creation converging due to disk caching. It was along the lines of the slower index would be reading from disk data cached by the first index creation's read. When the faster creation went out to read from disk, the one reading from memory could catch up. Possible? There would be some convergence effect while reading the table contents, but the subsequent sorting and index-writing would be competitive and ought to diverge again. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Installation on Duel-Core Pentium XP
Hi, My first posting for a few years, I hope this the right place. I am trying trace consistent errors with installation of PostgreSQL onto XP with Intel Duel-Core CPUs. The errors are strange, files missing, things like that. Have any members experienced difficult with this installation path? Thanks! Ben Clewett. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] Concurrency problem building indexes
I'm late to this thread, but maybe we can make the process of storing the new data in pg_class take a lock using LockObject() or something like that to serialize the access to the pg_class row. The idea would be that this lock doesn't conflict with a LockRelation(), but it would of course conflict with itself so no two CREATE INDEXES can enter that code section concurrently. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] FOR UPDATE lock problem ?
REYNAUD Jean-Samuel [EMAIL PROTECTED] writes:
When I use a select for update request whitch uses an index, the
locking system is inconsistant.
I've applied the attached patch to HEAD and 8.1 to fix this.
regards, tom lane
Index: createplan.c
===
RCS file: /cvsroot/pgsql/src/backend/optimizer/plan/createplan.c,v
retrieving revision 1.202.2.2
diff -c -r1.202.2.2 createplan.c
*** createplan.c29 Jan 2006 18:55:55 - 1.202.2.2
--- createplan.c25 Apr 2006 16:46:12 -
***
*** 816,823
* are not equal to, but are logically implied by, the index quals; so
we
* also try a predicate_implied_by() check to see if we can discard
quals
* that way. (predicate_implied_by assumes its first input contains
only
!* immutable functions, so we have to check that.) We can also
discard
!* quals that are implied by a partial index's predicate.
*
* While at it, we strip off the RestrictInfos to produce a list of
plain
* expressions.
--- 816,827
* are not equal to, but are logically implied by, the index quals; so
we
* also try a predicate_implied_by() check to see if we can discard
quals
* that way. (predicate_implied_by assumes its first input contains
only
!* immutable functions, so we have to check that.)
!*
!* We can also discard quals that are implied by a partial index's
!* predicate, but only in a plain SELECT; when scanning a target
relation
!* of UPDATE/DELETE/SELECT FOR UPDATE, we must leave such quals in the
!* plan so that they'll be properly rechecked by EvalPlanQual testing.
*
* While at it, we strip off the RestrictInfos to produce a list of
plain
* expressions.
***
*** 836,843
if (predicate_implied_by(clausel, nonlossy_indexquals))
continue;
! if (predicate_implied_by(clausel,
best_path-indexinfo-indpred))
! continue;
}
qpqual = lappend(qpqual, rinfo-clause);
}
--- 840,853
if (predicate_implied_by(clausel, nonlossy_indexquals))
continue;
! if (best_path-indexinfo-indpred)
! {
! if (baserelid != root-parse-resultRelation
! !list_member_int(root-parse-rowMarks,
baserelid))
! if (predicate_implied_by(clausel,
!
best_path-indexinfo-indpred))
! continue;
! }
}
qpqual = lappend(qpqual, rinfo-clause);
}
***
*** 920,927
* but are logically implied by, the index quals; so we also try a
* predicate_implied_by() check to see if we can discard quals that way.
* (predicate_implied_by assumes its first input contains only immutable
!* functions, so we have to check that.) We can also discard quals that
!* are implied by a partial index's predicate.
*
* XXX For the moment, we only consider partial index predicates in the
* simple single-index-scan case. Is it worth trying to be smart about
--- 930,941
* but are logically implied by, the index quals; so we also try a
* predicate_implied_by() check to see if we can discard quals that way.
* (predicate_implied_by assumes its first input contains only immutable
!* functions, so we have to check that.)
!*
!* We can also discard quals that are implied by a partial index's
!* predicate, but only in a plain SELECT; when scanning a target
relation
!* of UPDATE/DELETE/SELECT FOR UPDATE, we must leave such quals in the
!* plan so that they'll be properly rechecked by EvalPlanQual testing.
*
* XXX For the moment, we only consider partial index predicates in the
* simple single-index-scan case. Is it worth trying to be smart about
***
*** 945,952
{
IndexPath *ipath = (IndexPath *)
best_path-bitmapqual;
! if (predicate_implied_by(clausel,
ipath-indexinfo-indpred))
! continue;
}
}
qpqual = lappend(qpqual, clause);
--- 959,972
{
IndexPath *ipath = (IndexPath *)
best_path-bitmapqual;
! if (ipath-indexinfo-indpred)
Re: [HACKERS] [GENERAL] Concurrency problem building indexes
Alvaro Herrera [EMAIL PROTECTED] writes: I'm late to this thread, but maybe we can make the process of storing the new data in pg_class take a lock using LockObject() or something like that to serialize the access to the pg_class row. I'm inclined to think that the right solution is to fix UpdateStats and setRelhasindex so that they don't use simple_heap_update, but call heap_update directly and cope with HeapTupleUpdated (by looping around and trying the update from scratch). Another thing that's annoying here is that we update the pg_class row twice in some cases --- really we ought to try to get this down to one update. (So we'd only need one instance of the looping logic not two.) I'm not entirely clear on the cleanest way to do that, but am currently thinking that btbuild and friends ought to pass back the tuple counts they obtained, rather than writing them into the catalogs for themselves. IndexCloseAndUpdateStats ought to go away --- the index AM never had any business doing that for itself. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Catalog Access (was: [HACKERS] [GENERAL] Concurrency problem building indexes)
On Tue, Apr 25, 2006 at 12:48:04PM -0400, Alvaro Herrera wrote: I'm late to this thread, but maybe we can make the process of storing the new data in pg_class take a lock using LockObject() or something like that to serialize the access to the pg_class row. The idea would be that this lock doesn't conflict with a LockRelation(), but it would of course conflict with itself so no two CREATE INDEXES can enter that code section concurrently. Is there anything in comments/docs/list archives about why catalog access uses a bunch of 'magic' instead of treating catalog tables the same as every other table? I realize that ultimately you have to bootstrap somehow (kinda hard to read from pg_class if the info needed to do so is in pg_class), but perhaps switching over to the regular access methods after the system is up would be worth-while. Advantages: Allows for concurrent access (using MVCC) Potentially reduces locking requirements (if snapshots aren't required anymore, each backend should theoretically be able to rely on MVCC to get the right catalog info, though of course this depends on the actual operation) Should allow for much-sought-after triggers on the system catalogs But I'm sure this has come up in the past, I just can't find any info about why not to do this... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] ISBN/ISSN/ISMN/EAN13 module
I worked on this ISBN/ISSN/ISMN/EAN13 module about more than a year ago, and I was wondering if it could be made official, I honestly think it's better than the ISBN/ISSN currently included in the official release; plus mine would also probably support UPC codes and it already support the new ISBN13 codes. Check my old post: "New ISBN/ISSN/ISMN/EAN13 module." at http://archives.postgresql.org/pgsql-hackers/2004-11/msg00153.php In that post I explain what the module does... I was also describingsome problems I had, but the module it's working now. Please, share your thoughts. Kronuz. Express yourself instantly with MSN Messenger! MSN Messenger
Re: Catalog Access (was: [HACKERS] [GENERAL] Concurrency problem building indexes)
On Tue, Apr 25, 2006 at 12:25:35PM -0500, Jim C. Nasby wrote: Is there anything in comments/docs/list archives about why catalog access uses a bunch of 'magic' instead of treating catalog tables the same as every other table? I realize that ultimately you have to bootstrap somehow (kinda hard to read from pg_class if the info needed to do so is in pg_class), but perhaps switching over to the regular access methods after the system is up would be worth-while. I don't know if it's explicitly documented, but here's one mail that describes some of the issues: http://www.thelinuxreview.com/pgsql-hackers/2005-03/msg00840.php I think the basic problem is that DDL can't really work within a transaction. If I do an ALTER TABLE, some of these changes need to show up to concurrent transactions (maybe creating a unique index?). I think it's like Tom says in that email, it could be done, but the cost/benefit ratio isn't very good... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
[HACKERS] Avoiding redundant fetches of btree index metapages
Some experimentation over the weekend turned up the result that $SUBJECT is a good idea. I had never thought about this much before, figuring that in a heavily-used index the metapage would stay in cache anyway and so fetching it at the start of each index search isn't costing any extra I/O. That's true, but what it does cost is bufmgr contention, and in fact more than you'd expect because the amount of work actually done before releasing the page again is minuscule. (See off-list discussion attached below.) I'm working on cleaning up the patch shown below to make it really usable. The thoughts I have are: * Re-using rd_targblock was OK for a quick hack because we don't use it for indexes, but it's too klugy for real use, and it's not quite enough info anyway (we really ought to cache the root level as well as root block number). I'm planning to add a void * pointer to the Relation struct that the index AM is allowed to use as it sees fit, with the understanding that any pointed-to data lives in rd_indexcxt and the pointer will be reset to NULL on any relcache clear. btree would store a copy of the BTMetaPageData struct. The other AMs might have some other use for this. * The tricky part of caching this data is that we might try to use a very stale (many transactions old) root pointer, if a relcache entry sits unused for a long time. Btree's internal vacuuming protocol ensures that we won't delete a page that any active transaction is in flight to, but that logic won't protect a backend that tries to use an old cached pointer. So we could arrive at a page that is deleted, or has been deleted and then recycled to be a valid page (but not the one we want) or worst case ReadBuffer fails entirely because the pointer points past the current physical index end. Deleted isn't a problem because we can recognize such a page and fall back to fetching the metapage, but the other cases are nastier. What I'm considering doing to fix that is require any change to a btree's metapage data to send out a relcache inval message for the index. That will force all backends to flush the stale cache item not later than the start of their next transaction, and thereby guarantee that they aren't using pointers that are too old to be safe against vacuuming. (There are other ways we could handle this, but that one seems like the simplest and least invasive.) Comments? Anyone see any flaws in the reasoning? regards, tom lane --- Forwarded Messages Date:Sun, 23 Apr 2006 13:30:48 -0400 From:Tom Lane [EMAIL PROTECTED] To: Gavin Hamill [EMAIL PROTECTED] cc: Simon Riggs [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: [HACKERS] Further reduction of bufmgr lock contention I'm still not feeling very good, so I'll pass on trying to do anything with partitioning the BufMappingLock right now --- Simon, if you feel like having a go at it, be my guest. I did think of something simple to try, though, after noting that close to a quarter of the ReadBuffer traffic in Gavin's test comes from fetching btree index metapages. Apparently the indexes are not too large and mostly have just one level below the root, so the typical index lookup sequence is metapage - root - leaf - heap. It occurs to me that we could probably cache the root-page location and thereby avoid fetching the metapage at all in most cases. This won't save any actual I/O, since the metapage would've stayed in cache anyway, but what it does save is trips to the buffer manager. Since bufmgr contention is exactly our problem here, I thought that a 25% reduction in accesses might lead to better-than-25% improvement. An extremely quick and dirty patch for this is attached; it's not nearly ready for prime time, but it's enough to check performance, and what I see is 8.1.3: Concurrency Level: 4 Time taken for tests: 149.467 seconds Complete requests: 200 Failed requests:0 Broken pipe errors: 0 Total transferred: 58600 bytes HTML transferred: 0 bytes Requests per second:1.34 [#/sec] (mean) Time per request: 2989.34 [ms] (mean) Time per request: 747.34 [ms] (mean, across all concurrent requests) Transfer rate: 0.39 [Kbytes/sec] received Connnection Times (ms) min mean[+/-sd] median max Connect:0 00.0 0 0 Processing: 868 2962 2163.3 2540 26458 Waiting: 868 2961 2163.3 2540 26457 Total:868 2962 2163.3 2540 26458 Percentage of the requests served within a certain time (ms) 50% 2540 66% 3017 75% 3409 80% 3556 90% 4567 95% 5406 98% 8357 99% 9569 100% 26458 (last request) With cache-the-rootpage-location hack: Concurrency Level: 4 Time taken for tests: 99.063 seconds Complete requests: 200 Failed requests:0 Broken pipe errors: 0 Total transferred: 58600 bytes HTML transferred: 0 bytes Requests per second:2.02 [#/sec]
Re: [HACKERS] Avoiding redundant fetches of btree index metapages
Tom Lane wrote: Some experimentation over the weekend turned up the result that $SUBJECT is a good idea. I had never thought about this much before, figuring that in a heavily-used index the metapage would stay in cache anyway and so fetching it at the start of each index search isn't costing any extra I/O. That's true, but what it does cost is bufmgr contention, and in fact more than you'd expect because the amount of work actually done before releasing the page again is minuscule. (See off-list discussion attached below.) Wow, this is extremely nice. Congratulations on another well-spotted performance problem solved. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: Catalog Access (was: [HACKERS] [GENERAL] Concurrency problem building indexes)
Martijn van Oosterhout kleptog@svana.org writes: I think the basic problem is that DDL can't really work within a transaction. If I do an ALTER TABLE, some of these changes need to show up to concurrent transactions (maybe creating a unique index?). The point is that DDL can't be MVCC. If for instance you add an index to a table, once you commit every other transaction must *immediately* start updating that index when they modify the table. They can't use the excuse of not my problem because the catalog change postdates the snapshot I'm using. The drop-index case is even worse, since a transaction that believes the index is still present is likely to try to access/update a disk file that's not there anymore. Adding/dropping columns, constraints, triggers, etc all have hazards of the same ilk. I think it's like Tom says in that email, it could be done, but the cost/benefit ratio isn't very good... It's barely possible that we could make this happen, but it would be a huge amount of work, and probably a huge amount of instability for a very long time until we'd gotten all the corner cases sorted. I think there are much more productive uses for our development effort. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Installation on Duel-Core Pentium XP
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ben Clewett Sent: 25 April 2006 16:54 To: pgsql-hackers@postgresql.org Subject: [HACKERS] Installation on Duel-Core Pentium XP Hi, My first posting for a few years, I hope this the right place. I am trying trace consistent errors with installation of PostgreSQL onto XP with Intel Duel-Core CPUs. The errors are strange, files missing, things like that. Have any members experienced difficult with this installation path? I've been working on a core duo on XP for a month now with no problems. It's only a dev installation though, with naff all load. Regards, Dave. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Catalog Access
Martijn van Oosterhout wrote: On Tue, Apr 25, 2006 at 12:25:35PM -0500, Jim C. Nasby wrote: Is there anything in comments/docs/list archives about why catalog access uses a bunch of 'magic' instead of treating catalog tables the same as every other table? I realize that ultimately you have to bootstrap somehow (kinda hard to read from pg_class if the info needed to do so is in pg_class), but perhaps switching over to the regular access methods after the system is up would be worth-while. I don't know if it's explicitly documented, but here's one mail that describes some of the issues: http://www.thelinuxreview.com/pgsql-hackers/2005-03/msg00840.php O.k. I need to fix that... that should be an archives link ;0 http://archives.postgresql.org/pgsql-hackers/2005-03/msg00840.php Martin how did you find that link with the linuxreview domain ? Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Catalog Access
On Tue, Apr 25, 2006 at 11:57:13AM -0700, Joshua D. Drake wrote: Martijn van Oosterhout wrote: http://www.thelinuxreview.com/pgsql-hackers/2005-03/msg00840.php O.k. I need to fix that... that should be an archives link ;0 http://archives.postgresql.org/pgsql-hackers/2005-03/msg00840.php Martin how did you find that link with the linuxreview domain ? Google. It looked the same so I didn't pay attention to the fact the site was different. Oops :) Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Catalog Access
Martijn van Oosterhout wrote: On Tue, Apr 25, 2006 at 11:57:13AM -0700, Joshua D. Drake wrote: Martijn van Oosterhout wrote: http://www.thelinuxreview.com/pgsql-hackers/2005-03/msg00840.php O.k. I need to fix that... that should be an archives link ;0 http://archives.postgresql.org/pgsql-hackers/2005-03/msg00840.php Martin how did you find that link with the linuxreview domain ? Google. It looked the same so I didn't pay attention to the fact the site was different. Oops :) It is just a straggling domain... I just didn't want any confusion. :) Joshua D. Drake Have a nice day, -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] ACL_CONNECT patch 6
I have changed the revoke part of the patch. This time no restrictions,
only a warning message about a possible lockout.
http://www.xs4all.nl/~gevik/patch/patch-0.6.diff
Tom, Alvaro,
Do you think this will be accepted?
--
[EMAIL PROTECTED] ~]$ createdb
CREATE DATABASE
[EMAIL PROTECTED] ~]$ psql
Welcome to psql 8.2devel, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
gevik=# select datname,datacl from pg_catalog.pg_database;
datname | datacl
---+
postgres |
gevik |
template1 | {=c/gevik,gevik=CTc/gevik}
template0 | {=c/gevik,gevik=CTc/gevik}
(4 rows)
gevik=# revoke connection on database gevik from public;
REVOKE
gevik=# revoke connection on database gevik from gevik;
WARNING: This revoke statement removes the last database connection
privilege from database gevik.
DETAIL: Unless you have superuser privileges you could be locked out
from connecting to this database.
REVOKE
gevik=# select datname,datacl from pg_catalog.pg_database;
datname | datacl
---+
postgres |
template1 | {=c/gevik,gevik=CTc/gevik}
template0 | {=c/gevik,gevik=CTc/gevik}
gevik | {=T/gevik,gevik=CT/gevik}
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: Catalog Access (was: [HACKERS] [GENERAL] Concurrency problem
Ühel kenal päeval, T, 2006-04-25 kell 13:58, kirjutas Tom Lane: Martijn van Oosterhout kleptog@svana.org writes: I think the basic problem is that DDL can't really work within a transaction. If I do an ALTER TABLE, some of these changes need to show up to concurrent transactions (maybe creating a unique index?). The point is that DDL can't be MVCC. If for instance you add an index to a table, once you commit every other transaction must *immediately* start updating that index when they modify the table. How is it done in a way that all other backends see it, but only after commit ? Is there some secret transaction isolation mode for DDL? Maybe something that fits between read uncommitted and read committed ? Or is it just that catalog access is always done in read-committed mode, even if transaction is in serializable ? Would this take effect even inside a single command ? in other words, if it were possible that an index appeared in middle of a big update, would the tuples updated after the index becomes visible be also added to the index ? The reason I ask, is that I'm still keen on implementin a CONCURRENT INDEX command, and I want to get as much background info as possible before diving in. They can't use the excuse of not my problem because the catalog change postdates the snapshot I'm using. The drop-index case is even worse, since a transaction that believes the index is still present is likely to try to access/update a disk file that's not there anymore. Adding/dropping columns, constraints, triggers, etc all have hazards of the same ilk. at what point will an add/drop column become visible for parallel transactions ? can trigger/constraint appear magically inside a transaction ? so if I update 5 rows inside one serialisable trx, is it possible that a trigger added to the table after 2nd update will fire for last 3 updates ? btw, i don't think that a stored procedure (cached plans) will pick up something like added/changed default even after commit in both connections. I think it's like Tom says in that email, it could be done, but the cost/benefit ratio isn't very good... It's barely possible that we could make this happen, but it would be a huge amount of work, and probably a huge amount of instability for a very long time until we'd gotten all the corner cases sorted. I think there are much more productive uses for our development effort. True. --- Hannu ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: Catalog Access (was: [HACKERS] [GENERAL] Concurrency problem
Hannu Krosing wrote: Ühel kenal päeval, T, 2006-04-25 kell 13:58, kirjutas Tom Lane: Martijn van Oosterhout kleptog@svana.org writes: I think the basic problem is that DDL can't really work within a transaction. If I do an ALTER TABLE, some of these changes need to show up to concurrent transactions (maybe creating a unique index?). The point is that DDL can't be MVCC. If for instance you add an index to a table, once you commit every other transaction must *immediately* start updating that index when they modify the table. How is it done in a way that all other backends see it, but only after commit ? Is there some secret transaction isolation mode for DDL? Sort of. Catalog accesses normally use SnapshotNow, instead of ActiveSnapshot which is normally used by regular access. The use of ActiveSnapshot is what makes a transaction read committed or serializable; in a serializable transaction, ActiveSnapshot will point to SerializableSnapshot, while on a read committed transaction, ActiveSnapshot will point to a snapshot acquired at the beggining of the command by GetSnapshotData. Have a look at GetTransactionSnapshot() in tqual.c. (The trick is grokking the differences among the various HeapTupleSatisfies routines.) Would this take effect even inside a single command ? in other words, if it were possible that an index appeared in middle of a big update, would the tuples updated after the index becomes visible be also added to the index ? This can't happen, because an UPDATE to a table requires acquiring a lock (RowExclusiveLock) which conflicts with a lock taken by the CREATE INDEX (ShareLock). You can see the conflict table in lock.c, the LockConflicts array. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] ACL_CONNECT p.tch 6
Resend: my ISP reinstalled a stupid filter in their SMTP server. Gevik Babakhani wrote: I have changed the revoke part of the patch. This time no restrictions, only a warning message about a possible lockout. http://www.xs4all.nl/~gevik/patch/patch-0.6.diff Nah, after seeing Tom's comments it's clear that not even the warning is needed. I think patch 4 was OK already, not sure. Do you think this will be accepted? Certainly. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: Catalog Access (was: [HACKERS] [GENERAL] Concurrency problem
Alvaro Herrera [EMAIL PROTECTED] writes: Hannu Krosing wrote: Would this take effect even inside a single command ? in other words, if it were possible that an index appeared in middle of a big update, would the tuples updated after the index becomes visible be also added to the index ? This can't happen, because an UPDATE to a table requires acquiring a lock (RowExclusiveLock) which conflicts with a lock taken by the CREATE INDEX (ShareLock). Right. By and large, schema-changing operations take AccessExclusive lock on the table they are changing, which guarantees (a) no concurrent operation is is touching the table, and (b) by the time the lock is released, the schema-changing command is already committed and so its catalog changes appear valid to any subsequent transactions touching the table, since they look at the catalogs with SnapshotNow rules. CREATE INDEX is the only schema-changing op that I can think of offhand that takes a non-exclusive lock, and so its little problem with two concurrent operations on the same table is unique. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Installation on Duel-Core Pentium XP
Ben Clewett wrote: Hi, My first posting for a few years, I hope this the right place. I am trying trace consistent errors with installation of PostgreSQL onto XP with Intel Duel-Core CPUs. The errors are strange, files missing, things like that. Have any members experienced difficult with this installation path? You need to give us much more information: . how are you installing, from source compile or binary package? . which version? . what exactly are the errors? files missing, things like that is not the right way to report a problem cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
