Геолокация без GPS (часть 2)

wifi Здравствуйте! После предыдущей статьи (часть 1) я получил много отзывов о работе открытого API геолокации по WiFi и вышкам мобильных сетей. В этот раз я хочу представить свои исправления, лицензирование проекта, геопозиционирование по нескольким точкам доступа, кейсы использования и самое главное — текущие проблемы Сначала об исправленных ошибках:

  1. Исправлена грамматическая ошибка в ответе сервера (возвращался «lan» в место «lat») ключ «v=1.1»
  2. Добавлено лицензирование (об этом в конце)

Мобильные сети

Открытые данные
Основной критикой в сторону моего API был вопрос: «Чем плох OpenCellID?» ответ: ничем, кроме того что в этой базе мало данных. На данный момент у них 7,8 млн вышек. Это хороший результат для полностью открытой базы, но главный минус — это плохая точность. Сильно помогла база Mozilla Location Service для увеличения карты покрытия открытых данных. Из всех открытых баз я выкинул недостоверные данные, такие как радиус действия около 1200 км, либо же радиус действия 0. Т.е. вышка была «замечена» всего единожды, а следовательно, положение по ней можно оценить только качественно и по-хорошему точность 35 км как у средней соты (максимальная дальность средней соты за городом). Открытые данные обновляются автоматически раз в неделю. Т.е. база всегда актуальна. Сейчас в базе 13 млн. записей. Это отличный результат покрытия по всему миру. Надеюсь, вопросов, чем это лучше OpenCellID не будет. Важно отметить что в API нет никаких ограничений за количество запросов. Пример: http://api.mylnikov.org/mobile/main.py/get?v=1.1&mcc=250&mnc=02&cellid=200719106&lac=7840&data=open Сколько данных: 13.8M+ открытых данных (уникальных вышек)
Данные «как есть»
Здесь данные, на которые не распространяются никакие лицензии. Источников много, данные получаются достаточно достоверные. Скачать данные напрямую нельзя, но возможно получить в любом количестве через API. Система работы проста. Как только система получает запрос, на который не может ответить из текущей базы, производятся запросы к источникам, которые потенциально могут знать о регионе запроса. Важно заметить что «полученные» данные проходят полный цикл обновления за месяц. Пример: http://api.mylnikov.org/mobile/main.py/get?v=1.1&mcc=250&mnc=02&cellid=200719106&lac=7840 Развернутое описание работы с API геолокации по мобильным сетям
Уточненое местоположение
В документации описана функция геолокации по нескольким мобильным вышкам. Большинство открытых сервисов делать это не умеют и функция в диапазоне GSM-EDGE является крайне полезной, однако ответ API скорее носит качественную оценку геоположения. Я буду рад, если кто-нибудь напишет мне (alex@mylnikov.org) и поможет решить задачу «триангуляции» по нескольким мобильным вышкам. Максимальное количество точек доступа: 12 Пример: https://api.mylnikov.org/mobile/main.py/get?v=1.1&search=MjUwLDAyLDc4… Потенциально, при работе некоторых телефонных аппаратов возможно получать данные о вышках нескольких телефонных операторов (либо 2 симкарты, либо супер карты телефонов, которые показывают все видимые вышки, примеров я не знаю).
Скачать данные

WiFi точки доступа

Открытые данные
Главный вклад в открытые данные привнес великолепный проект OpenWLANMap данные из данного проекта обновляются раз в неделю. Используются еще несколько источников, о них описано в подробной документации. Пример: https://api.mylnikov.org/wifi/main.py/get?v=1.1&bssid=00:0C:42:1F:65:E9&data=open
Данные «как есть»
Источников мало. Скачать данные напрямую нельзя, но возможно получить в любом количестве через API. Система работы идентична работе API для мобильных сетей. Период обновления 1 месяц Пример: https://api.mylnikov.org/wifi/main.py/get?v=1.1&bssid=A0:F3:C1:3B:6F:90 Развернутое описание работы с API геолокации по WiFi точкам
Уточненое местоположение
В документации описана функция геолокации по нескольким мобильным вышкам. Для точек доступа WiFi функция крайне полезная. Хотя и дает качественное положение, однако обычно сетевой адаптер мобильного устройства чаще всего получает лист всех точек доступа даже при отсутствии подключения к какой либо точке доступа. С помощью этих данных за один запрос можно получить координату с точностью до 150 метров. Максимальное количество точек доступа: 20 Пояснение: Разумеется, данные в базе есть не о всех точках вашей квартиры, однако, те, которые будут найдены будут использованы для уточнения геопозиции. Пример: https://api.mylnikov.org/wifi/main.py/get?v=1.1&search=Mjg6Mjg6.. Скачать данные

