Re: [HACKERS] Autotuning Group Commit
On Sat, 2005-01-22 at 00:18 -0600, Jim C. Nasby wrote: 1) I'm in favor of autotuning anything possible. 2) In addition to turning group_commit on and off, what about also adjusting the commit delay, based on statistics of recent commits? It might require a slightly larger sample set (maybe the last 100 commits), but it seems it would provide more flexibility (hence more usefulness) to the autotuning. I belive you'd want to first calculate the elapsed time between each commit in the sample set, then look for groupings of elapsed time. If you have a set that looks like this: Time (ms) Number 2 * 4 * 6 8 ** 10 * 12 ** 14 16 ** 18 20 * then you'd want a delay of 16ms. I think this calculation could be done fairly quickly by grouping the commits into buckets of different elapsed times, then look for the largest elapsed time that has a number of commits greater than the mean number of commits for all the buckets. But I'm not a statistician, hopefully someone here is. :) Yes, I considered that, but since we're talking about a frequently executed piece of code, I was hoping to keep it short and sweet. What do others think? The other issue is the likely time granularity for many OS will be 10ms anyway, so you have a choice of 0, 10, 20, 30ms... Overall, group commit isn't much use until the log disk is getting very busy. Delays don't really need to be more than a disk rotation, so even a laptop can manage 11ms between sequential writes. I'd suggest hardcoding commit_delay at 10ms, but we still need an on/off switch, so it seems sensible to keep it. We may be in a better position to use fine grained settings in the future. -- Best Regards, Simon Riggs ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Extending System Views: proposal for 8.1/8.2
Jim C. Nasby wrote: On another naming note; the naming convention for system stuff has always driven me nuts. Some the letter prefix (ie: tab for tables) in front of every field name, with no underscores or anything. Extensive use of abbreviations that you need to remember (ie: indnatts, indexprs, indpred). No use of underscores (indisunique). Yet the view and table names do use underscores. I agree the naming conventions for system catalog columns is less than optimal, but it seems a net loss to rename columns that already exist (given the amount of code that would need to be updated, both within the tree and in admin utilities and the like). Renaming all the system catalogs and providing backward compatibility views would mean you'd only need to modify the PG source, although of course those modifications would be pretty time-consuming... In any case, that's no reason not to try for better names in newly-added system objects. -Neil ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Extending System Views: proposal for 8.1/8.2
On Fri, 21 Jan 2005 12:17:08 -0800, Josh Berkus wrote: I'm proposing to expand both the coverage and number of system views. Generally: Nice. But have you considered if the INFORMATION_SCHEMA could be used? Unfortunately, the INFORMATION_SCHEMA currently has a major problem in its usefulness in PostgreSQL: http://troels.arvin.dk/db/rdbms/#cli-list_of_tables-postgresql-gotchas However, that could (and in my opinion: _should_) be fixed be enforcing schema-wide constraint identifier uniqueness. pg_stats ADD statstarget for each column (the SET STATISTICS for each column) This reminds me: It would be nice if it were somehow possible to determine when (if ever) statistics have been gathered for a given schema object. This needs changes to more than VIEWs, though. pg_columns -- new view ** schemaname tablename columnname datatype typemodifiers (NOT NULL, default, etc) comment Do you propose that typemodifiers be one column? - If would prefer if it were several columns. And it would be useful if it were easy to determine if a column is - solely - or part of - a uniqueness constraint - solely - or part of - a foreign key (pointing where?) - if it is subject to a (set of) CHECK constraints I could use this to more easily build user interfaces (forms). -- Greetings from Troels Arvin, Copenhagen, Denmark ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] %2$, %1$ gettext placeholder replacement is not working under Win32
Greetings, for a couple of days I have been hacking on src/port/snprintf.c. With Magnus' help I have managed to implement argument replacement in snprintf(). The code is very crude and not quite optimised, any suggestions will be more than welcome. Here is what I did: 1. I renamed snprintf() to pg_snprintf(), vsnprintf() to pg_vsnprintf() and introduced pg_printf() that calls pg_vsnprintf(). 2. After running configure I manually added snprintf.o to src/Makefile.global's LIBOBJ declaration and -lpgport to Makefile.shlib's DLLWRAP declaration 3. To make sure these functions are used everywhere I introduced the following lines at the end of src/include/c.h: #define snprintf pg_snprintf #define vsnprintf pg_vsnprintf #define printf pg_printf 4. I introduced a volatile static char[] variable in snprintf.c code so I can grep executables for this string and be sure that it is included. 5. Before running regression test I always ran make install, apparently because libpq is read from /usr/local/. During compilation the following warnings were reported: ../../../src/include/utils/elog.h:121: warning: `pg_printf' is an unrecognized format function type which is perfectly fine because we replace printf with pg_printf and gcc's format() does know anything about it. On Linux, PostgreSQL passed regression tests with flying colours and prints messages with %n$ just fine. On win32: int8, timestamp, timestamptz, abstime, horology, constraints, vacuum, and many others failed. To check my code, I reverted snprintf.c to the original one from CVS and forced win32 port to use these functions and it fails in same places. After examining regression tests diff I came to conclusion that problem is in fmtnum() function when it operates with particularly long integers. In snprintf() file I changed only and only dopr() function, neve touching fmtnum(), fmtstr() or fmtfolat(). I would like to kindly ask these questions: 1. Am I on the right to implement %n$ ? Can it be accepted? 2. Why would we not just take FreeBSD's vfprintf() and use it instead? 3. What is wrong with fmtnum() on Win32? 4. What %m format string is used for? And where is it handled. Do I need to implement it? I am attaching my version if snprintf.c (because it is too different from the original to make a patch) and regression.diff of a failed Win32 regression test produced wither with my or with original snprintf.c. Best regards, Nicolai Tufar ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] pg_clog problem (PG version 7.4.5)
hackers, I am having a problem with table (identified by pg_dump). I get the follow error when I try to COPY the table to stdout (or /dev/null). DB=# copy rnk to '/dev/null'; ERROR: could not access status of transaction 1076101119 DETAIL: could not open file /usr/local/pgsql/data/pg_clog/0402: No such file or directory Pg version is select version(); version - PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.4 (Debian 1:3.3.4-6sarge1) I do have a good backup from the day before, if needed. is there a magic command to get me out of this? Thanks Jim ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] %2$, %1$ gettext placeholder replacement is not working under Win32
Nicolai Tufar wrote: 1. I renamed snprintf() to pg_snprintf(), vsnprintf() to pg_vsnprintf() and introduced pg_printf() that calls pg_vsnprintf(). This is not necessary. You will notice that we already have configure tests of snprintf format specifiers (%lld etc.), so using the original function names is OK even in that case. 4. I introduced a volatile static char[] variable in snprintf.c code so I can grep executables for this string and be sure that it is included. Remove that when finalizing the code. 5. Before running regression test I always ran make install, apparently because libpq is read from /usr/local/. That's because of the -rpath. 2. Why would we not just take FreeBSD's vfprintf() and use it instead? Try it. It's painful. 4. What %m format string is used for? And where is it handled. Do I need to implement it? It's only used in the error reporting functions in the server and is handled there. You don't need to worry about it. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_clog problem (PG version 7.4.5)
I just upgraded to 7.4.6 and have the same error message. -- Original Message --- From: Jim Buttafuoco [EMAIL PROTECTED] To: pgsql-hackers pgsql-hackers@postgresql.org Sent: Sat, 22 Jan 2005 09:35:02 -0500 Subject: [HACKERS] pg_clog problem (PG version 7.4.5) hackers, I am having a problem with table (identified by pg_dump). I get the follow error when I try to COPY the table to stdout (or /dev/null). DB=# copy rnk to '/dev/null'; ERROR: could not access status of transaction 1076101119 DETAIL: could not open file /usr/local/pgsql/data/pg_clog/0402: No such file or directory Pg version is select version(); version - PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.4 (Debian 1:3.3.4-6sarge1) I do have a good backup from the day before, if needed. is there a magic command to get me out of this? Thanks Jim ---(end of broadcast)--- TIP 8: explain analyze is your friend --- End of Original Message --- ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pg_clog problem (PG version 7.4.5)
Jim Buttafuoco wrote: hackers, I am having a problem with table (identified by pg_dump). I get the follow error when I try to COPY the table to stdout (or /dev/null). DB=# copy rnk to '/dev/null'; ERROR: could not access status of transaction 1076101119 DETAIL: could not open file /usr/local/pgsql/data/pg_clog/0402: No such file or directory Pg version is select version(); version - PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.4 (Debian 1:3.3.4-6sarge1) I do have a good backup from the day before, if needed. is there a magic command to get me out of this? You could try creating that file by writing a bunch of zeros to it, for example: Shutdown PostgreSQL Then run the following command in $PGDATA/pg_clog dd bs=8K count=1 if=/dev/zero of=filename Where the filename is the name of the file missing. No promises though. Sincerely, Joshua D. Drake Thanks Jim ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Extending System Views: proposal for 8.1/8.2
On Sat, Jan 22, 2005 at 08:46:21PM +1100, Neil Conway wrote: Jim C. Nasby wrote: On another naming note; the naming convention for system stuff has always driven me nuts. Some the letter prefix (ie: tab for tables) in front of every field name, with no underscores or anything. Extensive use of abbreviations that you need to remember (ie: indnatts, indexprs, indpred). No use of underscores (indisunique). Yet the view and table names do use underscores. I agree the naming conventions for system catalog columns is less than optimal, but it seems a net loss to rename columns that already exist (given the amount of code that would need to be updated, both within the tree and in admin utilities and the like). Renaming all the system catalogs and providing backward compatibility views would mean you'd only need to modify the PG source, although of course those modifications would be pretty time-consuming... In any case, that's no reason not to try for better names in newly-added system objects. Just to be clear, I'm not suggesting renaming anything in any of the existing pg_catalog objects. I'm suggesting creating a new, easier to use set of views that would sit on top of pg_catalog. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] pg_clog problem (PG version 7.4.5)
didn't work. ERROR: could not access status of transaction 1076101119 DETAIL: could not read from file /usr/local/pgsql/data/pg_clog/0402 at offset 57344: Success any more ideas? -- Original Message --- From: Joshua D. Drake [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-hackers pgsql-hackers@postgresql.org Sent: Sat, 22 Jan 2005 08:00:25 -0800 Subject: Re: [HACKERS] pg_clog problem (PG version 7.4.5) Jim Buttafuoco wrote: hackers, I am having a problem with table (identified by pg_dump). I get the follow error when I try to COPY the table to stdout (or /dev/null). DB=# copy rnk to '/dev/null'; ERROR: could not access status of transaction 1076101119 DETAIL: could not open file /usr/local/pgsql/data/pg_clog/0402: No such file or directory Pg version is select version(); version - PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.4 (Debian 1:3.3.4-6sarge1) I do have a good backup from the day before, if needed. is there a magic command to get me out of this? You could try creating that file by writing a bunch of zeros to it, for example: Shutdown PostgreSQL Then run the following command in $PGDATA/pg_clog dd bs=8K count=1 if=/dev/zero of=filename Where the filename is the name of the file missing. No promises though. Sincerely, Joshua D. Drake Thanks Jim ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL --- End of Original Message --- ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Autotuning Group Commit
On Sat, Jan 22, 2005 at 08:47:37AM +, Simon Riggs wrote: On Sat, 2005-01-22 at 00:18 -0600, Jim C. Nasby wrote: 1) I'm in favor of autotuning anything possible. 2) In addition to turning group_commit on and off, what about also adjusting the commit delay, based on statistics of recent commits? It might require a slightly larger sample set (maybe the last 100 commits), but it seems it would provide more flexibility (hence more usefulness) to the autotuning. I belive you'd want to first calculate the elapsed time between each commit in the sample set, then look for groupings of elapsed time. If you have a set that looks like this: Time (ms) Number 2 * 4 * 6 8 ** 10 * 12 ** 14 16 ** 18 20 * then you'd want a delay of 16ms. I think this calculation could be done fairly quickly by grouping the commits into buckets of different elapsed times, then look for the largest elapsed time that has a number of commits greater than the mean number of commits for all the buckets. But I'm not a statistician, hopefully someone here is. :) Yes, I considered that, but since we're talking about a frequently executed piece of code, I was hoping to keep it short and sweet. What do others think? I don't think the frequently executed code would need to differ between the two options. The remaining analysis would be done by a background process. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] French site with postgresql name
Some french guy on IRC showed the site http://www.postgresql.fr/ that does not contain anything about postgresql. I don't speak french so I can't tell what the page is about. It looks linux related. This doesn't really belong on the -hackers list but I don't know where to send the info or even if someone cares about it at all. --- some time later --- It turns out that the to me unknown french guy I was chatting with, was in fact Gaetano Mendola but with a new irc nick (and I've cc:ed him). -- /Dennis Björklund ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] pg_clog problem (PG version 7.4.5)
On Sat, Jan 22, 2005 at 12:06:46PM -0500, Jim Buttafuoco wrote: didn't work. ERROR: could not access status of transaction 1076101119 DETAIL: could not read from file /usr/local/pgsql/data/pg_clog/0402 at offset 57344: Success any more ideas? You need to extend the file further than byte 57344. So repeat the dd command, with count=8. Anyway, this situation is suspect anyway. There were bugs related to pg_clog files not being there, but AFAIR they were triggered on segment edges, not in the middle like this one. What other files there are in the pg_clog directory? If they are nowhere near the 0402 vicinity, you may have a memory or disk corruption problem. FWIW, 1076101119 is 1100011 in binary. Taking the highest 1 yeilds Xid 2359295. So, what files do you have? -- Alvaro Herrera ([EMAIL PROTECTED]) La primera ley de las demostraciones en vivo es: no trate de usar el sistema. Escriba un guión que no toque nada para no causar daños. (Jakob Nielsen) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] pg_clog problem (PG version 7.4.5)
Alvaro, Thanks for the reply. here is an ls of my pg_clog directory. The 0402 file, I created as per Joshua's directions. I might have created one too small. If so, what size do you think I should use. Jim bda1:/usr/local/pgsql/data# ls -l pg_clog total 992 -rw--- 1 postgres dba 262144 Sep 7 10:12 -rw--- 1 postgres dba 262144 Nov 12 09:57 0001 -rw--- 1 postgres dba 262144 Dec 7 17:31 0002 -rw--- 1 postgres dba 204800 Jan 22 13:11 0003 -rw-r--r-- 1 postgres dba 8192 Jan 22 12:05 0402 -- Original Message --- From: Alvaro Herrera [EMAIL PROTECTED] To: Jim Buttafuoco [EMAIL PROTECTED] Cc: Joshua D. Drake [EMAIL PROTECTED], pgsql-hackers pgsql-hackers@postgresql.org Sent: Sat, 22 Jan 2005 15:07:35 -0300 Subject: Re: [HACKERS] pg_clog problem (PG version 7.4.5) On Sat, Jan 22, 2005 at 12:06:46PM -0500, Jim Buttafuoco wrote: didn't work. ERROR: could not access status of transaction 1076101119 DETAIL: could not read from file /usr/local/pgsql/data/pg_clog/0402 at offset 57344: Success any more ideas? You need to extend the file further than byte 57344. So repeat the dd command, with count=8. Anyway, this situation is suspect anyway. There were bugs related to pg_clog files not being there, but AFAIR they were triggered on segment edges, not in the middle like this one. What other files there are in the pg_clog directory? If they are nowhere near the 0402 vicinity, you may have a memory or disk corruption problem. FWIW, 1076101119 is 1100011 in binary. Taking the highest 1 yeilds Xid 2359295. So, what files do you have? -- Alvaro Herrera ([EMAIL PROTECTED]) La primera ley de las demostraciones en vivo es: no trate de usar el sistema. Escriba un guión que no toque nada para no causar daños. (Jakob Nielsen) ---(end of broadcast)--- TIP 8: explain analyze is your friend --- End of Original Message --- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_clog problem (PG version 7.4.5)
Jim Buttafuoco [EMAIL PROTECTED] writes: Thanks for the reply. here is an ls of my pg_clog directory. The 0402 file, I created as per Joshua's directions. I might have created one too small. If so, what size do you think I should use. bda1:/usr/local/pgsql/data# ls -l pg_clog total 992 -rw--- 1 postgres dba 262144 Sep 7 10:12 -rw--- 1 postgres dba 262144 Nov 12 09:57 0001 -rw--- 1 postgres dba 262144 Dec 7 17:31 0002 -rw--- 1 postgres dba 204800 Jan 22 13:11 0003 -rw-r--r-- 1 postgres dba 8192 Jan 22 12:05 0402 Given that set of pre-existing files, there is no possible way that you really had a transaction in the range of IDs that 0402 would cover. I agree with Alvaro's theory of a corrupted tuple. In fact it seems plausible that the error is a single high-order 1 bit and the ID that appears to be in the range of 0402 really belonged to file 0002. A single dropped bit sounds more like RAM flakiness than disk problems to me, so I'd get out the memory tester programs and start looking. As far as recovering the data goes, you can use the usual techniques for homing in on the location of the bad tuple and getting rid of it (or try manually patching the XID field with a hex editor...) regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Merge pg_shadow pg_group -- UNTESTED
Hi Stephen and Hackers, Moved to -hackers. Here's a proof-of-concept pretty much untested (it compiles) patch against HEAD for review of the general approach I'm taking to merging pg_shadow and pg_group. This is in order to support group ownership and eventually roles. I have to disagree with your model. Roles are not so simple like you try to describe in your patch. I'm suposing this because your using role* in all of the 'pg_shadow'. What's Role? A set of relations with their respective privileges and a set of users and/or roles. Sometime ago, I drafted a model I think it can be useful. Here it is: Another catalog relation named 'pg_role' with the following members: - rolsysid (role id) - rolname (role name) - rolowner (role owner) - rolmembs[] (list of users that belong to the role) - rolrels[] (list of relations + their permissions) - hasroles (have dependent roles?) where: rolmembs[] is: - userid (user id or group id or role id) rolrels[] is: - relid (relation oid) - rs_privs (privileges) What do we do with 'groups'? Well, we can have three categories of object owners: users, groups and roles. So the 'group owner' can be implemented with this model. What about dependent roles? It will be in 'pg_depend'. Advantages: 1. Don't require changing the actual catalog model. Just an increment. 2. Can't introduce too much overhead. Once roles are in another catalog table, we need to search it only if it's required. 3. All serious commercial databases have it. And of course, PostgreSQL community want it too. :-) Disadvantages: 1. Some overhead when checking for roles and dependent roles. Comments and/or ideas? = Euler Taveira de Oliveira euler[at]yahoo_com_br __ Converse com seus amigos em tempo real com o Yahoo! Messenger http://br.download.yahoo.com/messenger/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] pg_clog problem (PG version 7.4.5)
I was able to copy the table over to a temp table and truncate it with only a little loss. I will be able to recover the lost data from backup so no big deal. I will have to schedule downtime to do the memory test with the big snow storm it will not be until monday night. thanks for the help Jim -- Original Message --- From: Tom Lane [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Alvaro Herrera [EMAIL PROTECTED], pgsql-hackers pgsql-hackers@postgresql.org Sent: Sat, 22 Jan 2005 13:41:04 -0500 Subject: Re: [HACKERS] pg_clog problem (PG version 7.4.5) Jim Buttafuoco [EMAIL PROTECTED] writes: Thanks for the reply. here is an ls of my pg_clog directory. The 0402 file, I created as per Joshua's directions. I might have created one too small. If so, what size do you think I should use. bda1:/usr/local/pgsql/data# ls -l pg_clog total 992 -rw--- 1 postgres dba 262144 Sep 7 10:12 -rw--- 1 postgres dba 262144 Nov 12 09:57 0001 -rw--- 1 postgres dba 262144 Dec 7 17:31 0002 -rw--- 1 postgres dba 204800 Jan 22 13:11 0003 -rw-r--r-- 1 postgres dba 8192 Jan 22 12:05 0402 Given that set of pre-existing files, there is no possible way that you really had a transaction in the range of IDs that 0402 would cover. I agree with Alvaro's theory of a corrupted tuple. In fact it seems plausible that the error is a single high-order 1 bit and the ID that appears to be in the range of 0402 really belonged to file 0002. A single dropped bit sounds more like RAM flakiness than disk problems to me, so I'd get out the memory tester programs and start looking. As far as recovering the data goes, you can use the usual techniques for homing in on the location of the bad tuple and getting rid of it (or try manually patching the XID field with a hex editor...) regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match --- End of Original Message --- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Merge pg_shadow pg_group -- UNTESTED
* Euler Taveira de Oliveira ([EMAIL PROTECTED]) wrote: Here's a proof-of-concept pretty much untested (it compiles) patch against HEAD for review of the general approach I'm taking to merging pg_shadow and pg_group. This is in order to support group ownership and eventually roles. I have to disagree with your model. Roles are not so simple like you try to describe in your patch. I'm suposing this because your using role* in all of the 'pg_shadow'. The particular name isn't really important- and don't take it to mean very much... What's Role? A set of relations with their respective privileges and a set of users and/or roles. That's a good question- I'm not really very familiar with roles. :) I'm honestly more interested in group ownership... Advantages: 1. Don't require changing the actual catalog model. Just an increment. I'm not sure what the value of this is.. 2. Can't introduce too much overhead. Once roles are in another catalog table, we need to search it only if it's required. ok. 3. All serious commercial databases have it. And of course, PostgreSQL community want it too. :-) Well, yes, we want roles, we're discussing implementations though, and I don't see this as an 'advantage' of your approach. :) Disadvantages: 1. Some overhead when checking for roles and dependent roles. It was Tom's suggestion that pg_shadow and pg_group be merged to guarntee unique in the 'id's, which needs to be there unless you want to change pg_object (iirc? whatever table it is) to handle additional information about what kind of 'id' it is (role, user or group). Stephen signature.asc Description: Digital signature
Re: [HACKERS] Much Ado About COUNT(*)
Added to TODO based on this discusion: --- * Speed up COUNT(*) We could use a fixed row count and a +/- count to follow MVCC visibility rules, or a single cached value could be used and invalidated if anyone modifies the table. Another idea is to -- get a count directly from a unique index, but for this to be faster than a sequential scan it must avoid access to the heap to obtain tuple visibility information. * Allow data to be pulled directly from indexes Currently indexes do not have enough tuple tuple visibility information to allow data to be pulled from the index without also accessing the heap. One way to allow this is to set a bit to index tuples to indicate if a tuple is currently visible to all transactions when the first valid heap lookup happens. This bit would have to be cleared when a heap tuple is expired. --- Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Ah, right, I missed the connection. Hmm ... that's sort of the inverse of the killed tuple optimization we put in a release or two back, where an index tuple is marked as definitely dead once it's committed dead and the deletion is older than all active transactions. Yes, it is sort of the reverse, but how do you get around the delete case? A would-be deleter of a tuple would have to go and clear the known good bits on all the tuple's index entries before it could commit. This would bring the tuple back into the uncertain status condition where backends would have to visit the heap to find out what's up. Eventually the state would become certain again (either dead to everyone or live to everyone) and one or the other hint bit could be set again. The ugly part of this is that clearing the bit is not like setting a hint bit, ie it's not okay if we lose that change. Therefore, each bit-clearing would have to be WAL-logged. This is a big part of my concern about the cost. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] %2$, %1$ gettext placeholder replacement is not working under Win32
On Sat, 22 Jan 2005 15:31:39 +0100, Peter Eisentraut [EMAIL PROTECTED] wrote: Nicolai Tufar wrote: 1. I renamed snprintf() to pg_snprintf(), vsnprintf() to pg_vsnprintf() and introduced pg_printf() that calls pg_vsnprintf(). This is not necessary. You will notice that we already have configure tests of snprintf format specifiers (%lld etc.), so using the original function names is OK even in that case. how about a test for the thing like: printf(%2$s %1$s!\n, world, Hello); It is what I am trying to solve :( 5. Before running regression test I always ran make install, apparently because libpq is read from /usr/local/. That's because of the -rpath. I see. Thanks for information. 2. Why would we not just take FreeBSD's vfprintf() and use it instead? Try it. It's painful. src/port/snprintf.c is not not a particularly pretty. And after my rework it got even uglier. I have looked at FreeBDS's one and it is not *that* complicated. Would you like me to try to incorporate into PostgreSQL. NetBSD's one is somewhat simplier but does not support %n$ feature by the way. 4. What %m format string is used for? And where is it handled. Do I need to implement it? It's only used in the error reporting functions in the server and is handled there. You don't need to worry about it. Oh, that is a relief. Peter Eisentraut Nicolai ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Merge pg_shadow pg_group -- UNTESTED
Hi Stephen, I have to disagree with your model. Roles are not so simple like you try to describe in your patch. I'm suposing this because your using role* in all of the 'pg_shadow'. The particular name isn't really important- and don't take it to mean very much... OK. So let it 'use*'. What's Role? A set of relations with their respective privileges and a set of users and/or roles. That's a good question- I'm not really very familiar with roles. :) I'm honestly more interested in group ownership... OK. Thinking better, keep up your work. I'm going to keep my eyes on it. = Euler Taveira de Oliveira euler[at]yahoo_com_br ___ Yahoo! Acesso Grátis - Instale o discador do Yahoo! agora. http://br.acesso.yahoo.com/ - Internet rápida e grátis ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] ARC patent
Jonah H. Harris [EMAIL PROTECTED] writes: I have a couple aquaintances at IBM that I can try to contact about it. Rather than assume what IBM will do, why not just ask them? If they don't respond, they don't respond. If they do respond, it's better than us guessing. People seem to be assuming that asking IBM is a zero-risk thing. It's not. If they are forced to deal with the issue, they might well feel that they have to take action that we'd not like; whereas as long as it's not officially in front of them, they can pretend to ignore us. This is not a whole lot different from our situation today: now that the issue of the pending patent is officially in front of us, we have to deal with it. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Autotuning Group Commit
Simon Riggs [EMAIL PROTECTED] writes: Each transaction commit gets the current time(NULL) immediately before it commits. time() has 1 second resolution and ergo is utterly useless for this. gettimeofday() may have sufficient resolution, but the resolution is not specified anywhere. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Much Ado About COUNT(*)
Tom Lane wrote: Tom Lane [EMAIL PROTECTED] writes: Manfred Koizar [EMAIL PROTECTED] writes: Last time we discussed this, didn't we come to the conclusion, that resetting status bits is not a good idea because of possible race conditions? There's no race condition, Actually, wait a minute --- you have a point. Consider a tuple whose inserting transaction (A) has just dropped below GlobalXmin. Transaction B is doing an index scan, so it's going to do something like * Visit index entry, observe that it is in uncertain state. * Visit heap tuple, observe that A has committed and is GlobalXmin, and there is no deleter. * Return to index entry and mark it visible to all. Now suppose transaction C decides to delete the tuple. It will * Insert itself as the XMAX of the heap tuple. * Visit index entry, set state to uncertain if not already that way. C could do this between steps 2 and 3 of B, in which case the index entry ends up improperly marked visible to all while in fact a deletion is pending. Ugh. We'd need some kind of interlock to prevent this from happening, and it's not clear what. Might be tricky to create such an interlock without introducing either deadlock or a big performance penalty. I am thinking we have to somehow lock the row while we set the index status bit. We could add a new heap bit that says my xid is going to set the status bit and put our xid in the expired location, set the bit, then return to the heap and clear it. Can we keep the heap and index page locked at the same time? Anyway it is clearly something that could be an issue. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Extending System Views: proposal for 8.1/8.2
Josh Berkus josh@agliodbs.com writes: I might suggest simply tables triggers types etc. The plurals of these reserved words are no, AFAIK, reserved. And if users are creating identically named objects in public, they just need to remember to use the schema. Only if you put them in some other schema. We have specifically promised not to create any tables/views in pg_catalog that do not have names beginning with pg_ --- see http://developer.postgresql.org/docs/postgres/ddl-schemas.html#DDL-SCHEMAS-CATALOG regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Extending System Views: proposal for 8.1/8.2
Jim, Just to be clear, I'm not suggesting renaming anything in any of the existing pg_catalog objects. I'm suggesting creating a new, easier to use set of views that would sit on top of pg_catalog. I have no objection to using easier to read names for the system views. (This is the user-friendly views, folks, not the actual system objects!). The reason I suggested the names I did was to be consistent. Thing is, at least for the next version, if we are changing the naming conventions, we need to leave the old views alone, at least for one version (pg_tables, pg_views, etc.). This means a new view name scheme for the new views. Suggestions? I might suggest simply tables triggers types etc. The plurals of these reserved words are no, AFAIK, reserved. And if users are creating identically named objects in public, they just need to remember to use the schema. Oh, also for the Parameters (array) etc.? I was planning on having text names there, *not* an array of OIDs or whatever. The purpose of these views is to be user-friendly. --Josh ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [pgsql-hackers-win32] [HACKERS] %2$, %1$ gettext placeholder replacement is not working under Win32
Nicolai Tufar [EMAIL PROTECTED] writes: On Sat, 22 Jan 2005 15:31:39 +0100, Peter Eisentraut [EMAIL PROTECTED] wrote: Nicolai Tufar wrote: 2. Why would we not just take FreeBSD's vfprintf() and use it instead? Try it. It's painful. src/port/snprintf.c is not not a particularly pretty. And after my rework it got even uglier. I have looked at FreeBDS's one and it is not *that* complicated. If you can get it to work then it'd be a better bet than writing (and having to maintain) our own. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Locale agnostic unicode text
Dawid Kuroczko [EMAIL PROTECTED] writes: So... I thoght, why not use this unitext to sort texts? So I've created functions, operators and operator class, This time setlocale() was needed to get the behaviour I needed (database initdb'ed to 'C', my order set to 'pl_PL', or whatever locale I need at given moment). I would imagine that the performance is spectacularly awful :-(. Have you benchmarked it? A large sort on a unitext column, for instance, would be revealing. ...but I would like to force ORDER BY using operators provided by me without this 'USING ' clause. Hmm, the existence of the default btree operator class should be sufficient. CREATE OR REPLACE FUNCTION lower(unitext) RETURNS unitext AS $$ utf8::decode($_[0]); return lc($_[0]); $$ LANGUAGE plperlu IMMUTABLE; AFAIK upper/lower cannot be considered to be locale-independent (see Turkish I/i business for a counterexample). regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Extending System Views: proposal for 8.1/8.2
On Sat, Jan 22, 2005 at 01:36:54PM -0800, Josh Berkus wrote: Jim, Just to be clear, I'm not suggesting renaming anything in any of the existing pg_catalog objects. I'm suggesting creating a new, easier to use set of views that would sit on top of pg_catalog. I have no objection to using easier to read names for the system views. (This is the user-friendly views, folks, not the actual system objects!). The reason I suggested the names I did was to be consistent. Out of curiosity, what's the relation between the tables in pg_catalog and the 'actual system objects'? I ass-u-me'd that these tables were the backing store for the real information, but maybe that's not the case. Thing is, at least for the next version, if we are changing the naming conventions, we need to leave the old views alone, at least for one version (pg_tables, pg_views, etc.). This means a new view name scheme for the new views. Suggestions? If we're dropping the pg_, maybe call the new schema just 'catalog'? I might suggest simply tables triggers types etc. The plurals of these reserved words are no, AFAIK, reserved. And if users are creating identically named objects in public, they just need to remember to use the schema. Actually, the view names don't bother me at all. Granted, pg_ is 3 extra characters to type, but the names are crystal clear. What I don't like are the field names inside the views, and especially inside the pg_catalog tables. Oh, also for the Parameters (array) etc.? I was planning on having text names there, *not* an array of OIDs or whatever. The purpose of these views is to be user-friendly. I think these views are also very useful in certain programming situations, in which also having the OIDs might be very useful. Another option would be to have functions that given a array of names would return a array of OIDs. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Extending System Views: proposal for 8.1/8.2
In [EMAIL PROTECTED], on 01/22/05 at 05:21 PM, Jim C. Nasby [EMAIL PROTECTED] said: On Sat, Jan 22, 2005 at 01:36:54PM -0800, Josh Berkus wrote: Jim, Just to be clear, I'm not suggesting renaming anything in any of the existing pg_catalog objects. I'm suggesting creating a new, easier to use set of views that would sit on top of pg_catalog. I have no objection to using easier to read names for the system views. (This is the user-friendly views, folks, not the actual system objects!). The reason I suggested the names I did was to be consistent. Out of curiosity, what's the relation between the tables in pg_catalog and the 'actual system objects'? I ass-u-me'd that these tables were the backing store for the real information, but maybe that's not the case. Thing is, at least for the next version, if we are changing the naming conventions, we need to leave the old views alone, at least for one version (pg_tables, pg_views, etc.). This means a new view name scheme for the new views. Suggestions? If we're dropping the pg_, maybe call the new schema just 'catalog'? That will break all of the older ODBC drivers. -- --- [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Extending System Views: proposal for 8.1/8.2
On Sat, Jan 22, 2005 at 05:21:32PM -0600, Jim C. Nasby wrote: Out of curiosity, what's the relation between the tables in pg_catalog and the 'actual system objects'? I ass-u-me'd that these tables were the backing store for the real information, but maybe that's not the case. They are. I think these views are also very useful in certain programming situations, in which also having the OIDs might be very useful. Another option would be to have functions that given a array of names would return a array of OIDs. So why not have both in the view? It's not like you are storing duplicated data anyway. -- Alvaro Herrera ([EMAIL PROTECTED]) Ellos andaban todos desnudos como su madre los parió, y también las mujeres, aunque no vi más que una, harto moza, y todos los que yo vi eran todos mancebos, que ninguno vi de edad de más de XXX años (Cristóbal Colón) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [pgsql-hackers-win32] [HACKERS] %2$, %1$ gettext placeholder replacement is not working under Win32
On Sat, 22 Jan 2005 17:05:22 -0500, Tom Lane [EMAIL PROTECTED] wrote: src/port/snprintf.c is not not a particularly pretty. And after my rework it got even uglier. I have looked at FreeBDS's one and it is not *that* complicated. If you can get it to work then it'd be a better bet than writing (and having to maintain) our own. Very well, I am starting to work on it. I will put everything necessary in on file. So far it is 1500-odd lines and will probably grow a little bit. Plus we would probably need to include Double-to-ASCII package too: http://cvsup.pt.freebsd.org/cgi-bin/cvsweb/cvsweb.cgi/src/contrib/gdtoa/ A daunting task indeed. I will do it but I am afraid it would not be too portable. I was wandering if reimplementing print formatting is the right thing to do. The problem I have is in fmtnum() and probably in fmtfloat() functions which are very platform-dependent. The code to shuffle xxprinf() arguments based on %n$ formatting stirngs is ready why don't shuffle formatting placeholders too and call OS's vsnprintf() with modified formatting formatting strings and va_list? Or, a similar solution, for every parameter extract formatting placeholder and value, call snprintf() and the combine the resulting string? The first solution requires doing nasty manipulations with va_list, the second will be slower because of multiple calls to snprintf(). Which one is better? Best regards, Nicolai Tufar regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Extending System Views: proposal for 8.1/8.2
Jim C. Nasby [EMAIL PROTECTED] writes: If we're dropping the pg_, maybe call the new schema just 'catalog'? Any new schemas introduced by PG itself will be named pg_something. This is not open to negotiation --- it's what we've promised to users to avoid tromping on their schema namespace. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Merge pg_shadow pg_group -- UNTESTED
Stephen Frost [EMAIL PROTECTED] writes: Here's a proof-of-concept pretty much untested (it compiles) patch against HEAD for review of the general approach I'm taking to merging pg_shadow and pg_group. This is in order to support group ownership and eventually roles. This patch includes my grammar and get_grosysid move patches, and so conflicts with them. One point is that you can't simply whack pg_shadow around and eliminate pg_group, because that will break lord-knows-how-much client software that looks at these tables. What I'm envisioning is to create a new system catalog (say pg_role) that holds the New Truth, and then make pg_shadow and pg_group be predefined views on this catalog that provide as much backwards compatibility as we can manage. I believe this was done once before already --- I think that the pg_user view exists to emulate a prior incarnation of pg_shadow. A related point is that I hope soon to get rid of type AclId and usesysid/grosysid/rolesysid and start identifying roles by Oids. This is connected to Alvaro's work to create proper dependencies for object owners and privilege entries: once that exists and you can't drop a referenced role, there will be no need to allow explicit setting of the SYSID for a new user. Not sure if you want to do any of the associated changes in your patch, but if int4 is bugging you then feel free to change it. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Merge pg_shadow pg_group -- UNTESTED
Euler Taveira de Oliveira [EMAIL PROTECTED] writes: What's Role? A set of relations with their respective privileges and a set of users and/or roles. Huh? How did relations get into it? What do we do with 'groups'? Well, we can have three categories of object owners: users, groups and roles. So the 'group owner' can be implemented with this model. Why wouldn't we fold all three into one concept? In particular, I really fail to see why we'd still keep a separate notion of groups. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
