Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hxk25-0008Ob-56 for pgsql-bugs@arkaria.postgresql.org; Wed, 14 Aug 2019 03:33:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hxk23-0002nz-NM for pgsql-bugs@arkaria.postgresql.org; Wed, 14 Aug 2019 03:33:03 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hxk23-0002je-8I for pgsql-bugs@lists.postgresql.org; Wed, 14 Aug 2019 03:33:03 +0000 Received: from sonic310-25.consmr.mail.ne1.yahoo.com ([66.163.186.206]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hxk1v-0004NA-Dd for pgsql-bugs@lists.postgresql.org; Wed, 14 Aug 2019 03:33:02 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1565753571; bh=TiXqmyCwJaxbRU1dTaX9X6VSjI9atRwwGp8c/cJ2J6I=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject; b=QQsDMAWuMN06qxnK4zDRtV1FZfzqJJO2Ei5HqX1avDqxmU039/rjsfjMvtT4M9EGposAjPSrO9rAmtc0Wt2TYXpAYdjlOw+36O2kCDcw2GZUTFo12JUBUeyj8WQMfVGJjX3OLOutlCgzoArc/kj5dluZyOsjDcI12gR3+Kqbwm6/bf9fRF9Apu3Jj3prr7gbjPR/vWW/6v6CI56RcWyUIUyVjXehZoYgK2GWSN8smCAdS2fcKW4+6bnFd8mbgbhyww/nvR4UAugcY9zO0kyYiCSsKHVw6WuMW5FwSPm18Jg2JQKMHqeKK6W56RfPm6Ua+rmK8cjtL5Z3U4DEWdwdaA== X-YMail-OSG: 3Dc.B1MVM1n20jH3ZW2vkSLxcvRyBK.nNnE9QMMKbKhH4fz0s646FUM4kkeDRq6 86pDeUiIGBJB5Ibjp7jXTm3RuwEJ7pfktsTFECerbd.1SSdPY_yinQeYOUN.lTUsW8n.PIOuB6u0 ZFVrJmgGBxBfEh1Wm7nfXz.w_FIu.ngFGwe1KFKqd4PtdsDM9CskZhRyQIhL4IM5.tcQtNUf93By p1NSOwFmMG.wJiew5bOQ7jKnkc_ndtiKh27XhaoD6BqjbF9yKeq5kXG6zbDd4aynX3K8m3Z7mjqs EFwWV1tQ7tvdVgwp3BA_NF5IuRF.9F0H04_6.TTNPwyBkQopWqA_5DXOglKMb7Y.SY4QAqkCb7GL CZ6czTyKoFKchl5ynPP5Qv3YzLAPvt1YUN5rStnpGpPW8qYK1ZsdW15jK2VISx_cufV9DCpbdLae 3vY.pGV6JVcK_NBpb3z6cHc9P8WR9mAjKqVOwy1wJ3AFAma4Q5KUpfiXSyORsj0ONPhCU21yx1Dr tWoPuRt0c9ekw_1W_3cn9ZrYE7rebZAAzM7xYBpfOqLsokJiB2AUH8KN2gagdFoW7shFwODAuX9l Eb8r9rve1DtGQ4TX4VmP6m3Y.iyjjkLRyozqZMBqg5.Cf7pKvshPZkhPAZ5lqFk8vP57rF0BlwAq rjM6RsoTfryYeBpqh6ftmlT1fx6ju8pZ98az5L5rcYIBlKB8HBvxalcX82ocJVUfQphCS27cqJ0S IOTd8mOpVvGc3tXTI0pdbJJWd1oXSnC7bGI.GzUPcbMeVQG1RliouJRs3cRujufxXSwkziybLI4w cRUo6cSOhtmX_4Mf7m0AMUViNaoUc6kn5ZQGLgOQPL8233XNXAt1mNCzCC3JtFwijlCVzFyga44o RQcRkrJVoJ_b2DtnNzlZSfRSes2DUnwAfczp.YTnM2s6P2drh.Y4MWgT7I2B_IHhEdpXlK.oCmtu klKIDnz5l6FFbbE9nEHeqbnb88._nahzU2VXlcJ6cDBesx.elvQqfp4wt3LogS8Y34_0YhIyKJqT CZMEuPuYKNuCx.sBvNPEkQo.R4EDzdJbD5zafLiUYmV3LTydgZHhZM53GuH55tsNS2Lb01aPq4_c LN3zgtqyvR79QmParTX8HIVEiMV7RGizNuAQEFtJwe2Pk9q2AkZpd6SCZySqiZZ1KsgsP.Yq61UV .B8Ui0flkio7uI_.OApo4Z4oMvt.dypZ9YSuNAMujOa.fM_QnbyI5FvDzRPKliJXCZVFrOdfSHOW HbAQWyUo9vcEhI5RuLoQWqX8nwExbY.SSXiKs Received: from sonic.gate.mail.ne1.yahoo.com by sonic310.consmr.mail.ne1.yahoo.com with HTTP; Wed, 14 Aug 2019 03:32:51 +0000 Date: Wed, 14 Aug 2019 03:32:50 +0000 (UTC) From: "Efrain J. Berdecia" To: Michael Paquier , David Rowley Cc: Amit Langote , PostgreSQL mailing lists Message-ID: <1822353966.5108046.1565753570138@mail.yahoo.com> In-Reply-To: References: <15954-b61523bed4b110c4@postgresql.org> <20190814024822.GC14446@paquier.xyz> Subject: Re: BUG #15954: Unable to alter partitioned table to set logged MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5108045_450929363.1565753570136" X-Mailer: WebService/1.1.14097 YMailNorrin Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/76.0.3809.100 Safari/537.36 Content-Length: 10237 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_5108045_450929363.1565753570136 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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 re= sistance.... Allow mix logging across partitions just like is currently wor= king now but let me set the parent's logging setting=C2=A0 to what I want..= PLEASE!!!!! As someone previously stated, I'm not sure why it was determin= e to skip tables with no storage set but i fee like if someone just concent= rates on updating this logic to allow the parent to be ALTERed the world wo= uld 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 p= artitions, I really do. And in my case, once I ran the ALTER SET LOGGED com= mand against the individual partitions it worked like a charm, of course, i= t logged the entire partition table for each of the partitions I ran the AL= TER against and I'm having to run the ALTER one table at a time, but it wor= ks!!!! Thanks,Efrain J. Berdecia=20 On Tuesday, August 13, 2019, 11:17:36 PM EDT, David Rowley wrote: =20 =20 On Wed, 14 Aug 2019 at 14:48, Michael Paquier 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.=C2=A0 I think that should have be= en > > 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 he= re? 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?=C2=A0 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. --=20 David Rowley=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services =20 ------=_Part_5108045_450929363.1565753570136 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
I'm in a pickle here guys...= I'm going to have to create a cron job to make sure any newly created part= itions using pg_partman are ALTERed SET LOGGED from now on....

In my humble opinion/recommendation would be to follow the path of le= ast resistance.... Allow mix logging across partitions just like is current= ly 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 de= termine to skip tables with no storage set but i fee like if someone just c= oncentrates on updating this logic to allow the parent to be ALTERed the wo= rld would be a better place, at least for me :-)

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


