** UnhandledExceptionEventHandler :: OFFICIAL LC / CHATTER THREAD ** - Page 608 - Computer Technical Help

Two Plus Two Forums Other Topics Computer and Technical Help

** UnhandledExceptionEventHandler :: OFFICIAL LC / CHATTER THREAD **

Post Reply Subscribe

...

Page 608 of 1603

First

108 408 508 558 588 598 603 604 605 606 607 608 609 610 611 612 613 618 628 658 708 808 1108

Last

Page 608 of 1603

First

108 408 508 558 588 598 603 604 605 606 607 608 609 610 611 612 613 618 628 658 708 808 1108

Last

11-02-2014 , 12:24 PM

#15176

well named

poorly undertitled

Join Date: Jun 2007 Posts: 78,085

One possibility is to use a staging table. I haven't really used triggers in postgres so I have no idea if this is realistic, but essentially your trigger first loads rows into a staging table, say orders_staging. From there:

If you want to replace rows in the orders table:

Quote:

DELETE FROM orders USING orders_staging WHERE orders.order_id = orders_staging.order_id;
INSERT INTO orders SELECT * FROM orders_staging;
DELETE FROM orders_staging;

If you want to ignore existing rows

Quote:

DELETE FROM orders_staging USING orders WHERE orders_staging.order_id = orders.order_id;
INSERT INTO orders SELECT * FROM orders_staging;

If you want something like on duplicate key update:

Quote:

UPDATE orders SET col1=orders_staging.col1, ...
FROM orders_staging
WHERE orders.order_id = orders_staging.order_id;

DELETE FROM orders_staging USING orders WHERE orders_staging.order_id = orders.order_id;

INSERT INTO orders SELECT * FROM orders_staging;

Quote

150% up to $2,000 Welcome Bonus on CoinPoker

Join the action now

Daily Rewards ? Splash Pots ? CoinRaces

11-02-2014 , 02:27 PM

#15177

daveT

S.A.G.E. Master

Join Date: Jun 2005 Posts: 23,955

Quote:

Originally Posted by clowntable

Am I understanding your problem correctly? You basically want something like MySQL's "insert ignore" or basically you want to upsert? Maybe this is helpful?
http://www.the-art-of-web.com/sql/upsert/

Section 5 is the relevant part. As for the rest, it isn't relevant since I'm not updating any rows.

It doesn't solve the problem because it still requires a full table read, although NOT EXISTS is faster thatn NOT IN. I can't use NOT EXISTS in a trigger though.

Quote:

Originally Posted by gaming_mouse

can you give a specific example with a little sample data. i can't quite understand the problem.

I can't put a bunch of customer info online, lol.

Quote:

Originally Posted by well named

Yes, I am using a staging table:

Code:

create table ebords.json_insert
(
  jid serial primary key,
  v json
);

This is pretty straight-forward, I hope. A JSON tree is inserted into the v column. The jid simplly increments. I don't want to prevent duplicate JSONs. Once again, I want to allow re-imports.

