Logical replication: duplicate key problem
I have followed the following process: * pg_dump --schema-only on server 1 * restored that schema-only dump on server 2 * created a publication on server 1 including all the tables on server 1 * created a subscription on server 2 Server 2 does not get updated data for the schema involved from anywhere else than the logical replication. For some tables (some of them having many millions of records) the process of replication seems to go smoothly. But for too many tables I get this type of error messages: 2018-03-18 08:00:45.915 SAST [13512] ERROR: duplicate key value violates unique constraint "country_pkey" 2018-03-18 08:00:46.088 SAST [13513] ERROR: duplicate key value violates unique constraint "abstract_id_key" In many of those cases it involves a serial field. In at least one case in involved a citext field with a unique constraint. Now just wonder how logical replication between the two servers can produce such errors if the constraints on both sides are the same? Is this a bug? Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
Using Lateral
In the past I could use this in a query:
SELECT
DISTINCT ut,
CASE
WHEN xpath_exists ('//t:address_spec/t:country/text()',
q.address_spec,
p.ns)
THEN unnest (xpath ('//t:address_spec/t:country/text()',
q.address_spec,
p.ns))::citext
ELSE NULL
END country,
No longer. The error message suggests I should use a lateral query.
But I could not figure out in the documentation how to get the same
result using a "lateral" construct.
Just selecting "unnest(...)" gives the wrong result because if the
xpath does not exist all the other parts of the query (like 'ut' in
this case) also are empty.
Regards
Johann
--
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)
Re: Using Lateral
Thanks Paul.
I was hesitant to post my full query. It is a long and complicated
query. But here it is now:
WITH p AS (
SELECT
ARRAY [ ARRAY [ 't',
'http://scientific.thomsonreuters.com/schema/wok5.4/public/FullRecord'
] ] AS ns),
uts AS (
SELECT
s.ut
FROM
wos_source.core_2015 s
WHERE
s.id BETWEEN 00
AND 10
),
utsb AS (
SELECT
b.ut
FROM
wos_2017_1.belongs2 b,
uts
WHERE
b.ut = uts.ut), q AS (
SELECT
s.ut,
unnest (xpath
('//t:static_data/t:fullrecord_metadata/t:addresses/t:address_name/t:address_spec',
xml,
ns)) AS address_spec
FROM
p,
uts a
LEFT JOIN utsb b ON b.ut = a.ut
LEFT JOIN wos_source.core_2015 s ON s.ut = a.ut
WHERE
b.ut IS NULL), r AS (
SELECT
s.ut,
unnest (xpath
('//t:static_data/t:item/t:reprint_contact/t:address_spec',
xml,
ns)) AS raddress_spec
FROM
p,
wos_2017_1.publication l,
uts a
LEFT JOIN utsb b ON b.ut = a.ut
LEFT JOIN wos_source.core_2015 s ON s.ut = a.ut
WHERE
b.ut IS NULL
AND xpath_exists
('//t:static_data/t:item/t:reprint_contact/t:address_spec', xml, ns)
AND s.ut = l.ut
AND l.pubyear < 1998), qd AS (
SELECT
DISTINCT ut,
CASE
WHEN xpath_exists ('//t:address_spec/t:country/text()',
q.address_spec,
p.ns)
THEN unnest (xpath ('//t:address_spec/t:country/text()',
q.address_spec,
p.ns))::citext
ELSE NULL
END country,
CASE
WHEN xpath_exists ('//t:address_spec/t:city/text()',
q.address_spec,
p.ns)
THEN unnest (xpath ('//t:address_spec/t:city/text()',
q.address_spec,
p.ns))::citext
ELSE NULL
END city,
CASE
WHEN xpath_exists ('//t:organizations/t:organization/text()',
q.address_spec,
ns)
THEN unnest (xpath ('//t:organizations/t:organization/text()',
q.address_spec,
ns))::citext
ELSE NULL
END organisation,
CASE
WHEN xpath_exists
('//t:organizations/t:organization[@pref="Y"]/text()',
q.address_spec,
ns)
THEN unnest (xpath
('//t:organizations/t:organization[@pref="Y"]/text()',
q.address_spec,
ns))::citext
ELSE NULL
END AS prefname,
CASE
WHEN xpath_exists ('//t:suborganizations/t:suborganization/text()',
q.address_spec,
ns)
THEN unnest (xpath ('//t:suborganizations/t:suborganization/text()',
q.address_spec,
ns))::citext
ELSE NULL
END suborgname,
CASE
WHEN xpath_exists ('/t:address_spec/@addr_no',
q.address_spec,
ns)
THEN (xpath ('/t:address_spec/@addr_no',
q.address_spec,
ns))
[ 1 ]::text::INTEGER
ELSE NULL
END addr_no
FROM
p,
q),
rd AS (
SELECT
DISTINCT ut,
CASE
WHEN xpath_exists ('//t:address_spec/t:country/text()',
r.raddress_spec,
p.ns)
THEN unnest (xpath ('//t:address_spec/t:country/text()',
r.raddress_spec,
p.ns))::citext
ELSE NULL
END country,
CASE
WHEN xpath_exists ('//t:address_spec/t:city/text()',
r.raddress_spec,
p.ns)
THEN unnest (xpath ('//t:address_spec/t:city/text()',
r.raddress_spec,
p.ns))::citext
ELSE NULL
END city,
CASE
WHEN xpath_exists ('//t:organizations/t:organization/text()',
r.raddress_spec,
ns)
THEN unnest (xpath ('//t:organizations/t:organization/text()',
r.raddress_spec,
ns))::citext
ELSE NULL
END organisation,
CASE
WHEN xpath_exists
('//t:organizations/t:organization[@pref="Y"]/text()',
r.raddress_spec,
ns)
THEN unnest (xpath
('//t:organizations/t:organization[@pref="Y"]/text()',
r.raddress_spec,
ns))::citext
ELSE NULL
END AS prefname,
CASE
WHEN xpath_exists ('//t:suborganizations/t:suborganization/text()',
r.raddress_spec,
ns)
THEN unnest (xpath ('//t:suborganizations/t:suborganization/text()',
r.raddress_spe
Re: Using Lateral
Thanks David and Paul,
You have helped me a lot.
Regards
Johann.
On 28 March 2018 at 20:49, David G. Johnston wrote:
> On Tuesday, March 27, 2018, Johann Spies wrote:
>>
>> In the past I could use this in a query:
>>
>> SELECT
>> DISTINCT ut,
>> CASE
>> WHEN xpath_exists ('//t:address_spec/t:country/text()',
>> q.address_spec,
>> p.ns)
>> THEN unnest (xpath ('//t:address_spec/t:country/text()',
>> q.address_spec,
>> p.ns))::citext
>> ELSE NULL
>> END country,
>> [...]
>> Just selecting "unnest(...)" gives the wrong result because if the
>> xpath does not exist all the other parts of the query (like 'ut' in
>> this case) also are empty.
>
>
> You should be able to solve the empty-set-in-target-list problem via a
> scalar subquery instead of a case construct.
>
> Select distinct ut, (select unnest(...)) as country from ...
>
> The subselect wil return null if fed zero rows. Though you will still have
> to solve an issue if the unrest returns 1+ rows.
>
> In lieu of the inline scalar subquery I would advise writing a function and
> just calling it directly in the target-list. But it should not return
> setof, I'd return an array if you need to accept the possibility of 1+
> matches, and return an empty array for zero matches.
>
> David J.
--
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)
Which background task looks for pg_xlog in 10?
I see this in /var/log/postgresql/postgresql-10-main.log:
postgres postgres@template1 ERROR: could not open directory
"pg_xlog": No such file or directory
postgres postgres@template1 STATEMENT: SELECT count(*) AS segments
FROM pg_ls_dir('pg_xlog') t(fn) \
WHERE fn ~ '^[0-9A-Z]{24}$'
Where does this come from?
Regards
Johann
--
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)
How to build a btree index with integer values on jsonb data?
How can I transform the following definition to index pubyear as integer and not text? CREATE INDEX pubyear_idx ON some_table_where_data_field_is_of_type_jsonb USING btree ((data -> 'REC'::text) -> 'static_data'::text) -> 'summary'::text) -> 'pub_info'::text) ->> '@pubyear'::text) COLLATE pg_catalog."default"); While I can cast the value in a SELECT statement to integer I have been able to do the same while creating the index. Why btree index? I want to do queries like select stuff from sometable where pubyear between 2015 and 2018; Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
Re: How to build a btree index with integer values on jsonb data?
On Thu, 6 Dec 2018 at 19:27, Laurenz Albe wrote: > > Replace > > COLLATE pg_catalog."default" > > with > > ::integer which results in syntax error at or near "::" LINE 2: ...'::text) -> 'pub_info'::text) ->> '@pubyear'::text)::integer moving the ::integer into the bracket also: syntax error at end of input LINE 2: ...'::text) -> 'pub_info'::text) ->> '@pubyear'::text::integer) ^ I have tried this before. Thanks for your try. Regards Johann
Re: How to build a btree index with integer values on jsonb data?
Thank you very much. It worked. Regards Johann On Thu, 13 Dec 2018 at 11:03, Andrew Gierth wrote: > > >>>>> "Johann" == Johann Spies writes: > > Johann> How can I transform the following definition to index pubyear > Johann> as integer and not text? > > Johann> CREATE INDEX pubyear_idx > Johann> ON some_table_where_data_field_is_of_type_jsonb USING btree > Johann> ((data -> 'REC'::text) -> 'static_data'::text) -> > Johann> 'summary'::text) -> 'pub_info'::text) ->> '@pubyear'::text) COLLATE > Johann> pg_catalog."default"); > > Johann> While I can cast the value in a SELECT statement to integer I > Johann> have been able to do the same while creating the index. > > Laurenz' answer was almost correct, just got the position of the parens > wrong. > > When you use an expression in an index, the outermost level of the > expression must either be (syntactically) a function call, or it must > have parens around its _outermost_ level. > > You can simplify selecting from nested json using #>> in place of the -> > and ->> operators. (x #>> array['foo','bar']) is equivalent to doing > ((x -> 'foo') ->> 'bar') > > So: > > CREATE INDEX pubyear_idx > ON some_table_where_data_field_is_of_type_jsonb USING btree >( > ((data #>> > array['REC','static_data','summary','pub_info','@pubyear'])::integer) >); > > Note the ::integer is inside the parens that define the column value > within the outermost ( ) which enclose the column _list_. > > -- > Andrew (irc:RhodiumToad) -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
Permission to refresh materialized view
We did run this query: *GRANT ALL ON ALL TABLES IN SCHEMA X TO USER Y;* But user Y gets the message that he has to be the owner of a materialized view to be able to refresh it. Is that intended behaviour? Is there a way to enable the user to refresh materialized views in that schema? Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
Bug in documentation (trim(...))?
The following illustrates the problem (removing double quotes from a string): *js=# select regexp_replace(regexp_replace( '"University of Cape Town"', '^"', ''),'"$', ''); regexp_replace - University of Cape Town(1 row)js=# trim(both '"' from '"University of Cape Town"');ERROR: syntax error at or near "trim"LINE 1: trim(both '"' from '"University of Cape Town"');* Example from the documentation: *js=# trim(both 'xyz' from 'yxTomxx');ERROR: syntax error at or near "trim"LINE 1: trim(both 'xyz' from 'yxTomxx');^* I would think that "trim" if it works in this case would be a cheaper function than using rexexp_replace twice. Or is there another way? Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
Re: Bug in documentation (trim(...))?
Thanks! I was a bit stupid. Johann On Mon, 20 May 2019 at 12:18, Francisco Olarte wrote: > On Mon, May 20, 2019 at 12:09 PM Johann Spies > wrote: > > The following illustrates the problem (removing double quotes from a > string): > > Missing "select " in your examples, is it intentional? > > > js=# trim(both '"' from '"University of Cape Town"'); > > ERROR: syntax error at or near "trim" > > LINE 1: trim(both '"' from '"University of Cape Town"'); > > js=# trim(both 'xyz' from 'yxTomxx'); > > ERROR: syntax error at or near "trim" > > LINE 1: trim(both 'xyz' from 'yxTomxx'); > > ^ > > I would think that "trim" if it works in this case would be a cheaper > function than using rexexp_replace twice. > > ( copied from your mail AFTER "select " ): > > $ select trim(both '"' from '"University of Cape Town"'); > btrim > - > University of Cape Town > (1 row) > > $ select trim(both 'xyz' from 'yxTomxx'); > btrim > --- > Tom > (1 row) > > Francisco Olarte. > -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