Актуальные проблемы

  • Совершенно не ясно как выбирать данные при условии наличия более одного источника. О некоторых точках доступа известно нескольким сервисам, причем неизвестно у кого из них данные более свежие и точные.
  • Не известно где искать дополнительные источники данных. Буду благодарен любому источнику.
  • Нагрузка после первой статьи сильно не увеличилась. Возникает ощущение что сервис не слишком интересен, возможно это из-за недоверия или молодости проекта.
  • «Уточненная» геолокация работает мягко говоря плохо.

Кейсы использования
С моей точки зрения API может быть полезно различным приложениям от геолокационых приложений до скрытого таргетинга контента приложений
Лицензирование
Лицензирование у проекта несколько хитрое. Через одно API (для мобильных вышек и WiFi точек доступа соответственно) доступно два проекта.

  1. ключ data=open. Данный ключ дает гарантию, что данные из ответа были получены исключительно из открытых источников. Данные распространяются под лицензией MIT
  2. отсутствие ключа data=open не дает никаких гарантий об источниках данных, а также данная часть проекта не лицензируется вовсе.

Доступность
Работоспособность сервиса «почти» гарантирован в течении 3х лет, точнее до конца 2018 года при наличии высого количества запросов со стороны пользователей и 1 года при отсутствии нагрузки. Фактически работает два сервера в назависимых дата-центрах. Средний показатель uptime за последний квартал 99,923% (по данным Яндекс.Метрики). Обратная совместимость каждой версии API гарантирована через ключ «v=» бессрочно.
Пояснение
Ни при каких обстоятельствах я не претендую на владение данными этого проекта. Я лишь хочу, чтобы в интернете был достоверный обширный и доступный источник геолокационных данных. Все данные принадлежат правообладателям. P.S. Я доступен в любое время дня и ночи по электронной почте alex@mylnikov.org и готов выслушать любую критику, пожелания и предложения.

Установка VMware Tools на Debian

vmwareМануалов много, решил просто скопировать себе на сайт.
Начинаетс все просто:

1. Входим в графическую консоль виртуальной машины и пробрасываем установочный диск VMware Tools в систему

Скрины

Для  гипервизора (ESXi)

vmware-tools-02

Для VMware Workstation

vmware-tools-01
2. Логинимся под пользователем, который может запускать sudo

Обновляем систему (опционально)
sudo apt-get update && sudo apt-get dist-upgrade
sudo shutdown -r now

Устанавливаем нужный софт для тула
sudo apt-get update && sudo apt-get install build-essential linux-headers-$(uname -r) libglib2.0-0

Запускаем установку
sudo mount /dev/cdrom /mnt
tar xvfz /mnt/VMwareTools-*.tar.gz -C /tmp/
sudo perl /tmp/vmware-tools-distrib/vmware-install.pl

Перезапускаем
sudo shutdown -r now

Поздравляю, VMware Tools установлен.

Геолокация без GPS

wifiЗдравствуйте!

Представляю публичную базу геопозиций телефонных вышек и Wi-Fi роутеров. Мне понадобилась стабильная и безлимитная база для приложения Android в котором нужна была точная геолокация из всех доступных источников (GPS, Wi-Fi, Mobile). Пришлось создать базу данных положений сотовых вышек и Wi-Fi. Всех заинтересовавшихся прошу под кат.

Читать далее Геолокация без GPS

Определение местоположения по Wi-Fi (MAC, bssid) открытое API

Wi-FiСегодня я хочу представить публичный API для определения местоположения по данным точки доступа Wi-Fi. В итернете достаточно много ресурсов предлагающих определение местоположения по данным Wi-Fi, однако все они либо платные, либо содержат ограничение на количество запросов, либо очень маленькие.

Данная база может быть интересна любым приложениям которые имеют географическую привязку с помощью Wi-Fi.

В своей базе я собрал все доступные публичные источники:

В данный момент база  содержит 10М Wi-Fi и постоянно наполняется новыми. Если у вас обширная база Wi-Fi  или источники буду рад ее добавить в свою базу. Пишите на alex@mylnikov.org.

API не содержит никаких скрытых лимитов или задержек. Данные предоставляются «как есть».

Описание публичного API (Открытые источники)

Адреса обращения

