Thread

1. is this a bug or feature?

   mikeo <mikeo@spectrumtelecorp.com> — 2000-07-13T13:17:42Z

   hi,
   
     i was building a testdb for development.  i did a pg_dump -t 
   
   to get the tables that i needed.  i then loaded those objects 
   
   with psql testdb << dump.file.  when i tried to do an update 
   
   against one of the tables i got this error message:
   
   
   <color><param>ffff,0000,0000</param>psql:update.sql:5: pqReadData() --
   backend closed the channel unexpectedly.
   
           This probably means the backend terminated abnormally
   
           before or while processing the request.
   
   psql:update.sql:5: connection to server was lost 
   
   
   </color>i got a similar error for insert, delete, or truncate but i could
   read
   
   the table without error.  i finally figured out that what had happened 
   
   was that the foreign key constraints to and from that table, table x,
   
   that existed in production ok, had loaded into my test area but the 
   
   associated tables that would have made those constraints valid did not
   
   exist;  i didn't load them as i didn't need them and had forgotten 
   
   that there were RI constraints dependent on them.  
   
   
   of course since i could still select from the table i guess i could go 
   
   microsoft and say that it's actually a "read only" feature of postgres! 
   :)
   
   
   my question is, why does postgres load up those constraints, when
   they'll
   
   be invalid and error out later with a vague error message, instead of 
   
   indicating up front that those constraints are there and refuse to load
   
   them?  
   
   
   it took me a while to figure this one out.  you cannot create a foreign 
   
   key to or from a non-existing table but the already generated triggers 
   
   from previous valid constraint creations will reload, in situations 
   such
   
   as my test database scenario up above, without initial complaint.
   
   
   xxxooo
   
   mikeo  
   
   

2. Re: is this a bug or feature?

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-07-13T15:53:21Z

   > the table without error.  i finally figured out that what had happened  
   > was that the foreign key constraints to and from that table, table x, 
   > that existed in production ok, had loaded into my test area but the  
   > associated tables that would have made those constraints valid did not
   > exist;  i didn't load them as i didn't need them and had forgotten> 
   > that there were RI constraints dependent on them.  
   
   Yep, it got noticed on -hackers a few days ago when someone reported 
   similar symptoms.
   
   The message is because the backend that was handling the request crashes
   in that case (so, definately a bug).  The foreign key triggers need to
   change a bit anyway and match partial needs to be implemented, so I
   plan to put something in to give a message that is meaningful inside
   the trigger if you get into this state.  As for not getting into that
   state, see below.
   
   > my question is, why does postgres load up those constraints, when
   > they'll 
   > be invalid and error out later with a vague error message, instead of 
   > indicating up front that those constraints are there and refuse to load 
   > them?  
   
   Mainly because the routine that re-adds the constraint is a much more
   general routine (create constraint trigger) which doesn't know anything
   about foreign keys.  It's effectively the same thing as calling create
   trigger for a c function on a table that exists, but giving it an arg
   to one that doesn't and having the trigger try to access the table
   assuming it exists (without checking that you successfully did).
   
   Theoretically, I'd guess that the new pg_dump is going to use 
   alter table add constraint to add the constraints.  That will prevent
   this particular way of getting to the problem, although you could still
   get into that state if you used a create constraint trigger manually.