Обсуждение: Stability questions RE 6.5 and 6.3.2 & 6.3.2 problems

This is an error I am getting with 6.3.2
under freebsd 2.2.8
Mon Sep 6 16:44:48 BST 1999: NOTICE: SIAssignBackendId: discarding tag 2147483020
Mon Sep 6 16:44:48 BST 1999: FATAL 1: Backend cache invalidation initialization failed


Our database is about 38megabyes. We do have about 20 - 30 simultaneous connections. Most accessing the same
tables with transactions.

We see the above error quite often, and once it happens we have to restart postgress and our application.
We are accessing the db via jdbc.

We do not use any large objects (since they caused constant backend crashes for us)

1) Can anyone explain what this error is, and if there is something we can do to work around it.

2) is 6.5 stable enough for 23.5x7 production applications. 
3) are large objects stable in 6.5 (where I can store and access 20,000 of them regularily)

4) if all my sql works in 6.3.2 will it need any changes to run under 6.5

I am loosing the -- flush this free crap and move to oracle -- war here, so please help.

> This is an error I am getting with 6.3.2
> under freebsd 2.2.8
> Mon Sep 6 16:44:48 BST 1999: NOTICE: SIAssignBackendId: discarding tag 2147483020
> Mon Sep 6 16:44:48 BST 1999: FATAL 1: Backend cache invalidation initialization failed
> 
> 
> Our database is about 38megabyes. We do have about 20 - 30 simultaneous connections. Most accessing the same
> tables with transactions.
> 
> We see the above error quite often, and once it happens we have to restart postgress and our application.
> We are accessing the db via jdbc.
> 
> We do not use any large objects (since they caused constant backend crashes for us)
> 
> 1) Can anyone explain what this error is, and if there is something we can do to work around it.

It is caused by the corrupted shared cache. No workaround exists for
6.3.2 as far as I know.

> 2) is 6.5 stable enough for 23.5x7 production applications.

6.5 is much stable than pre 6.5 including 6.3.2. Even 128 simultaneous
connections are fine if properly configured.

> 3) are large objects stable in 6.5 (where I can store and access 20,000 of them regularily)

Yes, except that 20,000 large objects would be slow (20,000 large
objects will create 40,000 files right under the database directory
that would make directory lookup slow). If you are serious about using
many large objects, probably I could supply patches to enhance the
performance as a workaround.

> 4) if all my sql works in 6.3.2 will it need any changes to run under 6.5

It depends on your sql works. But basically you should be required
very few changes, I believe.

> I am loosing the -- flush this free crap and move to oracle -- war here, so please help.

I'm getting wining over commercial DBMSs in many projects since 6.5
released. Thanks for the row-level locking and MVCC stuffs..
---
Tatsuo Ishii

Jason Venner <jason@idiom.com> writes:
> This is an error I am getting with 6.3.2
> under freebsd 2.2.8
> Mon Sep 6 16:44:48 BST 1999: NOTICE: SIAssignBackendId: discarding tag 2147483020

I believe this is a symptom of running out of per-backend slots in the
SI (shared inval) communication area.  Theoretically that should not
happen until you try to start the 65th concurrent backend.

> We do have about 20 - 30 simultaneous connections.

Could it be getting up to a peak of 65 or more?

You could try increasing MaxBackendId in include/storage/sinvaladt.h,
but a much better answer is to update to 6.5, which supports easy
alteration of the max number of backends (and doesn't die horribly
when you hit the limit, either).

> We do not use any large objects (since they caused constant backend
> crashes for us)

Quite a few large-object bugs have been fixed since 6.3.2.  In fact,
quite a few bugs of many descriptions have been fixed since 6.3.2.

> 2) is 6.5 stable enough for 23.5x7 production applications.

Much more so than 6.3.2, for sure.  You should actually use 6.5.1,
or wait a few more days for 6.5.2 which has a few more bugs fixed
(or grab the 6.5.2 beta tarball from a week or so back, or pull the
REL6_5_PATCHES branch from the CVS repository).

> 3) are large objects stable in 6.5 (where I can store and access
> 20,000 of them regularily)

They're stable, but 20000 of them will be pretty slow (you'll end up
with 40000 separate files in your DB directory :-().  There has been
talk of fixing this by keeping multiple large objects in one file,
but I'd rather see the effort go into allowing tuples larger than
one disk block, which would eliminate the need for large objects
altogether...

> 4) if all my sql works in 6.3.2 will it need any changes to run under 6.5

Should pretty much work.  There are a few gotchas such as words that
are reserved keywords now that weren't before --- you might have to
rename some fields or tables, or resign yourself to double-quoting
those names all the time.  (I got caught with a field named
"timestamp", for example.)

You might also want to redesign whatever cross-client locking scheme
you are using.  I'm in the middle of that for my company --- we used
to just do "BEGIN; LOCK TABLE primary_table; blah blah blah; END;"
in each client to ensure that concurrent updates to several distinct
tables never caused deadlocks or apparent inconsistencies.  While that
still *works* under 6.5, you can get a heck of a lot more concurrency
if you understand and exploit the MVCC features.


I'd recommend bringing up a test 6.5 installation in parallel with your
6.3.2 installation (just give it a different install directory and
port number) so that you can experiment before you commit to a
changeover.  But do make the upgrade.  6.4 was a big win over 6.3.2
for stability in my applications, and 6.5 is better.
        regards, tom lane