https://api.mylnikov.org/wifi/main.py/get?bssid={wifi bssid}

https://api.mylnikov.org/wifi/main.py/get?bssid={wifi bssid}

{wifi bssid} — Bssid точки доступа (MAC адрес сетевой карты точки доступа)

Варианты:

  • A0:F3:C1:3B:6F:90
  • A0F3C13B6F90
  • a0f3c13b6f90
  • A0-F3-C1-3B-6F-90
  • a0-f3-c1-3b-6f-90

Пример:

https://api.mylnikov.org/wifi/main.py/get?bssid=00:0C:42:1F:65:E9&data=open&v=1.1

Варианты ответа:

{«result»:200, «data»:{«range»: 140, «lan»: «60.05205150», «lon»: «30.33848000», «signal»: «-80»}}
Поле «result» содержит значение 200 если Wi-Fi найден, в противном случае возвращается 404

Описание успешного ответа:

  • lat — широта
  • lon — долгота
  • signal — средний уровень сигнала
  • range — точность определения координаты в метрах

Реализация API (Данные «как есть»)

https://api.mylnikov.org/wifi/main.py/get?bssid=A0:F3:C1:3B:6F:90&v=1.1

При данном виде запроса данные представляются только в иследовательских целях. Конечный пользователь использует данные на свой страх и риск

Уточненное местоположение

Вводится новая функция получения приблизительного пожложения точки триангуляционного центра. Синтаксис следующий: на сервер передается набор точек доступа, видимых приемником и силу сигналов точек, а сервер по имеющимся данным получает приблизительное положение точки, качественно являющейся трианглуляционным центром. все данныые запроса оборачиваются в base64

Пример исходной строки:

28:28:5d:d6:39:8a,-76;90:94:e4:ac:12:26,-80

bssid,SS;bssid1,SS1

SS — Signal Strength

https://api.mylnikov.org/wifi/main.py/get?v=1.1&search=Mjg6Mjg6NWQ6ZDY6Mzk6OGEsLTc2OzkwOjk0OmU0OmFjOjEyOjI2LC04MA==

В данном моде так же работает тег data=open который сообщает серверу что данные должны быть получены исключительно из открытых источников. К сожалению, в этом случае часты ситуации с отрицательным результатом поиска.

Лицензирование

Через одно API доступно, на самом деле, два проекта.

Первый и лицензируемый проект это все запросы к API с ключем «data=open» и он работает под лицензией  MIT License.

Второй проект получает данные «как есть». Полностью законное получение данных не оговаривается. И обращение к API без ключа open=data в принципе не документируется и предоставляется как «пасхальное яйцо». Данные предоставляемые используются конечным пользователем на свой страх и риск.

О любых ошибках или сложностях использования прошу писать мне на почту alex@mylnikov.org.

Скачать данные

Раздел загрузок

Приложение для тестирования:
Get it on Google Play

Приложение для тестирования (Верисия для разработчиков):
Get it on Google Play

Публичная база телефонных станций мира. Публичное открытое API

cellСегодня я хочу представить всем желающим базу телефонных станций всего мира.

Это может быть полезно приложениям или сервисам, которые по идентификаторам мобильной станции хотят получить ее геопозицию и тип. Данная база частично является копией The OpenCellID map, однако в официальной реализации есть ограничение на количество запросов и обязательно выпускать API ключ. В базе так же собраны и другие источники данных.

Список открытых источников:

В моей реализации нет не лимитов, ни ключей.

Данные обновляются еженедельно.

Реализация API (Открытые источники)

http://api.mylnikov.org/mobile/main.py/get адрес запросов передаются методом GET

Так же доступен по протоколу https://api.mylnikov.org/mobile/main.py/get

Для запроса есть 4 обязательных поля:

  1. mcc — Integer (Код страны)
  2. mnc — Integer (Код телефонного оператора)
  3. cellid — Integer (Код телефонной станции)
  4. lac — Integer (Код региона или Area, Location)
  5. v — Номер версии (Актуацльная версия 1.1)
  6. data=open  — указание, что данные полученные из API строго открытые

Пример:

http://api.mylnikov.org/mobile/main.py/get?data=open&mcc=250&mnc=02&cellid=200719106&lac=7840&v=1.1

Ответ:

Строка JSON имеющее обязательное поле result.

Если станция найдена в поле result приходит ответ 200, при ошибках возвращается код 404.

