[BUGS] 7.3.3 - plpython & trigger problem
I'm running "PostgreSQL 7.3.3 on i686-pc-linux-gnu, compiled by
GCC gcc (GCC) 3.2.2", and seeing a problem in trigger functions
written in plpython. This also happens in 7.3.2; I upgraded this
morning to see if the problem was already fixed. For fields of type
char, when a plpython trigger is called, the char fields in the
TD["new"] or TD["old"] maps show as 0 or 1 instead of their actual
value. As far as I've seen right now, it only affects char. The
varchar and text types seem ok, but I have not done any
comprehensive testing of the numerics and other types.
Here's a test script to show the problem:
CREATE TABLE test (
broken CHAR(4),
works VARCHAR(4),
alsoworks TEXT
);
CREATE OR REPLACE FUNCTION pytest() RETURNS TRIGGER
AS '
plpy.info ("TD=%s" % TD)
return None
' LANGUAGE 'plpython';
CREATE TRIGGER test_trig BEFORE INSERT OR UPDATE OR
DELETE ON test
FOR EACH ROW EXECUTE PROCEDURE pytest();
INSERT INTO test VALUES ('test', 'test', 'test');
UPDATE test SET broken='1234', works='1234', alsoworks='1234';
DELETE FROM test;
And here's the output from the last three statements on my system:
award=> INSERT INTO test VALUES ('test', 'test', 'test');
INFO: ("TD={'relid': '3928912', 'old': None, 'name': 'test_trig', 'level':
'ROW', 'new': {'alsoworks': 'test', 'broken': 1, 'works': 'test'}, 'args':
None, 'when': 'BEFORE', 'event': 'INSERT'}",)
INSERT 3928918 1
award=> UPDATE test SET broken='1234', works='1234',
alsoworks='1234';
INFO: ("TD={'relid': '3928912', 'old': {'alsoworks': 'test', 'broken': 1,
'works': 'test'}, 'name': 'test_trig', 'level': 'ROW', 'new': {'alsoworks':
'1234', 'broken': 0, 'works': '1234'}, 'args': None, 'when': 'BEFORE',
'event': 'UPDATE'}",)
UPDATE 1
award=> DELETE FROM test;
INFO: ("TD={'relid': '3928912', 'old': {'alsoworks': '1234', 'broken': 0,
'works': '1234'}, 'name': 'test_trig', 'level': 'ROW', 'new': None, 'args':
None, 'when': 'BEFORE', 'event': 'DELETE'}",)
DELETE 1
--
Arthur Ward
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] 7.3.3 - plpython & trigger problem
> There is a known bug with applying the same plpython trigger function to > multiple tables that have different rowtypes --- the rowtype info gets > cached for the first table the function is used with in a session, and > then inappropriately reused with the other tables. Does this seem to > describe your observations? No, it does not match what I'm seeing. To check, I dropped my test table and function, exited psql, started psql, and ran the script I originally posted. I still get the same results. If I had to guess, this seems more like a Postgres to plpython conversion problem than a caching problem. Another thing that seems weird about it is that the value is sometimes 1 and sometimes 0. In my test case, it's 1 when the value should be 'test', and 0 when '1234'. Had I not noticed plpython's problem with char fields, I would have already run smack into the caching problem you described. :-( -- Arthur Ward ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] 7.3.3 - plpython & trigger problem
> > No, it does not match what I'm seeing. To check, I dropped my test > > table and function, exited psql, started psql, and ran the script I > > originally posted. I still get the same results. If I had to guess, this > > seems more like a Postgres to plpython conversion problem than a > > caching problem. > > Bingo. The conversion-method-selection code is not only broken (for > several datatypes besides this one), but quite wrongheaded (testing on > the name of the datatype is the wrong approach). I've applied the > attached patch; please confirm it works for you. Applied successfully to 7.3.3 and the bug in question is fixed for my situation. Thanks a bunch for the quick fix! Now to implement the workaround for the plpython trigger & rowtype caching problem... -- Arthur Ward ---(end of broadcast)--- TIP 3: 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
[BUGS] plpython (triggers?) and dropped attributes
I have a table with a plpython trigger defined on it in 7.3.4. I've dropped a column from that table, and now I cannot get a plpython trigger to run at all on that table. I've dropped both the trigger and function and recreated them from scratch (not using "create or replace"), and I still get the following error when I set off the trigger: ERROR: plpython: Cache lookup for attribute `pg.dropped.5' type `0' failed The trigger procedure has a plpy.info call as the first statement, and I'm not seeing that output, thus I believe it's a problem in plpython. I suppose this is a bug in plpython's trigger handler building up the TD['new'] and TD['old'] structures. I figured I'd toss this out and see if somebody familiar with the plpython code can spot the problem faster than if I start muddling through the code blindly. Any thoughts? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] plpython (triggers?) and dropped attributes
> "Arthur Ward" <[EMAIL PROTECTED]> writes: >> I have a table with a plpython trigger defined on it in 7.3.4. I've >> dropped a column from that table, and now I cannot get a plpython >> trigger >> to run at all on that table. > > Would you try the attached patch? Lightly tested, but it seems to be working. Thanks a bunch! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] 7.4RC2 PANIC: insufficient room in FSM
I was a bit stunned last night when I found this in the server logs for a 7.4RC2 installation: Nov 24 20:37:18 x pg_autovacuum: [2003-11-24 08:37:18 PM] Performing: VACUUM ANALYZE "clients"."x" Nov 24 20:37:19 x postgres: [13904] PANIC: insufficient room in FSM Nov 24 20:37:19 x postgres: STATEMENT: VACUUM ANALYZE "clients"."x" Following this is of course the fallout of backends shutting down and PG recycling itself with no other problems. Did I miss something along the way about the FSM needing to be sufficiently large to hold all free pages no matter what? I plan to bump up the FSM size anyhow (perhaps tonight I can get some FSM stats from manually vacuuming), but my gosh, that's some bad behavior for a presumably minor situation. IMO, that's a significant bug. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] 7.4RC2 PANIC: insufficient room in FSM
> "Arthur Ward" <[EMAIL PROTECTED]> writes: >> I was a bit stunned last night when I found this in the server logs for >> a >> 7.4RC2 installation: > >> Nov 24 20:37:18 x pg_autovacuum: [2003-11-24 08:37:18 PM] Performing: >> VACUUM ANALYZE "clients"."x" >> Nov 24 20:37:19 x postgres: [13904] PANIC: insufficient room in FSM > > We have seen reports of similar things in situations where the real > problem was that the lock table had gotten too big --- is it possible > that you had something going on in parallel that would have acquired > lots of locks? If so, raising max_locks_per_transaction should avoid > the problem. I've combed through the system logs, our data-acquisition daemon's log, and web logs, and there's nothing indicating that there would be any more activity than there is normally all workday. It normally gets auto-vacuum hits during the day when there is a little more large-transaction activity with no problems. In the wee hours of the morning, I have a process doing bulk loads that locks about a dozen tables explicitly to avoid unnecessary rollbacks, but that was at least four hours in the future (or finished 19-ish hours in the past). That load also runs without issue. So, no, I can't say that there was anything out of the ordinary happening to cause the panic. ---(end of broadcast)--- TIP 8: explain analyze is your friend
