Re: Re: Removing INNER JOINs

[David Rowley]

On 1 December 2017 at 15:34, Andreas Joseph Krogh 
wrote:
>
> Can someone please explain, in layman-terms, what the problems with FKs
> are related to JOIN-removal?
>

Pretty much what I just wrote after "Unfortunately not," above, although
you asked a few seconds before I sent.

We're able to use UNIQUE INDEXes as proofs to remove LEFT JOINs as (with
the exception of deferred unique indexes) these are updated right away,
rather than deferred until the end of the statement as is the case with NOT
DEFERRABLE and not DEFERRED foreign keys. The fact that the foreign keys do
not update the referenced rows right away means that there is a non-zero
window of time that the constraint is violated, therefore, if a query which
is run, or is running during that time, we could return the incorrect
results if we were to remove an INNER JOIN during the planning of that
query.

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

Sv: Re: Removing INNER JOINs

[Andreas Joseph Krogh]

På fredag 01. desember 2017 kl. 03:30:21, skrev Simon Riggs <
si...@2ndquadrant.com >:
On 1 December 2017 at 12:20, David Rowley  wrote:
 > On 1 December 2017 at 02:52, Andreas Joseph Krogh  
wrote:
 >>
 >> I came across this from Oracle: 
https://oracle-base.com/articles/misc/join-elimination#basic-join-elimination
 >>
 >> Needless to say, this would be very cool to have in PG:-)
 >
 > It would be nice, I agree.
 >
 >> It seems this has been discussed before [1], [2], [3], and the consesus at 
the time was that the proposted implementation introduced way too much 
planning-overhead to be worth it. Given that other RDBMS-vendors provides this, 
and it's on the "Cool feactures other have that we don't"-list [4], is anyone 
interessted in working on improving this?
 >
 > The large hurdle which a good workaround was never really found for
 > was the fact that foreign key triggers only update the referenced rows
 > at the end of the statement, or end of query when the foreign key
 > constraint is deferred. I don't recall much concern about planner
 > overhead. It's likely not going to be too big a concern since we're
 > already checking for foreign keys nowadays during selectivity
 > estimation.
 >
 > I do still have all the code I wrote all those years ago, and possibly
 > it will still apply to master as I rebased it just several months ago.
 > I've just not yet come up with any bright ideas on how to solve the
 > foreign key trigger timing problem.

 So it would work if the Foreign Keys are marked NOT DEFERRABLE?
 
Can someone please explain, in layman-terms, what the problems with FKs are 
related to JOIN-removal?
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: Removing INNER JOINs

[David Rowley]

On 1 December 2017 at 15:30, Simon Riggs  wrote:
> On 1 December 2017 at 12:20, David Rowley  
> wrote:
>> The large hurdle which a good workaround was never really found for
>> was the fact that foreign key triggers only update the referenced rows
>> at the end of the statement, or end of query when the foreign key
>> constraint is deferred. I don't recall much concern about planner
>> overhead. It's likely not going to be too big a concern since we're
>> already checking for foreign keys nowadays during selectivity
>> estimation.
>>
>> I do still have all the code I wrote all those years ago, and possibly
>> it will still apply to master as I rebased it just several months ago.
>> I've just not yet come up with any bright ideas on how to solve the
>> foreign key trigger timing problem.
>
> So it would work if the Foreign Keys are marked NOT DEFERRABLE?

Unfortunately not, since a query may call some volatile function which
makes changes to referenced rows which could cause the removed join to
falsely "match" to referenced rows which no longer exist due to the
changes made by the volatile function not having been CASCADEd to the
referenced table yet.

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

Re: Removing INNER JOINs

[Simon Riggs]

On 1 December 2017 at 12:20, David Rowley  wrote:
> On 1 December 2017 at 02:52, Andreas Joseph Krogh  wrote:
>>
>> I came across this from Oracle: 
>> https://oracle-base.com/articles/misc/join-elimination#basic-join-elimination
>>
>> Needless to say, this would be very cool to have in PG:-)
>
> It would be nice, I agree.
>
>> It seems this has been discussed before [1], [2], [3], and the consesus at 
>> the time was that the proposted implementation introduced way too much 
>> planning-overhead to be worth it. Given that other RDBMS-vendors provides 
>> this, and it's on the "Cool feactures other have that we don't"-list [4], is 
>> anyone interessted in working on improving this?
>
> The large hurdle which a good workaround was never really found for
> was the fact that foreign key triggers only update the referenced rows
> at the end of the statement, or end of query when the foreign key
> constraint is deferred. I don't recall much concern about planner
> overhead. It's likely not going to be too big a concern since we're
> already checking for foreign keys nowadays during selectivity
> estimation.
>
> I do still have all the code I wrote all those years ago, and possibly
> it will still apply to master as I rebased it just several months ago.
> I've just not yet come up with any bright ideas on how to solve the
> foreign key trigger timing problem.

So it would work if the Foreign Keys are marked NOT DEFERRABLE?