{ 
"result":200,
"data":{ 
"lon":"30.385654398",
"mcc":250,
"lac": 7840,
"range":"869.994176272",
"radio":"UMTS",
"samples":49,
"time":1430577060,
"lat":"60.056594206",
"mnc":2,
"cellid":200719106
}
}
{ 
"result":404,
"data":{ 
},
"real":6,
"desc":"No information about location",
"time":1430579063
}

Описание успешного ответа:

  • lat — широта
  • lon — долгота
  • range — точность определения координаты в метрах
  • samples — количество измерений
  • mcc — код страны
  • mnc — код мобильного оператора
  • lac — код мобильного региона
  • cellid — Id базовой станции
  • radio — тим базовой стании

Реализация API (Данные «как есть»)

Для запроса есть 4 обязательных поля:

  1. mcc — Integer (Код страны)
  2. mnc — Integer (Код телефонного оператора)
  3. cellid — Integer (Код телефонной станции)
  4. lac — Integer (Код региона или Area, Location)
  5. v — Номер версии (Актуацльная версия 1.1)

Пример:

http://api.mylnikov.org/mobile/main.py/get?mcc=250&mnc=02&cellid=200719106&lac=7840&v=1.1

Уточненное местоположение

Вводится новая функция получения приблизительного пожложения точки триангуляционного центра. Синтаксис следующий: на сервер передается набор базовых станций, видимых приемником и силу сигналов станций, а сервер по имеющимся данным получает приблизительное положение точки, качественно являющейся трианглуляционным центром. все данныые запроса оборачиваются base64

Пример исходной строки:

250,02,7810,318,-81;250,02,4711,24208,-83;250,02,4711,24214,-89;250,02,4711,619,-95;250,02,4711,16627,-95

mcc,mnc,lac,cellid,SS;mcc1,mnc1,lac1,cellid1,SS1

SS — Signal Strength

https://api.mylnikov.org/mobile/main.py/get?v=1.1&search=MjUwLDAyLDc4MTAsMzE4LC04MTsyNTAsMDIsNDcxMSwyNDIwOCwtODM7MjUwLDAyLDQ3MTEsMjQyMTQsLTg5OzI1MCwwMiw0NzExLDYxOSwtOTU7MjUwLDAyLDQ3MTEsMTY2MjcsLTk1

В данном моде так же работает тег data=open который сообщает серверу что данные должны быть получены исключительно из открытых источников. К сожалению, в этом случае часты ситуации с отрицательным результатом.

Важно заметить, что API позволяет геолоцироваться по разным операторам одновременно, те если абонент слышить много сетей вокруг, то его местоположение пожно определить достаточно достоверно.

Лицензирование

Через одно API доступно, на самом деле, два проекта.

Первый и лицензируемый проект это все запросы к API с ключем «data=open» и он работает под лицензией  MIT License.

Второй проект получает данные «как есть». Полностью законное получение данных не оговаривается. И обращение к API без ключа open=data в принципе не документируется и предоставляется как «пасхальное яйцо». Данные предоставляемые используются конечным пользователем на свой страх и риск.

Скачать данные

Раздел загрузок

Приложение для тестирования:
Get it on Google Play

Приложение для тестирования (Верисия для разработчиков):
Get it on Google Play

CSC Бызы данных, 9 дз

Вам нужно посчитать некоторую статистику о багах в багтрекере. Статистику вы хотите видеть в представлениях, соответственно основной задачей является написание запросов, формирующих представления.

Во-первых, вам хочется для каждого проекта найти суммарное количество багов каждого из имеющихся у вас статусов. Хочется видеть примерно такое:

SELECT * FROM BusSummaryPerProject;

project_id  | new  | assigned | fixed | verified | not reproducible
1001          45     24         3       0          10
1002          56     10         37      104        15

Для упрощения можете считать, что множество статусов известно и не собирается меняться.

Во-вторых, для каждого статуса хочется видеть компоненту с максимальным, минимальным и медианным количеством багов с таким статусом. Например, если у нас 7 компонент и количество багов со статусом “new” в них распределено так:

C1 | 10
C2 | 50
C3 | 15
C4 | 30
C5 | 20
C6 | 25
C7 | 35

то в строке представления для статуса new должно быть:

SELECT * FROM StatisticsPerStatus WHERE status='new';

status | max_component | max_value | min_component | min_value | median_component | median_value
new      C2              50          C1              10          C6                 25

