Steps to build extensions -- Was: "Re: [GENERAL] Postgres superuser priviledges" - Mailing list pgsql-general

Sorry I could not keep up with your progress -- I am grossly
overcommited :(

Konstantinos Vassiliadis wrote:
>
> Thanks in advance for your help.
> I had a look at your defined type. I have some questions:
> a) I don't really understand the syntax of the Makefile. I understand it
> is required to do the job and I simply substituted your files with mine.

To tell you the truth, I can't say I understand it either. I can grasp
barely enough to make it work in some way.

> b) Do I need to place my directory with all the files under $PGROOT/src/
> where $PGROOT is the postgres directory?

If you did that, would be all set. I have always built my extensions by
placing them in $PGROOT/src/ and I did that as a postgres superuser,
just in order to avoid changing the makefiles. But since your user
privileges on the system you are using seem to be different, I made the
steps I needed to take, were I a regular user with all postgres
permissions. In this context, selkovjr is my unix name and it is also a
postgres user name.

1. Arrange superuser privileges for yourself:

[postgres@selkov-6 ec]$ createuser selkovjr
Enter user's postgres ID or RETURN to use unix user ID: 556 ->
Is user "selkovjr" allowed to create databases (y/n) y
Is user "selkovjr" allowed to add users? (y/n) y
createuser: selkovjr was successfully added


2. Place the extention source somewhere in your home directory or other
location with rwx permissions for yourself

mkdir src
cd src/
tar zxvf /home/postgres/ec-type.tgz  <-- originally form
http://wit.mcs.anl.gov/~selkovjr


3. Modify the absolute path to ec.so:

cd ec/
perl -i.b -npe 's#usr/src/pgsql#home/selkovjr/src/ec#g' * ; rm -f *.b

(specify the actual location of the source files between the last two
poundsign characters -- the resulting string must be the absolute path)


4. Change the first two lines in the Makefile to refer to $PGROOT/src/

SRCDIR= /usr/src/pgsql/src/
include /usr/src/pgsql/src/Makefile.global

(substitute with your $PGROOT, or set your environment, or supply an
argument to make)


5. Make it

make clean; make

rm -f ec.so
rm -f *.o *~ *#
gcc -I/usr/src/pgsql/src//include -I/usr/src/pgsql/src//backend
-Wall -Wmissing-prototypes -I/usr/src/pgsql/src//interfaces/libpq
-I../../include   -c ec.c -o ec.o
gcc -shared -o ec.so ec.o


6. Set up your postgres environment. I normally do not have these
settings when I work a reglular user.

source /home/postgres/.bash_profile

which has:

[selkovjr@selkov-6 ec]$ cat /home/postgres/.bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs
PATH=$PATH:$HOME/bin
ENV=$HOME/.bashrc
USERNAME=""

PATH=$PATH:/usr/local/pgsql/bin
MANPATH=$MANPATH:/usr/local/pgsql/man
PGLIB=/usr/local/pgsql/lib
PGDATA=/usr/local/pgsql/data
export MANPATH PGLIB PGDATA
export USERNAME ENV PATH


7. Create a test database. It is always safer to test extensions in a
junk database; if something goes wrong, you simply destroy it.

destroydb test; createdb test


8. Load the new typ in the database

psql -d test <ec.type.create.sql

Watch for errors. If it goes well, you will only receive a NOTICE saying
your type does not exist from CREATE FUNCTION ec_in(opaque). Make sure
you have a scrollable screen or redirect the output to a file (>&).

9. Test it

[selkovjr@selkov-6 ec]$ psql -d test
Welcome to the POSTGRESQL interactive sql monitor:
  Please read the file COPYRIGHT for copyright terms of POSTGRESQL

   type \? for help on slash commands
   type \q to quit
   type \g or terminate with semicolon to execute query
 You are currently connected to the database: test

test=> \d

Database    = test
 +------------------+----------------------------------+----------+
 |  Owner           |             Relation             |   Type   |
 +------------------+----------------------------------+----------+
 | selkovjr         | test_ec                          | table    |
 +------------------+----------------------------------+----------+
test=> select * from test_ec;
       ec
---------
  1.1.1.1
  1.1.1.-
1.2.1.114
  1.1.3.0
 2.3.18.4
  2.3.1.6
 4.3.2.12
  6.2.-.-
  5.4.1.9
  5.4.3.9
  1.1.1.4
 1.1.1.89
  2.7.1.1
 2.7.1.12
5.2.1.114
(15 rows)

test=> select * from test_ec where ec ~ '1.1';
      ec
--------
 1.1.1.1
 1.1.1.-
 1.1.3.0
 1.1.1.4
1.1.1.89
(5 rows)

test=>



That's about all to it. I will check out your telephone type as soon as
I get my bosses and children off my back.


Gene