Thread: a couple of minor itches: RI Trigger Names, and additional Alter ownerships commands.
a couple of minor itches: RI Trigger Names, and additional Alter ownerships commands.
From
Ryan Bradetich
Date:
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
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
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) >
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 > >
> 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
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 > >
> 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