On 7/22/2006 5:44 AM, Gavin Hamill wrote:

> Right, this issue has escalated from being a odd curiosity to having cost us a fortune in lost bookings.
> 
> Since roughly 4am our callcentre agents have received this error onscreen every time they try to complete a booking: Slony-I: old key column Booking."GuestID" IS NULL on UPDATE

Please get me the output of the following two queries for any table 
where this still occurs:

     select * from pg_trigger
         where tgrelid =
         (select oid from pg_class where relname = '<tablename>');

     select attname, attnum from pg_attribute
         where attrelid =
         (select oid from pg_class where relname = '<tablename>');

as well as a full schema description of the table, including primary 
keys, unique and not null constraints. I assume that the key/value 
column vector handed to the log trigger for those tables is out of sync 
with the current tables columns.

The only way I could imagine how slony got into that stage is that you 
have executed DDL like ALTER TABLE DROP COPUMN directly while the table 
was replicated (directly meaning not done via EXECUTE SCRIPT). Doing so 
would get the key attributes out of sync with respect to their position 
within all non-attisdroped columns.


Jan

> 
> Similarly to my previous research with DisplayNumberRooms, 
> 
> joltpg1:~# grep GuestID schema
>     "GuestID" integer,
> 
> i.e. GuestID does not appear anywhere in the schema but its definition as part of the Booking table. The table has 600k rows, with 400k having NULL GuestID (we only introduced the concept of a GuestID in March).
> 
> I tried REINDEX on the Booking table, which made no difference. Temporarily I have just done SET DROP TABLE for Booking so that our core business function can continue, but I am at a loss as to what to try next.
> 
> It can't just be a coincidence that the nightly VACUUM VERBOSE ANALYSE finished running about 10 minutes before, but I cannot find anything pertinent in the log output to indicate any error condition.
> 
> Any ideas?
> 
> Cheers,
> Gavin.
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/slony1-general


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck at Yahoo.com #