I can= see someone making a case to allow mixed setting for logging across partit= ions, 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 log= ged the entire partition table for each of the partitions I ran the ALTER a= gainst and I'm having to run the ALTER one table at a time, but it works!!!= !

Thanks,
<= div class=3D"ydpca1d7dadsignature">Efrain J. Berdecia


=20
=20
On Tuesday, August 13, 2019, 11:17:36 PM EDT, David Row= ley <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 se= nse 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 t= he
> children inherit the same state.

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

If we allow S= ET [UN]LOGGED on a partitioned table to cascade down to
e= ach partition, then do we need to insist that the child partition's
persistence setting does not deviate from the parents? Or would<= br clear=3D"none">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 partiti= on on an unlogged
partition hierarchy?  For this to = work ATTACH PARTITION would have to
not mess with the per= sistence 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 pa= rtition hierarchies
we'll get complaints. I think it's va= lid to have them, just imagine
implementing a highspeed q= ueue that does not require durability on
non-processed it= ems. Processing an item updates the "processed" flag
whic= h moves the tuple into a logged partition, thus making it durable.

So my thoughts are that unless someone is prop= osing to think of all
the corner cases for partitions inh= eriting their persistence from
their partitioned table, t= hen allowing UNLOGGED partitioned tables is
busted.


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

------=_Part_5108045_450929363.1565753570136--