BUG #15954: Unable to alter partitioned table to set logged

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #15954: Unable to alter partitioned table to set logged

PG Doc comments form
The following bug has been logged on the website:

Bug reference:      15954
Logged by:          Efrain Berdecia
Email address:      [hidden email]
PostgreSQL version: 11.1
Operating system:   Centos7
Description:        

I have a partition table that I created unlogged. I'm using pg_partman to
manage the partition. I then proceeded to load data into it with a COPY
command.

Afterwards, I ran an alter table set logged on the parent table but the
table still shows as UNLOGGED along with all its children.

Is this broken?

I'm running pg11.1

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15954: Unable to alter partitioned table to set logged

Amit Langote
On Wed, Aug 14, 2019 at 3:06 AM PG Bug reporting form
<[hidden email]> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      15954
> Logged by:          Efrain Berdecia
> Email address:      [hidden email]
> PostgreSQL version: 11.1
> Operating system:   Centos7
> Description:
>
> I have a partition table that I created unlogged. I'm using pg_partman to
> manage the partition. I then proceeded to load data into it with a COPY
> command.
>
> Afterwards, I ran an alter table set logged on the parent table but the
> table still shows as UNLOGGED along with all its children.
>
> Is this broken?

It is somewhat.  A workaround is to perform ALTER TABLE SET LOGGED
individually on each partition.  Specifying LOGGED / UNLOGGED for the
parent table is useless as things stand now.

Are you sure by the way that the individual partitions are themselves
UNLOGGED when you created them?  Partitions don't inherit the
logged-ness property from the parent table, so you must explicitly
create a partition using CREATE UNLOGGED TABLE ... PARTITION OF ... if
you want it to be unlogged.

Thanks,
Amit


Reply | Threaded
Open this post in threaded view
|

Re: BUG #15954: Unable to alter partitioned table to set logged

David Rowley-3
On Wed, 14 Aug 2019 at 13:47, Amit Langote <[hidden email]> wrote:

>
> On Wed, Aug 14, 2019 at 3:06 AM PG Bug reporting form
> > I have a partition table that I created unlogged. I'm using pg_partman to
> > manage the partition. I then proceeded to load data into it with a COPY
> > command.
> >
> > Afterwards, I ran an alter table set logged on the parent table but the
> > table still shows as UNLOGGED along with all its children.
> >
> > Is this broken?
>
> It is somewhat.  A workaround is to perform ALTER TABLE SET LOGGED
> individually on each partition.  Specifying LOGGED / UNLOGGED for the
> parent table is useless as things stand now.

I'd say it's broken in a sense that we can create an unlogged
partitioned table in the first place.  I think that should have been
blocked.

As for why ALTER TABLE <partitioned_table> SET LOGGED; does not work,
that's due to ATRewriteTables() skipping relations without storage in:

/* Relations without storage may be ignored here */
if (!RELKIND_HAS_STORAGE(tab->relkind))
continue;

meaning we never get to:

/*
* Select persistence of transient table (same as original unless
* user requested a change)
*/
persistence = tab->chgPersistence ?
tab->newrelpersistence : OldHeap->rd_rel->relpersistence;

I'm not too sure what we can do to fix this though. Ideally, we'd just
block UNLOGGED partitioned tables, but we can't really do that as a
bug fix in back branches since it might break someone's code.

We could maybe just fix it in master...

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: BUG #15954: Unable to alter partitioned table to set logged

Michael Paquier-2
On Wed, Aug 14, 2019 at 01:57:26PM +1200, David Rowley wrote:
> I'd say it's broken in a sense that we can create an unlogged
> partitioned table in the first place.  I think that should have been
> blocked.

I don't think that it is that crazy to be able to define a partitioned
table as unlogged, assuming that we could use that to make the
children inherit the same state.  We don't allow a mix of temporary
tables and permanent tables as temp table's state does not persist
after the session ends leading to a mess with dependency handling for
the partition tree, but we cannot have that problem with unlogged
tables.
--
Michael

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: BUG #15954: Unable to alter partitioned table to set log

Efrain J. Berdecia
In reply to this post by David Rowley-3
Is there any way to check in the code whether the table is a parent and then allow the execution of the alter accordingly?

