make error with Pg9.5

[ppKrauss]

I think this project will be a good solution to this explaind problem... But make is not working with PostgreSQL v9.5+.

make
Makefile:25: /usr/lib/postgresql/9.5/lib/pgxs/src/makefiles/pgxs.mk: 
  No such file or directory
make: *** No rule to make target 
  `/usr/lib/postgresql/9.5/lib/pgxs/src/makefiles/pgxs.mk'.  
  Stop.

Using UBUNTU 14 LTS

[wulczer]

Dang, looks like I need to update for new PGXS. I'll fix it soon and publish an updated version on PGXN.

[wulczer]

Hey, I tried on my local Debian machine and the extension compiled fine.

Could you make sure you have the postgresql-server-dev-9.5 package installed and paste the output of the following commands? Thanks!

which pg_config
dpkg -l 'postgresql-*'
pg_config --pgxs
file $(pg_config --pgxs)

[ppKrauss]

Hi @wulczer, thanks attention. My dump:

 which pg_config
/usr/bin/pg_config
peter@pk-lagarto:~$ dpkg -l 'postgresql-*'
Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
||/ Nome           Versão       Arquitectura Descrição
+++-==============-============-============-=================================
un  postgresql-7.4 <nenhuma>    <none>       (nenhuma descrição disponível)
un  postgresql-8.0 <nenhuma>    <none>       (nenhuma descrição disponível)
un  postgresql-9.1 <nenhuma>    <none>       (nenhuma descrição disponível)
ii  postgresql-9.3 9.3.11-1.pgd amd64        object-relational SQL database, v
un  postgresql-9.3 <nenhuma>    <none>       (nenhuma descrição disponível)
ii  postgresql-9.3 2.1.8+dfsg-5 amd64        Geographic objects support for Po
ii  postgresql-9.3 2.1.8+dfsg-5 all          PostGIS for PostgreSQL 9.3 -- scr
ii  postgresql-9.3 2.2.1+dfsg-2 amd64        Geographic objects support for Po
ii  postgresql-9.3 2.2.1+dfsg-2 all          Geographic objects support for Po
ii  postgresql-9.4 9.4.6-1.pgdg amd64        object-relational SQL database, v
un  postgresql-9.4 <nenhuma>    <none>       (nenhuma descrição disponível)
ii  postgresql-9.5 9.5.1-1.pgdg amd64        object-relational SQL database, v
un  postgresql-9.5 <nenhuma>    <none>       (nenhuma descrição disponível)
un  postgresql-9.5 <nenhuma>    <none>       (nenhuma descrição disponível)
ii  postgresql-9.5 2.2.1+dfsg-2 amd64        Geographic objects support for Po
ii  postgresql-9.5 2.2.1+dfsg-2 all          Geographic objects support for Po
un  postgresql-cli <nenhuma>    <none>       (nenhuma descrição disponível)
ii  postgresql-cli 9.3.11-1.pgd amd64        front-end programs for PostgreSQL
ii  postgresql-cli 9.4.6-1.pgdg amd64        front-end programs for PostgreSQL
ii  postgresql-cli 9.5.1-1.pgdg amd64        front-end programs for PostgreSQL
ii  postgresql-cli 172.pgdg14.0 all          manager for multiple PostgreSQL c
ii  postgresql-com 172.pgdg14.0 all          PostgreSQL database-cluster manag
ii  postgresql-con 9.5+172.pgdg all          additional facilities for Postgre
ii  postgresql-con 9.3.11-1.pgd amd64        additional facilities for Postgre
ii  postgresql-con 9.4.6-1.pgdg amd64        additional facilities for Postgre
ii  postgresql-con 9.5.1-1.pgdg amd64        additional facilities for Postgre
un  postgresql-doc <nenhuma>    <none>       (nenhuma descrição disponível)
un  postgresql-doc <nenhuma>    <none>       (nenhuma descrição disponível)
un  postgresql-doc <nenhuma>    <none>       (nenhuma descrição disponível)
un  postgresql-doc <nenhuma>    <none>       (nenhuma descrição disponível)
un  postgresql-pos <nenhuma>    <none>       (nenhuma descrição disponível)

pg_config --pgxs
/usr/lib/postgresql/9.5/lib/pgxs/src/makefiles/pgxs.mk

file $(pg_config --pgxs)
/usr/lib/postgresql/9.5/lib/pgxs/src/makefiles/pgxs.mk: ERROR: cannot open `/usr/lib/postgresql/9.5/lib/pgxs/src/makefiles/pgxs.mk' (No such file or directory)

[wulczer]

Thanks, it looks like you're still missing the postgresql-server-dev-9.5 package.

Could you paste the output of:

apt-cache policy postgresql-server-dev-9.5

And then maybe just try installing it with:

sudo aptitude install postgresql-server-dev-9.5

[ppKrauss]

Thanks! Only details to "standard UBUNTU",

sudo apt-cache policy postgresql-server-dev-9.5 
sudo apt-get install postgresql-server-dev-9.5
sudo service postgresql restart

---