-- 
Simon Riggshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Sv: Re: Removing INNER JOINs

[Andreas Joseph Krogh]

På fredag 01. desember 2017 kl. 02:20:19, skrev David Rowley <
david.row...@2ndquadrant.com >:
On 1 December 2017 at 02:52, Andreas Joseph Krogh  wrote:
 >
 > I came across this from Oracle: 
https://oracle-base.com/articles/misc/join-elimination#basic-join-elimination
 >
 > Needless to say, this would be very cool to have in PG:-)

 It would be nice, I agree.

 > It seems this has been discussed before [1], [2], [3], and the consesus at 
the time was that the proposted implementation introduced way too much 
planning-overhead to be worth it. Given that other RDBMS-vendors provides this, 
and it's on the "Cool feactures other have that we don't"-list [4], is anyone 
interessted in working on improving this?

 The large hurdle which a good workaround was never really found for
 was the fact that foreign key triggers only update the referenced rows
 at the end of the statement, or end of query when the foreign key
 constraint is deferred. I don't recall much concern about planner
 overhead. It's likely not going to be too big a concern since we're
 already checking for foreign keys nowadays during selectivity
 estimation.

 I do still have all the code I wrote all those years ago, and possibly
 it will still apply to master as I rebased it just several months ago.
 I've just not yet come up with any bright ideas on how to solve the
 foreign key trigger timing problem.
 
Thanks for answer.
 
I guess I'm back to hoping someone will spend time thinking about those 
challenges. In our app we have more and more dynamic queries which join lots of 
tables but not necessarily SELECTs from them. Maintaining the list of 
join-tables when constructing the dynamic queries is a pain...
 
Especially when running "paging-queries", like "Give me top 100 of 899 345 
totals". We fire two queries, one with the SELECT-list and one with count(*), 
but with the same FROM-clause. I see LEFT OUTER JOINs are being removed when 
issuing the count(*), which helps, but remove more JOINs would (probably) help 
even more:-)
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: Removing INNER JOINs

[David Rowley]

On 1 December 2017 at 02:52, Andreas Joseph Krogh  wrote:
>
> I came across this from Oracle: 
> https://oracle-base.com/articles/misc/join-elimination#basic-join-elimination
>
> Needless to say, this would be very cool to have in PG:-)

It would be nice, I agree.

> It seems this has been discussed before [1], [2], [3], and the consesus at 
> the time was that the proposted implementation introduced way too much 
> planning-overhead to be worth it. Given that other RDBMS-vendors provides 
> this, and it's on the "Cool feactures other have that we don't"-list [4], is 
> anyone interessted in working on improving this?

The large hurdle which a good workaround was never really found for
was the fact that foreign key triggers only update the referenced rows
at the end of the statement, or end of query when the foreign key
constraint is deferred. I don't recall much concern about planner
overhead. It's likely not going to be too big a concern since we're
already checking for foreign keys nowadays during selectivity
estimation.

I do still have all the code I wrote all those years ago, and possibly
it will still apply to master as I rebased it just several months ago.
I've just not yet come up with any bright ideas on how to solve the
foreign key trigger timing problem.

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

Re: ERROR: could not load library libperl.so, PostgreSQL trying to CREATE EXTENSION plperlu

[Alan Hodgson]

On Thu, 2017-11-30 at 22:59 +, Ben Nachtrieb wrote:
> Alan,
> 
>  
> 
> Thank you!  Solution: build them from source on the server?
Well, it would be more maintainable to find a source for packages built
for your particular OS. Or run a supported OS; that one looks pretty
old.
Or I guess you can build from source.
> 
> 
> 

Re: copy error with json/jsonb types

[Michael Paquier]

On Thu, Nov 30, 2017 at 3:01 PM, tao tony  wrote:
> hi dears,
>
>
> I'm using copy and jdbc copyin to build load data to pg,data type in table
> is jsonb.postgresql version are 9.6 and 10.0.
>
> Some records with escape characters would be failed with error"ERROR:
> invalid input syntax for type json"
>
> but when using insert they could be executed successfully.
>
> please kindly tell me how to fix this issue?

The lookup of JSON strings is done in json_lex_string(). If I
copy-paste the upthread values into a manual INSERT the tuples get
inserted, operation done using UTF-8. And both json and jsonb are
proving to work.
-- 
Michael

RE: ERROR: could not load library libperl.so, PostgreSQL trying to CREATE EXTENSION plperlu

[Ben Nachtrieb]

Alan,

Thank you!  Solution: build them from source on the server?
Ben
From: Alan Hodgson [mailto:ahodg...@lists.simkin.ca]
Sent: Thursday, November 30, 2017 3:36 PM
To: pgsql-general 
Subject: Re: ERROR: could not load library libperl.so, PostgreSQL trying to 
CREATE EXTENSION plperlu

On Thu, 2017-11-30 at 22:20 +, Ben Nachtrieb wrote:

