Thread: a couple of minor itches: RI Trigger Names, and additional Alter ownerships commands.

Hello postgresql-hackers,

Been a while sine I've particiapated on this list so I wanted to say
thank you for the great product postgresql 7.2.1 is!  I have been 
doing some testing in preperation of a database upgrade from 7.0.3
to 7.2.1 and I have a few small itches to scratch, so I thought I'd
get opinions from the experts :)


Itch #1: Referential Integrity trigger names in psql \d output.

Currently the \d ouput from psql displays non-obvious names for 
what the RI trigger actually does.  Reading through the source code
and quering the mailing lists indicate this is easily changed (and 
I have already done this on a test database without any ill effects
so far).

Here is what the \d displays from 7.2.1:

Before:
--------------------------------------------
test=# \d foo         Table "foo"Column  |  Type   | Modifiers 
---------+---------+-----------blah_id | integer | not nullfoo     | text    | not null
Primary key: foo_pkey
Triggers: RI_ConstraintTrigger_30670

test=# \d blah         Table "blah"Column  |  Type   | Modifiers 
---------+---------+-----------blah_id | integer | not nullblah    | text    | not null
Primary key: blah_pkey
Triggers: RI_ConstraintTrigger_30672,         RI_ConstraintTrigger_30674


After:
--------------------------------------------
test=# \d foo         Table "foo"Column  |  Type   | Modifiers 
---------+---------+-----------blah_id | integer | not nullfoo     | text    | not null
Primary key: foo_pkey
Triggers: RI_blah_id (insert)

test=# \d blah        Table "blah"Column  |  Type   | Modifiers 
---------+---------+-----------blah_id | integer | not nullblah    | text    | not null
Primary key: blah_pkey
Triggers: RI_blah_id (delete),         RI_blah_id (update)


This change was made with a simple update to the pg_trigger
system table for the tgname column.

Searching through the code and the mailing list, it looks like
the only constraint to the tgname column is that it needs to be
unique (although the database schema does not inforce this via 
a unique index) since the OID tacked on to the RI_ConstraintTrigger_*
was designed to keep this uniqueness.

What I would propose is to base the RI_* off the constrain name provided
during the RI_trigger creation, if the constrain name is not provided,
then to default to the current nameing scheme.

Can anyone think of side-affects of changing the tgname column in the
pg_trigger system table?  Does this proposal seem like an acceptable
solution?  Would there be interest in this if I provided a patch to do
this?



Itch #2: Alter ownership on a sequence, etc.

Alter table provides the functionality to change the ownership of a
table, but ownership on other structures like sequences, etc can not
be changed without dropping and recreating as the new owner.  Would
there be any interest if I worked on a patch to do this too?



Thanks again for all the hard work and a great database!

- Ryan Bradetich




Re: a couple of minor itches: RI Trigger Names, and additional

From
Joe Conway
Date:
Ryan Bradetich wrote:> Can anyone think of side-affects of changing the tgname column in> the pg_trigger system table?
Doesthis proposal seem like an> acceptable solution?  Would there be interest in this if I provided> a patch to do
this?

FWIW, not exactly what you are proposing, but ALTER TRIGGER RENAME is
available in current CVS. See:
http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-altertrigger.html

Joe



Re: a couple of minor itches: RI Trigger Names, and

From
Ryan Bradetich
Date:
Scrach itch #2.  I just figured out how to change the ownership of a
sequence:
alter table <sequence> owner to <owner>.

Amazing how easy it is to figure out once  you have posted the question
to the mailing list :)

thanks,

- Ryan


On Mon, 2002-05-06 at 11:52, Ryan Bradetich wrote:
.. snip ...
> Itch #2: Alter ownership on a sequence, etc.
> 
> Alter table provides the functionality to change the ownership of a
> table, but ownership on other structures like sequences, etc can not
> be changed without dropping and recreating as the new owner.  Would
> there be any interest if I worked on a patch to do this too?
> 
> 
> 
> Thanks again for all the hard work and a great database!
> 
> - Ryan Bradetich
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 




Re: a couple of minor itches: RI Trigger Names, and

From
Ryan Bradetich
Date:
Joe,

