Обсуждение: Postgres superuser priviledges

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.
        This probably means the backend terminated abnormally before or
while processing the request.

Why???????????????????
Initially, I thought it's because I did not have superuser priviledges.
However, if I didn't I would not be able to issue CREATE FUNCTION for a C
function ( I had this problem in the past and I had the system
administrator change my entry in the pg_user table so that the attribute
'usesuper' is set to true).

I get the same message from PQexec() when typing
  test=> load '/home/M97/acs/vassilik/protein/phone.so';


I think there is something wrong with the flags I used during compilation
or linkediting (I even tried to create a Makefile as somebody suggested to
me but I still had the same problems)

Can somebody help me? Anybody used Postgres under Linux Red Hat before?
I really need to know because I am stuck at this and cannot proceed with
my project.
Kostas

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:

> 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
>
>

#include <stdio.h>
/* In the FAQ section 4.2 it says not to include this header
 file when writing user-defined functions #include "libpq-fe.h"*/
/* #include "libpq-fe.h"*/
#include "postgres.h"
#include "utils/palloc.h"
#include "utils/mcxt.h"

typedef struct {
  char natcode[6]; /* one extra for the NUL character to fit*/
  char number[8];} /* one extra for the NUL character to fit*/ 
phone;

/* These prototypes declare the requirements that Postgres places on these
   user written functions.
*/
phone *phone_in(char *outphone);
char  *phone_out(phone *inphone);
/*****************************************************************************
* Input/Output functions
 *****************************************************************************/

phone *
phone_in(char  *outphone)
{
 char nat[6],num[8]; /* one extra for the NUL character to fit*/
 phone *result;
 int arguments,i;
 
  arguments=sscanf(outphone,"%[0-9]-%[0-9]",nat,num);/* the input string stops at white space or at the maximum field
width,whichever occurs first*/
 
  printf("The function returned %d arguments\n",arguments);
  printf("national code:%s\nnumber:%s\n",nat,num);
  if (arguments!=2) 
   return NULL;
 result=(phone *) palloc(sizeof(phone));
 i=0; 
 while (nat[i]!='\0')
 {
  result->natcode[i]=nat[i];
  i++;
 }
 i=0;
 while (num[i]!='\0')
 {
  result->number[i]=num[i];
  i++;
 }
 return (result);
 }

char *
phone_out(phone *inphone)
 {
 char *result;
 if (inphone==NULL)
  return NULL;
 result=(char *)palloc(60);
 sprintf(result,"%s-%-s",inphone->natcode,inphone->number);
 return result;
 }

main()
 {

  phone *a,*b,*c;
  
  a = phone_in("01483-827294");
  printf("a = %s\n", phone_out(a));
  b = phone_in("0161-2242394");
  printf("b = %s\n", phone_out(b));
  c = phone_in("01189-887762");
  printf("c = %s\n", phone_out(c));

}
CREATE FUNCTION phone_in(opaque)
 RETURNS phone
 AS '/home/postgres/linux/src/phone/phone.so'
 LANGUAGE 'c';

CREATE FUNCTION phone_out(opaque)
 RETURNS opaque
 AS '/home/postgres/linux/src/phone/phone.so'
 LANGUAGE 'c';

CREATE TYPE phone (
 internallength =8,
 input = phone_in,
 output = phone_out);

CREATE TABLE test_phone (
 mynumber phone,
 hernumber phone);

INSERT INTO test_phone VALUES ('01483-827294','0161-2242394');
INSERT INTO test_phone VALUES ('0171-8235465','01189-887762');

SELECT * FROM test_phone; 

DROP FUNCTION phone_in(opaque);
DROP FUNCTION phone_out(opaque);
DROP TYPE phone;
DROP TABLE test_phone;
SRCDIR= .. 
include ../Makefile.global

CFLAGS+= -I$(LIBPQDIR) -I../../include

#
# DLOBJS is the dynamically-loaded object files.
#

DLOBJS= phone$(DLSUFFIX)

ifdef EXPSUFF
DLOBJS+=$(DLOBJS:.o=$(EXPSUFF))

all: $(DLOBJS)

$(DLOBJS): phone.o
        gcc -shared -o phone.so phone.o

clean:
    rm -f $(DLOBJS)
        rm -f *.o *~ *# 

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:

> 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
>

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