Re: unlogged sequences

On 06.04.22 11:12, Peter Eisentraut wrote: We could also move forward with this patch independently of the other one.  If we end up reverting the other one, then this one won't be very useful but it won't really hurt anything and it would presumably become useful eventually.  What we presumably

Re: unlogged sequences

On 03.04.22 19:19, Peter Eisentraut wrote: On 01.04.22 18:31, Peter Eisentraut wrote: Consider that an identity sequence creates an "internal" dependency and a serial sequence creates an "auto" dependency. An "internal" dependency means that the internal object shouldn't really be operated o

Re: unlogged sequences

On 04.04.22 01:58, David G. Johnston wrote: "Because pg_dump is used to transfer data to newer versions of PostgreSQL, the output of pg_dump can be expected to load into PostgreSQL server versions newer than pg_dump's version." [1] That is what I'm getting on about when talking about migration

Re: unlogged sequences

On Sun, Apr 3, 2022 at 12:36 PM Peter Eisentraut < peter.eisentr...@enterprisedb.com> wrote: > On 03.04.22 20:50, David G. Johnston wrote: > > However, tables having an identity sequence seem to be unaddressed in > > this patch. The existing (and unchanged) pg_dump.c code results in: > > It is ad

Re: unlogged sequences

On Sun, Apr 3, 2022 at 12:36 PM Peter Eisentraut < peter.eisentr...@enterprisedb.com> wrote: > On 03.04.22 20:50, David G. Johnston wrote: > > However, tables having an identity sequence seem to be unaddressed in > > this patch. The existing (and unchanged) pg_dump.c code results in: > > It is ad

Re: unlogged sequences

On 03.04.22 20:50, David G. Johnston wrote: However, tables having an identity sequence seem to be unaddressed in this patch.  The existing (and unchanged) pg_dump.c code results in: It is addressed. For example, run this in PG14: create unlogged table t1 (a int generated always as identity,

Re: unlogged sequences

On Sun, Apr 3, 2022 at 10:19 AM Peter Eisentraut < peter.eisentr...@enterprisedb.com> wrote: > Here is an updated patch that fixes this pg_dump/pg_upgrade issue and > also adds a few more comments and documentation sentences about what > happens and what is allowed. I didn't change any behaviors;

Re: unlogged sequences

On 01.04.22 18:31, Peter Eisentraut wrote: Consider that an identity sequence creates an "internal" dependency and a serial sequence creates an "auto" dependency. An "internal" dependency means that the internal object shouldn't really be operated on directly.  (In some cases it's allowed for

Re: unlogged sequences

On Fri, Apr 1, 2022 at 12:31 PM Peter Eisentraut wrote: > > An "internal" dependency means that the internal object shouldn't really > > be operated on directly. (In some cases it's allowed for convenience.) > > So I think in that case the sequence must follow the table's persistence > > in all c

Re: unlogged sequences

On Fri, Apr 1, 2022 at 9:31 AM Peter Eisentraut < peter.eisentr...@enterprisedb.com> wrote: > On 01.04.22 18:22, Peter Eisentraut wrote: > > > > On 01.04.22 00:43, Tomas Vondra wrote: > >> Hmm, so what about doing a little bit different thing: > >> > >> 1) owned sequences inherit persistence of th

Re: unlogged sequences

On Fri, Apr 1, 2022 at 9:22 AM Peter Eisentraut < peter.eisentr...@enterprisedb.com> wrote: > > On 01.04.22 00:43, Tomas Vondra wrote: > > Hmm, so what about doing a little bit different thing: > > > > 1) owned sequences inherit persistence of the table by default > > > > 2) allow ALTER SEQUENCE t

Re: unlogged sequences

On 01.04.22 18:22, Peter Eisentraut wrote: On 01.04.22 00:43, Tomas Vondra wrote: Hmm, so what about doing a little bit different thing: 1) owned sequences inherit persistence of the table by default 2) allow ALTER SEQUENCE to change persistence for all sequences (no restriction for owned seq

Re: unlogged sequences

On 01.04.22 00:43, Tomas Vondra wrote: Hmm, so what about doing a little bit different thing: 1) owned sequences inherit persistence of the table by default 2) allow ALTER SEQUENCE to change persistence for all sequences (no restriction for owned sequences) 3) ALTER TABLE ... SET [UN]LOGGED

Re: unlogged sequences