The next step is decomposing the JSON tree, which willl be put into this table (there's a LOT of these tables, btw).

Code:

create table ebords.buyer_info
(
  jid int,
  order_id varchar primary key,
  address_owner varchar,
  name varchar,
  address_id varchar,
  external_address_id varchar,
  street1 varchar,
  street2 varchar,
  city varchar,
  country_name varchar,
  phone varchar,
  country varchar,
  postal_code varchar,
  state_province varchar,
  foreign key (jid) references ebords.json_insert (jid)
);

I should remove the jid column from here and place it into an intersection table of jid <-> order_id, but I didn't do that before. Right now, I'm trying to get the API data represented and will refine the design when I understand it more. The intersection table will allow me to log calls and help with debugging later on.

Now for the wonderful trigger syntax:

Code:

create trigger process_json_orders
after insert on ebords.json_insert
for each row
execute procedure ebords.process_orders();

Basically, triggers are for-loops. This is something I missed at first, which created a bunch of embarrassing issues. I'm not sure how this compares to triggers in other languages.

ebords.process_orders() is a function that returns a trigger. I'm removing the extra function calls here. There are a few other things going on, which is why there are nested transaction blocks. This is more for readability, though.

Code:

create or replace function ebords.process_orders ()
returns trigger as
$$
begin
	begin
	insert into ebords.buyer_info
	       (jid, order_id, address_owner, name, address_id, 
	       external_address_id, street1, street2, city, country_name, 
	       phone, country, postal_code, state_province)
	select r_jid, r_oid, r_address_owner, r_name, r_address_id, 
	       r_external_address_id, r_street1, r_street2, r_city,
	       r_country_name, r_phone, r_country, r_postal_code, 
	       r_state_province
	from ebords.insert_buyer_info(new.jid, new.v);
	end;
return new;
end;
$$ language plpgsql;

This function abstracts away some of NEW. syntax. If this was a single-table trigger, I would probably write it to look like this (though you will see that it is more complicated in a second):

Code:

create or replace function ebords.process_orders ()
returns trigger as
$$
begin
	begin
	insert into ebords.buyer_info
	       (jid, order_id, address_owner, name, address_id, 
	       external_address_id, street1, street2, city, country_name, 
	       phone, country, postal_code, state_province)
	values new.jid, new.oid, new.address_owner, new.name, new.address_id, ...)
	end;
return new;
end;
$$ language plpgsql;

Instead, I am calling the next function with the NEW. values. You can see that in the select clause in the actual function.

This trigger function then calls ebords.insert_buyer_info(int, json), which returns a table. Actually, this is returning a single row. There is a return row syntax, but it is deprecated and only used for legacy. It's also a PITA to use:

FWIW, I think the syntax for JSON is pretty nice. Very simple. The only thing you need to know is that -> returns a JSON object and the ->> returns the vale.

Code:

create or replace function ebords.insert_buyer_info (jid int, v json)
returns table (r_jid int, r_oid varchar, r_address_owner varchar, 
	      r_name varchar, r_address_id varchar, 
	      r_external_address_id varchar,
	      r_street1 varchar, r_street2 varchar, r_city varchar,
	      r_country_name varchar, r_phone varchar, r_country varchar,
	      r_postal_code varchar, r_state_province varchar)
as 
$$

declare
json_length int := json_array_length(v->'OrderArray'->'Order');
t_jid int := jid;
t_oid varchar;
t_address_owner varchar;
t_name varchar;
t_address_id varchar;
t_external_address_id varchar;
t_street1 varchar;
t_street2 varchar;
t_city varchar;
t_country_name varchar;
t_phone varchar;
t_country varchar;
t_postal_code varchar;
t_state_province varchar;

begin

for i in 0..json_length - 1
    loop
	select v->'OrderArray'->'Order'->i->'OrderID'->>'value'
	into t_oid;

	if t_oid not in (select order_id from ebords.buyer_info) then

		select v->'OrderArray'->'Order'->i->'ShippingAddress'->
			      'AddressOwner'->>'value'
		into t_address_owner;

		select v->'OrderArray'->'Order'->i->'ShippingAddress'
		       ->'Name'->>'value'
		into t_name;

	        select v->'OrderArray'->'Order'->i->'ShippingAddress'->
	               'AddressID'->>'value'
	        into t_address_id;

	        select v->'OrderArray'->'Order'->i->'ShippingAddress'->
	               'ExternalAddressID'->>'value'
                into t_external_address_id;

	        select v->'OrderArray'->'Order'->i->'ShippingAddress'->
	               'Street1'->>'value'
	        into t_street1;

	        select v->'OrderArray'->'Order'->i->'ShippingAddress'->
		       'Street2'->>'value'
                into t_street2;

	        select v->'OrderArray'->'Order'->i->'ShippingAddress'->
	               'CityName'->>'value'
                into t_city;

	        select v->'OrderArray'->'Order'->i->'ShippingAddress'->
	               'CountryName'->>'value'
                into t_country_name;

	        select v->'OrderArray'->'Order'->i->'ShippingAddress'->
		       'Phone'->>'value'
                into t_phone;

	        select v->'OrderArray'->'Order'->i->'ShippingAddress'->
		       'Country'->>'value'
                into t_country;

	        select v->'OrderArray'->'Order'->i->'ShippingAddress'->
		       'PostalCode'->>'value'
                into t_postal_code;

         	select v->'OrderArray'->'Order'->i->'ShippingAddress'->
	  	      'StateOrProvince'->>'value'
                into t_state_province;


        	return query select t_jid, t_oid, t_address_owner, t_name, 
	   	                    t_address_id, t_external_address_id,
				    t_street1, t_street2, t_city,
				    t_country_name, t_phone, t_country,
				    t_postal_code, t_state_province;

		end if;
