Re: [HACKERS] [GENERAL] Index greater than 8k
On 11/1/06, Teodor Sigaev <[EMAIL PROTECTED]> wrote: [snip] Brain storm method: Develop a dictionary which returns all substring for lexeme, for example for word foobar it will be 'foobar fooba foob foo fo oobar ooba oob oo obar oba ob bar ba ar'. And make GIN functional index over your column (to save disk space). [snip] Time of search in GIN weak depend on number of words (opposite to tsearch2/GiST), but insertion of row may be slow enough With the right folding the number of possible trigrams for ascii text is fairly small.. much smaller than the number of words in used in a large corpus of text so the GIN performance for searches should be pretty good. Real magic would be to teach the regex operator to transparently make use of such an index. ;) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Can't upgrade to 6.4
I just tried to upgrade a production server to 6.4 today. I do a psql -e template1 < dump psql complains about the query being too long and eventually segfaults. I reloaded 6.3.2 and it works find. I also tried the latest snapshot. Finally I tried pg_upgrade which doesn't seem to do anything for me. System is Dual PII running RedHat 5.1+errata and kernel 2.1.125+ac2+other patches. I really need working rules, so any help would be warmly accepted. I can send my dump file if needed. Thanks, Gregory Maxwell
Re: [GENERAL] Generic search
On Fri, 4 Dec 1998, Taral wrote: > >hygea=> explain select * from comuni where nome = 'A%'; > >NOTICE: QUERY PLAN: > >Index Scan using nome_comune_idx on comuni (cost=2.05 size=2 width=84) > >^ > > The question was about LIKE, not =. Because LIKE uses regexp-style matching and > we have no substring index functionality, it cannot use the index. If you're > always matching on the first character, you can do something like fulltextindex > does and use triggers and a second (indexed) table to be able to match on the > first character only. Actually, on a beginging string only search you can do: explain select * from td_prices where manu~'^IBM'; NOTICE: QUERY PLAN: Index Scan on td_prices (cost=682.34 size=1 width=64) Thats using a btree index. :) You can't do a case insensitive search though.
Re: [GENERAL] Why PostgreSQL is better than other commerial softwares?
On Thu, 17 Dec 1998, Bruce Momjian wrote: > Quick mailing list support except they cant tell me why a Dump/Restore from 6.3>6.4 wont work fore some people. :) /*Pardon my typing: Just switched to dvorak*/
RE: [GENERAL] non-case sensitive searches
On Wed, 13 Jan 1999, Jackson, DeJuan wrote: > And remember unless you use full text indexing (which is expensive) you > won't have indexing on that search (for either the LIKE or regex). > -DEJ Actually, if you use case sensitive regex, and anchor the begining (with ^) then it will use btree indexes.
Re: [GENERAL] GIS/GPS Experiences with pgsql?
On Wed, 17 Feb 1999, Peter T Mount wrote: [snip] > If the TIGER/Line data is raster, and each feature (polygon, line, > circle, etc) doesn't exceed the block size, then postgresql should be able > to handle it. [snip] Vector not raster. Right?
Re: [GENERAL] RE: cant connect
/usr/local/pgsql/data/pg_hba.conf On Sun, 11 Apr 1999, Vince Vielhaber wrote: > > redirected to general > - > > > We are trying to use psql on RedHat 5.2 to access a database on another > Redhat 5.2 box runing Postgres (started as user postgres: postmaster > > -D /var/lib/pgsql -B 256 -i -d 3 &.) > The backend start okay and we can access the data on the local machine > runing psql but cannont on another machine on the same net. We tried > adding PAM modules and adding the tcp port to the services files. We > tried to log in as many different users but it just keeps telling us on > the remote and local machine that authenication has failed. What could > we be doing wrong? Any help would be greatly appreciated. > > Joe > > > > > >
[GENERAL] Upgrading from 6.3->6.4.2/6.5b1 possible
AHH! The pgsql v6.3 backend has begun crashing horribly on me since I've added a few more database backed webpages (with messages like 'backend cache invalidation...').. I figured that upgrading to 6.4.2 would solve my problems.. But I can't upgrade! Things I've tried: * Using old pg_dumpall -z > file and psql -e template1 < file (after install and initdb) * Using the pg_dumpall from the new version. * Using pg_upgrade * Rereading the docs dozens of times * having a friend try it. * all of the above with both 6.4.2 and 6.5beta Every time I start loading, it chokes up on the dump output and falls into a constant stream of parse errors and messages like "PQsendQuery() -- query is too long. Maximum length is 8191" Argh!!! I've got websites constantly crashing and coustomers ready to revolt, what can I do???
Re: [GENERAL] Upgrading from 6.3->6.4.2/6.5b1 possible
On Sun, 30 May 1999, Oliver Elphick wrote: > Gregory Maxwell wrote: > > > >AHH! The pgsql v6.3 backend has begun crashing horribly on me since I've > >added a few more database backed webpages (with messages like 'backend > >cache invalidation...').. I figured that upgrading to 6.4.2 would solve my > >problems.. But I can't upgrade! Things I've tried: > > > >* Using old pg_dumpall -z > file and psql -e template1 < file (after > > install and initdb) > >* Using the pg_dumpall from the new version. > >* Using pg_upgrade > >* Rereading the docs dozens of times > >* having a friend try it. > >* all of the above with both 6.4.2 and 6.5beta > > > >Every time I start loading, it chokes up on the dump output and falls into > >a constant stream of parse errors and messages like "PQsendQuery() -- > >query is too long. Maximum length is 8191" > > > >Argh!!! I've got websites constantly crashing and coustomers ready to > >revolt, what can I do??? > > edit the dump file; change every line to an insert command. Run that > so that you can see what it is objecting to. > > 6.3's dump did not cover everything, so you are likely to have to edit the > dump file to make it reloadable. Is there a better way, perhaps even a perl script? My dump is quite large (~1 million rows?) and manual editing is right out. If I'm going to go through that kind of labor I'll probably switch to MySQL (which has a larger web userbase, even though I prefer postgres for it's completeness).. Argh.. Are you aware of any patches to v6.3 that might improve my stability without breaking compatibility? Thanks
Re: [GENERAL] Upgrading from 6.3->6.4.2/6.5b1 possible
I tried the below script (again, going from 6.3->6.4.2 on RedHat 5.2
Linux).. Now I don't get the out of memory errors but it still chokes up
on the syntax.. The back end spits out things like:
ERROR: type name lookup of char16 failed
ERROR: ChangeAcl: class "accounts" not found
ERROR: ChangeAcl: class "accounts" not found
ERROR: COPY command failed. Class accounts does not exist.
ERROR: parser: parse error at or near "0"
ERROR: type name lookup of char8 failed
ERROR: ChangeAcl: class "passwd" not found
ERROR: ChangeAcl: class "passwd" not found
ERROR: type name lookup of char8 failed
ERROR: ChangeAcl: class "page_text" not found
ERROR: ChangeAcl: class "page_text" not found
ERROR: ChangeAcl: class "page_text" not found
ERROR: type name lookup of char8 failed
ERROR: ChangeAcl: class "page_loan_matrix" not found
ERROR: ChangeAcl: class "page_loan_matrix" not found
ERROR: ChangeAcl: class "page_loan_matrix" not found
ERROR: COPY command failed. Class passwd does not exist.
ERROR: parser: parse error at or near "greg"
ERROR: parser: parse error at or near "$100"
FATAL 1: Database chuck1 does not exist in pg_database
Thanks for your help. Everyone here has been very helpful!
On Mon, 31 May 1999, Michael A. Koerber SR wrote:
> I have had trouble on numerous occasions "undumping" a pg_dump. Below is
> a perl script that I use as a pipe for undumping...hope it helps
>
> .pg_undump.
> #!/usr/bin/perl -p
>
> # This script is a pipe that is used to break up PSQL dumps into pieces
> # that are more manageable by the postmaster. If there are too many
> # input lines, the memory used by the transaction block can easily
> # choke the machine.
>
> BEGIN { $MAXLINES = 1000; }
>
> # Does the current input line define the beginning of an input block?
> if (m/^COPY/) {
> # When a copy line is encountered grab the line for later use
> # and turn on the line counter;
> $copyline = $_;
> $cnt = 0;
> }
>
> # Does the current input line define the end of an input block?
> if (m{^\\\.}) {
> # We have just macthed the end of STDIN line. Set counter off
> undef $cnt;
> }
>
> # If we are in an input block and the count is at the max, "flush" the buffer
> # and setup for the next block.
> if (defined($cnt) and ($cnt > $MAXLINES) ) {
> $cnt = 0;
> print '\.', "\n";
> print $copyline;
> }
>
> $cnt++ if defined $cnt;
>
