Forum OpenACS Q&A: db_transactions in PostgreSQL

Posted by Bart Teeuwisse on 09/13/02 03:22 AM

Consider the following example. Two tables in the ecommerce package are linked by referential integrity: ec_categories is referenced from ec_subcategories. To remove a category and its subcategories the following (snippet) of code is used:

      
      
db_transaction {      
      
    db_dml delete_ec_subcats "delete from ec_subcategories where    
category_id=:category_id"      
      
    db_dml delete_ec_categories "delete from ec_categories where      
category_id=:category_id"      
      
}

Yet PostgreSQL reports a referential integrity violation --key in ec_categories still referenced from ec_subcategories-- upon executing the second query. Given that this bit of code is inherited from pre OpenACS days I can only conclude that it works for Oracle. On top of that I can perform the following sequence in pgsql without any errors:

 
begin; 
  delete from ec_subcategories where category_id=1; 
  delete from ec_categories where category_id=1; 
commit;

Could this be an db_transaction issue? Or am I missing something obvious?

2: Response to db_transactions in PostgreSQL (response to 1)

Posted by Dave Bauer on 09/13/02 03:32 AM

Bart, What version of Postgresql are you using? This is a bug in postgresql that was fixed in 7.2.

WHat you are seeing is the effect of triggers on acs_objects when you try to delete a parent and child object inside a transaction.

3: Response to db_transactions in PostgreSQL (response to 1)

Posted by Bart Teeuwisse on 09/13/02 05:11 AM

Dave, you are right on the money I am using PG 7.1.3. Does that mean that OpenACS
requires PG 7.2 or newer? Or is there a workaround for PG 7.1? Personally I'm fine with
upgrading my system to PG 7.2 but what about new installations?

4: Response to db_transactions in PostgreSQL (response to 1)

Posted by Don Baccus on 09/13/02 07:18 PM

We've already decided that 4.6 will require PG 7.2 (it's been out for about a year and PG 7.3 is already in beta). In the past we've tried to maintain compatibility with the current and previous versions of PG. So shooting for 7.3 and 7.2 support, but dropping 7.1 support, fits with past history.

Of course I just learned yesterday that "timestamp" in PG 7.3 will be timezoneless while in PG <= 7.2 it includes timezone so all our code's going to break Real Soon Now but I haven't even started thinking about how we might work around this. Sigh.

5: Response to db_transactions in PostgreSQL (response to 1)

Posted by Jon Griffin on 09/13/02 07:23 PM

Don,

I think I will hold off on the PG fixes for ref-timezones until this is resolved.

Is there a good place to check what is going to change in PG 7.3?

6: Response to db_transactions in PostgreSQL (response to 1)

Posted by Don Baccus on 09/13/02 10:40 PM

I think all we need to do is to change "timestamp" to "timestamptz" ("timestamp_tz", whatever the hell it is called) in the sources. This fill fix new installs. This would be easier than trying to rewrite a bunch of code, which might extend to users of OACS 4.5 having to rewrite code.

For dump-restore from PG 7.2 to PG 7.3 at the moment my fingers are crossed that in PG 7.2 they really entered the type as "timestamptz" and that the dump-restore will work for this reason. If it doesn't we're in trouble (and if it does using timestamptz in the sources for new installs makes even more sense).

But I've not tried it. I don't think we should worry about it for 4.6. If the dump behavior doesn't work as I outline above we're going to be screwed so let's cross our fingers. If it does work it's easy to edit the files.

7: Response to db_transactions in PostgreSQL (response to 1)

Posted by Randy Kunkee on 09/14/02 12:52 AM

And there's the default 6 place decimal precision for the seconds in PG7.2.1. If one changes all of the timestamp types to timestamp(0), you'll retain compatability with 7.1.3.

8: Response to db_transactions in PostgreSQL (response to 1)

Posted by Don Baccus on 09/14/02 02:11 AM

That's good to know in general (I didn't know that), but we already will be requiring PG 7.2+ for 4.6 for unrelated reasons (the "triggered data change violation" bug that finally got fixed among other things).