echo 'SELECT ROW(tst.*) IS NULL FROM tst;' | psql wow | grep 't'
% echo 'SELECT count(*) FROM tst WHERE ROW(tst.*) IS NULL;' | psql wow
SET
count
---
0
(1 row)
What do I do wrong? Version of postgres - today'
ds are null, IS NOT
NULL is true if all the row's fields are not null. The former coding got
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
break;
}
}
break;
So, I can't understand why it's needed at all. First, it's returns length as
typmod, second, it looks like optimization, but I don't believe in significant
benefits... It's a constant coming from q
but not for 8.2 :(
BTW, try
# select plainto_tsquery('foo bar');
plainto_tsquery
-
'foo' & 'bar'
(1 row)
It parses plain text and makes tsquery. Function exists only in 8.2 - some later
we add docs about changes in tsearch2.
--
Teodor Sigaev
_tsquery syntax, just change AND to & and OR to |. :)
So, if you can validate AND/OR then you can validate &/| syntax or validate
AND/OR and change they to tsquery's equivalents.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
e and leave it
only for RETURNS?
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Why do you want to?
Just simplify.
For instance I can see reasons for wanting SETOF on a function's input
parameter (to pass it a whole table or select result in one call).
I see. But Typename is used for table's definition for example. I can't imagine
column "setof
asterMain+0x32a
postgres.exe!main+0x22b
postgres.exe+0x1237
postgres.exe+0x1288
kernel32.dll!RegisterWaitForInputIdle+0x49
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
*** ./src/backend/port/win3
ng problem occurs only on SMP box and
requires several hours to reproduce. So we are in testing now.
Yikes, that's definitely not nice :-)
//Magnus
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http:
:((
Patch doesn't work.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broadcast)---
TIP 1: if posting/reading through U
uggestions?
I intend to commit patch to all affected branches today or tomorrow if there are
no objections or better ideas.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
uot;exists"?
Sure, we can do it, as long as we aren't worried about adding
incompatibilities for existing hstore users.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
Andrew Dunstan wrote:
Teodor Sigaev wrote:
It's possible to create function 'exists' and mention only it in docs.
Good point. Will you do that, or do you want me to?
May I ask you? I'm afraid that there is more incorrectness.
--
Teodor Sigaev
nc_name_keyword { $$ = pstrdup($1); }
;
So call of function named 'exists' should be in quotas:
select "exists"('a=>1','a');
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
break;
}
I'm not sure that is more clean way...
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broadcast)
t of WSASend:
* if success then return 1
* WSAEWOULDBLOCK - continue loop
* SOCKET_ERROR - return 0
]
} else
break;
}
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
seems to me that 1) is good, but 2) makes some strange, unpredictable
result...
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broadcast
You're running in auto-commit, mode. An implicit commit happens after
this statement. Which clears the table.
> Looks right to me.
Oops, I see
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
W
le.
Also, will this work with default_with_oids = false? (When the schema
is initialised.)
All pg_ts_* tables are created with 'with oids' option.
Chris
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
--
Teodor
it's a bug. I'll check it.
It probably is then - although I do use a persistent connection pool,
but I wouldn't have thought that'd use more than a new connection every
once in a while?
Chris
--
Teodor Sigaev
2.so on my 7.4 production server?
Chris
Teodor Sigaev wrote:
I found several unpleasant blot in comparing functions and commit
changes to 7.4, 8.0 and head. Pls check (it need just to recompile .so
file)
Christopher Kings-Lynne wrote:
It's cached. This select should run only one time per
->dict_id )
return 0;
return ( ((DictInfo *) a)->dict_id < ((DictInfo *) b)->dict_id ) ? -1 :
1;
}
It works for small dict_id ( dict_id has type Oid ), but it was errnous for
dict_id>2^31.
Christopher Kings-Lynne wrote:
Hi Teodor,
What exactly did you fix here?
Chri
8.0. I don't have enough knowledge
of the GIST code to figure it out quickly, though.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
--
Teodor Sigaev
of supported operations by GiST is indefinite unlike, for
example, btree which supported only five: <, <=, =, =>, >.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://ww
ntrib modules needs 32. Will be
correct to use only half-value? I am afraid number of collisions will be more...
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
--
is it possible to reproduce
desired behaviour?
I tried to kill(getpid(), SIGKILL) in a middle of insertion (inside gist.c), but
postgres doesn't try ro restore aborted transaction
Thank you.
--
Teodor Sigaev E-mail: [EMAIL
such tuples will be little by
little changed to correct. Defect of this scheme will be a possible performance
degradation after crash recovery. In other hand, it's possible to add recreation
such tuples to vacuum code (in gistbulkdelete).
--
Teodor Sigaev
iST doesn't use offset on inner pages (on leaf pages, ItemPointerData points to
heap tuple), it always equal FirstOffsetNumber. So I can use offset to indicate
correctness of tuple's key...
--
Teodor Sigaev
I find this code pretty unreadable, but possibly lines 315 and 316
should be manipulating newpage->buffer instead of buffer?
You are right, I'll commit changes some later with a lot of additional work..
--
Teodor Sigaev E-mail: [EMAIL P
LWLockRelease(WALInsertLock);
return RecPtr;
}
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broadcast)---
TIP
--- there's no need for an extra call.
You partially right, I don't read it with care chaper 10.1 last paragraph :(
To alleviate the traffic on this high-frequency counter (LSN - teodor),
descending operations can memorize the node's LSN instead.
So, value of global LSN isn't neede
move rtree_gist into the core --- but the case for
it would be stronger if it worked correctly ...
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(e
We can make r-tree as contrib module and then we will have example of index in
contrib...
By integrating the opclasses needed to replace R-tree, we can start
down the path to deprecating and eventually removing R-tree.
--
Teodor Sigaev E-mail: [EMAIL
eneralizing ''Search'' in Generalized Search Trees", 1997, Paul M. Aoki,
http://www.sai.msu.su/~megera/postgres/gist/papers/csd-97-950.pdf
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
basic picksplit algoritm
1 simple sorting for ordered domain( btree_gist, ltree )
2 several variations of Guttmans algorithm (tsearch2, intarray, seg, cube)
3 linear picksplit for rtree_gist
(http://www.sai.msu.su/~megera/postgres/gist/papers/nsplitLN.ps.gz).
-
FYI, compress and decompress methods may be trivial.
For GiST you still need 7 support functions + the operator function,
some of which aren't exactly simple to implement, the picksplit for
instance.
--
Teodor Sigaev E-mail: [EMAIL PROT
- simple wrapper about concur.pl which reinit db, makes db and table.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broadcast)---
pport multibyte encoding and has problems with UTF :(.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broadcast)---
TIP 5: Have you ch
e are unique the the later keys will not be compared ;)
2. About rtree interface: it's possible to write GiST-RTree layer compatibility
interface. User's interface will just copied from RTree, and layer will
translate it to GiST interface.
--
Teodor Sigaev
Will look at it later (after feature freeze), if you don't find
the cause beforehand.
It's definitly bug in a vaccum code, I got the same trap without any GiST
indexes (to reproduce, just comment out 'create index' command in my script).
--
Teodor Sigaev
Sorry, fixed.
Qingqing Zhou wrote:
"Teodor Sigaev" <[EMAIL PROTECTED]> writes
concur.pl - generator of SQL statements
retrieving it is forbidden ...
Regards,
Qingqing
---(end of broadcast)---
TIP 7: don't forget
("!((*curpage)->offsets_used == num_tuples)", File:
"vacuum.c", Line: 2766)
LOG: server process (PID 15847) was terminated by signal 6
It's definitly bug in a vaccum code, I got the same trap without any GiST
indexes (to reproduce, just comment out 'create index' co
s
in the contrib/rtree_gist) ?
8.1 pgsql has short lived memory context during index creation which resets
after every insert. 8.0 hasn't this feature, so user-defined function (GiST
interfface) should carefully clean memory.
--
Teodor Sigaev E-mail: [EM
In other words ... if you can test this ... HELP!!!
I'll run tests.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broa
SQL
statements without any failure. Tests runs on two boxes: PIII/1133MHz adn Quad
Xeon/500MHz and works with four threads.
Further I'm going to increase concurrency up to 12 parallel threads.
PS GiST passed this tests too.
--
Teodor Sigaev E-mail: [
uarantee that operator < is really 'less-than' one. Is it?
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
IGN(VARDATA(evec)),
but I do not know what places need to change to support this.
Its only union and picksplit user-defined methods in contrib modules.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
---(end of broadcast)
t32 n, GISTENTRY *entryvec, int *size)
Datum picksplit(int32 n, GISTENTRY *entryvec, GIST_SPLITVEC *v)
It seems to me that first case is clearer. Of course, I change all contrib
modules to new interface.
What do you think?
--
Teodor Sigaev E-mail:
Ok, I just commited changes, pls, check it on HPPA.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail
-83, +007(095)939-23-83
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
---(end of broadcast)-------
TIP 7: don't forget to increase your free space map setti
--------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
I tried running 'make installcheck' in contrib just now, and didn't
get past btree_gist :-(
Fix and test on Alpha box.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
---(end of broadcast)---
TIP 8: explain
--
Hannu
---(end of broadcast)---
TIP 8: explain analyze is your friend
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
ion
-
PostgreSQL 7.4 on i386-unknown-freebsd5.1, compiled by GCC gcc (GCC) 3.2.2
[FreeBSD] 20030205 (release)
(1 row)
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: if posting/reading through Use
runtime: 0.047 ms
(2 rows)
Several thousands?
Tom Lane wrote:
Teodor Sigaev <[EMAIL PROTECTED]> writes:
Explain analyze takes 3 times more time for execution. Why?
Measurement overhead. It would seem your platform has a particularly
slow version of gettimeofday() though ... I've ne
one row... If you mean rows in Seq Scan than
gettimeofday was called (52936+1)*2. Huge value :(
Tom Lane wrote:
Teodor Sigaev <[EMAIL PROTECTED]> writes:
How many times is gettimeofday called?
Twice per plan node visit, if you are doing EXPLAIN ANALYZE. (The
number of "visits&qu
h :) - word parser
Whats bad:
1 update - too slow, some more efficient way is a bulk upload.
2 If word is frequent then query with 'IN (select * from func()) may works slow...
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
---(end of broadcas
dictionary, which can define language and normalize word...
Look at default_russian configuration of tsearch2 and read more about tsearch2.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
---(end of broadcast)---
TIP 1
search v2 has stat function:
select * from stat('select TSVECTOR from TABLE') order by ndoc desc, nentry
desc, word;
Where TSVECTOR is name of column of tsvector type and TABLE is a table with
that column.
Warn: it works very slow.
--
Teodor Sigaev
primary key (lexeme, textdate)
);
which would partition the invidx table on textdate (or some other
suitable datum)
2 If word is frequent then query with 'IN (select * from func()) may works slow...
if it is often too slow then creating a temp table and doing a plain
join
signature as described above.
internal tuple contains ored (super-imposed) signatures of childs.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: you can get off all lists at
manage it?
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
umn
indexes
How can I solve this problem ?
Is it a limitation of PostgreSQL or the R-Tree concept ?
Thank you in advance.
Márcio Caetano.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map setti
ed to add U lock in lock manager of pgsql.
So, I still thinking. If you has other thought/idea, pls, don't be quiet.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
the bottom half might be messed up too. So we are failing to exploit
the full intended "sign" space, which presumably is costing something
in index efficiency.
You are absolutly right. My fault.
It looks to me like the values calculated by this routine end up on
disk, and therefore we ca
Oh, good, that makes it easy. Do you want to apply the fix or shall I?
I will have normal access to Internet only on Monday. It can easy wait
for me :)
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desir
ite(15);
\x09 is a '\t'. Is it now prohibited(non-printable) symbol?
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broadcast)
;&
(Node*)((header->context)))->type) == T_AllocSetContext))))", File: "mcxt.c", Line: 612)
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(en
x insertion)
Authors:
Teodor Sigaev <[EMAIL PROTECTED]>
Oleg Bartunov
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broadcast)--
es for all types (no programming, just many changes of the catalog).
Distant future:
* Replace entry btree to something like the GiST
* Add multicolumn support
* Optimize insert operation (background index insertion)
Authors: All work was done by Teodor Sigaev ([EMAIL PROTECTE
s)
SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname, p3.opcname
FROM pg_amop AS p1, pg_operator AS p2, pg_opclass AS p3
==
-------(end of broadcast)---
TIP 5: d
a big
enough patch up to date...
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broadcast)---
TIP 5: don't forget to
inters to heap tuple during build/insert,
and during bulkdelete we count number of deleted and leaved itempointers. So,
N[before bulkdelete] == N[after bulkdelete] + N[deleted]
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
The ideal thing would be for GIN to return a count of the number of
distinct heap tuples referenced by the entries in the index, but I
suppose that would be impractical for VACUUM to compute.
Of course, in this case we should collect heap pointers in memory..
--
Teodor Sigaev
other. Cluster command
on GIN index should do nothing. May be, it will be cleaner to add indclustered
column to pg_am.
3) Return to WAL problem with GiST
4) work on gincostesimate and, possibly, GIN's opclasses costestimate tweak...
Including num_tuples issue
--
Teodor Sigae
ant of node. ltree uses B-tree like
structure
* R-tree - contains operation
In any case, clustering can prevent from chaotic seeks on disk.
So, two columns about clustering?
amclustered
amclusterable
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
nothing, but optimizer/planner takes clustering into
consideration for query planning.
We can use only amclustered, but in this case we can't forbid to cluster table
on any index. Just current situation.
--
Teodor
"Takes clustering into account" means nothing. We don't need that. Any
such consideration would be handled by the AM-specific amcostestimate
function.
Ok, I see. Sorry for misunderstanding. I thought that planner use that.
--
Teodor Sigaev
you. Sincerely yours.
Fernando Esparza
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
dex), so it should be fast
as possible for sorted arrays. As I remember ordered array is a worst case for
qsort(). May be, it will be better choice to use mergesort.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
eplay.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
ably means the server terminated abnormally
before or while processing the request.
A conexão com servidor foi perdida. Tentando reiniciar: Falhou.
!> \q
Regards,
Rodrigo Hjort
http://icewall.org/~hjort
--
Teodor Sigaev
Teodor Sigaev wrote:
Sorry, it isn't mentioned on page, but this example of code working only
with before 8.1 versions. In 8.1 interface to dictionary was changed.
Try attached dict_tmpl.c
2Oleg: place file on site, pls
--
Teodor Sigaev E-mail: [
Since we are on the topic, is there a timeline/plans for openfts being
brought into core? If not, I'll continue my work on bringing it into
Gentoo Portage.
OpenFTS never, but tsearch2 is possible. But it requires enough work to do, so I
have doubt that it will be done in 8.2...
--
T
(with formed before)
unions with suggested by pickSplit() by maximizing penalty between left and
right keys.
Also, I plan to split GIST_SPLITVEC to 2 structures: one of its will be argument
for pickSplit() and another will use internally in GiST core. Now GIST_SPLITVEC
contains a lot of field tha
compression. I am
afraid that is too big size...
What are opinions?
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broadcast
800kb, each ispell dictionaries will takes about 0.5-2M. All sizes are
Sorry, withOUT compression...
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of
e tsearch2 configuration.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broadcast)---
TIP 6: explain analyze is your friend
% cd PGSQL/contrib/tsearch2
% make LANG=norwegian
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broadcast)---
TIP 4: Have y
I'll place contrib module which will make all Snowball stemmers. Right
now I'm working on supporting OpenOffice's dictionaries in tsearch2, so
it will be simple to add it to packaging system.
done, http://archives.postgresql.org/pgsql-committers/2006-06/msg00112.php
-
Further I'm going to increase concurrency up to 12 parallel threads.
All is ok, test is passed with approximatly 40 millions statements
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.siga
cur.sh
Those scripts was wrote to test GiST concurrency.
I suspect it's needed to make some changes in generator to increase number of
updates and vacuums for your goal.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
http://archives.postgresql.org/pgsql-patches/2005-08/msg00304.php
Could you perhaps test this patch as well, while you already have a
setup for testing parallel vacuums under big loads ?
I didn't find any problem with your patch during testing with 1e8 statements...
--
Teodor S
Finded problem in GiST isn't too simple to resolve. I'm working on it. The
problem is about update query...
Tom Lane wrote:
Teodor Sigaev <[EMAIL PROTECTED]> writes:
http://www.sigaev.ru/gist/concur.pl
http://www.sigaev.ru/gist/concur.sh
BTW, these scripts seem to indicat
Fixed in 8.0, 7.4 and 7.3 branches.
Tom Lane wrote:
Teodor Sigaev <[EMAIL PROTECTED]> writes:
http://www.sigaev.ru/gist/concur.pl
http://www.sigaev.ru/gist/concur.sh
BTW, these scripts seem to indicate that there's a GIST or
contrib/intarray problem in the 8.0 branch. I was tr
1,2,3}';
select a from wow where a @ '{1,2,3}' and not a @ '{101}';
After update query select must not find any rows, but it did. The problem was in
GiST code and so any GiST idexes was affected.
Can you say more about your trouble?
--
Teodor Sigaev
ble something like to:
update tbl set ltreefield=... where ltreefield ...;
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broadcast)--
GUI apps like phpPgAdmin...
Chris
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev
ine 5 at select into variables
After reconnecting to database all is ok. Is it supposed behaviour?
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---
Seq Scan on book (cost=0.00..13.62 rows=1 width=4)
Filter: (fts @@ '''foo'''::tsquery)
Why planner suppose that t 'table' will return 1000 rows? Obviosly that function
returns only one value because of itsn'
701 - 800 of 830 matches
Mail list logo
