Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > alt.comp.databases.postgresql > #3

Re: Postgresql Trigger & function

Show all headers | View raw

Gerald Brown <gerbreown@gmail.com> writes:
> I am trying to create an application where I have 1 table called "person".
> This table has a field called "city" that is a foreignkey to the "city"
> table. The "city" table has a field "counter" as integer.

Ok:

create table city(id serial primary key, name text);

  create table person
   (
    id   serial primary key,
    name text,
    city integer references city(id)
   );
  
  insert into city(id, name)
       values (1, 'paris'),
              (2, 'madrid'),
              (3, 'istanbul'),
              (4, 'tokyo'),
              (5, 'nairobi');
  
  insert into person(name, city)
       values ('paul', 1), ('leon', 1), ('mohamed', 1),
              ('pedro', 2), ('juan', 2),
              ('alp', 3), ('mazhar', 3),
              ('akako', 4);

> What I want to do is create a function that will update the "counter" for
> the "city" that is selected. Also in my Django model I have an ordering =
> ('-counter', 'name') on the table "city". This will show the list of cities
> ordered by the one that is most selected.

    select city.name, count(person.id)
      from city left join person on person.city = city.id
  group by city.name
  order by count(person.id) desc;
  
     name   │ count 
  ══════════╪═══════
   paris    │     3
   madrid   │     2
   istanbul │     2
   tokyo    │     1
   nairobi  │     0
  (5 rows)

Why do you want to store the result of this query on-disk? How will you
handle cache invalidation and concurrency?

With a trigger, all insert, updates and delete against the same city
happening at once will have the effect of doing an update on the same
row for the counter, thus serializing your activity. That's very bad.

Regards,
-- 
Dimitri Fontaine
PostgreSQL DBA, Architecte

Back to alt.comp.databases.postgresql | Previous — Previous in thread | Find similar

Thread

Postgresql Trigger & function Gerald Brown <gerbreown@gmail.com> - 2017-06-02 03:55 -0700
  Re: Postgresql Trigger & function Dimitri Fontaine <dimitri.fontaine@schibsted.com> - 2017-06-02 21:47 +0200

csiph-web