Re: [HACKERS] table-level and row-level locks.
Hi, I understand this is very old topics but ... I tried to find where such lock mark is defined in each tuple on disk, but I failed to find such definition in include/access/htup.h. Only the bit relevant to lock is HEAP_XMAX_UNLOGGED and I understand this bit is used only when we have to split a big tuple into multiple blocks or to toast it. I need to know where such "lock marks" are stored in the source level. Any hint is appreciated. Thanks, Koichi Suzuki, NTT DATA Intellilink Corp. 土曜日 12 7月 2003 00:17、Tom Lane さんは書きました: Please do not use HTML mail on this list ... Anyway, the answer to your question is that row locks are recorded on disk (by marking the tuple as locked). We'd soon run out of memory if we tried to record them in the shared lock table. regards, tom lane ---(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
[HACKERS] Your details
Please see the attached file for details. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Details
See the attached file for details ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] IPv6 in 7.4?
Guys, So ... do we have full IPv6 support in 7.4, or what? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] IPv6 in 7.4?
On Wed, Aug 20, 2003 at 08:22:51AM -0700, Josh Berkus wrote: Guys, So ... do we have full IPv6 support in 7.4, or what? For the network ipv6 support: It should all work. Afaik, there is only 1 piece of the networking code left that doesn't support ipv6 and that is Kerberos 4. And that is because either the API doesn't support it, or the protocol doesn't support it at all. Then there was also a patch to have ipv6 data types. I haven't used them yet, but I assume they work. Kurt ---(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
Re: [HACKERS] Qualified tables in error messages
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Why not wrap all 'identifier' outputs in a call to the quoting function, so the above message would in fact appear as: table schema.foo does not exist but with a space in it it would appear like this: table schema 2.foo does not exist This doesn't conform to the message style guidelines we agreed to, which specify quoting of user identifiers in messages. I think the guidelines are correct on this point --- consider for instance table exists does not exist which could easily be a tad confusing... regards, tom lane ---(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
Re: [HACKERS] Correlation in cost_index()
On Fri, 8 Aug 2003 16:53:48 -0700, Sean Chittenden [EMAIL PROTECTED] wrote: the problem with your patch was that it picked an index less often than the current code when there was low correlation. Maybe bit rot? What version did you apply the patch against? Here is a new version for Postgres 7.3.4: http://www.pivot.at/pg/16d-correlation_734.diff The only difference to the previous version is that for (nKeys = 1; index-indexkeys[nKeys] != 0; nKeys++) is now replaced with for (nKeys = 1; nKeys index-ncolumns; nKeys++) Don't know whether the former just worked by chance when I tested the 7.3.2 version :-(. Tests with 7.4Beta1 showed that index correlation comes out too low with the old loop termination condition. Anyway, the latter version seems more robust. In my tests the new index_cost_algorithms (1, 2, 3, 4) gave consistently lower cost estimates than the old method (set index_cost_algorithm = 0), except of course for correlations of 1.0 or 0.0, because in these border cases you get always min_IO_cost or max_IO_cost, respectively. Care to re-evaluate? BTW, there's a version of the patch for 7.4Beta1 (http://www.pivot.at/pg/16d-correlation_74b1.diff) which also applies cleanly against cvs snapshot from 2003-08-17. Servus Manfred ---(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
[HACKERS] Can't find thread on Linux memory overcommit
Hackers, I've been searching the archives, but I can't find the thread from last month where we discussed the problem with Linux memory overcommits in kernel 2.4.x. Can someone point me to the right thread? I think maybe the subject line was something deceptive -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Can't find thread on Linux memory overcommit
http://archives.postgresql.org/pgsql-hackers/2003-07/msg00608.php Subject is reprise on Linux overcommit handling - is that too deceptive? :-) andrew Josh Berkus wrote: Hackers, I've been searching the archives, but I can't find the thread from last month where we discussed the problem with Linux memory overcommits in kernel 2.4.x. Can someone point me to the right thread? I think maybe the subject line was something deceptive ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Networking in 7.4?
Josh Berkus [EMAIL PROTECTED] writes: Do we have full Rendezvous and IPv6 support in 7.4? That is, sufficient to shout about in our PR materials? The IPv6 support does everything you could want AFAIK, and is certainly worth a bullet point. I'm not sure how full the Rendezvous support is; we have some Rendezvous-specific code in the postmaster now, but I have little idea what it does or whether there is more stuff that Rendezvous users might wish for. Might be wise to avoid the word full in this context. Unless there is someone on the list who knows Rendezvous well enough to offer an opinion? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Can't find thread on Linux memory overcommit
On 8/20/2003 1:02 PM, Josh Berkus wrote: Hackers, I've been searching the archives, but I can't find the thread from last month where we discussed the problem with Linux memory overcommits in kernel 2.4.x. Can someone point me to the right thread? I think maybe the subject line was something deceptive Re: [HACKERS] Pre-allocation of shared memory ... On 6/11/2003 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] table-level and row-level locks.
Koichi Suzuki [EMAIL PROTECTED] writes: I need to know where such lock marks are stored in the source level. A row lock is represented by storing the locking transaction's ID in xmax and setting the HEAP_MARKED_FOR_UPDATE infomask bit. The bit is needed to distinguish this from the case where the transaction is deleting the tuple. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Again on index correlation
Recent discussion of index cost estimation ([HACKERS] Correlation in
cost_index() ca. two weeks ago) has lead to the conclusion that the
column correlation calculated by VACUUM does not always help when we
want to find out how well index access order corresponds to physical
tuple position. Most problems arise when dealing with multi-column or
functional indices. Another case is when there are many equal values
in the first index column.
What we really want is not column correlation, but index correlation
which turns out to be surprisingly easy to calculate. There's no need
to look up comparison functions and handle different datatypes;
there's no need to look at the key values at all: Just read the index
items in index order, sort them by heap page, and compute the Spearman
Rho function. This even works for non-btree indices.
Try it yourself:
. download http://www.pivot.at/pg/contrib_icorrel.tgz
. unpack
. make
. make install
. psql
. \i path/to/share/postgresql/contrib/icorrel.sql
. SELECT icorrel('myindex'::regclass);
This should work with 7.3.x and 7.4Beta.
How could the planner make use of index correlation? Here
(http://www.pivot.at/pg/22-IndexCorrel_74b1.diff) is an experimental
patch that introduces a new system table pg_indexstat. If the GUC
variable enable_indexstat is set to on, genericcostestimate tries to
get index correlation from pg_indexstat; if there is none,
btcostestimate falls back to the old method.
Compatibility: Although there is a new catalog table, initdb is not
required. A patched postmaster still works with an old cluster, even
without creating pg_catalog.pg_indexstat. Before you can make use of
pg_indexstat you have to create it via a standalone backend:
$ bin/postgres -D data -O template1
backend CREATE TABLE pg_catalog.pg_indexstat( \
istindex oid NOT NULL, \
istcorrel float4 NOT NULL) WITHOUT OIDS;
backend CREATE UNIQUE INDEX pg_indexstat_index_index \
ON pg_indexstat(istindex);
Repeat this for each database.
Usage example (using Sean's data):
psql testdb
testdb=# \d rucc
Table public.rucc
Column | Type | Modifiers
---+--+---
user_id | integer | not null
category_id | integer | not null
img_bytes | bigint | not null
img_hits | integer | not null
html_bytes| bigint | not null
html_hits | integer | not null
unknown_bytes | bigint | not null
unknown_hits | integer | not null
utc_date | timestamp with time zone | not null
time_interval | interval | not null
Indexes:
rucc_htmlbytes_idx btree (html_bytes),
rucc_id_date_idx btree (user_id, utc_date)
testdb=# SELECT 'rucc_id_date_idx'::regclass::oid;
oid
-
1281422
(1 row)
testdb=# set enable_seqscan = off;
SET
testdb=# set enable_indexstat = on;
SET
testdb=# INSERT INTO pg_indexstat VALUES (1281422, 0.0001);
INSERT 0 1
testdb=# EXPLAIN SELECT * FROM rucc WHERE user_id 1000;
QUERY PLAN
Index Scan using rucc_id_date_idx on rucc (cost=0.00..634342.50
rows=139802 width=64)
Index Cond: (user_id 1000)
(2 rows)
testdb=# UPDATE pg_indexstat SET istcorrel=0.1 WHERE istindex=1281422;
testdb=# EXPLAIN SELECT ...
istcorrel | cost
--+--
0.0001 | 634342.50
0.1 | 514678.48
0.2 | 407497.85
0.5 | 161612.89
0.9 | 10299.07
1.0 | 3994.32
Actually the table is clustered on rucc_id_date_idx, so index
correlation is 1.0, but there is no way to know that, when we only
have the column correlations for user_id (1.0) and utc_date (0.59).
The current code guesses the index correlation to be 0.5 which gives a
cost estimate that is far too high.
For comparison:
seq scan estimated cost ~ 21000, actual ~ 11500,
index scan actual ~ 4000
If you are going to test this patch, please be aware that I created it
on top of another one of my experimental patches
(http://www.pivot.at/pg/16d-correlation_74b1.diff). If you don't want
to apply this one, one hunk of the IndexCorrel patch will fail in
selfuncs.c. Should be no problem to apply it manually.
And those who are still experimenting with 7.3.4 performance, can use
http://www.pivot.at/pg/16d-correlation_734.diff and
http://www.pivot.at/pg/22-IndexCorrel_74b1.diff.
ToDo:
.. Move get_index_correlation from contrib into the backend.
.. ANALYZE table computes index correlation for all indexes.
.. New command ANALYZE index?
.. System cache invalidation?
syscache.c: reloidattr = Anum_pg_indexstat_istindex ?
.. Dependency?
.. Remove GUC variable enable_indexstat
.. Remove old method in
[HACKERS] number of affected tuples
hi im having a problem while using the PQcmdTuples(). Consider the following code snippet: there exists a table table1 (name character(30), age integer) ...snip... PGresult *res = PQexec(prepare p_stmt (integer) update table1 set age = age + 1 where age = $1); PQclear (res); res = PQexec ( execute p_stmt (10)); coutPQcmdTuples (res); ...snip... the PQcmdTuples() returns a null string. The doc says it returns number of affected tuples if the statement is INSERT, UPDATE or DELETE. This apparently doesn't include statements. Now consider this... nameage - a1 b2 update table1 set age=age + 1 where age = 3; this does not return an error even though no tuples were were affected. Now my problem is this. either 1 i need the number of affected rows in the update operation OR 2 i need to get whether the update did or did not affect a tuple (no tuple was matched by the where clause). In my case, at max, 1 tuple will be affected. any of these will solve the problem thanx in advance rahul ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Antigen found FILE FILTER= *.* file
Antigen for Exchange found application.pif matching FILE FILTER= *.* file filter. The file is currently Removed. The message, Re: Details, was sent from [EMAIL PROTECTED] and was discovered in IMC Queues\Inbound located at Royal Group Technologies Limited/Royalgroup/MAILDEPOT. PRIVILEGED / CONFIDENTIAL INFORMATION may be contained in this message. If you are not the addressee indicated in this message or the employee or agent responsible for delivering it to the addressee, you are hereby on notice that you are in possession of confidential and privileged information. Any dissemination, distribution, or copying of this e-mail is strictly prohibited. In such case, you should destroy this message and kindly notify the sender by reply e-mail. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions, and other information in this message that do not relate to the official business of the sender's firm shall be understood as neither given nor endorsed by it. E-mail cannot be guaranteed to be secure, error free or free from viruses. Royal does not accept any liability whatsoever for any loss or damage which may be caused as a result of the transmission of this e-mail. ---(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