Or are you saying that any empty table we would not be able to change the unlogged state?

Should the logic to alter the table just ignore whether the table is empty or not?


On Tue, Aug 13, 2019 at 9:57 PM, David Rowley
On Wed, 14 Aug 2019 at 13:47, Amit Langote <[hidden email]> wrote:

>
> On Wed, Aug 14, 2019 at 3:06 AM PG Bug reporting form
> > I have a partition table that I created unlogged. I'm using pg_partman to
> > manage the partition. I then proceeded to load data into it with a COPY
> > command.
> >
> > Afterwards, I ran an alter table set logged on the parent table but the
> > table still shows as UNLOGGED along with all its children.
> >
> > Is this broken?
>
> It is somewhat.  A workaround is to perform ALTER TABLE SET LOGGED
> individually on each partition.  Specifying LOGGED / UNLOGGED for the
> parent table is useless as things stand now.

I'd say it's broken in a sense that we can create an unlogged
partitioned table in the first place.  I think that should have been
blocked.

As for why ALTER TABLE <partitioned_table> SET LOGGED; does not work,
that's due to ATRewriteTables() skipping relations without storage in:

/* Relations without storage may be ignored here */
if (!RELKIND_HAS_STORAGE(tab->relkind))
continue;

meaning we never get to:

/*
* Select persistence of transient table (same as original unless
* user requested a change)
*/
persistence = tab->chgPersistence ?
tab->newrelpersistence : OldHeap->rd_rel->relpersistence;

I'm not too sure what we can do to fix this though. Ideally, we'd just
block UNLOGGED partitioned tables, but we can't really do that as a
bug fix in back branches since it might break someone's code.

We could maybe just fix it in master...

--
David Rowley                  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15954: Unable to alter partitioned table to set logged

David Rowley-3
In reply to this post by Michael Paquier-2
On Wed, 14 Aug 2019 at 14:48, Michael Paquier <[hidden email]> wrote:
>
> On Wed, Aug 14, 2019 at 01:57:26PM +1200, David Rowley wrote:
> > I'd say it's broken in a sense that we can create an unlogged
> > partitioned table in the first place.  I think that should have been
> > blocked.
>
> I don't think that it is that crazy to be able to define a partitioned
> table as unlogged, assuming that we could use that to make the
> children inherit the same state.

Since we have no persistence inheritance feature, what are you proposing here?

If we allow SET [UN]LOGGED on a partitioned table to cascade down to
each partition, then do we need to insist that the child partition's
persistence setting does not deviate from the parents? Or would
altering the parent just change the partitions that were not already
set that way?

What would the behaviour be of doing ATTACH PARTITION on a logged
table onto an unlogged partitioned table?

Also, since there is no CREATE LOGGED TABLE syntax, what would users
do if that wanted to create a logged partition on an unlogged
partition hierarchy?  For this to work ATTACH PARTITION would have to
not mess with the persistence setting but the user would have to
CREATE TABLE ... (LIKE partitioned_table); then ATTACH PARTITION.
That seems a bit messy to me, it's inevitable that someone would
eventually complain and ask for a CREATE LOGGED TABLE syntax.

I think if we don't allow mixed persistence partition hierarchies
we'll get complaints. I think it's valid to have them, just imagine
implementing a highspeed queue that does not require durability on
non-processed items. Processing an item updates the "processed" flag
which moves the tuple into a logged partition, thus making it durable.

So my thoughts are that unless someone is proposing to think of all
the corner cases for partitions inheriting their persistence from
their partitioned table, then allowing UNLOGGED partitioned tables is
busted.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: BUG #15954: Unable to alter partitioned table to set log

David Rowley-3
In reply to this post by Efrain J. Berdecia
On Wed, 14 Aug 2019 at 14:51, Efrain J. Berdecia <[hidden email]> wrote:
>
> Is there any way to check in the code whether the table is a parent and then allow the execution of the alter accordingly?

Blocking ALTER TABLE ... SET [UN]LOGGED; on a partitioned table is a
trivial change. The question is, if we were to change this and back
patch then it could break user code.

> Or are you saying that any empty table we would not be able to change the unlogged state?
>
> Should the logic to alter the table just ignore whether the table is empty or not?

Not sure what the table being empty has to do with it.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: BUG #15954: Unable to alter partitioned table to set logged

Efrain J. Berdecia
In reply to this post by David Rowley-3
I'm in a pickle here guys... I'm going to have to create a cron job to make sure any newly created partitions using pg_partman are ALTERed SET LOGGED from now on....

In my humble opinion/recommendation would be to follow the path of least resistance.... Allow mix logging across partitions just like is currently working now but let me set the parent's logging setting  to what I want.. PLEASE!!!!! As someone previously stated, I'm not sure why it was determine to skip tables with no storage set but i fee like if someone just concentrates on updating this logic to allow the parent to be ALTERed the world would be a better place, at least for me :-)

/* Relations without storage may be ignored here */
if (!RELKIND_HAS_STORAGE(tab->relkind))
continue;


I can see someone making a case to allow mixed setting for logging across partitions, I really do. And in my case, once I ran the ALTER SET LOGGED command against the individual partitions it worked like a charm, of course, it logged the entire partition table for each of the partitions I ran the ALTER against and I'm having to run the ALTER one table at a time, but it works!!!!

Thanks,
Efrain J. Berdecia


On Tuesday, August 13, 2019, 11:17:36 PM EDT, David Rowley <[hidden email]> wrote:


On Wed, 14 Aug 2019 at 14:48, Michael Paquier <[hidden email]> wrote:
>
> On Wed, Aug 14, 2019 at 01:57:26PM +1200, David Rowley wrote:
> > I'd say it's broken in a sense that we can create an unlogged
> > partitioned table in the first place.  I think that should have been
> > blocked.
>
> I don't think that it is that crazy to be able to define a partitioned
> table as unlogged, assuming that we could use that to make the
> children inherit the same state.

Since we have no persistence inheritance feature, what are you proposing here?

If we allow SET [UN]LOGGED on a partitioned table to cascade down to
each partition, then do we need to insist that the child partition's
persistence setting does not deviate from the parents? Or would
altering the parent just change the partitions that were not already
set that way?

What would the behaviour be of doing ATTACH PARTITION on a logged
table onto an unlogged partitioned table?

Also, since there is no CREATE LOGGED TABLE syntax, what would users
do if that wanted to create a logged partition on an unlogged
partition hierarchy?  For this to work ATTACH PARTITION would have to
not mess with the persistence setting but the user would have to
CREATE TABLE ... (LIKE partitioned_table); then ATTACH PARTITION.
That seems a bit messy to me, it's inevitable that someone would
eventually complain and ask for a CREATE LOGGED TABLE syntax.

I think if we don't allow mixed persistence partition hierarchies
we'll get complaints. I think it's valid to have them, just imagine
implementing a highspeed queue that does not require durability on
non-processed items. Processing an item updates the "processed" flag
which moves the tuple into a logged partition, thus making it durable.

So my thoughts are that unless someone is proposing to think of all
the corner cases for partitions inheriting their persistence from
their partitioned table, then allowing UNLOGGED partitioned tables is
busted.


--
David Rowley                 
http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15954: Unable to alter partitioned table to set logged

Keith Fiske-2
In reply to this post by Amit Langote


On Tue, Aug 13, 2019 at 9:47 PM Amit Langote <[hidden email]> wrote:
On Wed, Aug 14, 2019 at 3:06 AM PG Bug reporting form
<[hidden email]> wrote:
> The following bug has been logged on the website:
>
> Bug reference:      15954
> Logged by:          Efrain Berdecia
> Email address:      [hidden email]
> PostgreSQL version: 11.1
> Operating system:   Centos7
> Description:
>
> I have a partition table that I created unlogged. I'm using pg_partman to
> manage the partition. I then proceeded to load data into it with a COPY
> command.
>
> Afterwards, I ran an alter table set logged on the parent table but the
> table still shows as UNLOGGED along with all its children.
>
> Is this broken?

It is somewhat.  A workaround is to perform ALTER TABLE SET LOGGED
individually on each partition.  Specifying LOGGED / UNLOGGED for the
parent table is useless as things stand now.

Are you sure by the way that the individual partitions are themselves
UNLOGGED when you created them?  Partitions don't inherit the
logged-ness property from the parent table, so you must explicitly
create a partition using CREATE UNLOGGED TABLE ... PARTITION OF ... if
you want it to be unlogged.

Thanks,
Amit



This is a feature of pg_partman itself and was implemented before native partitioning. I check the UNLOGGED status of the parent and set the children appropriately with explicit statements as you say here..


--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com
Reply | Threaded
Open this post in threaded view
|

Re: BUG #15954: Unable to alter partitioned table to set logged

Keith Fiske-2
In reply to this post by David Rowley-3


On Tue, Aug 13, 2019 at 11:17 PM David Rowley <[hidden email]> wrote:
On Wed, 14 Aug 2019 at 14:48, Michael Paquier <[hidden email]> wrote:
>
> On Wed, Aug 14, 2019 at 01:57:26PM +1200, David Rowley wrote:
> > I'd say it's broken in a sense that we can create an unlogged
> > partitioned table in the first place.  I think that should have been
> > blocked.
>
> I don't think that it is that crazy to be able to define a partitioned
> table as unlogged, assuming that we could use that to make the
> children inherit the same state.

Since we have no persistence inheritance feature, what are you proposing here?

If we allow SET [UN]LOGGED on a partitioned table to cascade down to
each partition, then do we need to insist that the child partition's
persistence setting does not deviate from the parents? Or would
altering the parent just change the partitions that were not already
set that way?

What would the behaviour be of doing ATTACH PARTITION on a logged
table onto an unlogged partitioned table?

Also, since there is no CREATE LOGGED TABLE syntax, what would users
do if that wanted to create a logged partition on an unlogged
partition hierarchy?  For this to work ATTACH PARTITION would have to
not mess with the persistence setting but the user would have to
CREATE TABLE ... (LIKE partitioned_table); then ATTACH PARTITION.
That seems a bit messy to me, it's inevitable that someone would
eventually complain and ask for a CREATE LOGGED TABLE syntax.

I think if we don't allow mixed persistence partition hierarchies
we'll get complaints. I think it's valid to have them, just imagine
implementing a highspeed queue that does not require durability on
non-processed items. Processing an item updates the "processed" flag
which moves the tuple into a logged partition, thus making it durable.

So my thoughts are that unless someone is proposing to think of all
the corner cases for partitions inheriting their persistence from
their partitioned table, then allowing UNLOGGED partitioned tables is
busted.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services




To me it seems that if someone sets the UNLOGGED status on the parent, that should indicate what the child state should be. Same as nearly every other feature of the partition set (indexes, constraints, defaults, etc). If someone wants to change the child tables later to be in a different state, that's fine. But there's got to be some sort of sane configuration defaults here for what state a child table should be in when it's immediately attached to a parent upon creation.

And as I replied before, this is an assumption I made in pg_partman's feature support well before native partitioning was implemented. I check the status of the parent table and upon child table creation, I set it to be either logged or unlogged depending on the parent state. The way things are now, this is broken and an unlogged partition set can never be permanently made into a logged on since the parent will never be able to have that state. All new child tables will continue to be unlogged.

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com
Reply | Threaded
Open this post in threaded view
|

Re: BUG #15954: Unable to alter partitioned table to set logged

David Rowley-3
On Wed, 14 Aug 2019 at 15:42, Keith Fiske <[hidden email]> wrote:

>
>
>
> On Tue, Aug 13, 2019 at 11:17 PM David Rowley <[hidden email]> wrote:
>> If we allow SET [UN]LOGGED on a partitioned table to cascade down to
>> each partition, then do we need to insist that the child partition's
>> persistence setting does not deviate from the parents? Or would
>> altering the parent just change the partitions that were not already
>> set that way?
>>
>> What would the behaviour be of doing ATTACH PARTITION on a logged
>> table onto an unlogged partitioned table?
>>
>> Also, since there is no CREATE LOGGED TABLE syntax, what would users
>> do if that wanted to create a logged partition on an unlogged
>> partition hierarchy?  For this to work ATTACH PARTITION would have to
>> not mess with the persistence setting but the user would have to
>> CREATE TABLE ... (LIKE partitioned_table); then ATTACH PARTITION.
>> That seems a bit messy to me, it's inevitable that someone would
>> eventually complain and ask for a CREATE LOGGED TABLE syntax.
>>
>> I think if we don't allow mixed persistence partition hierarchies
>> we'll get complaints. I think it's valid to have them, just imagine
>> implementing a highspeed queue that does not require durability on
>> non-processed items. Processing an item updates the "processed" flag
>> which moves the tuple into a logged partition, thus making it durable.
>>
>> So my thoughts are that unless someone is proposing to think of all
>> the corner cases for partitions inheriting their persistence from
>> their partitioned table, then allowing UNLOGGED partitioned tables is
>> busted.
>>
>
>
> To me it seems that if someone sets the UNLOGGED status on the parent, that should indicate what the child state should be. Same as nearly every other feature of the partition set (indexes, constraints, defaults, etc). If someone wants to change the child tables later to be in a different state, that's fine. But there's got to be some sort of sane configuration defaults here for what state a child table should be in when it's immediately attached to a parent upon creation.

If you think it should work this way, then it would be good if you
could chime in with how you think it should work exactly. I pointed
out a series of problems above. Adding your +1 to mention you want
such a feature does not really help to work out exactly how those
problems should be solved.

Also, keep in mind there's a bunch of other things that we don't
inherit from the partitioned table;  reloptions, for one, tableam is
another.  Are those broken/missing features too? I'm unsure where the
line should be drawn.

How we determine a tablespace for a partition is a pretty good example
of how complex the semantics of such inheritance can be. Alvaro worked
pretty hard to make that work in a reasonable way. Some people still
think it's unreasonable.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: BUG #15954: Unable to alter partitioned table to set logged

Efrain J. Berdecia
How about a compromise;

Allow us to ALTER the properties of the parent table without any expectation of a "cascading" effect UNLESS we use the already established CASCADE option.  

So for example, if I create a parent table the "regular" way, I can still specify at the time of creating the underlying partitions weather I want those to be created with any added properties or options; regardless of how the parent looks like.  Is okay for tools like pg_partman to let the users know that the tool will use the parent table as the "template" when creating the partitions.

But, if I ALTER any property of the parent table those properties will only affet the parent table UNLESS I use the CASCADE option at which point it will apply the property changes to all the underlying partitions.

It would look something like this;

ALTER TABLE parent_table SET UNLOGGED; -- this will only change the properties on the parent table

ALTER TABLE parent_table SET UNLOGGED CASCADE; -- this would populate the change throughout the partition set and to all the partitions

Thanks,
Efrain J. Berdecia


On Wednesday, August 14, 2019, 01:32:22 AM EDT, David Rowley <[hidden email]> wrote:


On Wed, 14 Aug 2019 at 15:42, Keith Fiske <[hidden email]> wrote:

>
>
>
> On Tue, Aug 13, 2019 at 11:17 PM David Rowley <[hidden email]> wrote:
>> If we allow SET [UN]LOGGED on a partitioned table to cascade down to
>> each partition, then do we need to insist that the child partition's
>> persistence setting does not deviate from the parents? Or would
>> altering the parent just change the partitions that were not already
>> set that way?
>>
>> What would the behaviour be of doing ATTACH PARTITION on a logged
>> table onto an unlogged partitioned table?
>>
>> Also, since there is no CREATE LOGGED TABLE syntax, what would users
>> do if that wanted to create a logged partition on an unlogged
>> partition hierarchy?  For this to work ATTACH PARTITION would have to
>> not mess with the persistence setting but the user would have to
>> CREATE TABLE ... (LIKE partitioned_table); then ATTACH PARTITION.
>> That seems a bit messy to me, it's inevitable that someone would
>> eventually complain and ask for a CREATE LOGGED TABLE syntax.
>>
>> I think if we don't allow mixed persistence partition hierarchies
>> we'll get complaints. I think it's valid to have them, just imagine
>> implementing a highspeed queue that does not require durability on
>> non-processed items. Processing an item updates the "processed" flag
>> which moves the tuple into a logged partition, thus making it durable.
>>
>> So my thoughts are that unless someone is proposing to think of all
>> the corner cases for partitions inheriting their persistence from
>> their partitioned table, then allowing UNLOGGED partitioned tables is
>> busted.
>>
>
>
> To me it seems that if someone sets the UNLOGGED status on the parent, that should indicate what the child state should be. Same as nearly every other feature of the partition set (indexes, constraints, defaults, etc). If someone wants to change the child tables later to be in a different state, that's fine. But there's got to be some sort of sane configuration defaults here for what state a child table should be in when it's immediately attached to a parent upon creation.

