Postgres superuser priviledges

Konstantinos Vassiliadis wrote:

Hi
I am new to Postgres. I am trying to load a C function in Postgres under
Red Hat Linux.
I compile using
gcc -I$PGROOT -I$PGROOT/include -I$PGROOT/src/include -c phone.c
to produce the object file 'phone.o'
Then I link using
ld -Bdynamic -o phone.so phone.o -lc
to produce the shared object 'phone.so'.
(Assuming I am doing things right so far) Then from psql:

=> CREATE FUNCTION phone_in(opaque)
RETURNS phone
AS '/home/M97/acs/vassilik/protein/phone.so'
LANGUAGE 'c';
NOTICE: ProcedureCreate: type 'phone' is not yet defined
CREATE

Same for the output function, the type itself and a table that uses the
type.
Then
test=> INSERT INTO test_phone VALUES ('01483-827294','0161-2242394');
PQexec() -- Request was sent to backend, but backend closed the channel
before responding.

There more than one thing that can go wrong. You are welcome to send me
your c source and sql to create the type. I will check.

Can somebody help me? Anybody used Postgres under Linux Red Hat before?

That's how is used most often, I think. You could also try to build one
of my own extensions, found at

http://wit.mcs.anl.gov/~selkovjr/

ec-type.tgz is the easiest of these.

Gene

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.
b) Do I need to place my directory with all the files under $PGROOT/src/
where $PGROOT is the postgres directory?

I have issued
%make
from the directory where all my files are and gave
make: *** ..: Is a directory. Stop.

I attach the C source, the SQL definitions and my Makefile.
Thanks again
Kostas

On Tue, 21 Jul 1998, Gene Selkov, Jr. wrote:

Hi again
I tried to use your data type. I compiled the ec.c using

gcc -I$PGROOT/include -c ec.c

to produce the ec.o file

Then issued "make" and gave
Makefile:19: *** missing separator. Stop.

This is the line
$(DLOBJS): ec.o
gcc -shared -o ec.so ec.o <---Here is line 19
What is wrong?

Kostas

Hi again
The server that PostgreSQL resides is a Sun running SunOS 4.1.x. The
client is a Red Hat Linux. I think I need to compile the C source for the
target platform (i.e. SunOS 4.1.x) is that right?

Anyway, I assumed this is right so I compiled
gcc -I$PGROOT/include -I$PGROOT/src/include -c phone.c
and then
% make
gave error Makefile:19***missing operator

On the other hand, if I compile under Red Hat Linux
gcc -I$PGROOT/include -I$PGROOT/src/include -c phone.c
and then
% make
gave error make: Fatal error in reader:../Makefile.global, line 54:
Unexpected end of line seen.

Which option (target or Red Hat)? Then why error in either?
Kostas

On Tue, 21 Jul 1998, Gene Selkov, Jr. wrote:

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

Konstantinos Vassiliadis wrote:

I have issued
%make
from the directory where all my files are and gave
make: *** ..: Is a directory. Stop.

I attach the C source, the SQL definitions and my Makefile.

[snip]

This is just to inform you of the status of your project as it would
stand now in a less hostile environment. Congratulations, you almost
made it. You still have some problem in your c code that I don't
understand (scanf? use of pointers?). I'll give it another shot
tomorrow.

Here is an account of the problems you apparently ran across.

A number of things were wrong in your makefile, mostly invisible ones.

1. The lines were terminated with '\r' which can upset some versions of
make and compilers. I cure this problem with

perl -i.b -npe 's/\r//g' * ; rm -f *.b

2. If you omit space here:

DLOBJS+= $(DLOBJS:.o=$(EXPSUFF))
^----thius one

It complains about

Makefile:23: *** missing `endif'

The cause is difficult to find. I could only arrive there through
comparison.

3. Rules in targets should always start with a tab. That's not just
style, it is part of the syntax. Failure to place a tab between a target
and its rules (or between these) results in

Makefile:19: *** missing separator.

4. Includes at the top of the Makefile must refer to the postgres source
directory and the top level Makefile, respectively.

In the c code,

1. should be no main() -- linker will complain and probably bail out
because of unresolved symbol

2. should be no printf() -- because there is no stdout. Use
fprintf(stderr,
...), it will write to the backend logfile, or use fopen(file, ...) in
the append mode and fprintf(file, ...), but this requires you to have
permissions to read the backend log or the file it will create.

This is what I got wen I built the library and ran your sql:

kostas=> INSERT INTO test_phone VALUES ('01483-827294','0161-2242394');
INSERT 23502 1
kostas=> INSERT INTO test_phone VALUES ('0171-8235465','01189-887762');
INSERT 23503 1
kostas=>
kostas=> SELECT * FROM test_phone;
mynumber |hernumber
-----------------------------+-------------
01483m820161-820161 |0161-22
0171',8201189m88�-8201189m88�|01189m88�-88�
(2 rows)

There is almost no way to debug *_in() functions from within themselves,
but I found it useful to define a set of simple procedures (sort of
_out()) that retrieve only one structure element at a time and return it
as a string. That way, if something goes wrong, you can be almost sure
it is not in the output. In your example, you could define a function
area_code() and do the following select:

select area_code(mynumber) from test_phone;

Gene