Re: [HACKERS] Hot Standby status
Michael Glaesemann wrote: > Other commands are in all-caps. Any reason INSERT, UPDATE, DELETE, > TRUNCATE, CREATE, DROP, ALTER, and COMMENT are not? No. Thanks, I'll fix that. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] next CommitFest
On Sun, 8 Nov 2009, Robert Haas wrote: I would personally prefer not to be involved in the management of the next CommitFest. Having done all of the July CommitFest and a good chunk of the September CommitFest, I am feeling a bit burned out. I was just poking around on the Wiki, and it looks like the role of the CommitFest manager isn't very well documented yet. Since you've done all of them since introducing the new CF software, I'm not sure if anyone else even knows exactly what you've been doing. The transition over to that was so successful there isn't even a copy of the schedule for 8.5 on the Wiki itself. Could you find some time this week to rattle off an outline of the work involved? It's hard to decide whether to volunteer to help without having a better idea of what's required. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] total execution time as reported by auto_explain
Hi,
I was using auto_explain to log plans (with analyze option on) from
queries that take more than 500ms, something i found i little odd is
that the duration says 779ms but actual time says 269ms (almost 3
times lower), maybe some kind of instrumentation cleanup can explain
this?
2009-11-10 10:32:41 GMTLOG: duration: 779.938 ms plan:
Sort (cost=1943.99..1967.38 rows=9355 width=466) (actual
time=235.655..269.061 rows=4997 loops=1)
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
2009-11-10 10:32:41 GMTLOG: duration: 779.938 ms plan:
Sort (cost=1943.99..1967.38 rows=9355 width=466) (actual
time=235.655..269.061 rows=4997 loops=1)
Sort Key: (upper(ltrim((b.nombre)::text)))
Sort Method: quicksort Memory: 3000kB
-> Hash Left Join (cost=8.18..1326.95 rows=9355 width=466) (actual
time=0.516..161.361 rows=4997 loops=1)
Hash Cond: (CASE btrim((b.marca)::text) WHEN ''::text THEN
NULL::bpchar ELSE b.marca END = c.codigo)
-> Seq Scan on fcproduc b (cost=0.00..1130.73 rows=9355
width=216) (actual time=0.194..65.821 rows=4997 loops=1)
Filter: (upper((nombre)::text) ~~ '%1%'::text)
-> Hash (cost=8.16..8.16 rows=2 width=261) (actual
time=0.153..0.153 rows=1 loops=1)
-> Bitmap Heap Scan on dbtablas c (cost=3.27..8.16
rows=2 width=261) (actual time=0.125..0.129 rows=1 loops=1)
Recheck Cond: (tipo = 'MAR'::bpchar)
-> Bitmap Index Scan on dbtablas_pkey
(cost=0.00..3.27 rows=2 width=0) (actual time=0.074..0.074 rows=1 loops=1)
Index Cond: (tipo = 'MAR'::bpchar)
2009-11-10 10:32:41 GMTSTATEMENT: select
b.codigo,b.nombre,b.ubicacion,b.grupo,b.P_IVA,b.PRECIO,b.PRECIO_2,b.PRECIO_3,b.PRECIO_4,b.PRECIO_5,c.nombre
AS marca,b.codigo_a
FROM fcproduc b
LEFT OUTER JOIN dbtablas c ON c.codigo = case TRIM(b.marca) when
'' then null else b.marca end and c.tipo = 'MAR'
WHERE UPPER(b.NOMBRE) LIKE UPPER('%1%') order by UPPER(LTRIM(b.nombre))
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Patch committers
Robert Haas wrote: > The next CommitFest is scheduled to start in a week. So far, it > doesn't look too bad, though a lot could change between now and then. > > I would personally prefer not to be involved in the management of the > next CommitFest. Having done all of the July CommitFest and a good > chunk of the September CommitFest, I am feeling a bit burned out. This brings up a concern I have --- that the number of patch committers/managers is decreasing while our patch volume is increasing. Consider that Heikki is working mostly on Hot Standby and Streaming Replication, Alvaro isn't as involved in applying patches, Neil Conway isn't involved with Postgres anymore, I am in a 42-day travel period, and Robert Haas is feeling burnt-out --- that is not a pretty sight. Much of the patch burden is falling on Tom. Now, Tom isn't complaining, but I am concerned about placing too much of a burden on him. I know we are growing new patch reviewers who will eventually be able to review _and_ apply patches on their own, but getting them to that point is going to take time. I have no real answers, but I am concerned. I have talked to many of you privately about this to get your input. On a brighter note, I was able to read three weeks of back-logged emails (3k emails) in the past two days because our community is now very good at resolving the greatly increased email volume of bug reports and questions. I can definitely say that the quality and volume of our email support to users had advanced significantly in the past year. It seems that a similar increase in patch application support is going to take longer to materialize. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parsing config files in a directory
On 11/10/09 5:59 AM, Bruce Momjian wrote: > Simon Riggs wrote: >> All of this *also* applies to shared_preload_libraries. We also need to >> be able to specify new load libraries without editing the same darn >> parameter. > > And to search_path, though that's even more complex because the order of > the entries is significant. Let's NOT start that discussion again. --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Deadlock on the same object?
I encountered the following log in 8.4.1 and HEAD. The deadlock occured on the same object (relation 17498 of database 17497). Is it reasonable? ERROR: deadlock detected DETAIL: Process 6313 waits for ExclusiveLock on relation 17498 of database 17497; blocked by process 6312. Process 6312 waits for ExclusiveLock on relation 17498 of database 17497; blocked by process 6313. Process 6313: SELECT test() Process 6312: SELECT test() HINT: See server log for query details. CONTEXT: SQL function "test" statement 1 STATEMENT: SELECT test() (relation 17498 is table 'a') A reproducible test set is: CREATE TABLE a (i integer PRIMARY KEY); CREATE TABLE b (i integer REFERENCES a(i)); CREATE FUNCTION test() RETURNS VOID AS $$ LOCK a IN EXCLUSIVE MODE; LOCK b IN EXCLUSIVE MODE; DELETE FROM a; $$ LANGUAGE sql STRICT; # Repeat the following commands in shell. psql -c "SELECT test()" & psql -c "SELECT test()" & psql -c "SELECT test()" & wait Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] PostgreSQL 8.3.8 on AIX5.3 : compilation failed
"Albe Laurenz" writes: > The problem is that both _LARGE_FILES and _LARGE_FILE_API are #defined > in this case, which makes #include fail. > Does anyone have an idea how to best fix this problem in the > source tree? I'm willing to implement and test. I've committed changes for this in CVS, please verify it fixes your problem. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] PostgreSQL 8.3.8 on AIX5.3 : compilation failed
Alvaro Herrera writes: > Tom Lane wrote: >> In most of the other cases the #include is done in an associated .y >> file, but there is none in psql. Anyone have a thought which of >> psql's .c files would be the most appropriate host? > mainloop.c? Yeah, that's about the same conclusion I had come to --- that's the principal caller so it's the closest analog to what we do elsewhere. Will fix. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] PostgreSQL 8.3.8 on AIX5.3 : compilation failed
Tom Lane wrote: > In most of the other cases the #include is done in an associated .y > file, but there is none in psql. Anyone have a thought which of > psql's .c files would be the most appropriate host? mainloop.c? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby status
On Nov 10, 2009, at 13:36 , Heikki Linnakangas wrote: Attached is the latest and greatest patch against CVS head, taken from the hs-riggs branch in my git repository. Awesome. Thank you, Simon and Heikki! I skimmed through the documentation to get a better handle on what this will mean. + + These actions produce error messages + + + + + DML - Insert, Update, Delete, COPY FROM, Truncate. + Note that there are no actions that result in a trigger + being executed during recovery. + + + + + DDL - Create, Drop, Alter, Comment (even for temporary tables because + currently these cause writes to catalog tables) + + Other commands are in all-caps. Any reason INSERT, UPDATE, DELETE, TRUNCATE, CREATE, DROP, ALTER, and COMMENT are not? Cheers, Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby status
On Tue, 2009-11-10 at 20:36 +0200, Heikki Linnakangas wrote: > Attached is the latest and greatest patch against CVS head, taken from > the hs-riggs branch in my git repository. Is there a reason why recovery.conf.sample does not include (sample) entries for recovery_connections and max_standby_delay? Regards, -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [HACKERS] "ERROR: could not read block 6 ...: read only 0 of 8192 bytes" after autovacuum cancelled
2009/11/10 Alvaro Herrera : > Pavel Stehule escribió: > >> Hello >> >> do you have a idea abou lazy vacuum lockinkg problem? >> >> any plans? > > Well, I understand the issue and we have an idea on how to attack it, > but I have no concrete plans to fix it ATM ... ok Pavel > > -- > Alvaro Herrera http://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] "ERROR: could not read block 6 ...: read only 0 of 8192 bytes" after autovacuum cancelled
Pavel Stehule escribió: > Hello > > do you have a idea abou lazy vacuum lockinkg problem? > > any plans? Well, I understand the issue and we have an idea on how to attack it, but I have no concrete plans to fix it ATM ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] "ERROR: could not read block 6 ...: read only 0 of 8192 bytes" after autovacuum cancelled
2009/11/10 Alvaro Herrera : > Tom Lane wrote: >> Alvaro Herrera writes: >> >> Looks OK, but please update the comment right before the >> >> RecordTransactionCommit, along the lines of "We prevent cancel >> >> interrupts after this point to mitigate the problem that you >> >> can't abort the transaction now". >> >> > BTW I'm thinking in backpatching this all the way back to 7.4 -- are >> > we agreed on that? >> >> Yeah, I would think the problems can manifest all the way back. > > Done, thanks for the discussion. Hello do you have a idea abou lazy vacuum lockinkg problem? any plans? Regards Pavel Stehule > > -- > Alvaro Herrera http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] drop tablespace error: invalid argument
Stephen Tyler writes: > On Tue, Nov 10, 2009 at 8:57 PM, Jan Otto wrote: >>> Someone else please confirm? If so I'll revert that patch. >> >> Yes i can confirm that this bug is fixed in Mac OS X 10.6.2. I have checked >> it twice. >> With removed workaround in tablespace.c and with my self written testcase >> from >> september. > I can confirm that I am no longer able to trigger "ERROR: could not read > directory "pg_xlog": Invalid argument" in Mac OS X 10.6.2 with > "checkpoint_segments = 128". OK, I've reverted the hack in tablespace.c. This is good, I was not looking forward to providing our own implementation of readdir() :-( > PS: I am observing some kind of disk lock-up on my machine that I can't > explain (and is present on both 10.6.1 and 10.6.2). Huge operations (like > "VACUUM FULL on a 50GB table") appear to run in brief spikes of activity > interspersed with 30 second pauses when the disk appears to be both inactive > and somewhat unresponsive and CPU is idle. Perhaps fsync() is misbehaving > (I have an SSD Raid 0 array). Maybe ktrace and/or dtrace would shed a bit of light on what's happening there. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] "ERROR: could not read block 6 ...: read only 0 of 8192 bytes" after autovacuum cancelled
Tom Lane wrote: > Alvaro Herrera writes: > >> Looks OK, but please update the comment right before the > >> RecordTransactionCommit, along the lines of "We prevent cancel > >> interrupts after this point to mitigate the problem that you > >> can't abort the transaction now". > > > BTW I'm thinking in backpatching this all the way back to 7.4 -- are > > we agreed on that? > > Yeah, I would think the problems can manifest all the way back. Done, thanks for the discussion. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parsing config files in a directory
> I would not complain if the server logged duplicates. (That would > actually be a minor blessing, as the startup would log all our > deviations from standard -- at least if our standard had an explicit > entry.) I agree with Kevin here: duplicate logging +1. Not starting on duplicates: -10^32 --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tightening binary receive functions
FYI, Heikki has fixed this bug and the fix will appear in Postgres 8.5. --- Andrew Gierth wrote: > > "Heikki" == Heikki Linnakangas > > writes: > > Heikki> Oops, you're right. The check is indeed confusing julian day > Heikki> numbers, with epoch at 23th of Nov 4714 BC, with > Heikki> postgres-reckoning day numbers, with epoch at 1th of Jan > Heikki> 2000. Thanks, will fix. > > Which reminds me: why isn't there an extract(jday from ...) function > or similar? > > -- > Andrew. > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] PostgreSQL 8.3.8 on AIX5.3 : compilation failed
"Albe Laurenz" writes: >> Why the "OBJECT_MODE" exported to 32 is not sufficient ? > I dug around a little, and the problem is in psqlscan.c which is generated > from psqlscan.l by flex. > The problem is that both _LARGE_FILES and _LARGE_FILE_API are #defined > in this case, which makes #include fail. Yeah. We solved this and other problems years ago by decreeing that we would never try to compile flex output files on their own; they'd always be #include'd into some other source file so that pg_config.h would get read first. psqlscan.l seems to have escaped that dictum somehow, but I guess we'd better make it fall into line. In most of the other cases the #include is done in an associated .y file, but there is none in psql. Anyone have a thought which of psql's .c files would be the most appropriate host? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Query::targetList and RETURNING
Marko Tiikkaja writes: > Tom Lane wrote: >> if (query->returningList) >> cteList = query->returningList; >> else >> cteList = query->targetList; > My initial thought is that this won't work because there might not be a > RETURNING clause, but I'm not sure. Hm, would we allow DML without RETURNING in CTEs? I'm not sure I see the point of that. But in any case, the other way is fine. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Query::targetList and RETURNING
Tom Lane wrote: Marko Tiikkaja writes: I wouldn't care for this at all, but with things the way they are right now, the writeable CTE patch has to do quite a few of these: [ shrug... ] How many is "quite a few"? In a quick search for existing references to targetList in the planner, it looked to me like the majority were places that wouldn't be relevant for writable CTEs anyway. For example, none of the references in allpaths.c are, because they have to do with deciding whether quals can be pushed down into the subquery. And the answer to that, for a non-SELECT CTE, is always "no". It appears we have four of those at the moment (hmm.. I thought there were more). Just a thought ... where you do need this, would it be better to phrase it as if (query->returningList) cteList = query->returningList; else cteList = query->targetList; ? I'm not sure myself, but it's something to consider. My initial thought is that this won't work because there might not be a RETURNING clause, but I'm not sure. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Query::targetList and RETURNING
Marko Tiikkaja writes: > I wouldn't care for this at all, but with things the way they are right > now, the writeable CTE patch has to do quite a few of these: [ shrug... ] How many is "quite a few"? In a quick search for existing references to targetList in the planner, it looked to me like the majority were places that wouldn't be relevant for writable CTEs anyway. For example, none of the references in allpaths.c are, because they have to do with deciding whether quals can be pushed down into the subquery. And the answer to that, for a non-SELECT CTE, is always "no". > if (query->cmdType != CMD_SELECT) > cteList = query->returningList; > else > cteList = query->targetList; Just a thought ... where you do need this, would it be better to phrase it as if (query->returningList) cteList = query->returningList; else cteList = query->targetList; ? I'm not sure myself, but it's something to consider. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Query::targetList and RETURNING
Tom Lane wrote: Marko Tiikkaja writes: Tom Lane wrote: This doesn't really seem like a good idea from here. You're changing a decision that has something like twenty years' standing in the code, for no real gain. AFAICS this is just going to move the special cases from point A to point B. Right, but this way you only have to special-case in grouping_planner(), and targetList always means the same thing. If you think that, it just means you have not found all the places you need to special-case ;-). One really obvious example is ruleutils.c, and I rather imagine there are multiple places in the parser and rewriter that would need attention, quite aside from whatever it does to the planner. Of course there were multiple places that needed attention, but those don't look like special-casing to me, they just have to make sure to do what they need to on the correct list of those two, which is what they did before. I wouldn't care for this at all, but with things the way they are right now, the writeable CTE patch has to do quite a few of these: List *cteList; if (query->cmdType != CMD_SELECT) cteList = query->returningList; else cteList = query->targetList; /* do something with cteList */ With this patch, they could use targetList directly without paying any attention to the type of the Query inside them. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Query::targetList and RETURNING
On Tue, Nov 10, 2009 at 2:56 PM, Marko Tiikkaja wrote: > (Sorry, forgot to CC the list) > > Tom Lane wrote: >> >> This doesn't really seem like a good idea from here. You're changing >> a decision that has something like twenty years' standing in the code, >> for no real gain. AFAICS this is just going to move the special cases >> from point A to point B. > > Right, but this way you only have to special-case in grouping_planner(), > and targetList always means the same thing. I haven't read through the patch but I can say the existing arrangement certainly seemed strange to me when I first saw it. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parsing config files in a directory
Bruce Momjian wrote: > A more radical approach would be for the server to refuse to start > if a setting is set in more than one file, and for the server to > report both locations. That would reduce the guesswork about > problems. -1 I see that as a big step backward. As mentioned earlier in the thread, we use an include at the end of postgresql.conf to point to a file with any overrides from our standard configuration. Without the ability to do that, managing 200 clusters would be harder. I would not complain if the server logged duplicates. (That would actually be a minor blessing, as the startup would log all our deviations from standard -- at least if our standard had an explicit entry.) -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Query::targetList and RETURNING
Marko Tiikkaja writes: > Tom Lane wrote: >> This doesn't really seem like a good idea from here. You're changing >> a decision that has something like twenty years' standing in the code, >> for no real gain. AFAICS this is just going to move the special cases >> from point A to point B. > Right, but this way you only have to special-case in grouping_planner(), > and targetList always means the same thing. If you think that, it just means you have not found all the places you need to special-case ;-). One really obvious example is ruleutils.c, and I rather imagine there are multiple places in the parser and rewriter that would need attention, quite aside from whatever it does to the planner. If there were a clear net benefit, I'd be for changing, but I think it's going to end up being roughly a wash. And if it's a wash we should not change it, because when you consider the follow-on costs (patches not back-patching, third-party code breaking, etc) that means we'd come out way behind. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] drop tablespace error: invalid argument
On Tue, Nov 10, 2009 at 8:57 PM, Jan Otto wrote: > > Someone else please confirm? If so I'll revert that patch. > > Yes i can confirm that this bug is fixed in Mac OS X 10.6.2. I have checked > it twice. > With removed workaround in tablespace.c and with my self written testcase > from > september. > I can confirm that I am no longer able to trigger "ERROR: could not read directory "pg_xlog": Invalid argument" in Mac OS X 10.6.2 with "checkpoint_segments = 128". I can also report that under 10.6.1, changing "checkpoint_segments = 128" to "checkpoint_segments = 64" made the pg_xlog errors disappear almost entirely. I could still easily trigger them with "VACUUM FULL", but could not trigger them on demand with regular db operations. Stephen PS: I am observing some kind of disk lock-up on my machine that I can't explain (and is present on both 10.6.1 and 10.6.2). Huge operations (like "VACUUM FULL on a 50GB table") appear to run in brief spikes of activity interspersed with 30 second pauses when the disk appears to be both inactive and somewhat unresponsive and CPU is idle. Perhaps fsync() is misbehaving (I have an SSD Raid 0 array). Anyway I am mentioning this as a caution that although I can detect no readdir() errors on Mac OS X 10.6.2, perhaps all is not OK on my system.
Re: [HACKERS] Parsing config files in a directory
On Tue, Nov 10, 2009 at 2:19 PM, Bruce Momjian wrote: > There was discussion of whether the directory files or postgresql.conf > file has precedence. If postgresql.conf has precedence, tools changing > values might not work, and if the directory has precendence, someone > changing postgresql.conf might have their changes ignored. The tools > can warn users if they think the change might be ignored (by checking > pg_settings), but someone modifying postgresql.conf can't get the same > warnings. I wonder if this is a good reason to give postgresql.conf > precedence (and have all postgresql.conf values commented-out by > default; no more initdb change in there). That was precisely my logic. Tools can check the "source" of the current value and provide an error if it overrides their new settings whereas the reverse doesn't work. I also think that in general manual human intervention should trump anything done behind the user's back. The systems that break this rule invariably end up driving me nuts. I think the concern about settings interfering is valid for things like general-purpose tuning tools. But we will have modules which will need some individual configuration too. And you could imagine having things like a SSLConfigurator which might not have much chance of conflicting with anything else. And in any case, like you pointed out, if you do run two general-purpose auto-tuners the second one could give a warning that it's overriding the first one or that its settings will be overridden based on the priority of the files. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Query::targetList and RETURNING
(Sorry, forgot to CC the list) Tom Lane wrote: This doesn't really seem like a good idea from here. You're changing a decision that has something like twenty years' standing in the code, for no real gain. AFAICS this is just going to move the special cases from point A to point B. Right, but this way you only have to special-case in grouping_planner(), and targetList always means the same thing. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parsing config files in a directory
On Tue, 2009-11-10 at 08:59 -0500, Bruce Momjian wrote: > Simon Riggs wrote: > > All of this *also* applies to shared_preload_libraries. We also need to > > be able to specify new load libraries without editing the same darn > > parameter. > > And to search_path, though that's even more complex because the order of > the entries is significant. Yes, good thought. It would be useful to have some way to express dependency there, rather than just sequence. search_path_add = 'my_module1, my_module2' search_path_depends = 'postgis' So that we can assemble a search_path with final sequence based upon the dependency tree, rather than risk a weird sequence. We might need to have a special keyword in the main search_path to show where additions go, e.g. search_path = '$user, $install, public, pg_catalog' This is beginning to sound like a very workable mechanism for plugins. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Query::targetList and RETURNING
Marko Tiikkaja writes: > While working on writeable CTEs, I noticed I have to special-case the > output of a Query node frequently because in INSERT/UPDATE/DELETE query > targetList is the target list which is used for modifying the result > relation and returningList is the output of that Query. However, this > is different from SELECT where targetList actually is the output of that > Query node. Attached is a patch which avoids this special-casing by > making Query's targetList always be the output target list. The target > list for the result relation is stored separately. The patch needs a > bit more work but I'll be glad to do it if people think this is useful. This doesn't really seem like a good idea from here. You're changing a decision that has something like twenty years' standing in the code, for no real gain. AFAICS this is just going to move the special cases from point A to point B. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] plpgsql EXECUTE will not set FOUND
David Fetter wrote: > On Mon, Nov 09, 2009 at 09:01:14PM -0500, Bruce Momjian wrote: > > > > I have applied the attached patch to document that FOUND is not affected > > by EXECUTE, while GET DIAGNOSTICS is. > > One minor nit here: > > > Index: doc/src/sgml/plpgsql.sgml > > === > > RCS file: /cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v > > retrieving revision 1.143 > > diff -c -c -r1.143 plpgsql.sgml > > *** doc/src/sgml/plpgsql.sgml 29 Sep 2009 20:05:29 - 1.143 > > --- doc/src/sgml/plpgsql.sgml 10 Nov 2009 01:55:33 - > > *** > > *** 1366,1372 > > > >FOUND is a local variable within each > >PL/pgSQL function; any changes to it > > ! affect only the current function. > > > > > > > > --- 1366,1374 > > > >FOUND is a local variable within each > >PL/pgSQL function; any changes to it > > ! affect only the current function. FOUND is not > > ! affected by EXECUTE, while GET > > ! DIAGNOSTICS is effected. > > The passive form of this phrase would be "is affected," instead of "is > effected," or (my preference) change to an active construction like: > > EXECUTE changes the output of GET > DIAGNOSTICS, but does not change the state of > FOUND. Thank you so much. I was struggling with the phrasing myself. Updated. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parsing config files in a directory
Josh Berkus wrote: > Kevin, > > > I'm talking about how the decision should be made as to which takes > > precedence. It's fine to document which one *was* chosen, but that > > doesn't eliminate the problem of conflicting settings making a mess. > > Someone else (Robert maybe?) gave an explicit example of how three > > files could have overlapping settings. Of course, *my* tool will name > > its configuration file "!.conf". > > Hey, if a DBA wants to do that, then it's fine with me. They can check > pg_settings afterwards to find out which was chosen. > > The precedence issues you (and Robert) are citing are no different from > what we have currently in a single file. I absolutely can't tell you > the number of hacked-up postgresql.conf files I've seen with the same > setting appearing in more than 3 places. And with the conf file being > over 1000 lines long, it's easy to miss that someone or some tool added > another instance of the variable at the bottom. > > Plus we already support includes of single files. Why is an include of > a directory controversial? If someone doesn't want to use it, they > don't have to. Sorry to be commenting late: I am glad we are looking at how other projects do their configuration, but I am concerned that we might be drawing conclusions from other projects that don't apply to us. For example, I assume the Apache directory configuration system works well because the module file values typically don't conflict with the values in other files. The Unix /etc/rc.d system works, but does that have many cases where an /etc/*.conf file sets a value that is overriden by a value in an /etc/rc.d file? I realize there are already problems in postgresql.conf where users put a value multiple places, but at least there is a clear ordering (top-to-bottom) so problems can be diagnosed. A top-level conf file and a subdirectory of conf files seems to make things extra-confusing. I realize the goal of having a config file that can be easily modified by tools and retaining the existing user-modified postgresql.conf file too. There was discussion of whether the directory files or postgresql.conf file has precedence. If postgresql.conf has precedence, tools changing values might not work, and if the directory has precendence, someone changing postgresql.conf might have their changes ignored. The tools can warn users if they think the change might be ignored (by checking pg_settings), but someone modifying postgresql.conf can't get the same warnings. I wonder if this is a good reason to give postgresql.conf precedence (and have all postgresql.conf values commented-out by default; no more initdb change in there). A more radical approach would be for the server to refuse to start if a setting is set in more than one file, and for the server to report both locations. That would reduce the guesswork about problems. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parsing config files in a directory
Simon Riggs wrote: > All of this *also* applies to shared_preload_libraries. We also need to > be able to specify new load libraries without editing the same darn > parameter. And to search_path, though that's even more complex because the order of the entries is significant. --- > > --- > > On Wed, 2009-10-28 at 22:00 +, Simon Riggs wrote: > > On Tue, 2009-10-27 at 00:38 -0400, Greg Smith wrote: > > > new feature > > > > One additional point that would be useful is a way to match up the usage > > of custom_variable_classes with this new style of .conf file processing. > > > > At the moment if you wish to add a custom variable class everybody needs > > to edit the *same* parameter. Finding which one to edit could be a > > little difficult with a whole directory to search in. > > > > I propose a new form of processing for that variable: each new parameter > > instance is added to last one, rather than replacing it. > > e.g. > > custom_variable_class = 'x' > > custom_variable_class = 'y' > > custom_variable_class = 'z' > > is equivalent to > > custom_variable_classes = 'x,y,z' > > > > That allows NewFeatureX to drop in a file called "newfeaturex.conf", > > which looks like this > > > > custom_variable_class = 'newfeaturex' > > newfeaturex.param1 = x > > newfeaturex.param2 = y > > newfeaturex.param3 = z > > > > This requires no editing of any other files, just a straight drop in. > > That will make it much easier to produce real installers/deinstallers > > for add-in modules. > > > -- > Simon Riggs www.2ndQuadrant.com > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] accent-insensitive
rgc.tech wrote: > Hello, I would suggest the inclusion an option to create the > database accent-insensitive, > without need to use functions that in some cases it's slow to return > the result. > > Example: > select name from table where name ilike 'joão' Yes, this is in the current development branch (8.5) in contrib/unaccent. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] PostgreSQL 8.3.8 on AIX5.3 : compilation failed
I CC: this to the hackers list as it seems to be an old problem that was never fixed. See at the bottom. Alexandra Roy wrote: > This is a summary of what I have noticed concerning > PostgreSQL 8.3.8 compilation on AIX 5.3 TL9. > > 1. To do a successful 64-bit build, specify and execute: > > > # export OBJECT_MODE="64" > # export CFLAGS="-maix64" > # export LDFLAGS="-maix64 -Wl,-bbigtoc" > # ./configure > # gmake > # gmake install I forgot to answer your question concerning the benefits of a 64-bit build: You will be able to use more memory. > 2. To do a successful 32-bit build, you must specify: > > > # export OBJECT_MODE="32" > > and use the "-disable-largefile" option with configure : > > > # ./configure -disable-largefile > > > If I use the "-disable-largefile" option, "configure" > indicates at the end: > > > configure: WARNING: option ignored: --enable-largefile > > > And then, the compilation is successful: > > > All of PostgreSQL successfully made. Ready to install. > > > Without the "-disable-largefile" option, the compilation is > still failing with this error: > > > In file included from psqlscan.c:2385: > /usr/include/unistd.h:171: error: conflicting types > for 'lseek64' > /usr/include/unistd.h:169: error: previous > declaration of 'lseek64' > was here > In file included from /usr/include/unistd.h:744, > from psqlscan.c:2385: > /usr/include/sys/lockf.h:64: error: conflicting > types for 'lockf64' > /usr/include/sys/lockf.h:62: error: previous declaration of > 'lockf64' was here > > > Does someone have an idea about this ? > Why the "-disable-largefile" option is necessary to do a > PostgreSQL 8.3.8 32-build ? > Why the "OBJECT_MODE" exported to 32 is not sufficient ? I dug around a little, and the problem is in psqlscan.c which is generated from psqlscan.l by flex. I found descriptions of the problem in the archives: http://archives.postgresql.org/pgsql-hackers/2002-09/msg01142.php http://archives.postgresql.org/pgsql-hackers/2002-09/msg01545.php The problem is that both _LARGE_FILES and _LARGE_FILE_API are #defined in this case, which makes #include fail. A possible workaround, suggested by Peter Eisentraut at the time, would be to add this line at the beginning of bin/psql/psqlscan.c: #include "postgres.h" This fixes the problem for me. Does anyone have an idea how to best fix this problem in the source tree? I'm willing to implement and test. Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Query::targetList and RETURNING
Hi,
While working on writeable CTEs, I noticed I have to special-case the
output of a Query node frequently because in INSERT/UPDATE/DELETE query
targetList is the target list which is used for modifying the result
relation and returningList is the output of that Query. However, this
is different from SELECT where targetList actually is the output of that
Query node. Attached is a patch which avoids this special-casing by
making Query's targetList always be the output target list. The target
list for the result relation is stored separately. The patch needs a
bit more work but I'll be glad to do it if people think this is useful.
Thoughts?
Regards,
Marko Tiikkaja
*** a/src/backend/executor/functions.c
--- b/src/backend/executor/functions.c
***
*** 1070,1078 check_sql_fn_retval(Oid func_id, Oid rettype, List
*queryTreeList,
(parse->commandType == CMD_INSERT ||
parse->commandType == CMD_UPDATE ||
parse->commandType == CMD_DELETE) &&
!parse->returningList)
{
! tlist = parse->returningList;
}
else
{
--- 1070,1078
(parse->commandType == CMD_INSERT ||
parse->commandType == CMD_UPDATE ||
parse->commandType == CMD_DELETE) &&
!parse->hasReturning)
{
! tlist = parse->targetList;
}
else
{
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
***
*** 2225,2235 _copyQuery(Query *from)
COPY_SCALAR_FIELD(hasDistinctOn);
COPY_SCALAR_FIELD(hasRecursive);
COPY_SCALAR_FIELD(hasForUpdate);
COPY_NODE_FIELD(cteList);
COPY_NODE_FIELD(rtable);
COPY_NODE_FIELD(jointree);
COPY_NODE_FIELD(targetList);
! COPY_NODE_FIELD(returningList);
COPY_NODE_FIELD(groupClause);
COPY_NODE_FIELD(havingQual);
COPY_NODE_FIELD(windowClause);
--- 2225,2236
COPY_SCALAR_FIELD(hasDistinctOn);
COPY_SCALAR_FIELD(hasRecursive);
COPY_SCALAR_FIELD(hasForUpdate);
+ COPY_SCALAR_FIELD(hasReturning);
COPY_NODE_FIELD(cteList);
COPY_NODE_FIELD(rtable);
COPY_NODE_FIELD(jointree);
COPY_NODE_FIELD(targetList);
! COPY_NODE_FIELD(resultTargetList);
COPY_NODE_FIELD(groupClause);
COPY_NODE_FIELD(havingQual);
COPY_NODE_FIELD(windowClause);
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
***
*** 861,871 _equalQuery(Query *a, Query *b)
COMPARE_SCALAR_FIELD(hasDistinctOn);
COMPARE_SCALAR_FIELD(hasRecursive);
COMPARE_SCALAR_FIELD(hasForUpdate);
COMPARE_NODE_FIELD(cteList);
COMPARE_NODE_FIELD(rtable);
COMPARE_NODE_FIELD(jointree);
COMPARE_NODE_FIELD(targetList);
! COMPARE_NODE_FIELD(returningList);
COMPARE_NODE_FIELD(groupClause);
COMPARE_NODE_FIELD(havingQual);
COMPARE_NODE_FIELD(windowClause);
--- 861,872
COMPARE_SCALAR_FIELD(hasDistinctOn);
COMPARE_SCALAR_FIELD(hasRecursive);
COMPARE_SCALAR_FIELD(hasForUpdate);
+ COMPARE_SCALAR_FIELD(hasReturning);
COMPARE_NODE_FIELD(cteList);
COMPARE_NODE_FIELD(rtable);
COMPARE_NODE_FIELD(jointree);
COMPARE_NODE_FIELD(targetList);
! COMPARE_NODE_FIELD(resultTargetList);
COMPARE_NODE_FIELD(groupClause);
COMPARE_NODE_FIELD(havingQual);
COMPARE_NODE_FIELD(windowClause);
*** a/src/backend/nodes/nodeFuncs.c
--- b/src/backend/nodes/nodeFuncs.c
***
*** 1392,1400 query_tree_walker(Query *query,
{
Assert(query != NULL && IsA(query, Query));
! if (walker((Node *) query->targetList, context))
return true;
! if (walker((Node *) query->returningList, context))
return true;
if (walker((Node *) query->jointree, context))
return true;
--- 1392,1400
{
Assert(query != NULL && IsA(query, Query));
! if (walker((Node *) query->resultTargetList, context))
return true;
! if (walker((Node *) query->targetList, context))
return true;
if (walker((Node *) query->jointree, context))
return true;
***
*** 2090,2097 query_tree_mutator(Query *query,
query = newquery;
}
MUTATE(query->targetList, query->targetList, List *);
- MUTATE(query->returningList, query->returningList, List *);
MUTATE(query->jointree, query->jointree, FromExpr *);
MUTATE(query->setOperations, query->setOperations, Node *);
MUTATE(query->havingQual, query->havingQual, Node *);
--- 2090,2097
query = newquery;
}
+ MUTATE(query->resultTargetList, que
[HACKERS] accent-insensitive
Hello, I would suggest the inclusion an option to create the database accent-insensitive, without need to use functions that in some cases it's slow to return the result. Example: select name from table where name ilike 'joão' Return: joão joao JOAO JOÃO Text by: Google translator Regards, Rafael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] drop tablespace error: invalid argument
>> My inclination is to continue assuming that the EINVAL is a new bug >> introduced in Snow Leopard. I sure hope they fix it in 10.6.2 though. >> If they don't, we may have to think about a workaround, messy as that >> will apparently be. > > 10.6.2 is out, and it appears to fix the bug --- if I remove the hack > in tablespace.c, we still pass regression tests. > > Someone else please confirm? If so I'll revert that patch. Yes i can confirm that this bug is fixed in Mac OS X 10.6.2. I have checked it twice. With removed workaround in tablespace.c and with my self written testcase from september. regards, jan otto -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
