[PATCHES] Fix pgstatindex using for large indexes
Hi.
In pgstatindex.c and pgstattuple.sql, some variables are defined with
int type. So when we try to get informations about a large index by using
pgstatindex, we get strange value of size and density.
Because the values exceed int-max.
# Like following output. I used pgstatindex just after data load.
So "density" is should be nearly 90.
test=# SELECT * FROM pgstatindex('large_index');
-[ RECORD 1 ]--+
version| 2
tree_level | 4
index_size | -1349410816 ★
root_block_no | 119666
internal_pages | 28936
leaf_pages | 1379204
empty_pages| 0
deleted_pages | 0
avg_leaf_density | 60.33 ★
leaf_fragmentation | 0
I think that max_avail and free_space should be uint64.
And the output format for index_size should be "%lld" (INT64_FORMAT).
I made the patch and tryed it. (And it seemed OK.)
test=# SELECT * FROM pgstatindex('large_index');
-[ RECORD 1 ]--+
version| 2
tree_level | 4
index_size | 11535491072
root_block_no | 119666
internal_pages | 28936
leaf_pages | 1379204
empty_pages| 0
deleted_pages | 0
avg_leaf_density | 90.64
leaf_fragmentation | 0
I also fix *_pages variables just in case.
Please confirm this.
Best regards.
--
NTT OSS Center
Tatsuhito Kasahara
kasahara.tatsuhito _at_ oss.ntt.co.jp
diff -crN postgresql-8.3.0.org/contrib/pgstattuple/pgstatindex.c
postgresql-8.3.0/contrib/pgstattuple/pgstatindex.c
*** postgresql-8.3.0.org/contrib/pgstattuple/pgstatindex.c 2007-11-16
06:14:31.0 +0900
--- postgresql-8.3.0/contrib/pgstattuple/pgstatindex.c 2008-02-21
22:34:40.0 +0900
***
*** 63,77
uint32 level;
uint32 root_pages;
! uint32 internal_pages;
! uint32 leaf_pages;
! uint32 empty_pages;
! uint32 deleted_pages;
! uint32 max_avail;
! uint32 free_space;
! uint32 fragments;
} BTIndexStat;
/* --
--- 63,77
uint32 level;
uint32 root_pages;
! uint64 internal_pages;
! uint64 leaf_pages;
! uint64 empty_pages;
! uint64 deleted_pages;
! uint64 max_avail;
! uint64 free_space;
! uint64 fragments;
} BTIndexStat;
/* --
***
*** 87,94
Relationrel;
RangeVar *relrv;
Datum result;
! uint32 nblocks;
! uint32 blkno;
BTIndexStat indexStat;
if (!superuser())
--- 87,94
Relationrel;
RangeVar *relrv;
Datum result;
! BlockNumber nblocks;
! BlockNumber blkno;
BTIndexStat indexStat;
if (!superuser())
***
*** 207,213
values[j] = palloc(32);
snprintf(values[j++], 32, "%d", indexStat.level);
values[j] = palloc(32);
! snprintf(values[j++], 32, "%d", (indexStat.root_pages +
indexStat.leaf_pages +
indexStat.internal_pages +
indexStat.deleted_pages +
--- 207,213
values[j] = palloc(32);
snprintf(values[j++], 32, "%d", indexStat.level);
values[j] = palloc(32);
! snprintf(values[j++], 32, INT64_FORMAT, (indexStat.root_pages +
indexStat.leaf_pages +
indexStat.internal_pages +
indexStat.deleted_pages +
***
*** 215,231
values[j] = palloc(32);
snprintf(values[j++], 32, "%d", indexStat.root_blkno);
values[j] = palloc(32);
! snprintf(values[j++], 32, "%d", indexStat.internal_pages);
values[j] = palloc(32);
! snprintf(values[j++], 32, "%d", indexStat.leaf_pages);
values[j] = palloc(32);
! snprintf(values[j++], 32, "%d", indexStat.empty_pages);
values[j] = palloc(32);
! snprintf(values[j++], 32, "%d", indexStat.deleted_pages);
values[j] = palloc(32);
! snprintf(values[j++], 32, "%.2f", 100.0 - (float)
indexStat.free_space / (float) indexStat.max_avail * 100.0);
values[j] = palloc(32);
! snprintf
[PATCHES] Fix for initdb failures on Vista
The attached patch fixes problems reported primarily on Vista, but also on some Windows 2003 and XP installations in which initdb reports that it cannot find postgres.exe. This occurs because of security-related changes implemented in Windows Vista and recent patches on older OS's. When running initdb or pg_ctl we currently create a restricted security token with the Administrators and Power Users groups (and thus their privileges) removed and re-execute the same program using the restricted token. This ensures that the process is run without potentially dangerous privileges no matter what user account it was started from. On Vista and friends however, the default DACL (list of Access Control Entries) used in the restricted token contains Administrators (the group) & System when we run as Administrator, vs. User + System when run as other users. Because we then drop Administrators, we are left with only the System ACE in the DACL, which does not allow us to use CreatePipe()/CreateProcess(). To fix this, when we create the restricted process, we initially start it in suspended mode. We modify it's DACL to explicitly add an ACE for the current user, and then resume the child process. This remains secure because administrative privileges are granted to the groups that we've dropped, not the user itself. I've tested on Vista and XP, but additional testing would be useful (Andrew, Magnus?). Please apply to head, 8.3 and 8.2 -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Oracle-compatible database company fix_dacl.diff Description: Binary data ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PATCHES] fix in --help output
I attach fix for --help output. I replaced --NAME... with -NAME and add some
example. getopt parse -- as a end of options and rest of line is not parsed.
This should be backported for 8.3 and 8.2 as well. PG8.1 does not have this options.
Thanks Zdenek
Index: src/backend/main/main.c
===
RCS file: /zfs_data/cvs_pgsql/cvsroot/pgsql/src/backend/main/main.c,v
retrieving revision 1.110
diff -c -r1.110 main.c
*** src/backend/main/main.c 1 Jan 2008 19:45:49 - 1.110
--- src/backend/main/main.c 21 Feb 2008 17:02:16 -
***
*** 288,294
printf(_(" -p PORT port number to listen on\n"));
printf(_(" -s show statistics after each query\n"));
printf(_(" -S WORK-MEM set amount of memory for sorts (in kB)\n"));
! printf(_(" --NAME=VALUEset run-time parameter\n"));
printf(_(" --describe-config describe configuration parameters, then exit\n"));
printf(_(" --help show this help, then exit\n"));
printf(_(" --version output version information, then exit\n"));
--- 288,294
printf(_(" -p PORT port number to listen on\n"));
printf(_(" -s show statistics after each query\n"));
printf(_(" -S WORK-MEM set amount of memory for sorts (in kB)\n"));
! printf(_(" -NAME=VALUE set run-time parameter (e.g. -shared_buffers=16384)\n"));
printf(_(" --describe-config describe configuration parameters, then exit\n"));
printf(_(" --help show this help, then exit\n"));
printf(_(" --version output version information, then exit\n"));
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
[PATCHES] --describe-config crashes
Function printMixedStruct calls printf with NULL argument. It causes
segmentation fault. Please, apply it for 8.3 - 8.1 too.
thanks Zdenek
Index: src/backend/utils/misc/help_config.c
===
RCS file: /zfs_data/cvs_pgsql/cvsroot/pgsql/src/backend/utils/misc/help_config.c,v
retrieving revision 1.19
diff -c -r1.19 help_config.c
*** src/backend/utils/misc/help_config.c 1 Jan 2008 19:45:54 - 1.19
--- src/backend/utils/misc/help_config.c 21 Feb 2008 17:48:00 -
***
*** 117,123
case PGC_STRING:
printf("STRING\t%s\t\t\t",
! structToPrint->string.boot_val);
break;
default:
--- 117,123
case PGC_STRING:
printf("STRING\t%s\t\t\t",
! (structToPrint->string.boot_val == NULL) ? "(NULL)" : structToPrint->string.boot_val);
break;
default:
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [PATCHES] Fix pgstatindex using for large indexes
Tatsuhito Kasahara <[EMAIL PROTECTED]> writes: > In pgstatindex.c and pgstattuple.sql, some variables are defined with > int type. So when we try to get informations about a large index by using > pgstatindex, we get strange value of size and density. > Because the values exceed int-max. > ... > I think that max_avail and free_space should be uint64. Most places where we've dealt with this before, we use double, which is guaranteed to be available whereas uint64 is not ... regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] fix in --help output
Zdenek Kotala <[EMAIL PROTECTED]> writes: > I attach fix for --help output. I replaced --NAME... with -NAME and add some > example. getopt parse -- as a end of options and rest of line is not parsed. Surely this is outright wrong. Or if you do have a getopt that acts that way, the bug is that we are using it rather than one that acts the way we want. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] --describe-config crashes
Zdenek Kotala <[EMAIL PROTECTED]> writes: > Function printMixedStruct calls printf with NULL argument. It causes > segmentation fault. Please, apply it for 8.3 - 8.1 too. Ugh, I guess we've only tested --describe-config on platforms where %s treats a NULL pointer the same as an empty string. Since that's also effectively the way GUC handles it, I'm inclined to think we should print NULL as "" not "(NULL)". Otherwise, will apply. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] fix in --help output
Zdenek Kotala wrote: > I attach fix for --help output. I replaced --NAME... with -NAME But that is wrong. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] fix in --help output
Tom Lane napsal(a): Zdenek Kotala <[EMAIL PROTECTED]> writes: I attach fix for --help output. I replaced --NAME... with -NAME and add some example. getopt parse -- as a end of options and rest of line is not parsed. Surely this is outright wrong. Or if you do have a getopt that acts that way, the bug is that we are using it rather than one that acts the way we want. Ah, sorry it really does not work. However, I get following error on Solaris: bash-3.2$ /usr/postgres/8.2/bin/postgres -D /tmp/db --share_buffers=16000 /usr/postgres/8.2/bin/postgres: illegal option -- share_buffers=16000 Try "postgres --help" for more information. but following command works fine: /usr/postgres/8.2/bin/postgres -D /tmp/db -c shared_buffers=16000 By my opinion problem is in getopt which interprets -- as a end of options list. See http://www.opengroup.org/onlinepubs/009695399/basedefs/xbd_chap12.html#tag_12_02 Guideline 10 It maybe work on linux but I think it is not portable solution. Zdenek ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] fix in --help output
Alvaro Herrera napsal(a): Zdenek Kotala wrote: It maybe work on linux but I think it is not portable solution. What we should do is avoid using Solaris' getopt_long and instead use the copy we have in src/port/. If I looked correctly there is no getopt_long. There is only getopt with - as a option. See PostmasterMain: while ((opt = getopt(argc, argv, "A:B:c:D:d:EeFf:h:ijk:lN:nOo:Pp:r:S:sTt:W:-:")) != -1) If I understand correctly the POSIX standard "-" should not used in a option list. Zdenek ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] fix in --help output
Zdenek Kotala wrote: Tom Lane napsal(a): Zdenek Kotala <[EMAIL PROTECTED]> writes: I attach fix for --help output. I replaced --NAME... with -NAME and add some example. getopt parse -- as a end of options and rest of line is not parsed. Surely this is outright wrong. Or if you do have a getopt that acts that way, the bug is that we are using it rather than one that acts the way we want. Ah, sorry it really does not work. However, I get following error on Solaris: bash-3.2$ /usr/postgres/8.2/bin/postgres -D /tmp/db --share_buffers=16000 /usr/postgres/8.2/bin/postgres: illegal option -- share_buffers=16000 Try "postgres --help" for more information. but following command works fine: /usr/postgres/8.2/bin/postgres -D /tmp/db -c shared_buffers=16000 By my opinion problem is in getopt which interprets -- as a end of options list. See http://www.opengroup.org/onlinepubs/009695399/basedefs/xbd_chap12.html#tag_12_02 Guideline 10 It maybe work on linux but I think it is not portable solution. -- on its own might indicate the end of arguments, but that's quite different from --foo=bar. Guideline 10 of the above surely only refers to -- as an entire argument, not to -- as the first two characters of an argument. If your getopt treats *any* -- as the end of options then I think it is broken (complain to your vendor ;-) ). And the answer is known - use the one we have in src/port. cheers andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] fix in --help output
Zdenek Kotala wrote: > It maybe work on linux but I think it is not portable solution. What we should do is avoid using Solaris' getopt_long and instead use the copy we have in src/port/. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] fix in --help output
Andrew Dunstan napsal(a): Zdenek Kotala wrote: Tom Lane napsal(a): Zdenek Kotala <[EMAIL PROTECTED]> writes: I attach fix for --help output. I replaced --NAME... with -NAME and add some example. getopt parse -- as a end of options and rest of line is not parsed. Surely this is outright wrong. Or if you do have a getopt that acts that way, the bug is that we are using it rather than one that acts the way we want. Ah, sorry it really does not work. However, I get following error on Solaris: bash-3.2$ /usr/postgres/8.2/bin/postgres -D /tmp/db --share_buffers=16000 /usr/postgres/8.2/bin/postgres: illegal option -- share_buffers=16000 Try "postgres --help" for more information. but following command works fine: /usr/postgres/8.2/bin/postgres -D /tmp/db -c shared_buffers=16000 By my opinion problem is in getopt which interprets -- as a end of options list. See http://www.opengroup.org/onlinepubs/009695399/basedefs/xbd_chap12.html#tag_12_02 Guideline 10 It maybe work on linux but I think it is not portable solution. -- on its own might indicate the end of arguments, but that's quite different from --foo=bar. Guideline 10 of the above surely only refers to -- as an entire argument, not to -- as the first two characters of an argument. If your getopt treats *any* -- as the end of options then I think it is broken (complain to your vendor ;-) ). And the answer is known - use the one we have in src/port. You are not correct (I pointed to wrong Guidline). If you look to Guidline 3 it specific only alphanumeric character. And you can read "The implementation may accept other characters as an extension." in http://www.opengroup.org/onlinepubs/009695399/functions/getopt.html It means that Solaris implementation is OK. Linux uses some extensions but it is not portable. If we want to use long options. We have getop_long for it. Zdenek ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] fix in --help output
Zdenek Kotala wrote: > If I looked correctly there is no getopt_long. There is only getopt with > - as a option. See PostmasterMain: > > >while ((opt = getopt(argc, argv, > "A:B:c:D:d:EeFf:h:ijk:lN:nOo:Pp:r:S:sTt:W:-:")) != -1) > > If I understand correctly the POSIX standard "-" should not used in a > option list. Hmm, right. Our current parsing of --long-opts is quite a hack, it seems :-( Having to list all GUC options in the getopt_long array would be a mess. Any other ideas? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] 2WRS [WIP]
On Thu, Feb 21, 2008 at 6:44 AM, <[EMAIL PROTECTED]> wrote: > Hi. > > That's the last release and refers to 8.3.0 and not to 8.2.5 as before. Hope > you can tell me if I created it correctly please. > no, it doesn't... > ! /* GUC variables */ > #ifdef TRACE_SORT > booltrace_sort = false; > #endif > - #ifdef DEBUG_BOUNDED_SORT > - booloptimize_bounded_sort = true; > - #endif it's seems you're removing something added in 8.3 -- regards, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] fix in --help output
Zdenek Kotala wrote: > However, I get following error on Solaris: > > bash-3.2$ /usr/postgres/8.2/bin/postgres -D /tmp/db --share_buffers=16000 > /usr/postgres/8.2/bin/postgres: illegal option -- share_buffers=16000 > Try "postgres --help" for more information. > > but following command works fine: > > /usr/postgres/8.2/bin/postgres -D /tmp/db -c shared_buffers=16000 share_buffers is not the same as shared_buffers. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] fix in --help output
Peter Eisentraut napsal(a): Zdenek Kotala wrote: However, I get following error on Solaris: bash-3.2$ /usr/postgres/8.2/bin/postgres -D /tmp/db --share_buffers=16000 /usr/postgres/8.2/bin/postgres: illegal option -- share_buffers=16000 Try "postgres --help" for more information. but following command works fine: /usr/postgres/8.2/bin/postgres -D /tmp/db -c shared_buffers=16000 share_buffers is not the same as shared_buffers. Yeah, it was a typo but it does not work anyway. Zdenek ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
