FK Constraint sort order with pg_dump
Hello,
The sorting order of FK constraints with the same name is based on the
OID (because it lands in the “Usually shouldn’t get here” OID comparison
block at [1]). Wouldn’t it be better if the order of those constraints
were based on the table name?
Details:
The above schema is identical except in the order how the constraints
were added (the constraint name is the same on those two tables):
--8<---cut here---start->8---
-- --- Schema Version 1:
CREATE TABLE a (id int unique);
CREATE TABLE b (id int);
ALTER TABLE b ADD CONSTRAINT x_fkey FOREIGN KEY (id) REFERENCES a(id);
CREATE TABLE c (id int);
ALTER TABLE c ADD CONSTRAINT x_fkey FOREIGN KEY (id) REFERENCES a(id);
-- --- Schema Version 2:
CREATE TABLE a (id int unique);
CREATE TABLE c (id int);
ALTER TABLE c ADD CONSTRAINT x_fkey FOREIGN KEY (id) REFERENCES a(id);
CREATE TABLE b (id int);
ALTER TABLE b ADD CONSTRAINT x_fkey FOREIGN KEY (id) REFERENCES a(id);
--8<---cut here---end--->8---
Doing a pg_dump on Version 1 and Version 2 leads to two different dumps
despite being the same schema: (*)
--8<---cut here---start->8---
--- version12022-07-21 19:16:31.369010843 +0200
+++ version22022-07-21 19:16:26.688976178 +0200
@@ -86,18 +86,18 @@
--
--- Name: b x_fkey; Type: FK CONSTRAINT; Schema: public; Owner: bch
+-- Name: c x_fkey; Type: FK CONSTRAINT; Schema: public; Owner: bch
--
-ALTER TABLE ONLY public.b
+ALTER TABLE ONLY public.c
ADD CONSTRAINT x_fkey FOREIGN KEY (id) REFERENCES public.a(id);
--
--- Name: c x_fkey; Type: FK CONSTRAINT; Schema: public; Owner: bch
+-- Name: b x_fkey; Type: FK CONSTRAINT; Schema: public; Owner: bch
--
-ALTER TABLE ONLY public.c
+ALTER TABLE ONLY public.b
ADD CONSTRAINT x_fkey FOREIGN KEY (id) REFERENCES public.a(id);
--8<---cut here---end--->8---
Attached is a patch file that adds a string comparison function call to
sort FK constraints (based on the table if it exists). Any thoughts on
that?
[1]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/bin/pg_dump/pg_dump_sort.c;h=80641cd79a2e6ce0a10bd55218b10d22ac369ed5;hb=7c850320d8cfa5503ecec61c2559661b924f7595#l212
(*) Tested on 14.4
--
Christian Barthel
modified src/bin/pg_dump/pg_dump_sort.c
@@ -300,6 +300,23 @@ DOTypeNameCompare(const void *p1, const void *p2)
if (cmpval != 0)
return cmpval;
}
+ else if (obj1->objType == DO_FK_CONSTRAINT)
+ {
+ ConstraintInfo *cobj1 = *(ConstraintInfo *const *)p1;
+ ConstraintInfo *cobj2 = *(ConstraintInfo *const *)p2;
+
+ if (cobj1->contable != NULL && cobj2->contable != NULL) {
+ /* Sort two FK constraints with the same name by their
+ * corresponding relationname:
+ */
+ cmpval = strcmp(
+cobj1->contable->dobj.name,
+cobj2->contable->dobj.name);
+ if (cmpval != 0)
+return cmpval;
+ }
+ }
/* Usually shouldn't get here, but if we do, sort by OID */
return oidcmp(obj1->catId.oid, obj2->catId.oid);
Re: FK Constraint sort order with pg_dump
On Thursday, July 21, 2022, Adrian Klaver wrote: > On 7/21/22 10:25, Christian Barthel wrote: >> Hello, The sorting order of FK constraints with the same name is >> based on the OID (because it lands in the “Usually shouldn’t get >> here” OID comparison block at [1]). Wouldn’t it be better if the >> order of those constraints were based on the table name? >> > > Why does it matter? As the comment in pg_dump.c states, logically identical schemas should produce identical dumps: | * We rely on dependency information to help us determine a safe order, | so * the initial sort is mostly for cosmetic purposes: we sort by name | to * ensure that logically identical schemas will dump identically. <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/bin/pg_dump/pg_dump.c#l883> This is done for most objects (tables, functions etc). Why not for FK constraints? It makes comparing schemas on different postgres instances simpler (i.e. when you’re working with testing, staging, live systems etc). -- Christian Barthel
Re: Logical replication versus pglogical on PostgreSQL 14
On Saturday, July 23, 2022, Rory Campbell-Lange wrote: > Is native logical replication and pglogical replication fundamentally > the same? Here is a discussion about the differences between pglogical 2.1 and logical replication in PostgreSQL 10: <https://www.2ndquadrant.com/en/blog/pglogical-logical-replication-postgresql-10/> As far as I can tell, pglogical provides more features than the builtin logical replication in PostgreSQL (row/column filtering as an example). > Is pglogical likely to be continue to be supported? The link above says so: | First, let me assure you that the development of pglogical continues. Furthermore, the last release is from Dec, 2021 and the last commit is 12 days ago, <https://github.com/2ndQuadrant/pglogical>. -- Christian Barthel
PL/pgSQL RETURN QUERY and DOMAIN CHECKs
Hello,
can anyone explain why the domain check is not generating an exception
when used within PL/pgSQL ‘RETURN QUERY’ statement?
See tf2() and tf3() above - in particular, the INSERT statement in line
100 and 163:
--8<---cut here---start->8---
1
2 -- x, y greater equal 0
3 -- y lower equal than x.
4 CREATE TYPE _t AS (
5x double precision,
6y double precision
7);
8 CREATE DOMAIN t AS _t
9 CHECK (
10 (VALUE).x >= 0 AND
11 (VALUE).y >= 0 AND
12 (VALUE).y <= (VALUE).x
13 );
14
15 CREATE TABLE test (v t);
16
17 -- - Tests:
18
19 -- works as expected
20 insert into test values ('(1,1)'::t), ('(3, 2)'::t);
21 -- works as expected
22 select '(1,1)'::t;
23 select '(4,2)'::t;
24
25 -- works as expected / throws exception: t_check
26 select '(4,5)'::t;
27 /*
28 ,,* x=# SELECT '(4,5)'::t;
29 ERROR: 23514: value for domain t violates check constraint "t_check"
30 SCHEMA NAME: public
31 DATATYPE NAME: t
32 CONSTRAINT NAME: t_check
33 LOCATION: ExecEvalConstraintCheck, execExprInterp.c:3670
34 ,,*/
35
36 -- works as expected: call tf1() fails with constraint error
37 CREATE OR REPLACE FUNCTION tf1() RETURNS t
38 AS $$
39 DECLARE
40 BEGIN
41 return '(4,5)'::t;
42 END; $$ LANGUAGE plpgsql SECURITY DEFINER;
43
44
45 -- works not as expected:
46 CREATE OR REPLACE FUNCTION tf2() RETURNS SETOF t
47 AS $$
48 DECLARE
49 BEGIN
50 RETURN QUERY select 4::double precision, 5::double precision;
51 END; $$ LANGUAGE plpgsql SECURITY DEFINER;
52 /*
53 *** *x=# SELECT tf2();
54 *** +---+
55 *** | tf2 |
56 *** +---+
57 *** | (4,5) |
58 *** +---+
59 *** (1 row)
60 ***
61 *** Time: 0.821 ms
62 *** *x=# SELECT '(4,5)'::t;
63 *** ERROR: 23514: value for domain t violates check constraint "t_check"
64 *** SCHEMA NAME: public
65 *** DATATYPE NAME: t
66 *** CONSTRAINT NAME: t_check
67 *** LOCATION: ExecEvalConstraintCheck, execExprInterp.c:3639
68 *** Time: 0.521 ms
69 *** *x=# SELECT tf2()::t;
70 *** +---+
71 *** | tf2 |
72 *** +---+
73 *** | (4,5) |
74 *** +---+
75 ***
76 *** (1 row)
77 ***
78 *** Time: 0.546 ms
79 *** *x=# SELECT pg_typeof(tf2());
80 *** +---+
81 *** | pg_typeof |
82 *** +---+
83 *** | t |
84 *** +---+
85 *** (1 row)
86 ***
87 *** *x=# insert into test values ('(1,1)'::t), ('(3, 2)'::t);
88 *** INSERT 0 2
89 *** Time: 0.897 ms
90 *** *x=#
91 *** *x=# SELECT tf2();
92 *** +---+
93 *** | tf2 |
94 *** +---+
95 *** | (4,5) |
96 *** +---+
97 *** (1 row)
98 ***
99 *** Time: 0.532 ms
100 *** *x=# INSERT into test VALUES ((select tf2())) returning *;
101 *** +---+
102 *** | v |
103 *** +---+
104 *** | (4,5) |
105 *** +---+
106 *** (1 row)
107 ***
108 *** INSERT 0 1
109 *** Time: 0.759 ms
110 *** *x=# SELECT v::t from test;
111 *** +---+
112 *** | v |
113 *** +---+
114 *** | (1,1) |
115 *** | (3,2) |
116 *** | (4,5) |
117 *** +---+
118 *** (3 rows)
119 ***
120 *** Time: 0.559 ms
121 ***
122 *** *x=# SELECT '(4,5)'::t;
123 *** ERROR: 23514: value for domain t violates check constraint "t_check"
124 *** SCHEMA NAME: public
125 *** DATATYPE NAME: t
126 *** CONSTRAINT NAME: t_check
127 *** LOCATION: ExecEvalConstraintCheck, execExprInterp.c:3639
128 *** Time: 0.634 ms
129 ***
130 */
131
132 -- not expected, same example with REURN QUERY EXECUTE:
133 CREATE OR REPLACE FUNCTION tf3() RETURNS SETOF t
134 AS $$
135 DECLARE
136 BEGIN
137 RETURN QUERY EXECUTE
138 format(
139 $sql$
140 select 4::double precision,5::double precision
141 $sql$);
142 END; $$ LANGUAGE plpgsql SECURITY DEFINER;
143
144 /*
145 *** *x=# SELECT tf3();
146 *** +---+
147 *** | tf3 |
148 *** +---+
149 *** | (4,5) |
150 *** +---+
151 *** (1 row)
152 ***
153 *** Time: 0.924 ms
154 *** *x=# SELECT tf3()::t;
155 *** +---+
156 *** | tf3 |
157 *** +---+
158 *** | (4,5) |
159 *** +---+
160 *** (1 row)
161 ***
162 *** Time: 0.538 ms
163 *** *x=# insert into test ((select tf3()));
164 *** INSERT 0 1
165 *** Time: 0.840 ms
166 *** *x=# SELECT v::t from test;
167 *** +---+
168 *** | v |
169 *** +---+
170 *** | (1,1) |
171 *** | (3,2) |
172 *** | (4,5) |
173 *** | (4,5) |
174 *** +---+
175 *** (4 rows)
176 */
--8<---cut here---end--->8---
Is that on purpose?
Have I missed something in the documentation?
--
Christian Barthel
