drop table if exists company, product;
CREATE TABLE company ( id integer primary key, name text, num_products integer );
CREATE TABLE product ( id serial primary key, company_id integer not null references company);

CREATE or replace FUNCTION update_num_products() RETURNS trigger
LANGUAGE plpythonu
AS $$

new = TD["new"]

def lock_company(company_id):
	plpy.execute("""
		SELECT * FROM company
		WHERE id = %(company_id)s
		FOR NO KEY UPDATE
	""" % {'company_id': company_id})

def update_company(company_id, delta):
	plpy.execute("""
		UPDATE company
		SET num_products = coalesce(num_products, 0) + %(delta)s
		WHERE id = %(company_id)s
	""" % {'company_id': company_id, 'delta': delta})

with plpy.subtransaction():
	# lock_company(new['company_id'])
	update_company(new['company_id'], 1)
$$;

CREATE TRIGGER update_num_products AFTER INSERT OR UPDATE ON product FOR
EACH ROW EXECUTE PROCEDURE update_num_products();

INSERT into company VALUES (0,1);