Re: Renaming sequences
Mike Dewhirst <[email protected]> Wed, 18 Dec 2019 17:23:02 +1100
| Newsgroups | gmane.comp.db.postgresql.novice |
|---|---|
| Message-ID | <[email protected]> |
This is a multi-part message in MIME format. --------------29A43A64D0C87F75399EEAE7 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 18/12/2019 5:03 pm, Zahid Rahman wrote: > > If you are referring to a sequence which meets the same definition > below. That is to say a persistent number generator. > > Surely by changing the sequence name then any code using that > sequence by name will fail. That's from an application developer's view. > Yes. I think I'd better ask on the Django users list. The fact that it is working with mismatched names probably means it would stop working if I adjusted them. Django probably reads the migration record to establish which sequence to use. Thanks > > A *sequence* in*PostgreSQL* is a user-defined schema-bound object that > generates a*sequence* of integers based on a specified specification. > To create a *sequence* in *PostgreSQL*, you use the CREATE *SEQUENCE > *statement. > > After a sequence is created, you use the > > functions |nextval|, |currval|, and |setval| to operate on the > sequence. These functions are documented in Section 9.16 > <https://www.postgresql.org/docs/9.5/functions-sequence.html>. > > Although you cannot update a sequence directly, you can use a query like: > > SELECT * FROMname; > > > On Wed, 18 Dec 2019, 05:33 Mike Dewhirst, <[email protected] > <mailto:[email protected]>> wrote: > > Are there any consequences for renaming sequences to match the > tables which own them? > > In an existing production Django project I have just converted > auth.user into common.user and company.userprofile into > common.userprofile. > > Having gone through the migration process more or less unscathed > the original sequences are owned by the renamed tables. Eg > public.auth_user_id_seq is owned by public.common_user.id > <http://public.common_user.id> > > Everything seems to work fine but my unit tests are playing up and > error messages are showing the original (and still correct) > sequence names. It would make much visual sense to me now and > especially to the future me (or anyone else) if the sequences were > renamed as well. > > I know how I could do it but I just need to know if I should. > > Thanks for any advice > > Mike > --------------29A43A64D0C87F75399EEAE7 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> </head> <body text="#000000" bgcolor="#FFFFFF"> <div class="moz-cite-prefix">On 18/12/2019 5:03 pm, Zahid Rahman wrote:<br> </div> <blockquote type="cite" cite="mid:CAPGSW3SRLw=NDMnJ0FDfnCR1Ld3Kvqdy4aROgxy5tjvJQAzLGQ@mail.gmail.com"> <meta http-equiv="content-type" content="text/html; charset=UTF-8"> <div dir="auto"> <div> <p style="margin-top:0px;color:rgb(13,10,11);font-family:"open sans",sans-serif;font-size:14.4px;background-color:rgb(255,255,255);margin-bottom:1rem!important">If you are referring to a sequence which meets the same definition below. That is to say a persistent number generator. </p> <p style="margin-top:0px;color:rgb(13,10,11);font-family:"open sans",sans-serif;font-size:14.4px;background-color:rgb(255,255,255);margin-bottom:1rem!important">Surely by changing the sequence name then any code using that sequence by name will fail. That's from an application developer's view.</p> </div> </div> </blockquote> <br> Yes. <br> <br> I think I'd better ask on the Django users list. <br> <br> The fact that it is working with mismatched names probably means it would stop working if I adjusted them. Django probably reads the migration record to establish which sequence to use.<br> <br> Thanks<br> <br> <br> <blockquote type="cite" cite="mid:CAPGSW3SRLw=NDMnJ0FDfnCR1Ld3Kvqdy4aROgxy5tjvJQAzLGQ@mail.gmail.com"> <div dir="auto"> <div> <p style="margin-top:0px;color:rgb(13,10,11);font-family:"open sans",sans-serif;font-size:14.4px;background-color:rgb(255,255,255);margin-bottom:1rem!important"><br> </p> <p style="margin-top:0px;color:rgb(13,10,11);font-family:"open sans",sans-serif;font-size:14.4px;background-color:rgb(255,255,255);margin-bottom:1rem!important"><span style="color:rgb(60,64,67);font-family:roboto,helveticaneue,arial,sans-serif;font-size:18px">A </span><b style="max-height:999999px;color:rgb(60,64,67);font-family:roboto,helveticaneue,arial,sans-serif;font-size:18px">sequence</b><span style="color:rgb(60,64,67);font-family:roboto,helveticaneue,arial,sans-serif;font-size:18px"> in</span><b style="max-height:999999px;color:rgb(60,64,67);font-family:roboto,helveticaneue,arial,sans-serif;font-size:18px">PostgreSQL</b><span style="color:rgb(60,64,67);font-family:roboto,helveticaneue,arial,sans-serif;font-size:18px"> is a user-defined schema-bound object that generates a</span><b style="max-height:999999px;color:rgb(60,64,67);font-family:roboto,helveticaneue,arial,sans-serif;font-size:18px">sequence</b><span style="color:rgb(60,64,67);font-family:roboto,helveticaneue,arial,sans-serif;font-size:18px"> of integers based on a specified specification. To create a </span><b style="max-height:999999px;color:rgb(60,64,67);font-family:roboto,helveticaneue,arial,sans-serif;font-size:18px">sequence</b><span style="color:rgb(60,64,67);font-family:roboto,helveticaneue,arial,sans-serif;font-size:18px"> in </span><b style="max-height:999999px;color:rgb(60,64,67);font-family:roboto,helveticaneue,arial,sans-serif;font-size:18px">PostgreSQL</b><span style="color:rgb(60,64,67);font-family:roboto,helveticaneue,arial,sans-serif;font-size:18px">, you use the CREATE </span><b style="max-height:999999px;color:rgb(60,64,67);font-family:roboto,helveticaneue,arial,sans-serif;font-size:18px">SEQUENCE </b><span style="color:rgb(60,64,67);font-family:roboto,helveticaneue,arial,sans-serif;font-size:18px">statement.</span></p> <p style="margin-top:0px;color:rgb(13,10,11);font-family:"open sans",sans-serif;font-size:14.4px;background-color:rgb(255,255,255);margin-bottom:1rem!important">After a sequence is created, you use the </p> <p style="margin-top:0px;color:rgb(13,10,11);font-family:"open sans",sans-serif;font-size:14.4px;background-color:rgb(255,255,255);margin-bottom:1rem!important">functions <code style="font-family:monospace,monospace;font-size:1em;border-radius:0.25rem;margin:0.6rem 0px;color:inherit!important;background-color:rgb(248,249,250)!important">nextval</code>, <code style="font-family:monospace,monospace;font-size:1em;border-radius:0.25rem;margin:0.6rem 0px;color:inherit!important;background-color:rgb(248,249,250)!important">currval</code>, and <code style="font-family:monospace,monospace;font-size:1em;border-radius:0.25rem;margin:0.6rem 0px;color:inherit!important;background-color:rgb(248,249,250)!important">setval</code> to operate on the sequence. These functions are documented in <a href="https://www.postgresql.org/docs/9.5/functions-sequence.html" style="color:rgb(132,0,50);text-decoration-line:none;background-color:transparent;font-weight:600" rel="noreferrer noreferrer" target="_blank" moz-do-not-send="true">Section 9.16</a>.</p> <p style="margin-top:0px;color:rgb(13,10,11);font-family:"open sans",sans-serif;font-size:14.4px;background-color:rgb(255,255,255);margin-bottom:1rem!important">Although you cannot update a sequence directly, you can use a query like:</p> <pre style="font-family:monospace,monospace;font-size:14.4px;color:rgb(13,10,11);border-radius:0.25rem;border:1px solid rgb(206,212,218);margin-top:1rem!important;margin-bottom:1rem!important;background-color:rgb(248,249,250)!important;padding:0.8rem!important">SELECT * FROM <tt style="font-weight:900;font-style:italic;border-radius:0.25rem;margin:0.6rem 0px;font-size:0.9rem!important;color:inherit!important">name</tt>;</pre> <br> <br> <div class="gmail_quote"> <div dir="ltr" class="gmail_attr">On Wed, 18 Dec 2019, 05:33 Mike Dewhirst, <<a href="mailto:[email protected]" rel="noreferrer noreferrer" target="_blank" moz-do-not-send="true">[email protected]</a>> wrote:<br> </div> <blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> <div text="#000000" bgcolor="#FFFFFF"> <font size="-1">Are there any consequences for renaming sequences to match the tables which own them?<br> <br> In an existing production Django project I have just converted auth.user into common.user and company.userprofile into common.userprofile.<br> <br> Having gone through the migration process more or less unscathed the original sequences are owned by the renamed tables. Eg public.auth_user_id_seq is owned by <a href="http://public.common_user.id" rel="noreferrer noreferrer noreferrer" target="_blank" moz-do-not-send="true">public.common_user.id</a><br> <br> Everything seems to work fine but my unit tests are playing up and error messages are showing the original (and still correct) sequence names. It would make much visual sense to me now and especially to the future me (or anyone else) if the sequences were renamed as well.<br> <br> I know how I could do it but I just need to know if I should.<br> <br> Thanks for any advice<br> <br> Mike<br> <br> </font> </div> </blockquote> </div> </div> </div> </blockquote> <br> </body> </html> --------------29A43A64D0C87F75399EEAE7--