How to parse XML in Postgres newer versions also
Hi!
In Postgres 9.1.2 script below produces proper results:
1.34
5.56
In Postgres 11 it produces wrong results:
null
null
How to make it also to work in newer versions on Postgres ?
create temp table t(x xml, nsa text[][]) on commit drop;
insert into t values(
'
http://www.w3.org/2001/XMLSchema-instance";
xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053.001.02">
1.34
5.56
'::xml,
ARRAY[ARRAY['ns','urn:iso:std:iso:20022:tech:xsd:camt.053.001.02']]);
SELECT
(xpath('Amt/text()', x,nsa))[1]::text::numeric AS tasusumma
FROM (
SELECT unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry',
x,nsa)) as x,
nsa
FROM t
) Ntry
Andrus.
Re: How to parse XML in Postgres newer versions also
Hi
ne 17. 3. 2019 v 12:11 odesílatel Andrus napsal:
> Hi!
>
> In Postgres 9.1.2 script below produces proper results:
>
> 1.34
> 5.56
>
> In Postgres 11 it produces wrong results:
>
> null
> null
>
> How to make it also to work in newer versions on Postgres ?
>
> create temp table t(x xml, nsa text[][]) on commit drop;
> insert into t values(
> '
> http://www.w3.org/2001/XMLSchema-instance";
> xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053.001.02">
>
>
>
> 1.34
>
>
> 5.56
>
>
>
> '::xml,
> ARRAY[ARRAY['ns','urn:iso:std:iso:20022:tech:xsd:camt.053.001.02']]);
>
> SELECT
> (xpath('Amt/text()', x,nsa))[1]::text::numeric AS tasusumma
> FROM (
> SELECT
> unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry',
> x,nsa)) as x,
> nsa
> FROM t
> ) Ntry
>
> Andrus.
>
>
This variant is working
postgres=# SELECT
(xpath('/ns:Ntry/ns:Amt/text()', x,nsa))[1]::text::numeric AS tasusumma
FROM (
SELECT
unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry',
x,nsa)) as x,
nsa
FROM t
) Ntry
;
But I have not a idea, why old code doesn't work. It is little bit strange
so it worked without namespace before Amt tag.
Re: How to parse XML in Postgres newer versions also
ne 17. 3. 2019 v 14:49 odesílatel Pavel Stehule
napsal:
> Hi
>
> ne 17. 3. 2019 v 12:11 odesílatel Andrus napsal:
>
>> Hi!
>>
>> In Postgres 9.1.2 script below produces proper results:
>>
>> 1.34
>> 5.56
>>
>> In Postgres 11 it produces wrong results:
>>
>> null
>> null
>>
>> How to make it also to work in newer versions on Postgres ?
>>
>> create temp table t(x xml, nsa text[][]) on commit drop;
>> insert into t values(
>> '
>> http://www.w3.org/2001/XMLSchema-instance";
>> xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053.001.02">
>>
>>
>>
>> 1.34
>>
>>
>> 5.56
>>
>>
>>
>> '::xml,
>> ARRAY[ARRAY['ns','urn:iso:std:iso:20022:tech:xsd:camt.053.001.02']]);
>>
>> SELECT
>> (xpath('Amt/text()', x,nsa))[1]::text::numeric AS tasusumma
>> FROM (
>> SELECT
>> unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry',
>> x,nsa)) as x,
>> nsa
>> FROM t
>> ) Ntry
>>
>> Andrus.
>>
>>
> This variant is working
> postgres=# SELECT
> (xpath('/ns:Ntry/ns:Amt/text()', x,nsa))[1]::text::numeric AS
> tasusumma
> FROM (
> SELECT
> unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry',
> x,nsa)) as x,
> nsa
> FROM t
> ) Ntry
> ;
>
> But I have not a idea, why old code doesn't work. It is little bit strange
> so it worked without namespace before Amt tag.
>
>
You can use XMLTABLE function
select xmltable.*
from t,
lateral
xmltable(xmlnamespaces('urn:iso:std:iso:20022:tech:xsd:camt.053.001.02' as
ns),
'/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry/.' passing t.x
columns tasusumma numeric path 'ns:Amt')
>
>
>
Re: How to parse XML in Postgres newer versions also
Andrus schrieb am 17.03.2019 um 08:36:
In Postgres 9.1.2 script below produces proper results:
1.34
5.56
create temp table t(x xml, nsa text[][]) on commit drop;
insert into t values(
'
http://www.w3.org/2001/XMLSchema-instance";
xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053.001.02">
1.34
5.56
'::xml,
ARRAY[ARRAY['ns','urn:iso:std:iso:20022:tech:xsd:camt.053.001.02']]);
SELECT
(xpath('Amt/text()', x,nsa))[1]::text::numeric AS tasusumma
FROM (
SELECT unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry',
x,nsa)) as x,
nsa
FROM t
) Ntry
If you don't need compatibility with old versions, I would use XMLTABLE in
Postgres 11
select x.*
from t,
xmltable(
XMLNAMESPACES(t.nsa[1][2] AS ns),
'/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry'
passing t.x
columns tasusumma numeric path 'ns:Amt'
) as x;
Re: How to parse XML in Postgres newer versions also
Hi
>This variant is working
>postgres=# SELECT
>(xpath('/ns:Ntry/ns:Amt/text()', x,nsa))[1]::text::numeric AS tasusumma
>FROM (
>SELECT unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry',
>x,nsa)) as x,
>nsa
>FROM t
>) Ntry
>;
>But I have not a idea, why old code doesn't work. It is little bit strange so
>it worked without namespace before Amt tag.
In 9.1.5 it returns nulls
In 9.1.5 without namespaces
(xpath('Amt/text()', x,nsa))[1]::text::numeric AS tasusumma
works.
How to make it work in both versions?
Is it possible add some CASE WHEN or IF command or any other idea ?
Andrus.
Re: Facing issue in using special characters
Many of us have faced character encoding issues because we are not in control of our input sources and made the common assumption that UTF-8 covers everything. In my lab, as an example, some of our social media posts have included ZawGyi Burmese character sets rather than Unicode Burmese. (Because Myanmar developed technology In a closed to the world environment, they made up their own non-standard character set which is very common still in Mobile phones.). We had fully tested the app with Unicode Burmese, but honestly didn’t know ZawGyi was even a thing that we would see in our dataset. We’ve also had problems with non-Unicode word separators in Arabic. What we’ve found to be helpful is to view the troubling code in a hex editor and determine what non-standard characters may be causing the problem. It may be some data conversion is necessary before insertion. But the first step is knowing WHICH characters are causing the issue.
Re: How to parse XML in Postgres newer versions also
ne 17. 3. 2019 v 15:19 odesílatel Andrus napsal:
> Hi!
>
> >You can use XMLTABLE function
> >select xmltable.*
> > from t,
> > lateral
> > xmltable(xmlnamespaces('urn:iso:std:iso:20022:tech:xsd:camt.053.001.02'
> as
> > ns),
> >
> > '/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry/.' passing t.x
> > columns tasusumma numeric path 'ns:Amt')
>
> In 9.1.5 this throws error
>
> ERROR: syntax error at or near "("
> LINE 20: lateral xmltable(xmlnamespaces(
> ^
> SQL state: 42601
> Character: 582
>
sure, XMLTABLE is supported from PostgreSQL 10.
Pavel
> Andrus.
>
>
>
>
>
>
>
>
>
Re: How to parse XML in Postgres newer versions also
ne 17. 3. 2019 v 15:11 odesílatel Andrus napsal:
> Hi
>
> >This variant is working
> >postgres=# SELECT
> >(xpath('/ns:Ntry/ns:Amt/text()', x,nsa))[1]::text::numeric AS
> tasusumma
> >FROM (
> >SELECT
> unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry',
> >x,nsa)) as x,
> >nsa
> >FROM t
> >) Ntry
> >;
> >But I have not a idea, why old code doesn't work. It is little bit
> strange so it worked without namespace before Amt tag.
> In 9.1.5 it returns nulls
>
> In 9.1.5 without namespaces
>
> (xpath('Amt/text()', x,nsa))[1]::text::numeric AS tasusumma
>
> works.
>
> How to make it work in both versions?
> Is it possible add some CASE WHEN or IF command or any other idea ?
>
Probably this bugfix breaks your code
https://github.com/postgres/postgres/commit/79af9a1d2668c9edc8171f03c39e7fed571eeb98#diff-b0a58a99389873468848ebb302aa8185
9.1 is unsupported release, so if you can, just fix it for supported
releases.
else where, you need to prepare two different expressions.
Regards
Pavel
>
> Andrus.
>
Re: How to parse XML in Postgres newer versions also
"Andrus" writes:
>> But I have not a idea, why old code doesn't work. It is little bit strange
>> so it worked without namespace before Amt tag.
> In 9.1.5 without namespaces
> (xpath('Amt/text()', x,nsa))[1]::text::numeric AS tasusumma
> works.
> How to make it work in both versions?
I'm hardly an XML expert, but I think the behavior change that's getting
you came in with these commits:
Author: Peter Eisentraut
Branch: REL9_4_STABLE Release: REL9_4_1 [6bbf75192] 2015-01-17 22:11:20
-0500
Branch: REL9_3_STABLE Release: REL9_3_6 [e32cb8d0e] 2015-01-17 22:13:27
-0500
Branch: REL9_2_STABLE Release: REL9_2_10 [c8ef5b1ac] 2015-01-17 22:14:21
-0500
Branch: REL9_1_STABLE Release: REL9_1_15 [c975fa471] 2015-01-17 22:37:07
-0500
Branch: REL9_0_STABLE Release: REL9_0_19 [cebb3f032] 2015-01-17 22:37:32
-0500
Fix namespace handling in xpath function
Previously, the xml value resulting from an xpath query would not have
namespace declarations if the namespace declarations were attached to
an ancestor element in the input xml value. That means the output value
was not correct XML. Fix that by running the result value through
xmlCopyNode(), which produces the correct namespace declarations.
Author: Ali Akbar
As things currently work, the lower xpath call is producing
namespace-labeled XML:
regression=# SELECT
unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry',
x,nsa)) as x
FROM t;
x
---
+
1.34+
+
5.56+
(2 rows)
so you have to attach namespaces to the elements of the upper xpath
call if you want a match:
regression=# SELECT
(xpath('ns:Ntry/ns:Amt/text()', x,nsa)) AS tasusumma
FROM (
SELECT unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry',
x,nsa)) as x,
nsa
FROM t
) Ntry
;
tasusumma
---
{1.34}
{5.56}
(2 rows)
but of course that'd fail in 9.1.5 because the output of the lower
xpath call bears no namespace labels :-(. (Or so I think anyway,
I don't have any such installation handy to try.)
The behavior you're seeing in 9.1.5 is just broken, so ideally what
you'd do to resolve the cross-version discrepancy is update that
installation to 9.1.15 or later. Or perhaps you could drop the usage
of namespaces from this query? I think the old and new behaviors are
the same if no namespaces are involved.
regards, tom lane
Re: How to parse XML in Postgres newer versions also
Hi!
You can use XMLTABLE function
select xmltable.*
from t,
lateral
xmltable(xmlnamespaces('urn:iso:std:iso:20022:tech:xsd:camt.053.001.02' as
ns),
'/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry/.' passing t.x
columns tasusumma numeric path 'ns:Amt')
In 9.1.5 this throws error
ERROR: syntax error at or near "("
LINE 20: lateral xmltable(xmlnamespaces(
^
SQL state: 42601
Character: 582
Andrus.
software or hardware RAID?
We aren't sure whether to use software MDRaid or a MegaRAID card. We're buying some new Postgres servers with 2 x 240GB Intel SSD S4610 (RAID1 : system) 4 x 960GB Intel SSD S4610 (RAID10 : db) We'll be using Postgres 11 on Debian. The MegaRAID 9271-8i with flash cache protection is available from our provider. I think they may also have the 9361-8i which is 12Gb/s. Our current servers which use the LSI 9261 with SSDs and we don't see any IO significant load as we are in RAM most of the time and the RAID card seems to flatten out any IO spikes. We use MDRaid elsewhere but we've never used it for our databases before. Advice gratefully received. Rory [A previous version of this was sent to the PostgreSQL performance list]
Re: Upgrade standby after starting cluster using rsync
Greetings, * Martín Fernández (fmarti...@gmail.com) wrote: > I've wrote a couple of questions around pg_upgrade and updating standbys > using rsync last week. We were able to successfully upgrade half of our > cluster (the other half was kept for failover) from pg92 with postgis 1.5.8 > to pg10 with postgis 2.4. It was a really interesting challenge because of > postgis binary incompatibility for geometry data types. > > The rsync call that we used looked exactly like this (taken from pg_upgrade > man page basically): > > `rsync --verbose --verbose --progress --archive --delete --hard-links > --size-only --no-inc-recursive /var/lib/postgres/9.2 /var/lib/postgres/10 > $REPLICA_IP:/var/lib/postgres` > > We are now in the journey of upgrading the other half of the cluster since we > have concluded that the upgrade was successful. > > We are planning on using the same rsync call to upgrade the rest of the > standbys (in combination with pg_start_backup/pg_stop_backup low level api). > My only concern is that I'm not 100% sure if the `--size-only` flag will be > enough to guarantee that files are the same. On the initial set of standbys > that we upgraded this shouldn't generate an issue since the standbys were at > the same last checkpoint than the master and we did the rsync call before > starting the primary (after running pg_upgrade). No, you can't use --size-only to rebuild those replicas while the primary is online, even if you're using pg_start/stop_backup, you should really enable the rsync checksums-based check to make sure that you're copying all of the files that you need to from the primary to the replica during the pg_start/stop_backup, and then you need to make sure and have an appropriate backup_label installed on all the replicas to get them to replay from the pg_start_backup checkpoint and to replay through to the end of the pg_stop_backup. You would have a recovery.conf file already but you might need to make sure it has a restore_command which can pull back WAL that might have already been archived by the primary. Note that this method of rebuilding the replicas will likely be time-consuming but unfortunately it's necessary. There are alternatives to using rsync to perform this if you need to get it done faster. > Is there any potential issues that could show up if we do it with --size-only > ? Should we use the default rsync mechanism that would check for size and > timestamps ? I wouldn't trust just size/timestamp in this case, you really should use checksums. > Hoping someone has some better experience than me on upgrading standbys using > rsync. The rsync-based pg_upgrade mechanism for replicas *only* works when it's done after all of the systems have been shut down and you've verified that all the nodes reached the same shutdown checkpoint, it is *not* appropriate for online rebuilding of replicas. Thanks! Stephen signature.asc Description: PGP signature
