Обсуждение: newbie: renaming sequences task
Hi - I was perusing the todo list to see some easy items that I might help out on (and get up to speed on postgres hacking)...one of them (with %) seems to lead to another: o %Have ALTER TABLE RENAME rename SERIAL sequence names o Have ALTER SEQUENCE RENAME rename the sequence name storedin the sequence table and perhaps this one as well: Consider placing all sequences in a single table, or create a system view I read through the mailing list links (they seem to culminate with these two): http://archives.postgresql.org/pgsql-bugs/2007-09/msg00141.php http://archives.postgresql.org/pgsql-bugs/2007-10/msg00038.php But I'm left a bit confused on what, if anything, can or should be done. Maybe this isn't the best item to start with? IfI had some more direction, it might be straightforward enough. One thing I did notice: sequence names are stored in both pg_type.typname and pg_class.relkind. I presume both tables wouldneed to be updated, unless we remove the redundancy? Why can they not be updated within a single transaction (easily)?What sort of restructuring would be needed to separate out the transactional vs non-transactional aspects? thanks! --craig ____________________________________________________________________________________ Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs
Oops: meant pg_type.typname and pg_class.relname. ----- Original Message ---- From: craigp <craigp98072@yahoo.com> To: pgsql-hackers@postgresql.org Sent: Sunday, March 2, 2008 2:36:33 AM Subject: newbie: renaming sequences task Hi - I was perusing the todo list to see some easy items that I might help out on (and get up to speed on postgres hacking)... one of them (with %) seems to lead to another: o %Have ALTER TABLE RENAME rename SERIAL sequence names o Have ALTER SEQUENCE RENAME rename the sequence name stored in the sequence table and perhaps this one as well: Consider placing all sequences in a single table, or create a system view I read through the mailing list links (they seem to culminate with these two): http://archives.postgresql.org/pgsql-bugs/2007-09/msg00141.php http://archives.postgresql.org/pgsql-bugs/2007-10/msg00038.php But I'm left a bit confused on what, if anything, can or should be done. Maybe this isn't the best item to start with? If I had some more direction, it might be straightforward enough. One thing I did notice: sequence names are stored in both pg_type.typname and pg_class.relkind. I presume both tables would need to be updated, unless we remove the redundancy? Why can they not be updated within a single transaction (easily)? What sort of restructuring would be needed to separate out the transactional vs non-transactional aspects? thanks! --craig ____________________________________________________________________________________ Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs ____________________________________________________________________________________ Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping
Oops: meant pg_type.typname and pg_class.relname. ----- Original Message ---- From: craigp <craigp98072@yahoo.com> To: pgsql-hackers@postgresql.org Sent: Sunday, March 2, 2008 2:36:33 AM Subject: newbie: renaming sequences task Hi - I was perusing the todo list to see some easy items that I might help out on (and get up to speed on postgres hacking)... one of them (with %) seems to lead to another: o %Have ALTER TABLE RENAME rename SERIAL sequence names o Have ALTER SEQUENCE RENAME rename the sequence name stored in the sequence table and perhaps this one as well: Consider placing all sequences in a single table, or create a system view I read through the mailing list links (they seem to culminate with these two): http://archives.postgresql.org/pgsql-bugs/2007-09/msg00141.php http://archives.postgresql.org/pgsql-bugs/2007-10/msg00038.php But I'm left a bit confused on what, if anything, can or should be done. Maybe this isn't the best item to start with? If I had some more direction, it might be straightforward enough. One thing I did notice: sequence names are stored in both pg_type.typname and pg_class.relkind. I presume both tables would need to be updated, unless we remove the redundancy? Why can they not be updated within a single transaction (easily)? What sort of restructuring would be needed to separate out the transactional vs non-transactional aspects? thanks! --craig ____________________________________________________________________________________ Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs ____________________________________________________________________________________ Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping
craigp <craigp98072@yahoo.com> writes: > I was perusing the todo list to see some easy items that I might help out on (and get up to speed on postgres hacking)...one of them (with %) seems to lead to another: > ... > But I'm left a bit confused on what, if anything, can or should be done. Maybe this isn't the best item to start with?If I had some more direction, it might be straightforward enough. No, the reason those are still on the TODO list is that it's not straightforward. The first one is really not related to the others --- it just proposes that when renaming a table or individual column, we should look for sequences "owned by" that column or columns, and rename them so that they still look like "table_column_seq". This is about 50% straightforward searching of pg_depend, and about 50% dealing with collisions --- if there's already something of that name, you'd need to go through the same type of fallback name selection that's already done when a serial column is first made. (Thinking about it, I kinda wonder whether we even *want* such behavior anymore. In the presence of ALTER SEQUENCE ... OWNED BY, it's entirely possible that an owned sequence has a name that's got nothing to do with table_column_seq, and which the user wouldn't really want us to forcibly rename. Maybe this TODO has been overtaken by events?) The second one is not about that, but about wishing that the sequence name that (for purely historical reasons) is stored in the sequence's data row would track ALTER SEQUENCE RENAME. The problem here is that ordinary sequence operations like nextval() are nontransactional, and it's hard to mix transactional and nontransactional updates of the same row. There's been talk of rearranging the representation of sequences to fix this, but nothing concrete. The only concrete solution offered has been to remove that copy of the name, which would be simple but not backwards compatible. The third one is a semi-independent feature wish, namely for a single system table or view in which all sequences' parameters could be seen. (An example of the usefulness of that is that it's currently extremely hard to get psql's \dS to show sequence parameters, because we can't join dynamically to individual sequences.) The tie between #2 and #3 is mostly just not wanting to repeatedly whack around the representation or user-visible properties of sequences. If we're going to do both things they should happen in the same update. I'm not sure that #2 or #3 is a suitable first hacking project. The real task underlying both of them is "redesign the representation of sequences in a way that has the right combination of transactional and nontransactional behaviors, and try to make sure that it'll scale to lots and lots of sequences". regards, tom lane
--- Tom Lane <tgl@sss.pgh.pa.us> wrote: > The first one is really not related to the others --- it just proposes > that when renaming a table or individual column, we should look for > sequences "owned by" that column or columns, and rename them so that > they still look like "table_column_seq". This is about 50% > straightforward searching of pg_depend, and about 50% dealing with > collisions --- if there's already something of that name, you'd need > to go through the same type of fallback name selection that's already > done when a serial column is first made. > > (Thinking about it, I kinda wonder whether we even *want* such behavior > anymore. In the presence of ALTER SEQUENCE ... OWNED BY, it's entirely > possible that an owned sequence has a name that's got nothing to do with > table_column_seq, and which the user wouldn't really want us to forcibly > rename. Maybe this TODO has been overtaken by events?) Well, if we just look at the first one, I wonder (as well) whether or not it is useful (and as you said, perhaps surprising in some cases). I did a quick test, and neither sequences nor primary keys are renamed [1]. Maybe this should be the default behaviour, unless we give provide some other option to ALTER TABLE RENAME? Another possibility might be to only rename sequences and/or primary keys which had been created implicitly during table creation (assuming that information is tracked)? [1] create table t1 (id serial8 primary key not null) alter table t1 rename to t2 pk still named t1_pkey sequence still named t1_id_seq ____________________________________________________________________________________ Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping