Обсуждение: BUG #15954: Unable to alter partitioned table to set logged
The following bug has been logged on the website: Bug reference: 15954 Logged by: Efrain Berdecia Email address: ejberdecia@yahoo.com 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
On Wed, Aug 14, 2019 at 3:06 AM PG Bug reporting form <noreply@postgresql.org> wrote: > The following bug has been logged on the website: > > Bug reference: 15954 > Logged by: Efrain Berdecia > Email address: ejberdecia@yahoo.com > 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
On Wed, 14 Aug 2019 at 13:47, Amit Langote <amitlangote09@gmail.com> 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
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
Вложения
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<david.rowley@2ndquadrant.com> wrote:On Wed, 14 Aug 2019 at 13:47, Amit Langote <amitlangote09@gmail.com> 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
On Wed, 14 Aug 2019 at 14:48, Michael Paquier <michael@paquier.xyz> 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
On Wed, 14 Aug 2019 at 14:51, Efrain J. Berdecia <ejberdecia@yahoo.com> 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
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;
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 <david.rowley@2ndquadrant.com> wrote:
On Wed, 14 Aug 2019 at 14:48, Michael Paquier <michael@paquier.xyz> 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 Rowleyhttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
>
> 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
PostgreSQL Development, 24x7 Support, Training & Services
On Tue, Aug 13, 2019 at 9:47 PM Amit Langote <amitlangote09@gmail.com> wrote:
On Wed, Aug 14, 2019 at 3:06 AM PG Bug reporting form
<noreply@postgresql.org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 15954
> Logged by: Efrain Berdecia
> Email address: ejberdecia@yahoo.com
> 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
On Tue, Aug 13, 2019 at 11:17 PM David Rowley <david.rowley@2ndquadrant.com> wrote:
On Wed, 14 Aug 2019 at 14:48, Michael Paquier <michael@paquier.xyz> 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.
On Wed, 14 Aug 2019 at 15:42, Keith Fiske <keith.fiske@crunchydata.com> wrote: > > > > On Tue, Aug 13, 2019 at 11:17 PM David Rowley <david.rowley@2ndquadrant.com> 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 shouldbe. Same as nearly every other feature of the partition set (indexes, constraints, defaults, etc). If someone wantsto change the child tables later to be in a different state, that's fine. But there's got to be some sort of sane configurationdefaults 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
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 <david.rowley@2ndquadrant.com> wrote:
On Wed, 14 Aug 2019 at 15:42, Keith Fiske <keith.fiske@crunchydata.com> wrote:
>
>
>
> On Tue, Aug 13, 2019 at 11:17 PM David Rowley <david.rowley@2ndquadrant.com> 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
>
>
>
> On Tue, Aug 13, 2019 at 11:17 PM David Rowley <david.rowley@2ndquadrant.com> 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
On Wed, Aug 14, 2019 at 8:46 AM Efrain J. Berdecia <ejberdecia@yahoo.com> 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 tableALTER TABLE parent_table SET UNLOGGED CASCADE; -- this would populate the change throughout the partition set and to all the partitionsThanks,Efrain J. BerdeciaOn Wednesday, August 14, 2019, 01:32:22 AM EDT, David Rowley <david.rowley@2ndquadrant.com> wrote:On Wed, 14 Aug 2019 at 15:42, Keith Fiske <keith.fiske@crunchydata.com> wrote:
>
>
>
> On Tue, Aug 13, 2019 at 11:17 PM David Rowley <david.rowley@2ndquadrant.com> 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.
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
Вложения
At Wed, 21 Aug 2019 22:44:34 +0900, Michael Paquier <michael@paquier.xyz> wrote in <20190821134434.GA9383@paquier.xyz> > 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
On Wed, Aug 21, 2019 at 9:44 AM Michael Paquier <michael@paquier.xyz> 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.
On Mon, Apr 22, 2024 at 12:29 PM Kyotaro Horiguchi <horikyota.ntt@gmail.com> wrote:
At Wed, 21 Aug 2019 22:44:34 +0900, Michael Paquier <michael@paquier.xyz> wrote in <20190821134434.GA9383@paquier.xyz>
> 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.
I have no issue leaving the unlogged aspect of an unlogged partitioned table being basically something the system ignores. But it is a bug to fail to update the metadata related to this if it is explicitly altered. Can we at least get that part fixed?
There is little upside to preventing the existence of an unlogged partitioned table at this point. I have just submitted a doc patch to deal with that side of the equation.
David J.
On Mon, Apr 22, 2024 at 12:36:20PM -0700, David G. Johnston wrote: > There is little upside to preventing the existence of an unlogged > partitioned table at this point. I have just submitted a doc patch to deal > with that side of the equation. I would not object to that. However, now that we're five years into this problem, I'd rather treat that as a new entire feature than change the existing behavior in the back branches. Not changing unlogged property of a partitioned table on ALTER TABLE is of course no good. However, we also ignore the fact that a partitioned table is unlogged when we create its partitions, and there is a point in making partitions inherit this property from the parent. So, my take would be: - To switch the relpersistence of a partitioned table on ALTER TABLE, without touching any of its partitions. - New partitions should inherit the unlogged property of the parent. - pg_dump should not need tweaks to be able to cope with that, as loggedness is part of the CREATE queries. Compared to the recent experiences around tablespaces and table AMs, this should be less complicated with code paths for relations without storage in tablecmds.c. -- Michael
Вложения
On Monday, April 22, 2024, Michael Paquier <michael@paquier.xyz> wrote:
On Mon, Apr 22, 2024 at 12:36:20PM -0700, David G. Johnston wrote:
> There is little upside to preventing the existence of an unlogged
> partitioned table at this point. I have just submitted a doc patch to deal
> with that side of the equation.
I would not object to that. However, now that we're five years into
this problem, I'd rather treat that as a new entire feature than
change the existing behavior in the back branches.
Not changing unlogged property of a partitioned table on ALTER TABLE
is of course no good.
Correct, it is an outright bug that I see no reason not to just fix and back-patch. The new feature needs its own thread so as not to have this one see the same fate it had 5 years ago.
David J.
On Tue, Apr 23, 2024 at 05:33:00AM -0700, David G. Johnston wrote: > Correct, it is an outright bug that I see no reason not to just fix and > back-patch. The new feature needs its own thread so as not to have this > one see the same fate it had 5 years ago. The semantics have become much more complicated than 5 years ago because partitioned tables have gained support for more things, one being the recent addition of identity columns. While I've analyzed the whole, I have asked myself a couple of questions about what the best user-friendly behavior would be, implemented one and mentioned the rest. Anyway, a new thread has been created about this topic, so feel free to reply there: https://www.postgresql.org/message-id/ZiiyGFTBNkqcMQi_%40paquier.xyz -- Michael