Forum OpenACS Development: update instead of falling on duplicate acs_objects

Collapse
1: update instead of falling on duplicate acs_objects
Posted by Eduardo Pérez on 06/17/04 05:31 PM
When doing development or just reinstalling half installed OpenACS packages I get:

psql:package-create.sql:1: ERROR:  duplicate key violates unique constraint "acs_obj_types_pretty_name_un"
CONTEXT:  PL/pgSQL function "acs_object_type__create_type" line 7 at SQL statement
PL/pgSQL function "inline_0" line 3 at perform

Wouldn't it be easier to just update that object with new values instead of falling?
As I said this is very common when reinstalling half installed OpenACS packages and many times total removal prior reinstallation is not easy as usually is not possible.

Collapse
2: update instead of falling on create table (response to 1)
Posted by Eduardo Pérez on 06/17/04 06:19 PM
What about tables?
Is there a way to update a table if it's already created without destroying the data it contains?
It just should add the columns that doesn't exist in the create and modify the columns to the new types if the type is changed, really throwing an error if there's data to be destroyed because there's no column on the new create or the new column type can't have the data it had previously.
Collapse
3: Re: update instead of falling on duplicate acs_objects (response to 1)
Posted by Eduardo Pérez on 06/17/04 08:11 PM
I just want to add, this is already done (as you know) with functions:
"create or replace function "
so, why not doing it with every other database object?
Collapse
4: Re: update instead of falling on duplicate acs_objects (response to 1)
Posted by Don Baccus on 06/18/04 01:42 AM
With tables you have to write explicit ALTER TABLE commands to add columns, and you have to know which columns already exist when you do so.

We only do this in old-version-to-new-version update files.

There's nothing in SQL for tables that is equivalent to CREATE OR REPLACE ...

Collapse
5: Re: update instead of falling on duplicate acs_objects (response to 4)
Posted by Eduardo Pérez on 06/18/04 12:39 PM
<blockquote> With tables you have to write explicit ALTER TABLE commands
to add columns, and you have to know which columns already
exist when you do so.
</blockquote>

I know that. I'm asking if it would be a good idea to have a "create or update table " command in (Posgre)SQL to do that updates more easily.

<blockquote> We only do this in old-version-to-new-version update files.
</blockquote>

Of course old-version-to-new-version update files would still be needed but at least "create or update table " wouldn't fail when the table is already created.

<blockquote> There's nothing in SQL for tables that is equivalent to CREATE OR REPLACE ...
</blockquote>

That's what I'm suggesting having something like "create or update table " in (Postgre)SQL.

Collapse
6: Re: update instead of falling on duplicate acs_objects (response to 5)
Posted by Jeff Davis on 06/18/04 02:24 PM
I seriously doubt a "create or update table" would be considered by the postgres team, but if you want to persue it, this is the wrong venue pgsql-hackers or the general list would be better.

There also isn't anything even vaguely like that in any of the SQL standards I don't think.

You might be able to write a plpgsql function that took did it but it would be pretty hairy I think (and to be honest I think the better solution for development is just having drop scripts that work even if the datamodel is only part installed).

Helper functions to do things like check for index existence before creating or dropping an index would be great to have and would probably make upgrading quite a bit more robust.

Collapse
7: Re: update instead of falling on duplicate acs_objects (response to 6)
Posted by Eduardo Pérez on 06/18/04 05:00 PM
<blockquote> I seriously doubt a "create or update table" would be
considered by the postgres team, but if you want to persue
it, this is the wrong venue pgsql-hackers or the general
list would be better.
</blockquote>

I'm asking here first to know if this is a crazy idea or not.

<blockquote> There also isn't anything even vaguely like that in any of
the SQL standards I don't think.
</blockquote>

I also think so, but it may help many SQL applications including OpenACS.

<blockquote> You might be able to write a plpgsql function that took
did it but it would be pretty hairy I think (and to be
honest I think the better solution for development is just
having drop scripts that work even if the datamodel is
only part installed).
</blockquote>

They work OK when they are perfect but in case of any problem you can get a broken app.

<blockquote> Helper functions to do things like check for index
existence before creating or dropping an index would be
great to have and would probably make upgrading quite a
bit more robust.
</blockquote>

What about not falling on "create index" (if there's and index do nothing) or "drop index" (if there's no index do nothing)?

