Обсуждение: max relations in a single database
Hi, using: - postgres 7.2.1 (with pl_funcs.c patch) - linux kernel 2.4.17 on intel 32 dual CPU While attempting to port an existing application things started to fail randomly, like 'relation xyz not found' etc. - It took me a while to figure out, that things starting to behave strangely, once I define more than 254 relations in one database. Currently I use: - 62 tables - 124 views - 68 sequences Now I define one more table, for example. postgres does not show up any errors while creating the table, it shows up fine in "\d", but the number of rows don't exceed 254. Now I define three more views, for example, they show up fine in "\d", but the rows still don't exceed 254. The problem is, that old relations start to be deleted (?) or being unaccessible. They do however get dumped with pg_dump, but they get an error by: SELECT COUNT(*) FROM race_dots_2002; ERROR: Relation "race_dots_2002" does not exist Am I missing something important? Do I have to raise another configuration directive, like for the max parameters in functions (which I already did and seems to work fine). Kind regards ... Ralph ...
Ralph Graulich <maillist@shauny.de> writes: > While attempting to port an existing application things started to fail > randomly, like 'relation xyz not found' etc. - It took me a while to > figure out, that things starting to behave strangely, once I define more > than 254 relations in one database. There is no such limit. Instead, I conclude that there is something seriously broken about your installation. I would suggest trying a system-catalog REINDEX (read the REINDEX man page about how to do this procedure; it's a bit involved). Then see if you can run pg_dumpall without errors --- if so, initdb and reload. In practically every case we've seen recently of behavior as weird as that, the problem eventually traced to hardware failures; for example, bad RAM leading to index corruption. I'd suggest running any diagnostic programs you can find. (badblocks and memtest86 have been spoken of highly in the lists.) Given that you are using a dual-CPU machine, it might also be worth looking at whether your kernel version has any known SMP-handling bugs. regards, tom lane
Hi Tom, > There is no such limit. Instead, I conclude that there is something > seriously broken about your installation. Otherwise the installation runs fine, as I hope the recent increase of parameters in pg_config.h didn't break anything: #define INDEX_MAX_KEYS 64 #define FUNC_MAX_ARGS INDEX_MAX_KEYS > I would suggest trying a system-catalog REINDEX (read the REINDEX man > page about how to do this procedure; it's a bit involved). Then see > if you can run pg_dumpall without errors --- if so, initdb and reload. I will try that, reindexing the system catalogue. I can however pg_dumpall even right now with any problems and all the relations show up fine in the resulting dump file. This is what I checked before asking. Meanwhilst I checked the documentation on the system catalogue tables and did a "SELECT * FROM pg_tables WHERE tablename NOT LIKE 'pg%';" and the tables and views I am missing show up there, but with a leading space. Then I went back to the failing relation of before and quoted the table name, added a leading space and it worked. The relation is there. However, in the create statement of those relations in question, there are neither any quotes nor any leading spaces. Double checked that. > In practically every case we've seen recently of behavior as weird as > that, the problem eventually traced to hardware failures; for example, > bad RAM leading to index corruption. I'd suggest running any diagnostic > programs you can find. (badblocks and memtest86 have been spoken of > highly in the lists.) I can't exclude the possibility of any hardware issues, for sure, but I am pretty sure my system runs fine, as there is an oracle development database on it and running RAID drives with an ICP vortex RAID controller (RAID 1). No problems shown related to any other program, but will check nonetheless. Just imported all the table definitions on my web machine (physically not connected to my development machine), which runs kernel 2.2.19, single CPU machine, RAID 1 drives. Same error there. Am really puzzled now. Below you'll find the configuration line I used to compile postgres on both machines, using gcc 2.95.2. ./configure \ --prefix=/home/postgres \ --mandir=/usr/man \ --with-pgport=5432 \ --with-perl \ --with-openssl=/usr/local/ssl \ --enable-odbc \ --enable-syslog \ --enable-locale Now I'll test the things you mentioned and will give feedback as soon as the tests are through. King regards ... Ralph ...
Ralph Graulich <maillist@shauny.de> writes: > Otherwise the installation runs fine, as I hope the recent increase of > parameters in pg_config.h didn't break anything: > #define INDEX_MAX_KEYS 64 > #define FUNC_MAX_ARGS INDEX_MAX_KEYS Shouldn't have, as long as you did a full recompile ("make clean all") after editing pg_config.h. You will also need to have initdb'd after installing that. regards, tom lane
Hi Tom, referring to your other mail: I did a "make distclean", reran "configure" again, edited the pg_config.h and patched the pl_funcs.c again. I think this is even more strict than doing "make clean all". Checking the local machine with memtest and did a RAID check on the volumes, no errors were found. I can't check the remote machine right now, as I cannot safely reboot, as the machine has no serial console. Concerning reindexing the system catalogue, I did this as describes in the REINDEX manpage, which was very helpful. So I reindexed in "postgres single user mode" (so to say) and it didn't complain about anything unusual. I pg_dumpall'ed the database, did initdb just to be on the safe side and recreated everything. While importing the dump file I get the following message on my logfile: RTERangeTablePosn: RTE not found (internal error) I traced that back to a CREATE VIEW statement: CREATE VIEW horse_exported_imported_by_year_country AS . . . If I copy'n'paste this definition by hand it tells me that it truncates the identifier: NOTICE: identifier "horse_exported_imported_by_year_country" will be truncated to "horse_exported_imported_by_year" Can this cause any error as listed above? Kind regards ... Ralph ...
Ralph Graulich <maillist@shauny.de> writes: > While importing the dump file I get the following message on my logfile: > RTERangeTablePosn: RTE not found (internal error) Oh? > I traced that back to a CREATE VIEW statement: > CREATE VIEW horse_exported_imported_by_year_country > AS > . > . > . > If I copy'n'paste this definition by hand it tells me that it truncates > the identifier: > NOTICE: identifier "horse_exported_imported_by_year_country" will be > truncated to "horse_exported_imported_by_year" > Can this cause any error as listed above? No, I don't think so. Can you post a self-contained example for someone else to try? I'm still suspicious of flaky hardware, but ... regards, tom lane
FWIW, I built PG 7.2.1 sources here (on a RHL 7.2 box) using ./configure --enable-cassert --enable-debug set INDEX_MAX_KEYS to 64 in src/include/pg_config.h make all; make install and ran the regression tests without seeing any problem. This is hardly conclusive since you may have used different configure options, but it does tell me that there's not anything seriously wrong with setting INDEX_MAX_KEYS = 64. Have you tried running the regression tests on your installation? regards, tom lane
Hi Tom, > > RTERangeTablePosn: RTE not found (internal error) > Oh? That's why I found at least; dunno wether this is directly related to this, but judging depending on the time it occured, it's likely. > > NOTICE: identifier "horse_exported_imported_by_year_country" will be > > truncated to "horse_exported_imported_by_year" > > Can this cause any error as listed above? > No, I don't think so. Can you post a self-contained example for someone > else to try? I'm still suspicious of flaky hardware, but ... Another thing: the name of the view, to which the new name gets truncated, already existed before. But no error message appears that the given view used. Just the "NOTICE" of above gets displayed. I am trying to assemble a self-contained example to show the error, however it can take quite a while, cause except for this error, postgres runs fine. Seems to appear only when lots of relation exist. I'll let the list know as soon as I got a ready-to-run test suite. Kind regards ... Ralph ...
[...] > but it does tell me that there's not anything seriously wrong with > setting INDEX_MAX_KEYS = 64. Glad to hear about that. > Have you tried running the regression tests on your installation? After the first installation I ran the regression tests; nothing unusual occured. On the other hand, I didn't run the regression tests after blowing up INDEX_MAX_KEYS and patching the pl_funcs.c file. But I don't think those changes are related to my problem. Anyhow, to be on the safe side, I'll do a fresh compiler run with the changes mentioned and will run a regression test. Results to follow... Kind regards ... Ralph ...
Hi Tom, > Have you tried running the regression tests on your installation? Meanhwilst I did a fresh compile with the changes involved and ran a regression test with no unusual errors showing up. Imported the dumpfile again gives me the same errors as before. As the dumpfile is plain ASCII text, I used vi to edit the names of the views by hand. I changed their names to be a good amount below the margin of 30 or whatever characters, just using abbreviations. Then I reimported without any problems. So far I tracked down it must have something to do with the following things in combination: - long view names using complete ave space for their names - names of the views only differing in the last char (e.g. ...year_2000, ...year_2001, ...year_2002) - and only if you're using views on views (e.g. view_all as union of the single years' views) - only if a fair amount of relations is already defined (maybe 254 was just by accident) Currently trying to tear the database apart and trying to build a test suite for it. Kind regards & thanks for your help so far ... Ralph ...
Ralph Graulich wrote: > As the dumpfile is plain ASCII text, I used vi to edit the names of the > views by hand. I changed their names to be a good amount below the margin > of 30 or whatever characters, just using abbreviations. > > Then I reimported without any problems. > Did you change NAMEDATALEN? See: pgsql/src/include/postgres_ext.h /* * NAMEDATALEN is the max length for system identifiers (e.g. table * names, attribute names, function names, etc). It must be a multiple * of sizeof(int) (typically 4). * * NOTE that databases with different NAMEDATALEN's cannot interoperate! */ #define NAMEDATALEN 32 If you have not changed this, then your maximum *allowable* table name (and other identifiers) is 31 characters (allowing for the null terminator). Could this be related to your problem? HTH, Joe