Forum OpenACS Q&A: Postgres Problem -again

As I am trying the 7.1devel with outer joins support, I attacked the
ec_recommendations_cats_view - though I made the query work, the view
doesn't want to execute. Anyway, as it is used only twice, I
reconstructed the query, getting the recommendations to work. And
again, once I have made a recommendation, deleting it is impossible.

Any further ideas on PG behavior (it deletes from ec_items or
ec_orders, while doesn't want to work with the above mentioned tables)

Thanks.

DELETE 0

(and surely it has records to delete and even without WHERE (DELETE FROM ec_product_recommendations) clause it is still DELETE 0) - It does not complain about referential integrity if that's what you'are about.

for example (you might want to run pg_dump first, in case you need to restore the original data):

-- save the original data in a tmp table.
create table tmp_user_classes as
select * from ec_user_classes;

-- this will also drop the referential integrity triggers.
drop table ec_user_classes;

-- do this or...
create table ec_user_classes as
select * from tmp_user_classes;

-- use the original table definition
create table ec_user_classes...

-- followed by 
insert into ec_user_classes
select * from tmp_user_classes;

-- now try and delete from ec_user_classes
delete from ec_user_classes;

It works even though I recreated those tables with appropriate foreign
keys. So it looks that when you load the data model from SQL scripts
when installing or after dump it won't work, while manual recreation
of the above does. Strange.

The ec_user_classes is referenced only from within ec_user_class_user_map and it references users, while ec_user_class_user_map references ec_user_classes and users, while not being referenced by another table. Almost the same for ec_sale prices.

If you can, please check this operations on your installation and report if it works.

Did you have any problems reloading the dump?  Did you edit the dump file at all before reloading it?  Did you use any switches to pg_dump?

I dump and restore my PG 7.0-based site all the time, and have never seen this behavior (I dump my live site to load into my development site for testing changes).

There were some strange things with referential integrity and permissions,  i.e. with different permissions on the referenced and referencing tables, but I can't imagine you've stumbled down that path .  Not by doing a dump/restore...

Thanks, Dan- I owe you one!