If you think it should work this way, then it would be good if you
could chime in with how you think it should work exactly. I pointed
out a series of problems above. Adding your +1 to mention you want
such a feature does not really help to work out exactly how those
problems should be solved.

Also, keep in mind there's a bunch of other things that we don't
inherit from the partitioned table;  reloptions, for one, tableam is
another.  Are those broken/missing features too? I'm unsure where the
line should be drawn.

How we determine a tablespace for a partition is a pretty good example
of how complex the semantics of such inheritance can be. Alvaro worked
pretty hard to make that work in a reasonable way. Some people still
think it's unreasonable.


--
David Rowley                  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Reply | Threaded
Open this post in threaded view
|

Re: BUG #15954: Unable to alter partitioned table to set logged

Keith Fiske-2


On Wed, Aug 14, 2019 at 8:46 AM Efrain J. Berdecia <[hidden email]> wrote:
How about a compromise;

Allow us to ALTER the properties of the parent table without any expectation of a "cascading" effect UNLESS we use the already established CASCADE option.  

So for example, if I create a parent table the "regular" way, I can still specify at the time of creating the underlying partitions weather I want those to be created with any added properties or options; regardless of how the parent looks like.  Is okay for tools like pg_partman to let the users know that the tool will use the parent table as the "template" when creating the partitions.

But, if I ALTER any property of the parent table those properties will only affet the parent table UNLESS I use the CASCADE option at which point it will apply the property changes to all the underlying partitions.

It would look something like this;

ALTER TABLE parent_table SET UNLOGGED; -- this will only change the properties on the parent table

ALTER TABLE parent_table SET UNLOGGED CASCADE; -- this would populate the change throughout the partition set and to all the partitions

Thanks,
Efrain J. Berdecia


On Wednesday, August 14, 2019, 01:32:22 AM EDT, David Rowley <[hidden email]> wrote:


On Wed, 14 Aug 2019 at 15:42, Keith Fiske <[hidden email]> wrote:

>
>
>
> On Tue, Aug 13, 2019 at 11:17 PM David Rowley <[hidden email]> wrote:
>> If we allow SET [UN]LOGGED on a partitioned table to cascade down to
>> each partition, then do we need to insist that the child partition's
>> persistence setting does not deviate from the parents? Or would
>> altering the parent just change the partitions that were not already
>> set that way?
>>
>> What would the behaviour be of doing ATTACH PARTITION on a logged
>> table onto an unlogged partitioned table?
>>
>> Also, since there is no CREATE LOGGED TABLE syntax, what would users
>> do if that wanted to create a logged partition on an unlogged
>> partition hierarchy?  For this to work ATTACH PARTITION would have to
>> not mess with the persistence setting but the user would have to
>> CREATE TABLE ... (LIKE partitioned_table); then ATTACH PARTITION.
>> That seems a bit messy to me, it's inevitable that someone would
>> eventually complain and ask for a CREATE LOGGED TABLE syntax.
>>
>> I think if we don't allow mixed persistence partition hierarchies
>> we'll get complaints. I think it's valid to have them, just imagine
>> implementing a highspeed queue that does not require durability on
>> non-processed items. Processing an item updates the "processed" flag
>> which moves the tuple into a logged partition, thus making it durable.
>>
>> So my thoughts are that unless someone is proposing to think of all
>> the corner cases for partitions inheriting their persistence from
>> their partitioned table, then allowing UNLOGGED partitioned tables is
>> busted.
>>
>
>
> To me it seems that if someone sets the UNLOGGED status on the parent, that should indicate what the child state should be. Same as nearly every other feature of the partition set (indexes, constraints, defaults, etc). If someone wants to change the child tables later to be in a different state, that's fine. But there's got to be some sort of sane configuration defaults here for what state a child table should be in when it's immediately attached to a parent upon creation.

If you think it should work this way, then it would be good if you
could chime in with how you think it should work exactly. I pointed
out a series of problems above. Adding your +1 to mention you want
such a feature does not really help to work out exactly how those
problems should be solved.

Also, keep in mind there's a bunch of other things that we don't
inherit from the partitioned table;  reloptions, for one, tableam is
another.  Are those broken/missing features too? I'm unsure where the
line should be drawn.

How we determine a tablespace for a partition is a pretty good example
of how complex the semantics of such inheritance can be. Alvaro worked
pretty hard to make that work in a reasonable way. Some people still
think it's unreasonable.


--
David Rowley                  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


So in the mean time I've added a fix to v4.2.0 of pg_partman to have it get the unlogged status of the partition set to the template table system I've put in place to handle inheritance properties not handled by native (primary/unique keys, etc).

As the others have stated, I think we just need to get some sort of consistent method of handling this. Currently running an ALTER TABLE on the parent to change the UNLOGGED state simply does nothing which is not intuitive whatsoever. Even if it's just throwing an error saying you cannot change this property, that would be better until a more thorough solution can be implemented in the future.

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com
Reply | Threaded
Open this post in threaded view
|

Re: BUG #15954: Unable to alter partitioned table to set logged

Michael Paquier-2
On Wed, Aug 21, 2019 at 09:24:38AM -0400, Keith Fiske wrote:
> As the others have stated, I think we just need to get some sort of
> consistent method of handling this. Currently running an ALTER TABLE on the
> parent to change the UNLOGGED state simply does nothing which is not
> intuitive whatsoever. Even if it's just throwing an error saying you cannot
> change this property, that would be better until a more thorough solution
> can be implemented in the future.

One problem with an error is that it may break existing application
code :(
--
Michael

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: BUG #15954: Unable to alter partitioned table to set logged

Kyotaro Horiguchi-4
At Wed, 21 Aug 2019 22:44:34 +0900, Michael Paquier <[hidden email]> wrote in <[hidden email]>

> On Wed, Aug 21, 2019 at 09:24:38AM -0400, Keith Fiske wrote:
> > As the others have stated, I think we just need to get some sort of
> > consistent method of handling this. Currently running an ALTER TABLE on the
> > parent to change the UNLOGGED state simply does nothing which is not
> > intuitive whatsoever. Even if it's just throwing an error saying you cannot
> > change this property, that would be better until a more thorough solution
> > can be implemented in the future.
>
> One problem with an error is that it may break existing application
> code :(

It is quite strange that we can CREATE both LOGGED and UNLOGGED
partitioned table but cannot ALTER the property. I believe no one
does ALTER TABLE SET (UN)LOGGED expecting it is silently ignored.

But I'm not sure about the CREATE case.. CREATE UNLOGGED VIEW is
complained as "cannot be unlogged bacause they do not have
storage" but I don't think partitioned tables don't necessarily
need to behave so. Even if any, I'm not sure what those who does
"CREATE UNLOGGED TABLE parent" expect (*), I think we can ignore
and always make the new table as LOGGED. The only trouble case is
happen only for those who checks the property.

*: Maybe they expect that the property propagates to children,
 but anyway we are not doing so.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center


Reply | Threaded
Open this post in threaded view
|

Re: BUG #15954: Unable to alter partitioned table to set logged

Keith Fiske-2
In reply to this post by Michael Paquier-2


On Wed, Aug 21, 2019 at 9:44 AM Michael Paquier <[hidden email]> wrote:
On Wed, Aug 21, 2019 at 09:24:38AM -0400, Keith Fiske wrote:
> As the others have stated, I think we just need to get some sort of
> consistent method of handling this. Currently running an ALTER TABLE on the
> parent to change the UNLOGGED state simply does nothing which is not
> intuitive whatsoever. Even if it's just throwing an error saying you cannot
> change this property, that would be better until a more thorough solution
> can be implemented in the future.

One problem with an error is that it may break existing application
code :(
--
Michael


Their application is already broken if it's expecting the property to actually change.

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com