What about "update instead of falling on duplicate acs_objects"?

Collapse
8: Re: update instead of falling on duplicate acs_objects (response to 7)
Posted by Jeff Davis on 06/18/04 05:25 PM
I'm asking here first to know if this is a crazy idea or not.
It is crazy (at least crazy to think anyone who could do it would be interested in doing it).

The fact that nothing like it exists in SQL99 is indicative that it's unlikely that they will be interested. I don't dispute that it might be useful in some circumstances, it's just not going to be implemented.

They work OK when they are perfect but in case of any problem you can get a broken app.

Which is true of most computer things. The work to make the drop script work "perfectly" is a *lot* less than the work to make install scripts magically be able to install on top of a partial install.

What about not falling on "create index" (if there's and index do nothing) or "drop index" (if there's no index do nothing)?
Yes, that's exactly what I meant.

As for updating instead of failing on duplicate acs_object_types, I don;t think you could write something that did not run the risk of updating the wrong acs_object_type. I think I would prefer an error in this case.

Collapse
9: Re: update instead of falling on duplicate acs_objects (response to 8)
Posted by Eduardo Pérez on 06/28/04 05:53 PM
<blockquote> As for updating instead of failing on duplicate
acs_object_types, I don;t think you could write something that
did not run the risk of updating the wrong acs_object_type.
I think I would prefer an error in this case.
</blockquote>

Most system administrators (users) don't like errors. They prefer getting the package installed.
"updating the wrong acs_object_type" is an error anyway and I prefer getting the application working.

Collapse
10: Re: update instead of falling on duplicate acs_objects (response to 1)
Posted by Eduardo Pérez on 03/07/05 03:29 PM
Because this problem hit me lots of times in the pasts days I made a patch:
https://openacs.org/bugtracker/openacs/patch?patch_number=650
I still think it's a good idea, don't you?
Collapse
11: Re: update instead of falling on duplicate acs_objects (response to 1)
Posted by Dave Bauer on 03/07/05 03:42 PM
I think you should backup your database before making major changes. If there is an error, go to the backup.

Fix or find someone to fix the error in the install of the new package, then try again.

I think magically doing something unexpected is a bad idea in this case and will lead to strange unexplained errors instead of a straigtforward error on intsall.

Collapse
12: Re: update instead of falling on duplicate acs_objects (response to 1)
Posted by Eduardo Pérez on 03/07/05 04:14 PM
I think you should backup your database before making major changes. If there is an error, go to the backup.

I already do. But that doesn't fix all the problems.

Fix or find someone to fix the error in the install of the new package, then try again.

What's in the database is already broken so that wouldn't help.

I think magically doing something unexpected is a bad idea in this case and will lead to strange unexplained errors instead of a straightforward error on install.

There's nothing magical in an update.
What are those unexplained errors? (Could you give some example?)

Collapse
13: Re: update instead of falling on duplicate acs_objects (response to 1)
Posted by Dave Bauer on 03/07/05 04:24 PM
I don't understand the issue here.

You pass in invalid data and get an appropriate response.

Call acs_content_type__drop_type to remove an already existing type to recreate it. The APIs support this.

There is an effort to add a Tcl API for object types, so we can probably offer better error messages, but I think erroring is the right thing to do in this case.

Collapse
14: Re: update instead of falling on duplicate acs_objects (response to 1)
Posted by Eduardo Pérez on 03/07/05 07:07 PM
You pass in invalid data and get an appropriate response.

No, the data is valid I just want to update it.

Call acs_content_type__drop_type to remove an already existing type to recreate it. The APIs support this.

What I'm proposing is easier and acs_content_type__drop_type doesn't seem to work if there's already data in the DB.

There is an effort to add a Tcl API for object types, so we can probably offer better error messages, but I think erroring is the right thing to do in this case.

I don't like errors.

Could you tell me the problems my approach causes?

Collapse
15: Re: update instead of falling on duplicate acs_objects (response to 1)
Posted by Dave Bauer on 03/07/05 07:16 PM
acs_attribute__create_attribute DOES work fine. It can create (and drop can remove) attribute columns.

What you can't do is create a type that already exists.

Feel free to update the acs_object_types table if you want to change something. In practice I can't imagine you would need to update an object type very often besides adding or removing attributes.