Thanks for the link.  Didn't think to check the patches list :(  I found
and downloaded the patch, but the patch does not apply cleanly (it
depends on some new files that are not present yet like: src/backend/commands/tablecmds.c
)


But from what I have read, and the examples you gave this will do
exactly what I want :)  not automatic, but still gives me the ability to
rename the trigger to something more obvious!

I'll have to play around with this some more and see if I can get it to
apply... or just wait until 7.2.2 comes out :)

Thanks for the excellent patch and pointer!

- Ryan


On Mon, 2002-05-06 at 14:22, Joe Conway wrote:
> Ryan Bradetich wrote:
>  > Can anyone think of side-affects of changing the tgname column in
>  > the pg_trigger system table?  Does this proposal seem like an
>  > acceptable solution?  Would there be interest in this if I provided
>  > a patch to do this?
> 
> FWIW, not exactly what you are proposing, but ALTER TRIGGER RENAME is
> available in current CVS. See:
> http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-altertrigger.html
> 
> Joe
> 
> 




Re: a couple of minor itches: RI Trigger Names, and

From
"Christopher Kings-Lynne"
Date:
> Thanks for the link.  Didn't think to check the patches list :(  I found
> and downloaded the patch, but the patch does not apply cleanly (it
> depends on some new files that are not present yet like:
>     src/backend/commands/tablecmds.c
> )
>
>
> But from what I have read, and the examples you gave this will do
> exactly what I want :)  not automatic, but still gives me the ability to
> rename the trigger to something more obvious!
>
> I'll have to play around with this some more and see if I can get it to
> apply... or just wait until 7.2.2 comes out :)
>
> Thanks for the excellent patch and pointer!

You'll actually have to wait for 7.3 - many months off yet!

In the meantime, can't you just edit the system catalogs directly and then
restart your postmaster, instead of applying a patch that may or may not
work on old sources?

Chris



Re: a couple of minor itches: RI Trigger Names, and

From
Ryan Bradetich
Date:
Yep,

That is what I am doing now.  Joe pointed me to his patch that did it
the SQL way :)  

I would rather not re-invent work that someone else has already and has
committed to the tree.  Joe did a nice job, so I'd rather apply his work
to my older tree if I could.  Looks like it will be too much trouble, so
I'll manually update the system catalogs for now, and patiently wait for
7.3 :)

Thanks,

- Ryan

On Tue, 2002-05-07 at 20:26, Christopher Kings-Lynne wrote:
> > Thanks for the link.  Didn't think to check the patches list :(  I found
> > and downloaded the patch, but the patch does not apply cleanly (it
> > depends on some new files that are not present yet like:
> >     src/backend/commands/tablecmds.c
> > )
> >
> >
> > But from what I have read, and the examples you gave this will do
> > exactly what I want :)  not automatic, but still gives me the ability to
> > rename the trigger to something more obvious!
> >
> > I'll have to play around with this some more and see if I can get it to
> > apply... or just wait until 7.2.2 comes out :)
> >
> > Thanks for the excellent patch and pointer!
> 
> You'll actually have to wait for 7.3 - many months off yet!
> 
> In the meantime, can't you just edit the system catalogs directly and then
> restart your postmaster, instead of applying a patch that may or may not
> work on old sources?
> 
> Chris
> 
> 




Re: a couple of minor itches: RI Trigger Names, and

From
Joe Conway
Date:
> I would rather not re-invent work that someone else has already and has
> committed to the tree.  Joe did a nice job, so I'd rather apply his work
> to my older tree if I could.  Looks like it will be too much trouble, so
> I'll manually update the system catalogs for now, and patiently wait for
> 7.3 :)

Thanks!

I took a quick look at back-patching the REL7_2_STABLE branch, but quite 
a bit has changed since then -- sorry :(

If you do undertake this yourself, please note that Tom Lane cleaned up 
a few things after my patch was committed -- so you'll want his final 
version of the renametrig() function, which is now in 
~/src/backend/commands/trigger.c.

In the meantime, manually updating the system catalogs to rename a 
trigger should work fine, as long as you restart your backends -- see 
Tom's email (if you haven't already) for more detail:
http://archives.postgresql.org/pgsql-hackers/2002-04/msg01091.php

Joe