On Thu, Mar 31, 2022 at 6:03 PM Robert Haas wrote: > On Thu, Mar 31, 2022 at 8:44 PM David G. Johnston > wrote: > > > The "give the user power" argument is also valid. But since they > already have power through unowned sequences, having the owned sequences > more narrowly defined doesn't detra

Re: unlogged sequences

On Thu, Mar 31, 2022 at 6:03 PM Robert Haas wrote: > > In this new system, does the user still get a logged sequence? If they > get an unlogged sequence, how does dump-and-restore work? What if they > want to still have a logged sequence? But for sequences that are > simply owned, there is no pro

Re: unlogged sequences

On Thu, Mar 31, 2022 at 8:44 PM David G. Johnston wrote: > It seems reasonable to extend the definition of "ownership of a sequence" in > this way. We always let you create unowned sequences with whatever > persistence you like if you need flexibility. I'd say it doesn't seem to have any benef

Re: unlogged sequences

On Thu, Mar 31, 2022 at 8:42 PM Tomas Vondra wrote: > Well, yeah. I did this because the patch was somewhat inconsistent when > handling owned sequences - it updated persistence for owned sequences > when persistence for the table changed, expecting to keep them in sync, > but then it also allowed

Re: unlogged sequences

On Thu, Mar 31, 2022 at 5:25 PM Robert Haas wrote: > On Thu, Mar 31, 2022 at 10:14 AM Tomas Vondra > wrote: > > * When linking a sequence to a table (ALTER SEQUENCE ... OWNED BY), > > there's an ereport(ERROR) if the relpersistence values do not match. > > > > * Disallow changing persistence for

Re: unlogged sequences

On 4/1/22 02:25, Robert Haas wrote: > On Thu, Mar 31, 2022 at 10:14 AM Tomas Vondra > wrote: >> * When linking a sequence to a table (ALTER SEQUENCE ... OWNED BY), >> there's an ereport(ERROR) if the relpersistence values do not match. >> >> * Disallow changing persistence for owned sequences dire

Re: unlogged sequences

On Thu, Mar 31, 2022 at 10:14 AM Tomas Vondra wrote: > * When linking a sequence to a table (ALTER SEQUENCE ... OWNED BY), > there's an ereport(ERROR) if the relpersistence values do not match. > > * Disallow changing persistence for owned sequences directly. Wait, what? I don't understand why we

Re: unlogged sequences

On Thu, Mar 31, 2022 at 3:43 PM Tomas Vondra wrote: > On 3/31/22 22:40, David G. Johnston wrote: > > On Thu, Mar 31, 2022 at 1:05 PM Tomas Vondra > > mailto:tomas.von...@enterprisedb.com>> > > wrote: > > > > > > I agree the first part is not contentious, so shall we extract this > part > >

Re: unlogged sequences

On 3/31/22 22:40, David G. Johnston wrote: > On Thu, Mar 31, 2022 at 1:05 PM Tomas Vondra > mailto:tomas.von...@enterprisedb.com>> > wrote: > > > I agree the first part is not contentious, so shall we extract this part > of the patch and get that committed for PG15? Or is that too late to

Re: unlogged sequences

On Thu, Mar 31, 2022 at 1:40 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > The DBA just has to execute the ALTER SEQUENCE command on all relevant > sequences. > Additional, if we do not implement the forced matching of persistence mode, we should consider adding an "ALTER TABLE SET

Re: unlogged sequences

On Thu, Mar 31, 2022 at 1:05 PM Tomas Vondra wrote: > > I agree the first part is not contentious, so shall we extract this part > of the patch and get that committed for PG15? Or is that too late to > make such changes to the patch? > > The minimum viable feature for me, given the written goal f

Re: unlogged sequences

On 3/31/22 21:55, David G. Johnston wrote: > On Thu, Mar 31, 2022 at 12:36 PM Tomas Vondra > mailto:tomas.von...@enterprisedb.com>> > wrote: > > On 3/31/22 19:35, David G. Johnston wrote: > > On Thu, Mar 31, 2022 at 9:28 AM Andres Freund > >

Re: unlogged sequences

On Thu, Mar 31, 2022 at 12:36 PM Tomas Vondra wrote: > On 3/31/22 19:35, David G. Johnston wrote: > > On Thu, Mar 31, 2022 at 9:28 AM Andres Freund > > wrote: > > > > I agree it makes sense to have logged sequences with unlogged > tables. We > > should call out

