Re: [GENERAL] compile error
At 06:38 PM 4/2/2001 -0400, you wrote: >Soma Interesting <[EMAIL PROTECTED]> writes: > > Judging by the mailing list archives, lots of people have ran into this, > > but none received answers. > >You must not have looked in the right threads. > >If you're building interfaces/perl5 by hand (ie, not letting >src/interfaces/Makefile do it for you) then you need to say > > perl Makefile.PL POLLUTE=1 > >which is what the Makefile does (at least in recent releases). > >This is fixed in 7.1, but earlier releases need the "POLLUTE=1". > > regards, tom lane Thanks, Tom. One last hurdle (yeah right), I copied plperl.so into the same folder as plpgsql.so (/usr/lib/pgsql/lib). Then ran createlang --PGLIB=/usr/lib/pgsql/lib plperl database_name and got no errors. I then wrote a hello world plperl function and called it but got this error: ERROR: Load of file /usr/lib/pgsql/lib/plperl.so failed: libperl.so: cannot open shared object file: No such file or directory. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] compile error
At 11:03 AM 4/2/2001 -0700, you wrote: >linking ./include/port to interfaces/odbc/port >mkdir: cannot create directory `interfaces/odbc': No such file or directory >ln: ./include/port: hard link not allowed for directory >configure: error: can not link interfaces/odbc/port to ./include/port Ok, that error was because I'd only installed postgres from the base archive. Silly me. However this error... who knows Pg.xs: In function `XS_Pg_PQprint': Pg.xs:584: `na' undeclared (first use in this function) Pg.xs:584: (Each undeclared identifier is reported only once Pg.xs:584: for each function it appears in.) Pg.xs: In function `XS_PG_results_fetchrow': Pg.xs:1255: `sv_undef' undeclared (first use in this function) Pg.xs: In function `XS_PG_results_print': Pg.xs:1295: `na' undeclared (first use in this function) make[2]: *** [Pg.o] Error 1 make[2]: Leaving directory `/home/dfunct/postgresql-7.0.3/src/interfaces/perl5' make[1]: *** [all] Error 2 make[1]: Leaving directory `/home/dfunct/postgresql-7.0.3/src/interfaces' make: *** [all] Error 2 Judging by the mailing list archives, lots of people have ran into this, but none received answers. (why is it searching the mailing list on anything 75% of the time returns multiple people asking the same thing with no answers in the thread?). ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] compile error
I'm attempting to recompile postgres with perl support. Perl compiled fine, however postgres insists on compiling with odbc... I'm getting the following error when I attempt to ./configure linking ./include/port to interfaces/odbc/port mkdir: cannot create directory `interfaces/odbc': No such file or directory ln: ./include/port: hard link not allowed for directory configure: error: can not link interfaces/odbc/port to ./include/port using the following ./configure statement: ./configure --prefix=/usr/lib/pgsql --with-perl --enable-multibyte --with-odbc=no I only added the --with-odbc=no, after it gave the above error. Compiling is pretty new to me, so its probably something simple that I'm doing wrong. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] plperl -- postgresql married to perl
At 06:47 PM 3/30/2001 -0600, you wrote: > > I may be able to use array's if postgres will allow using variables to > > reference points in the array. OK, so how do I reference a specific element of an array in a record within pl/pgsql. I've tried: NEW.name[1] NEW.name.1 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] dynamic field names in a function.
At 05:47 PM 3/30/2001 -0600, you wrote: >HOWEVER -- we do have arrays, don't forget... sometimes they can >be bent to do more than intended (but usually not!) > > create table mailing( > person_id serial, > sent int4[], > prefs varchar[], > current int2 > ); > -- insert, update, munge and frob, then > select person_id,sent[current] from something; > >-- > >oh, and if your PostgreSQL instance is new enough, you might have >PERL built in, which could make all of this moot. (now we just >need someone to DOCUMENT the sucker so we know how perl can talk >back to postgres for cross-lookups and such...) Will, you are a powerful postgres wizard and never let anyone tell you otherwise. I may be able to use array's if postgres will allow using variables to reference points in the array. As for your comment about perl being built in, how can I find this out? I guess just try CREATE LANGUAGE specifying perl? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: [GENERAL] dynamic field names in a function.
At 11:30 AM 3/30/2001 -0600, you wrote: >In my experience, the best way to find out answers like this is to try it >out and see. Usually I find that I need to fiddle around with the syntax a >bit (I believe it's called debugging) before getting something to work. >Postgresql is very powerful; the capability to utilize that power comes at a >price. In other words, be prepared to put in a solid investment if you want >to see a return. >(I'm not accustomed to preaching, but sometimes this just needs to be said). Well its good you don't do it often because your not very good at it. ;) I've spent a reasonable about of time trying different approaches before posting my question here. If I had the confidence that what I was trying to do was certainly possible with pl/pgsql - then I'd pursue it until I made it work. I probably have more tenacity than you realize, despite it being so very, very obvious by my trying to get an answer in this mailing list. However, since I could declare a variable called id and have a column in the table called id and perhaps I want to reference NEW.id where id is the variable value not the column - then it would seem that whatever parser is at work may have some ambiguities to cope with. Thus I begin to doubt if its something that should be expected of pl/pgsql. I've not come across any way to make a variable reference more explicit to the parser in the postgres docs so I have no choice but to ask here. Then again it would make good sense if the parser did evaluated variables before evaluating field references... but the fact is, "I DON'T KNOW". Thanks. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Fwd: Re: [GENERAL] timestamp/function question
>blah blah blah > >...and that all meant what? The postgres manual is open to much >interpretation to anyone new trying to understand its contents. Combine >that with documentation that's still not written, or broken across several >different sections (programmer, user, admin, etc) and a search engine >which returns absolute crap well I guess us new users can just go use >MySQL. > >as far as I can tell the above sounds like a complicated work-around to a >bug, but maybe you'll be kind enough to correct me on this...? BETTER YET! edit my example code so it works and post it to this list so everyone else can have a function that uses ''now'' as actually "this very moment in time", rather than "the moment the main parser made 'now' a constant". Yeesh! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] timestamp/function question
At 12:41 AM 3/29/2001 -0600, you wrote: >do. In the case of logfunc1(), the Postgres main parser knows >when preparing the plan for the INSERT, that the string 'now' >should be interpreted as datetime because the target field of >logtable is of that type. Thus, it will make a constant from it >at this time and this constant value is then used in all >invocations of logfunc1() during the lifetime of the backend. >Needless to say that this isn't what the programmer wanted. > >In the case of logfunc2(), the Postgres main parser does not know >what type 'now' should become and therefor it returns a datatype >of text containing the string 'now'. During the assignment to the >local variable curtime, the PL/pgSQL interpreter casts this >string to the datetime type by calling the text_out() and >datetime_in() functions for the conversion. blah blah blah ...and that all meant what? The postgres manual is open to much interpretation to anyone new trying to understand its contents. Combine that with documentation that's still not written, or broken across several different sections (programmer, user, admin, etc) and a search engine which returns absolute crap well I guess us new users can just go use MySQL. as far as I can tell the above sounds like a complicated work-around to a bug, but maybe you'll be kind enough to correct me on this...? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] time to integer
At 02:27 PM 3/6/2001 -0500, you wrote: >date_part('epoch', timestamp) produces a Unix-style seconds count. > > regards, tom lane Ok, thanks I misunderstood the directions and thought I had to use epoch with the timestamp function somehow :) this is my trigger and function: CREATE FUNCTION oned_member_num() RETURNS OPAQUE AS ' BEGIN NEW.member_num := NEW.id + date_part('epoch', timestamp 'now'); RETURN new; END;' LANGUAGE 'plpgsql'; CREATE TRIGGER oned_member_num BEFORE INSERT ON members FOR EACH ROW EXECUTE PROCEDURE oned_member_num(); I'm not 100% sure that a pl/pgsql function is the best/only solution here. Is it overkill, or my only option? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Fwd: Re: [GENERAL] time to integer
I should have mentioned that NEW.id is set to default to nextval('id_seq'). >this is my trigger and function: > >CREATE FUNCTION oned_member_num() RETURNS OPAQUE AS ' > BEGIN > NEW.member_num := NEW.id + date_part('epoch', timestamp > 'now'); > RETURN new; > END;' >LANGUAGE 'plpgsql'; > > >CREATE TRIGGER oned_member_num >BEFORE INSERT >ON members >FOR EACH ROW >EXECUTE PROCEDURE oned_member_num(); > >I'm not 100% sure that a pl/pgsql function is the best/only solution here. >Is it overkill, or my only option? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Test for existence of Table
Can I test for an existing table before issuing the "CREATE TABLE" command? IF EXISTS employees{ DROP TABLE employees } CREATE TABLE employees Can you provide an example of this. I couldn't find this in the manual or Bruce's book - did I not look hard enough ? :) - - - - - - - - - - - WARNING: Some experts believe that use of any keyboard may cause serious injury. Consult Users Guide. [EMAIL PROTECTED]
[GENERAL] extra spaces
I'm using PHP & PostgreSQL. All values called from the database are still padded with extra spaces from the column size in the database. Is this normal - I don't remember this happening when I was using MySQL. I thought usually the database stripped the extra spaces when it retrieved the values. How do you recommend dealing with this, using a PHP command to strip every value from the database - or can I change something in the database to have it strip the spaces auto magic like? - - - - - - - - - - - WARNING: Some experts believe that use of any keyboard may cause serious injury. Consult Users Guide. [EMAIL PROTECTED]
[GENERAL] help with referential integrity
Date: Mon, 11 Dec 2000 18:43:55 -0800 To: [EMAIL PROTECTED] From: Soma Interesting <[EMAIL PROTECTED]> Subject: help with referential integrity I'm attempting, for the first time, to make use of referential integrity, but I'm getting an error. Warning: PostgreSQL query failed: ERROR: referential integrity violation - key referenced from LessonsPages not found in Lessons Here is the table's structures: CREATE SEQUENCE "LessonsPages_id_seq"; CREATE TABLE "LessonsPages" ( "id" int4 DEFAULT nextval('"LessonsPages_id_seq"') NOT NULL, "lid" int4 NOT NULL REFERENCES "Pages" ON DELETE CASCADE, "pid" int4 NOT NULL REFERENCES "Lessons" ON DELETE CASCADE, PRIMARY KEY ("lid", "pid") ); CREATE SEQUENCE "Lessons_id_seq"; CREATE TABLE "Lessons" ( "id" int4 nextval('"Lessons_id_seq"') NOT NULL, "page_name" char(64), "summary_email" bool, "order_num" int2, PRIMARY KEY ("id") ); CREATE SEQUENCE "Pages_id_seq"; CREATE TABLE "Pages" ( "id" int4 nextval('"Pages_id_seq"') NOT NULL, "page_name" char(64), "summary_email" bool, "order_num" int2, PRIMARY KEY ("id") ); The error comes when: INSERT INTO "Pages" (order_num) VALUES (1); SELECT id FROM "Pages"; | id | 16 1 Row INSERT INTO "Lessons" (lesson_name, order_num) VALUES ('test lesson', 2); SELECT id FROM "Lessons"; | id | 9 1 Row INSERT INTO "LessonsPages" (lid,pid) VALUES (9,16); What am I doing wrong? - - - - - - - - - - - - WARNING: Some experts believe that use of any keyboard may cause serious injury. Consult Users Guide. [EMAIL PROTECTED]
[GENERAL] help with referential integrity
I'm attempting, for the first time, to make use of referential integrity, but I'm getting an error. Warning: PostgreSQL query failed: ERROR: referential integrity violation - key referenced from LessonsPages not found in Lessons Here is the table's structures: CREATE SEQUENCE "LessonsPages_id_seq"; CREATE TABLE "LessonsPages" ( "id" int4 DEFAULT nextval('"LessonsPages_id_seq"') NOT NULL, "lid" int4 NOT NULL REFERENCES "Pages" ON DELETE CASCADE, "pid" int4 NOT NULL REFERENCES "Lessons" ON DELETE CASCADE, PRIMARY KEY ("lid", "pid") ); CREATE SEQUENCE "Lessons_id_seq"; CREATE TABLE "Lessons" ( "id" int4 nextval('"Lessons_id_seq"') NOT NULL, "page_name" char(64), "summary_email" bool, "order_num" int2, PRIMARY KEY ("id") ); CREATE SEQUENCE "Pages_id_seq"; CREATE TABLE "Pages" ( "id" int4 nextval('"Pages_id_seq"') NOT NULL, "page_name" char(64), "summary_email" bool, "order_num" int2, PRIMARY KEY ("id") ); The error comes when: INSERT INTO "Pages" (order_num) VALUES (1); SELECT id FROM "Pages"; | id | 16 1 Row INSERT INTO "Lessons" (lesson_name, order_num) VALUES ('test lesson', 2); SELECT id FROM "Lessons"; | id | 9 1 Row INSERT INTO "LessonsPages" (lid,pid) VALUES (9,16); What am I doing wrong? - - - - - - - - - - - - WARNING: Some experts believe that use of any keyboard may cause serious injury. Consult Users Guide. [EMAIL PROTECTED]
[GENERAL] Messages not going through
I've sent a message of mine to this list twice now, and it never comes through. Odd. - - - - - - - - - - - - WARNING: Some experts believe that use of any keyboard may cause serious injury. Consult Users Guide. [EMAIL PROTECTED]
[GENERAL] multi-byte support
Documentation on multi-byte and locales support seems to be lacking in the postgreSQL site - anyone have any information on this? Thanks.
Re: [GENERAL] Overhead of tables.
At 11:48 PM 12/5/2000 -0500, you wrote: >Soma Interesting <[EMAIL PROTECTED]> writes: > > I'd like to get an idea of the overhead introduced by large quantity of > > tables being hosted off a single PostgreSQL server. It is possible I'll be > > wanting to host upwards of 200-500 tables per server. Essentially, will I > > be surprised to find out that performance in PostgreSQL (or DBMS in > > general) is significantly hindered by sheer quantity of tables? >When you get to tens of thousands of tables per server, we might start >to worry a little... 500 is in the "what me worry?" class. > > regards, tom lane That is what I'd expect - but I've not experienced it to really know first hand. Thanks for the input. If a couple more people would just say the same thing - I could rest easy about moving forward on this. :)