Если число компонент четное, то считайте медианой большее из двух возможных значений. Если медианное значение принадлежит нескольким компонентам, то добавьте лексикографическую сортировку по названию компоненты (так, если бы в примере выше в компоненте C4 было бы 25 багов, то медианой стала бы она, если бы в компоненте C5 было 25 багов, то медианой осталась бы C6, а если бы и в C4 и в C5 было бы 25 багов, то медианой стала бы C5)

Собсвтенно решение:

Пунт 1

Для вывода информаци по кадому проекту я построил впомогательно представление которое получает отношение «проект  — статус  — количество»

-- View: statustoproject

-- DROP VIEW statustoproject;

CREATE OR REPLACE VIEW statustoproject AS 
 SELECT count(bug.status_id) AS count, bug.status_id AS status, component.project_id AS project
   FROM bug
   JOIN bugcomponent ON bug.num = bugcomponent.bug_num
   JOIN component ON bugcomponent.component_id = component.id
  GROUP BY component.project_id, bug.status_id
  ORDER BY component.project_id, bug.status_id;

ALTER TABLE statustoproject
  OWNER TO postgres;

Далее, собственно, выбираю из вспомогательной выбоки и строю нужное отношение.

-- View: bussummaryperproject

-- DROP VIEW bussummaryperproject;

CREATE OR REPLACE VIEW bussummaryperproject AS 
 SELECT project.id AS project_id, COALESCE(( SELECT statustoproject.count
           FROM statustoproject
          WHERE statustoproject.project = project.id AND statustoproject.status = 1), 0::bigint) AS new, COALESCE(( SELECT statustoproject.count
           FROM statustoproject
          WHERE statustoproject.project = project.id AND statustoproject.status = 2), 0::bigint) AS assigned, COALESCE(( SELECT statustoproject.count
           FROM statustoproject
          WHERE statustoproject.project = project.id AND statustoproject.status = 3), 0::bigint) AS fixed, COALESCE(( SELECT statustoproject.count
           FROM statustoproject
          WHERE statustoproject.project = project.id AND statustoproject.status = 4), 0::bigint) AS verified, COALESCE(( SELECT statustoproject.count
           FROM statustoproject
          WHERE statustoproject.project = project.id AND statustoproject.status = 5), 0::bigint) AS "not reproducible"
   FROM project;

ALTER TABLE bussummaryperproject
  OWNER TO postgres;

Пункт 2

Самым сложным для меня было создать функцию медианы

CREATE OR REPLACE FUNCTION array_median(numeric[])
  RETURNS numeric AS
$$
    SELECT CASE WHEN array_upper($1,1) = 0 THEN null ELSE asorted[ceiling(array_upper(asorted,1)/2.0)] END
    FROM (SELECT ARRAY(SELECT ($1)[n] FROM
generate_series(1, array_upper($1, 1)) AS n
    WHERE ($1)[n] IS NOT NULL
            ORDER BY ($1)[n]
) As asorted) As foo ;
$$
  LANGUAGE 'sql' IMMUTABLE;
CREATE  AGGREGATE median(numeric) (
                  SFUNC=array_append,
                  STYPE=numeric[],
                  FINALFUNC=array_median
                );

Далее как и в первом примере я строю вспомогательное представление.

-- View: statustocomponent

-- DROP VIEW statustocomponent;

CREATE OR REPLACE VIEW statustocomponent AS 
 SELECT DISTINCT count(bug.status_id) AS count, bug.status_id AS status, component.id, component.title AS componentname
   FROM bug
   JOIN bugcomponent ON bug.num = bugcomponent.bug_num
   JOIN component ON bugcomponent.component_id = component.id
  GROUP BY component.id, bug.status_id
  ORDER BY component.id, bug.status_id;

ALTER TABLE statustocomponent
  OWNER TO postgres;

Далее строю нужно отношение.

-- View: statisticsperstatus

-- DROP VIEW statisticsperstatus;