Re: unlogged sequences

On 3/31/22 19:35, David G. Johnston wrote: > On Thu, Mar 31, 2022 at 9:28 AM Andres Freund > wrote: > > I agree it makes sense to have logged sequences with unlogged tables. We > should call out the behavioural change somewhere prominent in the > release >

Re: unlogged sequences

On Thu, Mar 31, 2022 at 9:28 AM Andres Freund wrote: > I agree it makes sense to have logged sequences with unlogged tables. We > should call out the behavioural change somewhere prominent in the release > notes. > > We can/do already support that unlikely use case by allowing one to remove the O

Re: unlogged sequences

Hi, On 2022-03-31 16:14:25 +0200, Tomas Vondra wrote: > 1) Do we need to do something about pg_upgrade? I mean, we did not have > unlogged sequences until now, so existing databases may have unlogged > tables with logged sequences. If people run pg_upgrade, what should be > the end result? Should

Re: unlogged sequences

Hi, Here's a slightly improved patch, adding a couple checks and tests for owned sequences to ensure both objects have the same persistence. In particular: * When linking a sequence to a table (ALTER SEQUENCE ... OWNED BY), there's an ereport(ERROR) if the relpersistence values do not match. * D

Re: unlogged sequences

Patch rebased over some conflicts, and some tests simplified. On 24.03.22 14:10, Peter Eisentraut wrote: Here is an updated patch that now also includes SET LOGGED/UNLOGGED support.  So this version addresses all known issues and open problems. On 28.02.22 10:56, Peter Eisentraut wrote: reba

Re: unlogged sequences

Here is an updated patch that now also includes SET LOGGED/UNLOGGED support. So this version addresses all known issues and open problems. On 28.02.22 10:56, Peter Eisentraut wrote: rebased patch, no functional changes On 11.02.22 10:12, Peter Eisentraut wrote: On 25.06.19 20:37, Andres Fre

Re: unlogged sequences

rebased patch, no functional changes On 11.02.22 10:12, Peter Eisentraut wrote: On 25.06.19 20:37, Andres Freund wrote: I.e. I think it'd be better if we just added a fork argument to fill_seq_with_data(), and then do something like smgrcreate(srel, INIT_FORKNUM, false); log_smgrcreate(&rel->r

Re: unlogged sequences

On 25.06.19 20:37, Andres Freund wrote: I.e. I think it'd be better if we just added a fork argument to fill_seq_with_data(), and then do something like smgrcreate(srel, INIT_FORKNUM, false); log_smgrcreate(&rel->rd_node, INIT_FORKNUM); fill_seq_with_data(rel, tuple, INIT_FORKNUM); and add a Fl

Re: unlogged sequences

On 2019-Aug-01, Thomas Munro wrote: > On Wed, Jun 26, 2019 at 6:38 AM Andres Freund wrote: > > On 2019-06-20 09:30:34 +0200, Peter Eisentraut wrote: > > > I'm looking for feedback from those who have worked on tableam and > > > storage manager to see what the right interfaces are or whether some

Re: unlogged sequences

On Wed, Jun 26, 2019 at 6:38 AM Andres Freund wrote: > On 2019-06-20 09:30:34 +0200, Peter Eisentraut wrote: > > I'm looking for feedback from those who have worked on tableam and > > storage manager to see what the right interfaces are or whether some new > > interfaces might perhaps be appropria

Re: unlogged sequences

Hi, On 2019-06-20 09:30:34 +0200, Peter Eisentraut wrote: > I'm looking for feedback from those who have worked on tableam and > storage manager to see what the right interfaces are or whether some new > interfaces might perhaps be appropriate. Hm, it's not clear to me that tableam design matters

Re: unlogged sequences

On 2019-06-21 07:31, Michael Paquier wrote: > 1) Some SQL queries: > create unlogged sequence popo; > alter sequence popo increment 2; The problem is that the above command does a relation rewrite but the code doesn't know to copy the init fork of the sequence. That will need to be addressed. >

Re: unlogged sequences

On Thu, Jun 20, 2019 at 09:30:34AM +0200, Peter Eisentraut wrote: > The discussion in bug #15631 revealed that serial/identity sequences of > temporary tables should really also be temporary (easy), and that > serial/identity sequences of unlogged tables should also be unlogged. > But there is no s