...to ld.so.conf, I get:
ERROR:  could not load library "/var/lib/pgsql10/lib/postgresql/plperl.so": 
/var/lib/pgsql10/lib/postgresql/plperl.so: undefined symbol: Perl_xs_handshake
SQL state: XX000


It looks to me like your plperl is built against a substantially different 
version of Perl than what you have installed on the server.

I'd guess you installed a PostgreSQL binary built on a different OS version, or 
have otherwise mismatched packages.

Re: ERROR: could not load library libperl.so, PostgreSQL trying to CREATE EXTENSION plperlu

[Alan Hodgson]

On Thu, 2017-11-30 at 22:20 +, Ben Nachtrieb wrote:
>  
> 
> ...to ld.so.conf, I get:
> 
> ERROR:  could not load library
> "/var/lib/pgsql10/lib/postgresql/plperl.so":
> /var/lib/pgsql10/lib/postgresql/plperl.so: undefined symbol:
> Perl_xs_handshake
> 
> SQL state: XX000

It looks to me like your plperl is built against a substantially
different version of Perl than what you have installed on the server.
I'd guess you installed a PostgreSQL binary built on a different OS
version, or have otherwise mismatched packages.
> 
> 

ERROR: could not load library libperl.so, PostgreSQL trying to CREATE EXTENSION plperlu

[Ben Nachtrieb]

Hello,

I want to use the perl language in a postgres function.

I am attempting to:
CREATE EXTENSION plperlu

...via pgAdmin 4 connected to postgres 10 and I get this error:
ERROR:  could not load library "/var/lib/pgsql10/lib/postgresql/plperl.so": 
libperl.so: cannot open shared object file: No such file or directory
SQL state: XX000

I look for the library:
find -name libperl.so

...and I see it in these locations:
./usr/lib/perl5/5.18.2/i586-linux-thread-multi/CORE/libperl.so
./usr/lib/perl5/5.18.2/x86_64-linux-thread-multi/CORE/libperl.so

So I conclude that it is already on my server distribution, but isn't in the 
location postgres is looking so I search and find this link:
https://www.postgresql.org/message-id/D960CB61B694CF459DCFB4B0128514C207B2C9DC%40exadv11.host.magwien.gv.at

...and I am opting do this (from the link above, but I have different paths): 
"- Add the directory that contains libperl.so to /etc/ld.so.conf and run 
"ldconfig" as root user. Then start the PostgreSQL server."

So I find the config file:
find -name ld.so.conf

And get this path:
./etc/ld.so.conf

...and edit the file:
vi ./etc/ld.so.conf

... and add this path:
./usr/lib/perl5/5.18.2/x86_64-linux-thread-multi/CORE/

... load the new config:
ldconfig

... restart pg:
systemctl restart postgresql-10.service

... run this query:
CREATE EXTENSION plperlu;

...and get the same error:
ERROR:  could not load library "/var/lib/pgsql10/lib/postgresql/plperl.so": 
libperl.so: cannot open shared object file: No such file or directory
SQL state: XX000

...when I replace the previous path and add this path:
/usr/lib/perl5/5.18.2/x86_64-linux-thread-multi/CORE

...to ld.so.conf, I get:
ERROR:  could not load library "/var/lib/pgsql10/lib/postgresql/plperl.so": 
/var/lib/pgsql10/lib/postgresql/plperl.so: undefined symbol: Perl_xs_handshake
SQL state: XX000

... a little different error, but I don't know if I am closer or further away.
How do I get PostgreSQL pointed to the correct library so I can use perl?

I have:

  *   PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 
20120313 (Red Hat 4.4.7-18), 64-bit
  *   pgAdmin 4 2.0
  *   SUSE Linux Enterprise 12 64-bit virtual server

PS - If you prefer Stackoverflow, I asked the exact question here, but haven't 
gotten any traction yet:
https://stackoverflow.com/questions/47578148/error-could-not-load-library-libperl-so-postgresql-trying-to-create-extension

Thanks!
Ben

Re: large numbers of inserts out of memory strategy

[Christopher Browne]

On 30 November 2017 at 05:22, Peter J. Holzer  wrote:
> On 2017-11-29 08:32:02 -0600, Ted Toth wrote:
>> Yes I did generate 1 large DO block:
>>
>> DO $$
>> DECLARE thingid bigint; thingrec bigint; thingdataid bigint;
>> BEGIN
>> INSERT INTO thing
>> (ltn,classification,machine,source,thgrec,flags,serial,type) VALUES
>> ('T007336','THING',0,1025,7336,7,'XXX869977564',1) RETURNING id,thgrec
>> INTO thingid,thingrec;
>> INSERT INTO recnum_thing (recnum,thing_id) VALUES (thingrec,thingid);
>> INSERT INTO thingstatus
>> (thing_id,nrpts,rmks_cs,force_type_id,ftn_cs,force_code,arr_cs,mask,toi_state,plot_id,signa_cs,lastchange,des_cs,rig_cs,ownship,correlation,maxrpts,rtn_cs,ctc_cs,group_mask,dep_cs)
>> VALUES 
>> (thingid,121,'{0,0,0,0}',440,0,23,0,0,0,'{23,-1,3803,3805,-1,-1,0,6}',0,1509459164,0,0,0,0,1000,0,0,0,0);
>> INSERT INTO thinger
>> (thing_id,spe_key,cse_unc,lat_spd,cov,dtg,lng,spd,ave_spd,cse,tol,nrpts,lat,alpha,sigma,spd_unc,lng_spd)
>> VALUES 
>> (thingid,-1,0.0,-6.58197336634e-08,'{4.27624291532e-09,0.0,3.07802916488e-09,0.0,4.27624291532e-09,0.0,3.07802916488e-09,4.16110417234e-08,0.0,4.16110417234e-08}',1509459163,2.21596980095,0.000226273215958,1.0,0.0,0.1000149,121,0.584555745125,10.0,4.23079740131e-08,0.0,-2.4881907e-10);
>> INSERT INTO thingdata
>> (thing_id,category,db_num,xref,org_type,trademark,shortname,fcode,platform,callsign,type,orig_xref,shipclass,home_base,uic,service,di,lngfixed,hull,precision,alert,flag,besufx,name,mmsi,catcode,ntds,imo,pn_num,chxref,threat,sconum,latfixed,db_type,pif,echelon,jtn,quantity,overwrite)
>> VALUES 
>> (thingid,'XXX','','','','','004403704','23','','','','','UNEQUATED','','','','',0.0,'','{0,0,0,0,0}','','KS','','UNKNOWN','004403704','','','','','','AFD','',0.0,3,'','',0,0,0)
>> RETURNING id INTO thingdataid;
>> INSERT INTO thingnum (thingdata_id,thgnum,state,dtg,cmd) VALUES
>> (thingdataid,'013086',0,1502970401,'FOO');
>>
>> 
>>
>> END $$;
>>
>> Should I limit the number of 'thing' inserts within a DO block or
>> wrapping each 'thing' insert in it's own DO block?
>
> I would suggest getting rid of the do block entirely if that is
> possible. Just create lots of insert statements. You can get the current
> value of a sequence with currval('sequence_name').
>
> Alternately or in addition, since you are using python, you might want
> to insert directly into the database from python using psycopg2. For
> separate insert statements that should have about the same performance.
> (It is usually much faster to write to a csv file and load that with
> copy than to insert each row, but you don't do that and it might be
> difficult in your case).

A long time ago (pre version 2.2), Slony-I used to do this, having a big
stream of INSERT, UPDATE, and DELETE statements.

This seemed pretty OK when tuples weren't very large, but we did get
some reports in from people with applications with very large tuples
that would run into out-of-memory conditions if a bunch of INSERTs
involving very large tuples were processed together.

The "fix" we initially did was to have the bulk processing stream
hold off on any Very Large Tuples, so that when we'd encounter
them, we'd process the big tuples one by one.  This worked
relatively OK, but meant that we'd be switching logic every time
there was big data, and there was a pretty painful amount of
configuration to force people to worry about.

The "2.2" change was to switch to COPY-based streaming.  In
our case, we put a trigger onto the single log table and have that
initiate tossing data as it came in into the appropriate target table.

That introduced an Amount Of Complexity (e.g. - a rather complex
stored function in C/SPI), but it's notable that we got a pretty big
performance boost as well as complete elimination of memory
allocation worries out of turning the process into COPY streaming.

It may be challenging to get a suitable set of COPY requests to do
what is necessary.  (It sure would be cool if it could just be one...)
But there are three crucial things I'd observe:
a) Loading data via COPY is *way* faster,
b) Parsing INSERT statements is a *big* slowdown,
c) Parsing INSERT statements means that those statements must
be drawn into memory, and that chews a lot of memory if the query
has very large attributes.  (COPY doesn't chew that memory!)

At one point, Jan Wieck was looking into an alternative COPY
protocol that would have allowed more actions, notably:

- You could specify multiple tables to stream into, and switch
  between them on a tuple by tuple basis.

- You could specify actions of INSERT, UPDATE, or DELETE.

It seemed like a really interesting idea at the time; it was intended
to be particularly useful for Slony, but some folks designing data
warehouse ETL systems observed that it would be useful to them,
too.
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

Re: large numbers of inserts out of memory strategy

[Christopher Browne]

On 30 November 2017 at 05:22, Peter J. Holzer  wrote:
> On 2017-11-29 08:32:02 -0600, Ted Toth wrote:
>> Yes I did generate 1 large DO block:
>>
>> DO $$
>> DECLARE thingid bigint; thingrec bigint; thingdataid bigint;
>> BEGIN
>> INSERT INTO thing
>> (ltn,classification,machine,source,thgrec,flags,serial,type) VALUES
>> ('T007336','THING',0,1025,7336,7,'XXX869977564',1) RETURNING id,thgrec
>> INTO thingid,thingrec;
>> INSERT INTO recnum_thing (recnum,thing_id) VALUES (thingrec,thingid);
>> INSERT INTO thingstatus
>> (thing_id,nrpts,rmks_cs,force_type_id,ftn_cs,force_code,arr_cs,mask,toi_state,plot_id,signa_cs,lastchange,des_cs,rig_cs,ownship,correlation,maxrpts,rtn_cs,ctc_cs,group_mask,dep_cs)
>> VALUES 
>> (thingid,121,'{0,0,0,0}',440,0,23,0,0,0,'{23,-1,3803,3805,-1,-1,0,6}',0,1509459164,0,0,0,0,1000,0,0,0,0);
>> INSERT INTO thinger
>> (thing_id,spe_key,cse_unc,lat_spd,cov,dtg,lng,spd,ave_spd,cse,tol,nrpts,lat,alpha,sigma,spd_unc,lng_spd)
>> VALUES 
>> (thingid,-1,0.0,-6.58197336634e-08,'{4.27624291532e-09,0.0,3.07802916488e-09,0.0,4.27624291532e-09,0.0,3.07802916488e-09,4.16110417234e-08,0.0,4.16110417234e-08}',1509459163,2.21596980095,0.000226273215958,1.0,0.0,0.1000149,121,0.584555745125,10.0,4.23079740131e-08,0.0,-2.4881907e-10);
>> INSERT INTO thingdata
>> (thing_id,category,db_num,xref,org_type,trademark,shortname,fcode,platform,callsign,type,orig_xref,shipclass,home_base,uic,service,di,lngfixed,hull,precision,alert,flag,besufx,name,mmsi,catcode,ntds,imo,pn_num,chxref,threat,sconum,latfixed,db_type,pif,echelon,jtn,quantity,overwrite)
>> VALUES 
>> (thingid,'XXX','','','','','004403704','23','','','','','UNEQUATED','','','','',0.0,'','{0,0,0,0,0}','','KS','','UNKNOWN','004403704','','','','','','AFD','',0.0,3,'','',0,0,0)
>> RETURNING id INTO thingdataid;
>> INSERT INTO thingnum (thingdata_id,thgnum,state,dtg,cmd) VALUES
>> (thingdataid,'013086',0,1502970401,'FOO');
>>
>> 
>>
>> END $$;
>>
>> Should I limit the number of 'thing' inserts within a DO block or
>> wrapping each 'thing' insert in it's own DO block?
>
> I would suggest getting rid of the do block entirely if that is
> possible. Just create lots of insert statements. You can get the current
> value of a sequence with currval('sequence_name').
>
> Alternately or in addition, since you are using python, you might want
> to insert directly into the database from python using psycopg2. For
> separate insert statements that should have about the same performance.
> (It is usually much faster to write to a csv file and load that with
> copy than to insert each row, but you don't do that and it might be
> difficult in your case).

Once upon a time, Slony-I used to use INSERT/UPDATE/DELETE to
do all of its work, so that the replication stream consisted of gigantic
numbers of INSERT/UPDATE/DELETE statements that had to be
parsed and processed.


-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

Re: Searching for big differences between values

[Laurenz Albe]

Durumdara wrote:
> Somewhere the users made mistakes on prices (stock).
> 
> I need to search for big differences between values.
> For example:
> 
> 20
> 21
> 21,5
> 30
> 28
> ..
> 46392 <-
> 46392 <-
> 
> But it could be:
> 
> 42300
> 43100
> 44000
> 43800
> 65000 <-
> 42100
> 
> Human eye could locate these values, but there we need to check 30.000 
> articles and 450.000 values.
> 
> Do you have any idea, how to this with SQL?

You could use some variant of

  SELECT id, price
  FROM (SELECT id, price,
   avg(price) OVER (ORDER BY id ROWS BETWEEN 3 PRECEDING AND 3 
FOLLOWING) AS av
FROM stock) q
  WHERE NOT (price BETWEEN 0.8 * av AND 1.25 * av);

Yours,
Laurenz Albe

Re: large numbers of inserts out of memory strategy

[Ted Toth]

On Thu, Nov 30, 2017 at 4:22 AM, Peter J. Holzer  wrote:
> On 2017-11-29 08:32:02 -0600, Ted Toth wrote:
>> Yes I did generate 1 large DO block:
>>
>> DO $$
>> DECLARE thingid bigint; thingrec bigint; thingdataid bigint;
>> BEGIN
>> INSERT INTO thing
>> (ltn,classification,machine,source,thgrec,flags,serial,type) VALUES
>> ('T007336','THING',0,1025,7336,7,'XXX869977564',1) RETURNING id,thgrec
>> INTO thingid,thingrec;
>> INSERT INTO recnum_thing (recnum,thing_id) VALUES (thingrec,thingid);
>> INSERT INTO thingstatus
>> (thing_id,nrpts,rmks_cs,force_type_id,ftn_cs,force_code,arr_cs,mask,toi_state,plot_id,signa_cs,lastchange,des_cs,rig_cs,ownship,correlation,maxrpts,rtn_cs,ctc_cs,group_mask,dep_cs)
>> VALUES 
>> (thingid,121,'{0,0,0,0}',440,0,23,0,0,0,'{23,-1,3803,3805,-1,-1,0,6}',0,1509459164,0,0,0,0,1000,0,0,0,0);
>> INSERT INTO thinger
>> (thing_id,spe_key,cse_unc,lat_spd,cov,dtg,lng,spd,ave_spd,cse,tol,nrpts,lat,alpha,sigma,spd_unc,lng_spd)
>> VALUES 
>> (thingid,-1,0.0,-6.58197336634e-08,'{4.27624291532e-09,0.0,3.07802916488e-09,0.0,4.27624291532e-09,0.0,3.07802916488e-09,4.16110417234e-08,0.0,4.16110417234e-08}',1509459163,2.21596980095,0.000226273215958,1.0,0.0,0.1000149,121,0.584555745125,10.0,4.23079740131e-08,0.0,-2.4881907e-10);
>> INSERT INTO thingdata
>> (thing_id,category,db_num,xref,org_type,trademark,shortname,fcode,platform,callsign,type,orig_xref,shipclass,home_base,uic,service,di,lngfixed,hull,precision,alert,flag,besufx,name,mmsi,catcode,ntds,imo,pn_num,chxref,threat,sconum,latfixed,db_type,pif,echelon,jtn,quantity,overwrite)
>> VALUES 
>> (thingid,'XXX','','','','','004403704','23','','','','','UNEQUATED','','','','',0.0,'','{0,0,0,0,0}','','KS','','UNKNOWN','004403704','','','','','','AFD','',0.0,3,'','',0,0,0)
>> RETURNING id INTO thingdataid;
>> INSERT INTO thingnum (thingdata_id,thgnum,state,dtg,cmd) VALUES
>> (thingdataid,'013086',0,1502970401,'FOO');
>>
>> 
>>
>> END $$;
>>
>> Should I limit the number of 'thing' inserts within a DO block or
>> wrapping each 'thing' insert in it's own DO block?
>

Thanks for the specific suggestions.

> I would suggest getting rid of the do block entirely if that is
> possible. Just create lots of insert statements. You can get the current
> value of a sequence with currval('sequence_name').

What is the downside of using a DO block? I'd have to do a nextval on
each sequence before I could use currval, right? Or I could do 'select
last_value from '.

One thing that is unclear to me is when commits occur while using psql
would you know where in the docs I can find information on this
subject?

>
> Alternately or in addition, since you are using python, you might want
> to insert directly into the database from python using psycopg2. For
> separate insert statements that should have about the same performance.
> (It is usually much faster to write to a csv file and load that with
> copy than to insert each row, but you don't do that and it might be
> difficult in your case).

Yes, I thought about generating csv files but didn't see a way to deal
with the foreign keys.

>
> hp
>
> --
>_  | Peter J. Holzer| we build much bigger, better disasters now
> |_|_) || because we have much more sophisticated
> | |   | h...@hjp.at | management tools.
> __/   | http://www.hjp.at/ | -- Ross Anderson 

Re: Searching for big differences between values

[Chris Mair]

I need to search for big differences between values.

[...]

Hi,

from an SQL point of view this is not difficult, but you need to
carefully define a criteria for the outliers.

For example, to find values that are more than a standard deviation
away from the mean, do something like this:

chris=# select * from val;
   x
---
20
21
  21.5
30
28
 46392
 46393
40
(8 rows)

chris=# select * from val where x > (select avg(x) + stddev(x) from val) or x < 
(select avg(x) - stddev(x) from val);
   x
---
 46392
 46393
(2 rows)

Try with n*stddev(x) for n = 2, 3, 4, 5, ... to see
if you can get to your outliers...

Bye,
Chris.

copy error with json/jsonb types

[tao tony]

hi dears,


I'm using copy and jdbc copyin to build load data to pg,data type in table is 
jsonb.postgresql version are 9.6 and 10.0.

Some records with escape characters would be failed with error"ERROR:  invalid 
input syntax for type json"

but when using insert they could be executed successfully.

please kindly tell me how to fix this issue?

here is the table.

hdb=# \d salejsonb
 Table "public.salejsonb"
 Column  | Type  | Collation | Nullable | Default
-+---+---+--+-
 jsonval | jsonb |   |  |


below is th sample data:

{"uuid":"b93b0307-c71b-4c07-8f29-e490cad3e5f1","version":0,"createInfo":{"time":"2017-11-11T08:59:47.000+0800","operator":{"id":"13832122566","fullName":"马秀菊","qualifiedId":"13832122566"}},"lastModifyInfo":{"time":"2017-11-11T08:59:47.000+0800","operator":{"id":"13832122566","fullName":"马秀菊","qualifiedId":"13832122566"}},"number":"17-002","shop":"a02a7070","posNo":"1","amount":14.1000,"discountAmount":0.,"changeAmount":0.,"qty":0.4700,"lines":[{"uuid":"14fdcfe8-6199-4273-91a9-158d719722ae","line":1,"shopSku":"c0229a90-f6c0-41b6-9cd7-a5f85e371b08","shopSkuBarcode":"055","platformSku":"","skuName":"蒸蛋糕（散）","skuMunit":"公斤","price":30.,"qty":0.4700,"amount":14.1000,"costPrice":18.,"saleLine":"d47ea60e-da40-410c-80e6-7716eae2e592"},{"uuid":"c443ef5c-865d-458b-bb0a-af3f11446413","line":2,"shopSku":"c892cc8b-8bec-4fb2-95c3-b2466e09d6fb","shopSkuBarcode":"053","platformSku":"","skuName":"达利园派\\蛋糕（散）","skuMunit":"公斤","price":24.,"qty":0.,"amount":0.,"costPrice":16.,"saleLine":"1e114c97-83a3-4dce-8655-a17f21aeb39b"},{"uuid":"58be2eab-dd49-4b9e-983f-c17a7d3a4c7e","line":3,"shopSku":"c892cc8b-8bec-4fb2-95c3-b2466e09d6fb","shopSkuBarcode":"053","platformSku":"","skuName":"达利园派\\蛋糕（散）","skuMunit":"公斤","price":24.,"qty":0.,"amount":0.,"costPrice":16.,"saleLine":"09efe69a-7b61-4c67-a8c3-05c859e4210d"},{"uuid":"7a6a9613-e3b5-44d4-bbc1-b9ae2d6bdde8","line":4,"shopSku":"cf3ecdb1-6c1c-4536-ac25-14fd774f377e","shopSkuBarcode":"050","platformSku":"","skuName":"伟龙饼干（散）","skuMunit":"公斤","price":14.,"qty":0.,"amount":0.,"costPrice":9.6000,"saleLine":"bb56bfd0-39a5-45d8-9f01-5d2785ed17ac"},{"uuid":"195e4c03-b327-4df2-88ac-291070314a64","line":5,"shopSku":"b0880e17-6121-4d39-87c2-d55cd43c3313","shopSkuBarcode":"035","platformSku":"","skuName":"黑皮雪饼仙贝（散）","skuMunit":"公斤","price":22.,"qty":0.,"amount":0.,"costPrice":7.,"saleLine":"8fb2b7d5-4aff-4867-95dc-0bfb41c4b6e7"}],"payments":[{"uuid":"3da970b3-7cdc-4b3b-9256-b55c2c01a6ad","line":1,"paymentMethod":"CASH","paidAmount":14.1000,"payingAmount":0.,"payChannel":"CASH","created":"2017-11-11T08:59:47.000+0800"}],"sale":"a919eb97-5d2f-411b-9348-1f4a8902e807","saleNumber":"17-041","shiftName":"早班","shiftNameDate":"2017-11-11T00:00:00.000+0800","_dcbustype":"salereturn"}


