pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedTable's REPLICATE IDENTITY : where is it kept?
4+ messages / 2 participants
[nested] [flat]
Commits from this thread:
2018-07-30 c0a552921b f6ef3ed436 8c7f64b0ed b868c08eb6 addf9e1bd6 96b1d984fe Fix pg_dump's failure to dump REPLICA IDENTITY for constraint indexes.
* Table's REPLICATE IDENTITY : where is it kept?
@ 2018-07-30 10:44 Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 4+ messages in thread
From: Achilleas Mantzios @ 2018-07-30 10:44 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hello,
I noticed that after changing a table's REPLICA IDENTITY via the ALTER TABLE command, this is not reflected in pg_dump.
\d shows the REPLICA IDENTITY , also this is track-able via pg_index.indisreplident but I can't seem to find where this is reflected in terms of the pg_dump command.
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Table's REPLICATE IDENTITY : where is it kept?
@ 2018-07-30 13:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2018-07-30 13:37 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin@lists.postgresql.org
Achilleas Mantzios <achill@matrix.gatewaynet.com> writes:
> I noticed that after changing a table's REPLICA IDENTITY via the ALTER TABLE command, this is not reflected in pg_dump.
Really? There's certainly code in there that purports to do it:
/*
* dump properties we only have ALTER TABLE syntax for
*/
if ((tbinfo->relkind == RELKIND_RELATION ||
tbinfo->relkind == RELKIND_PARTITIONED_TABLE ||
tbinfo->relkind == RELKIND_MATVIEW) &&
tbinfo->relreplident != REPLICA_IDENTITY_DEFAULT)
{
if (tbinfo->relreplident == REPLICA_IDENTITY_INDEX)
{
/* nothing to do, will be set when the index is dumped */
}
else if (tbinfo->relreplident == REPLICA_IDENTITY_NOTHING)
{
appendPQExpBuffer(q, "\nALTER TABLE ONLY %s REPLICA IDENTITY NOTHING;\n",
qualrelname);
}
else if (tbinfo->relreplident == REPLICA_IDENTITY_FULL)
{
appendPQExpBuffer(q, "\nALTER TABLE ONLY %s REPLICA IDENTITY FULL;\n",
qualrelname);
}
}
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Table's REPLICATE IDENTITY : where is it kept?
@ 2018-07-30 14:11 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Achilleas Mantzios @ 2018-07-30 14:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin@lists.postgresql.org
On 30/07/2018 16:37, Tom Lane wrote:
> Achilleas Mantzios <achill@matrix.gatewaynet.com> writes:
>> I noticed that after changing a table's REPLICA IDENTITY via the ALTER TABLE command, this is not reflected in pg_dump.
> Really? There's certainly code in there that purports to do it:
When its a plain INDEX it is pg_dump'ed correctly.
But when this is a CONSTRAINT index it is not.
e.g.
\d status
...
Indexes:
"status_id_key" UNIQUE CONSTRAINT, btree (id)
"status_uk" UNIQUE, btree (id)
alter table status REPLICA IDENTITY USING INDEX status_id_key;
This does not get dumped:
postgres@TEST-smadb:~$ pg_dump --schema-only | grep "REPLICA IDENTITY"
postgres@TEST-smadb:~$
But if I do :
alter table status REPLICA IDENTITY USING INDEX status_uk;
then I get correct result :
postgres@TEST-smadb:~$ pg_dump --schema-only | grep "REPLICA IDENTITY"
ALTER TABLE ONLY public.status REPLICA IDENTITY USING INDEX status_uk;
postgres@TEST-smadb:~$
> /*
> * dump properties we only have ALTER TABLE syntax for
> */
> if ((tbinfo->relkind == RELKIND_RELATION ||
> tbinfo->relkind == RELKIND_PARTITIONED_TABLE ||
> tbinfo->relkind == RELKIND_MATVIEW) &&
> tbinfo->relreplident != REPLICA_IDENTITY_DEFAULT)
> {
> if (tbinfo->relreplident == REPLICA_IDENTITY_INDEX)
> {
> /* nothing to do, will be set when the index is dumped */
> }
> else if (tbinfo->relreplident == REPLICA_IDENTITY_NOTHING)
> {
> appendPQExpBuffer(q, "\nALTER TABLE ONLY %s REPLICA IDENTITY NOTHING;\n",
> qualrelname);
> }
> else if (tbinfo->relreplident == REPLICA_IDENTITY_FULL)
> {
> appendPQExpBuffer(q, "\nALTER TABLE ONLY %s REPLICA IDENTITY FULL;\n",
> qualrelname);
> }
> }
>
> regards, tom lane
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Table's REPLICATE IDENTITY : where is it kept?
@ 2018-07-30 14:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2018-07-30 14:48 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin@lists.postgresql.org
Achilleas Mantzios <achill@matrix.gatewaynet.com> writes:
> On 30/07/2018 16:37, Tom Lane wrote:
>> Really? There's certainly code in there that purports to do it:
> When its a plain INDEX it is pg_dump'ed correctly.
> But when this is a CONSTRAINT index it is not.
Oooh, you're right, dumpIndex() has code for that but dumpConstraint()
lacks it.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2018-07-30 14:48 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-07-30 10:44 Table's REPLICATE IDENTITY : where is it kept? Achilleas Mantzios <achill@matrix.gatewaynet.com>
2018-07-30 13:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-07-30 14:11 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2018-07-30 14:48 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox