[GENERAL] Given: Win98, Cygwin, postgresql-7.0.3.tar.gz | Need: to make libpg/(++) libraries?
Hi, As the Subject line says, all I am looking to do is to make the libraries so I can write some C/C++ postgresql client programs under Cygwin (under Win98) using the GNU C/C++ compilers. I guess I could use ODBC and MSVC++, but I just don't want to get stuck in a rut. I do not need to build any of the server components, so I was hoping that the "configure" script would be smart enough to produce make files that will make the rest of the package with the necessary client libraries; however, it was clear as I was monitoring the "make" output that I either need other libraries or I need to restrict my "configure" options further. The make encountered problems when it started to compile "dllinit.c": dllinit.c:49: warning: no previous prototype for '_cygwin_dll_entry' dllinit.c:49: warning: no previous prototype for '_cygwin_noncygwin_dll_entry' This either caused or was caused by the dlltool to fail with the port/SUBSYS.o: dlltool: port/SUBSYS.o: no symbols I am guessing it was caused by an incomplete SUBSYS.o object created from compiling src/backend/port/dynloader.c... but I have not taken a detailed look. I am hoping that someone can point me in the right direction or tell me what is missing. Do I have the right package? I seem to recall that there was some splitting up of the package into 3 parts: client-only, server-only and common... but I may be mixing that recollection with something else. TIA, Frank Shim ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] plpgsql -- arrays/temporary tables?
The following function doesn't work when called multiple times: CREATE FUNCTION foo(INTEGER) RETURN BOOLEAN AS ' DECLARE y INTEGER; BEGIN CREATE TEMP TABLE a ( x INTEGER ); INSERTINTO a VALUES(4); SELECTINTO y x FROM a; DROP TABLE a; RETURN TRUE; END; ' LANGUAGE 'plpgsql'; I understand that this is because the query plan uses the existing table `a' repeatedly. My questions: * How can I make this work? Are temporary tables essentially useless inside plpgsql functions? * Does plpgsql support array constructs? How can I use one of these? How can I use it in an IN clause? (Assume the array contained a list of primary keys.) * I've considered trying plperl, but I've seen no examples anywhere of how to execute SQL queries inside plperl! Any pointers on documentation, or an example, or anything? I've combed the search engines for a clue but haven't found one. I am using 7.1RC4. Thanks in advance for any tips! -- Steven D. Arnold Que quiero sera [EMAIL PROTECTED] "He was part of my dream, of course -- but then I was part of his dream too." -- Lewis Carroll ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Re: Speaking of Indexing... (Text indexing)
> Furthermore, after trying to just index on a 8191-character long substring > of the resume, I run into the following: > ERROR: btree: index item size 3948 exceeds maximum 2713 > The only way I could actually get the index created was to substring the > body of the resumes down to 2k. I also later tried using HASH rather than > BTREE, which worked, but none of these solutions really appreciably > increased performance in the way we were hoping. > > Are these known and accepted limitations of the current 7.1 > implementation, or am I doing something terribly wrong? ;) Hmm. I'm pretty sure that a single index on the entire contents of a resume *as a single field* is close to useless. And an index on an 8k piece is also useless. Presumably you really want an index covering each significant word of each resume, in which case you would not run into the 4k limit (or 2k limit? it is documented somewhere) on the size of an *index* field (which is still a limitation on PostgreSQL built with the standard 8k block size. Of course, you can build with a larger block size). hth - Thomas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Re: COPY from file to table containing unique index
On Tue, 10 Apr 2001, Joe Johnson wrote: > I have a table with over 1,000,000 records in it containing names and phone > numbers, and one of the indexes on the table is a unique index on the phone > number. I am trying to copy about 100,000 more records to the table from a > text file, but I get an error on copying because of duplicate phone numbers > in the text file, which kills the COPY command without copying anything to > the table. Is there some way that I can get Postgres to copy the records > from the file and just skip records that contain duplicates to the unique > index? I found that using PHP scripts to do inserts for a file of this size > take MUCH longer than I'd like, so I'd like to avoid having to do it that > way if I can. Any help is appreciated. Thanks! There are a few options. This was discussed yesterday, in the thread 'problem with copy command' -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Re: newbie question - INSERT
On Tue, 10 Apr 2001, Cefull Lo wrote: > When I type INSERT INTO friend > VALUES ('', '', ''); > it returns > INSERT 19748 1 > > what means of 19748 and 1? It's the OID, a unique idenifier for everything in the database. Read the Momjian book on the website -- it explains this very well. -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Re: Speaking of Indexing... (Text indexing)
On Tue, 10 Apr 2001, Poet/Joshua Drake wrote: > I've been experimenting a bit with Full Text Indexing in PostgreSQL. I > have found several conflicting sites various places on the net pertaining > to whether or not PostgreSQL supports FTI, and I was hoping I could find > an authoritative answer here - I tried searching the website's archives, > but the search seems to be having some problems. > > At any rate, I am running a CVS snapshot of 7.1, and I have been trying to > create a full text index on a series of resumes. Some of these exceed 8k > in size, which is no longer a storage problem of course with 7.1, but I > seem to have run into the wicked 8k once again. Specifically: Joshua -- CREATE INDEX ... creates an index on a field, allowing for faster searches, *if* you're looking to match the first part of that text string. So, if I have a table of movie titles, creating an index on column title will allow for faster searches if my criteria is something like title='Toto Les Heros' (or like 'Toto%' or such), but not (AFAIK) for title ~ 'Les' or title LIKE '%Les%'. The index doesn't help here. For these long fields you have, you probably want to search for a word in the field, not match the start of the field. A regular index isn't your answer. There is a full text indexing solution in the contrib/ directory of the source. It essentially creates a new table w/every occurence of every word fragment, with a reference back to the row that contains it. Searching against this is indexed, and is speedy. The only downside is that you will have a *large* table holding the full text index. More help can be found in the README file in contrib/fulltextindex HTH, -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] point-in-time restore
No, not yet. Hopefully 7.2. You can perform a pg_dump anytime, though. > Is there a way to perform a point in time restore with postgresql ? > > /Claes > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] newbie question - INSERT
When I type INSERT INTO friend VALUES ('', '', ''); it returns INSERT 19748 1 what means of 19748 and 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] newbie question - INSERT
On Tue, 10 Apr 2001, Cefull Lo wrote: > When I type INSERT INTO friend > VALUES ('', '', ''); > it returns > INSERT 19748 1 > > what means of 19748 and 1? 19748 is the OID of that record, and 1 means one record was inserted. For information on OID's, look in the documentation. -- Dominic J. Eidson "Baruk Khazad! Khazad ai-menu!" - Gimli --- http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] point-in-time restore
On Tue, Apr 10, 2001 at 09:27:50PM +0200, some SMTP stream spewed forth: > Is there a way to perform a point in time restore with postgresql ? > Nope, not yet anyway, to the best of my knowledge. dan > /Claes > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Re: JDBC and Perl compiling problems w/ postgresql-7.1rc4
> The reason I need to compile w/ Perl > support turned on is what I am reading > in the README.rserv of the ERServer > available in contrib directory. > It says that the requirements are: > - PostgreSQL >= 7.0.X >A separate Makefile is required for PostgreSQL 7.0.x and earlier > - Perl5 and the PostgreSQL perl interface > I am thinking that it only requires client lib as > the module compiles just fine. Can you confirm this please? Yes. It is only the external (client-side) perl interface which is required, to support the rserv scripts. - Thomas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] COPY from file to table containing unique index
I have a table with over 1,000,000 records in it containing names and phone numbers, and one of the indexes on the table is a unique index on the phone number. I am trying to copy about 100,000 more records to the table from a text file, but I get an error on copying because of duplicate phone numbers in the text file, which kills the COPY command without copying anything to the table. Is there some way that I can get Postgres to copy the records from the file and just skip records that contain duplicates to the unique index? I found that using PHP scripts to do inserts for a file of this size take MUCH longer than I'd like, so I'd like to avoid having to do it that way if I can. Any help is appreciated. Thanks! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] JDBC and Perl compiling problems w/ postgresql-7.1rc4
Thought that I share this with the group. Thanks Marco. The problem was gone once I created a soft link to JAVA_HOME/lib/tools.jar in the default directory JAVA_HOME/jre/lib/ext/. I have not set specific CLASSPATH variable and I could understand the problem better if I had seen the problem when using ant to build other pkgs. I used ant to build tomcat and never saw this problem! For those of you looking for the compiled jar file go to the bottom of following page http://hyousefi.tripod.com/pub.html Thanks, HY -- Marko Kreen wrote: > On Mon, Apr 09, 2001 at 03:33:19PM -0700, Homayoun Yousefi'zadeh wrote: > >> I first ran configure with the following options >> >> ./configure --with-perl --with-tcl --enable-odbc --with-java >> --enable-syslog --enable-debug >> >> and then compiled postgresql-7.1rc4 on Redhat 7.0 successfully >> with the exceptions in JDBC and Perl modules as >> indicated below. > > > >> BUILD FAILED >> >> /usr/pgsql-pkg/postgresql-7.1rc4/src/interfaces/jdbc/build.xml:99: >> Cannot use classic compiler, as it is not available >> > > > (using jdk 1.3) > > Seems like you dont have JAVA_HOME/lib/tools.jar in Ant's classpath. > > Next, if you have Ant 1.2 (especially Debian's) & jdk1.3 (well, you > have this one) and have problems with: > > 1) undefined ${major} > > or > > 2) Zip* error: cannot create archive with no entries (from memory, >cant remember exact message) > > then use this patch: > > http://www.l-t.ee/marko/ant12.diff > > [for some reason (core people did not have this configuretion)] > it did not reach main tree. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: Indexes not used in 7.1RC4: Bug?
> I have the following table, containing about 57 Rows, but some > indexes are not used, on 7.1RC4, freshly vacuumed (analyse). It was the > same at least in 7.1RC1 > CREATE TABLE access_log( > access_time timestamp NOT NULL DEFAULT NOW(), > method_num int2NOT NULL, > url_id int4NOT NULL REFERENCES urls(id), > ); > CREATE INDEX method_idx ON access_log(method_num); > CREATE INDEX url_idx ON access_log(url_id); > url_idx seems OK: > But the others not: > logger=# EXPLAIN SELECT * FROM access_log WHERE method_num = 0; > Seq Scan on access_log (cost=0.00..16443.71 rows=559371 width=89) The parser does not know that your int4 constant "0" can be represented as an int2. Try SELECT * FROM access_log WHERE method_num = int2 '0'; (note the type coersion on the constant; there are other ways of specifying the same thing). For the other cases, PostgreSQL is estimating the query cost to be lower with a sequential scan. For the "SELECT 1" subselect case, it may be that the optimizer does not cheat and determine that there will be only one row returned, or that the query can be reformulated to use a simple constant. HTH - Thomas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] LOs and pg_dump, restore, vacuum for 7.1
David Wall writes: > Does 7.1 "natively" handle large objects in dumps, restores and vaccums? In > 7.0.3 there was a contrib for LOs. Yes. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Re: Converting from access to pgsql..questions...
On Tue, 10 Apr 2001, mazzo wrote: > First of all, since i'm new to this mailing listHi all..!! > My first question.. > I have to convert an access 97 database to pgsql...are there any tools to do > this?? I checked the website but the link for the tools are not working so i > tried to export the tables from access using the ODBC driver...(i'll be > using access to insert data into these tables)...everything went just fine > except one small (well not so small..) problem... > If i export a table i have problems with the primary keys if they are set to > Counterswhen i export them to pgsql they get turned into numeric and > since all the primary keys of my database are counters i have big problems > since i will have to update these manually...Is there some way to solve this > prob..?? I was thinking of using a trigger...is this a good idea?? Can it be > done..?? Or is there a better way to solve these problems..?? > Thanks in advance for your answers..and excuse me for my english and if i > made any mistakes..(i'm italian..!) You want pgAdmin, which is a great Windows admin tool for PG, and can help w/the conversion. http://www.greatbridge.org/project/pgadmin/projdisplay.php There's a useful FAQ of info on PostgreSQL + Access at http://www.scw.org Your English is *much* better than my Italian! :-) -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Converting from access to pgsql..questions...
First of all, since i'm new to this mailing listHi all..!! My first question.. I have to convert an access 97 database to pgsql...are there any tools to do this?? I checked the website but the link for the tools are not working so i tried to export the tables from access using the ODBC driver...(i'll be using access to insert data into these tables)...everything went just fine except one small (well not so small..) problem... If i export a table i have problems with the primary keys if they are set to Counterswhen i export them to pgsql they get turned into numeric and since all the primary keys of my database are counters i have big problems since i will have to update these manually...Is there some way to solve this prob..?? I was thinking of using a trigger...is this a good idea?? Can it be done..?? Or is there a better way to solve these problems..?? Thanks in advance for your answers..and excuse me for my english and if i made any mistakes..(i'm italian..!) Bye to all! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] personel appliactions?
On Tue, Apr 10, 2001 at 01:42:13PM -0400, Stan Brown wrote: > I have been suing PostgreSQL quite happily in projects for > several years now. i have the smae porbelm -- you might wnat to solw down wehn you tyep your email msesaegs. Suing is quite different from Using. :) -- americans should never read anything so subversive as what's at http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html [EMAIL PROTECTED] http://sourceforge.net/projects/newbiedoc -- we need your brain! http://www.dontUthink.com/ -- your brain needs us! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] JDBC and Perl compiling problems w/ postgresql-7.1rc4
On Mon, Apr 09, 2001 at 03:33:19PM -0700, Homayoun Yousefi'zadeh wrote: > > I first ran configure with the following options > > ./configure --with-perl --with-tcl --enable-odbc --with-java > --enable-syslog --enable-debug > > and then compiled postgresql-7.1rc4 on Redhat 7.0 successfully > with the exceptions in JDBC and Perl modules as > indicated below. > BUILD FAILED > > /usr/pgsql-pkg/postgresql-7.1rc4/src/interfaces/jdbc/build.xml:99: > Cannot use classic compiler, as it is not available > (using jdk 1.3) Seems like you dont have JAVA_HOME/lib/tools.jar in Ant's classpath. Next, if you have Ant 1.2 (especially Debian's) & jdk1.3 (well, you have this one) and have problems with: 1) undefined ${major} or 2) Zip* error: cannot create archive with no entries (from memory, cant remember exact message) then use this patch: http://www.l-t.ee/marko/ant12.diff [for some reason (core people did not have this configuretion)] it did not reach main tree. -- marko ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Re: personel appliactions?
On Tue, 10 Apr 2001, Stan Brown wrote: > I have been suing PostgreSQL quite happily in projects for several years > now. > > Now, I am thinking about actually using the computers at home for something > useful (what a concept :-)). > > So, I was wandering if anyone knows wehre I could find a few small > applicationsUsing PostgreSQL as abckaends? I'm looking for. > > 1. CD/Music lirary management. > 2. Library (books) management. > 3. Household inventory/shoping list applications. > 4. Calorie intake application. Check www.freshmeat.net; apps like this would be posted there. You may find some apps w/only a MySQL version; if so, consider doing the translation to PostgreSQL -- in most cases, little/no change in code is neccessary (especially if the author is using Perl or Python, which tend to have more abstracted DB interfaces than PHP); usually all that's needed is just to edit the SQL statements to create/select/etc. from the schema. If there is a calorie intake application, please don't tell me about it ;-) Good luck! -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] personel appliactions?
I have been suing PostgreSQL quite happily in projects for several years now. Now, I am thinking about actually using the computers at home for something useful (what a concept :-)). So, I was wandering if anyone knows wehre I could find a few small applicationsUsing PostgreSQL as abckaends? I'm looking for. 1. CD/Music lirary management. 2. Library (books) management. 3. Household inventory/shoping list applications. 4. Calorie intake application. Any sugestions? -- Stan Brown [EMAIL PROTECTED]843-745-3154 Charleston SC. -- Windows 98: n. useless extension to a minor patch release for 32-bit extensions and a graphical shell for a 16-bit patch to an 8-bit operating system originally coded for a 4-bit microprocessor, written by a 2-bit company that can't stand for 1 bit of competition. - (c) 2000 Stan Brown. Redistribution via the Microsoft Network is prohibited. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] LOs and pg_dump, restore, vacuum for 7.1
Does 7.1 "natively" handle large objects in dumps, restores and vaccums? In 7.0.3 there was a contrib for LOs. Thanks, David ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Re: [HACKERS] JDBC and Perl compiling problems w/ postgresql-7.1rc4
"Homayoun Yousefi'zadeh" <[EMAIL PROTECTED]> writes: > The reason I need to compile w/ Perl > support turned on is what I am reading > in the README.rserv of the ERServer > available in contrib directory. > It says that the requirements are: > > - PostgreSQL >= 7.0.X >A separate Makefile is required for PostgreSQL 7.0.x and earlier > - Perl5 and the PostgreSQL perl interface > > I am thinking that it only requires client lib as > the module compiles just fine. Can you confirm this please? That agrees with my reading of the sentence above, but I've not installed rserv so I'm not absolutely positive. One thing you can do is look in the SQL code that rserv uses and see if there are any CREATE FUNCTION foo() RETURNS whatever AS 'blah' LANGUAGE 'plperl'; statements. -Doug ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] JDBC compile
Tony Grant wrote: > Can someone please point me to a precompiled jdbc driver for 7.1RC4 and > JDK1.3 > > I just don't have the patience to figure out what is going wrong here. > > /usr/share/ant > > Ant install made a /usr/share/java withe the jar files in it. > > Java is installed on the machine as /usr/java/jdk1.3 > > [javac] Note: Some input files use or override a deprecated API. > [javac] Note: Recompile with -deprecation for details. > [javac] 4 errors > > is all that this gives me. > > Hey guys it has to be easier than this the rest of the compile went > really well... From what you are describing, you should have warnings rather than errors. You may actually be OK. You need to copy the file postgresql.jar to JAVA_HOME/jre/lib/ext/ if it has been created. Simply ignore the deprecation msg's and see whether you can connect to a sample database using the driver. Check the content of the jar file w/ jar tvf postgresql.jar if the driver file is there but the connection fails. Thanks, HY ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] JDBC compile
Can someone please point me to a precompiled jdbc driver for 7.1RC4 and JDK1.3 I just don't have the patience to figure out what is going wrong here. /usr/share/ant Ant install made a /usr/share/java withe the jar files in it. Java is installed on the machine as /usr/java/jdk1.3 [javac] Note: Some input files use or override a deprecated API. [javac] Note: Recompile with -deprecation for details. [javac] 4 errors is all that this gives me. Hey guys it has to be easier than this the rest of the compile went really well... Cheers Tony Grant -- RedHat Linux on Sony Vaio C1XD/S http://www.animaproductions.com/linux2.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Re: Very long running query
On 10 Apr 2001, Konstantinos Agouros wrote: > Hi, > > I have a query running for 97hours now and I am wondering if this can be made > any faster. > The Query is: > insert into dailyreport select timestamp(date >'1-8-2001'),a.category,'Observed',sum(b.count) as count from websensebycat a, >netscapereduce b where a.url = b.url and a.action='Observed' and a.datum='1-8-2001' >and b.datum='1-8-2001' and not exists (select url from urlcounts where >urlcounts.url=b.url) group by a.category; > > The tables websensebycat and netscapereduce do have indices on it. > Netscapereduce has 60020 entries and websensebycat has around 6000 entries. > Urlcounts has 55 entries. The whole thing is running postgres 7.1RC2. > Anybody has a tip to accelerate this. The whole thing is running on a E250 with > 64Bit Solaris7. what does explain command show about your query? -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html