end loop;

end;
$$ language plpgsql;

The JSON looks like this:

Code:

[
  {"pets":[{"name":"jack"},{"name":"john"},{"name":"joe"}]},
  {"pets":[{"name":"jack"},{"name":"john"},{"name":"joe"}]},
  {"pets":[{"name":"jack"},{"name":"john"},{"name":"joe"}]},
  {"pets":[{"name":"jack"},{"name":"john"},{"name":"joe"}]},
  {"pets":[{"name":"jack"},{"name":"john"},{"name":"joe"}]},
]

and that's why there is a LOOP construct with an index 0 to the len(array) - 1.

I'm looping in the function, and checking if the order_id is in the buyer_info table each and every time I run through the loop in the function. Not only can this one table get large, but if I have to do this for *all* 20+ tables, this trigger will never finish. While I could use NOT EXISTS in the trigger itself, I am still doing the exact same thing, albeit with a slightly faster syntax (I'm returning rows, not entire tables here).

The only solution I can think of at this point is to insert a JSON and diff it to another JSON, so that if it matches a JSON > 90% or something, I can extract the new stuff and insert it into a new row on the json_insert table. This would likely be faster, but I'm not sure, plus I can only think of how buggy this would be.

Last edited by daveT; 11-02-2014 at 02:33 PM.

Quote

11-02-2014 , 02:47 PM

#15178

well named

poorly undertitled

Join Date: Jun 2007 Posts: 78,085

The difference between a staging table that is essentially just a JSON wrapper and a staging table that has the same columns as your final destination table is that you can do the kind of joins between the master and staging table that allow you to merge the tables. I'm suggesting you add one more staging table. Hopefully the SQL makes that clear

edit: fwiw I have a similar enough use case on amazon redshift (based on postgres) where I use the kind of merging procedure I'm suggesting, so I didn't just make this up. What I'm doing involves CSV files on Amazon S3 rather than JSON but otherwise it's the same kind of problem. A periodic data import into the warehouse may include records already imported, including multiple subtables that have foreign keys to the master table. So I'm fairly confident that you can make what I'm suggesting work

See also http://docs.aws.amazon.com/redshift/...-examples.html

Just think of your JSON -> SQL TABLE step as the data creation step, rather than the JSON as being the "staging table"

Last edited by well named; 11-02-2014 at 02:54 PM.

Quote

11-02-2014 , 03:08 PM

#15179

daveT

S.A.G.E. Master

Join Date: Jun 2005 Posts: 23,955

That's an interesting solution, but I fear that it will fail spectacularly when the data set gets large unless I'm partitioning. Although it is minor difference, I'm only looking to insert new records, so would it be worth it to have all of that read / write overhead in my case?

Quote

11-02-2014 , 03:25 PM

#15180

well named

poorly undertitled

Join Date: Jun 2007 Posts: 78,085

My assumption is that in every possible case it will outperform a query that does a NOT IN (select id from subtable), once the number of IDs gets large. That said, I don't have enough experience with postgres to demonstrate that, it's just been my experience with trying to optimize SQL in general. Most of my work has been on MySQL and I've used that kind of query before and seen issues. Of course MySQL is not postgres.

Unfortunately redshift has enough of its own amazon-specific optimizations that I don't know whether the fact that it is recommended for that platform will generalize, but it does make sense to me that the kind of query (UPDATE ... FROM, INSERT SELECT) is easier for it to optimize than the IN (big list of IDs).

In my case, it's a scheduled data migration that happens frequently so the master tables are very large and the staging tables are always pretty small, importing a moving window's worth of data.

Also worth possibly thinking about is whether you can use the COPY command? I know that it is recommended for importing data into postgres. I'm imagining for large data sets its getting the raw data into the db at all that is the greatest overhead, rather than any queries you do once it's in there

Quote

11-02-2014 , 03:27 PM

#15181

well named

poorly undertitled

Join Date: Jun 2007 Posts: 78,085

one way to find out is to make a big fake data set and test it of course. Trial and error is my preferred mode of software development :P

Quote

11-02-2014 , 03:42 PM

#15182

daveT

S.A.G.E. Master

Join Date: Jun 2005 Posts: 23,955

Interesting food for thought. I'll have some EXPLAINS to run today.

I'm using Python to create the calling map then pushing the result directly into Postgres. The API puts an upper limit on how many items can be pulled on a single request, so if I want to pull a full month of data, they enforce pagination. Each single call is very fast (assuming there is no network failure), but a large sum of calls probably takes a while.

Yes, you are absolutely right that NOT IN does not optimize well in Postgres for whatever reason. I bet you are right on using staging tables as well. I won't know until I try it, obv.

Quote

11-02-2014 , 03:52 PM

#15183

gaming_mouse

Carpal \'Tunnel

Join Date: Oct 2004 Posts: 13,786

Quote:

I can't put a bunch of customer info online, lol.

dave, i wasn't suggesting that. what i'm asking for is standard practice on every coding forum and on stackoverflow it's practically a pre-requisite to your question not being closed in many cases.

i can't believe that you are making me spell this out, but: remove any sensitive data, or make up sample data that has the same structure as your real data.

Quote

11-02-2014 , 04:06 PM

#15184

daveT

S.A.G.E. Master

Join Date: Jun 2005 Posts: 23,955

Sorry to frustrate you, gaming_mouse, that wasn't my intention.

The JSON is very large and rather complex. Not only will altering a sample be very time-consuming, I'm bound to expose sensitive data.

Regardless, I'm not sure how a data sample will be much use here. I'm simply converting JSON into relational data. The question is trying to figure out where my approach is wrong because the hot-spots are visibly obvious without having to run tests. I brought the question here and not SO because I'd rather have a discussion about approaches and other soft ideas, which SO is very bad for.

Quote

11-02-2014 , 04:19 PM

#15185

gaming_mouse

Carpal \'Tunnel

Join Date: Oct 2004 Posts: 13,786

Quote:

Originally Posted by daveT

ok, np. i think you're misunderstanding the reason for my original question, which is i don't even understand what you are trying to do at a high level. that's needed even for a soft discussion. there was a lot of technology-specific jargon in your question. sample code, even when it is greatly simplified from the original problem, helps remove confusion. so does forcing yourself to describe the problem in a technology agnostic way.

that is, your original question takes for granted your approach of using triggers and doing everything in the database. but maybe that should be questioned? i don't know because i don't understand fundamentally what you're trying to accomplish, though from your subsequent posts i now get the gist of it. i was trying to get you to rephrase in a way that would possibly get your better answers.

Quote

11-02-2014 , 05:05 PM

#15186

daveT

S.A.G.E. Master

Join Date: Jun 2005 Posts: 23,955

I know that this isn't super precise and not entirely accurate.

Results after running DISCARD PLAN:

Code:

create or replace function trash_one ()
returns int
as
$$
begin

create temp table bi as 
select * from ebords.buyer_info
limit 1;

delete from bi
using ebords.buyer_info ebi
where bi.order_id = ebi.order_id;

insert into bi
select * from ebords.buyer_info;

drop table bi;

return null;
end;
$$ language plpgsql;

explain analyze select trash_one();

result:

Code:

"Result  (cost=0.00..0.26 rows=1 width=0) (actual time=19.203..19.223 rows=1 loops=1)"
"Total runtime: 19.362 ms"

********

Code:

create temp table bi as 
select * from ebords.buyer_info
limit 1;

create or replace function trash_two ()
returns int
as
$$
begin

insert into bi
select *
from ebords.buyer_info ebi
where ebi.order_id not in
	(select order_id
	from bi);

return null;
end;
$$ language plpgsql;

explain analyze select trash_two()

