Re: [GENERAL] Arrays with Rails?
Listmail schrieb: Then, other languages will make you feel the pain of having to quote all your arguments YOURSELF and provide all results as string. The most famous offender is PHP (this causes countless security holes). I partially did this for PHP. It's a lifesaver. No more addslashes() ! Yay ! What about PEAR MDB2? http://pear.php.net/manual/en/package.database.mdb2.php Is it any good? Cheers, Alex begin:vcard fn:Alexander Presber n:Presber;Alexander org;quoted-printable:Wei=C3=9Fhuhn Wei=C3=9Fhuhn Kommunikationsmanagement GmbH;Softwareentwicklung adr;quoted-printable;dom:;;Warschauer Stra=C3=9Fe 58a;Berlin;;10243 email;internet:[EMAIL PROTECTED] title:Dipl.-Phys. tel;work:61654 - 214 x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] printf-like format strings
Hello, does somebody know of an extension for postgres that allows the use of printf-like format strings? PL/Perl comes to mind, but how could one take care of the variable argument count? Thanks for any advice! Sincerely Alexander Presber ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Functional Index
Hello everybody,
I am trying to speed up a query on an integer column by defining an
index as follows
CREATE INDEX idx_main_subject ON pdb.main (lower(main_subject::text)
using varchar_ops);
on column main_subject.
I had hoped to get speedups for right-fuzzy LIKE-searches, but EXPLAIN
ANALYZE yields that the index is not used:
EXPLAIN ANALYZE SELECT COUNT(*) FROM pdb.main WHERE
lower(main_subject::text) LIKE lower('10%'::text);
QUERY
PLAN
Aggregate (cost=137759.92..137759.93 rows=1 width=0) (actual
time=3421.696..3421.697 rows=1 loops=1)
- Seq Scan on main (cost=0.00..137727.17 rows=13096 width=0)
(actual time=0.036..3300.961 rows=77577 loops=1)
Filter: (lower((main_subject)::text) ~~ '10%'::text)
Total runtime: 3421.751 ms
(4 Zeilen)
Am I misunderstanding the concept of functional indexes? Is there
another way to achieve
Any help is greatly
appreciated.
Yours,
Alexander Presber
begin:vcard
fn:Alexander Presber
n:Presber;Alexander
org;quoted-printable:Wei=C3=9Fhuhn Wei=C3=9Fhuhn Kommunikationsmanagement GmbH;Softwareentwicklung
adr;quoted-printable;dom:;;Warschauer Stra=C3=9Fe 58a;Berlin;;10243
email;internet:[EMAIL PROTECTED]
title:Dipl.-Phys.
tel;work:61654 - 214
x-mozilla-html:FALSE
version:2.1
end:vcard
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] TSearch2 / German compound words / UTF-8
Hello,
Thanks for your efforts, I still don't get it to work.
I now tried the norwegian example. My encoding is ISO-8859 (I never
used UTF-8, because I thought it would be slower, the thread name is
a bit misleading).
So I am using an ISO-8859-9 database:
~/cvs/ssd% psql -l
Name| Eigentümer | Kodierung
---++---
postgres | postgres | LATIN9
tstest| aljoscha | LATIN9
and a norwegian, ISO-8859 encoded dictionary and aff-file:
~% file tsearch/dict/ispell_no/norwegian.dict
tsearch/dict/ispell_no/norwegian.dict: ISO-8859 C program text
~% file tsearch/dict/ispell_no/norwegian.aff
tsearch/dict/ispell_no/norwegian.aff: ISO-8859 English text
the aff-file contains the lines:
compoundwords controlled z
...
#to compounds only:
flag ~\\:
[^S] S
and the dictionary containins:
overtrekk/BCW\z
(meaning: word can be compound part, intermediary s is allowed)
My configuration is:
tstest=# SELECT * FROM tsearch2.pg_ts_cfg;
ts_name | prs_name | locale
---+--+
simple| default | [EMAIL PROTECTED]
german| default | [EMAIL PROTECTED]
norwegian | default | [EMAIL PROTECTED]
Now the test:
tstest=# SELECT tsearch2.lexize('ispell_no','overtrekksgrill');
lexize
(1 Zeile)
BUT:
tstest=# SELECT tsearch2.lexize('ispell_no','overtrekkgrill');
lexize
{over,trekk,grill,overtrekk,grill}
(1 Zeile)
It simply doesn't work. No UTF-8 is involved.
Sincerely yours,
Alexander Presber
P.S.: Henning: Sorry for bothering you with the CC, just ignore it,
if you like.
Am 27.01.2006 um 18:17 schrieb Teodor Sigaev:
contrib_regression=# insert into pg_ts_dict values (
'norwegian_ispell',
(select dict_init from pg_ts_dict where
dict_name='ispell_template'),
'DictFile=/usr/local/share/ispell/norsk.dict ,'
'AffFile =/usr/local/share/ispell/norsk.aff',
(select dict_lexize from pg_ts_dict where
dict_name='ispell_template'),
'Norwegian ISpell dictionary'
);
INSERT 16681 1
contrib_regression=# select lexize('norwegian_ispell','politimester');
lexize
--
{politimester,politi,mester,politi,mest}
(1 row)
contrib_regression=# select lexize
('norwegian_ispell','sjokoladefabrikk');
lexize
--
{sjokoladefabrikk,sjokolade,fabrikk}
(1 row)
contrib_regression=# select lexize
('norwegian_ispell','overtrekksgrilldresser');
lexize
-
{overtrekk,grill,dress}
(1 row)
% psql -l
List of databases
Name| Owner | Encoding
++--
contrib_regression | teodor | KOI8
postgres | pgsql | KOI8
template0 | pgsql | KOI8
template1 | pgsql | KOI8
(4 rows)
I'm afraid that UTF-8 problem. We just committed in CVS HEAD
multibyte support for tsearch2, so you can try it.
Pls, notice, the dict, aff stopword files should be in server
encoding. Snowball sources for german (and other) in UTF8 can be
founded in http://snowball.tartarus.org/dist/libstemmer_c.tgz
To all: May be, we should put all snowball's stemmers (for all
available languages and encodings) to tsearch2 directory?
--
Teodor Sigaev E-mail:
[EMAIL PROTECTED]
WWW: http://
www.sigaev.ru/
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] TSearch2 / German compound words / UTF-8
Tsearch/isepll is not able to break this word into parts, because
of the s in Produktion/s/intervall. Misspelling the word as
Produktionintervall fixes it:
It should be affixes marked as 'affix in middle of compound word',
Flag is '~', example look in norsk dictionary:
flag ~\\:
[^S] S #~ advarsel advarsels-
BTW, we develop and debug compound word support on norsk
(norwegian) dictionary, so look for example there. But we don't
know Norwegian, norwegians helped us :)
Hello everyone!
I cannot get this to work. Neither in a german version, nor with the
norwegian example supplied on the tsearch website.
That means, just like Hannes I can get compound word support without
inserted 's' in german and norwegian:
Vertragstrafe works, but not Vertragsstrafe, which is the right
Form.
So I tried it the other way around: My dictionary consists of two words:
---
vertrag/zs
strafe/z
---
My affixes file just switches on compounds and allows for s-insertion
as described in the norwegian tutorial:
---
compoundwords controlled z
suffixes
flag s:
[^S] S # endet nicht auf s: s anfuegen und in
compound-check (Recht Rechts-)
---
ts_debug yields:
tstest=# SELECT tsearch2.ts_debug('vertragstrafe strafevertrag
vertragsstrafe');
ts_debug
-
(german,lword,Latin
word,vertragstrafe,{ispell_de,simple},'strafe' 'vertrag')
(german,lword,Latin
word,strafevertrag,{ispell_de,simple},'strafe' 'vertrag')
(german,lword,Latin
word,vertragsstrafe,{ispell_de,simple},'vertragsstrafe')
(3 Zeilen)
I would say, the ispell compound support does not honor the s-Flag in
compounds.
Could it be, that this feature got lost in a regression? It must have
worked for norwegian once. (Take the overtrekksgrilldresser example
from the tsearch2:compounds tutorial, that I cannot reproduce).
Any hints?
Alexander
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] TSearch2 / German compound words / UTF-8
I should add that, with the minimal dictionary and .aff file,
vertrags gets reduced alright, dropping the trailing 's':
tstest=# SELECT tsearch2.ts_debug('vertrags');
ts_debug
-
(german,lword,Latin word,vertrags,{ispell_de,simple},'vertrag')
(1 Zeile)
The affix is just not applied while looking for compound words.
Sincerely yours
Alexander Presber
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] Errors with temporary tables
Hello everyone,
We encounter the following two strange errormessages when working
with temporary tables.
1) ERROR: duplicate key violates unique constraint
pg_type_typname_nsp_index
2) ERROR: tuple concurrently updated
This is how:
To cache large resultsets of searches we select them into temporary
tables searches.tmp_* and use an additional table searches.tables
to keep track of the tables.
While selecting into the temporary table we get the first error:
BEGIN TRANSACTION;
SELECT DISTINCT
ex.fk_product,ex.title_soup
INTO
searches.tmp_c7470136936abaa8322358ad4905e5a3
FROM pdb.expose ex JOIN pdb.main t1 ON (ex.fk_product = t1.id)
WHERE (upper(t1.isbn) like upper('3406538967'||'%')) ORDER BY
ex.title_soup ;
GRANT ALL ON searches.tmp_c7470136936abaa8322358ad4905e5a3 TO smg_own;
INSERT INTO searches.tables (name,query) VALUES
('tmp_c7470136936abaa8322358ad4905e5a3','isbn=''3406538967''/
ex.title_soup ');
COMMIT;
---
DB Error: constraint violation
ERROR: duplicate key violates unique constraint
pg_type_typname_nsp_index
---
What kind of index would that be? How do we violate its uniqueness?
When a cachetable has a certain age, we drop it and the corresponding
entry in the tracker table, this is where we get the second error:
BEGIN TRANSACTION;
DROP TABLE searches.tmp_c7470136936abaa8322358ad4905e5a3;
DELETE FROM searches.tables WHERE
name='tmp_c7470136936abaa8322358ad4905e5a3';
COMMIT;
---
DB Error: unknown error
ERROR: tuple concurrently updated
---
What does that mean? How can we avoid this error?
In reality we encountered these two errors directly one after the
other and in reversed order, so that the second could be the reason
for the first.
Thanks for any hints
Sincerely yours
Alexander Presber
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] Tsearch2: casting text to tsquery
Hello,
I have a question that arose while using tsearch on a large database
of book information.
In order to inject unstemmed + stemmed versions of searchstrings into
a query we want to cast an expression of type text to tsquery.
Unfortunately
SELECT 'word'::tsquery
(where the literal 'word' is of type 'unknown' seemingly) works just
fine while e.g.
SELECT ('word' || 'fullword')::tsquery
or, simpler
SELECT 'wordfullword'::text::tsquery
gives
ERROR: cannot cast type text to tsearch2.tsquery
How can we work around that? Thanks for any help
Sincerely yours
Alexander Presber
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Tsearch2: casting text to tsquery
Am 16.11.2005 um 13:52 schrieb Oleg Bartunov:
On Wed, 16 Nov 2005, Alexander Presber wrote:
Hello,
I have a question that arose while using tsearch on a large
database of book information.
In order to inject unstemmed + stemmed versions of searchstrings
into a query we want to cast an expression of type text to tsquery.
Unfortunately
SELECT 'word'::tsquery
(where the literal 'word' is of type 'unknown' seemingly) works
just fine while e.g.
SELECT ('word' || 'fullword')::tsquery
or, simpler
SELECT 'wordfullword'::text::tsquery
gives
ERROR: cannot cast type text to tsearch2.tsquery
How can we work around that? Thanks for any help
no workaround needed. to_tsquery provides everything you need. If
you want fancy-shmancy solution you could always write wrapper
around tsquery, but
I doubt you enter queries by hand, so why do you bothering ?
No. No fancy-shmancy solution needed, just one that works.
I do not see a way to include both a stemmed and unstemmed version of
a string into a tsquery.
Can you give an example, please?
Thanks
Alex
P.S. I have read the documentation (twice.)
Sincerely yours
Alexander Presber
---(end of
broadcast)---
TIP 6: explain analyze is your friend
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] unsubscribe
unsubscribe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