CREATE OR REPLACE VIEW statisticsperstatus AS 
 SELECT bugstatus.value AS status, COALESCE(( SELECT statustocomponent.componentname
           FROM statustocomponent
          WHERE statustocomponent.status = bugstatus.id
          ORDER BY statustocomponent.count DESC, statustocomponent.componentname
         LIMIT 1), 'null'::character varying) AS max_component, COALESCE(( SELECT max(statustocomponent.count) AS max
           FROM statustocomponent
          WHERE statustocomponent.status = bugstatus.id), 0::bigint) AS max_value, COALESCE(( SELECT statustocomponent.componentname
           FROM statustocomponent
          WHERE statustocomponent.status = bugstatus.id
          ORDER BY statustocomponent.count, statustocomponent.componentname
         LIMIT 1), 'null'::character varying) AS min_component, COALESCE(( SELECT min(statustocomponent.count) AS min
           FROM statustocomponent
          WHERE statustocomponent.status = bugstatus.id), 0::bigint) AS min_value, COALESCE(( SELECT statustocomponent.componentname
           FROM statustocomponent
          WHERE statustocomponent.status = bugstatus.id AND statustocomponent.count::numeric = (( SELECT median(statustocomponent.count::numeric) AS median
                   FROM statustocomponent
                  WHERE statustocomponent.status = bugstatus.id))
          ORDER BY statustocomponent.componentname
         LIMIT 1), 'null'::character varying) AS median_component, COALESCE(( SELECT median(statustocomponent.count::numeric) AS median
           FROM statustocomponent
          WHERE statustocomponent.status = bugstatus.id), 0::numeric) AS median_value
   FROM bugstatus;

ALTER TABLE statisticsperstatus
  OWNER TO postgres;

Работет все это достаточно шутсро, поэтому решиние претендует быть верным.
Вомозможно я неверно лексиграфически упрорячиваю названия компонент

Дамп базы dbhw9.sql

CSC Базы данных, 8 дз

У нас в багтрекере появилось новое требование: мы хотим хранить историю изменений статуса бага. Мы хотим иметь возможность видеть отчет типа

Баг №1: Improve overall performance дата | статус 01.09.2014 new 02.09.2014 assigned 25.09.2014 fixed 26.09.2014 assigned 06.10.2014 fixed 07.10.2014 verified

Мы не планируем выполнять такие отчеты часто, нам все же в основном будет интересен актуальный статус всех багов. Поэтому идея вносить дополнительный атрибут “дата” в таблицу Bug, или же наоборот, убирать атрибут status_id из таблицы Bug кажется не очень хорошей (но вы можете попробовать это сделать и оценить, насколько она в действительности плоха. Оценить можно написав, например, запрос, выдающий актуальные статусы всех багов)

с точки зрения клиента, в идеале, процедура изменения статуса не должна измениться вообще, и должно быть достаточно выполнить запрос вида

UPDATE Bug SET status_id = 2 WHERE num=1

Поиск актуальных статусов, в идеале, тоже должен остаться прежним:

SELECT * FROM Bug

А историю клиенту хочется получать запросом вида

SELECT * FROM BugHistory WHERE num=1

где BugHistory — это отдельная таблица, или представление.

Задание решается достаточно просто.
А имеено: с помощью триггреров. Наша задача при добавлении бага начинать его историю, и при каждом UPDATE если статус был изменен вносить строку в таблицу истории. Далее приведены функции.

CREATE OR REPLACE FUNCTION log_update()
  RETURNS trigger AS
$BODY$
BEGIN
    IF NEW.status_id <> OLD.status_id THEN
        INSERT INTO BugHistory(num, date, status_id)
        VALUES (OLD.num, now()::date, NEW.status_id)    
    END IF;    
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
CREATE TRIGGER update_log_status
BEFORE UPDATE
ON bug
FOR EACH ROW
EXECUTE PROCEDURE log_update();
CREATE OR REPLACE FUNCTION log_insert()
  RETURNS trigger AS
$BODY$
BEGIN
    INSERT INTO bughistory(num, date, status_id)
    VALUES (NEW.num, now()::date, NEW.status_id);
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
CREATE TRIGGER insert_log_status
AFTER INSERT
ON bug
FOR EACH ROW
EXECUTE PROCEDURE log_insert();

 

Структура созданной таблицы bughistory

CREATE TABLE bughistory
(
  num integer,
  date date,
  status_id integer,
  id serial NOT NULL,
  CONSTRAINT pk_bhis PRIMARY KEY (id ),
  CONSTRAINT status_fk FOREIGN KEY (status_id)
      REFERENCES bugstatus (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT to_bug_table FOREIGN KEY (num)
      REFERENCES bug (num) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE bughistory
  OWNER TO postgres;

-- Index: fki_status_fk

-- DROP INDEX fki_status_fk;

CREATE INDEX fki_status_fk
  ON bughistory
  USING btree
  (status_id );

-- Index: fki_to_bug_table

-- DROP INDEX fki_to_bug_table;

CREATE INDEX fki_to_bug_table
  ON bughistory
  USING btree
  (num );

Так же прилагаю дамп базы данных dbhw8