Code:

"Result  (cost=0.00..0.26 rows=1 width=0) (actual time=1.520..1.527 rows=1 loops=1)"
"Total runtime: 1.588 ms"

Cashed results are significantly different in the opposite direction, where the staging table is about 4x faster. Interesting.

I'm think that removing the NOT IN from inside of the for-loop and replacing it with a staging table strategy may be better, but I'm not entire sure yet. I'd probably have to build the staging table and process in the trigger function.

Quote

11-02-2014 , 05:10 PM

#15187

daveT

S.A.G.E. Master

Join Date: Jun 2005 Posts: 23,955

Quote:

Originally Posted by gaming_mouse

that is, your original question takes for granted your approach of using triggers and doing everything in the database. but maybe that should be questioned? i don't know because i don't understand fundamentally what you're trying to accomplish, though from your subsequent posts i now get the gist of it. i was trying to get you to rephrase in a way that would possibly get your better answers.

I understood what you were doing.

I'm not entirely convinced that using a trigger is the best way, but this is far easier than doing JSON processing in Python before inserting into the database. Balance the hands, I guess?

While there is an argument for calling subsequent SQL functions with code, I'd rather kill the entire transaction instead of having partial data.

Quote

11-02-2014 , 05:14 PM

#15188

well named

poorly undertitled

Join Date: Jun 2007 Posts: 78,085

You should be able to commit or rollback a transaction from code also.

Quote

11-02-2014 , 05:23 PM

#15189

daveT

S.A.G.E. Master

Join Date: Jun 2005 Posts: 23,955

Quote:

Originally Posted by well named

You should be able to commit or rollback a transaction from code also.

Psycopg2 automatically executes rollback() without specifying commit(). I think that if anything fails, it will rollback automatically since it would presumably never reach the commit() function. I'm not entirely sure which circumstances causes the entire program to crash though.

Quote

11-02-2014 , 06:01 PM

#15190

well named

poorly undertitled

Join Date: Jun 2007 Posts: 78,085

I read the docs a bit and that seems somewhat odd, although the auto-rollback doesn't seem like it would make it impossible to handle transactions in code also, assuming along with the auto-rollback you can catch the error in code and proceed appropriately. You just don't have to call rollback() manually, where you do have to call commit() manually. And you have to make sure you use a new connection or commit() any prior queries so your transaction starts in the right place.

It also looks like you can use execute() to manually run the BEGIN, COMMIT, and ROLLBACK sql commands themselves.

Quote

11-02-2014 , 07:33 PM

#15191

gaming_mouse

Carpal \'Tunnel

Join Date: Oct 2004 Posts: 13,786

Quote:

Originally Posted by daveT

I understood what you were doing.

i have a bias against triggers. i find PL/SQL hard to read and maintain. i imagine there's not much ecosystem for writing unit tests for PL/SQL triggers either, but there may be -- i've never looked into it.

anyway i'd imagine JSON processing is incredibly simple in python -- i know it is in ruby. it's a single include and then JSON.parse gets you a native object. again, without knowing more, no way to say if this would be the right approach with your problem. personally i like to keep business logic out of the database as much as possible.

Quote

11-02-2014 , 07:55 PM

#15192

daveT

S.A.G.E. Master

Join Date: Jun 2005 Posts: 23,955

Quote:

Originally Posted by well named

The docs are terrible. Either outdated or plain wrong in many places. Functions that don't even exist are used all throughout to documentation. Thank goodness for dir().

Using Python to instantiate the next SQL function brings up a few interesting issues, some good and some bad. The big one is timing. I'd hate to call a function before another function is ready. I suppose after a function returns some value, then the next function is ready to roll. On the other hand, quite few functions could run concurrently. Python 2 doesn't concurrency baked in, and it may be a tad over the top to do that anyways. I'm not sure if there is a compelling reason to execute functions from Python instead of Postgres if those functions are strictly in PL, outside of having a little bit more execution control. I'd rather bail entirely and restart the whole process on error because that indicates an issue in the JSON data itself. There's no need to keep the mal-formed data and continuing execution wouldn't resolve anything.

