Обсуждение: 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