Postgres and Tracepoints

I've recently made some changes to avoid tracebacks in forms (e.g. when
the user fills in some invalid values into a form like an invalid ID in
a Link property).
The reason for most tracebacks was that the Postgres backend raises a
postgres-specific error when something goes wrong. When this occurs the
current transaction is aborted. The other backends ignore most of these
errors (and e.g. return an empty query for non-numeric ID values)
To emulate the behavior of other backends I have added tracepoints
before such problematic SQL constructs to roll back to when an error
occurs. This allows us to have a failing action in a longer transaction.
Since I'm re-using the same tracepoint name the tracepoint should be
overwritten.

I've discovered two problems with this:
(1)
- Postgres does not overwrite the tracepoint but keeps the last
  tracepoint of the same name and only makes it inaccessible
  https://www.postgresql.org/docs/17/sql-savepoint.html
  (the behavior didn't change since postgres version 8)
- Probably as a result of this for large transactions I'm running out of
  memory for locks within transactions, this can be fixed by increasing
  the postgres config variable max_locks_per_transaction (this is 64 by
  default on Debian, for some of my large transactions I needed to set
  this to 512 with the new code)
(2)
- Transactions in postgres with the tracepoint overhead are slower,
  sometimes by a factor of 2

So I think this needs some changes, probably only the methods called by
unverified input from the web-interface should use tracepoints. All
other uses (e.g. calling something from the detector interface) should
not. This might need additional checks so that unverified user input
never makes it into the detector functions.

For turning tracepoints on and off I can think of two variants:
- Using an additional parameter 'use_tracepoints' for
  - Class.filter and Class.filter_iter
  - Class.is_retired
  - Database.getnode
- Instead of an additional parameter use methods with a suffix (e.g.
  _safe or similar).
The additional parameter would (currently) apply only to postgres (and
be ignored by the default code used by the other backends) and the
suffix methods would be identical to the original for the default rdbms
backend code (which is inherited by all sql backends)

The semantics of this additional parameter (or method) would be that
the current transaction is not aborted if a traceback due to invalid
data occurs.

The cgi methods (e.g. in cgi/templating.py) would use the new variant.

Let me know what you think.

Kind regards
Ralf Schlatterbeck
-- 
Dr. Ralf Schlatterbeck                  Tel:   +43/2243/26465-16
Open Source Consulting                  www:   www.runtux.com
Reichergasse 131, A-3411 Weidling       email: [email protected]