Thread: Pseudo modification of views and triggers (again)

Pseudo modification of views and triggers (again)

From
Jean-Michel POURE
Date:
Dear Dave,

1) Views
View pseudo-modification works fine. It was implemented in
pgSchema->pgView->Let Defition property. This allows the modification of view
definition only (not the name of the view).

This is OK because view names should not be changed (views can be called
inside functions or other views).

The problem with pgSchema->pgView->Let Defition is that if a wrong definition
is entered, an error is reported and the definition is saved in PostgreSQL.
This ***might*** be a new feature of PostgreSQL 7.2. I don't remember 7.1
accepted wrong definitions for views, but I may be wrong.

We need to rollback views in such a case.

Does this means implementing view modification at pgSchema->View level?

2) Triggers
It should be possible to change the name of a trigger as well as the
underlying function, but not the trigger table itself. Therefore, it sounds
like trigger pseudo modification should be implemented in pgSchema->Triggers.
What is your opinion?

3) CVS commit
The current implementation of views works well. Therefore, I will commit it
to CVS in a few minutes so that you can review the code.

Cheers,
Jean-Michel


Re: Pseudo modification of views and triggers (again)

From
Dave Page
Date:

> -----Original Message-----
> From: Jean-Michel POURE [mailto:jm.poure@freesurf.fr]
> Sent: 19 February 2002 19:53
> To: Dave Page
> Cc: pgadmin-hackers@postgresql.org
> Subject: Pseudo modification of views and triggers (again)
>
>
> Dear Dave,
>
> 1) Views
> View pseudo-modification works fine. It was implemented in
> pgSchema->pgView->Let Defition property. This allows the
> modification of
> pgSchema->pgView->view
> definition only (not the name of the view).

The code looks great. I've made a couple of minor changes:

- In pgSchema, I've added code to commit the change to the RCS. This was
also missing from the Function modification code.

- In pgAdmin.frmView:Initialise I've moved the code that unlocks the hbx.
This is to be consistant with other similar functions.

- In pgAdmin.frmView:cmdOK_Click I've removed the code that saved the
comment before updating the definition. Just updating the comment first
should do fine.

> This is OK because view names should not be changed (views
> can be called
> inside functions or other views).

I've just found out that views can be renamed using ALTER TABLE <viewname>
RENAME... iirc, VIEWs are referred to by OID from PL/SQL functions & other
views, and by name from pl/pgsql functions.

Sequences can also be renamed in this way.

It might be an idea if you warned the user that some things will break when
the view is redefined in cmdOK_Click, and give them a chance to abort.

>
> The problem with pgSchema->pgView->Let Defition is that if a
> wrong definition
> is entered, an error is reported and the definition is saved
> in PostgreSQL.
> This ***might*** be a new feature of PostgreSQL 7.2. I don't
> remember 7.1
> accepted wrong definitions for views, but I may be wrong.
>
> We need to rollback views in such a case.
>
> Does this means implementing view modification at
> pgSchema->View level?

7.2:

helpdesk=# create view cow as select widget from notable;
ERROR:  Relation "notable" does not exist
ERROR:  Relation "notable" does not exist
helpdesk=# create view cow as select widget from pg_class;
ERROR:  Attribute 'widget' not found
ERROR:  Attribute 'widget' not found
helpdesk=# create view cow as selects * from pg_class;
ERROR:  parser: parse error at or near "selects"
ERROR:  parser: parse error at or near "selects"
helpdesk=#

I would suggest (in pgSchema) saving the definition in a local variable
before the update attempt, then if an error occurs, re-run the old SQL.

NOTE: I also noticed that you do not retrieve the new OID. This could be
done along with the definition.

> 2) Triggers
> It should be possible to change the name of a trigger as well as the
> underlying function, but not the trigger table itself.
> Therefore, it sounds
> like trigger pseudo modification should be implemented in
> pgSchema->Triggers.
> What is your opinion?

Triggers could be implemented exactly as Views. Each property will need to
be handled seperately, though in the future we might look into merging
multiple updates into one.

The Name property must be handled differently (as it would be for Views &
Sequences) in the Collection class. This has already been done for tables,
so there is some example code to steal.

Regards, Dave.

Re: Pseudo modification of views and triggers (again)

From
Jean-Michel POURE
Date:
> I've just found out that views can be renamed using ALTER TABLE <viewname>
> RENAME... iirc, VIEWs are referred to by OID from PL/SQL functions & other
> views, and by name from pl/pgsql functions.
OK. Since we can modify PL/pgSQL functions, it is no problem.
It is a pitty we don't have CREATE OR REPLACE VIEW anyway.

> Sequences can also be renamed in this way.
Great. It also works for indexes. So interesting.

> I would suggest (in pgSchema) saving the definition in a local variable
> before the update attempt, then if an error occurs, re-run the old SQL.
Yes.

> NOTE: I also noticed that you do not retrieve the new OID. This could be
> done along with the definition.
Yes, you are right.

> Triggers could be implemented exactly as Views. Each property will need to
> be handled seperately, though in the future we might look into merging
> multiple updates into one.
Can we rename a trigger with ALTER TABLE RENAME ?

> The Name property must be handled differently (as it would be for Views &
> Sequences) in the Collection class. This has already been done for tables,
> so there is some example code to steal.
You already did 99% of the job and I wron't complain about it.

The next big thing now is table pseudo-modification. We should be able to
provide a very nice solution.

Cheers,
Jean-Michel