As far as I know, I can run anything in the connection strings, though they can get a bit tricky to work with. Getting the JSON to feed into the database was nearly impossible. I got it to work with a lucky guess and yeah, the docs were way off on this one.

Hell if I know. The more I think about it, the more I get myself confused.

The good news is that this is already the largest program I ever wrote, yet it is just the start and it doesn't do a damn thing.

Quote

11-02-2014 , 07:57 PM

#15193

daveT

S.A.G.E. Master

Join Date: Jun 2005 Posts: 23,955

Quote:

Originally Posted by gaming_mouse

JSON in Python is awful.

Code:

try: 
## if the end-point is here, great else:
except: pass

don't even get me started on encoding.

Yes, in general, I agree that triggers suck. These are simple triggers I'm writing though. The functions don't have to be triggers, so they could be called with Python if I decide to do so.

Quote

11-02-2014 , 08:37 PM

#15194

gaming_mouse

Carpal \'Tunnel

Join Date: Oct 2004 Posts: 13,786

Quote:

Originally Posted by daveT

JSON in Python is awful.

looks like a 2-liner:
http://stackoverflow.com/questions/2...file-in-python

what about it is awful?

Quote

11-02-2014 , 09:02 PM

#15195

jjshabado

Carpal Tunnel

Join Date: Jul 2006 Posts: 22,732

I've never had a problem with Json in Python and we use it all the time.

I'd be surprised if it wasn't simple to call the api you have and get back the Python version of the Json without having to worry about the parsing at all.

Edit: like this maybe: https://github.com/timotheus/ebaysdk...ter/README.rst

Last edited by jjshabado; 11-02-2014 at 09:07 PM.

Quote

11-02-2014 , 09:04 PM

#15196

daveT

S.A.G.E. Master

Join Date: Jun 2005 Posts: 23,955

Quote:

Originally Posted by gaming_mouse

looks like a 2-liner:
http://stackoverflow.com/questions/2...file-in-python

what about it is awful?

The get_iterator() is a JSON wrapper from TwitterAPI, but the rule stands for all JSON that I've worked with in Python:

Code:

for item in r.get_iterator ():
    print (item)

output?

Code:

    return codecs.charmap_encode(input,self.errors,encoding_table)[0]
 UnicodeEncodeError: 'charmap' codec can't encode characters in position 1273-1274: character maps to <undefined>

So, how do we prevent this?

Code:

for item in r.get_iterator():
    try: 
        print(item)
    except: 
        pass

Quote

11-02-2014 , 09:10 PM

#15197

jjshabado

Carpal Tunnel

Join Date: Jul 2006 Posts: 22,732

What's get_iterator?

The built in Json functionality just converts the Json object to a dictionary and things 'just work'.

Quote

11-02-2014 , 09:54 PM

#15198

daveT

S.A.G.E. Master

Join Date: Jun 2005 Posts: 23,955

get_iterator is a generator. I've used other JSON APIs and ran into the same issues, even after converting to dict. Python2 also had quite a few encoding issues, if I recall.

Regardless, I'm not particularly married to any one idea. I still want to store the raw JSON and then convert it over. I'll probably just call the PL functions with Python and think about the optimization issues after I get these tables built and organized a bit.

Quote

11-03-2014 , 06:32 AM

#15199

jjshabado

Carpal Tunnel

Join Date: Jul 2006 Posts: 22,732

I'm still pretty confused why you need anything more than Json.loads. Like I said we use Json a lot for our own web services, our mongo database, calling 3rd party APIs, etc. I've never had encoding or other issues with it.

Anyway, I didn't go deep into your particular problem but just wanted to make clear that Json in Python has always been trivial for me and shouldn't be hard at all.

Quote

11-03-2014 , 11:44 PM

#15200

Anais

Carpal \'Tunnel

Join Date: Mar 2006 Posts: 6,663

There's pretty much never a situation in this day and age where one has to specifically keep track of memory addresses, right? I mean, aside from writing some low level code or whatever.

Covering pointers we discussed how, say, a double pointer array starting at memory location 1000 will point to 1008 if you do array+1. That's just background info to know and nothing the vast majority of programmers ever need, eh?