Hi!
Our admin upgraded PostgreSQL 7.2.3 to 7.3.1 a while ago (dump and
restore).
Previously I had a table with a long name (>32 chars), which at creation
was truncated to proper length. The table was used from a perl-script
(using the full name) doing mostly DELETE's and INSERT's. The user
accessing the table had been granted SELECT, UPDATE and INSERT.
After the upgrade the calls to the table didn't truncate the name any
longer (as expected), and thus didn't find the table. So I renamed the
table to it's full length name, and now the scripts did find the table
again. But - deletes were no longer allowed! Selects are ok, and so are
inserts and updates, but delete gives "permission denied".
I've verified that grants are the same as before. Altering the table
name to the previous short version gives the same result.
Putting away the data in a temp table, dropping the problematic one and
recreating it made the whole thing work again.
Below is the declaration used (before and after the upgrade):
Regards,
Andreas
drop table gradsite_dev_course_classification;
create table gradsite_dev_course_classification (
cid integer
references gradsite_dev_courses (cid)
on delete cascade,
class integer
references gradsite_dev_course_classes (class)
on delete cascade,
primary key (cid, class)
);
create index gradsite_dev_course_classification_cid on
gradsite_course_classification (cid);
create index gradsite_dev_course_classification_class on
gradsite_course_classification (class);
grant select,insert,update,delete on gradsite_dev_course_classification
to gradsite;