{"uuid":"ba98867d-f05c-4b3e-9b2c-81cd1a46ee41","version":0,"createInfo":{"time":"2017-11-23T08:53:09.000+0800","operator":{"id":"1890201","fullName":"201","qualifiedId":"1890201"}},"lastModifyInfo":{"time":"2017-11-23T08:53:09.000+0800","operator":{"id":"1890201","fullName":"201","qualifiedId":"1890201"}},"number":"171123-002","encryptNumber":"171123-7449","shop":"a03a8283","posNo":"1","shiftName":"早班","shiftNameDate":"2017-11-23T00:00:00.000+0800","amount":2.,"discountAmount":0.,"changeAmount":0.,"qty":1.,"remark":"收银员手动点已付","lines":[{"uuid":"6ea72092-5db0-4973-a3b6-8f464c42758d","line":1,"shopSku":"9ebe9e92-13d0-4b7a-9493-9b841d3b4fe7","shopSkuBarcode":"6944591800691","platformSku":"4E878E6DFFBB49268CCB9B8CE46F5344","skuName":"创越漱口杯","skuMunit":"只","price":2.,"skuSalePrice":3.,"qty":1.,"amount":2.,"costPrice":2.2200,"saleLineFavItems":[{"uuid":"c6fbf7f4-ce5a-4a39-a623-aecf45a4bc02","price":0,"amount":0,"discount":1,"favType":"ChangePriceLineFavItem","favAmount":1.,"causeType":"MatchQfRuleFavCause","causeJson":"{\"tenant\":\"f62ba32a-b432-4786-9cf9-f7805d71dc40\",\"_cause_type_\":\"MatchQfRuleFavCause\",\"activity\":{\"id\":\"f01adece-30ea-4e62-a0c5-23f2d1c64b80\",\"namespace\":\"com.qianfan123.mp::Activity\"}}","activity":"f01adece-30ea-4e62-a0c5-23f2d1c64b80","activityNamespace":"com.qianfan123.mp::Activity","dependOnLines":[]}]}],"payments":[{"uuid":"b6cd982e-fdc5-416e-94d2-c54d61dbeeca","line":3,"paymentMethod":"ALIPAY","paidAmount":0.0100,"payingAmount":0.0100,"state":"PROCESSING","artificialState":"PAID","tranId":"0be234ea-6560-4146-8b09-3b96f1ff5ed9","payChannel":"ONLINE","created":"2017-11-23T08:53:09.000+0800"},{"uuid":"23d60861-a207-4f36-a0b4-f73381fb6f5f","line":4,"paymentMethod":"CASH","paidAmount":1.9900,"payingAmount":0.,"payChannel":"CASH","created":"2017-11-23T08:53:09.000+0800"}],"favDetails":[],"favItems":[],"_dcbustype":"sale"}