The message after cd first_last_agg; make was

cp sql/first_last_agg.sql sql/first_last_agg--0.1.4.sql
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -g -O2 -fstack-protector --param=ssp-buffer-size=4 -Wformat -Werror=format-security -I/usr/include/mit-krb5 -fPIC -pie -fno-omit-frame-pointer -fpic -I. -I./ -I/usr/include/postgresql/9.5/server -I/usr/include/postgresql/internal -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include/tcl8.6  -c -o src/first_last_agg.o src/first_last_agg.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -g -O2 -fstack-protector --param=ssp-buffer-size=4 -Wformat -Werror=format-security -I/usr/include/mit-krb5 -fPIC -pie -fno-omit-frame-pointer -fpic -L/usr/lib/x86_64-linux-gnu -Wl,-Bsymbolic-functions -Wl,-z,relro -Wl,-z,now -Wl,--as-needed -L/usr/lib/mit-krb5 -L/usr/lib/x86_64-linux-gnu/mit-krb5  -Wl,--as-needed  -shared -o src/first_last_agg.so src/first_last_agg.o

if was correct, how to use it at psql (need Readme remembering)?
CREATE EXTENSION IF NOT EXISTS first_last_agg;

but is not working, the message at psql is "file not found" /usr/share/postgresql/9.5/extension/first_last_agg.control

so, your make not created it.

... It is not a problem with /usr/share/postgresql/9.5/extension (it exists) neither with my extensions — I am using eg postgis.control and fuzzystrmatch.control).

---

other dumps

ignore it, is only a dump to remember later, in case of need...

PS: strange, I was using 9.5 (!), not make sense reinstall only for pgxs... but is working... perhaps need to change the in-use-cluster... There was an upgrade from 9.5.1 o 9.5.2

sudo apt-cache policy postgresql-server-dev-9.5
postgresql-server-dev-9.5:
  Instalado: (nenhum)
  Candidato: 9.5.2-1.pgdg14.04+1
  Tabela de versão:
     9.5.2-1.pgdg14.04+1 0
        500 http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg/main amd64 Packages

sudo apt-get install postgresql-server-dev-9.5
Lendo listas de pacotes... Pronto
Construindo árvore de dependências       
Lendo informação de estado... Pronto
Os seguintes pacotes foram instalados automaticamente e já não são necessários:
  libdbd-mysql-perl libdbi-perl libterm-readkey-perl
Utilize 'apt-get autoremove' para os remover.
Os NOVOS pacotes a seguir serão instalados:
  postgresql-server-dev-9.5
0 pacotes atualizados, 1 pacotes novos instalados, 0 a serem removidos e 16 não atualizados.
É preciso baixar 715 kB de arquivos.
Depois desta operação, 3.791 kB adicionais de espaço em disco serão usados.
Obter:1 http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg/main postgresql-server-dev-9.5 amd64 9.5.2-1.pgdg14.04+1 [715 kB]
Baixados 715 kB em 5s (129 kB/s)                   
A seleccionar pacote anteriormente não seleccionado postgresql-server-dev-9.5.
(Lendo banco de dados ... 496722 ficheiros e directórios actualmente instalados.)
Preparing to unpack .../postgresql-server-dev-9.5_9.5.2-1.pgdg14.04+1_amd64.deb ...
Unpacking postgresql-server-dev-9.5 (9.5.2-1.pgdg14.04+1) ...
Configurando postgresql-server-dev-9.5 (9.5.2-1.pgdg14.04+1) ...

[wulczer]

You need to run sudo make install to install the extension after it's compiled. Once that's done, you should be able to execute CREATE EXTENSION IF NOT EXISTS first_last_agg; from within psql.

[ppKrauss]

Yes, thanks, is working!

So, summarising UBUNTU+pg9.5 install (suggest README with some clues):

which pg_config
dpkg -l 'postgresql-*'
pg_config --pgxs
file $(pg_config --pgxs)

sudo apt-cache policy postgresql-server-dev-9.5 
sudo apt-get install postgresql-server-dev-9.5
sudo service postgresql restart

sudo make install

---

Using first_last_agg

As in this example (or this expectations) we expected that first() have better performance than max() or than use GROUP BY x,y,z ... But my test with first() with 40000 rows not show better performance (is worst than GROUP BY in 0.1 sec).

[wulczer]

Glad to hear you made it work! For the record, first() won't be any faster than max() since they're both build-in C functions. It's intended to replace these functions, so if your use case requires them, then it's going to give you a small performance benefit.

[hgl]

Is first() not being faster than max() due to the reason that first() doesn't return early? Is it possible for an aggregate function to return early and not to look at future rows?

[wulczer]

Aggregate functions are invoked on every tuple in the aggregate, there is currently no API that allows telling the executor to not call the function anymore.

[ppKrauss]

Hi @wulczer, your project is a demonstration that the "Core of PostgreSQL" need an API (!), so I invite @hgl, and offer myself, to help you in a dialog with the (core of) PostgreSQL community.

PS: we can also enhance discussions at this dba.stackexchange question or this stackoverflow question.