Обсуждение: 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 >
Steps to build extensions -- Was: "Re: [GENERAL] Postgres superuser priviledges"
От
"Gene Selkov, Jr."
Дата:
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