Forum OpenACS Q&A: myfirstpackage: ERROR: duplicate key violates unique constraint

qp@int:/var/lib/aolserver/qp/packages/myfirstpackage/sql/postgresql$ psql -f myfirstpackage-drop.sql content_folder__unregister_content_type
-----------------------------------------
0
(1 row)

psql:myfirstpackage-drop.sql:12: NOTICE: function mfp_notes_f(mfp_notesi) depends on type mfp_notesi
CONTEXT: SQL statement "drop view mfp_notesi"
PL/pgSQL function "content_type__drop_type" line 90 at execute statement
psql:myfirstpackage-drop.sql:12: ERROR: cannot drop view mfp_notesi because other objects depend on it
HINT: Use DROP ... CASCADE to drop the dependent objects too.
CONTEXT: SQL statement "drop view mfp_notesi"
PL/pgSQL function "content_type__drop_type" line 90 at execute statement

Then, I got the following error when I tried to recreate the database:

ERROR: duplicate key violates unique constraint "acs_object_types_pk"
CONTEXT: SQL statement "insert into acs_object_types (object_type, pretty_name, pretty_plural, supertype, table_name, id_column, abstract_p, type_extension_tab le, package_name, name_method) values ( $1 , $2 , $3 , $4 , $5 , $6 , $7 , $8 , $9 , $10 )"
PL/pgSQL function "acs_object_type__create_type" line 37 at SQL statement
SQL statement "SELECT acs_object_type__create_type ( $1 , $2 , $3 , $4 , $ 5 , $6 , null, 'f', null, $7 )"
PL/pgSQL function "content_type__create_type" line 41 at perform content_folder__register_content_type

I uninstalled the package from the package manager, then reinstalled it but still got the same errors. I'm not sure what went wrong, maybe a typo? (Again, argh). Since I don't have any data yet, is there any way to purge it altogether and clean out any problematic catalog entries? I'd even be content if I could purge everything under $OPENACS_SERVICE_NAME and resinstall openACS without having to start at the very beginning (Postgres) again.

Sorry for all the beginner's posts... Hopefully will get going on the learning curve...

Rick

Unfortunately, after double checking the create call file, I pasted over it to try again, so I can't be absolutely sure at this point.

If I assume that I did make a type in the type name, is there anyway to undo the mess?

Rick

I chatted with Dave briefly about it. He mentioned it could have something to do with a change in the way views work so that they are not getting dropped?

code should be:
permission::require_permission \
-object_id $package_id \
-privilege admin

myfirstpackage-drop.sql:

create or replace function content_type__drop_type(varchar,bool,bool) returns int4 as '
        declare
          drop_type__content_type          alias for $1;
          drop_type__drop_children_p        alias for $2;
          drop_type__drop_table_p          alias for $3;
          table_exists_p                      boolean;
          v_table_name                      varchar;
          is_subclassed_p                  boolean;
          child_rec                        record;
          attr_row                          record;
        begin


          select
            count(*) > 0 into is_subclassed_p
          from
            acs_object_types
          where supertype = drop_type__content_type;


          if drop_type__drop_children_p and is_subclassed_p then

            for child_rec in select
                              object_type
                            from
                              acs_object_types
                            where
                              supertype = drop_type__content_type
            LOOP
              PERFORM content_type__drop_type(child_rec.object_type, ''t'', drop_type__drop_table_p);
            end LOOP;

          end if;

          for attr_row in select
                            attribute_name
                          from
                            acs_attributes
                          where
                            object_type = drop_type__content_type
          LOOP
            PERFORM content_type__drop_attribute(drop_type__content_type,
                                                attr_row.attribute_name,
                                                ''f''
            );
          end LOOP;


          select
            table_exists(lower(table_name)) into table_exists_p
          from
            acs_object_types
          where
            object_type = drop_type__content_type;

          if table_exists_p and drop_type__drop_table_p then
            select
              table_name into v_table_name
            from
              acs_object_types
            where
              object_type = drop_type__content_type;


            execute ''drop table '' || v_table_name || '' cascade'';
          end if;

          PERFORM acs_object_type__drop_type(drop_type__content_type, ''f'');

          return 0;
        end;' language 'plpgsql';

select content_folder__unregister_content_type(-100,'mfp_note','t');

select content_type__drop_type(
          'mfp_note',
          't',
          't'
    );

Sorry for the long posting ;). Comments are appreciated, I am not sure this is correct but as far as I checked all the references to mfp_notes are gone now.