Re: large numbers of inserts out of memory strategy

[Peter J. Holzer]

On 2017-11-29 08:32:02 -0600, Ted Toth wrote:
> Yes I did generate 1 large DO block:
> 
> DO $$
> DECLARE thingid bigint; thingrec bigint; thingdataid bigint;
> BEGIN
> INSERT INTO thing
> (ltn,classification,machine,source,thgrec,flags,serial,type) VALUES
> ('T007336','THING',0,1025,7336,7,'XXX869977564',1) RETURNING id,thgrec
> INTO thingid,thingrec;
> INSERT INTO recnum_thing (recnum,thing_id) VALUES (thingrec,thingid);
> INSERT INTO thingstatus
> (thing_id,nrpts,rmks_cs,force_type_id,ftn_cs,force_code,arr_cs,mask,toi_state,plot_id,signa_cs,lastchange,des_cs,rig_cs,ownship,correlation,maxrpts,rtn_cs,ctc_cs,group_mask,dep_cs)
> VALUES 
> (thingid,121,'{0,0,0,0}',440,0,23,0,0,0,'{23,-1,3803,3805,-1,-1,0,6}',0,1509459164,0,0,0,0,1000,0,0,0,0);
> INSERT INTO thinger
> (thing_id,spe_key,cse_unc,lat_spd,cov,dtg,lng,spd,ave_spd,cse,tol,nrpts,lat,alpha,sigma,spd_unc,lng_spd)
> VALUES 
> (thingid,-1,0.0,-6.58197336634e-08,'{4.27624291532e-09,0.0,3.07802916488e-09,0.0,4.27624291532e-09,0.0,3.07802916488e-09,4.16110417234e-08,0.0,4.16110417234e-08}',1509459163,2.21596980095,0.000226273215958,1.0,0.0,0.1000149,121,0.584555745125,10.0,4.23079740131e-08,0.0,-2.4881907e-10);
> INSERT INTO thingdata
> (thing_id,category,db_num,xref,org_type,trademark,shortname,fcode,platform,callsign,type,orig_xref,shipclass,home_base,uic,service,di,lngfixed,hull,precision,alert,flag,besufx,name,mmsi,catcode,ntds,imo,pn_num,chxref,threat,sconum,latfixed,db_type,pif,echelon,jtn,quantity,overwrite)
> VALUES 
> (thingid,'XXX','','','','','004403704','23','','','','','UNEQUATED','','','','',0.0,'','{0,0,0,0,0}','','KS','','UNKNOWN','004403704','','','','','','AFD','',0.0,3,'','',0,0,0)
> RETURNING id INTO thingdataid;
> INSERT INTO thingnum (thingdata_id,thgnum,state,dtg,cmd) VALUES
> (thingdataid,'013086',0,1502970401,'FOO');
> 
> 
> 
> END $$;
> 
> Should I limit the number of 'thing' inserts within a DO block or
> wrapping each 'thing' insert in it's own DO block?

I would suggest getting rid of the do block entirely if that is
possible. Just create lots of insert statements. You can get the current
value of a sequence with currval('sequence_name').

Alternately or in addition, since you are using python, you might want
to insert directly into the database from python using psycopg2. For
separate insert statements that should have about the same performance.
(It is usually much faster to write to a csv file and load that with
copy than to insert each row, but you don't do that and it might be
difficult in your case).

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

Re: Searching for big differences between values

[Chris Travers]

On Nov 30, 2017 08:35, "Durumdara"  wrote:

Hello!

Somewhere the users made mistakes on prices (stock).

I need to search for big differences between values.
For example:

20
21
21,5
30
28
..
46392 <-
46392 <-

But it could be:

42300
43100
44000
43800
65000 <-
42100

Human eye could locate these values, but there we need to check 30.000
articles and 450.000 values.

Do you have any idea, how to this with SQL?

In most cases the first values are ok, the second interval (after a date)
it could be wrong...

I don't know how to define the solution, but I think PGSQL have intelligent
solution for this problem.

We need to search for elements have bigger value like base price *
tolerance.
But the base price is calculated dynamically from the lower values... The
tolerance is lower on highest base prices.


The good news is relational dbs excel at performing.  However you have not
provided nearly enough info to help write a query.  How are such things
calculated?


Thank you for any help!

Best regards
   dd